I’d like to thank you all for attending the webinar held on 30th September 2016. I talked about some amazing under cover aspects of Power BI Desktop model. In this session you learnt:
A while ago I wrote a blog post about Power BI Publisher for Excel. Today I want to explain some new features added to the publisher. In this post you learn how to analyse Power BI data in Excel. Using the new Power BI Publisher for Excel, not only can we pin an Excel range or chart to a Power BI dashboard directly from Excel, but also we are now able to easily connect to a Power BI service, select any group workspaces and analyse a desired report or dataset.
Requirements
Desktop versions of Microsoft Excel 2007 and later
Power BI Publisher for Excel add-in will be enabled by default after you install it, however, if you don’t see the “Power BI” tab in the ribbon in Excel you can enable it from File –> Options –> Add-ins –> COM Add-ins –> tick Microsoft Publisher for Excel.
Connect to and Analyse Power BI Data in Excel
Analyse Power BI Service Reports or Datasets in Excel (From Power BI Service)
Previously we could analyse Power BI data in Excel directly from Power BI service by:
Log in to Power BI Service
Clicking ellipsis button of a desired dataset and clicking “Analyse in Excel”
Clicking ellipsis button of a desired report and clicking “Analyse in Excel”
Doing either way, it downloads an “odc” file that could be opened in Excel.
Now you can analyse the data in Excel using pivot tables and pivot charts.
One of the most powerful features in Power BI and Excel is supporting geospatial visualisations. In Excel we can use Map visualisation in Power View, or use Power Map directly. In Power BI, as you know, there are two built-in visualisations supporting geographic coordinate data, Map and Filled map. They work beautifully if you have enough data supported by Bing Maps. But, there are some issues with Map visualisations in both Power BI and Excel. In this post I address some of the issues I faced myself and I’ll provide the solutions for the issues. As “Filled Map” and “Map” visualisations in Power BI are very similar my focus in this post would be on “Map” visualisation. My intention is not explaining Power View and Power Map that much so my focus in this article would be on Power BI more than the other two.
Requirements
To experiment everything I explain in this post you need to have:
The new SQL Server sample, WideWorldImportersDW (WWI). You can download it here
The latest version on Power BI Desktop (current version is 2.35.4399.381 64-bit (May 2016))
Select Fact.Sales and Dimension.City then load data
Map Issues In Power BI
Wrong Cities in Power BI
Expand the “Dimension City” table
Select “City” column then change its Data Category to City (Data Category is on “Modeling” tab from the ribbon)
Put a Map visual into the page
Put “City” on Location
Put “Total Excluding Tax” on Size
As you see sales distributed across different countries, but, this is not quiet right.
Put a slicer on the page then put “Country” on the slicer
Click “United States” to filter the Map
Oops! This is not quiet right. What happened is that Bing Map Engine gets confused with the city names so that it shows a city with the same name outside of the US, just like New Plymouth which a city in New Zealand, but, the New Plymouth we have in our data source is the New Plymouth from Idaho in the US.
Power 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.
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:
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
