3

I have a DW dimensional model as expected, with facts and dimensions and a Date table. This date table goes from 2015 to 2050 with two additional extreme values (1900-01-01 and 9999-12-31).

From this DW, a Tabular model is created which will serve as connection for Power BI, and as Power BI Time Intelligence functions require that the dates are continuous, both extreme elements are eliminated from the importing.

However, I can also create the table from a DAX function, as the number of rows is fairly low and it will be a table with a couple columns.

Which approach would be more time efficient when processing the tabular model? Importing the full table with the restrictions or executing the DAX query to create the calculated table?

Thanks for any information.

Jesus Rincon
  • 136
  • 9

1 Answers1

5

Whenever possible, pre-calculate attributes in a data warehouse and import them into a Tabular model. It's significantly more efficient. The key reason import is more efficient than a calculation has to do with the storage optimization - imports are optimized while calculated columns are not. Calculated columns can also substantially increase data refresh time.

It might be an insignificant difference for the Calendar table (it's small), but it's just a good habit to develop to be consistent. In general, avoid calculated columns in Tabular (and Power BI) - they should be only used during a prototyping process; once you figured out what you want, move it to the database. Calculated columns and tables are useful for people who don't have effective access to the data warehouse. If you do, don't use them (in my 5 years of modeling, I am yet to have a single calculated column in my models).

RADO
  • 7,733
  • 3
  • 19
  • 33