Search code examples
databasepowerbidata-modelingbusiness-intelligenceentity-relationship

Data model for a BI dashboard

I am hoping to get some help on the data model design of a dashboard I am building. I think it should be fairly straight forward, but I want to be sure I am doing this right.

Currently I have just three tables in a simple star schema layout:

  • Client table
  • Location table (Clients have multiple locations)
  • Metrics table for monthly billing

Data model visual

Now, I want to create some new fields based on the monthly data in the Billing_Metrics table. There are more but this should give an idea of what I am looking for. I would like to track these at both the client and location level:

  • Three, six, and twelve month averages for units sold and revenue.
  • Percentage change from previous month to current month for units sold and revenue.
  • Flags that would indicate things such as a client (or location) that had charges in the previous month, but has zero for the current month.

Ok, so the question is where the best place for these new fields in the data model would be and how should they be joined to the current structure? I can’t work it out in my head how that would look, especially when it needs to be at both the client and location level. Can anyone point me in the right direction?

Thank you!!!

Solution

  • You don't need to add these metrics to the table. Since you have "PowerBI" tag, I assume you plan to use Power BI as a reporting tool. Such tools allow you to calculate complex metrics dynamically (in PowerBI case, using language called "DAX" - Data Analysis Expressions). Such dynamic metrics automatically recalculate based on your report layouts and filters, including any combinations of locations, clients and dates. You can get a sense of the capabilities here:

    Measures in DAX

    In general, keywords you need are:

    • Star Schema
    • Dimensional Modeling
    • Ralph Kimball, Bill Inmon
    • PowerBI, DAX, calculated measures

    Note:

    • You are missing a "Calendar" table in your model.

    • If you are using PowerBI, remove primary key from your "Metrics" table. It will bloat the size of your model dramatically, while providing no analytical value.