I am test the attached script from MSDN. However, I found that after execute
the script:

- The meta data page shows that the data slice has been changed by the code
("1998, Q4, 12").
- However, it still show 1997 when right click the new created partition and
choose "Edit", "next", "next"...

What's the real slice of the newly created partition? Is it a bug of
Analysis Service?

Thanks.


'/************************************************** *******************
' File: ClonePart.vbs
'
'Desc: This sample script creates a new partition in the FoodMart 2000
' Sales cube, based on the latest partition in the cube. The
' purpose of the script is to show the kinds of DSO calls that are
' used to clone a partition. The resulting partition is processed,
' but adds no data to the cube.
'
' Users of this script may want to delete the resulting partition
' after running the script and exploring the results.
'
' Parameters: None
'************************************************* ********************/

Call ClonePart

Sub ClonePart()

On Error Resume Next

Dim intDimCounter, intErrNumber
Dim strOlapDB, strCube, strDB, strAnalysisServer, strPartitionNew
Dim dsoServer, dsoDB, dsoCube, dsoPartition, dsoPartitionNew

' Initialize server, database, and cube name variables.
strAnalysisServer = "LocalHost"
strOlapDB = "FoodMart 2000"
strCube = "Sales"

' VBScript does not support direct use of enumerated constants.
' However, constants can be defined to supplant enumerations.
Const stateFailed = 2
Const olapEditionUnlimited = 0

' Connect to the Analysis server.
Set dsoServer = CreateObject("DSO.Server")
dsoServer.Connect strAnalysisServer

' If connection failed, then end the script.
If dsoServer.State = stateFailed Then
MsgBox "Error-Not able to connect to '" & strAnalysisServer _
& "' Analysis server.", ,"ClonePart.vbs"
Err.Clear
Exit Sub
End if

' Certain partition management features are available only
' in the Enterprise Edition and Developer Edition releases
' of Analysis Services.
If dsoServer.Edition <> olapEditionUnlimited Then
MsgBox "Error-This feature requires Enterprise or " & _
"Developer Edition of SQL Server to " & _
"manage partitions.", , "ClonePart.vbs"
Exit Sub
End If

' Ensure that a valid data source exists in the database.
Set dsoDB = dsoServer.mdStores(strOlapDB)
If dsoDB.Datasources.Count = 0 Then
MsgBox "Error-No data sources found in '" & _
strOlapDB & "' database.", , "ClonePart.vbs"
Err.Clear
Exit Sub
End If

' Find the cube.
If (dsoDB.mdStores.Find(strCube)) = 0 then
MsgBox "Error-Cube '" & strCube & "' is missing.", , _
"ClonePart.vbs"
Err.Clear
Exit Sub
End If

' Set the dsoCube variable to the desired cube.
Set dsoCube = dsoDB.MDStores(strCube)

' Find the partition
If dsoCube.mdStores.Count = 0 Then
MsgBox "Error-No partitions exist for cube '" & strCube & _
"'.", , "ClonePart.vbs"
Err.Clear
Exit Sub
End If

' Set the dsoPartition variable to the desired partition.
Set dsoPartition = dsoCube.MDStores(dsoCube.MDStores.Count)
MsgBox "New partition will be based on existing partition: " _
& chr(13) & chr(10) & _
dsoDB.Name & "." & dsoCube.Name & "." & _
dsoPartition.Name, , "ClonePart.vbs"

' Get the quoting characters from the datasource, as
' different databases use different quoting characters.
Dim sLQuote, sRQuote
sLQuote = dsoPartition.DataSources(1).OpenQuoteChar
sRQuote = dsoPartition.DataSources(1).CloseQuoteChar

'************************************************* ********************
' Create the new partition based on the desired partition.
'************************************************* ********************

' Create a new, temporary partition.
strPartitionNew = "NewPartition" & dsoCube.MDStores.Count
Set dsoPartitionNew = dsoCube.MDStores.AddNew("~temp")

' Clone the properties from the desired partition to the
' new partition.
dsoPartition.Clone dsoPartitionNew

' Change the partition name from "~temp" to the
' name intended for the new partition.
dsoPartitionNew.Name = strPartitionNew
dsoPartitionNew.AggregationPrefix = strPartitionNew & "_"

' Set the fact table for the new partition.
dsoPartitionNew.SourceTable = _
sLQuote & "sales_fact_dec_1998" & sRQuote

' Set the FromClause and JoinClause properties of the new
' partition.
dsoPartitionNew.FromClause = Replace(dsoPartition.FromClause, _
dsoPartition.SourceTable, dsoPartitionNew.SourceTable)

dsoPartitionNew.JoinClause = Replace(dsoPartition.JoinClause, _
dsoPartition.SourceTable, dsoPartitionNew.SourceTable)

' Change the definition of the data slice used by the new
' partition, by changing the SliceValue properties of the
' affected levels and dimensions to the desired values.
dsoPartitionNew.Dimensions("Time").Levels("Year"). SliceValue = "1998"
dsoPartitionNew.Dimensions("Time").Levels("Quarter ").SliceValue = "Q4"
dsoPartitionNew.Dimensions("Time").Levels("Month") .SliceValue = "12"

' Estimate the rowcount.
dsoPartitionNew.EstimatedRows = 18325

' Add another filter. The SourceTableFilter provides an additional
' opportunity to add a WHERE clause to the SQL query that will
' populate this partition. We're using this filter to ensure our new
' partition contains zero rows. For the purposes of this sample code
' we don't want to change the data in the FoodMart cube. Comment out
' this line if you want to see data in the new partition.

dsoPartitionNew.SourceTableFilter = dsoPartitionNew.SourceTable _
& "." & sLQuote & "time_id" & sRQuote & "=100"

' Save the partition definition in the metadata repository
dsoPartitionNew.Update

' Check the validity of the new partition structure.
IF NOT dsoPartitionNew.IsValid Then
MsgBox "Error-New partition structure is invalid."
Err.Clear
Exit Sub
End If

MsgBox "New partition " & strPartitionNew & " has been created and " _
& "processed. To see the new partition in Analysis Manager, you " _
& "may need to refresh the list of partitions in the Sales cube " _
& "of FoodMart 2000. The new partition contains no data.", , _
"ClonePart.vbs"

' The next statement, which is commented out, would process the
partition.
' In a real partition management system, this would likely be a separate
' process, perhaps managed via DTS.
' dsoPartitionNew.Process

' Clean up.
Set dsoPartition = Nothing
Set dsoPartitionNew = Nothing
Set dsoCube = Nothing
Set dsoDB = Nothing
dsoServer.CloseServer
Set dsoServer = Nothing


End Sub

It still shows the old slice criteria of cloned partition in the partition
wizard/Select the data slice, while the Meta Data page shows the updated
slice condition.

"nick" <nbdy9 (AT) removethis (DOT) hotmail.com> wrote

How about this?

dsoPartition.Clone dsoPartitionNew, cloneMajorChildren

Ohjoo Kwon

"mark" <nbdy9 (AT) removethis (DOT) hotmail.com> wrote

No, it doesn't solve the problem. However, the script executes much faster
after apply it...

Btw, How to change the datasource of new created partition?

I tried dsoPartitionNew.DataSource = "...", but it doesn't work

"Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> wrote

On my laptop, data slice and data source are modified successfully, after
cloneMajorChildren is added.

I tested the same code as yours. I'm not sure what your problem is. I hope
someone can find the clue.

Ohjoo

"mark" <nbdy9 (AT) removethis (DOT) hotmail.com> wrote

The data slice and data source are modified successfully in my case too, the
problem is the display in the value displayed in the Edit Wizard still shows
the wrong cloned value while the Meta Data page shows the right value.

What's the value in data slice page of the Edit Wizard in your case?

- Nick

"Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> wrote

The only way to *really* solve this is to use the metadata scripter that is
on the SQL Server 2000 Resource Kit. It generates VB code to reverse
engineer DSO code.
1) Start from a known good structure.
2) Run the metadata scripter to generate a VB program (let's call it ProgA)
3) Generate the partition using Analysis Manager
4) Run the metadata scripter to generate a VB program (let's call it ProgB)
5) Go back to the structure as in #1
6) Generate the partition using your application
7) Run the metadata scripter to generate a VB program (let's call it ProgC)

Now compare the differences between ProgA and ProgB -- and then ProgA and
ProgC.
You will probably see some piece of magic that Analysis Manager did in the
cloning that you missed.

This isn't uncommon at all -- and this is the cheatsheet that I use to solve
it.

Hope that helps.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"mark" <nbdy9 (AT) removethis (DOT) hotmail.com> wrote

Thanks very much, sounds a little bit work.
I found that besides the wrong display, the partition are actually correct
(that is, the Meta Data page is the correct description of the structure,
not the wizard).
So I want to just keep it that way if it doesn't have any other side
effect(?)...

"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

I can also see the modified results through the partition wizard.

Of course, SP3 was already installed. FYI, any dot net components including
dot not framework are not installed on my latptop.

I'm not sure what differences between you and me.

Ohjoo

"mark" <nbdy9 (AT) removethis (DOT) hotmail.com> wrote

When cloning, I've noticed the same thing, the slice is right on the metadata
page, but in the wizard for the partition, it shows the old slice on the
"select the slice" screen.

I use...

dsoPartitionMaster.Clone dsoPartitionClone, cloneMajorChildren

....to clone partitions and get the aggregations, etc..


***BE VERY CAREFUL DOING THIS***
***WARNING---DISCLAIMER---TRY AT YOUR OWN RISK, etc....***

When I create partitions through code, I basically run a SQL statement
against the repository to update that one row for that one partition to fix
the problem, since the cloning of the major children and then changing the
slice doesn't seem to fix it.

In my code that creates partitions, I put the "default slice" (the value
showing on the "select the slice screen") for the parent dimension into a
string variable...
(This is an example for partitioning by year, obviously it should be data
and variable driven, thus the hardcoding)

strDefaultSlice = "All.2004"


Then, if I'm creating a new partition, for say 2005...

strNewSliceValue = "All.2005"


strSQL = "UPDATE OlapObjects SET [ObjectDefinition] =
REPLACE([ObjectDefinition],'" & _
strDefaultSlice & "','" & strNewSliceValue & "') WHERE [ObjectName] = '" &
_
strNewPartName & "' "

Then I execute the SQL.

In this instance, I update ONE ROW in the OLAPObjects table for the new
partition I've created, the strNewPartName. This just looks for the default
string in the OBJECTDefinition column and replaces it with the new slice
value.

***BE VERY CAREFUL DOING THIS***

***MAKE BACKUPS OF the OLAPOBJECTS TABLE BEFORE TESTING AND PERFECTING THIS
CODE***

You don't want to update all your rows in your repository because you didn't
issue a WHERE clause properly. Stepping on your repository with bad SQL can
be extremely detrimental.