You have a multidimensional SQL Server Analysis Services (SSAS) cube that your users connect to with Power View for ad-hoc reporting. You’d like your users to be able to use the mapping functionality of Power View to map specific locations of interest. You expose your locations as latitude and longitude attributes in your Locations dimension, because Power View supports latitudes and longitudes. But no matter what you try to do within your SSAS dimension, from changing the basic attribute types, the data types, the names or values, nothing will enable you to drag these attributes into their designated boxes in Power View. What gives? Short answer –
Exposing your geographical location data in a SSAS cube measure group instead of a dimension will allow you to use latitudes and longitudes in Power View.
Why Put Latitudes and Longitudes in a SSAS Cube Measure Group?
Why expose your latitudes and longitudes in an Analysis Services cube Measure group and not in a dimension? Good question! In many cases, you likely have dimensions with distinct locations that you care about, and having latitude and longitude as attributes of those locations makes perfect sense. But I can see the other scenario as well, where you’re perhaps tracking your jogging path or your voyage as you take a road trip across the country. You’re collecting many geographic points in this case, that you don’t really care too much about individually. You’d put these location points into a fact table without thinking twice about it. But in any case, exposing your latitude and longitude in the cube dimension won’t let you use them in their designated location area in Power View, as so many people have discovered. So, because it doesn’t work in a dimension, that’s a pretty good reason to put your location information in a measure group.
An article in a Tableau forum is what gave me the idea to try using a measure group for my latitudes and longitudes. It offered advice to Tableau users that storing the location information in a measure group instead of a dimension could get the values to register correctly as location information in Tableau. “That won’t take long to test out in Power View”, I thought. I already have the latitudes and longitudes in a dimension. I even have an ugly hack working in Power View. You can expose the name of your location in the format: “Latitude Longitude (Real location name)”. It works, but doesn’t make for very readable location names. So why don’t I throw that same location point data into a measure and try it out? Here’s a simple walk-through on doing just that.
How to Expose Cube Dimension Latitudes and Longitudes in a Measure Instead
Open your data source view in your analysis services database with visual studio and add a new named query. Let’s call it “LocationsMeasure”. Make sure you include the keys in the query that you can relate back to the existing dimension, along with the latitudes and longitudes, like in this example:
SELECT DISTINCT LocationKey, Latitude, Longitude FROM DW.DimLocation
Next, create your relationship back to your “Locations” dimension table in the DSV.
Now, open up your SSAS cube and create a new measure group. Let’s call it “Location LatLongs”. Choose your new “LocationsMeasure” as the data source of this measure. Then, in your dimension usage, make sure your mappings to the Location LatLongs measure group are set as regular to your existing Locations dimension. If necessary also set as referenced (through the Locations dimension) any other dimensions where you might have higher level geographical hierarchies that cascade down to Locations.
All Done, and Ready for Power View!
And that’s it! Now, in Power View mapping, drag your natural location name from your Locations dimension into the Locations area. Then, drag your latitudes and longitudes from your Location Latlongs measure group into the Longitude and Latitude area. Look at that, your points render on the map appropriately now. Nice! Now your users have everything available in the Analysis Services multidimensional cube you’ve made available to them to roll out some cool map visualizations.