Post reply

Customer as Fact Table

  • March 6, 2012 at 11:58 am

    #252709

    My business is fairly standard in that we have customers that place orders. Right now I have Customer as a dimension but it seems like it could be both a dimension and a fact table. For example, my sales people want to know how many customers belong to each sales region. All that data comes from the Customer dimension... if people are constantly looking up numbers based on data in a single dimension, is that a sign that something is wrong with the schema? I guess I'm having trouble understanding how to know when something should be a fact vs a dimension.

  • March 6, 2012 at 12:14 pm

    #1455502

    take a look here....a relatively simple starter

    http://msdn.microsoft.com/en-us/library/ms175669.aspx

    hope it helps

    kind regards

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • March 6, 2012 at 2:14 pm

    #1455580

    I'm not sure that MSDN link really answers your question. If you're referring to the actual tables in a SQL Server database then no it doesn't make sense to make the Customer a fact table.

    In an extreme case, if your Customer dimension was not slowly changing and you needed to track customers per sales region over time, then you might consider a fact snapshot kind of table, but in Analysis Services you should be able to use a dimension table as your measure group table.

    Here's some info from the Kimball group about determining Facts vs. Dimensions

    http://www.information-management.com/issues/2007_45/10000878-1.html

  • March 6, 2012 at 2:28 pm

    #1455593

    Thanks Chris, I had read that page before and it does not answer my question, but you have helped. In my case I think the Customer dimension should be a slowly-changing dimension but I'm not tracking changes right now, just repopulating the dimension nightly with the latest data. I need to change that though because we base our commissions off of

    I still don't know whether it's common for people to query a dimension by itself, e.g. to see how many customers are in a sales region, like I said.

  • March 6, 2012 at 3:01 pm

    #1455615

    it's not the kind of thing I would build in. If someone wants to know something that basic then they probably aren't in the cube (ymmv). I would support building reports off the cube for that kind of stuff. just my opinion.

    Using customer as a dimension allows them to filter by customer or region or whatever other characteristics are in your customer dimension.

    I think it's unlikely that there is enough value in measuring number of customers to turn that into a fact table but I'm not sure what else you store there??

  • March 7, 2012 at 12:07 am

    #1455758

    Such questions are easily answered with the most basic SQL statements. So a report would do the trick quite nicely.

    If you really want it in the cube, it's also possible. You can create a calculated measure that counts the members of the dimension:

    COUNT(existing [Customer].[Customer Code].[Customer Code])

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

  • March 7, 2012 at 12:08 am

    #1455759

    p.s.: that means you don't have to create an entire fact table on top of your dimension, just a calculated measure

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

  • March 7, 2012 at 10:07 am

    #1456122

    oh yeah, a calc! What a deliciously simple solution!

  • March 7, 2012 at 11:24 am

    #1456152

    Thanks all, that helps a lot. I could easily create a report for it but I'm trying to give my users a single place where they can get all they data they want to see.

  • March 7, 2012 at 2:11 pm

    #1456247

    Alex Bransky (3/7/2012)

    Thanks all, that helps a lot. I could easily create a report for it but I'm trying to give my users a single place where they can get all they data they want to see.

    Then the calculated measure is the way to go.

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

  • March 11, 2012 at 8:02 am

    #1457581

    Assuming you have some kind of location dimension, you can create a reference dimension that would do this. I have, for example, a location dimension with region, state, county, zip and a point of sale dimension that has a zip code field. With that, I was able to create a POS Location dimension referencing the POS to the location dimension on the zip. That dimension has all the attributes and hierarchies of the location table.

Viewing 11 posts - 1 through 10 (of 10 total)

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