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