Row-based Time Intelligence

For this blog, I want to share an interesting data modelling technique which you might consider when having performance problems with time intelligence calculations. This particular pattern works well on very large data-sets, especially for visuals that contain multiple measures and multiple periods.

The idea is to create a Time Intelligence table which contains blocks of rows per period. Some example time-periods might be:

  • Current Year
  • Prior Year
  • Last 2 Months
  • Last 3 Weeks
  • Last 28 Days
  • Year to Date

Here is a link to a simple PBIX file that demonstrates the technique.

You can have as many periods in the table as you like and the data in this table differs from a traditional calendar table. A traditional calendar table should only contain 1 row per day, have no gaps and only enough rows to cover the time range of your core data-set.

Where a single date can appear multiple times in a row-based Time Intelligence table. Once for every period included in the table.

A row-based Time Intelligence table doesn’t replace the traditional calendar table; rather, it complements it. For specific scenarios, this approach will produce the most optimal query plan in both Import or Direct Query mode.

In advanced scenarios, you may consider having multiple Time Intelligence tables. One may have periods that naturally align to calendar weeks, while another may hold periods that align nicely to calendar months. This type of scenario comes in to play more when you want to take advantage of Aggregation Awareness.

What makes row-based Time Intelligence fast?

DAX calculations that use a row-based Time Intelligence element have a much better chance of taking advantage of DAX Fusion. This optimization attempts to combine similar tasks into a single storage engine query. This is why queries using this technique often don’t increase much in terms of overall time when you add additional time periods.

How does it work?

The basic version of a row-based Time Intelligence table only has three columns, and a simple version of the pattern might look as follows:

The ‘Time Intelligence’ table can be related to a Calendar table. In this scenario, the relationship should be BiDirectional so that selections made to a field in the Time Intelligence table propagate through to your Fact table. In this case the Fact table is ‘Fact Sale’.

Typically, you should be cautious when configuring a relationship in your model to be Bi-directional – but in this case, it is okay. The other approach is to connect the ‘Time Intelligence’ table directly to your FACT table using a direct many-to-many relationship with the cross filter set in a single direction. There is little difference between either approach in terms of performance, or query plan (more about this below).

The data for the Time Intelligence table can be generated in DAX or your data-source. If you generate it in DAX, you can’t take full advantage of the table in composite models.

Here are some links with examples on how to generate a Time Intelligence table in either a SQL DB, or as a calculated table directly in your model.

The sample scripts only include patterns to generate about 6 periods but should give you the idea on how to easily add as many additional periods needed to suit your organisations reporting requirements.

The basic three-column Time Intelligence table should look something like this:

For simple periods, the value in the [Axis Date] column have a 1:1 relationship with the value in the [Date] column. However, for cumulative periods, there are multiple [Date] rows per [Axis Date] value.

The following image shows a version of this table filtered, so the [Period] is “Totals YTD” and the [Axis Date] is 4th of January, 2016. Note there are 4 rows and not a single row. When the [Axis Date] gets filtered to the 5th of January, there should be 5 rows.

Advantages

One advantage of this data modelling approach is for visuals that show a mixture of multiple periods combined with multiple measures. The visual below shows 5 different measures in the rows, while 6 different time periods are showing in the columns.

Another variation on this visual is a matrix showing a period comparison index measures (this year vs last year) for a range of periods. This case can be optimised further by pivoting data from rows to columns during the ETL process, which I plan to cover in an article soon.

Not all visuals in the report need to use fields from the row-based Time Intelligence table. Some visuals can use fields from the traditional calendar, while the more complex visuals can use this.

In both examples above, the [Date] field from the Time Intelligence table gets used in relationships. Relationships from the TI table can either be to a Calendar table (BiDi) or a Fact table (Direct M2M). The other two fields in the TI tables ([Period] and [Axis Date]) are the fields you would add to your visual.

Performance testing on very large data-sets shows this technique can be at least twice as fast in complex visuals like these examples, that use a mixture of periods and measures.

This approach also allows you to easily offer dynamic periods when combined with a slicer showing the various periods. An end-user can dynamically control which columns (or rows) are visible based on a slicer selection.

Disadvantages

An additional table with periods could be confusing for an end-user. Ideally, the report author who designs a report using this table does so in a way that reduces any confusion. Use fields from the traditional calendar table for visuals where it makes sense and only uses fields from the row-based Time Intelligence table for more complex.

The row-based Time Intelligence table does not perform point-in-time well. So, if you were interested in seeing how everything was from a specific point in the past, you may get some strange results. This disadvantage can probably get solved through additional columns in the row-based Time Intelligence table which I will cover in a future article.

This approach can be interesting getting to work with Aggregate Awareness. However, I have an article planned to describe how you can configure a model to row-based Time Intelligence tables with Agg. Awareness.

How fast is it?

Consider the following DAX query over a Direct Query mode to an Azure SQL DW running at 400DTU.

DEFINE 
MEASURE 'fact MyEvents'[Events] = SUM('fact MyEvents'[Quantity_ThisYear])
MEASURE 'fact MyEvents'[Events LY] = SUM('fact MyEvents'[Quantity_LastYear])
MEASURE 'fact MyEvents'[Events Diff] = [Events] - [Events LY]
MEASURE 'fact MyEvents'[Events Diff %] = 
	VAR ty = [Events]
	VAR ly = [Events LY]
	RETURN (ty-ly) / ty
EVALUATE
SUMMARIZECOLUMNS(
      'dim TimeIntelligence'[Period],
      "Events", 	'fact MyEvents'[Events],
      "Events_LY", 	'fact MyEvents'[Events LY],
      "Events_Diff", 	'fact MyEvents'[Events Diff],
      "Events_Diff__", 	'fact MyEvents'[Events Diff %]
    )

The ‘fact MyEvents’ table in this query, has 8.2 Billion rows and contains telemetry data covering 2 years to September 2019. The core [Events] metric is a simple SUM calculation over a column in the 8.2B row table. This example uses a pretty heavy query. There are no aggregations, or summary tables in play for this example.

For each row in the result, the engine needs to calculate a value using the appropriate measure for every column. In this case, the query took 29 seconds to complete. As you see from the captured server timings, only three storage engine tasks took place. Of the three SE Queries, only a single T-SQL Direct Query statement gets sent to the back-end source. The image below shows the SE Query that consumed most of the 29 seconds.

By comparison, the server timings for a measure based approach to generate the same values is shown below. The measure based query takes 87 seconds compared with 29 seconds on the same hardware. The lack of DAX fusion means more storage engine queries take place to generate the same result.

Row-based TI29 seconds
Measure Based TI87 seconds

The other approach

The other approach is to use a direct many-to-many relationship between the row-based Time Intelligence table and the fact table.

The specifics of the relationship are shown below. The Cardinality for this relationship (1) should be set to Many to Many, while the Cross filter direction (2) should be one way.

Out of interest, the same DAX query used to in the example over the 8.2 billion row table produced the following plan. The overall query time was faster at 25 seconds, compared with 29 seconds and there is 1 less storage engine query event – which reflects a reduced number of relationships involved in the design.

Summary

I’m not suggesting you use this approach for all models. Just consider the technique, especially if you are working with larger data-sets and you aren’t happy with the performance of your existing time intelligence calculations.

I have applied this design a few times this year on some very large models and have found it to provide the best performance, when it comes to Time Intelligence calculations.

As always, capture a baseline and use load testing to really flesh out how the model will perform with high concurrency.

5 8 votes
Article Rating

Leave a Reply

29 Comments
Inline Feedbacks
View all comments
Jeff Weir(d)
4 years ago

This is awesome, Phil. I’ve done something similar before, but used a disconnected table/slicer and TREATAS to change the grain…a trick I stole from @Kjonge as per his blog here:

https://www.kasperonbi.com/dynamically-switching-axis-on-visuals-with-power-bi/

I take it on large datasets you’d want to use a dedicated relationship like you are doing?

Michael van Vlimmeren
Michael van Vlimmeren
4 years ago

Hi Phil, i’m not able to download the sample files from your shared onedrive location. Can you send them to me bij email perhaps?

Bas
Bas
4 years ago

This is an awesome technique, but as you mentioned works only for time intelligence with <> as the comparison date. How would you solve for rather generic techniques such as YTD per month?

billanton
billanton
4 years ago

Great post! Especially the performance optimization aspect (reduced scans) which are particularly helpful in DQ models. Thanks for sharing

Tomek Wozniak
Tomek Wozniak
4 years ago

Hi Phil.
Did you try to cheat the system and model relatiobship from Fact to Time Intellegence as many to one? I am interested in performance of this solution on your dataset.
It should work in direct query and perform much better.

Phil
4 years ago
Reply to  Tomek Wozniak

Hi Tomek, nice to hear from you! I need to come visit 🙂 I didn’t cheat the system and used either a BiDi (from TI to Calendar) or M:M Direct between TI and FACT. All this was done in PBI Desktop

trackback

[…] Row-based Time Intelligence (@PhilSeamark) […]

Chris Jones
Chris Jones
4 years ago

I had been just adding calculated columns to my calendar table of the form IsLast28DaysInd – and living with the proliferation of measures (waiting to upgrade to calculation groups coming).

I like this better though, since it gives you dimensions to slice one and the same measure by. The proliferation of rows and the degraded user-comprehensibility of another date table is too bad, but the virtues of this approach are strong.

trackback
4 years ago

[…] of creative ways to incorporate Agg tables in your models. When these techniques get combined with row-based Time Intelligence and pivoting data for speed, you should be good to create models over multi-billion row tables and […]

Hans Peeters
Hans Peeters
4 years ago

Hi Phil, i’m not able to download the sample files from your shared onedrive location. Can you send them to me bij email perhaps?

Hans Peeters
Hans Peeters
4 years ago

Great post. Did the same for a KPI overview. Only nog from the Date dimension but from a product dimension. And the content is downloadable again.

Vinay Pugalia
3 years ago

Hello Phil,

Thanks a lot for this great explanation. However, I would like to understand your views on the below approaches –
1. Using the out-of-box date filter set to “Relative”
2. Just add a custom column to the Calendar – “DaysFarFromToday” of type INT. This will have value 0 (zero) for current date & 1 for yesterday, so on. And then measures can be built to handle any kind of relativity.

Please share your views on the same.

trackback

[…] octubre de 2019 Phil Seamark hablaba sobre ello en un post: https://dax.tips/2019/10/09/row-based-time-intelligence/ basándose en la dimensión período creada en M que no necesita de la tabla auxiliar y planteando […]

Hudson Vieira
Hudson Vieira
3 years ago

Hi Phil!! Tks for this awesome explanation,

I have a question on that topic.

in the “previous month” argument you used a today-30.

works well on any day of the month, except on the 30th. (01/30 -30 = day 0)
how can i resolve this issue? Any idea?

Missilent
Missilent
3 years ago

Absolutely wonderful – makes my financial reporting for YTD, MTD, LY ,FY ….really easy…Thanks Fiona

Pavan
Pavan
3 years ago

Sorry but I am new to this how about creating a period type for Fiscal Year, how would you do that ?

thanks
thanks
2 years ago

Can’t download . Can yo fix that ?

Evgueni Vassiliev
Evgueni Vassiliev
2 years ago

Hi Phil, thank you for the article. I was wondering: what if the user wants to select a time period? Say, they choose October 25, 2015 as their “Today” variable. Then the YTD, last 2 month, etc does not work.
Would it be possible to assign the “Today” date to a value from the filter that they select? Instead of “VAR Today = MAX(‘Fact Sale'[Delivery Date Key])” ?

Thanks!

KT
KT
2 years ago

Hi Phil, I tried using your time intelligence and ran into an issue when trying to change your Last 28 days for a last 7 days. When I have this — DATE(ThisYear , ThisMonth , ThisDay-7) — I get the error: “An Argument of Function Date has wrong data type or result is too small or too large”. Today is 07-Feb-2021. I am assuming because the Format of ThisDay = 7 from ThisDay = DAY(Today) I am also guessing I would run into the same problem on 28-Feb-2021 if I use the Last 28 days — DATE(ThisYear , ThisMonth ,… Read more »

LESLIE
LESLIE
2 years ago

Phil, this is AWESOME! mostly solved the problem I was having Except… it is filtering on some date in 2022. so the prior year is 2021… its May 2021 which should be the current year? I used your sample and just changed the VAR today info to my table name.

Help Please

mkokash
2 years ago

Thanks Phil for this helpful article, but what if I want to make WOW,MOM is it possible with this TI pattern? also faced another issue while doing last month calculation with the current pattern its showing Last month to date so modified it to be last month only

// Last Month
ADDCOLUMNS(
GENERATE(
SELECTCOLUMNS({“Last Month”},”Period”,[Value]) ,
GENERATESERIES(
DATE(ThisYear , ThisMonth -1, 1) ,
EOMONTH(DATE(ThisYear , ThisMonth -1, 1),0)

Thanks

Erik Leschnewski
Erik Leschnewski
1 year ago

Hi,

I had trouble with the case, that “ThisDay” in Date ( ThisYear, ThisMonth, ThisDay) is 0!
The Measure show an Error.
I solved this Issue by handling the error the following way:

// Last 14 Days
ADDCOLUMNS(
GENERATE(
SELECTCOLUMNS({“D – Last 14 Days”},”Period”,[Value]) ,
GENERATESERIES(
IF ( ThisDay – 14 0,
DATE(ThisYear , ThisMonth , ThisDay-14) ,
DATE(ThisYear , ThisMonth , 1) – 1
),
Today-1
)
),
“Axis Date”,[Value]
),

Rose
Rose
10 months ago

Hi Phil, Does this work if I want to bring in a time table as well (values from 0 to 23 as a separate table). Having hard time in calculating LY values using dateadd

Sami
8 months ago

Wow Phil! This is incredible! Saved me a tons of time and headache.

By the way is it possible to add the MAT [Moving Annual Total] in the slicer, i did it in the measure, but somehow struggling with date here :-(, any idea?