Hi,
we have a cube which is basically structured like that:
We have dimensions like D1 (2000 records), D2 (6000 records) and D3 (6000 records). There are some others (like a time dimension), but they don't matter so much (I think). D1, D2 and D3 are defined as Parent-Child dimensions, but we don't use this structure, yet.
We also have some facts, just around 10,000 records.
There are other fact tables using some of the dimensions as well, but I guess that's not important at all.
We have a query which runs forever. This is using some attributes from D1, D2 and D3 and just one measure. Basically it's a select of one measure on the columns and a non empty crossjoin of the three attribute hierarchies we use from the dimensions. So really nothing fancy. We have some calculated members (measures) as well, but they are not used in this query...
Select {[Measures].[X]} on columns, non empty {{[D1].[H1].[All].children}*{[D2].[H2].[All].children}*{[D3].[H3].[All].children}} on rows from [DB]
We constructed the MDX by ProClarity, they add some cell properties at the end of the query, using that we see the memory flowing away to msmdsrv.exe...
I understand that internally SSAS has to calculate a cross join with 2000x6000x6000 combinations. But this is something SSAS has to be able to handle, otherwise the concept of Attribute Hierarchies doesn't work at all.
We're using SSAS 2005 RTM, a colleague tested that with SP1, no change.
So what's wrong with the query or the concept or the person typing this post?
Thanks,
I think you should try nonemptycrossjoin instead:
Select {[Measures].[X]} on columns, {nonemptycrossjoin([D1].[H1].[All].children,[D2].[H2].[All].children,[D3].[H3].[All].children)} on rows from [DB]
nonemptycrossjoin is more efficient when there're several dimensions,
|||I think you should try nonemptycrossjoin instead:
Select {[Measures].[X]} on columns, {nonemptycrossjoin([D1].[H1].[All].children,[D2].[H2].[All].children,[D3].[H3].[All].children)} on rows from [DB]
nonemptycrossjoin is more efficient when there're several dimensions,
|||Have you thought of creating a fact dimension containing H1, H2, H3?
With your attributes approaching size of the fact table it might be better idea.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
