I am trying to write a C# backend program that will handle the database maintenance that I would otherwise have to do via Microsoft's Analysis Services front end GUI Wizards. Currently I am having a problem with recreating the SliceValue. The value is a time dimension and should equate to [Monthly Dates].[Year].[Quarter].[Month] and instead it is these same values, but instead of being seperated by periods it has some sort of non-displayable character (maybe a newline) that is placing each value on a new line. The error I receive when I attempt to process the partition is as follows:An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in ProcessCubes.exeAdditional information: Processing error [Object does not exist] '[All][2003][Quarter 2][May]' The code is below. public void ProcessCubeIncremental(string ServerName, string DatabaseName, string Frequency, string CubeName, string FactTableName, string ReportDate) { DSO.ServerClass dsoServer = new DSO.ServerClass(); DSO.MDStore dsoDatabase; DSO.MDStore dsoCube; DSO.MDStore dsoPartitionNew; DSO.MDStore dsoPartitionOld; DSO.Dimension dsoDimension; DSO.Level dsoLevel; //DSO.Level dsoLevelAll; //DSO.Level dsoLevelYear; //DSO.Level dsoLevelQuarter; //DSO.Level dsoLevelMonth; DSO.Property dsoProperty; DateTime dt = Convert.ToDateTime(ReportDate); GregorianCalendar c = new GregorianCalendar(GregorianCalendarTypes.Localized); int YearOld = c.GetYear(c.AddMonths(dt,-1)); int MonthOld = c.GetMonth(c.AddMonths(dt,-1)); string YearOldStr = YearOld.ToString(); string MonthOldStr; if (MonthOld < 10) { MonthOldStr = "0"+MonthOld.ToString(); } else { MonthOldStr = MonthOld.ToString(); } int YearNew = c.GetYear(dt); int MonthNew = c.GetMonth(dt); string YearNewStr = YearNew.ToString(); string MonthNewStr; if (MonthNew < 10) { MonthNewStr = "0"+MonthNew.ToString(); } else { MonthNewStr = MonthNew.ToString(); } switch(MonthNew) { case 1: MonthNewName = "January"; break; case 2: MonthNewName = "February"; break; case 3: MonthNewName = "March"; break; case 4: MonthNewName = "April"; break; case 5: MonthNewName = "May"; break; case 6: MonthNewName = "June"; break; case 7: MonthNewName = "July"; break; case 8: MonthNewName = "August"; break; case 9: MonthNewName = "September"; break; case 10: MonthNewName = "October"; break; case 11: MonthNewName = "November"; break; case 12: MonthNewName = "December"; break; } string PartitionNameOld = "p"+YearOldStr+MonthOldStr; string PartitionNameNew = "p"+YearNewStr+MonthNewStr; string ReportDateStart = MonthNewStr+"/1/"+YearNewStr; string ReportDateRange; string ReportDateEnd = MonthNewStr+"/"+c.GetDaysInMonth(YearNew,MonthNew).ToString()+"/"+YearNewStr; if (Frequency == "Daily") { ReportDateRange = "between '" + ReportDateStart + "' and '" + ReportDateEnd + "'"; } else { ReportDateRange = "= '" + ReportDateEnd + "'"; } // Connect to the local server dsoServer.Connect(ServerName); // Set up the MDStore objects: // database, cube, and partition. dsoDatabase = (DSO.MDStore) dsoServer.MDStores.Item(DatabaseName); dsoCube = (DSO.MDStore) dsoDatabase.MDStores.Item(CubeName); dsoPartitionOld = (DSO.MDStore) dsoCube.MDStores.Item(PartitionNameOld); // delete the partition if it already exists if (dsoCube.MDStores.Find(PartitionNameNew) == true) { dsoCube.MDStores.Remove(PartitionNameNew); } // Create a new partition. dsoPartitionNew = (DSO.MDStore) dsoCube.MDStores.AddNew("~temp", DSO.SubClassTypes.sbclsRegular); // Clone the properties from the desired partition to the new partition. dsoPartitionOld.Clone(dsoPartitionNew, DSO.CloneOptions.cloneMajorChildren); // Change the partition name from "~temp" to the name intended for the new partition. dsoPartitionNew.Name = PartitionNameNew; dsoPartitionNew.AggregationPrefix = PartitionNameNew+"_"; //dsoPartitionNew.SourceTable = "\"dbo\".\""+FactTableName+"\""; dsoPartitionNew.SourceTable = "\""+FactTableName+"\""; // Estimate the rowcount from the reference partition. dsoPartitionNew.EstimatedRows = dsoPartitionOld.EstimatedRows; dsoPartitionNew.SourceTableFilter = dsoPartitionNew.SourceTable + ".\"ReportDate\" " + ReportDateRange; // Set the FromClause and JoinClause properties of the new partition. //dsoPartitionNew.FromClause = dsoPartitionOld.FromClause.Replace(dsoPartitionOld.SourceTable, dsoPartitionNew.SourceTable); //dsoPartitionNew.JoinClause = dsoPartitionOld.JoinClause.Replace(dsoPartitionOld.SourceTable, dsoPartitionNew.SourceTable); // Change the definition of the data Slice used by the new partition. // Change the SliceValue properties of the affected levels and dimensions to the correct values. switch(MonthNew) { case 1: case 2: case 3: Quarter = 1; break; case 4: case 5: case 6: Quarter = 2; break; case 7: case 8: case 9: Quarter = 3; break; case 10: case 11: case 12: Quarter = 4; break; } // 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. dsoDimension = (DSO.Dimension) dsoPartitionNew.Dimensions.Item("Monthly Dates"); dsoDimension.CustomProperties.Clear(); //dsoDimension.CustomProperties.Remove("SliceValue"); //dsoDimension.CustomProperties.Add("[All].["+YearNewStr+"].Quarter "+Quarter.ToString()+"].["+MonthNewStr+"]","SliceValue",VBA.VbVarType.vbString); //dsoLevels = (DSO.Level) dsoDimension.Levels.Item("(All).Year.Quarter.Month"); //dsoLevels.SliceValue = "[All].["+YearNewStr+"].[Quarter "+Quarter.ToString()+"].["+MonthNewName+"]"; //dsoProperty = (DSO.Property) dsoDimension.CustomProperties.Add("[All]","(All)",VBA.VbVarType.vbString); //dsoProperty = (DSO.Property) dsoDimension.CustomProperties.Add("["+YearNewStr+"]","Year",VBA.VbVarType.vbString); //dsoProperty = (DSO.Property) dsoDimension.CustomProperties.Add("[Quarter "+Quarter.ToString()+"]","Quarter",VBA.VbVarType.vbString); //dsoProperty = (DSO.Property) dsoDimension.CustomProperties.Add("["+MonthNewName+"]","Month",VBA.VbVarType.vbString); dsoLevel = (DSO.Level) dsoDimension.Levels.Item("(All)"); dsoLevel.SliceValue = "[All]"; dsoLevel = (DSO.Level) dsoDimension.Levels.Item("Year"); dsoLevel.SliceValue = "["+YearNewStr+"]"; dsoLevel = (DSO.Level) dsoDimension.Levels.Item("Quarter"); dsoLevel.SliceValue = "[Quarter "+Quarter.ToString()+"]"; dsoLevel = (DSO.Level) dsoDimension.Levels.Item("Month"); dsoLevel.SliceValue = "["+MonthNewName+"]"; //dsoLevelAll = (DSO.Level) dsoDimension.Levels.Item("(All)"); //dsoLevelAll.SliceValue = "[All]"; //dsoLevelYear = (DSO.Level) dsoDimension.Levels.Item("Year"); //dsoLevelYear.SliceValue = "["+YearNewStr+"]"; //dsoLevelQuarter = (DSO.Level) dsoDimension.Levels.Item("Quarter"); //dsoLevelQuarter.SliceValue = "[Quarter "+Quarter.ToString()+"]"; //dsoLevelMonth = (DSO.Level) dsoDimension.Levels.Item("Month"); //dsoLevelMonth.SliceValue = "["+MonthNewName+"]"; // Save the partition definition in the metadata repository. dsoPartitionNew.Update(); // Process the temporary partition. //dsoCube.LockObject(DSO.OlapLockTypes.olapLockProcess, "Processing object..."); dsoPartitionNew.Process(DSO.ProcessTypes.processFull); //dsoCube.UnlockObject(); // Close the allocated resources and exit the subroutine. dsoCube = null; dsoDatabase = null; dsoServer.CloseServer(); dsoServer = null; }
Please help me, I'm a desparate man!