ODS – universal codebooks and hierarchies

Posted: August 6, 2016 in MSSQL
Tags:

Task: to show how to create universal codebooks and hierarchies for Operational Data Storage (3NF) in DW solutions

a) universal codebooks:

ods_univ_codebook_diagram

CREATE TABLE [dbo].[CodeBook](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [CodeBookTypeAutoID] [int] NULL,
 [CodeBookID] [nvarchar](50) NULL,
 [CodeBookText] [nvarchar](50) NULL,
 [CodeBookTextEnglish] [nvarchar](50) NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL,
 CONSTRAINT [PK_CodeBook] PRIMARY KEY CLUSTERED 
( [AutoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[CodeBookType](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [CodeBookTypeID] [nvarchar](50) NULL,
 [CodeBookTypeText] [nvarchar](50) NULL,
 [CodeBookTypeTextEnglish] [nvarchar](50) NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL,
 CONSTRAINT [PK_CodeBookType] PRIMARY KEY CLUSTERED 
( [AutoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[CodeBook2Contract](
 [CodeBookTypeAutoID] [int] NULL,
 [CodeBookAutoID] [int] NULL,
 [ContractAutoID] [int] NULL,
 [ValidFrom] [int] NULL,
 [ValidTo] [int] NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[Contract](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [ContractID] [nvarchar](50) NULL,
 [ValidFrom] [int] NULL,
 [ValidTo] [int] NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL,
 CONSTRAINT [PK_Contract] PRIMARY KEY CLUSTERED 
( [AutoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[CodeBook2Contract] WITH NOCHECK ADD CONSTRAINT [FK_CodeBook2Contract_CodeBook] FOREIGN KEY([CodeBookAutoID])
REFERENCES [dbo].[CodeBook] ([AutoID])
GO
ALTER TABLE [dbo].[CodeBook2Contract] CHECK CONSTRAINT [FK_CodeBook2Contract_CodeBook]
GO

ALTER TABLE [dbo].[CodeBook2Contract] WITH NOCHECK ADD CONSTRAINT [FK_CodeBook2Contract_CodeBookType] FOREIGN KEY([CodeBookTypeAutoID])
REFERENCES [dbo].[CodeBookType] ([AutoID])
GO
ALTER TABLE [dbo].[CodeBook2Contract] CHECK CONSTRAINT [FK_CodeBook2Contract_CodeBookType]
GO

ALTER TABLE [dbo].[CodeBook2Contract] WITH NOCHECK ADD CONSTRAINT [FK_CodeBook2Contract_Contract] FOREIGN KEY([ContractAutoID])
REFERENCES [dbo].[Contract] ([AutoID])
GO
ALTER TABLE [dbo].[CodeBook2Contract] CHECK CONSTRAINT [FK_CodeBook2Contract_Contract]
GO


ALTER TABLE [dbo].[CodeBook] WITH NOCHECK ADD CONSTRAINT [FK_CodeBook_CodeBookType] FOREIGN KEY([CodeBookTypeAutoID])
REFERENCES [dbo].[CodeBookType] ([AutoID])
GO
ALTER TABLE [dbo].[CodeBook] CHECK CONSTRAINT [FK_CodeBook_CodeBookType]
GO


SET IDENTITY_INSERT [dbo].[CodeBookType] ON
GO
INSERT [dbo].[CodeBookType] ([AutoID], [CodeBookTypeID], [CodeBookTypeText], [CodeBookTypeTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'CS', NULL, N'Contract status', NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[CodeBookType] OFF
GO


SET IDENTITY_INSERT [dbo].[CodeBook] ON
GO
INSERT [dbo].[CodeBook] ([AutoID], [CodeBookTypeAutoID], [CodeBookID], [CodeBookText], [CodeBookTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, 1, N'CSO', NULL, N'Open contract', NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[CodeBook] OFF
GO


SET IDENTITY_INSERT [dbo].[Contract] ON
GO
INSERT [dbo].[Contract] ([AutoID], [ContractID], [ValidFrom], [ValidTo], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'1', NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[Contract] OFF
GO


INSERT [dbo].[CodeBook2Contract] ([CodeBookTypeAutoID], [CodeBookAutoID], [ContractAutoID], [ValidFrom], [ValidTo], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, 1, 1, NULL, NULL, NULL, NULL)
GO

 

ods_univ_codebook_result

b) universal hierarchies:

ods_univ_hierarchy_diagram

CREATE TABLE [dbo].[HierarchyType](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [HierarchyTypeID] [nvarchar](50) NULL,
 [HierarchyTypeText] [nvarchar](50) NULL,
 [HierarchyTypeTextEnglish] [nvarchar](50) NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL,
 CONSTRAINT [PK_HierarchyType] PRIMARY KEY CLUSTERED 
( [AutoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[Hierarchy](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [HierarchyID] [nvarchar](50) NULL,
 [HierarchyTypeAutoID] [int] NULL,
 [HierarchyText] [nvarchar](50) NULL,
 [HierarchyTextEnglish] [nvarchar](50) NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL,
 CONSTRAINT [PK_Hierarchy] PRIMARY KEY CLUSTERED 
( [AutoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[HierarchyLinkType](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [HierarchyLinkTypeID] [nvarchar](50) NULL,
 [HierarchyTypeSourceAutoID] [int] NULL,
 [HierarchyTypeDestinationAutoID] [int] NULL,
 [HierarchyLinkTypeText] [nvarchar](50) NULL,
 [HierarchyLinkTypeTextEnglish] [nvarchar](50) NULL,
 [ValidFrom] [int] NULL,
 [ValidTo] [int] NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL,
 CONSTRAINT [PK_HierarchyLinkType] PRIMARY KEY CLUSTERED 
( [AutoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[HierarchyLink](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [HierarchyLinkID] [nvarchar](50) NULL,
 [HierarchyLinkTypeAutoID] [int] NULL,
 [HierarchySourceAutoID] [int] NULL,
 [HierarchyDestinationAutoID] [int] NULL,
 [ValidFrom] [int] NULL,
 [ValidTo] [int] NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL,
 CONSTRAINT [PK_HierarchyLink] PRIMARY KEY CLUSTERED 
( [AutoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[Hierarchy] WITH NOCHECK ADD CONSTRAINT [FK_Hierarchy_HierarchyType] FOREIGN KEY([HierarchyTypeAutoID])
REFERENCES [dbo].[HierarchyType] ([AutoID])
GO
ALTER TABLE [dbo].[Hierarchy] CHECK CONSTRAINT [FK_Hierarchy_HierarchyType]
GO


ALTER TABLE [dbo].[HierarchyLinkType] WITH NOCHECK ADD CONSTRAINT [FK_HierarchyLinkType_HierarchyType] FOREIGN KEY([HierarchyTypeSourceAutoID])
REFERENCES [dbo].[HierarchyType] ([AutoID])
GO
ALTER TABLE [dbo].[HierarchyLinkType] CHECK CONSTRAINT [FK_HierarchyLinkType_HierarchyType]
GO


ALTER TABLE [dbo].[HierarchyLink] WITH NOCHECK ADD CONSTRAINT [FK_HierarchyLink_Hierarchy_S] FOREIGN KEY([HierarchySourceAutoID])
REFERENCES [dbo].[Hierarchy] ([AutoID])
GO
ALTER TABLE [dbo].[HierarchyLink] CHECK CONSTRAINT [FK_HierarchyLink_Hierarchy_S]
GO

ALTER TABLE [dbo].[HierarchyLink] WITH NOCHECK ADD CONSTRAINT [FK_HierarchyLink_Hierarchy_D] FOREIGN KEY([HierarchyDestinationAutoID])
REFERENCES [dbo].[Hierarchy] ([AutoID])
GO
ALTER TABLE [dbo].[HierarchyLink] CHECK CONSTRAINT [FK_HierarchyLink_Hierarchy_D]
GO

ALTER TABLE [dbo].[HierarchyLink] WITH NOCHECK ADD CONSTRAINT [FK_HierarchyLink_HierarchyLinkType] FOREIGN KEY([HierarchyLinkTypeAutoID])
REFERENCES [dbo].[HierarchyLinkType] ([AutoID])
GO
ALTER TABLE [dbo].[HierarchyLink] CHECK CONSTRAINT [FK_HierarchyLink_HierarchyLinkType]
GO


SET IDENTITY_INSERT [dbo].[HierarchyType] ON 
GO
INSERT [dbo].[HierarchyType] ([AutoID], [HierarchyTypeID], [HierarchyTypeText], [HierarchyTypeTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'ORG_L1', NULL, N'Level1', NULL, NULL)
GO
INSERT [dbo].[HierarchyType] ([AutoID], [HierarchyTypeID], [HierarchyTypeText], [HierarchyTypeTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (2, N'ORG_L2', NULL, N'Level2', NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[HierarchyType] OFF
GO


SET IDENTITY_INSERT [dbo].[Hierarchy] ON 
GO
INSERT [dbo].[Hierarchy] ([AutoID], [HierarchyID], [HierarchyTypeAutoID], [HierarchyText], [HierarchyTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'ORG_L1_1', 1, NULL, N'ORG Level1 A', NULL, NULL)
GO
INSERT [dbo].[Hierarchy] ([AutoID], [HierarchyID], [HierarchyTypeAutoID], [HierarchyText], [HierarchyTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (2, N'ORG_L2_1', 2, NULL, N'ORG Level2 A', NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[Hierarchy] OFF
GO


SET IDENTITY_INSERT [dbo].[HierarchyLinkType] ON 
GO
INSERT [dbo].[HierarchyLinkType] ([AutoID], [HierarchyLinkTypeID], [HierarchyTypeSourceAutoID], [HierarchyTypeDestinationAutoID], [HierarchyLinkTypeText], [HierarchyLinkTypeTextEnglish], [ValidFrom], [ValidTo], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'ORG_L1_L2', 1, 2, NULL, N'ORG Level1 - Level2', NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[HierarchyLinkType] OFF
GO


SET IDENTITY_INSERT [dbo].[HierarchyLink] ON 
GO
INSERT [dbo].[HierarchyLink] ([AutoID], [HierarchyLinkID], [HierarchyLinkTypeAutoID], [HierarchySourceAutoID], [HierarchyDestinationAutoID], [ValidFrom], [ValidTo], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'ORG_L1_1-ORG_L2_1', 1, 1, 2, NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[HierarchyLink] OFF
GO

 

ods_univ_hierarchy_result

Conclusion:

a) universal codebooks: one set of tables (3x codebook + 1 per entity) can contain more codebooks’ records (e.g. Contract status, Contract payment frequency, Client type etc.)

b) universal hierarchies: one set of tables (4 “common” tables) can contain more hierarchies’ records (e.g. Organisational structure, Business structure, Sales channels etc.) By using entities “HierarchyLinkType and HierarchyLink” is possible to create combinations between “source” and “destination” (bi-directional connections.)

Source code:

Additional references:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s