Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


process


process

Author
Message
niha.736
niha.736
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 233
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
PB_BI
PB_BI
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1290 Visits: 2402
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
niha.736
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 233
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
PB_BI
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1290 Visits: 2402
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
Attachments
screens.doc (15 views, 518.00 KB)
niha.736
niha.736
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 233
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search