SSAS2005 - When do partitions need to be processed?

I've become increasingly frustrated while trying to figure out why some
dimensions, when process updated, require a processing of all the partitions
in the measure group within that processing job.

We've got a cube with a single measure group with anywhere from 90 to 113
partitions (90 day partitions and a partition added every hour through the
day). Every hour we update this cube adding a new partition to the measure
group, also process updating all the dimensions within it. When three out of
eight dimensions process during the hourly job, this adds 30 minutes of
processing time every hour. It's unacceptable and I've spent days on trying
to figure out why this happens.

At first I thought it was aggregations, however I removed all aggregations.
Second, I thought it may be due to only dimensions that have hierarchies,
however processing the Date dimension does not process all the partitions.
Third, I thought it may be because data changed. However, I can update the
dimension five times in a row without adding any data or partitions to the
cube and still get get the processing of the partitions. Fourth, does this
only happen to reference dimensions. Not the case.

My question is this: when and why does this happen?


See examples below:


Example one: Does NOT process the partitions within the measure group:


<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine";>
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";>
<Object>
<DatabaseID>Sales</DatabaseID>
<DimensionID>v Dim Calendar</DimensionID>
</Object>
<Type>ProcessUpdate</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Dimension 'Date' completed successfully.
Start time: 5/12/2006 3:46:22 PM; End time: 5/12/2006 3:46:25 PM;
Duration: 0:00:03
Processing Dimension Attribute '(All)' completed successfully.
Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:23 PM;
Duration: 0:00:00
Processing Dimension Attribute 'Year' completed successfully. 3 rows have
been read.
Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM;
Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_vDim_Calendar].[CalendarYEAR] AS [dbo_vDim_CalendarCalendarYEAR0_0]
FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar]
Processing Dimension Attribute 'Day Of The Week' completed successfully. 8
rows have been read.
Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM;
Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_vDim_Calendar].[DayNameOrderID] AS
[dbo_vDim_CalendarDayNameOrderID0_0],[dbo_vDim_Calendar].[DayName] AS
[dbo_vDim_CalendarDayName0_1]
FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar]
Processing Dimension Attribute 'Day Of Month' completed successfully. 32
rows have been read.
Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM;
Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_vDim_Calendar].[DayNumInMonth] AS [dbo_vDim_CalendarDayNumInMonth0_0]
FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar]
Processing Dimension Attribute 'Month' completed successfully. 18 rows
have been read.
Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM;
Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_vDim_Calendar].[YEARMo] AS
[dbo_vDim_CalendarYEARMo0_0],[dbo_vDim_Calendar].[MonthName] AS
[dbo_vDim_CalendarMonthName0_1],[dbo_vDim_Calendar].[CalendarYEAR] AS
[dbo_vDim_CalendarCalendarYEAR0_2]
FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar]
Processing Dimension Attribute 'Day' completed successfully. 498 rows have
been read.
Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM;
Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_vDim_Calendar].[FullDate] AS
[dbo_vDim_CalendarFullDate0_0],[dbo_vDim_Calendar].[DayLongName] AS
[dbo_vDim_CalendarDayLongName0_1],[dbo_vDim_Calendar].[YEARMo] AS
[dbo_vDim_CalendarYEARMo0_2]
FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar]
Processing Dimension Attribute 'CalendarDWID' completed successfully. 498
rows have been read.
Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM;
Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_vDim_Calendar].[CalendarDWID] AS
[dbo_vDim_CalendarCalendarDWID0_0],[dbo_vDim_Calendar].[FullDate] AS
[dbo_vDim_CalendarFullDate0_1],[dbo_vDim_Calendar].[DayNameOrderID] AS
[dbo_vDim_CalendarDayNameOrderID0_2],[dbo_vDim_Calendar].[DayNumInMonth] AS
[dbo_vDim_CalendarDayNumInMonth0_3]
FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar]
Processing Hierarchy 'Date' completed successfully.
Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM;
Duration: 0:00:01
Processing Cube 'Sales Current' completed successfully.
Start time: 5/12/2006 3:46:25 PM; End time: 5/12/2006 3:46:31 PM;
Duration: 0:00:06
Processing Measure Group 'Sales measures' completed successfully.
Start time: 5/12/2006 3:46:29 PM; End time: 5/12/2006 3:46:29 PM;
Duration: 0:00:00





Example two: Processes all the partitions within the measure group.


<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine";>
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";>
<Object>
<DatabaseID>Sales</DatabaseID>
<DimensionID>v Dim Business Unit Current</DimensionID>
</Object>
<Type>ProcessUpdate</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Dimension 'BU2' completed successfully.
Start time: 5/12/2006 3:41:54 PM; End time: 5/12/2006 3:41:58 PM;
Duration: 0:00:04
Processing Dimension Attribute 'Business Unit Flat' completed
successfully. 27 rows have been read.
Start time: 5/12/2006 3:41:56 PM; End time: 5/12/2006 3:41:57 PM;
Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_vDim_BusinessUnitCurrent].[BusinessUnitID] AS
[dbo_vDim_BusinessUnitCurrentBusinessUnitID0_0],[dbo_vDim_BusinessUnitCurrent].[BusinessUnitName]
AS
[dbo_vDim_BusinessUnitCurrentBusinessUnitName0_1],[dbo_vDim_BusinessUnitCurrent].[ParentBusinessUnitID]
AS [dbo_vDim_BusinessUnitCurrentParentBusinessUnitID0_2]
FROM [dbo].[vDim_BusinessUnitCurrent] AS [dbo_vDim_BusinessUnitCurrent]
Processing Dimension Attribute '(All)' completed successfully.
Start time: 5/12/2006 3:41:55 PM; End time: 5/12/2006 3:41:55 PM;
Duration: 0:00:00
Processing Hierarchy 'Business Unit' completed successfully.
Start time: 5/12/2006 3:41:56 PM; End time: 5/12/2006 3:41:57 PM;
Duration: 0:00:01
Processing Cube 'Sales Current' completed successfully.
Start time: 5/12/2006 3:41:58 PM; End time: 5/12/2006 3:42:57 PM;
Duration: 0:00:59
Processing Measure Group 'Sales measures' completed successfully.
Start time: 5/12/2006 3:42:02 PM; End time: 5/12/2006 3:42:55 PM;
Duration: 0:00:53
Processing Partition 'H_2006051214' completed successfully.
Start time: 5/12/2006 3:42:03 PM; End time: 5/12/2006 3:42:05 PM;
Duration: 0:00:02
Processing Partition 'H_2006051212' completed successfully.
Start time: 5/12/2006 3:42:07 PM; End time: 5/12/2006 3:42:08 PM;
Duration: 0:00:01
Processing Partition 'H_2006051200' completed successfully.
Start time: 5/12/2006 3:42:10 PM; End time: 5/12/2006 3:42:12 PM;
Duration: 0:00:02
Processing Partition 'H_2006051205' completed successfully.
Start time: 5/12/2006 3:42:15 PM; End time: 5/12/2006 3:42:18 PM;
Duration: 0:00:03
Processing Partition 'H_2006051202' completed successfully.
Start time: 5/12/2006 3:42:20 PM; End time: 5/12/2006 3:42:21 PM;
Duration: 0:00:01
Processing Partition 'H_2006051204' completed successfully.
Start time: 5/12/2006 3:42:23 PM; End time: 5/12/2006 3:42:24 PM;
Duration: 0:00:01
Processing Partition 'H_2006051201' completed successfully.
Start time: 5/12/2006 3:42:26 PM; End time: 5/12/2006 3:42:27 PM;
Duration: 0:00:01
Processing Partition 'H_2006051203' completed successfully.
Start time: 5/12/2006 3:42:29 PM; End time: 5/12/2006 3:42:31 PM;
Duration: 0:00:02
Processing Partition 'H_2006051213' completed successfully.
Start time: 5/12/2006 3:42:32 PM; End time: 5/12/2006 3:42:34 PM;
Duration: 0:00:02
Processing Partition 'H_2006051208' completed successfully.
Start time: 5/12/2006 3:42:35 PM; End time: 5/12/2006 3:42:37 PM;
Duration: 0:00:02
Processing Partition 'template' completed successfully.
Start time: 5/12/2006 3:42:38 PM; End time: 5/12/2006 3:42:39 PM;
Duration: 0:00:01
Processing Partition 'H_2006051206' completed successfully.
Start time: 5/12/2006 3:42:41 PM; End time: 5/12/2006 3:42:42 PM;
Duration: 0:00:01
Processing Partition 'H_2006051209' completed successfully.
Start time: 5/12/2006 3:42:44 PM; End time: 5/12/2006 3:42:45 PM;
Duration: 0:00:01
Processing Partition 'H_2006051210' completed successfully.
Start time: 5/12/2006 3:42:47 PM; End time: 5/12/2006 3:42:48 PM;
Duration: 0:00:01
Processing Partition 'H_2006051207' completed successfully.
Start time: 5/12/2006 3:42:50 PM; End time: 5/12/2006 3:42:52 PM;
Duration: 0:00:02
Processing Partition 'H_2006051211' completed successfully.
Start time: 5/12/2006 3:42:53 PM; End time: 5/12/2006 3:42:55 PM;
Duration: 0:00:02


.

Relevant Pages

  • Re: SSAS2005 - When do partitions need to be processed?
    ... dimensions are updated and a new partition is added to the cube. ... With AS2005 there is a proactive caching option at the dimension level, ... Duration: 0:00:03 ...
    (microsoft.public.sqlserver.olap)
  • Re: SSAS2005 - When do partitions need to be processed?
    ... dimensions are updated and a new partition is added to the cube. ... With AS2005 there is a proactive caching option at the dimension level, ... Duration: 0:00:03 ...
    (microsoft.public.sqlserver.olap)
  • Re: SSAS2005 - When do partitions need to be processed?
    ... The cache of the cube himself is updated, this mean that adding new content ... partitions (90 day partitions and a partition added every hour through the ... Duration: 0:00:03 ... Processing Dimension Attribute '' completed successfully. ...
    (microsoft.public.sqlserver.olap)
  • Re: SSAS2005 - When do partitions need to be processed?
    ... dimensions are updated and a new partition is added to the cube. ... With AS2005 there is a proactive caching option at the dimension level, ... Duration: 0:00:03 ... Processing Partition 'H_2006051214' completed successfully. ...
    (microsoft.public.sqlserver.olap)
  • Re: Calculating Max Usage with OLAP
    ... cases where you are filtering on members from the majority of your dimensions. ... but a tool called 'Partition Manager' ... it will give you details of how to find which aggregations you ... > dimension with two levels and 24 members. ...
    (microsoft.public.sqlserver.olap)
Loading