Query performance

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.

Query on YTD Distinct Count

Is it possible to calculate " YTD Distinct Count " of number of Customers who are been serviced by a agent. We have two dimensions for period...as Year and Month . Other dimension for agent heirarchy....

"Is their any way to access fact column in my MDX qry for calculated measure?".

Thanks !!

With one time key, at the (Year)month level, and one agent key in the fact table you should be able to achive this. You will have all the keys that you will need.

Regards

Thomas Ivarsson

|||

Sasteam,

Were you able to accomplish this? I cannot get this to work for me. I keep ending up with the sum of the monthly distinct counts...which is not correct if the same customer was serviced in multiple months.

I would appreciate any sharing of info you may have.

Thanks.

|||

njojo,

I assume you are doing this with a calculated measure. If you have your measure set up as a "distinct count" measure AND use the Aggregate() function in your calculation this should work. The aggregate() function in SSAS is "aware" of distinct count measures.

|||

I can't seem to get Aggregate to work with YTD.

My measure is a distinct count: [Measures].[DC MctBrandID]

I ended up having to do this (which I found on another forum):

Count(NonEmptyCrossJoin(Descendants([MerchantBrand].CurrentMember,,Leaves)

,YTD([DateLevels].CurrentMember)

, {[Measures].[DC MctBrandID]},1))

I tried doing: Sum(YTD([DateLevels].CurrentMember),[Measures].[DC MctBrandID]).....which of course gave me the sum of the dc for each month in the year...not correct.

Then I did: Sum(YTD(Ancestor([DateLevels].CurrentMember,[DateLevels].[Year])), [Measures].[DC MctBrandID])....this number was correct but was for the whole year, not "to date"....still not right.

This is probably too much info and I apologize. I just want to do this calcuation the best possible way...my reports all have YTD and PTD requirements.

Thanks,

njojo

|||

In that case I am guessing you are using AS 2000 (sorry, I had assumed SSAS 2005)

In AS2000, the aggregate function had a couple of issues, to the point where I don't think many people actually used it and you are probably left having to use the Count(NonEmptyCrossjoin()) solution.

The Sum(YTD(Ancestor(...))) function will always produce the full year figure as the Ancestor function is returning the member at the year level, which is making the YTD() function redundant, the cube is simply returning the distinct count from the year member.

|||

I am Nitin Sharma from SASTeam. The initial person who posted this query.

I solved this by designing a dimension in my database for each Period-Type (viz. YTD, Rolling 12 months, Period). The dimension which I had designed is a view on my base Year and Month period along with a key field to identify the type of Period for which we need to calculate base measure. The eg. for dimension data is as follows:

col1 | col2 | col3 | col4 | col5 |

2006 | FEB | 2006 | FEB | 1 |

2006 | FEB | 2006 | JAN | 2 |

2006 | FEB | 2006 | FEB | 2 |

2006 | FEB | 2005 | MAR | 3 |

2006 | FEB | 2005 | APR | 3 |

.

.

.

2006 | FEB | 2005 | DEC | 3 |

2006 | FEB | 2006 | JAN | 3 |

2006 | FEB | 2006 | FEB | 3 |

Using this dimension in my cube I have linked my fact with new dimension as follows: derived year (3rd col) to fact-year and derived month (4th col) to fact-month. Actual Year dimension is linked with my new dimension-year (1st col) and actual month dimension is linked with new dimension-month (2nd col). The last col is used to define the PeriodType....1 for Simple Period, 2 for YTD and 3 for Rolling 12 month.

Now my time constraints (Period-Type) problem is solved and I can calculate any DISTINCT COUNT or any base calculation on my fact usign this logic, and hence the problem of YTD Distinct Count is solved.

I hope this works for you.

Thanks & Best Regards,

Nitin Sharma

Query on YTD Distinct Count

Is it possible to calculate " YTD Distinct Count " of number of Customers who are been serviced by a agent. We have two dimensions for period...as Year and Month . Other dimension for agent heirarchy....

"Is their any way to access fact column in my MDX qry for calculated measure?".

Thanks !!

With one time key, at the (Year)month level, and one agent key in the fact table you should be able to achive this. You will have all the keys that you will need.

Regards

Thomas Ivarsson

|||

Sasteam,

Were you able to accomplish this? I cannot get this to work for me. I keep ending up with the sum of the monthly distinct counts...which is not correct if the same customer was serviced in multiple months.

I would appreciate any sharing of info you may have.

Thanks.

|||

njojo,

I assume you are doing this with a calculated measure. If you have your measure set up as a "distinct count" measure AND use the Aggregate() function in your calculation this should work. The aggregate() function in SSAS is "aware" of distinct count measures.

|||

I can't seem to get Aggregate to work with YTD.

My measure is a distinct count: [Measures].[DC MctBrandID]

I ended up having to do this (which I found on another forum):

Count(NonEmptyCrossJoin(Descendants([MerchantBrand].CurrentMember,,Leaves)

,YTD([DateLevels].CurrentMember)

, {[Measures].[DC MctBrandID]},1))

I tried doing: Sum(YTD([DateLevels].CurrentMember),[Measures].[DC MctBrandID]).....which of course gave me the sum of the dc for each month in the year...not correct.

Then I did: Sum(YTD(Ancestor([DateLevels].CurrentMember,[DateLevels].[Year])), [Measures].[DC MctBrandID])....this number was correct but was for the whole year, not "to date"....still not right.

This is probably too much info and I apologize. I just want to do this calcuation the best possible way...my reports all have YTD and PTD requirements.

Thanks,

njojo

|||

In that case I am guessing you are using AS 2000 (sorry, I had assumed SSAS 2005)

In AS2000, the aggregate function had a couple of issues, to the point where I don't think many people actually used it and you are probably left having to use the Count(NonEmptyCrossjoin()) solution.

The Sum(YTD(Ancestor(...))) function will always produce the full year figure as the Ancestor function is returning the member at the year level, which is making the YTD() function redundant, the cube is simply returning the distinct count from the year member.

|||

I am Nitin Sharma from SASTeam. The initial person who posted this query.

I solved this by designing a dimension in my database for each Period-Type (viz. YTD, Rolling 12 months, Period). The dimension which I had designed is a view on my base Year and Month period along with a key field to identify the type of Period for which we need to calculate base measure. The eg. for dimension data is as follows:

col1 | col2 | col3 | col4 | col5 |

2006 | FEB | 2006 | FEB | 1 |

2006 | FEB | 2006 | JAN | 2 |

2006 | FEB | 2006 | FEB | 2 |

2006 | FEB | 2005 | MAR | 3 |

2006 | FEB | 2005 | APR | 3 |

.

.

.

2006 | FEB | 2005 | DEC | 3 |

2006 | FEB | 2006 | JAN | 3 |

2006 | FEB | 2006 | FEB | 3 |

Using this dimension in my cube I have linked my fact with new dimension as follows: derived year (3rd col) to fact-year and derived month (4th col) to fact-month. Actual Year dimension is linked with my new dimension-year (1st col) and actual month dimension is linked with new dimension-month (2nd col). The last col is used to define the PeriodType....1 for Simple Period, 2 for YTD and 3 for Rolling 12 month.

Now my time constraints (Period-Type) problem is solved and I can calculate any DISTINCT COUNT or any base calculation on my fact usign this logic, and hence the problem of YTD Distinct Count is solved.

I hope this works for you.

Thanks & Best Regards,

Nitin Sharma