November 26, 2017 at 4:01 am
CRM developer wants to create a hierarchy table to remove records in Service table that don't exist in Problem table (using column FKCase to detect this). That hierarchy table will then be available for BI developer to get the data from and insert into data warehouse (inclusive of case, problem, service data). Hierarchy table will look like this: Case > Problem > Service.
(Parent is Case, Problem is child level 1, Service is child level 2).
Below is the create statement for the 3 tables (showing how they look like currently):
CREATE TABLE [dbo].[Service](
[PKCaseService] [int] IDENTITY(1,1) NOT NULL,
[FKCase] [int] NOT NULL,
[ServiceType] [nvarchar](50) NOT NULL,
[ServiceDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Service] ON
INSERT [dbo].[Service] ([PKCaseService], [FKCase], [ServiceType], [ServiceDate]) VALUES (102, 541, N'f6ad4cd0-f509-e111-8843-00155d00300b', CAST(0x0000A27A00000000 AS DateTime))
INSERT [dbo].[Service] ([PKCaseService], [FKCase], [ServiceType], [ServiceDate]) VALUES (573, 547, N'cffbee83-fc08-e111-8843-00155d00300b', CAST(0x0000A25B00000000 AS DateTime))
INSERT [dbo].[Service] ([PKCaseService], [FKCase], [ServiceType], [ServiceDate]) VALUES (574, 547, N'f6ad4cd0-f509-e111-8843-00155d00300b', CAST(0x0000A25B00000000 AS DateTime))
INSERT [dbo].[Service] ([PKCaseService], [FKCase], [ServiceType], [ServiceDate]) VALUES (583, 548, N'cffbee83-fc08-e111-8843-00155d00300b', CAST(0x0000A21300000000 AS DateTime))
INSERT [dbo].[Service] ([PKCaseService], [FKCase], [ServiceType], [ServiceDate]) VALUES (584, 548, N'f6ad4cd0-f509-e111-8843-00155d00300b', CAST(0x0000A21300000000 AS DateTime))
INSERT [dbo].[Service] ([PKCaseService], [FKCase], [ServiceType], [ServiceDate]) VALUES (585, 548, N'03ebc4bb-f609-e111-8843-00155d00300b', CAST(0x0000A21400000000 AS DateTime))
INSERT [dbo].[Service] ([PKCaseService], [FKCase], [ServiceType], [ServiceDate]) VALUES (611, 550, N'1bebc4bb-f609-e111-8843-00155d00300b', CAST(0x0000A00700000000 AS DateTime))
INSERT [dbo].[Service] ([PKCaseService], [FKCase], [ServiceType], [ServiceDate]) VALUES (613, 551, N'f6ad4cd0-f509-e111-8843-00155d00300b', CAST(0x0000A00D00000000 AS DateTime))
INSERT [dbo].[Service] ([PKCaseService], [FKCase], [ServiceType], [ServiceDate]) VALUES (623, 552, N'cffbee83-fc08-e111-8843-00155d00300b', CAST(0x0000A0CA00000000 AS DateTime))
INSERT [dbo].[Service] ([PKCaseService], [FKCase], [ServiceType], [ServiceDate]) VALUES (669, 554, N'f6ad4cd0-f509-e111-8843-00155d00300b', CAST(0x00009FA200000000 AS DateTime))
SET IDENTITY_INSERT [dbo].[Service] OFF
GO
CREATE TABLE [dbo].[Problem](
[PKCaseProblem] [int] IDENTITY(1,1) NOT NULL,
[FKCase] [int] NOT NULL,
[ProblemType] [nvarchar](50) NOT NULL,
[ProblemRelatesTo] [int] NOT NULL,
[ProblemDate] [datetime] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Problem] ON
INSERT [dbo].[Problem] ([PKCaseProblem], [FKCase], [ProblemType], [ProblemRelatesTo], [ProblemDate]) VALUES (49, 541, N'444656e9-fc08-e111-8843-00155d00300b', 2, CAST(0x0000A26D00000000 AS DateTime))
INSERT [dbo].[Problem] ([PKCaseProblem], [FKCase], [ProblemType], [ProblemRelatesTo], [ProblemDate]) VALUES (211, 547, N'bec88efb-fc08-e111-8843-00155d00300b', 6, CAST(0x0000A26000000000 AS DateTime))
INSERT [dbo].[Problem] ([PKCaseProblem], [FKCase], [ProblemType], [ProblemRelatesTo], [ProblemDate]) VALUES (212, 547, N'bc4480f5-fc08-e111-8843-00155d00300b', 6, CAST(0x0000A25B00000000 AS DateTime))
INSERT [dbo].[Problem] ([PKCaseProblem], [FKCase], [ProblemType], [ProblemRelatesTo], [ProblemDate]) VALUES (218, 548, N'bec88efb-fc08-e111-8843-00155d00300b', 6, CAST(0x0000A21300000000 AS DateTime))
INSERT [dbo].[Problem] ([PKCaseProblem], [FKCase], [ProblemType], [ProblemRelatesTo], [ProblemDate]) VALUES (225, 550, N'374656e9-fc08-e111-8843-00155d00300b', 7, CAST(0x0000A00700000000 AS DateTime))
INSERT [dbo].[Problem] ([PKCaseProblem], [FKCase], [ProblemType], [ProblemRelatesTo], [ProblemDate]) VALUES (226, 551, N'374656e9-fc08-e111-8843-00155d00300b', 6, CAST(0x0000A00D00000000 AS DateTime))
INSERT [dbo].[Problem] ([PKCaseProblem], [FKCase], [ProblemType], [ProblemRelatesTo], [ProblemDate]) VALUES (229, 552, N'374656e9-fc08-e111-8843-00155d00300b', 6, CAST(0x0000A0CA00000000 AS DateTime))
INSERT [dbo].[Problem] ([PKCaseProblem], [FKCase], [ProblemType], [ProblemRelatesTo], [ProblemDate]) VALUES (243, 554, N'7b0063ef-fc08-e111-8843-00155d00300b', 6, CAST(0x00009FA200000000 AS DateTime))
INSERT [dbo].[Problem] ([PKCaseProblem], [FKCase], [ProblemType], [ProblemRelatesTo], [ProblemDate]) VALUES (244, 554, N'bec88efb-fc08-e111-8843-00155d00300b', 6, CAST(0x00009FA200000000 AS DateTime))
INSERT [dbo].[Problem] ([PKCaseProblem], [FKCase], [ProblemType], [ProblemRelatesTo], [ProblemDate]) VALUES (361, 766, N'374656e9-fc08-e111-8843-00155d00300b', 6, CAST(0x0000A29800000000 AS DateTime))
SET IDENTITY_INSERT [dbo].[Problem] OFF
GO
CREATE TABLE [dbo].[Case](
[PKCase] [int] NOT NULL,
[CaseNumber] [nvarchar](50) NOT NULL,
[CaseOpenDate] [date] NOT NULL,
[Company] [nvarchar](50) NOT NULL,
[Site] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Case] ([PKCase], [CaseNumber], [CaseOpenDate], [Company], [Site]) VALUES (541, N'CASE1', CAST(0xC8370B00 AS Date), N'Company1', N'Site1')
INSERT [dbo].[Case] ([PKCase], [CaseNumber], [CaseOpenDate], [Company], [Site]) VALUES (547, N'CASE2', CAST(0xB6370B00 AS Date), N'Company2', N'Site2')
INSERT [dbo].[Case] ([PKCase], [CaseNumber], [CaseOpenDate], [Company], [Site]) VALUES (547, N'CASE2', CAST(0xB6370B00 AS Date), N'Company2', N'Site2')
INSERT [dbo].[Case] ([PKCase], [CaseNumber], [CaseOpenDate], [Company], [Site]) VALUES (548, N'CASE3', CAST(0x6E370B00 AS Date), N'Company2', N'Site2')
INSERT [dbo].[Case] ([PKCase], [CaseNumber], [CaseOpenDate], [Company], [Site]) VALUES (550, N'CASE4', CAST(0x62350B00 AS Date), N'Company3', N'Site3')
INSERT [dbo].[Case] ([PKCase], [CaseNumber], [CaseOpenDate], [Company], [Site]) VALUES (551, N'CASE5', CAST(0x68350B00 AS Date), N'Company3', N'Site3')
INSERT [dbo].[Case] ([PKCase], [CaseNumber], [CaseOpenDate], [Company], [Site]) VALUES (552, N'CASE6', CAST(0x25360B00 AS Date), N'Company3', N'Site3')
INSERT [dbo].[Case] ([PKCase], [CaseNumber], [CaseOpenDate], [Company], [Site]) VALUES (554, N'CASE7', CAST(0xFD340B00 AS Date), N'Company3', N'Site3')
INSERT [dbo].[Case] ([PKCase], [CaseNumber], [CaseOpenDate], [Company], [Site]) VALUES (554, N'CASE7', CAST(0xFD340B00 AS Date), N'Company3', N'Site3')
INSERT [dbo].[Case] ([PKCase], [CaseNumber], [CaseOpenDate], [Company], [Site]) VALUES (766, N'CASE8', CAST(0xF3370B00 AS Date), N'Company4', N'Site4')
Is it normal to pull in Hierarchy tables into data warehouse staging area ?
Why would such as method be used instead of simply using a where clause ?
November 26, 2017 at 4:12 am
And your question is?
...
November 26, 2017 at 4:49 am
Updated post
November 27, 2017 at 7:00 am
My initial answer would be YES this is normal. This assumes that there is a business requirement to be able to report on aggregated values at different levels of the hierarchy.
Hierarchy is a perfectly valid - if difficult to manage - DWH pattern.
Employee->Team->Department->Division
location->town->county->state->country->continent
HOWEVER looking at your insert statements, these are not hierarchical but are different dimensions. If this were hierarchical then ServiceType would likely be a 1:N of ProplemType - for constraint purposes to prevent dumb data, and the FK on service would be FKProblem, not FKCase.
What you will need to be conscious of is that every case would need to be fully aggregated in the two dimensions or you might get a discrepancy depending on which dimension you use for your reports.
With that design consideration in mind, The request as stated does not make sense because you can't find orphaned service records based on non existence of a problem record. Do you want to look again at the design and let us know whether the database design or the problem statement is incorrect.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply