How to add filter based on query?

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 ?