A Multiple Arcs  can be modeled choosing one of these options:

  • bridge table without surrogates
  • bridge table with surrogates
  • bridge table with surrogates and snowflake


 


Project: Sample Project
Datamart: Commercial
Fact: Invoice

Additional attribute for this example: Company



Bridge table without surrogates

The many-to-many association will be modeled using a bridge table referencing each dimension table using natural keys. 

A weight attribute will be added to give different relevance to tuples. 





-- ----------------------------------------------------------------
--          Indyco Builder - Auto-Generated DDL Script           --
-- ----------------------------------------------------------------
--
-- Project Name . . . . . . : test_docs_project
--  + Exported Datamarts. . : Commercial
--  + Export Timestamp. . . : 2015-04-07 14:40:13
--
-- 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. . . . . . . . : SqlServer
--  + Export Primary Keys . : True
--  + Export Foreign Keys . : True
--
-- ----------------------------------------------------------------

CREATE TABLE [FT_INVOICE]
(
  [ID_SUB_AGENT] INT NOT NULL,
  [QUANTITY] NUMERIC (18,3),
  [NET_VALUE] NUMERIC (18,3),
  [FIRST_COMMERCIAL_DISCOUNT] NUMERIC (18,3),
  [SECOND_COMMERCIAL_DISCOUNT] NUMERIC (18,3),
  [SELL_IN_MARGIN] NUMERIC (18,3),
  [NET_AVERAGE_PRICE] NUMERIC (18,3)
);
ALTER TABLE [FT_INVOICE] ADD CONSTRAINT [PK_FT_INVOICE] PRIMARY KEY ([ID_SUB_AGENT]);


CREATE TABLE [DT_SUB_AGENT]
(
  [ID_SUB_AGENT] INT NOT NULL,
  [SUB_AGENT] VARCHAR (255),
  [AGENT] VARCHAR (255),
  [AREA_MANAGER] VARCHAR (255)
);
ALTER TABLE [DT_SUB_AGENT] ADD CONSTRAINT [PK_DT_SUB_AGENT] PRIMARY KEY ([ID_SUB_AGENT]);


CREATE TABLE [BT_AGENT_CONTROLLER]
(
  [AGENT] VARCHAR (255) NOT NULL,
  [WEIGHT] INT,
  [ID_CONTROLLER] INT NOT NULL
);
ALTER TABLE [BT_AGENT_CONTROLLER] ADD CONSTRAINT [PK_BT_AGENT_CONTROLLER] PRIMARY KEY ([AGENT], [ID_CONTROLLER]);


CREATE TABLE [DT_CONTROLLER]
(
  [ID_CONTROLLER] INT NOT NULL,
  [CONTROLLER] VARCHAR (255),
  [COMPANY] VARCHAR (255)
);
ALTER TABLE [DT_CONTROLLER] ADD CONSTRAINT [PK_DT_CONTROLLER] PRIMARY KEY ([ID_CONTROLLER]);


ALTER TABLE [FT_INVOICE] ADD CONSTRAINT [FK_FT_INVC_ID_SUB_AGNT_ID_SUB] FOREIGN KEY ([ID_SUB_AGENT]) REFERENCES [DT_SUB_AGENT] ([ID_SUB_AGENT]);
ALTER TABLE [BT_AGENT_CONTROLLER] ADD CONSTRAINT [FK_BT_AGNT_CNTR_ID_CNTR_ID_CN] FOREIGN KEY ([ID_CONTROLLER]) REFERENCES [DT_CONTROLLER] ([ID_CONTROLLER]); 

    



Bridge table with surrogates

The many-to-many will be modeled using a bridge table referencing each dimension table using surrogate keys. 

A weight attribute will be added to give different relevance to tuples. 


   

-- ----------------------------------------------------------------
--          Indyco Builder - Auto-Generated DDL Script           --
-- ----------------------------------------------------------------
--
-- Project Name . . . . . . : test_docs_project
--  + Exported Datamarts. . : Commercial
--  + Export Timestamp. . . : 2015-04-07 14:40:41
--
-- Selected Profile . . . . : Custom
--  + Shared Hierarchies. . : Do not snowflake
--  + Cross Dimensional Attr: Bridge table without surrogates
--  + Degenerate Dimensions : Use dimension table
--  + Multiple Arcs . . . . : Bridge table with surrogates
--  + Recursions. . . . . . : Use self-referencing keys
--
-- Target DB. . . . . . . . : SqlServer
--  + Export Primary Keys . : True
--  + Export Foreign Keys . : True
--
-- ----------------------------------------------------------------

CREATE TABLE [FT_INVOICE]
(
  [ID_SUB_AGENT] INT NOT NULL,
  [QUANTITY] NUMERIC (18,3),
  [NET_VALUE] NUMERIC (18,3),
  [FIRST_COMMERCIAL_DISCOUNT] NUMERIC (18,3),
  [SECOND_COMMERCIAL_DISCOUNT] NUMERIC (18,3),
  [SELL_IN_MARGIN] NUMERIC (18,3),
  [NET_AVERAGE_PRICE] NUMERIC (18,3)
);
ALTER TABLE [FT_INVOICE] ADD CONSTRAINT [PK_FT_INVOICE] PRIMARY KEY ([ID_SUB_AGENT]);


CREATE TABLE [DT_SUB_AGENT]
(
  [ID_SUB_AGENT] INT NOT NULL,
  [SUB_AGENT] VARCHAR (255),
  [AGENT] VARCHAR (255),
  [AREA_MANAGER] VARCHAR (255)
);
ALTER TABLE [DT_SUB_AGENT] ADD CONSTRAINT [PK_DT_SUB_AGENT] PRIMARY KEY ([ID_SUB_AGENT]);


CREATE TABLE [BT_AGENT_CONTROLLER]
(
  [CONTROLLER] VARCHAR (255) NOT NULL,
  [WEIGHT] INT,
  [ID_SUB_AGENT] INT NOT NULL,
  [COMPANY] VARCHAR (255)
);
ALTER TABLE [BT_AGENT_CONTROLLER] ADD CONSTRAINT [CON] PRIMARY KEY ([ID_SUB_AGENT], [CONTROLLER]);


ALTER TABLE [FT_INVOICE] ADD CONSTRAINT [FK_FT_INVC_ID_SUB_AGNT_ID_SUB] FOREIGN KEY ([ID_SUB_AGENT]) REFERENCES [DT_SUB_AGENT] ([ID_SUB_AGENT]);
ALTER TABLE [BT_AGENT_CONTROLLER] ADD CONSTRAINT [FK_BT_AGNT_CNTR_ID_SUB_AGNT_I] FOREIGN KEY ([ID_SUB_AGENT]) REFERENCES [DT_SUB_AGENT] ([ID_SUB_AGENT]); 

   



Bridge table with surrogates and snowflake

The many-to-many will be modeled using a bridge table referencing each dimension table using surrogate keys.

The target attribute (in this case Controller) will be modeled as a separated dimension table. 

A weight attribute will be added to give different relevance to tuples. 


 

 

-- ----------------------------------------------------------------
--          Indyco Builder - Auto-Generated DDL Script           --
-- ----------------------------------------------------------------
--
-- Project Name . . . . . . : test_docs_project
--  + Exported Datamarts. . : Commercial
--  + Export Timestamp. . . : 2015-04-07 14:45:13
--
-- Selected Profile . . . . : Custom
--  + Shared Hierarchies. . : Do not 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. . . . . . . . : SqlServer
--  + Export Primary Keys . : True
--  + Export Foreign Keys . : True
--
-- ----------------------------------------------------------------

CREATE TABLE [FT_INVOICE]
(
  [ID_SUB_AGENT] INT NOT NULL,
  [QUANTITY] NUMERIC (18,3),
  [NET_VALUE] NUMERIC (18,3),
  [FIRST_COMMERCIAL_DISCOUNT] NUMERIC (18,3),
  [SECOND_COMMERCIAL_DISCOUNT] NUMERIC (18,3),
  [SELL_IN_MARGIN] NUMERIC (18,3),
  [NET_AVERAGE_PRICE] NUMERIC (18,3)
);
ALTER TABLE [FT_INVOICE] ADD CONSTRAINT [PK_FT_INVOICE] PRIMARY KEY ([ID_SUB_AGENT]);


CREATE TABLE [DT_SUB_AGENT]
(
  [ID_SUB_AGENT] INT NOT NULL,
  [SUB_AGENT] VARCHAR (255),
  [AGENT] VARCHAR (255),
  [AREA_MANAGER] VARCHAR (255)
);
ALTER TABLE [DT_SUB_AGENT] ADD CONSTRAINT [PK_DT_SUB_AGENT] PRIMARY KEY ([ID_SUB_AGENT]);


CREATE TABLE [BT_AGENT_CONTROLLER]
(
  [ID_AGENT] INT NOT NULL,
  [ID_CONTROLLER] INT NOT NULL,
  [WEIGHT] INT
);
ALTER TABLE [BT_AGENT_CONTROLLER] ADD CONSTRAINT [PK_BT_AGENT_CONTROLLER] PRIMARY KEY ([ID_AGENT], [ID_CONTROLLER]);


CREATE TABLE [DT_CONTROLLER]
(
  [ID_CONTROLLER] INT NOT NULL,
  [CONTROLLER] VARCHAR (255),
  [COMPANY] VARCHAR (255)
);
ALTER TABLE [DT_CONTROLLER] ADD CONSTRAINT [CON] PRIMARY KEY ([ID_CONTROLLER]);


ALTER TABLE [FT_INVOICE] ADD CONSTRAINT [FK_FT_INVC_ID_SUB_AGNT_ID_SUB] FOREIGN KEY ([ID_SUB_AGENT]) REFERENCES [DT_SUB_AGENT] ([ID_SUB_AGENT]);
ALTER TABLE [BT_AGENT_CONTROLLER] ADD CONSTRAINT [FK_BT_AGNT_CNTR_ID_CNTR_ID_CN] FOREIGN KEY ([ID_CONTROLLER]) REFERENCES [DT_CONTROLLER] ([ID_CONTROLLER]);
ALTER TABLE [BT_AGENT_CONTROLLER] ADD CONSTRAINT [FK_BT_AGENT_CNTR_ID_AGNT_AGNT] FOREIGN KEY ([ID_AGENT]) REFERENCES [DT_SUB_AGENT] ([ID_SUB_AGENT]);