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");