applies to: Indyco Builder 1.57 +

feature not available on: 

Educational License

A degenerate dimension can be modeled choosing one of these options:

  • Use junk table
  • Use dimension table
  • Use fact table



Use Junk Table

Every degenerate dimension will be added to a junk table, referenced by the fact table using a surrogate key.


-- ----------------------------------------------------------------
--          Indyco Builder - Auto-Generated DDL Script           --
-- ----------------------------------------------------------------
--
-- Project Name . . . . . . : docs
--  + Exported Datamarts. . : Datamart Junk
--  + Export Timestamp. . . : 2015-04-03 14:47:43
--
-- Selected Profile . . . . : Custom
--  + Shared Hierarchies. . : Do not snowflake
--  + Cross Dimensional Attr: Bridge table without surrogates
--  + Degenerate Dimensions : Use junk table
--  + Multiple Arcs . . . . : Bridge table without surrogates
--  + Recursions. . . . . . : Use self-referencing keys
--
-- Target DB. . . . . . . . : Oracle
--  + Export Primary Keys . : True
--  + Export Foreign Keys . : True
--
-- ----------------------------------------------------------------

CREATE TABLE "FT_FACT"
(
  "ID_JT_FACT" NUMBER (9),
  "ID_CUSTOMER" NUMBER (9),
  "MEASURE" NUMBER (18,3)
);
ALTER TABLE "FT_FACT" ADD CONSTRAINT "PK_FT_FACT" PRIMARY KEY ("ID_JT_FACT", "ID_CUSTOMER");


CREATE TABLE "JT_FACT"
(
  "ID_JT_FACT" NUMBER (9),
  "FLAG_B" CHAR(1),
  "FLAG_A" CHAR(1),
  "FLAG_C" CHAR(1),
  "FLAG_D" CHAR(1)
);
ALTER TABLE "JT_FACT" ADD CONSTRAINT "PK_JT_FACT" PRIMARY KEY ("ID_JT_FACT");


CREATE TABLE "DT_CUSTOMER"
(
  "ID_CUSTOMER" NUMBER (9),
  "CUSTOMER" NVARCHAR2 (255),
  "COMPANY" NVARCHAR2 (255)
);
ALTER TABLE "DT_CUSTOMER" ADD CONSTRAINT "PK_DT_CUSTOMER" PRIMARY KEY ("ID_CUSTOMER");


ALTER TABLE "FT_FACT" ADD CONSTRAINT "FK_FT_FACT_ID_JT_FACT_ID_JT_F" FOREIGN KEY ("ID_JT_FACT") REFERENCES "JT_FACT" ("ID_JT_FACT");
ALTER TABLE "FT_FACT" ADD CONSTRAINT "FK_FT_FACT_ID_CSTM_ID_CSTM" FOREIGN KEY ("ID_CUSTOMER") REFERENCES "DT_CUSTOMER" ("ID_CUSTOMER");
 

  


Use Dimension Table

For each degenerate dimension a dedicated dimension table will be added.


-- ----------------------------------------------------------------
--          Indyco Builder - Auto-Generated DDL Script           --
-- ----------------------------------------------------------------
--
-- Project Name . . . . . . : docs
--  + Exported Datamarts. . : Datamart Junk
--  + Export Timestamp. . . : 2015-04-03 14:52:31
--
-- Selected Profile . . . . : Custom
--  + Shared Hierarchies. . : Do not snowflake
--  + Cross Dimensional Attr: Bridge table without surrogates
--  + Degenerate Dimensions : Use dimension table
--  + Multiple Arcs . . . . : Bridge table without surrogates
--  + Recursions. . . . . . : Use self-referencing keys
--
-- Target DB. . . . . . . . : Oracle
--  + Export Primary Keys . : True
--  + Export Foreign Keys . : True
--
-- ----------------------------------------------------------------

CREATE TABLE "FT_FACT"
(
  "ID_FLAG_B" NUMBER (9),
  "ID_FLAG_A" NUMBER (9),
  "ID_FLAG_C" NUMBER (9),
  "ID_FLAG_D" NUMBER (9),
  "ID_CUSTOMER" NUMBER (9),
  "MEASURE" NUMBER (18,3)
);
ALTER TABLE "FT_FACT" ADD CONSTRAINT "PK_FT_FACT" PRIMARY KEY ("ID_FLAG_B", "ID_FLAG_A", "ID_FLAG_C", "ID_FLAG_D", "ID_CUSTOMER");


CREATE TABLE "DT_FLAG_B"
(
  "ID_FLAG_B" NUMBER (9),
  "FLAG_B" CHAR(1)
);
ALTER TABLE "DT_FLAG_B" ADD CONSTRAINT "PK_DT_FLAG_B" PRIMARY KEY ("ID_FLAG_B");


CREATE TABLE "DT_FLAG_A"
(
  "ID_FLAG_A" NUMBER (9),
  "FLAG_A" CHAR(1)
);
ALTER TABLE "DT_FLAG_A" ADD CONSTRAINT "PK_DT_FLAG_A" PRIMARY KEY ("ID_FLAG_A");


CREATE TABLE "DT_FLAG_C"
(
  "ID_FLAG_C" NUMBER (9),
  "FLAG_C" CHAR(1)
);
ALTER TABLE "DT_FLAG_C" ADD CONSTRAINT "PK_DT_FLAG_C" PRIMARY KEY ("ID_FLAG_C");


CREATE TABLE "DT_FLAG_D"
(
  "ID_FLAG_D" NUMBER (9),
  "FLAG_D" CHAR(1)
);
ALTER TABLE "DT_FLAG_D" ADD CONSTRAINT "PK_DT_FLAG_D" PRIMARY KEY ("ID_FLAG_D");


CREATE TABLE "DT_CUSTOMER"
(
  "ID_CUSTOMER" NUMBER (9),
  "CUSTOMER" NVARCHAR2 (255),
  "COMPANY" NVARCHAR2 (255)
);
ALTER TABLE "DT_CUSTOMER" ADD CONSTRAINT "PK_DT_CUSTOMER" PRIMARY KEY ("ID_CUSTOMER");


ALTER TABLE "FT_FACT" ADD CONSTRAINT "FK_FT_FACT_ID_FLAG_B_ID_FLAG" FOREIGN KEY ("ID_FLAG_B") REFERENCES "DT_FLAG_B" ("ID_FLAG_B");
ALTER TABLE "FT_FACT" ADD CONSTRAINT "FK_FT_FACT_ID_FLAG_A_ID_FLAG" FOREIGN KEY ("ID_FLAG_A") REFERENCES "DT_FLAG_A" ("ID_FLAG_A");
ALTER TABLE "FT_FACT" ADD CONSTRAINT "FK_FT_FACT_ID_FLAG_C_ID_FLAG" FOREIGN KEY ("ID_FLAG_C") REFERENCES "DT_FLAG_C" ("ID_FLAG_C");
ALTER TABLE "FT_FACT" ADD CONSTRAINT "FK_FT_FACT_ID_FLAG_D_ID_FLAG" FOREIGN KEY ("ID_FLAG_D") REFERENCES "DT_FLAG_D" ("ID_FLAG_D");
ALTER TABLE "FT_FACT" ADD CONSTRAINT "FK_FT_FACT_ID_CSTM_ID_CSTM" FOREIGN KEY ("ID_CUSTOMER") REFERENCES "DT_CUSTOMER" ("ID_CUSTOMER");

 



Use Fact Table

Each degenerate dimension will be added to the fact table as a new column.


-- ----------------------------------------------------------------
--          Indyco Builder - Auto-Generated DDL Script           --
-- ----------------------------------------------------------------
--
-- Project Name . . . . . . : docs
--  + Exported Datamarts. . : Datamart Junk
--  + Export Timestamp. . . : 2015-04-03 14:41:39
--
-- Selected Profile . . . . : Custom
--  + Shared Hierarchies. . : Do not snowflake
--  + Cross Dimensional Attr: Bridge table without surrogates
--  + Degenerate Dimensions : Use fact table
--  + Multiple Arcs . . . . : Bridge table without surrogates
--  + Recursions. . . . . . : Use self-referencing keys
--
-- Target DB. . . . . . . . : Oracle
--  + Export Primary Keys . : True
--  + Export Foreign Keys . : True
--
-- ----------------------------------------------------------------

CREATE TABLE "FT_FACT"
(
  "FLAG_B" CHAR(1),
  "FLAG_A" CHAR(1),
  "FLAG_C" CHAR(1),
  "FLAG_D" CHAR(1),
  "ID_CUSTOMER" NUMBER (9),
  "MEASURE" NUMBER (18,3)
);
ALTER TABLE "FT_FACT" ADD CONSTRAINT "PK_FT_FACT" PRIMARY KEY ("FLAG_B", "FLAG_A", "FLAG_C", "FLAG_D", "ID_CUSTOMER");
CREATE TABLE "DT_CUSTOMER"
(
  "ID_CUSTOMER" NUMBER (9),
  "CUSTOMER" NVARCHAR2 (255),
  "COMPANY" NVARCHAR2 (255)
);
ALTER TABLE "DT_CUSTOMER" ADD CONSTRAINT "PK_DT_CUSTOMER" PRIMARY KEY ("ID_CUSTOMER");
ALTER TABLE "FT_FACT" ADD CONSTRAINT "FK_FT_FACT_ID_CSTM_ID_CSTM" FOREIGN KEY ("ID_CUSTOMER") REFERENCES "DT_CUSTOMER" ("ID_CUSTOMER");