Re: SSAS2005 - When do partitions need to be processed?

generally a reprocess is required when there is a change in a hierarchy.
for example, if an employee is in New York and change to Boston, then the
hierarchy changes which required a process of the cubes to recalculates the
aggregations correctly.
Changing a label (like the name of the customer) required only a simple
update (if the key column is not the same as the label column).

if you anticipate this type of changing, you have to use the slow changing
feature in the dimension. I have not use it in AS2005 but in AS2000 yes.
this option will slow down accesses to the cubes because the system will
keep only aggregates at the top (all member) and bottom levels (the
employee)
intermediate levels are recalculate when asked by the user.
So when a employee change, there is no need to reprocess the cubes because
the aggregation for boston and new york are not stored on the disk.

With AS2005 there is a proactive caching option at the dimension level, but
I have not use it, if this caching works like in a cube... this could help
you by reaggregate data when a table in the database change.

take a close look at the proactive caching features.

"Jesse O." <jesperzz@xxxxxxxxxxx> wrote in message
news:uN%233so8dGHA.4108@xxxxxxxxxxxxxxxxxxxxxxx
No, no slice set. We're using MOLAP. Thanks for your suggestion of
proactive caching.

I'm still confused as to why some dimensions process partitions while
others don't.

Perhaps someone from MS could chime in?

TIA.

Jesse.


"Jeje" <willgart@xxxxxxxxxxx> wrote in message
news:Ocp0ODrdGHA.4532@xxxxxxxxxxxxxxxxxxxxxxx
well...

when you create partitions, do you associate this partition to a specific
slice of the cube?

use realtime partitions and the proactive caching feature. and/or
incremental processing.

I have seen a webcast where a billion rows database and the associated
cubes are updated in realtime.
The cache of the cube himself is updated, this mean that adding new
content in the cube fill the cache at the same time so the cube continue
to be in warm mode. A normal process remove the cache and the cube become
cold and slower to respond during the first accesses.

some articles:
http://www.microsoft.com/technet/prodtechnol/sql/2005/rtbissas.mspx
http://sqljunkies.com/WebLog/sqlbi/archive/2004/10/09/4542.aspx


"Jesse O." <jesperzz@xxxxxxxxxxx> wrote in message
news:%23vQmrehdGHA.3388@xxxxxxxxxxxxxxxxxxxxxxx
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 ... Processing Partition 'H_2006051214' completed successfully. ...
    (microsoft.public.sqlserver.olap)
  • Re: SSAS2005 - When do partitions need to be processed?
    ... The cache of the cube himself is updated, ... I can update the dimension five times in a row without adding ... Duration: 0:00:03 ... Processing Partition 'H_2006051214' completed successfully. ...
    (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)
  • Remote datasource cannot be found in the metadata
    ... Duration: 0:00:00 ... Processing Dimension Attribute '' completed successfully. ... Processing Dimension Attribute 'Country' completed successfully. ... Errors in the OLAP storage engine: An error occurred while the dimension, ...
    (microsoft.public.sqlserver.olap)
Loading