public void ProcessCube()
{
AppendLogLine("Start full process Cube.");
if (_CbDatabase != null)
{
_CbDatabase.Process(ProcessType.ProcessFull);
AppendLogLine("End full process Cube.");
}
}
public static void CalculatedColumnAdd(AMO.Database tabularDatabase,
string tableName,
string columnName,
string daxExpression,
bool updateInstance = true,
ColumnInfo?columnProperties = null,
ReportingInfo?reportingProperties = null,
AMO.ProcessType?processType = null)
{
// Major steps in adding a calculated calculatedColumn to a table in the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Adding calculatedColumn as attribute to dimension
// - Adding calculatedColumn as attribute to degenerated dimension in Measure Group
// - Set calculatedColumn properties according to optional parameters
// - Set reporting properties according to optional parameters
// - Process table/database according to optional parameters
//
// Note: There are no validations for duplicated names, invalid names or
// similar scenarios. It is expected the server will take care of them and
// throw exceptions on any invalid situation.
//
// Note: In AMO, strings as indexers refer to the ID of the object, not the Name of the object
//
// Note: Only one DataSourceView is used in Tabular Models
// ==> tabularDatabase.DataSourceViews[0] represents the DSV of the model
//
// Note: Only one Cube is used in Tabular Models
// ==> tabularDatabase.Cubes[0] represents the cube in the model
//
// Note: Microsoft design tools use the following pattern to keep track of the
// datasource matching elements:
// DataSourceView->TableName <---> Dimension.ID, MeasureGroup.ID
// DataSourceView->ColumnName <---> Dimension->ColumnID, MeasureGroup.DegeneratedDimension->CoumnID
// So far, this sample follows the same pattern.
//
// WARNING: Breaking the above pattern when creating your
// own AMO to Tabular functions might lead to
// unpredictable behavior when using Microsoft
// Design tools in your models.
//
// Note: There are no validations on the ProcessType requested and whatever value is passed it's used
//
// Note: For Calculated Columns, in tabular models, the following ProcessType values are 'valid' or have sense:
// - ProcessDefault ==> (issued at table level) verifies if a data (at partition level) or recalc is
// required and issues coresponding internal process tasks
// - ProcessFull ==> (issued at table level) forces data upload (on all partitions) and recalc,
// regardless of table status
// - ProcessRecalc ==> (issued at Database level) forces a recalc of internal structures (measures,
// calculated columns, hierarchies, etc.) at database level; doesn't load
// new data.
//
// Note: Issuing a process request (setting parameter processType != null) forces a database update
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (columnName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(ColumnStringName);
}
if (daxExpression.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(DaxExpressionStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
columnName = columnName.Trim();
daxExpression = daxExpression.Trim();
// - Obtain table name in DSV
string datasourceTableName = tabularDatabase.Dimensions.GetByName(tableName).ID;
// - Obtain "RowNumber" column id
string rowNumberColumnId = string.Empty;
foreach (AMO.DimensionAttribute da in tabularDatabase.Dimensions[datasourceTableName].Attributes)
{
if (da.Type == AMO.AttributeType.RowNumber)
{
rowNumberColumnId = da.ID;
break;
}
}
#endregion
// Add calculated calculatedColumn as attribute to the Dimension
// Note: datasourceTableName == tableId; because parity with DS object names needs to be kept
using (AMO.Dimension tableDimension = tabularDatabase.Dimensions[datasourceTableName])
using (AMO.DimensionAttribute calculatedColumnDimensionAttribute = tableDimension.Attributes.Add(columnName, columnName))
using (AMO.DataItem dataItemEmptyType = new AMO.DataItem(datasourceTableName, columnName, System.Data.OleDb.OleDbType.Empty))
using (AMO.ExpressionBinding expressionBinding = new AMO.ExpressionBinding(daxExpression))
using (AMO.DataItem dataItemWCharType = new AMO.DataItem(datasourceTableName, columnName, System.Data.OleDb.OleDbType.WChar))
{
calculatedColumnDimensionAttribute.Usage = AMO.AttributeUsage.Regular;
calculatedColumnDimensionAttribute.KeyUniquenessGuarantee = false;
calculatedColumnDimensionAttribute.KeyColumns.Add(dataItemEmptyType);
calculatedColumnDimensionAttribute.KeyColumns[0].Source = expressionBinding.Clone();
calculatedColumnDimensionAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
calculatedColumnDimensionAttribute.NameColumn = dataItemWCharType;
calculatedColumnDimensionAttribute.NameColumn.Source = expressionBinding;
calculatedColumnDimensionAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
calculatedColumnDimensionAttribute.OrderBy = AMO.OrderBy.Key;
using (AMO.AttributeRelationship calculatedColumnDimensionAttributeRelationship = tableDimension.Attributes[rowNumberColumnId].AttributeRelationships.Add(calculatedColumnDimensionAttribute.ID))
{
calculatedColumnDimensionAttributeRelationship.Cardinality = AMO.Cardinality.Many;
calculatedColumnDimensionAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
}
}
// Add calculatedColumn as attribute to the MG, in the DegeneratedMeasureGroupDimension
using (AMO.MeasureGroup tableMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups[datasourceTableName])
using (AMO.DegenerateMeasureGroupDimension tableMGDimension = (AMO.DegenerateMeasureGroupDimension)tableMeasureGroup.Dimensions[datasourceTableName])
using (AMO.MeasureGroupAttribute calculatedColumnMGAttribute = new AMO.MeasureGroupAttribute(columnName))
using (AMO.DataItem dataItemEmptyType = new AMO.DataItem(datasourceTableName, columnName, System.Data.OleDb.OleDbType.Empty))
using (AMO.ExpressionBinding expressionBinding = new AMO.ExpressionBinding(daxExpression))
{
calculatedColumnMGAttribute.KeyColumns.Add(dataItemEmptyType);
calculatedColumnMGAttribute.KeyColumns[0].Source = expressionBinding;
tableMGDimension.Attributes.Add(calculatedColumnMGAttribute);
}
// Set/update optional calculatedColumn properties
if (columnProperties != null)
{
if (!columnProperties.Value.DataFormat.IsNullOrEmptyOrWhitespace())
{
ColumnAlterFormat(tabularDatabase, tableName, columnName, columnProperties.Value.DataFormat);
}
if (columnProperties.Value.DataType != DataType.Default && columnProperties.Value.DataType != DataType.Unsupported)
{
ColumnAlterDataType(tabularDatabase, tableName, columnName, columnProperties.Value.DataType);
}
if (columnProperties.Value.Visible != null)
{
ColumnAlterVisibility(tabularDatabase, tableName, columnName, columnProperties.Value.Visible.Value);
}
if (!columnProperties.Value.SortByColumn.IsNullOrEmptyOrWhitespace())
{
ColumnAlterSortByColumnName(tabularDatabase, tableName, columnName, columnProperties.Value.SortByColumn);
}
}
// ToDo: Set/update optional reporting properties
//if (reportingProperties != null)
//{
//}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
if (processType != null)
{
// Throw exception if server instance is outdated and user requests process
if (!updateInstance)
{
throw new InvalidOperationException(Resources.ProcessRequestedForOutdatedModelInvalidOperationException);
}
// Now the table, that contains the Calculated Column, can be processed according to the user request
TableProcess(tabularDatabase, tableName, (AMO.ProcessType)processType);
// Calculated columns require a database level process recalc
tabularDatabase.Process(AMO.ProcessType.ProcessRecalc);
}
}
