Thursday, April 25, 2013

[SQL Server Data Warehousing] How would my fact and dim tables look like in this example?


Please bear with me.


This is a very simplified example, but it's more or less the foundation.


We get hourly data for several cells, for example ORLFL-1-123-1. This cell is a combination of AREA-CAB-SITE-SECTOR.


Data analysts want to get several KPIs from all the data coming from these cells, but by AREA, CAB, SITE, SECTOR (not cell). And the smallest date range is daily (daily, weekly, monthly, etc).


Let's say the KPIs are KPI_ABC, KPI_NDO, KPI_DRT, KPI_QRR.


I have DimDate, DimSite, DimArea, DimCab, DimSector. I assume that's correct.


My question are the Fact tables. I was thinking of creating one Fact table for each KPI (FactKPI_ABC, FactKPI_NDO, etc). Each Fact table would have the total per site per day, plus the attributes for the site. For example the fields for FactKPI_ABC would be:



Datekey
Site
KPI_ABC
Area
Cab
Site
Sector

The problem I see (if it's a problem) is that all the tables would be exactly the same, since the only value that changes is the actual KPI total.


Is this the correct way to go? Any help is greatly appreciated.


Thanks.




VM



.

social.technet.microsoft.com/Forums

No comments:

Post a Comment

Search This Blog