I've come upon some time processing length problems while processing a
dimension which has a Usage of Parent.

We have a 2005 OLAP db with a single cube, nine measure groups. Each of
these measure groups have about 100 partitions for a total of 900
partitions. We process every hour, 24-7. A good majority of the dimensions
are updated (Process Update) every hour as part of this processing. Users
have high expecations for the data to get in there early.


The non parent child dimensions process like this:

--Processing Dimension "Time" completed successfully -duration 20 seconds
--Processing Cube "Sales" completed successfully --duration 2 seconds
--Processing Measure Goup "Sales measures" completed
uccessfully --duration 1 second

They run relatively quick. Notice they do not process each of the individual
partitions in the measure groups.


Now notice how a parent child dimesion processes:

--Processing Dimension "Business Unit" completed successfully -duration 7
seconds
--Processing Cube "Business Unit" completed successfully --duration 28
minutes
--Processing Measure Goup "Sales measures" completed
uccessfully --duration 27 minutes
--Processing Partition 'D_20060504' completed successfully.
<and so forth for every partition>

This update takes nearly a half hour because it also processes the 900 other
partitions. We only experience this behavior when a Process Update is ran on
a dimension with an attribute of Parent.

I've ruled out aggregation design and whether the dimension is materialized
or not. We have another OLAP database with a parent child dimension and it
performs no different.

My question is, what can I do to avoid all the processing of the partitions
when a parent child dimension is updated? This is absolutely killing the
time for the processing window (the processing of the PC dimension taking up
97% of the time window).

Help is very much appreciated with this issue.

jesse.

Anyone?

This is a mission critical thing. It'll stop the migration.


"Jesse O." <jesperzz (AT) hotmail (DOT) com> wrote

Hi Jesse,

I haven't personally encountered this exact scenario but, based on the
AS 2005 Processing paper below, an important issue may be whether you're
just adding new members to the dimension, or updating members as well:

http://msdn.microsoft.com/library/de.../en-us/dnsql90
/html/sql2k5_asprocarch.asp
...
Depending on the nature of the changes in the dimension table,
ProcessUpdate can affect dependent partitions. If only new members were
added, then the partitions are not affected. But if members were deleted
or if member relationships changed (e.g., a Customer moved from Redmond
to Seattle), then some of the aggregation data and bitmap indexes on the
partitions are dropped. The cube is still available for queries, albeit
with lower performance.

ProcessAdd is a new processing option for dimensions that did not exist
in Analysis Services 2000. It essentially optimizes ProcessUpdate for
the scenario where only new members are added. ProcessAdd never deletes
or updates existing members. It only adds new members. The user can
restrict the dimension table so that ProcessAdd reads only the new rows.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Deepak.

I've processed against a static dataset with the same results.

Anyone from MS? It looks like I'll have to open a case with them.

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Jesse, as another data point, does the ProcessAdd option on an empty set
produce the same (or different) results?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***