Pass Business Intelligence Virtual Chapter, Power BI, Power BI Desktop, Power BI Service, Presentations, Uncategorized

Webinar Materials: Power BI Under the Hood

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:

If you’ve missed the webinar you can watch it online here:

Download the Power Point presentation file here.:

[gview file=”https://www.biinsight.com/wp-content/uploads/2016/10/Pass-DW-BI^LJ-Power-BI-Under-The-Hood-with-Soheil-Bakhshi-Presentation-2.pptx”]

Here is the PDF version of presentation:

[gview file=”https://www.biinsight.com/wp-content/uploads/2016/10/Pass-DW-BI^LJ-Power-BI-Under-The-Hood-with-Soheil-Bakhshi-Presentation.pdf”]

DAX, Excel, Power BI, Power BI Desktop, Power Map, Power Pivot, Power Query, Power View, Self-Service BI, T-SQL

How to Overcome Map Related Issues in Power BI, Power View and Power Map

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))
Excel 2016 or Excel 2013

If you use Excel 2016, then you need to turn on Power View on.

Check this out if you want to learn more about BI features in Excel 2016.

Get Data in Power BI

Open Power BI Desktop
Get Data from SQL Server Database
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.

Continue reading “How to Overcome Map Related Issues in Power BI, Power View and Power Map” →

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 SSMS 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:

%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:

Importing role playing dimensions several times into the model
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
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

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

Continue reading “Role Playing Dimensions in Power BI” →

Tag: DAX

Webinar Materials: Power BI Under the Hood

Like this:

How to Overcome Map Related Issues in Power BI, Power View and Power Map

Requirements

Get Data in Power BI