Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dev-13
Helper I
Helper I

Data Modelling

  • Hi

 

I have a dataset that I would like to transform into a data model, and I would like some advice on its design. 

 

The dataset has totals by sales person by day of the week, and also a grand total for all days in the week. The only date available in the dataset is the week ending date. 

 

I want to split the data into facts and dims. I have a fact table set up that includes the totals by week ending, but I am not sure if it should really be by day of the week instead. 

 

Should I have one fact table that goes right down to day level that aggregates up, or should I have one that just shows the total for the week? 

 

The reporting requirement at the moment is the total by week which in turn will aggregate up to month, quarter, year etc.

 

If I was to have a fact table by day of the week, I don't have the dates for the days, but could probably work this out by the week ending.

 

Any advice would be great. Thank you.

 

1 ACCEPTED SOLUTION
vojtechsima
Memorable Member
Memorable Member

Hi, @Dev-13 ,
first of all create a Calendar in Power Query or DAX. I prefer power query, here's link:
https://www.youtube.com/watch?v=lDjKS3Ur3ks
Then you should probably also have a dimension for Sales people.

In general the lower level you can get (if you really need it) the better (so you have more control later, if requirements changes).
if you will never report by Day, just use the End Of week  day and connect it to the calendar.
From Calendar you can use the week, month quarter, year, etc.

 

 

View solution in original post

3 REPLIES 3
Dev-13
Helper I
Helper I

Hi Thank you for your reply. I think I should be doing it by day of the week then to allow for flexibility in the future.  How is it possible to seed the dates for the days of the week. I only have a date for the week ending which is a Sunday. Even if I link this into the calendar I won't be able to link in for every date, only the week ending date? Thanks 

Hi, @Dev-13 
Well based on your information, I thought this "The dataset has totals by sales person by day of the week" is something you could use to get daily data.

 
vojtechsima
Memorable Member
Memorable Member

Hi, @Dev-13 ,
first of all create a Calendar in Power Query or DAX. I prefer power query, here's link:
https://www.youtube.com/watch?v=lDjKS3Ur3ks
Then you should probably also have a dimension for Sales people.

In general the lower level you can get (if you really need it) the better (so you have more control later, if requirements changes).
if you will never report by Day, just use the End Of week  day and connect it to the calendar.
From Calendar you can use the week, month quarter, year, etc.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.