Indyco Support Center

A great feature of indyco, is about the opportunity to manage Conformed hierarchies that you can easily import in your fact schema.

Using the Default profile (star schema), a conformed hierarchy is modeled by a single dimension table.

In case a fact schema locally extends a conformed hierarchy instance, every added attribute is included into the dimension table as a nullable column.

In case a fact schema partially includes a conformed hierarchy (i.e., starting from a non-root attribute), a SQL View is created pointing to the dimension table and including a specific subset of columns.

Example:

We will illustrate this scenario with reference to our Sample Project, focusing in particular on conformed hierarchies "Geography" and "Customer”. 

As shown in the picture below, Geography is included in Customer.

Geography is also partially included (starting from the Country attribute) in the "Historical Order" fact schema.

Finally, Customer is included in the "Order" fact schema.

The Customer conformed hierarchy is modeled by two dimension tables: DT_CUSTOMER and DT_GEOGRAPHY.

-- ----------------------------------------------------------------
--          Indyco Builder - Auto-Generated DDL Script           --
-- ----------------------------------------------------------------
--
-- Project Name . . . . . . : Indyco Tutorial
--  + Exported Datamarts. . : Commercial
--  + Export Timestamp. . . : 2015-04-03 11:03:02
--
-- 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. . . . . . . . : MySql
--  + Export Primary Keys . : True
--  + Export Foreign Keys . : True
--
-- ----------------------------------------------------------------

CREATE TABLE `DT_GEOGRAPHY`
(
  `ID_CITY` INT PRIMARY KEY,
  `CITY` VARCHAR (255),
  `COUNTY` VARCHAR (255),
  `ID_COUNTY` INT,
  `STATE` VARCHAR (255),
  `ID_STATE` INT,
  `COUNTRY` VARCHAR (255),
  `ID_COUNTRY` INT,
  `AREA` VARCHAR (255),
  `ID_AREA` INT
);

CREATE TABLE `DT_CUSTOMER`
(
  `ID_CUSTOMER` INT PRIMARY KEY,
  `CUSTOMER` VARCHAR (255),
  `BILLING_INFORMATION` VARCHAR (255),
  `ID_CITY` INT,
  `SHIPPING_ADDRESS` VARCHAR (255)
);

ALTER TABLE `DT_CUSTOMER` ADD CONSTRAINT `FK_DT_CSTM_ID_CITY_ID_CITY` FOREIGN KEY (`ID_CITY`) REFERENCES `DT_GEOGRAPHY` (`ID_CITY`);
ALTER TABLE `FT_ORDER` ADD CONSTRAINT `FK_FT_ORDER_ID_CSTM_ID_CSTM` FOREIGN KEY (`ID_CUSTOMER`) REFERENCES `DT_CUSTOMER` (`ID_CUSTOMER`); 

-- [...]

In the Historical Order fact table, the Geography dimension is modeled using an ID_COUNTRY surrogate key that references a dedicated SQL view DTV_GEOGRAPHY_COUNTRY, which only contains the Country and Area columns.

-- [...]

CREATE VIEW `DTV_GEOGRAPHY_COUNTRY` AS 
SELECT DISTINCT 
  `ID_COUNTRY`,
  `COUNTRY`,
  `AREA`
 FROM GEOGRAPHY` ; 

You can try this sample by yourself by downloading  a free trial at indyco.com.

indyco Builder includes the sample project used in this article.