Time Intelligence with Monthly Data Model

[skondolf]

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.

[Seward12533 · Accepted solution]

Sounds like the right approach. 

[Anonymous]

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

[Anonymous]

No

[skondolf]

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 · Accepted solution]

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.

[Anonymous]

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

[OwenAuger]

Hi @Anonymous 

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.

[Seward12533 · Accepted solution]

Sounds like the right approach.