Hi guys,
I have the following problem:
I have several tables in SQL 2005 ( table GeographicLocations and other related to it ). I created a cube in SSAS in which GeographicLocations is dimension.
I was able to add parameters but I need something more complicated: Having columns longitude and latitude in GeographicLocations I need to be able to return all locations within X (parameter) miles from passed GeographicLocation (parameter - GeographicLocationID )
I found SQL function that calculates distance from passed longitude and latitude of two points and T-SQL would like this:
declare @.GeoId int;
set @.GeoID = 200765;
select * from GeographicLocations as t1
where dbo.geo_distance(t1.longitude, t1.latitude,
(select longitude from GeographicLocations where id=@.GeoID),
(select latitude from GeographicLocations where id=@.GeoID)) < 2000
but how I can make same in SSAS and Reports?
Thanks in advance
I suggest making Longitude and Latitude related attributes of GeoLocation, and then implement SSAS sproc which computes difference between two points (similar to geo_distance).
|||
Hi Mosha,
Thank you very much for helping me.
I would like to ask you to be more specific as I am newbee in SSAS. Could you point me at some samples and resources that use such approach?
It is kind of blury to me how to create related attributes in such manner (although I read this article ), how to create SSAS stored procedure and how to call it from the report project
Best regards,
Galin
|||
Hi,
Any advise ? I'm dealing with the same problem and I'm also a newbie in SSAS.
I was thinking to make an user defined function in an assembly to compute the distance but I can't figure out if this will be viable.
Daniel.
|||I made .NET function to calculate distance - same as T-SQL one
also I made Longitude and Latitude measures and I could pass them in MDX query
Hope this helps|||
Hi,
Can you provide a sample of your MDX query ?
I was thinking that Lat and Long to be dimensions not measures.
Thnx.
daniel
|||
I've changed the fact table in order to have Latitude and Longitude as Measures; so now I have:
Measures(AggregateFunction)
PriceCount (Count),
Latitude (None),
Longitude (None)
Dimension
[Tbl DW Dim Property Type]
but from tests I've found that performing filter operations on Latitude/Longitude measures it is returning wrong results:
Code Snippet
SELECT
NON EMPTY { [Measures].[Prices Count] } ON COLUMNS,
NON EMPTY FILTER([Tbl DW Dim Property Type].[Name].Members, [Measures].[Latitude]>44.42659335504373) ON ROWS
FROM [DW DEV]
so I used an .NET sp to capture the data:
Code Snippet
SELECT
NON EMPTY { [Measures].[Prices Count] } ON COLUMNS,
NON EMPTY FILTER([Tbl DW Dim Property Type].[Name].Members, MDXClassLibrary.DoSomething([Measures].[Latitude])>44.42659335504373) ON ROWS
FROM [DW DEV]
Code Snippet
public static double DoSomething(double x)
{
return x;
}
And during debugging I’ve found that the sp’s parameter is receiving wrong values not the ones that are in [Measures].[Latitude].
I know that I'm missing something but I can't figure.
Any help would be appreciated.
|||
I've managed to run a query like this
Code Snippet
SELECT NON EMPTY { [Measures].[Prices Avg], [Measures].[Prices Max], [Measures].[Prices Min] } ON COLUMNS,
NON EMPTY { ([Tbl DW Dim Time].[Calendar Year].[Calendar Year].ALLMEMBERS *
[Tbl DW Dim Time].[Month Number Of Year].[Month Number Of Year].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM
( SELECT ( { FILTER([Tbl DW Dim Gis].[Coord].MEMBERS,
mdxclasslibrary.dist([Tbl DW Dim Gis].[Coord].CurrentMember.Name,
'44.42366640719915000000000','26.07366800308228000000000',1 )>0)}) ON COLUMNS
FROM (
SELECT ({[Tbl DW Dim Property Type].[Name].&[Apartment] } ) ON COLUMNS
FROM [DEV]))
but i noticed a significant performance degradation.
Any suggestions ?
