Connecting to a Tabular Model Using Power BI

[Anonymous]

In this blog, we are going to discuss a live connection using a tabular model in Power BI, when to use a tabular model as a source and what the limitations are when we use a tabular model as a source in Power BI.

Power BI has limitations on importing data that is a maximum size of 1 GB. If the data is more than 1 GB, we have two options: One is Direct Query, and another is a live connection.

But, still, again, when we use direct query, we have to deal with performance issues. So, a better choice is creating a tabular model and creating relationships in the model itself using require tables.

If you use a tabular model, you can improve the performance of your report. We have a roles option in the tabular model. Using those roles, we can add some security to our data model.

SO, let’s get started with the tabular model.

Open Visual Studio -> Create New project -> Analysis Services -> Select Tabular -> Analysis Services Tabular Project. -> Model -> Import from Data Sources -> Microsoft SQL Server -> Enter the Server Name and Database Name and click on Test connection to check the connection and Click on Next -> Service account user name and Password-> Select from list of tables and Views to choose data to import:

Select Tables or views which are required in your model and click on Finish.

Now you have a model with all tables, and you can create a relationship between them by clicking on diagram.

Note: We have 3 relationships in the SSAS model. One is one-to-one, another one is one-to-many and the third one is many-to-many.

So, we are all set to deploy our Cube on the server to access it from Power BI.

Go to project, right-click on it and Click on “Process all” and then click on solution, right-click on Project and then click on Build and, once it gives you a success message, click on Deploy Model.

Note:

You cannot have a measure table in Power BI, so you have to manage all measures inside the folder using the “Display Folder” property.

You can check your deployed model using SSMS and you can add roles from there, also.

Let’s connect to the model using Power BI.

Open Power BI Desktop -> Click on Get Data -> Select Azure Analysis Services -> Enter the server and Select Model.

This is the first window you will see when you connect to Model. As you can see, there is only a report tab and Create new column, and the manage relationship option is disabled.

SO, all the things related to Column and relationship have to be managed at the model side only.

Above are some limitations using a tabular model as a source.

Now, we will check some tricky reports using a tabular model.

If you are familiar with SSRS you know about report criteria where we display all selected values inside a parameter.

The same thing can be done in Power BI using the below DAX.

Group Name Display = if(ISFILTERED('Sub Group'[Parent Group Name]),CONCATENATEX(ALLSELECTED('Sub Group'[Parent Group Name]),'Sub Group'[Parent Group Name],","),"All")

In the above DAX, we are displaying comma-separated slicer selected values.

I will take one example which is quite tricky and simple, and I have seen many questions related to this in the community.

So, the requirement is that a user wants to select a Date Type and date, and the report will show data according to the selected date and type.

(We have multiple dates from multiple tables, such as invoice date, received date, submit date.)

This is tricky because you can’t have an option to create a table or columns in Power BI when the source is a tabular model.

So, the solution is this: First, you need to create one date table in a model which is not connected with any another table in Tabular Model, and the date type parameter table with required values in the model.

As below.

Now, we can write a measure on this to get the required output.

ASO Totals = SWITCH(SELECTEDVALUE('Date Type'[Date Type]),"Paid Date",CALCULATE(([ASO Total]),filter(

'Claim Original Paid Date','Claim Original Paid Date'[Original Paid Date]>=min('Date'[CALENDAR_DATE]) && 'Claim Original Paid Date'[Original Paid Date]<=max('Date'[CALENDAR_DATE]))),"Service Date",CALCULATE(([ASO Total]),

filter('Claim Latest Service Date','Claim Latest Service Date'[Latest Service Date]>=min('Date'[CALENDAR_DATE]) && 'Claim Latest Service Date'[Latest Service Date]<=max('Date'[CALENDAR_DATE]))),

"Date Received",CALCULATE(([ASO Total]),filter('Claim Receive Date','Claim Receive Date'[Receive Date]>=min('Date'[CALENDAR_DATE]) && 'Claim Receive Date'[Receive Date]<=max('Date'[CALENDAR_DATE]))),

"Date Determined",CALCULATE(([ASO Total]),filter('Claim Latest Determination Date','Claim Latest Determination Date'[Latest Determination Date]>=min('Date'[CALENDAR_DATE]) && 'Claim Latest Determination Date'[Latest Determination Date]<=max('Date'[CALENDAR_DATE]))),

"Check Date",CALCULATE(([ASO Total]),FILTER(Payment,Payment[Issue Date]>=min('Date'[CALENDAR_DATE]) &&Payment[Issue Date]<=max('Date'[CALENDAR_DATE]))

 ),BLANK(),([ASO Total]))

This total will get changed with the selected Date Type and dates.

One more Scenario is this:

When we have only columns to show in table from different dimensions based on selected date type and dates.

Now, this date slicer will not work, as it is not connected to any table, so it will not filter our data.

To achieve such requirements, all we need to do is create a measure as below and add it to the visual level filter.

Date type filter =

SWITCH(SELECTEDVALUE('Parameter Claim Date Type'[Date Type]),"Date Paid",

SUMX('Fact Claim',IF(RELATED('Claim Original Paid Date'[Original Paid Date])>=MIN('Date'[CALENDAR_DATE]) && RELATED('Claim Original Paid Date'[Original Paid Date])<=MAX('Date'[CALENDAR_DATE]),1,0)),

"Date of Service",SUMX('Fact Claim',IF(RELATED('Claim Latest Service Date'[Latest Service Date])>=MIN('Date'[CALENDAR_DATE]) && RELATED('Claim Latest Service Date'[Latest Service Date])<=MAX('Date'[CALENDAR_DATE]),1,0)),

"Date Received",SUMX('Fact Claim',IF(RELATED('Claim Receive Date'[Receive Date])>=MIN('Date'[CALENDAR_DATE]) && RELATED('Claim Receive Date'[Receive Date])<=MAX('Date'[CALENDAR_DATE]),1,0)),

"Date Entered/Clean",SUMX('Fact Claim',IF(RELATED('Claim Original Entered Date'[Original Entered Date])>=MIN('Date'[CALENDAR_DATE]) && RELATED('Claim Original Entered Date'[Original Entered Date])<=MAX('Date'[CALENDAR_DATE]),1,0)),

"Date Last Processed",SUMX('Fact Claim',IF(RELATED('Claim Latest Processed Date'[Latest Processed Date])>=MIN('Date'[CALENDAR_DATE]) && RELATED('Claim Latest Processed Date'[Latest Processed Date])<=MAX('Date'[CALENDAR_DATE]),1,0)),

"Date Adjudicated",SUMX('Fact Claim',IF(RELATED('Claim Original Adjudication Date'[Original Adjudication Date])>=MIN('Date'[CALENDAR_DATE]) && RELATED('Claim Original Adjudication Date'[Original Adjudication Date])<=MAX('Date'[CALENDAR_DATE]),1,0)),

"Check Date",SUMX('Fact Claim',IF(RELATED(Payment[Issue Date])>=MIN('Date'[CALENDAR_DATE]) && RELATED(Payment[Issue Date])<=MAX('Date'[CALENDAR_DATE]),1,0)),

BLANK(),1

)

And add this measure to visual level filter and set it to “is not 0”.

I hope this will help you whenever you are working with tabular model sources.

Thanks & regards,

Pravin Wattamwar

www.linkedin.com/in/pravin-p-wattamwar