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
skondolf
Regular Visitor

Time Intelligence with Monthly Data Model

Hello -

 

BI Newbie here and probably something very simple but I can't find a definitive answer so my apologies in advance ...

 

I'm working on my first BI project and have a requirement to provide sales information by salesrep.  the source data is at a month granularity i.e. sales by rep for Jan 2018, Feb 2018, Mar 2018, etc.  I have 3 years of data like this.  I also have a pre-built calendar table in our source environment that I'm planning on loading in - this table is keyed on a date field with 1 entry for each day starting back in 2010 all the way up through 2040.

 

My understanding when creating a relationship on a calendar table to a fact table is to make sure the key is a date field.

 

Since my sales table is monthly and my calendar table is daily my thought is to create a 'date' field in the sales table for the 1st of each month i.e. 01/01/2018, 02/01/2018, etc.  I can then join the 2 tables to take advantage of all the time intelligence functionality.

 

Is this the correct approach when dealing with monthly data?  Does it make more sense for the date value to be the LAST DAY of each month?  Or can I create a Year/Month field i.e. YYYYMM and join the 2 tables on that?

 

Again, this sounds simple but it seems like there's multiple ways this could be accomplished.

 

Thanks in advance.

2 ACCEPTED SOLUTIONS
Seward12533
Solution Sage
Solution Sage

Sounds like the right approach. 

View solution in original post

OwenAuger
Super User
Super User

Agree, I routinely create models with monthly transactional data, and simply follow a first-of-the-month or last-of-the-month date convention with dates in the fact table (can't see any reason to prefer one over the other), related to a standard daily calendar table. 

 

As long as reports don't filter below the month level, it works just fine with time intelligence functions.

 

There are methods to allocate monthly data to days (some ideas e.g. here) but there is no point in doing this if your underlying granularity is monthly.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Can you build a date table without the day grain...   and time intelligence functions still work?

No

skondolf
Regular Visitor

Hello -

 

BI Newbie here and probably something very simple but I can't find a definitive answer so my apologies in advance ...

 

I'm working on my first BI project and have a requirement to provide sales information by salesrep.  the source data is at a month granularity i.e. sales by rep for Jan 2018, Feb 2018, Mar 2018, etc.  I have 3 years of data like this.  I also have a pre-built calendar table in our source environment that I'm planning on loading in - this table is keyed on a date field with 1 entry for each day starting back in 2010 all the way up through 2040.

 

My understanding when creating a relationship on a calendar table to a fact table is to make sure the key is a date field.

 

Since my sales table is monthly and my calendar table is daily my thought is to create a 'date' field in the sales table for the 1st of each month i.e. 01/01/2018, 02/01/2018, etc.  I can then join the 2 tables to take advantage of all the time intelligence functionality.

 

Is this the correct approach when dealing with monthly data?  Does it make more sense for the date value to be the LAST DAY of each month?  Or can I create a Year/Month field i.e. YYYYMM and join the 2 tables on that?

 

Again, this sounds simple but it seems like there's multiple ways this could be accomplished.

 

Thanks in advance.

OwenAuger
Super User
Super User

Agree, I routinely create models with monthly transactional data, and simply follow a first-of-the-month or last-of-the-month date convention with dates in the fact table (can't see any reason to prefer one over the other), related to a standard daily calendar table. 

 

As long as reports don't filter below the month level, it works just fine with time intelligence functions.

 

There are methods to allocate monthly data to days (some ideas e.g. here) but there is no point in doing this if your underlying granularity is monthly.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

What about when you have a mixe of date detail and Period summary?

Hi @MondayApril 

Do you mean that your fact table contains values at both a date level and totals for periods, effectively double-counting?

I would generally say include one or the other, but not both (depending on reporting needs).

 

Alternatively, an aggregated fact table could be included separately from the detailed fact table (similar concept to aggregations but without necessarily using DirectQuery for the detailed table). Then measures could be written to reference the appropriate table.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Seward12533
Solution Sage
Solution Sage

Sounds like the right approach. 

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.