Processing methodology

we have 3 large fact tables with combined rec cnts of 32 million. These 3 fact tables share order attribute that we would like to pull out and create a order dimension so that the facts tables don't have to contain the attributes. I've tried update processing of this dimension but the time it takes is not much different than a full process. I would like to know if there is anything like a incremental processing of this dimension and how would I set that up?

I assume that you are using Analysis Services 2005?

Actually, the "Process Update" method is slow than doing a "Process Full" on a dimension. The reason for this is, that "Process Update" reads the entire dimension table and compares the content of this with the existing, processed dimension. Any differences are then updated in the OLAP dimension. "Process Full", on the other hand, does not have to make any comparisons - it simply reads the entire dimension table and builds the OLAP dimension.

Now, the main reason for using "Process Update" is that is does not invalidate your processed measure groups. If you do a "Process Full" on the dimension, you are forced into doing a "Process Full" on any measure group that contains this dimension. Note, that "Process Update" will invalidate the aggregations on any associated measure group, provided that changes have occured to the dimension. This means, that a "Process Update" on the dimension will have to be followed by a "Process Index" on the associated measure groups in order to get aggregations back.

Process AS Database

Hi There,

I have one Cube database with couple of facttable and dimensions (SQL 2005), one of the dimension is invoice dimension which has 2 million records, and as you know it is growing every day with new invoice.

I setup SSIS process cube task to process the whole AS Database, however it is failed on me. then I open up my project, try to process one cube at a time, it still gives me error and it is releated my invoice dimension, then I try to process the invoice dimension first, then process the cube, then there is no error.

so the question is: for chaning dimension (new entry every day), should I process this first before processing the cube? I thought when you process the whole AS Database, it should automatically process the dimension then cube.

Thanks

When you are getting the errors, it sounds like you have fact records that have keys pointing to dimension values that have not yet been pulled into the invoice dimension. Processing the invoice dimension first or processing the whole database will avoid this problem. Still, there are other approaches.

First, SSAS includes Unknown member logic. There are a series of properties set on both the dimension and on the cube that determine how this situation is handled. You can configure your cube to recognize the missing dimension value, substitute the Unknown member in its place, and move on. (Use Books Online to help you get started with this. Search for "Unknown Member".)

I must make clear I personally do not like this approach. There are better ways to handle missing/late arriving dimension values, but there are situations where this may be appropriate.

Second, I don't know much about your model, but it sounds like you are working with what Kimball would call a degenerate dimension which is addressed in SSAS as a "fact dimension". Take a look at the Books Online document "Defining a Fact Relationship ". Using fact dimensions may offer some processing advantages to you.

Thanks,
Bryan Smith

|||

Hi Bryan,

Thanks a lot for quick reply. I realize there is error configuration for unknow member, but in my case, this is, as you pointed out correctly, "late arriving dimension", it really should not be in "Unknown", you should think As should be smart enough to process the dimension first, then cube, but it seems not the case here.