LIKE and IN in MDX Together: Filtering Set on Set

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

LIKE and IN in MDX Together: Filtering Set on Set

Author

Topic

polaro
Starting Member

2 Posts

Posted - 2011-06-22 : 19:09:53

Greetings,

What I like to do is calculate clicks for members of dimension A where the
members resemble members of dimension B:

I have used familiar Instr statement to simulate LIKE operator:

with set sites as [Sites].[Site Name].children
set prec as [Yahoo Advertiser].[Advertiser Domain].children
select ([Date].[Month Desc].children,[Measures].[Click Count]) on 0,
(filter(prec, instr(1, prec.currentmember.properties("Name"), "pg")<>0)) on
1
from cube
where ([Date].[Year - Month - Date].[Year].&[2011].&[201101]:null)

So far so good but it only works with hard-coded "parameter" ("pg" in my case)
. What I want to do is:

with set sites as [Sites].[Site Name].children
set prec as [Yahoo Advertiser].[Advertiser Domain].children
select ([Date].[Month Desc].children,[Measures].[Click Count]) on 0,
(filter(prec, instr(1, prec.currentmember.properties("Name"), sites.
currentmember)<>0)) on 1
from cube
where ([Date].[Year - Month - Date].[Year].&[2011].&[201101]:null)

I think what needs to be done is sites.currentmember be converted into string
and then "loop" through all sites members.

Any help will be much appreciated. Thanks

polaro
Starting Member

2 Posts

Posted - 2011-06-29 : 10:41:47

Here is what I was able build using help from various forums. The queries are based on AdventureWorks, just plug in your dimensions and measures. Word of caution though - the operators are iterative and take a while to process. It does not make sense to employ them if you have more than a few thousand members in either of the dimensions. For instance, I had to resort to SQL instead of MDX for the actual implementation because my dimensions are several hundred thousands each and querying even one single day takes more than 10 minutes.

Dimension 1: [Product].[Product Line].children
Dimension 2: [Reseller].[Product Line].children

1. How to find exact matches of members in product compared to reseller

in SQL:
select [product line] from product where [product line] IN (select [product line] from reseller)

with
set promo as [Reseller].[Product Line].children
set prod as [Product].[Product Line].children
select
([Date].[Calendar].[Month].members,[Measures].[Reseller Sales Amount]) on 0,
GENERATE ( promo, filter(prod, prod.currentmember.name=promo.current.item(0).Name)) on 1
from [Adventure Works]

2. How to find members of products which resemble members of resellers (combining IN and LIKE)

with
set promo as [Reseller].[Product Line].children
set prod as [Product].[Product Line].children
select
([Date].[Calendar].[Month].members,[Measures].[Reseller Sales Amount]) on 0,
GENERATE ( prod, filter(promo, instr(1, promo.currentmember.name, prod.current.item(0).Name)<>0)) on 1
from [Adventure Works]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources