I am quite new to MDX (who isn't) and I need someone to help me with this query.
[Dim Products].[Product].[Product].members * [Dim Date].Week.Week.members, [Measures].[Price]
[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!