Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Analysis Services (2000)
 Why DSO is getting measures from clon partition...

Author  Topic 

JohnE
Starting Member

1 Post

Posted - 2008-05-12 : 14:44:49
Hi,


I am creating partition by cloinig old partition with DSO on Analysis 2K.
it is weird that measures is getting from clone partition.
So I am getting errors when I process the partition.
As you see red chars at below it is still using clon partition
I have also added my DSO code at below.

thanks...



------------ERROR---------------------------------------------

Processing Partition 'account_20080507' failed. No changes have been made to the database.

Partition 'account_20080507' Execute : SELECT "dbo"."account"."account_name", "dbo"."account_category"."account_category_key",
DatePart(year,"dbo"."calendar"."the_date"), DatePart(quarter,"dbo"."calendar"."the_date"), DatePart(month,"dbo"."calendar"."the_date"),
convert(CHAR,"dbo"."calendar"."the_date", 112), "dbo"."domain"."domain_type", "dbo"."account_20080424"."num_account",
"dbo"."account_20080424"."num_total"

FROM "account_20080507", "dbo"."account", "dbo"."account_category", "dbo"."calendar",
"dbo"."domain" WHERE ("account_20080507"."account_key"="dbo"."account"."account_key")
AND ("account_20080507"."account_category_key"="dbo"."account_category"."account_category_key")
AND ("account_20080507"."account_date_id"="dbo"."calendar"."date_id")
AND ("account_20080507"."domain_key"="dbo"."domain"."domain_key")


Data source provider error: The column prefix 'dbo.account_20080424' does not match with a table name or alias
name used in the query.;42000;The column prefix 'dbo.account_20080424' does not match with a table name or alias
name used in the query.;42000; Time:5/9/2008 4:21:10 PM



-----------MY CODE ------------------------------




'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
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 = "accdb"
strCube = "account"

' 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 = "account_20080507" ' & 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 & "account_20080507" & 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
dsoPartitionNew.EstimatedRows = 9000000

' 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

   

- Advertisement -