8 April 2021

Different Ways to Implement Power BI

Different Ways to Implement Power BI

Microsoft Power BI is a business intelligence platform that provides functionality and tools for aggregating, analyzing, visualizing, and sharing data. Power BI’s user interface is amicable for users familiar with Excel. This Blog describes the different ways of Power BI Implementation that supports Direct Query, import data, Live Connection, and Mix/Hybrid mode to develop a solution.

Supported Data Source Metric

Power BI supports various data sources. Power BI organizes data types in the following categories

  • All
  • File
  • Database
  • Power Platform
  • Azure
  • Online Services
  • Others

The ‘All’ category includes all data connection types of all sorts. With Power BI Desktop, you can connect to data from many different sources. For a complete list of available data sources, see Power BI data sources.

Power BI Solutions

There are three types of architectures one can select to implement a Power BI solution.

  1. On-premises Power BI implementation
  2. On-Cloud Power BI Implementation
  3. Hybrid Model

All architectures above can have different types of connection to pull the data into the reports, such as:

  • Power BI Live connection
  • Power BI Import Mode
  • Power BI Direct-Query Mode and
  • Power BI Hybrid Mode.

Power BI Live Connection

Live Connection mode connects to an existing data warehouse model developed using SQL Server Analysis Services. There are two types of data warehouse models available, Multidimensional and Tabular models. The advantage of using a Live connection to the SSAS data warehouse model is fast data retrieval, and data does not need to be embedded or stored within the power BI report file.

Power BI Live Connection

Pros:

  1. It’s delivered a single version of the truth.
  2. It allows you to connect with the whole model and sub-set of the model like you can create a perspective that contains only the sales department table and field out of sales, finance, HR, and inventory department table detail.
  3. Dashboard tiles, when based on DirectQuery models, can update automatically as frequently as every 15 minutes.
  4. Users can add measures using DAX in the Tabular Model.
  5. The user’s identity is passed through to role-level security permission.

Cons:

  1. You can only connect with a single data source or data warehouse.
  2. Users can’t change the data type of existing dimension and measure, define the relationship, and not create Roles into Power BI Desktop directly.
  3. The user can’t be able to add measures in a multidimensional model.
  4. If any cell-level security is enabled in a cube, you cannot connect that cell in the Power BI report.
  5. Specific cube-calculated members on user hierarchies are not exposed.
  6. The live connection mode is available in a few data sources, i.e., Power BI dataset, Azure Analysis Services, and SQL Server Analysis Service.

Power BI Import Mode

Import mode is the default mode when creating a new Power BI Desktop solution. Import Data Connectivity mode imports data into the Power BI cache, which delivers high-speed performance. We highly recommend using an Import connection when the data size is less than 1 GB, and the data is not continually changing. With Import connection, you can take full advantage of the high-performance query engine. Using this model, you can use Q&A and quick insight reporting features for better visuals.

Imported data is always stored on a disk. When queried or refreshed, the data must be fully loaded into the memory of the Power BI capacity. Once in memory, import models can then achieve speedy query results. There is no concept like the import model being partially loaded into memory.

Import mode requires sufficient memory to load the model when it’s queried or refreshed.

Upon publishing the report as a .pbix file to the Power BI service, a dataset is created and uploaded to the Power BI service. The imported data is included with that dataset.

It’s then possible to schedule a refresh of that data, for example, to reimport the data every day. Depending upon the original data source’s location, it might be necessary to configure an on-premises data gateway.

Pros:

  1. Import mode can import data from multiple data sources.
  2. Built-in time intelligence capabilities are available for the date column, i.e., year, month, day, etc.
  3. Import mode supports all Power BI data sources.
  4. Import mode supports all DAX functions and calculated tables.
  5. Import mode supports Q & A and Quick Insights reporting features.
  6. Power BI can achieve incremental refresh to avoid truncating and reloading entire tables when the dataset is hosted in a premium capacities workspace.

Cons:

  1. While using import mode, it is not possible to change data connectivity mode from import to DirectQuery.
  2. Maximum 8 schedule refreshes are available per day.
  3. Import mode maximum data set limit is 1 GB; hence it can’t be used with a large dataset.
  4. A complete refresh will remove all data from all tables and reload it from the data source. This operation can be expensive in terms of time and resources for the Power BI service and the data source.

Power BI Direct-Query Mode

Direct-Query mode is an alternative to Import mode DirectQuery mode is used to build Real-time or near real-time BI solutions from a single data source when the data is frequently changing, and the Data volume is enormous. No data will be imported into Power BI. DirectQuery mode, only stores the metadata of the dataset.

Power BI Direct-Query Mode

Models developed in DirectQuery mode don’t import data. Instead, they consist only of metadata defining the model structure. When the model is queried, native queries are used to retrieve data from the underlying data source.

DirectQuery mode requires minimal memory to load the model when it’s queried.

Upon publishing the report to the Power BI service, it will again result in a Power BI service dataset, the same as for import. However, no data is included with that dataset.

Currently, Spark and Azure SQL DW data sources have DirectQuery sources available in Power BI Service.

Pros:

  1. Import mode maximum size limits do not apply on DirectQuery mode.
  2. Models don’t require a refresh.
  3. DirectQuery model enables reporting over large datasets.
  4. Report users will see the latest data when interacting with report filters and slicers. 
  5. Report users can refresh the entire report to retrieve current data.
  6. Real-time reports can be developed by using the Automatic page refresh feature.
  7. Dashboard tiles, when based on DirectQuery models, can update automatically as frequently as every 15 minutes.
  8. While using DirectQuery mode, it is possible to change data connectivity mode from DirectQuery to import.

Cons:

  1. All tables must come from a single database.
  2. Relationship filtering is limited to a single direction rather than both directions.
  3. Calculated tables and calculated columns that reference a DirectQuery table from a data source with Single Sign-on (SSO) authentication are not supported in the Power BI Service.
  4. Time intelligence capabilities are not available in DirectQuery. For example, the date hierarchy of date columns (year, quarter, month, day, so on) is not supported in DirectQuery mode.
  5. There is a one-million-row limit for returning data when using DirectQuery. The limit does not affect aggregations or calculations used to create the dataset returned using DirectQuery, only the rows returned. For example, you can aggregate 10 million rows with your query that runs on the data source and accurately produce the aggregation results to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. If more than 1 million rows are returned from DirectQuery, Power BI returns an error.
  6. Quick Insight and Q & A feature is not supported.

Power BI Hybrid Mode

A model that combines data from more than one DirectQuery source or combines DirectQuery with import data is called a Hybrid model.

The hybrid model can mix Import and Direct-Query modes or integrate multiple Direct-Query data sources. Models developed in Hybrid mode support configuring the storage mode for each model table. The table storage mode can be configured as Import, DirectQuery, or Dual.

A table configured as Dual storage mode is both Import and DirectQuery, and this setting allows the Power BI service to determine the most efficient way to use it on a query-by-query basis.

Data modelers who develop Hybrid models are likely to configure dimension-type tables in Import or Dual storage mode and fact-type tables in a Direct-Query way.

SAP HANA, SAP Business Warehouse, SQL Server Analysis Services, Power BI datasets, and Azure Analysis Services can’t be used with Hybrid models. When you connect to these sources using DirectQuery, you can’t connect to another DirectQuery source or combine it with import data.

Power BI Hybrid Mode

Pros:

  1. Calculated tables can be created into a Hybrid model.
  2. Mix modes in a single model to leverage the benefits of selecting each method on a table basis.
  3. The hybrid model can combine more than one DirectQuery Model.

Cons:

  1. SQL, Oracle, and Teradata data sources only support incremental refresh.
  2. A calculated column of a DirectQuery Table can’t refer to another table, i.e., the calculated column on an import table can refer to other tables, but a calculated column on a DirectQuery table can still refer only to columns on the same table.
  3. Q & A and quick insight reporting features are not available on the DirectQuery table; hence it is not working on the whole Hybrid model.
  4. The relationship between different mode (import, DirectQuery or Dual) tables must have a many-to-many relationship.
  5. Due to the import and DirectQuery model combination, performance may be slow.
  6. Data from one source may be sent to a different source to perform filtering; hence, administrators could see this data.
  7. Hybrid Models cannot be combined with live connection mode, i.e., SAP HANA, SAP Business Warehouse, SQL Server Analysis Services, Power BI datasets, and Azure Analysis Services.
Back to Index