Click here to Skip to main content
15,439,556 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am quite new to MDX (who isn't) and I need someone to help me with this query.

SQL
GENERATE
(
  FILTER
  (
    [Dim Products].[Product].[Product].members * [Dim Date].Week.Week.members, [Measures].[Price]
  ),
  NONEMPTY
  ( 
    TOPCOUNT
    (
      [Dim Price].[Price].[Price]*[Dim Products].[Product].currentmember * [Dim Date].Week.currentmember , 1, [Measures].[Price Count]
    )
  )
)


I am using the above Named Set in a dashboard tool (Dundas Dashboard) in order to retrieve the MODE (price value that repeats the most). It does show correct results however it is slow. It takes 2-3 seconds if there is a filter on a single week and takes about 6-7 seconds if there is no filter on week (shows data for all weeks). And this is in SSMS but the client tool takes even longer to get the result set, sometimes timing out.

After some tests it appears that the number of the rows in the fact table does not affect the performance, I've drastically decreased it but still the same. The performance increased, however, once I've decreased the number of dimensions in 2 of the dimension tables - [Dim Price], [Dim Products]. Initially it was taking 20+ seconds to get result set but improved to 6-7 seconds once I've decreased the number of dimensions as follows:
Table Rows Before Rows After
[Dim Price] 2400 620
[Dim Products] 1080 101

This makes me think there is a Cartesian Product between the 3 dimensions that is affecting the performance.

Again I want to mention that I am a newbie therefore I need someone to advise how I can improve the query to increase the performance.

Thank you in advance!
Mihai
Posted

1 solution

Any advise please? I need help with this as soon as possible.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900