none
How would my fact and dim tables look like in this example? RRS feed

  • Question

  • 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

    Wednesday, April 17, 2013 5:16 PM

Answers

  • If the level of grain is the same for all KPIs I see no reason to have separate fact tables.
    • Marked as answer by Eileen Zhao Thursday, April 25, 2013 5:59 AM
    Wednesday, April 17, 2013 6:58 PM