Hi All,

I need to create a new partition on a Cube using T-SQL and I am not much aware of either the Cubes or the ActiveX script. Can someone please help me in writing a T-SQL script for creating this partition on a cube.

Select Case iMonth
Case 1,2,3
sQuarter = "1"
Case 4,5,6
sQuarter = "2"
Case 7,8,9
sQuarter = "3"
Case 10,11,12
sQuarter = "4"
End Select
CreateNewPartition("CustomerLog")
CreateNewPartition("CustomerLogUpdates")

Main = DTSTaskExecResult_Success

End Function


'************************************************* ************************************************** ***********************************************
'This function creates the new cube partition
'************************************************* ************************************************** ***********************************************
Function CreateNewPartition( sCubeName)

Dim sSourceTableNew
Dim sLQuote
Dim sRQuote
Dim sPartitionName
Dim sDimensionName

sPartitionName = sCubeName
Set dsoServer = CreateObject("DSO.Server")
dsoServer.Connect (sServerName)
Set dsoDB = dsoServer.MDStores(sDatabaseName)
Set dsoSelectedCube = dsoDB.MDStores.Item(sCubeName)

'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).Le vels("(All)").SliceValue = "All Date"
dsoNewPartition.Dimensions.Item(sDimensionName).Le vels("Year").SliceValue = sYear
dsoNewPartition.Dimensions.Item(sDimensionName).Le vels("Quarter").SliceValue = sQuarter
dsoNewPartition.Dimensions.Item(sDimensionName).Le vels("Month").SliceValue = sMonth

'Apply all the above changes
dsoNewPartition.Update
dsoSelectedCube.Update

'Process the new partition
dsoNewPartition.Process


End Function

-----------
Thanks
Mr.Bean

No one has an idea about this?

Can someone look into this? I desperately need a help with this issue..