Friday, July 8, 2016

Testing DWH

I expect this post will be updated over time. Here I want to describe my test DB to handle business cases.

List of tables
Data within tables
Let's create some Cube-orientated DWH:
create view [dbo].[dim_Product] as   
select  
    [ID]  
    ,[Name]  
from [Product]

create view [dbo].[dim_Client] as  
select   
    [ID]  
    ,[Name]  
from [Client]
  
create view [dbo].[dim_Date] as  
select   
    [Day]  
    ,[DayName]  
    ,[Month]  
    ,[MonthName]  
    ,[Year]  
from [Date] 
 
create view [dbo].[dim_Invoice] as  
select   
    [ID]  
    ,[Number]     
from [Invoice_Header]
  
create view [dbo].[dim_Price] as  
select   
    [Price]  
from [Invoice_Detail]  

create view [dbo].[fact_Invoice] as  
select   
     [Invoice_Detail].[ID]  
     ,[Product]  
     ,[Date]  
     ,[Client]  
     ,[Price]  
     ,[Amount]  
     ,[Qty]  
from [Invoice_Detail]  
     left join [Invoice_Header]  
     on [Invoice_Detail].[ID] = [Invoice_Header].[ID]  
Now we can create relationships between dim and fact tables:




Here we have a cube:



No comments:

Post a Comment