process

Question

process

niha.736

Ten Centuries

Points: 1210
More actions
September 12, 2012 at 3:21 am

#393159

Hi All,
in my solution we need to process weekly sasles cube through a ssis package.
for this purpose there is a ssis package but it is not giving the best performance.
to improve the process speed we want to process current year partitions where data is changing.
dataflow of current package having 4 process tasks 1.dim process 2.as process task 3.weekly sales dim process 4.weekly sales cube process task.
can anybody suggest the best way, either it is XMLA script task or any other solution..
thanks,
Nihaa

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17463 More actions · Answer 1

There are a few ways this can be achieved, depending on your data. I'll describe a solution I did for a client recently and hopefully it'll help you out....

The way the client had their partitions set up was to have partitions by business month determined by the load date. In the package I had a number of variables which held xmla tasks to update, create and process partitions.

I created a table in an "Admin" database to hold the partition names, start and end dates and the date of last process.

The first thing the package does is gather data from this table to get the most recent partition names, start dates and the last processed date which are all passed to variables. Then an execute SQL task determines if the day that the package is running is the start of a new business month. If it is, then it uses the (dynamic) xmla to close off the previous partition and create a new one. The new partition are then logged in the admin table and are then processed. The dynamic XMLA variables are all run through an Analysis Services Execute DDL Task.

Since one of the fact tables is processed incrementally without partitioning the last processed date is used to do this.

Once it's finished the processing the last thing it does is update the last processed date in the admin table.

I hope that helps, as I said there are a number of ways this can be done and it depends on the needs of the client.

I would attach the package but it's not a permitted file type

I'm on LinkedIn

niha.736 Ten Centuries Points: 1210 More actions · Answer 2

Hi Yayomayn thanks for ur reply,

if possible can u please provide me screenshots of the package

so that i can assume the flow in sequential manner.

Thanks for ur help

Niha

PB_BI SSCoach Points: 17463 More actions · Answer 3

I'm happy to help 🙂

There are two screens attached here (too big for one!)

You'll notice something that I missed from my original description. If it's a new business year then the perevious years' monthly partitions are merged in a For Each Loop before being closed off.

If you PM me your email address I can send you the package itself.

I'm on LinkedIn

niha.736 Ten Centuries Points: 1210 More actions · Answer 4

Hi thanks for the screens,

now i have devided my package into two, 1 for weekly sales and other for pos.

"""**The only problem now is that WeeklySales cube needs always full processing in order to update data. Any other processing modes will not update the data correctly

If you any ideas on that, it would be great***"""

currently my weekly sales package contains 3 tasks,

1.

select isnull(max(AuditKey) + 1,0) as AuditKey

from DimAudit

where PkgName = ?

and TableName = ?

2. AS processing task

3.INSERT INTO [PSDW].[dbo].[DimAudit]

([AuditKey]

,[TableName]

,[PkgName]

,[PkgGUID]

,[PkgVersionGUID]

,[PkgVersionMajor]

,[PkgVersionMinor]

,[ExecStartDT]

,[ExecStopDT]

,[ExtractRowCount]

,[InsertRowCount]

,[UpdateRowCount]

,[ErrorRowCount]

,[TableInitialRowCount]

,[TableFinalRowCount]

,[TableMaxSurrogateKey]

,[SuccessFullProcessingInd]

,[ExecutionID])

VALUES

(?,?,?,?,?,?,?,?,GETDATE(),?,?,?,?,NULL,NULL,NULL,?,?)

can anybody suggest something ??

Thanks

Niha