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.exe
Additional information: Processing error [Object does not exist] '[All]
[2003]
[Quarter 2]
[May]'
The code is below.
Please help me, I'm a desparate man!
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in ProcessCubes.exe
Additional information: Processing error [Object does not exist] '[All]
[2003]
[Quarter 2]
[May]'
The code is below.
Code:
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!