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

We already snowflake, good suggestion however.

What do you mean in "fixed" dimensions?


"Shred" <Shred@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E9924DF9-C043-4019-85A6-F5E16157B250@xxxxxxxxxxxxxxxx
One of the things that you could look at to speed up updates is
snowflaking
your large dimensions. Here is a good Blog on the issue.
http://sqljunkies.com/WebLog/sqlbi/archive/2005/10/07/17040.aspx
I have not had the opportunity to test this, but my dimensions are not
nearly as large and we only update daily.

I am also going to be setting most of my dimensions to fixed for my 2nd
itteration to try and stop the index rebuilds that need to happen all the
time.

"Jesse O." wrote:

I'm not so sold into Proactive Caching yet, at least in our environment.

We have a typical datawarehouse which is loaded and updated every hour
through a batch. Our process cube job runs every five minutes to check to
see if a batch has completed. If a batch has completed, the associated
dimensions are updated and a new partition is added to the cube. When
there
are 24 batches completed for that day, we delete the hour partitions for
that day and create and process a day partition.

The amount of data we process every hour is large. Two of our dimensions
have two million plus members and our largest fact table grows by four
million rows an hour. We're planning on storing 90 days of data in this
cube
which is processed hourly, and another cube which processes daily and has
all historical data. Our pain point right now is the length to process
the
dimensions every hour. It constitutes 95% of our processing time.

I don't see a real need for proactive caching since it generally seems
like
it's done against the transaction system, not the datawarehouse. Part of
that is due to my inexperience in SSAS2005 and not really having a good
grasp on proactive caching.

What do you guys think?










"Jeje" <willgart@xxxxxxxxxxx> wrote in message
news:%23DAflH9dGHA.564@xxxxxxxxxxxxxxxxxxxxxxx
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


.

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?
    ... 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: Remove dimensions with DSO doesnt show up correctly in Analys
    ... DSO.Dimension dimension = cube.Dimensions; ... // A cube with a single partition has one with the same name as the cube. ... > 3) Put back your dimension and remove the dimension with Analysis Manager. ...
    (microsoft.public.sqlserver.olap)
  • Re: one fact table devided into two cubes
    ... > When you create a cube you also have a partition. ... > partition on the last screen of the wizard you get a button advanced. ... >> I'm having a problem with a huge dimension. ... >> The large dimension is customers, and they wish to have the cube divided ...
    (microsoft.public.sqlserver.olap)
  • SSAS2005 - When do partitions need to be processed?
    ... Every hour we update this cube adding a new partition to the measure ... however processing the Date dimension does not process all the partitions. ... Duration: 0:00:03 ... SQL queries 1 ...
    (microsoft.public.sqlserver.olap)