January 22, 2012 at 6:23 pm
You cannot vote on your own post
0
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 Stored Procedure 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).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
End Function
Thanks
Bangaaram
-----------------------------------------------------------------------
Known is a DROP, Unknown is an OCEAN.:ermm:
January 23, 2012 at 2:33 am
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic1239976-17-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2012 at 4:35 pm
GilaMonster (1/23/2012)
Please don't cross post. It just wastes peoples time and fragments replies.No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic1239976-17-1.aspx
The topic belongs to both the sections. So, I thought I would get help in either of the thread.
-----------------------------------------------------------------------
Known is a DROP, Unknown is an OCEAN.:ermm:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply