Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MDX query filter

Posted on 2011-05-09
6
Medium Priority
?
1,228 Views
Last Modified: 2013-11-16
Hi,
I have a cube with three dimensions (Region, Market, Office)
Following Query is returning lot of additional records which I do not need.

SELECT NON EMPTY { [Measures].[Measure1],
                    [Measures].[Measure2]} ON COLUMNS,
    NON EMPTY { ([Dim Region].[Region].ALLMembers*
                    [Dim Market].[Market].ALLMembers *
                    [Dim Office].[Office].ALLMembers
                    ) }    ON ROWS
FROM [MyCube]


Here is the sample result set this query returns.

Region       Market       Office
R.All       M.All       O.All
R.All       M.All       O.O1
R.All       M.All       O.O2
R.All       M.All       O.O3
R.All       M.All       O.O4
R.All       M.All       O.O5
R.All       M.All       O.O6
R.All       M.All       O.O7
R.All       M.All       O.O8
R.All       M.M1       O.All
R.All       M.M1       O.O1
R.All       M.M1       O.O2
R.All       M.M1       O.O3
R.All       M.M1       O.O4
R.All       M.M1       O.O5
R.All       M.M1       O.O6
R.All       M.M1       O.O7
R.All       M.M1       O.O8

I am looking for query that returns following

Region       Market       Office
R.All       M.All       O.All
R.R1       M.All       O.All
R.R1       M.M1       O.All
R.R1       M.M1       O.O1
R.R1       M.M1       O.O2
R.R1       M.M2       O.All
R.R1       M.M2       O.O3
R.R1       M.M2       O.O4
R.R2       M.All       O.All
R.R2       M.M3       O.All
R.R2       M.M3       O.O5
R.R2       M.M3       O.O6
R.R2       M.M4       O.All
R.R2       M.M4       O.O7
R.R2       M.M4       O.O8

Please suggest filters\changes to the MDX query that returns above result set.

Thanks
0
Comment
Question by:Buster_SM
  • 3
  • 3
6 Comments
 

Author Comment

by:Buster_SM
ID: 35731326
Can some please look into this issue.

Thanks!
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 35732082
Hi,

What criteria can be used to determine the unwanted rows? It's not possible to determine this from the results you've posted.

Thanks

Tim
0
 

Author Comment

by:Buster_SM
ID: 35732364
Hi Tim,
Thanks for looking into this.

Here is the criteria.

If Region = "All" and Market <> "All then the row should be eliminated.
If Market  = "All" and Office <> "All then the row should be eliminated.

Thanks!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:Tim Humphries
ID: 35735750
HI,

Try this use of the FILTER command:

SELECT NON EMPTY { [Measures].[Measure1],
                    [Measures].[Measure2]} ON COLUMNS,
    FILTER(NON EMPTY { ([Dim Region].[Region].ALLMembers*
                    [Dim Market].[Market].ALLMembers *
                    [Dim Office].[Office].ALLMembers
                    ) },
NOT
(
([Dim Region].[Region].CURRENTMEMBER.MEMBER_CAPTION = "ALL" AND [Dim Market].[Market].CURRENTMEMBER.MEMBER_CAPTION <> "ALL")
      OR ([Dim Market].[Market].CURRENTMEMBER.MEMBER_CAPTION = "ALL" AND [Dim Office].[Office].CURRENTMEMBER.MEMBER_CAPTION <> "ALL")))
    ON ROWS
FROM [MyCube]

Works in an equivalent query in my environment.

Cheers

Tim
0
 

Author Comment

by:Buster_SM
ID: 35749648
Hi Tim,
Thanks for the response.

I tried this but the query is running for long time. I let it run for 15 minutes and it is eating up resources on the server. I stopped it after 15 minutes.

I have 15 million records in the fact table.

If I remove the filter, I am getting results in 3 seconds.

Is there any other way to do this?

Thanks!


0
 
LVL 15

Accepted Solution

by:
Tim Humphries earned 2000 total points
ID: 35752861
ok, try this:

SELECT NON EMPTY { [Measures].[Measure1],
                    [Measures].[Measure2]} ON COLUMNS,
    NON EMPTY { [Dim Region].[Region].ALLMembers *
                    [Dim Market].[Market].ALLMembers *
                    [Dim Office].[Office].ALLMembers
                     }
      -{[Dim Region].[Region].[All] * [Dim Market].[Market].Children * [Dim Office].[Office].ALLMembers}
      -{[Dim Region].[Region].ALLMembers * [Dim Market].[Market].[All] * [Dim Office].[Office].Children}


    ON ROWS
FROM [MyCube]

This works by excluding the sets of rows that you don't want. As these are bulk selections it should perform much better.

Tim
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question