Overcome Map Associated Points in Energy BI, Energy View and Energy Map

Some of the highly effective options in Energy BI and Excel is supporting geospatial visualisations. In Excel we will use Map visualisation in Energy View, or use Energy Map straight. In Energy BI, as , there are two built-in visualisations supporting geographic coordinate information, Map and Crammed map. They work superbly you probably 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 handle 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 related my focus on this submit could be on “Map” visualisation. My intention is just not explaining Energy View and Energy Map that a lot so my focus on this article could be on Energy BI greater than the opposite two.

To experiment every little thing I clarify on this submit you might want to have:

• The brand new SQL Server pattern, WideWorldImportersDW (WWI). You possibly can obtain it right here
• The newest model on Energy BI Desktop (present model is 2.35.4399.381 64-bit (Might 2016))
• Excel 2016 or Excel 2013

If you happen to use Excel 2016, then you might want to activate Energy View on.

Verify this out if you wish to be taught 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

Flawed Cities in Energy BI

• Broaden 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 completely different nations, 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 reveals a metropolis with the identical title exterior of the US, identical to New Plymouth which a metropolis in New Zealand, however, the New Plymouth we’ve got in our information supply is the New Plymouth from Idaho within the US.

Fixing the Flawed Cities Challenge 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 right cities. We are able to do that is Energy Question or writing DAX expressions. I clarify DAX resolution and depart the Energy Question situation 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. Swap to “Information” view to see how the brand new column seems to be like.

Answer 2: Getting 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 shortly see that it accommodates latitude and longitude data which is precisely what we want. We solely must take away “POINTS( “ and “)” from the string after which changing area with comma to get location information primarily based 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 will. You possibly can add a brand new column and add the expression under 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 flawed now? The placement meant to point out extra exact factors on the Map, however, why it reveals most of locations in Antarctica? Critically?

Right here is when the following problem reveals up.

OK, maintain studying to seek out out why Map reveals flawed information.

Energy Question Challenge in Changing SQL Server “Geography” Information Kind 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 received 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 hook up with the SQL Server occasion that hosts the WideWorldImportersDW database
• Question Dimension.Metropolis

The “Location” column information sort in SQL Server is “geography” which suggests we will 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

Observe: Geography is a .NET Frequent Language Expression (CLR) information sort obtainable in SQL Server. Within the above question “.Lat” and “.Lengthy” are properties of geography occasion. The properties of geography are case delicate, so in the event you sort “[Location].lat” fairly than “[Location].Lat” you’ll get the under error message:

“Couldn’t discover property or subject ‘lat’ for sort ‘Microsoft.SqlServer.Sorts.SqlGeography’ in meeting ‘Microsoft.SqlServer.Sorts’.”

To be taught extra about geography information sort in SQL Server discuss with the under hyperlinks:

OK, let’s take a look at each SQL question and Energy Question outcome side-by-side.

Effectively, we uncovered the problem now. For some cause, Energy Question, erroneously transformed SQL Server geography information sort to textual content. The conversion sample needs to be “POINT (Latitude Longitude)” fairly than “POINT (Longitude Latitude)”. It seems to be like a bug. I already reported this as a bug to Energy BI staff. If you happen to additionally wish to report it click on right here.

However, what we must always do till Energy BI staff fixes the problem? Effectively, we will overcome this in two methods.

• We are able to cut 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: Cut up “Location” column to Latitude and Longitude

We already went greater than half of the best way within the earlier part once we remodeled “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 cut 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 fastened.

Energy Question Challenge with Supporting CLR sort ‘Microsoft.SqlServer.Sorts.SqlGeography’

This problem occurs once you wish to import information from a SQL question which queries a column of sort geography. Generally you might want to get information from SQL Server by operating SQL statements. For example, in our pattern database, take a look at the “Dimension.Metropolis” desk. The “Legitimate From” and “Legitimate To” columns characterize Slowly Altering Dimension which suggests not all of the rows within the Dimension.Metropolis are legitimate. A finest observe to maintain a Energy BI Mannequin measurement optimum is to not import the info you want. It additionally improves the efficiency of our mannequin. So I’m interested by 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 sort the SQL assertion under then click on OK

SELECT *

FROM Dimension.Metropolis

WHERE Yr([Valid To]) = 9999

• It introduced “Error”, however, why? Click on “Edit” to see what’s flawed with “Location”

• Click on on a cell with error

We received “DataSource.Error: We don’t help CLR sort ‘Microsoft.SqlServer.Sorts.SqlGeography’” error. Hmm, it doesn’t look good. Effectively, let’s transfer ahead and repair this problem as nicely.

Answer: Get “Latitude” and “Longitude” Properties from “Location” with T-SQL

We are able to simply repair this problem by modifying our T-SQL a wee. I defined that we will get “Latitude” and “Longitude” from geography information sort in SQL Server earlier on this article. We use the identical method to repair this problem. All you might want to do is to name “Lat” and “Lengthy” properties of “Location”. Therefore, the SQL assertion will probably 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 switch the previous SQL assertion with the brand new one simply click on on the gear icon () on the best aspect of “Supply” step from “Question Settings” pane.

Click on OK then click on “Shut & Apply” to get the right outcomes with no errors. The one distinction within the outcome 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 handle 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 straight

• Import information from SQL Server into Energy Pivot mannequin straight

• Hook up with SQL Server, import and rework information utilizing Energy Question, then load the outcomes to both Excel sheets or Energy Pivot information mannequin

To maintain this half so simple as potential I clarify load information into Energy Pivot straight.

Observe: If you happen to use Excel 2013, don’t fear because it’s not that completely different from Excel 2016. You simply must import information utilizing Energy Pivot tab on the ribbon

Flawed Cities in Energy View and Energy Map

The identical factor occurs in Energy View and Energy Map. While you put “Metropolis” on the map it reveals flawed 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 under is from Energy Map:

Answer: Concatenating Nation and Metropolis in Energy Pivot

We’ve 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 Kind

Identical to what we confronted in Energy BI once 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 Yr([Valid To]) = 9999

Here’s what we get in Energy Pivot:

As you see there isn’t a “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 under:

In case you are working in a undertaking 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 sort. I’ll in all probability get flawed 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 find out how to repair them could possibly be annoying and expensive. So I hope Microsoft repair these type of points within the subsequent launch/updates of Energy BI and Excel 2016.

If you happen to confronted every other points it will be nice in the event you share it with us. So why not writing your story within the feedback part under?

Associated