Post reply

Fact table for each KPI

  • February 2, 2014 at 12:08 pm

    #303974

    Still new to BI design methologie.

    When im building my facts tables is it generally accepted to to have one fact table for each business processes and have quantitative or aggregated columns for each KPI you want to track within the fact table. Or should you have separate fact tables for each KPI

    or have i got it completely wrong 🙁

    thanks guys

  • February 2, 2014 at 2:24 pm

    #1685358

    Typically you have one fact table per business process.

    I wouldn't store aggregated facts in the fact table (next to the low grain transactional facts).

    If you want to store aggregated facts, store them in a seperate table.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 4, 2014 at 11:32 am

    #1685961

    You don't give enough information to give exact advice. However, I don't see the words "grain" or "level of granularity" in your question. A fact table is an aspect of the business which can represent all items at that grain. Chances are aggregations would belong at a different level, but some aggregations at a lower level could belong in a fact table that is not an aggregation at a higher level. KPIs might not even belong in a fact table per se. For example, if I'm measuring how many jobs are completed relative to a forecasted amount, there's not a number I can simply put in a fact table, but in the cube I would have an MDX formula to do the count of jobs against the forecasted amount (which would be a number in the forecast fact table) to produce a result.

    As you say, you are at the beginning. If you haven't read Ralph Kimball's books on data warehouse design, please do so. You will find them invaluable.

  • February 4, 2014 at 12:57 pm

    #1685989

    RonKyle (2/4/2014)

    As you say, you are at the beginning. If you haven't read Ralph Kimball's books on data warehouse design, please do so. You will find them invaluable.

    And after you finish Kimball, read Star Schema The Complete Reference. You won't regret it.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 5, 2014 at 2:13 am

    #1686114

    Thanks guys, yes im currently working my way through the kimbal toolkit books ill have a look at the Star Schema The Complete Reference also.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply