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