Current Architecture

The reports designed in Power BI are built upon a data set. All these these reports have their independent data sets.

A data set is a collection of data that you import or connect to. Power BI lets you connect to and import all sorts of data sets and bring all of it together in one place.

On the top of these data sets, applied steps are added which modify the data sets to create Power BI tables.
The Power BI tables are linked to each other using different cardinalities i.e. ‘One to One’ or ‘One to Many’. It is not feasible to have multiple joins between limited tables because the complex relationships could lead to “many to many relationships”.

In such a situation as suggested by Power BI, it is recommended that the table should load twice.

Loading complex tables twice would take a lot of time in loading and refreshing the tables.

To overcome this, it is recommended to use DAX formulas which duplicate the table without actually loading the table from the data set with all the applied steps again.

Integration of Reports

Earlier when two reports having a mutually exclusive relationship had different applied steps.

Now with the recent updates, both the reports have started to have more or less the same set of data sets. Maintaining the applied steps and data sets to keep uniformity between the reports has become difficult due to this.

It is therefore recommended that there should be a single set of data set and applied steps for all the reports and we can have a unified report for all the reports keeping all of them in different tabs.

This would require combining the applied steps, relationship and formula from both the reports but would ensure integrity of data.

Also like mentioned above, the problem of Many to Many relationships can be fixed by keeping a duplicated data set using DAX formulas to create duplicate tables for the Reports, which would be identical copies of the tables, having the same data but would have their own sets of relationship, in order to avoid Many to Many ambiguities.

Regular and Fast Refresh of Reports

In order to keep the data up to date, it is suggested that the reports be refreshed at regular intervals. In order to do that, software called ‘On Premise Data Gateway’ for Power BI is required. This software is free of cost.

With the recent optimizations the refresh time has reduced significantly by 60%. The optimizations are basically due to removal of unnecessary data sets from the data set as well as duplicating the tables by using DAX rather than loading the tables altogether again.

Read Next: Negative Filtering in Power BI