One of the vital highly effective options in Energy BI and Excel is supporting geospatial visualisations. In Excel we are able to use Map visualisation in Energy View, or use Energy Map immediately. In Energy BI, as you realize, there are two built-in visualisations supporting geographic coordinate information, Map and Crammed map. They work fantastically when you have sufficient information supported by Bing Maps. However, there are some points with Map visualisations in each Energy BI and Excel. On this submit I deal with a number of the points I confronted myself and I’ll present the options for the problems. As “Crammed Map” and “Map” visualisations in Energy BI are very comparable my focus on this submit can be on “Map” visualisation. My intention just isn’t explaining Energy View and Energy Map that a lot so my focus on this article can be on Energy BI greater than the opposite two.
To experiment all the things I clarify on this submit it is advisable have:
- The brand new SQL Server pattern, WideWorldImportersDW (WWI). You may obtain it right here
- The most recent model on Energy BI Desktop (present model is 2.35.4399.381 64-bit (Might 2016))
- Excel 2016 or Excel 2013
Should you use Excel 2016, then it is advisable activate Energy View on.
Test this out if you wish to study extra about BI options in Excel 2016.
- Open Energy BI Desktop
- Get Information from SQL Server Database
- Choose Truth.Gross sales and Dimension.Metropolis then load information
Improper Cities in Energy BI
- Increase the “Dimension Metropolis” desk
- Choose “Metropolis” column then change its Information Class to Metropolis (Information Class is on “Modeling” tab from the ribbon)
- Put a Map visible into the web page
- Put “Metropolis” on Location
- Put “Whole Excluding Tax” on Measurement
As you see gross sales distributed throughout totally different international locations, however, this isn’t quiet proper.
- Put a slicer on the web page then put “Nation” on the slicer
- Click on “United States” to filter the Map
Oops! This isn’t quiet proper. What occurred is that Bing Map Engine will get confused with town names in order that it exhibits a metropolis with the identical identify outdoors of the US, similar to New Plymouth which a metropolis in New Zealand, however, the New Plymouth we now have in our information supply is the New Plymouth from Idaho within the US.
Fixing the Improper Cities Difficulty In Energy BI
Answer 1: Concatenating Nation and Metropolis in Energy BI
We are able to simply overcome the problem by including a calculated column to the desk and concatenate “Nation” and “Metropolis” columns in order that the Bing Map engine finds the proper cities. We are able to do that is Energy Question or writing DAX expressions. I clarify DAX answer and depart the Energy Question state of affairs to you.
Nation, Metropolis = ‘Dimension Metropolis'[Country] & “, “ & ‘Dimension Metropolis'[City]
Click on the “Nation, Metropolis” column then from “Modeling” tab change its Information Class to “Metropolis”
Click on on the Map
Exchange “Metropolis” with “Nation, Metropolis” on Location
Hmm! It seems to be a lot better now. Change to “Information” view to see how the brand new column seems to be like.
Answer 2: Making ready Location in Energy BI
We are able to use “Location” column on the Map to get extra exact factors. Trying on the “Location” column we rapidly see that it comprises latitude and longitude info which is precisely what we’d like. We solely must take away “POINTS( “ and “)” from the string after which changing house with comma to get location information based mostly on latitude and longitude from the “Location” column.
Now you can click on Shut & Apply. However wait, can we do the entire above three steps in only one step?
The reply is sure we are able to. You may add a brand new column and add the expression beneath that takes care of the entire above three steps in only one single step:
=Textual content.Exchange(Textual content.Trim(Textual content.Take away([Location], {“A”..”Z”, “(“, “)”})), ” “, “,”)
- Now click on “Shut and Apply”
Oops! What’s improper now? The placement meant to point out extra exact factors on the Map, however, why it exhibits most of locations in Antarctica? Severely?
Right here is when the subsequent difficulty exhibits up.
OK, preserve studying to seek out out why Map exhibits improper information.
Energy Question Difficulty in Changing SQL Server “Geography” Information Sort to Energy Question Textual content
Let’s take a look on the entire course of that we went by way of to date to seek out out what the problem is. We obtained information from the Dimension.Metropolis desk from SQL Server, proper? Click on “Edit Queries” and take a look on the “Location” column once more.
Click on “Navigation” step from “Question Settings” pane to see what we had initially in “Location” column earlier than doing any transformations.
Let’s take a look on the Dimension.Metropolis as nicely and see what we actually have within the supply desk.
- Open SQL Server Administration Studio (SSMS) and connect with the SQL Server occasion that hosts the WideWorldImportersDW database
- Question Dimension.Metropolis
The “Location” column information kind in SQL Server is “geography” which implies we are able to simply get latitude and longitude from the situation column in SSMS. Now run the next question to get latitude and longitude from the Location column:
SELECT [City Key],
[WWI City ID],
Metropolis,
[Location],
[Location].Lat AS Latitude,
[Location].Lengthy AS Longitude
FROM Dimension.Metropolis
Word: Geography is a .NET Frequent Language Expression (CLR) information kind obtainable in SQL Server. Within the above question “.Lat” and “.Lengthy” are properties of geography occasion. The properties of geography are case delicate, so if you happen to kind “[Location].lat” slightly than “[Location].Lat” you’ll get the beneath error message:
“Couldn’t discover property or area ‘lat’ for kind ‘Microsoft.SqlServer.Sorts.SqlGeography’ in meeting ‘Microsoft.SqlServer.Sorts’.”
To study extra about geography information kind in SQL Server confer with the beneath hyperlinks:
OK, let’s take a look at each SQL question and Energy Question consequence side-by-side.
Properly, we uncovered the problem now. For some cause, Energy Question, erroneously transformed SQL Server geography information kind to textual content. The conversion sample needs to be “POINT (Latitude Longitude)” slightly than “POINT (Longitude Latitude)”. It seems to be like a bug. I already reported this as a bug to Energy BI workforce. Should you additionally wish to report it click on right here.
However, what we must always do till Energy BI workforce fixes the problem? Properly, we are able to overcome this in two methods.
We are able to break up “Location” column in Energy Question to 2 columns, Latitude and Longitude
We are able to use the SQL question we wrote beforehand and import the “Dimension.Metropolis” desk once more
Answer: Break up “Location” column to Latitude and Longitude
We already went greater than half of the best way within the earlier part once we reworked “location” column from “POINT (-78.651695 42.1083969)” to “-78.651695,42.1083969”. So we actually want to separate the “Location” column to Latitude and Longitude.
- Choose “Comma” as delimiter then click on OK
- Rename “Location.1” to “Longitude” and “Location.2” to “Latitude”
- Click on Shut and Apply
- As we break up “Location” column to 2 new columns, the Map breaks, click on “Repair This”
- Change information class for each “Latitude” and “Longitude” columns
- Set “Default Summarization” property of each “Latitude” and “Longitude” columns to “Do Not Summarise”
- Click on on Map
- Drag and drop each Latitude and Longitude columns on the Map
The difficulty is mounted.
Energy Question Difficulty with Supporting CLR kind ‘Microsoft.SqlServer.Sorts.SqlGeography’
This difficulty occurs while you wish to import information from a SQL question which queries a column of kind geography. Typically it is advisable get information from SQL Server by working SQL statements. For example, in our pattern database, have a look at the “Dimension.Metropolis” desk. The “Legitimate From” and “Legitimate To” columns signify Slowly Altering Dimension which implies not all of the rows within the Dimension.Metropolis are legitimate. A finest observe to maintain a Energy BI Mannequin dimension optimum is to not import the info you want. It additionally improves the efficiency of our mannequin. So I’m enthusiastic about importing simply legitimate rows from Dimension.Metropolis desk that are these rows with “Legitimate To” equal to “9999/12/31”.
Get Information from SQL Server Database
Enter “Server” and “Database” names
Click on “Superior” then kind the SQL assertion beneath then click on OK
SELECT *
FROM Dimension.Metropolis
WHERE 12 months([Valid To]) = 9999
It introduced “Error”, however, why? Click on “Edit” to see what’s improper with “Location”
Click on on a cell with error
We obtained “DataSource.Error: We don’t help CLR kind ‘Microsoft.SqlServer.Sorts.SqlGeography’” error. Hmm, it doesn’t look good. Properly, let’s transfer ahead and repair this difficulty as nicely.
Answer: Get “Latitude” and “Longitude” Properties from “Location” with T-SQL
We are able to simply repair this difficulty by modifying our T-SQL a wee. I defined that we are able to get “Latitude” and “Longitude” from geography information kind in SQL Server earlier on this article. We use the identical approach to repair this difficulty. All it is advisable do is to name “Lat” and “Lengthy” properties of “Location”. Therefore, the SQL assertion might be one thing like this:
SELECT [City Key],
[WWI City ID],
Metropolis,
[State Province],
Nation,
Continent,
[Sales Territory],
Area,
Subregion,
Location.Lat AS Latitude,
Location.Lengthy AS Longitude,
CONCAT(Location.Lat
, ‘, ‘
, Location.Lengthy
) AS Location,
[Latest Recorded Population],
[Valid From],
[Valid To],
[Lineage Key]
FROM Dimension.Metropolis
WHERE (YEAR([Valid To]) = 9999)
To exchange the outdated SQL assertion with the brand new one simply click on on the gear icon (
) on the appropriate aspect of “Supply” step from “Question Settings” pane.
Click on OK then click on “Shut & Apply” to get the proper outcomes with no errors. The one distinction within the consequence set is that we now have two extra columns displaying latitude and longitude. We concatenated latitude and longitude to help “Location” as nicely.
Within the subsequent part I deal with the identical points in Energy View and Energy Map.
There are numerous methods to arrange information for Energy View and Energy Map in Excel 2016.
Import information from SQL Server into Excel immediately
Import information from SQL Server into Energy Pivot mannequin immediately
Hook up with SQL Server, import and remodel information utilizing Energy Question, then load the outcomes to both Excel sheets or Energy Pivot information mannequin
To maintain this half so simple as attainable I clarify load information into Energy Pivot immediately.
Word: Should you use Excel 2013, don’t fear because it’s not that totally different from Excel 2016. You simply must import information utilizing Energy Pivot tab on the ribbon
Improper Cities in Energy View and Energy Map
The identical factor occurs in Energy View and Energy Map. Whenever you put “Metropolis” on the map it exhibits improper cities. It’s trivial as all of those instruments are utilizing Bing Map engine to visualise map information.
Here’s what we get in Energy View:
The screenshot beneath is from Energy Map:
Answer: Concatenating Nation and Metropolis in Energy Pivot
We have now concatenated Nation and Metropolis in Energy BI by writing a quite simple DAX expression. The precept applies to Energy Pivot.
Here’s what we get in Energy View and Energy Map after we add a brand new calculated column that concatenates Nation and Metropolis columns Energy Pivot.
Energy View outcomes:
Energy Map outcomes:
Energy Pivot Ignores the Columns with Geography Information Sort
Identical to what we confronted in Energy BI while you needed to import a SQL question outcomes into Energy BI, if we wish to do the identical in Energy Pivot we see that Energy Pivot merely ignores the “Location” column once we write the next question in Energy Pivot:
SELECT *
FROM Dimension.Metropolis
WHERE 12 months([Valid To]) = 9999
Here’s what we get in Energy Pivot:
As you see there isn’t any “Location” column.
Once more the answer is getting “Latitude” and “Longitude” properties from “Location” with T-SQL. So click on “Desk Properties” from “Design” tab from the ribbon in Energy Pivot and modify the SQL assertion as beneath:
If you’re working in a mission that entails with geospatial information and your supply database is SQL Server then take heed to incompatibility of Energy BI, Energy Pivot and Energy Question with SQL Server geography information kind. I’ll most likely get improper cities in Energy BI map visualisations, Energy View map or Energy Map visualisations if you don’t specify which metropolis precisely you need.
Dealing with the above points for the primary time with out understanding how one can repair them may very well be annoying and dear. So I hope Microsoft repair these form of points within the subsequent launch/updates of Energy BI and Excel 2016.
Should you confronted some other points it could be nice if you happen to share it with us. So why not writing your story within the feedback part beneath?
