Power Bi With Large Data Quantities: Three Pragmatic Tips

Tip 1: Separate pbix file as data set

If the amount of data is so large that it takes a long time to upload the desktop file to the Power BI service, we recommend separating the data set from the report file. This means that the data connection is developed as usual in a pbix file, but without creating a report page in it. This is then the data set. It is published inside a workspace to the service, so that any number of further reports (this time only with report pages) can be added.

Separating development of report pages from that of data sets has the following advantages:

• The data set and reports can be edited at the same time.
• The report file becomes smaller and more stable.
• Uploading the report file no longer takes as long.
• Multiple report files can be added on the basis of the same data set, which can be useful for grouping during creation of a Power BI app.

Serving next for further explanation is a simple example of a data warehouse. It is based on an SQL database in Azure. Individual products comprise the smallest component in the Fact_Orders table.

Tip 2: Use of aggregated tables and the composite model

The idea here is as follows: In most cases, the majority of visuals do not at all use the smallest fact component (Product in our example), but aggregate the data (for example, at Customer and OrderDate). Detailed data at the product level are therefore not required at all in such cases. A table with aggregated data suffices instead. Created accordingly is an aggregated fact table which is often much smaller and can therefore be imported.

Still remaining here is the problem with visuals and filters, which still fall back on the product level (for example, for evaluations of sales according to product). In such cases, a fact table at the product level is unavoidable. However, the Direct Query mode is ideal for use with detailed fact tables of this kind.

Provided next is a brief guide to practical implementation. In our example, we aggregate the Product dimension, and group it according to the remaining dimensions. First, we create all queries as direct ones in the Power Query editor. The query for the fact table is then referenced and renamed (to Fact_Orders_Agg in this case), and grouping is performed according to the dimensions to be retained (see the diagram).

With the number of rows reduced by aggregation, you can now switch the table in the model view to import mode. Also connected in this process are the dimension tables.

Because the storage mode for each fact table is Direct Query or Import, the storage mode for the dimension tables must be set to Dual. Power BI thus automatically detects which storage mode is optimal for these tables: If data from the aggregated fact table are queried, the data from the dimension tables are used. However, if data at the product level are queried in a visual, for example (via the Fact_Orders table), the data from the Dim_Product table are also fetched via Direct Query.

For this logic to work, however, Power BI needs to be able to differentiate between the aggregation and detail tables.

As already evident in the message, the aggregation table is automatically hidden. This means that all visuals appear to be prepared on the basis of the detail table (Fact_Orders), while Power BI retrieves the data according to the logic mentioned above. Here is an example:

The upper diagram contains only data from the aggregation table, so that the saved information is obtained from Power BI. The lower diagram contains a Color field belonging to the Dim_Product table. These data are therefore retrieved via Direct Query.

‍

‍

Let’s Unlock the Full Potential of Your Data – Together!

Looking to become more data-driven, optimize processes, or leverage cutting-edge technologies? Our blog provides valuable insights – but the best way to tackle your specific challenges is through a direct conversation.

Let’s talk – our experts are just one click away!