Archive for August, 2016

ODS – universal codebooks and hierarchies

Posted: August 6, 2016 in MSSQL
Tags:
0

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: