Table Design Multiple Meaures and Levels

Question

Table Design Multiple Meaures and Levels

SQL_Kills

Hall of Fame

Points: 3154
More actions
December 28, 2022 at 11:56 pm

#4131275
Hi,
I after some ideas on how to create a Fact table that has multiple measures at different levels (levels is based on Level 1 the data type would be a whole number and Level 2 would be a decimal (for percentage calculation).
So at the first day of each month I will get different figures from different departments that will give me figures for the previous months activity, so this would consist measures for the following:
HR Department
- Count of Terminations
- Count of Hires
- % Turnover
Sales Department
- Total Revenue
- Total Margin
- Count of Orders
I am thinking to have the of following columns in my Fact Table
- DateKey (This will be the first of each month i.e. 01/11/2022, 01/12/2022)
- KpiId (This is a foreign Key to another Dimension Table which explains the name of KPI in question
- LevelId (This is a foreign key to another Dimension Key explaining what Level this relates i.e. 1 or 2, 1 would be for measures like Count of Terminations and 2 would be like % Turnover)
- Value (This will show the value of the realated measurement in question)
Or my Fact Table would show the DateKey and then a column for each of the Measures I will be receiving for each Department.
The issue I see with this is that the Table will be wide and I would have to keep altering the table to add new columns if there are new measures in the future.
What are everyones thoughts on this?
Thanks

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

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

What you're describing in a roundabout way is a tabular model using DAX. So there are two things you need to get your head around to do this: DAX and proper data modeling. So, ...

Step 1: Open up Ralph Kimball's book The Data Warehouse Toolkit, and start reading

What are you even modeling?

Your question is like "I'm going to build a database. What tables do I need?" Without context, it's an impossible question to answer.

Also read Rob Collie's book on DAX. And play along. (Playing along, and making mistakes, figuring out why DAX is doing what it's doing, etc is critical. So you can't just read the book and expect to understand it.) Oh, there are TONS of videos on YouTube on DAX.

This reply was modified 1 year, 10 months ago by pietlinden.
This reply was modified 1 year, 10 months ago by pietlinden.

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 2

I have got the book Ralph Kimball Margy Ross The Data Warehouse Toolkit 3rd Edition but cannot see anything on any of the chapters about this scenario.

I am producing a Executive Dashboard and the key Stakeholders want to figures at a low level granularity for different KPI's for each month. The measures are unrelated the only common field to each one is the Datakey where the Stakehoilders will have the ability to see at different months to see what the measures was at that point of time; I will be using Power BI to produce the report.

So my question would be the best way to create the Fact Table

This reply was modified 1 year, 10 months ago by SQL_Kills.

pietlinden SSC Guru Points: 63503 More actions · Answer 3

Are you trying to put all of this stuff into a single fact table? Don't. Hiring/Turnover is one table, Sales etc is another. They share a Time/Date dimension, so summarize along that. If I were you, I'd just build a small model in Excel, and then get the measures working. Then go from there. Why not make up some fake data and post it? (You did see the key word "Fake" there, right?)

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 4

KpiId (This is a foreign Key to another Dimension Table which explains the name of KPI in question

This is probably not right. Each metric should have its own column. If you get a new metric, you'll have to add a new column. That does not mean the approach is bad.