dimension level member count, cube aggregations and cube re-processing
-
Before fully re-processing a cube, is it important to also update the member count of each dimension level, given that:
(a) the member count of the dimension levels stay the same once they are set during dimension creation (observed behavior), and
(b) Analysis Services relies on the member counts to create the proper aggregations (according to the documentation)
-
Statement or question?
-
correct if it's a statement, hard to answer if it's question
Steve.
-
I thought the question (How can I reset the dimension member counts automatically?) was about to follow.
-
I think he's asking if he should re-process the dimensions every time he reprocesses his cube, for performance and accuracy reasons.
Accuracy is not affected by not re-processing dimensions, as long as you set the error count to zero. OLAP will throw an error if you try to add a fact member without having a dimension member for it. In this case, incremental processing of the dimensions will fix that, and you won't have to re-process the entire cube.
Incremental fact and dimension updates is what you want, if at all possible. Otherwise your going to have to put limits on how big your cubes can get.
However, if you do fully re-process the cube, do fully re-process all dimensions as well, for both accuracy and performance (on query).
cl
Signature is NULL
-
Hi guys,
Sorry for the confusion. I meant to ask if it is really important to keep the member count updated before re-processing. And, yes, I was going to ask how I can reset the dimension member counts automatically, if so.
-
I'm not sure what you mean by "Count". you should use DTS as part of your ETL process...this allows incremental and full processing.
cl
Signature is NULL
-
He means the counts (member counts) against each of the levels within each dimension. These influence the aggregation wizard (along with the anticipated fact table size) when calculating aggregations.
I would be guessing that keepin gthe counts correct will make little difference unless you're re-running the agg wizard regularly, because once the aggregations are set they won't change until you change them (manually, wizard, or usage based).
cheers,
Steve.
-
I see... So the aggregations are not updated along with the other cube objects even when the cube is fully re-processed... Isn't this a bad thing? I mean, suppose that I add new dimension and/or fact table rows and then re-process the cube and/or its dimensions. Then, the cube will have been loaded with the updated data but the aggregations are still for those of the old data? Is my understanding correct? Will this not make querying the cube inaccurate, or not as fast as it can be, or both?
Also, is there a way to update the aggregations from DTS?
-
If your fact table or dimension tables grow and you reprocess your cube, your aggregations will remain unchanged, unless you chose to amend them manually. However, if you add a new dimension to a cube AS notifies you that your “storage settings will be deleted”, meaning you will have to redesign you aggregations.
As Steve has mentioned, the member counts on your dimensions affect the way the aggregations are built.
As to how important is it to update you member count each time you reprocess depends mainly on how much extra/less data there is, and at what level, for example you have a dimension that is 5 levels and the member counts are 1,2 3,75,5000, if you add 2 new members to level 5 there’s not much reason to re-calculate the members, adding 15000, however, is a different matter. Also, if you add 60 new members to level 3, you may want to consider re-calculating the members.
Here’s a Microsoft document that tries to explain the 1/3 rule of aggregations.
go.microsoft.com/fwlink/?linkid=23156
If you can get your head round it you’ll get an idea on how the aggregations are built and just how unimportant it is, and more that that you’ll be a man my son.
-
Christian, just to confirm what wildh has said re: aggs and with reference to your question after my post, the aggregations don't change but the values that are being aggregated definitely do change (otherwise the cube values would be wrong and the world would indeed be flat).
Keep in mind that there are two parts to aggregations: i) what levels of what deimensions will be aggregated; and ii) the actual aggregation values.
Part i) above is what is created using the agg wizards (basically either based on usage or member counts)- ie Design.
Part ii) above uses the aggregation "maps" created in part i and then generates the aggregations appropriately - ie population
The doc that wildh has referenced is a subset of the performance (or possibly the operations) guide for AS, both are worth reading.
If you're having trouble "seeing" the aggregations in your head, try using the aggregation/partition tool that comes with SSABI, it shows each aggregation in a row based format. Basically the aggregation is a row, and each column show you the level within each dim that is being aggrgated at.
HTH,
Steve.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply