SQL Server Analysis – Services Database

In this mode the model components and definitions such as tables, columns 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 :

1. Power BI dataset that has been published to Power BI Service
2. On-Premise SQL Server Analysis Services (Tabular or Multidimensional)
3. Azure Analysis Services 

In Live Connection mode, Model and Data pages are not available and  Report is the only visible tab: 

However, you can create new measures by using DAX in the report (see below):  

Live Connection to a Power BI Dataset

You may be surprised to see Power BI dataset in the above list. Power BI uses the same in-memory vertipaq engine as SSAS tabular. When a Power BI file – containing a model – is published to Power BI Service, a dataset will be created that is actually a cube database in the cloud and can be used by other Power BI files as a 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 are signed into on Power BI Desktop):

There are some benefits in separating your Power BI model and reports file:

1. Tables and calculations are centralised in one place without repetitions/duplicates of each of the report files. 
2. More governance in your team of developers. You are able to separate data modellers from those creating the reports and ensure that everyone uses the same mode version.
3. In the case of migrating datasource for example from Power BI dataset to SSAS cube, there will be less impact on the report 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 on Azure Analysis services. In Power BI Desktop SSAS datasource is under Databases or Azure categories based on the type of the host server:

Advantages

• Data models and calculations are centralised and shared between multiple Power BI reports.
• There is no limitation for the size of datasets. SSAS servers can be scaled up when required.
• There is more security – you can define users’ role and their access in SSAS.
• You are able to partition large tables – this keeps the query function at performance high and decreases the data refresh time.
• Use of source control tools (e.g. GIT and TFS) for instances where multiple developers are working on one model. 

Disadvantages

• The 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 are unable to add any new table (from a different datasource) to the reports. 

This article was written by Ali Sharifi from Agile Analytics – read more of his great tips and insights on his blog!