Question on initial aggregation design

Hi, all experts here,

I have questions for the initial aggregation design and these questions are really important as part of the implementation of the cubes.

1. Before we launch the aggregation design wizard, we create user-hierarchies, after we process the cube, these user-hierarchies are then as the first initial aggregations? How can we evaluate the benefits and cost of these aggregations based on user-hierachies?

2. When we create initial aggregations with 'Aggregation design wizard', setting up the value for 'performance increase', how this fits into (work together with user_hierarchies?). Say, we set up the value for 'performance increase' as 30%, the initial user-hierarchies aggregations may not meet this goal, in this case, how the system works to meet this goal? Will it create other aggregations together with the user-hierarchies based aggregations? And where can we see these information? And how can we evaluate the aggregations after its design?

Thanks a lot and I think they are very very necessary for me to get these information and I think they are very very helpful. And I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hi,

I am not to sure if I get the what you are asking, but the idea of aggregations is to help performance when querying the cube. So you should be able to test the benefits of any aggregation in the time it takes to retrieve the data. You are going to have to clear the cache as well when doing this, as the cache will probably play an adverse effect on your results if you don't.

If the performance is not met with the wizard i would suggest using the aggregation design tool and create your own that help specific queries you have. One aggregation may help improve one query but not others, and the wizard may not always get it right. It is a matter of trial and error, I would suggest using the profiler and see whether the queries you are running actually hit the aggregation or the partition. You can normally tell from the profiler where to start with the aggregations or even the queries themselves.

Hope that was some help, may have missed what you were asking

Cheers

Matt

|||

Hi, Matt,

Thanks a lot for the very helpful advices.

With best regards,

Yours sincerely,

Question on cube write back

Hi, all experts here,

I encountered a problem with cube writing back which contains measures with count aggregation rather than sum which said write back can not be allowed on measure groups with other aggregations rather than sum? Is it saying we can only enable write back on cubes with measure groups all based on sum aggregations?

But in my case, the measures needed to be counted rather than sum. What can I try to enable write back on this cube?

I am looking forward to hearing from you for your advices and thank you very much in advance.

With best regards,

Yours sincerely,

I'm having trouble thinking of a situation where you would need to writeback to a count measure, but that is beside the point.

One work around might be to create a new column in your fact table for this measure and pre-populate it with a value of 1 during ETL. Then you would be able to use a sum aggregation and get the same value as you would with a count measure. It would then also be possible to write back to this measure.

|||

Hi,

In my data, the facts are bit data, which only with values of 1 and 0 meaning different things respectively. Therefore I need to count these different situations happened in the fact table across different levels. There are no numeric data types at the moment which are for sum.

Thanks for your advices and I am looking forward to hearing from any of you here more ideas in this situation. Maybe you would give me some better ideas on how to deal with it.

With best regards,

Yours sincerely,

|||

Helen,

Explain me better what is the goal, I didnt understood... Explain me as I a dummy!

:-)

Regards!

|||

HI, PedroCGD,

The facts in the facts tables are with 0 or 1 values only. (0 and 1 therefore represent different meanings), therefore I dont see any point to sum these facts in any cube. Instead, count aggregation makes more sense which can count the numbers for the fact being 0 or 1.

Hope my explanation is clear for your help.

With best regards,

Yours sincerely,

|||It sounds like you have some flags which should modelled as separate attributes, not as measures. You would have one count measure and set either a single dimension or a combined dimension (also known as a 'junk' dimension). In this way you should be able to get the count by any combination of attributes.|||

Hi, Darren,

Thanks for your kind advices.

As you kindly advised, I will then need to update the underlying relational database (as the underlying data warehouse is designed by others)?Since at the moment, the data residing in the data warehouse does not have any junk dimensions at all. All those flags attributes are residing in the fact tables.

I would need to create single dimension for each flag attribute or combine some of the flag attributes together into one single dimension? But only one count measure for all these dimension attributes?

Will it be any good to complete these in data source view of the cube? Thanks again. And hope you can give me more further advices on it.

With best regards,

Yours sincerely,

Question on Cube aggregation percentage?

Hi, all experts here,

As I am a bit confused about the aggregation percentage of a cube, when we partition the cube, we need to set the aggregation percentage. But how does the analysis services really work here with the pre-defined cube aggregation percentage? Does not the analysis services engine only aggregates data on cube hierarchies and user-defined aggregations? (assume we have defined both the natural and user-defined hierarchies and user-defined aggregations), how can we determine how much percentage of aggregations it will be for that?

Hope it is clear for your advices and help and I am looking forward to hearing from you shortly and thanks a lot in advance.

With best regards,

Yours sincerely,

The aggregation percent number is only an approximate representation of the number of aggregations and estimated performance benifit of aggregations designed by the aggregation wizard. Note that it's a bit misleading in that you don't want 100% but something more like 30%. I recommend reading http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc for more information on this subject.|||

Hi, Matt,

Thanks a lot for your kind advices and the very helpful imformation.

With best regards,

Yours sincerely,