Share via

Is there any way to set a latitude and longitude attribute type for a dimension?

Andrew Hopkinson 6 Reputation points
2023-10-31T19:35:42.0066667+00:00

I am trying to connect from Power BI to an Analysis Services Cube (2016) using the live connection and a map visualizer. Is there anyway to define the latitude and longitude in Visual Studio for the dimension attribute so Power BI picks it up? I notice there are a bunch of Geography types in VS but I don't see lat and long. The only way seems to be to import the data into Power BI, and then change the type there. I would like to avoid that. Thank you.

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Greg Low 1,495 Reputation points Microsoft Regional Director
    2024-02-12T11:37:38.04+00:00

    Power BI doesn't "get" the SQL Server CLR data types.
    Also, there isn't a lat or long data type but the SQL geography data type has lat and long properties. What we usually do when the source data has a geography type, is to change the query that loads the data into SSAS (in our case it's a view), so that it loads the lat and long values and then you can just map/categorize those in your data model.
    i.e., instead of SELECT something, location, somethingelse FROM table do SELECT something, location.Lat AS Latitude, location.Long AS Longitude, somethingelse FROM table
    That should work fine for the standard mapping visualizations. If you're lucky enough to have one that understands WKT (well-known text) instead, you could just call the STAsText method and then you only need that one value (as a string)

    0 comments