There are numerous ways to apply data transformations when creating a dashboard. One of the most important aspects is when to apply these transformations. This decision may impact not only the performance but also the complexity of the dashboard.
Let us review 4 different options and analyze which one is better under certain conditions.
|Option 1||Option 2||Option 3||Option 4|
|Language||DAX (used in PBI presentation layer)||M Language (used in PBI data layer)||SQL (query database from PBI)||SQL (View in database)|
|Transformation needs user input||Yes||No||No||No|
|Complexity||Low to high||Low||High||High|
|Sample||Average delivery time in user selected period||Time to deliver a product. (Delivery date – order date)||Time to deliver a product||Time to deliver a product|
This is the most frequent option. Transformations are applied at PBI presentation layer by using DAX in measures or calculated columns. These kinds of transformations are only recommended when they depend on some user input. Otherwise, since in previous steps the user input is unknown, every possible choice would need to be considered.
Note that these kinds of transformations are recalculated every time the dashboard UI changes and therefore may negatively influence the report performance.
For example, this approach would fit the need to calculate an average value according to a time period selected by the user.
On the other hand, this option is not recommended for transformations that do not depend on the user context. For example, in a fact table about product deliveries the delivery time could be calculated by subtracting the order date minus the delivery date.
Transformations are applied at the PBI data layer by using the M Language in the Power Query editor. This is a very powerful tool since it has a wide range of transformation tools available with little to no code. This option is ideal when the given transformation does not involve user interaction and neither of the two following options are available.
Note that these transformations are recalculated by the Power Query Engine every time the report’s dataset is refreshed.
For example, referring to the example of bad use of the first option, the column “DeliveryTime” can be added with a Power Query step. This is a better approach than using the first option but still could be better with the next two options.
Transformations are applied by using a custom SQL query within PowerBI when the data source is being imported into the report. This option is ideal when the given transformation does not involve user interaction and we cannot create views in the database.
Note that these queries are executed by the database engine itself every time the report’s dataset is refreshed.
For example, to achieve the same result as in the previous example, the following import configuration could be used.
Transformations are made directly in the data source by creating a table view that contains the data with the desired transformations. This is the best option as long as the transformations do not involve user interaction and we have enough rights in the database. Also, once the view is created, it can be reused in multiple dashboards.
Note that these queries are also executed by the database engine itself every time the view is accessed.
For example, to achieve the same result as in the previous example, a “delivery_time” view should be created with the corresponding grouping.
There is a key aspect to have in mind when deciding how or when to apply data transformations, the user interaction. If the transformations depend on any user interaction, then it must be performed as in the first option. Otherwise, it must be performed as in the other three options.
Among these other options the fourth is the most preferable, but sometimes we do not have the required rights to create views in the database and therefore option three becomes useful. If option three is not available due to the type of data source, then the second option is the way to go.