A Shared Hierarchy can be modeled choosing one of these options:

• Snowflake
• Do not snowflake

Snowflake option

The shared attribute will be modeled as a dedicated dimension table.

-- ----------------------------------------------------------------
--          Indyco Builder - Auto-Generated DDL Script           --
-- ----------------------------------------------------------------
--
-- Project Name . . . . . . : docs
--  + Exported Datamarts. . : Datamart
--  + Export Timestamp. . . : 2015-04-03 12:17:27
--
-- Selected Profile . . . . : Snowflake all
--  + Shared Hierarchies. . : Snowflake
--  + Cross Dimensional Attr: Bridge table without surrogates
--  + Degenerate Dimensions : Use dimension table
--  + Multiple Arcs . . . . : Bridge table with surrogates and snowflake
--  + Recursions. . . . . . : Use self-referencing keys
--
-- Target DB. . . . . . . . : Oracle
--  + Export Primary Keys . : True
--  + Export Foreign Keys . : True
--
-- ----------------------------------------------------------------

CREATE TABLE "FT_SALE"
(
  "ID_STORE" NUMBER (9),
  "ID_CUSTOMER" NUMBER (9)
);
ALTER TABLE "FT_SALE" ADD CONSTRAINT "PK_FT_SALE" PRIMARY KEY ("ID_STORE", "ID_CUSTOMER");

CREATE TABLE "DT_STORE"
(
  "ID_STORE" NUMBER (9),
  "STORE" NVARCHAR2 (255),
  "ADDRESS" NVARCHAR2 (255),
  "ID_CITY" NUMBER (9)
);
ALTER TABLE "DT_STORE" ADD CONSTRAINT "PK_DT_STORE" PRIMARY KEY ("ID_STORE");
CREATE TABLE "DT_CITY"
(
  "ID_CITY" NUMBER (9),
  "CITY" NVARCHAR2 (255),
  "ID_COUNTRY" NUMBER (9)
);
ALTER TABLE "DT_CITY" ADD CONSTRAINT "PK_DT_CITY" PRIMARY KEY ("ID_CITY");
CREATE TABLE "DT_COUNTRY"
(
  "ID_COUNTRY" NUMBER (9),
  "COUNTRY" NVARCHAR2 (255)
);
ALTER TABLE "DT_COUNTRY" ADD CONSTRAINT "PK_DT_COUNTRY" PRIMARY KEY ("ID_COUNTRY");
CREATE TABLE "DT_CUSTOMER"
(
  "ID_CUSTOMER" NUMBER (9),
  "CUSTOMER" NVARCHAR2 (255),
  "ID_CITY" NUMBER (9)
);
ALTER TABLE "DT_CUSTOMER" ADD CONSTRAINT "PK_DT_CUSTOMER" PRIMARY KEY ("ID_CUSTOMER");
ALTER TABLE "FT_SALE" ADD CONSTRAINT "FK_FT_SALE_ID_STORE_ID_STORE" FOREIGN KEY ("ID_STORE") REFERENCES "DT_STORE" ("ID_STORE");
ALTER TABLE "FT_SALE" ADD CONSTRAINT "FK_FT_SALE_ID_CSTM_ID_CSTM" FOREIGN KEY ("ID_CUSTOMER") REFERENCES "DT_CUSTOMER" ("ID_CUSTOMER");
ALTER TABLE "DT_STORE" ADD CONSTRAINT "FK_DT_STORE_ID_CITY_ID_CITY" FOREIGN KEY ("ID_CITY") REFERENCES "DT_CITY" ("ID_CITY");
ALTER TABLE "DT_CITY" ADD CONSTRAINT "FK_DT_CITY_ID_COUNTRY_ID_CNTR" FOREIGN KEY ("ID_COUNTRY") REFERENCES "DT_COUNTRY" ("ID_COUNTRY");
ALTER TABLE "DT_CUSTOMER" ADD CONSTRAINT "FK_DT_CSTM_ID_CITY_ID_CITY" FOREIGN KEY ("ID_CITY") REFERENCES "DT_CITY" ("ID_CITY");

Do Not Snowflake

The shared attribute and its outgoing nodes will be included into every dimension table "pointing" it.

-- ----------------------------------------------------------------
--          Indyco Builder - Auto-Generated DDL Script           --
-- ----------------------------------------------------------------
--
-- Project Name . . . . . . : docs
--  + Exported Datamarts. . : Datamart
--  + Export Timestamp. . . : 2015-04-03 12:14:47
--
-- Selected Profile . . . . : Default
--  + 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_SALE"
(
  "ID_STORE" NUMBER (9),
  "ID_CUSTOMER" NUMBER (9)
);
ALTER TABLE "FT_SALE" ADD CONSTRAINT "PK_FT_SALE" PRIMARY KEY ("ID_STORE", "ID_CUSTOMER");
CREATE TABLE "DT_STORE"
(
  "ID_STORE" NUMBER (9),
  "STORE" NVARCHAR2 (255),
  "ADDRESS" NVARCHAR2 (255),
  "CITY" NVARCHAR2 (255),
  "COUNTRY" NVARCHAR2 (255)
);
ALTER TABLE "DT_STORE" ADD CONSTRAINT "PK_DT_STORE" PRIMARY KEY ("ID_STORE");
CREATE TABLE "DT_CUSTOMER"
(
  "ID_CUSTOMER" NUMBER (9),
  "CUSTOMER" NVARCHAR2 (255),
  "CITY" NVARCHAR2 (255),
  "COUNTRY" NVARCHAR2 (255)
);
ALTER TABLE "DT_CUSTOMER" ADD CONSTRAINT "PK_DT_CUSTOMER" PRIMARY KEY ("ID_CUSTOMER");
ALTER TABLE "FT_SALE" ADD CONSTRAINT "FK_FT_SALE_ID_STORE_ID_STORE" FOREIGN KEY ("ID_STORE") REFERENCES "DT_STORE" ("ID_STORE");
ALTER TABLE "FT_SALE" ADD CONSTRAINT "FK_FT_SALE_ID_CSTM_ID_CSTM" FOREIGN KEY ("ID_CUSTOMER") REFERENCES "DT_CUSTOMER" ("ID_CUSTOMER");
 

Default Profiles

Selected Strategy on Default profiles:

Automatic choices

indyco includes lot of best practices and rules from both the academic and business "worlds": to enforce and ensure the best design quality of your data platform, indyco will automatically detect conditions where only one option is viable and applies it.

For example, in case a shared hierarchy is directly connected to a fact, indyco will implicitly choose the Snowflake option: a surrogate will be added to the fact table for each arc pointing to the shared hierarchy.

As a result the fact table will contain two surrogate columns, one per arc referencing to DT_DATE

CREATE TABLE `FT_WAREHOUSE_MOVEMENT`
(
  `ID_CAUSE` INT,
  `ID_MERCHANDISE_TYPE` INT,
  `IN_DATE` INT,
  `ID_WAREHOUSE` INT,
  `ID_SKU` INT,
  `OUT_DATE` INT,
  `QUANTITY` NUMERIC (18,3)
);



CREATE TABLE `DT_DATE`
(
  `ID_DATE` INT PRIMARY KEY,
  `DATE` VARCHAR (255),
  `MONTH_IN_YEAR` VARCHAR (255),
  `ID_MONTH_IN_YEAR` INT,
  [...]
); 

-- [...]

ALTER TABLE `FT_WAREHOUSE_MOVEMENT` 
ADD CONSTRAINT `FK_FT_WAREHOUSE_MOVEMENT_IN_DATE_ID_DATE`
FOREIGN KEY (`IN_DATE`) REFERENCES `DT_DATE` (`ID_DATE`);

ALTER TABLE `FT_WAREHOUSE_MOVEMENT` 
ADD CONSTRAINT `FK_FT_WAREHOUSE_MOVEMENT_OUT_DATE_ID_DATE` FOREIGN KEY (`OUT_DATE`) 
REFERENCES `DT_DATE` (`ID_DATE`);