I was mentoring a client recently trying to help them with some tips for using tabular. I couldn’t find all of the information I was looking for, so I decided to write all of them down so that next time I could just go to my blog for the information. There are a lot of things to consider so I broke them up into 3 parts. To make sure you get all of them, please subscribe to my blog. And now, here are some more tips for tabular.
Marking all Date tables as Date
Tabular models include a number of Time Intelligence functions which provide the ability to quickly and easily calculate things such as Rolling 12 months, Year-to-date calculation, and more. In order for this functionality to be possible, the table needs to be marked as date. In order for this feature to be available, there needs to be one contiguous date field in the table, meaning there should be no gaps in the date field. If there is one default missing date field which is many years from the start of the contiguous date values, the field needs to be removed or all of the dates after that date need to be added ensuring there are no gaps in the dates. You can choose to join on the date value or another key.
When there is a field where the date and time are both needed, the values should be separated so that there is both a date field and a time field. Having date time in two fields assists in the dictionary encoding as the date and time fields can be separately sorted into columns where the values are the same, decreasing the number of dictionary entries. To further improve compression, only include the seconds if absolutely necessary, as add decreasing the cardinality will increase compression.
All relationships between tables should be active relationships. If you have any dashed lines which connect portions of your model, these are inactive relationships and should be removed, unless you are writing DAX Code to use them. DAX code must be written to use inactive relationships and users often find it confusing. As a best practice for situations where there are multiple dates in the fact table, such as Order Date, Ship Date and Due Date, is to have 3 different date tables. The other date tables can be created by using a calculated table to create a second date table and join the other value to that date table. Step-by-step instructions for creating a calculated table are included in the Reference section1. Having multiple date tables is a best practice design for tabular models as it is a common for there to be multiple dates in one table.
Partitioning Tabular models does not improve query performance. In fact multiple partitions can actually make performance worse. Partitioning should be implemented to assist in the loading of the data. If you do not need to create a partition to make a load timeframe, then there is no reason to do it.
Data aficionado et SQL Raconteur