Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

process Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 3:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 8:11 AM
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
Post #1357855
Posted Wednesday, September 12, 2012 6:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 17, 2014 9:15 AM
Points: 451, Visits: 846
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
Post #1357931
Posted Wednesday, September 12, 2012 10:06 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 8:11 AM
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
Post #1358327
Posted Thursday, September 13, 2012 4:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 17, 2014 9:15 AM
Points: 451, Visits: 846
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


  Post Attachments 
screens.doc (15 views, 518.50 KB)
Post #1358452
Posted Monday, September 17, 2012 9:56 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 8:11 AM
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
Post #1360541
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse