How to clone an existing partition into a new partiton using T-SQL in SQL Server 2000?

Question

Hi All,

   I have something in .net script to clone an existing  partition into a new one in a cube. However, I am supposed to write the same in T-SQL. Can someone help me with this asap?

    'Clone the existing partition into a new partition
    Set dsoOldPartition = dsoSelectedCube.MDStores.Item(sPartitionName)
    Set dsoNewPartition = dsoSelectedCube.MDStores.AddNew(sPartitionName & "_" & sYear & "_" & sMonth)
    dsoNewPartition.AggregationPrefix = dsoOldPartition.AggregationPrefix &"_" & sYear & "_" & sMonth & "_"
    dsoOldPartition.Clone dsoNewPartition, cloneMinorChildren
    dsoNewPartition.EstimatedRows = iEstimatedRows
    'Update the source table in the new partition
    sLQuote = dsoOldPartition.DataSources(1).OpenQuoteChar
    sRQuote = dsoOldPartition.DataSources(1).CloseQuoteChar
    sSourceTableNew = sLQuote & "dbo" & sRQuote & "." & sLQuote & sFactTablePrefix  & sYear & "_" & sMonth & sRQuote
    dsoNewPartition.SourceTable = sSourceTableNew
    ' Update the FromClause and JoinClause properties of the new partition.
    dsoNewPartition.FromClause = Replace(dsoOldPartition.FromClause, dsoOldPartition.SourceTable, sSourceTableNew)
    dsoNewPartition.JoinClause = Replace(dsoOldPartition.JoinClause, dsoOldPartition.SourceTable, sSourceTableNew)
    ' Update the SliceValue properties of the affected levels and dimensions to the correct values.
    'sDimensionName = sCubeName & "_" & sYear & "_" & sMonth & "^Date"
    sDimensionName = "Date"
    dsoNewPartition.Dimensions.Item(sDimensionName).Levels("(All)").SliceValue = "All Date"
    dsoNewPartition.Dimensions.Item(sDimensionName).Levels("Year").SliceValue = sYear
    dsoNewPartition.Dimensions.Item(sDimensionName).Levels("Quarter").SliceValue = sQuarter
    dsoNewPartition.Dimensions.Item(sDimensionName).Levels("Month").SliceValue = sMonth
    'Apply all the above changes
    dsoNewPartition.Update
    dsoSelectedCube.Update
    'Process the new partition
    dsoNewPartition.Process

Thanks
Bangaaram

---

Known is a DROP, Unknown is an OCEAN.

Answer 1

Hi Bangaaram, I would not use SSAS 2000 for the new development. In SSAS 2008 /2008 R2 you can capture XMLA script from your existing application using SQL Server profiler and then "replay" it to clone the partition.

This blog post has a sample how to execute XMLA using SSIS: http://bigorakine.wordpress.com/2009/10/30/create-new-ssas-partition-dynamically-xml-task/

Best regards, Vlad.

Answer 2

Hi Bangaaram, I would not use SSAS 2000 for the new development. In SSAS 2008 /2008 R2 you can capture XMLA script from your existing application using SQL Server profiler and then "replay" it to clone the partition.

This blog post has a sample how to execute XMLA using SSIS: http://bigorakine.wordpress.com/2009/10/30/create-new-ssas-partition-dynamically-xml-task/

Best regards, Vlad.

Thanks for the reply Vlad. But, all I have is only SSAS 2000 and should create a partition using SQL.

---

Known is a DROP, Unknown is an OCEAN.

Answer 3

Bangaaram, SSAS 2000 is an old product which is out of the support lifecycle, I would not recommend to make any new development with it.

This link has some information on partitioning in SSAS 2000 : http://technet.microsoft.com/en-us/library/cc917607.aspx#EOAA

In SQL 2000 you can use extended stored procedures : http://msdn.microsoft.com/en-us/library/aa214418(v=SQL.80).aspx check if you can convert your existing code or check this article, it contains sample VB code which you can use to create your extended stored procedure:

http://msdn.microsoft.com/en-us/library/aa902650(v=SQL.80).aspx#partitionsindw_topic3

But just a reminder, all these tools are obsolete.

Best regards, Vlad.

Answer 4

Bangaaram, SSAS 2000 is an old product which is out of the support lifecycle, I would not recommend to make any new development with it.

This link has some information on partitioning in SSAS 2000 : http://technet.microsoft.com/en-us/library/cc917607.aspx#EOAA

In SQL 2000 you can use extended stored procedures : http://msdn.microsoft.com/en-us/library/aa214418(v=SQL.80).aspx check if you can convert your existing code or check this article, it contains sample VB code which you can use to create your extended stored procedure:

http://msdn.microsoft.com/en-us/library/aa902650(v=SQL.80).aspx#partitionsindw_topic3

But just a reminder, all these tools are obsolete.

Best regards, Vlad.

Hi Vlad,

I've tried something using the OLE Automated Procedures. But, I couldn't create a cube as a partition. All I was able to create is a separate cube instead of a partition. Also, I am not sure how to load it with data once I create it. Can you help me with that?

---

Known is a DROP, Unknown is an OCEAN.

Answer 5

Hi Bangaaram, you can create partitions for measures, not cube. To fill partitions with the data you need process cube and if data fell within  partition filter, it will be populated.

Please, take look on this article: http://msdn.microsoft.com/en-us/library/ms175688.aspx

Best regards, Vlad.

Answer 6

Hi Bangaaram, you can create partitions for measures, not cube. To fill partitions with the data you need process cube and if data fell within  partition filter, it will be populated.

Please, take look on this article: http://msdn.microsoft.com/en-us/library/ms175688.aspx

Best regards, Vlad.

Thank Vlad ut, I am still having some problem. This is the code I've been trying to run and it shows up the following error:

--CREATE PROCEDURE [dbo].[usp_ProcessCube]
DECLARE @Database	NVARCHAR(100)
DECLARE @Cube		NVARCHAR(100)
DECLARE @Partition	NVARCHAR(100) -- = null, -- If NULL, process the entire Cube
DECLARE @Server		NVARCHAR(100) --= 'localhost'
DECLARE @iEstimatedRows BIGINT
DECLARE @sFactTablePrefix NVARCHAR(100)
SET @Server = '10.x.x.x'
SET @Database = 'Customer'
SET @sFactTablePrefix = 'Market_Summary_Fact_'
SET @iEstimatedRows = '500000'
SET @Cube = 'MarketSummary'
SET @Partition = 'MarketSummaryMonthly'
/* Variables used to store object handles */
DECLARE @o_svr int, @o_db int, @o_cube int, @o_part int, @o_mds int
DECLARE @hr int
/* Different cube processing options. This SP uses "default" */
DECLARE @PROCESS_DEFAULT		INT
DECLARE @PROCESS_FULL			INT
DECLARE @PROCESS_REFRESH_DATA	INT
SET @PROCESS_DEFAULT 		= 0
SET @PROCESS_FULL 		= 1
SET @PROCESS_REFRESH_DATA 	= 2
-- Create a DSO.Server object:
EXEC @hr = sp_OACreate 'DSO.Server', @o_svr OUT
IF @hr <> 0
BEGIN
	PRINT 'Error at create server:'
	EXEC sp_OAGetErrorInfo @o_svr
	GOTO cleanup
END
-- Connect to the Server:
EXEC @hr = sp_OAMethod @o_svr, 'Connect', NULL, @Server
IF @hr <> 0
BEGIN
	PRINT 'Error at connect to server:'
	EXEC sp_OAGetErrorInfo @o_svr
	GOTO cleanup
END
-- Get the MDStores property from the Server:
EXEC @hr = sp_OAGetProperty @o_svr,'MDStores', @o_mds OUT
IF @hr <> 0
BEGIN
	PRINT 'Error at get getting Server MDStores:'
	EXEC sp_OAGetErrorInfo @o_svr
	GOTO cleanup
END
-- Get the Database from the MDStores:
EXEC @hr = sp_OAGetProperty @o_mds,'Item',@o_db OUT,@Database
IF @hr <> 0
BEGIN
	PRINT 'Error at get database:'
	EXEC sp_OAGetErrorInfo @o_mds
	GOTO cleanup
END
-- Get the MDStores Property from the Database:
EXEC sp_OADestroy @o_mds
EXEC @hr = sp_OAGetProperty @o_db,'MDStores', @o_mds OUT
IF @hr <> 0
BEGIN
	PRINT 'Error at get database MDStores:'
	EXEC sp_OAGetErrorInfo @o_db
	GOTO cleanup
END
-- Add New Partition to the existing Partition
DECLARE @NewPartition NVARCHAR(100)
SET @NewPartition = @Partition + '_2012_01'
	EXEC @hr = sp_OAGetProperty @o_mds,'AddNew',@o_part OUT, @NewPartition
	IF @hr <> 0
	BEGIN
		PRINT 'Error at get Parition:'
		EXEC sp_OAGetErrorInfo @o_mds
		GOTO cleanup
	END
 Clone the existing Partition:
EXEC @hr = sp_OAMethod @o_svr, 'Clone', NULL, @NewPartition
IF @hr <> 0
BEGIN
	PRINT 'Error in Cloning the New Partition'
	EXEC sp_OAGetErrorInfo @o_svr
	GOTO cleanup
END
 
IF @Partition IS NULL -- Process the entire Cube, not just a single partition
BEGIN
	EXEC @hr = sp_OAMethod @o_cube, 'Process', NULL, @PROCESS_DEFAULT
	
    IF @hr <> 0
	BEGIN
		PRINT 'Error at process Cube:'
		EXEC sp_OAGetErrorInfo @o_cube
		GOTO cleanup
	END
END
ELSE    -- Just Process the Specified Partition
BEGIN
   -- Get the MDStores property of the Cube:
	EXEC sp_OADestroy @o_mds
	EXEC @hr = sp_OAGetProperty @o_cube,'MDStores', @o_mds OUT
	IF @hr <> 0
	BEGIN
		PRINT 'Error at get Cube MDStores:'
		EXEC sp_OAGetErrorInfo @o_cube
		GOTO cleanup
	END
 
   -- Get the partition to process:
	EXEC @hr = sp_OAGetProperty @o_mds,'Item',@o_part OUT, @Partition
	IF @hr <> 0
	BEGIN
		PRINT 'Error at get Parition:'
		EXEC sp_OAGetErrorInfo @o_mds
		GOTO cleanup
	END
 
   -- Process the Partition:
	EXEC @hr = sp_OAMethod @o_part, 'Process', NULL, @PROCESS_DEFAULT
        IF @hr <> 0
	BEGIN
		PRINT 'Error at process Partition:'
		EXEC sp_OAGetErrorInfo @o_part
		GOTO cleanup
	END
END

-- And Unlock all Objects on the Server:
EXEC @hr = sp_OAMethod @o_svr, 'UnlockAllObjects'
IF @hr <> 0
BEGIN
	PRINT 'Error at unlock all server objects:'
	EXEC sp_OAGetErrorInfo @o_svr
	GOTO cleanup
END
cleanup:
IF @o_mds IS NOT NULL EXEC sp_OADestroy @o_mds
IF @o_Part IS NOT NULL EXEC sp_OADestroy @o_Part
IF @o_cube IS NOT NULL EXEC sp_OADestroy @o_cube
IF @o_db IS NOT NULL EXEC sp_OADestroy @o_db
IF @o_svr IS NOT NULL EXEC sp_OADestroy @o_svr

All I can do is create a Cube but not partition with this. And that cube has the partition within it. And being created as a separate cube, I couldn't process the Cube/Partition.

---

Known is a DROP, Unknown is an OCEAN.