Probably the most 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 instantly. In Energy BI, as you understand, there are two built-in visualisations supporting geographic coordinate knowledge, Map and Stuffed map. They work superbly in case you have sufficient knowledge supported by Bing Maps. However, there are some points with Map visualisations in each Energy BI and Excel. On this put up I deal with a number of the points I confronted myself and I’ll present the options for the problems. As “Stuffed Map” and “Map” visualisations in Energy BI are very related my focus on this put up could be on “Map” visualisation. My intention will not be 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 all the things I clarify on this put up it’s essential 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

When you use Excel 2016, then it’s essential to activate Energy View on.

Examine 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 Reality.Gross sales and Dimension.Metropolis then load knowledge

Improper 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 “Complete Excluding Tax” on Dimension

As you see gross sales distributed throughout totally 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 the 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 knowledge supply is the New Plymouth from Idaho within the US.

Fixing the Improper Cities Situation In Energy BI

Resolution 1: Concatenating Nation and Metropolis in Energy BI

We will simply overcome the difficulty 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 will do that is Energy Question or writing DAX expressions. I clarify DAX resolution and go away 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 appears significantly better now. Swap to “Information” view to see how the brand new column appears like.

Resolution 2: Making ready Location in Energy BI

We will use “Location” column on the Map to get extra exact factors. Wanting on the “Location” column we shortly see that it incorporates latitude and longitude data which is precisely what we want. We solely have to take away “POINTS( “ and “)” from the string after which changing area with comma to get location knowledge primarily based on latitude and longitude from the “Location” column.

Now you can click on Shut & Apply. However wait, can we do all the 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 all the 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 unsuitable now? The situation 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 subsequent situation reveals up.

OK, preserve studying to seek out out why Map reveals unsuitable knowledge.

Energy Question Situation in Changing SQL Server “Geography” Information Sort to Energy Question Textual content

Let’s take a look on the complete course of that we went by up to now to seek out out what the difficulty is. We received knowledge 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 effectively 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 knowledge sort in SQL Server is “geography” which implies we will simply get latitude and longitude from the placement 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

Be aware: Geography is a .NET Widespread Language Expression (CLR) knowledge 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” moderately than “[Location].Lat” you’ll get the under error message:

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

To study extra about geography knowledge sort in SQL Server discuss with the under hyperlinks:

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

Effectively, we uncovered the difficulty now. For some cause, Energy Question, erroneously transformed SQL Server geography knowledge sort to textual content. The conversion sample ought to be “POINT (Latitude Longitude)” moderately than “POINT (Longitude Latitude)”. It appears like a bug. I already reported this as a bug to Energy BI group. When you additionally wish to report it click on right here.

However, what we should always do till Energy BI group fixes the difficulty? Effectively, we will overcome this in two methods.

• We will cut up “Location” column in Energy Question to 2 columns, Latitude and Longitude

• We will use the SQL question we wrote beforehand and import the “Dimension.Metropolis” desk once more

Resolution: Cut up “Location” column to Latitude and Longitude

We already went greater than half of the best way within the earlier part after 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 knowledge 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 problem is mounted.

Energy Question Situation with Supporting CLR sort ‘Microsoft.SqlServer.Varieties.SqlGeography’

This situation occurs once you wish to import knowledge from a SQL question which queries a column of sort geography. Generally it’s essential to get knowledge from SQL Server by operating SQL statements. As an example, in our pattern database, take a look at the “Dimension.Metropolis” desk. The “Legitimate From” and “Legitimate To” columns symbolize Slowly Altering Dimension which implies not all of the rows within the Dimension.Metropolis are legitimate. A finest follow 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 eager 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 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 unsuitable with “Location”

• Click on on a cell with error

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

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

We will simply repair this situation by modifying our T-SQL a wee. I defined that we will get “Latitude” and “Longitude” from geography knowledge sort in SQL Server earlier on this article. We use the identical approach to repair this situation. All it’s essential to do is to name “Lat” and “Lengthy” properties of “Location”. Therefore, the SQL assertion can 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 interchange the outdated SQL assertion with the brand new one simply click on on the gear icon (

) on the suitable facet 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 end result set is that we now have two extra columns displaying latitude and longitude. We concatenated latitude and longitude to help “Location” as effectively.

Within the subsequent part I deal with the identical points in Energy View and Energy Map.

There are numerous methods to arrange knowledge for Energy View and Energy Map in Excel 2016.

• Import knowledge from SQL Server into Excel instantly

• Import knowledge from SQL Server into Energy Pivot mannequin instantly

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

To maintain this half so simple as attainable I clarify load knowledge into Energy Pivot instantly.

Be aware: When you use Excel 2013, don’t fear because it’s not that totally different from Excel 2016. You simply have to import knowledge 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 reveals unsuitable cities. It’s trivial as all of those instruments are utilizing Bing Map engine to visualise map knowledge.

Here’s what we get in Energy View:

The screenshot under is from Energy Map:

Resolution: Concatenating Nation and Metropolis in Energy Pivot

We’ve got 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 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 after 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 venture that entails with geospatial knowledge and your supply database is SQL Server then take heed to incompatibility of Energy BI, Energy Pivot and Energy Question with SQL Server geography knowledge sort. I’ll most likely get unsuitable cities in Energy BI map visualisations, Energy View map or Energy Map visualisations if you don’t specify which metropolis precisely you need.

Going through the above points for the primary time with out understanding methods to repair them could possibly be annoying and dear. So I hope Microsoft repair these kind of points within the subsequent launch/updates of Energy BI and Excel 2016.

When you confronted another points it could be nice in the event you share it with us. So why not writing your story within the feedback part under?

Associated