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.
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.
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
First of all I would like to briefly explain Microsoft Power BI Designer. Then we’ll see how easy we can create a report using designer. I will use Adventure Works DW database as the source database.
Microsoft Power BI Designer is basically an integration of Power Query and Power View. Saying that the tool is still a preview version and it’s NOT actually a released product we’ll expect to see more features when it’s released. Some features like PowerPivot models expected to be available to the release version. At the moment PowerPivot models are not available in the designer, but, hopefully Microsoft will add it to the tool. So I added an idea into BI in SQL vNext as I believe it would be great to have PowerPivot functionalities included in the release version of the product. We’ll see what happens.
Let’s have a look at the tool. At the first look, I would like to say it’s an amazing tool integrating lots of awesome features all together with ease of use. It’s so fun to use the tool to create very effective and flashy reports in a short amount of time. First of all you need to download the designer from here. Install the designer and open it. I’ll use AdventureWorksDW2012 as the source database.
Open the Microsoft Power BI Designer Preview. If you want to get more familiar with the tool click on the videos on the startup screen.
·To connect to SQL Server click on “Get Data” or “New Source”