In this mode the model components and definitions such as tables,column’s attributes and datatypes, relationships, hierarchies, calculated tables and calculated columns are implemented in a semantic SSAS model and Power BI is used just for report presentation. The measures can be created either in SSAS model or in Power BI Report.
Live connection is used when connecting to one of the data sources below :
- Power BI dataset that has been published to Power BI Service
- On-Premise SQL Server Analysis Services (Tabular or Multidimensional)
- Azure Analysis Services
In Live Connection mode, Model and Data pages are not available and Report is the only visible tab:
Whoever you can create new measures by using DAX in the report:
Live Connection to a Power BI dataset
You may have been surprised to see Power BI dataset in the above list. Power BI uses the same in-memory vertipaq engine as SSAS tabular and when a Power BI file which contains a model is published to Power BI Service, a dataset will be created that is actually a cube database in the cloud and it can be used by other Power BI files as datasource for reporting purposes:
To create this model, in Power BI Desktop and Get Data dialog select Power BI and then Power BI datasets:
Then you will see the list of datasets published to your Power BI tenancy based on the account you signed in Power BI Desktop:
There are some benefits in separating Power BI model and reports file:
- Tables and calculations are centralized in one place with no need to be repeated in each of the report files.
- More governance in a team of developer by separating data modeler from those who creating the reports. This also ensures everyone using the same version of the mode.
- In the case of migrating datasource for example from Power BI dataset to SSAS cube there will be not much impact on the reports files.
Live Connection to a SSAS database
SSAS databases are designed and developed in Visual Studio and SSDT(Sql Server Data Tools) and then are hosted on an on-premise SQL Server or Azure Analysis services. In Power BI Desktop SSAS datasource is under Databases or Azure categories based on the type of the host server:
- Data model and calculations are centralised and shared between multiple Power BI reports.
- There is no limitation for size of dataset. SSAS servers can be scaled up when required.
- There is more security by defining users’ role and access in SSAS.
- Partitioning large tables to keep the query performance high and decrease data refresh time.
- Using source control tools (e.g. GIT and TFS) when multiple developers working on one model.
- There is an additional cost for using SSAS.
- Working with more tools such as SSDT, Sql Server Management Studio and Azure Portal.
- Only one live datasource can be used in the reports and you can not add any new table from another datasource to the reports.