Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2011
    Posts
    29

    Question Unanswered: How to Create a New Partition on a Cube using T-SQL in SQL Server 2000?

    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

  2. #2
    Join Date
    Dec 2011
    Posts
    29
    No one has an idea about this?

  3. #3
    Join Date
    Dec 2011
    Posts
    29
    Can someone look into this? I desperately need a help with this issue..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •