Tag: DAX

BISM Tabular, Business Intelligence - BI, DAX, Power BI, Power BI Desktop

Connect to Power BI Desktop Model via its Local Port from Excel and SSMS

Power BI Desktop Excel SSMSPower BI Desktop is a fantastic report authoring tool. I have lots of experience working with Tableau as well and I can say, man, Power BI is growing very quickly. Lots of awesome ideas have been added to Power BI and a lot more is coming. But, It might be a question for some of you that is that possible to connect to a Power BI Desktop model from Excel, SQL Server Management Studio (SSMS) or SQL Server Profiler? The answer is yes, you can. But, how on earth someone should connect to a Power BI Desktop model from Excel, SSMS or SQL Server Profiler? Well, it could be useful for the following scenarios:

  • Connecting to the model using SQL Server Profiler for performance tuning, monitoring and so forth
  • Again, if you have some performance issues you might need to connect to the model from SSMS
  • You have a complex model and it’s hard for you understand it, but, you are a great Excel developer, so you can connect to Power BI Desktop model from Excel so you can use reach features available in Excel like named sets
  • Just for curiosity! You are curious about writing MDX codes over an existing model, you want to see how your model look like in Excel and so forth

In this article I show you how to connect to Power BI Desktop model regardless of any use case scenarios. So for whatever reason you’d like to connect to a Power BI Desktop model this post will help you achieve your goal.

How it works

Power BI Desktop uses xVelocity technology and loads data into memory. It uses a local instance of SQL Server Analysis Services (SSAS). It does the job by running msmdsrv.exe file which can be found in “bin” folder under your Power BI Desktop installation folder which is normally under you Program Files. The msmdsrv.exe is indeed the SSAS service file. So even if you haven’t installed SSAS on your machine Power BI Desktop runs msmdsrv.exe. When Power BI Desktop runs msmdsrv.exe it creates a local instance of SSAS. This local SSAS instance uses a random port number so it would be valid until Power BI Desktop is not closed or the msmdsrv.exe is not killed from Task Manager.

Find msmdsrv.exe in Power BI Desktop Folder

So, we have a local instance of SSAS using a random port number. Therefore, we should be able to connect to the instance from Excel, SSMS or SQL Server Profiler only if we know the port number.

Note: If you have installed an instance of SSAS on your machine you can find msmdsrv.exe under “\OLAP\bin” folder from SQL Server installation path:

%ProgramFiles%\Microsoft SQL Server\msasXX.INSTANCE_NAME\OLAP\bin

which XX is your version of SQL Server. So XX would be 10, for SQL Server 2008R2, could be 11 for SQL Server 2012 and so on. The difference between the local msmdsrv.exe file located in your Power BI Desktop\bin folder with the other one you can find under your SQL Server installation folder is that the one which Power BI Desktop runs is a console programme while the other one is a Windows service programme.

How to find Power BI Desktop local port?

There are various methods you can obtain the port number. In this post I explain three of them.

  • Finding Power BI Desktop local port using Windows Command Prompt (CMD)
  • Using DAX Studio
  • Finding local port number from Power BI Desktop temp directory

Continue reading “Connect to Power BI Desktop Model via its Local Port from Excel and SSMS” →

DAX, Power BI, Power BI Desktop

Role Playing Dimensions in Power BI

In this post I want to explain how to handle role playing dimensions in Power BI. I wrote an article awhile ago regarding role playing dimensions in SSAS Tabular which is valid for Power BI Desktop.

To recap, in the role playing dimensions in SSAS Tabular article I explained three different solutions:

  1. Importing role playing dimensions several times into the model
  2. Creating database views in the source side (in case your source is a from of RDBMS like SQL Server, Oracle etc…) then import the data into the model
  3. Keep the inactive relationships in the model and create several measures to take care of different roles using USERELATIONSHIP functions in DAX

In this post I show you alternative ways for the first two solutions to handle role playing dimensions without importing data several times into the Power BI model. You also don’t have to create database views on your source database. I show you how to manage this in both DirectQuery and Import modes when connecting Power BI Desktop to a SQL Server database. I explain the third option in another post.

I used AdventureWorksDW, but, you can use any other versions of AdventureWorksDW database or you can mimic the process to your own model.

Note: If you are designing a star schema for your data warehouse you can easily create a Date dimension as explained here.

The idea is to manage role playing dimensions in Power BI Desktop itself in the easiest way possible.

Role Playing Dimensions in Import Mode

  • Open Power BI Desktop
  • Get data
  • Select “SQL Server”
  • Enter the server and database names then click OK

Power BI SQL Server Connection

  • Select DimDate and FactInternetSales from the list then click “Load”
  • “Import” mode is selected by default. Click OK

Power BI Connection Settings

Continue reading “Role Playing Dimensions in Power BI” →

Data Warehousing - DW, DAX, Power BI, Power BI Desktop, Power Pivot

Side-by-side Role-Playing Dimensions In Power BI

Role-playing dimension is one those concepts that is discussed a lot from time to time. I also posted an article about implementing role-playing dimensions in Tabular models.

To recap, in the role playing dimensions in SSAS Tabular article I explained three different solutions:

  1. Importing role playing dimensions several times into the model
  2. Creating database views in the source side (in case your source is a from of RDBMS like SQL Server, Oracle etc…) then import the data into the model
  3. Keep the inactive relationships in the model and create several measures to take care of different roles using USERELATIONSHIP functions in DAX

I this post I explain implementation of the third option above. In this scenario you need to create especial calculated measures based on the roles you have in a fact table. One the most common role-playing dimensions is Date dimension. Consider you have to show Internet Sales Amount by Order Date, Due Date and Ship Date in a single chart in your report. In this case, having 3 different date tables won’t help us to achieve the goal.

New to Power BI? Quickly learn about Data Visualisation in Power BI here.

Defining new Measures in Power BI Desktop

Basically, what I’m going to explain in this post is using inactive relationships between FactInternetSales table and the DimDate dimension by adding a new Calculated measure. In this case, we’ll be able to show Sales Amount by different roles, well, dates in this sample in a single chart.

Continue reading “Side-by-side Role-Playing Dimensions In Power BI” →

DAX, Power BI, Power BI Desktop, Power Pivot, Power Query, Power View

What is new for BI in Excel 2016

It’s been awhile that we are waiting for a sensible improvements in Microsoft self-service BI. The good news is that finally there will be some cool new features added to the next version of Excel which is Excel 2016. By some, I mean, well, there not a lot new BI features, but, some. Something is better than nothing, not too bad though!

Integrating BI features with Excel:

Power View and Power Map:

As you know, Power Pivot was integrated as a built-it feature to Excel 2013. Now I’m really happy that the same thing happened to Power View and Power Map. So you don’t need to install them separately. You can now turn these features on from:

File–> Options–> Advanced-> (scroll down the page) Data-> Enable Data Analysis Add-ins: Power Pivot, Power View, and Power Map

image

OR you can still turn them on from “COM Add-ins”:

Continue reading “What is new for BI in Excel 2016” →