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

Thanks Adrian.

We record a bunch of things for our processing in order to know which
partitions or dimensions need to be processed.

Can this be done in parallel processing? Ever see someone do it?


Table schema:
CREATE TABLE [dbo].[CubeProcessingLogSSAS2005](

[CubeProcessingLogID] [int] IDENTITY(1,1) NOT NULL,

[EventDescription] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,

[IsSuccess] [tinyint] NULL,

[RecordActive] [tinyint] NULL,

[EventStartTime] [datetime] NULL,

[EventEndTime] [datetime] NULL,

[DatabaseName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CubeName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ObjectName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ObjectType] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[PartitionName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SelectStatement] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ProcessingType] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CalendarDWID] [int] NULL,

[TimeOfDayDWID] [int] NULL,

CONSTRAINT [pk_CubeProcessingLogSSAS2005] PRIMARY KEY CLUSTERED

(

[CubeProcessingLogID] ASC





"Adrian Dumitrascu [MS]" <adriandu@xxxxxxxxxxxxx> wrote in message
news:eGxY6%23eeGHA.3364@xxxxxxxxxxxxxxxxxxxxxxx
To process multiple objects at once with AMO (in a Batch, in Parallel),
this is sample code (sorry it's C#, I don't have it available in VB.NET):

Server s = new Server();

s.Connect("localhost");

try
{
// We'll put AMO in capture-mode; this means that all .Process, .Update
etc commands
// are saved in a log (Server.CaptureLog), instead of being sent to the
server. And then
// we can run the commands from the log in a single Batch (eventually
in Parallel).
s.CaptureXml = true;

// Now we'll call the .Process methods as normally.
s.Databases["my database id"].Dimensions["my dimension id1"].Process();
s.Databases["my database id"].Dimensions["my dimension id2"].Process();

// Once we called Process on all the objects we want, we'll exit
capture-mode and
// we'll run the script.
s.CaptureXml = false; // we exit the capture mode
XmlaResultCollection results = s.ExecuteCaptureLog(true, true); //
transactional and parallel

// Now we'll check the results
foreach (XmlaResult result in results)
{
foreach (XmlaMessage message in result.Messages)
{
Console.WriteLine(message.Description);
if (message is XmlaError)
{
// the processing failed, there is at least one error
reported here.
}
}
}
}
finally
{
s.Disconnect();
}


Adrian Dumitrascu

"Jesse O." <jesperzz@xxxxxxxxxxx> wrote in message
news:%23BQl6KReGHA.564@xxxxxxxxxxxxxxxxxxxxxxx
Thanks for all the replies guys, I really appreciate it.

As far as the time only taking 59 seconds. That's with a limited dataset.

This is the AMO code we use to process our dimensions. Any suggestions?



Public Sub ProcessAllDimensions()

Dim oDimension As CubeDimension

Dim BeginTime As DateTime



For Each oDimension In objCube.Dimensions

Try

BeginTime = Now

InsertCubeProcessingLog("Process Dimension All Begin", 1, 0, BeginTime,
Now, objDatabase.Name, objCube.Name, oDimension.Name, "Dimension", "",
"", "", "", "")

oDimension.Dimension.Process(ProcessType.ProcessUpdate)

InsertCubeProcessingLog("Process Dimension All End", 1, 1, BeginTime,
Now, objDatabase.Name, objCube.Name, oDimension.Name, "Dimension", "",
"", "", "", "")

Catch

Dim ErrorReplace As String = Err.Description

ErrorReplace = ErrorReplace.Replace("'", "")

InsertCubeProcessingLog("Process Dimension All Failed: " & Err.Number &
" - " & ErrorReplace, 0, 0, BeginTime, Now, objDatabase.Name,
objCube.Name, oDimension.Name, "Dimension", "", "", "", "", "")

ObjectCleanup()

End Try

Next oDimension

End Sub







"Akshai Mirchandani [MS]" <akshaim@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:eoT5REFeGHA.4304@xxxxxxxxxxxxxxxxxxxxxxx
My guess here is that it is not aggregations but rather indexes being
processed.

I would suggest running Profiler against the server during processing
and seeing the events there -- the events for the partition should be
for ProcessIndex...

The actual processing of partitions doesn't appear to be significant
here though. It shows as:

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

Also, are you doing ProcessUpdate on all the dimensions in one operation
(using Batch/Parallel)? This would unify the cube processing into the
same transaction so that it only needs to happen once instead of
repeated for each of the dimensions.

HTH,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no
rights
Please do not send email directly to this alias. This alias is for
newsgroup
purposes only.

"Darren Gosbell" <jam@xxxxxxxxxxxxxxxxx> wrote in message
news:MPG.1ed32ff1a7122d298991a@xxxxxxxxxxxxxxxxxxxxx
This behaviour could be depend on whether your dimension has flexible
or
rigid attribute relationships. BOL did not have a very succinct
definition, but the following is from one of the tutorials:


...you can specify that the relationship is either flexible or rigid.
If
you define a relationship as rigid, Analysis Services retains
aggregations when the dimension is updated. If a relationship that is
defined as rigid actually changes, Analysis Services generates an error
during processing unless the dimension is fully processed


Time inherently has rigid relationships (you can't change which month a
particular date belongs to). But I believe all other dimension default
to flexible relationships which would mean that processing them would
result in all the aggregations (not the leaf level data) being dropped
and re-processed.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <#DAflH9dGHA.564@xxxxxxxxxxxxxxxxxxxx>, willgart@xxxxxxxxxxx
says...
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








.

Relevant Pages

  • Re: SSAS2005 - When do partitions need to be processed?
    ... My guess here is that it is not aggregations but rather indexes being ... The actual processing of partitions doesn't appear to be significant here ... Processing Cube 'Sales Current' completed successfully. ... aggregations when the dimension is updated. ...
    (microsoft.public.sqlserver.olap)
  • Re: long processing time
    ... currently it is processing 2 partitions in parallel. ... >1) Have you ran the Optimize Schema wizard in the Cube ... >the RDBMS (SQL Server or Oracle), ... >> The cube which took 9 days to process has time dimension ...
    (microsoft.public.sqlserver.olap)
  • Re: Disadvantages in using cube partitions
    ... processing of the cube or the complexity of development does not matter ... the main focus is access time on reports. ... I start managing partitions in my cube based on the time dimension. ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: SSAS2005 - When do partitions need to be processed?
    ... InsertCubeProcessingLog("Process Dimension All End", 1, 1, BeginTime, Now, ... The actual processing of partitions doesn't appear to be significant here ... Processing Cube 'Sales Current' completed successfully. ... With AS2005 there is a proactive caching option at the dimension level, ...
    (microsoft.public.sqlserver.olap)
  • Re: SSAS2005 - When do partitions need to be processed?
    ... InsertCubeProcessingLog("Process Dimension All Begin", 1, 0, BeginTime, Now, ... The actual processing of partitions doesn't appear to be significant here ... Processing Cube 'Sales Current' completed successfully. ... With AS2005 there is a proactive caching option at the dimension level, ...
    (microsoft.public.sqlserver.olap)