Thursday, March 8, 2012

Best practice about creating partitions dynamically in AS2000 ?

Does anyone have a good article, link or just input concering the dynamic creation and processing of partitions in a cube.

The scenario where a large amount of data is daily loaded into the data warehouse. In the ETL there is some logic creating a new table when data is comming in for a new month (example: the table Fact_Q1_2007 is created when data is being recieved on 1. januar 2007 and Fact_Q2_2007 is created when data is recieved 1.april and so on)

The question is now - how do i set up the logic to create partitions dynamic in the cube and afterwards make sure that the partitions are being processed successfully.

One approach is to use the Decision Support Objects (DSO) API, which can be invoked from tools which support COM (like DTS):

http://msdn2.microsoft.com/en-us/library/aa936638(SQL.80).aspx

>>

Decision Support Objects Programmer's Reference

Microsoft? SQL Server? 2000 Analysis Services offers substantial opportunity for you to create and integrate custom applications. The server object model, Decision Support Objects (DSO), provides interfaces and objects that can be used with any COM automation programming language

...

>>

http://msdn2.microsoft.com/en-us/library/aa177800(SQL.80).aspx

>>

...

Use the following code to create an object of ClassType clsPartition:

'Assume an object (dsoCube) of ClassType clsCube exists

Dim dsoPartition As DSO.MDStore

Set dsoPartition = dsoCube.MDStores.AddNew("MyPartition")

>>

Typically, you might then clone an existing "template" partition, and update relevant properties like SourceTable:

http://msdn2.microsoft.com/en-us/library/aa177699(SQL.80).aspx

>>

Properties, clsPartition

...

SourceTable

The name of the fact table for the partition.

SourceTableFilter

Contains the WHERE clause of the SQL statement used to determine which source table rows are to be included in the partition.

>>

|||

Thanks

I read that there should be some tools in the SQL Server 2000 Ressource Kit, which lays only on MSDN. But the File Transfer Manager isn't able to download that file ("Application validation failed, transfers are not enabled"), there seems nowhere else to get that kit. Don't know how long microsoft will take to fix their File Transfer Manager......

No comments:

Post a Comment