Showing posts with label aggregations. Show all posts
Showing posts with label aggregations. Show all posts

Wednesday, March 7, 2012

Another aggregation design question

Guys,

I model a cube with dimensions, I create the partitions and their aggregations, then I change the dimensions structure or I change the dataview.

Cube does not complain about aggregations at any time during the change process.

So what is this all about?

I suspect I should run a re-design of aggregations when I am done with structural changes but I wonder why I am never warned about existing aggregations becoming obsolete?

So when should you re-design aggregations? (I use 30% then I add usage based after a while).

Thanks,

Philippe

Yes, you need to go an re-desing your aggregations after you've made stuctural changes to your dimension: You've added, or removed attrubutes from the dimension, you've changed attribute relationships you should go and re-desing aggregations.

If you defined natural hierarchies ( you can search this forum for the definition of these) 30% should be a good start. But in case and if you are using just regual hierarchies without defining attribute relationships, or you browse attributes using attribute hierarchies, you should go over all the attributes you browse by and change AggregationUsage property to Full or Unrestricted.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Sunday, February 19, 2012

Analyzing MDX query evaluation

Are there any tools or tricks for looking at how an MDX query is evaluated in SSAS 2005? Something that indicates how aggregations are being used, for example - sort of like a SHOWPLAN for MDX.
As you might know, AS 2005 MDX query execution events are now captured in SQL Server Profiler. What's not as clear from the docs (that I've come across, anyway) is the aggregation usage info available via SQL Profiler. This was well explained at the recent SQL PASS Conference, in Session 108: "Understanding Analysis Services 2005 Aggregations from Every Angle", by Dave Wickert and Len Wyatt:

>>
SQL Profiler

Will tell you when:
an aggregation is used for a query
the partition data is used for a query

It won’t tell you why it decides to use or not use a particular aggregation

§>>

|||Deepak,
Thanks, that sounds very promising. Do you know where I can find more information about using Profiler for this purpose? That SQL PASS session doesn't appear to be downloadable (that I can find, at least), and the documentation isn't much help here (as you point out).
Thanks,
Kevin
|||Hi Kevin,

If you're a PASS member, then the presentation slides should be downloadable, or you can order the physical media with MP3 sound. Now maybe Dave and Len are making their presentation (or equivalent info) available elsewhere - don't know!

http://www.softconference.com/250927
>>

CO1: Compilation CD-ROM set with full desktop capture - MP3 audio including live demos and PowerPoints (for attendees of the 2005 PASS Summit) CDROM (Attendees only) $ 195.00 Add to shopping cart CO2: Compilation CD-ROM set with full desktop capture - MP3 audio including live demos and PowerPoints (for members of PASS who did not attend the 2005 Summit) CDRom (Members only) $ 395.00 Add to shopping cart CO3: Compilation CD-ROM set with full desktop capture - MP3 audio including live demos and PowerPoints (for non-attendees, non-members of PASS) CDRom $ 595.00


>>

Thursday, February 9, 2012

Analysis Services 2005 Aggregations?

Hi,

When i try to calculate the cube aggregations it will always give me 0 aggregations... why is this happening? I have about 10 dimensions and if i remove 3 of them (always the same dims) the aggregations are calculated to about 99% so why the hell with that dims it would not calculate? They are Parent-Child Dimensions...

Best Regards,

Luis Sim?es

Looks like a bug to me.

Can you please try and contact Customer Support and open a case about this behavior. It would be helpful if you will be willing to share your cube design.

Thanks.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

How do i contact Customer Support?

About the cube i really can't post anything about it because it is beeing done to a major public entity and i have no authorization to pass schemas, designs or data out of here...

So is there anyway we can synch and try to find the problem?

Best Regards,

Luis Sim?es

|||

http://support.microsoft.com/ph/2855

See if you pass the information to Customer Support in more general terms. How many dimensions, how many attributes per dimension. How many hierarhies in every dimension.
To try and reproduce the problem in generic version of your cube.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

I am doomed! The cube is so slow that i can't do anything with it... Jesus....

Has for the microsoft site i was unable to find a direct way of contact with them unless i have to pay for support...

Do you have any sugestions?

U have msn?

Regards

|||

Luis.

It is really hard or nearly impossible to help you with very short description of the problem.
It is even harder to help if you cannot share any of your data.

Can you try and describe your cube design in general terms like:

Dim1 ( KeyAttr, Attr1 , Attr2 ....) Parent-Child
Attr1 is related to KeyAttr and usage is set to Parent.

Dim2 (KeyAttr, Attr1 , Attr2 ) Regular
Hierarchies Hier1 (Attr2, Attr1, KeyAttr)

....

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

Well i am shure now that the problem lays within the Parent-Child Dimensions because i have created new dimensions for the same purpose but flattened.. no parent-child and the cube gets 95% aggregated...

So basically all my Parent-Child Dimensions are done like the book says...

So i have A Parent Atribute and a Key Atribute

Example: Products Dimension

PARENT_PRODUCT_COD -> this is the parent atribute

PRODUCT_COD -> this is the key atribute

In the parent atribute i also have parameterized the UNARY OPERATOR and ROLLUP FORMULA with the correct columns

So i don't really know where is the problem...

Regards,

|||

The solution for your problem ( I got better description of it through the MS guys in Portugal) is:

For every PC dimension you should change AggregationUsage property of the Parent attribute to None.
For every Key attribute you should set AggregationUsage to Unrestricted.

Once you've done this, you should see aggregation desing algorithm desinging aggregations for you.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

I have received from Jose Silva (MS Portugal) the changed project, after calculating the aggregations i still get 0%....

It seems it is a known bug by AS 2005 programmers team and probably only solved by a hotfix...

Lets see what happens in the next days... and then i will post the result here so the community can be informed about this problem.

Thank you!

Best Regards,

Luis Simoes
WorldVision Consulting

Analysis Services 2005 Aggregations?

Hi,

When i try to calculate the cube aggregations it will always give me 0 aggregations... why is this happening? I have about 10 dimensions and if i remove 3 of them (always the same dims) the aggregations are calculated to about 99% so why the hell with that dims it would not calculate? They are Parent-Child Dimensions...

Best Regards,

Luis Sim?es

Looks like a bug to me.

Can you please try and contact Customer Support and open a case about this behavior. It would be helpful if you will be willing to share your cube design.

Thanks.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

How do i contact Customer Support?

About the cube i really can't post anything about it because it is beeing done to a major public entity and i have no authorization to pass schemas, designs or data out of here...

So is there anyway we can synch and try to find the problem?

Best Regards,

Luis Sim?es

|||

http://support.microsoft.com/ph/2855

See if you pass the information to Customer Support in more general terms. How many dimensions, how many attributes per dimension. How many hierarhies in every dimension.
To try and reproduce the problem in generic version of your cube.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

I am doomed! The cube is so slow that i can't do anything with it... Jesus....

Has for the microsoft site i was unable to find a direct way of contact with them unless i have to pay for support...

Do you have any sugestions?

U have msn?

Regards

|||

Luis.

It is really hard or nearly impossible to help you with very short description of the problem.
It is even harder to help if you cannot share any of your data.

Can you try and describe your cube design in general terms like:

Dim1 ( KeyAttr, Attr1 , Attr2 ....) Parent-Child
Attr1 is related to KeyAttr and usage is set to Parent.

Dim2 (KeyAttr, Attr1 , Attr2 ) Regular
Hierarchies Hier1 (Attr2, Attr1, KeyAttr)

....

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

Well i am shure now that the problem lays within the Parent-Child Dimensions because i have created new dimensions for the same purpose but flattened.. no parent-child and the cube gets 95% aggregated...

So basically all my Parent-Child Dimensions are done like the book says...

So i have A Parent Atribute and a Key Atribute

Example: Products Dimension

PARENT_PRODUCT_COD -> this is the parent atribute

PRODUCT_COD -> this is the key atribute

In the parent atribute i also have parameterized the UNARY OPERATOR and ROLLUP FORMULA with the correct columns

So i don't really know where is the problem...

Regards,

|||

The solution for your problem ( I got better description of it through the MS guys in Portugal) is:

For every PC dimension you should change AggregationUsage property of the Parent attribute to None.
For every Key attribute you should set AggregationUsage to Unrestricted.

Once you've done this, you should see aggregation desing algorithm desinging aggregations for you.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

I have received from Jose Silva (MS Portugal) the changed project, after calculating the aggregations i still get 0%....

It seems it is a known bug by AS 2005 programmers team and probably only solved by a hotfix...

Lets see what happens in the next days... and then i will post the result here so the community can be informed about this problem.

Thank you!

Best Regards,

Luis Simoes
WorldVision Consulting