public RelationshipGraph(AMO.Database tabularDatabase)
{
foreach (TableInfo tableInfo in AMO2Tabular.TablesEnumerateFull(tabularDatabase))
{
using (AMO.MeasureGroup currentMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups[tableInfo.DataSourceName])
{
foreach (AMO.MeasureGroupDimension measureGroupDimension in currentMeasureGroup.Dimensions)
{
if (measureGroupDimension is AMO.ReferenceMeasureGroupDimension)
{
AMO.ReferenceMeasureGroupDimension referencedTable = measureGroupDimension as AMO.ReferenceMeasureGroupDimension;
string foreignTableId = referencedTable.IntermediateCubeDimensionID;
string foreignColumnId = referencedTable.IntermediateGranularityAttributeID;
string primaryKeyTableId = referencedTable.CubeDimensionID;
string primaryKeyColumnId = string.Empty;
foreach (AMO.MeasureGroupAttribute attribute in referencedTable.Attributes)
{
if (attribute.Type == AMO.MeasureGroupAttributeType.Granularity)
{
primaryKeyColumnId = attribute.AttributeID;
break;
}
}
FullName foreignKeyEnd = new FullName(foreignTableId, foreignColumnId);
FullName primaryKeyEnd = new FullName(primaryKeyTableId, primaryKeyColumnId);
RelationshipPair relationship = new RelationshipPair(primaryKeyEnd, foreignKeyEnd);
AddForeignKeyUpPair(primaryKeyTableId, relationship);
AddPrimaryKeyDownPair(foreignTableId, relationship);
}
}
}
}
}
private static void RelationshipAddReferenceMeasureGroupDimension(AMO.Database tabularDatabase,
string currentMeasureGroupId,
RelationshipPair relationshipPair,
string relationshipId = null)
{
string foreignTableId = relationshipPair.ForeignKeyEnd.TableId;
string foreignColumnId = relationshipPair.ForeignKeyEnd.ColumnId;
string pkTableId = relationshipPair.PrimaryKeyEnd.TableId;
string pkColumnId = relationshipPair.PrimaryKeyEnd.ColumnId;
// Creating the ReferenceMeasureGroupDimension that defines the 'Activeness' of a relationship
using (AMO.Cube modelCube = tabularDatabase.Cubes[0])
using (AMO.MeasureGroup currentMG = modelCube.MeasureGroups[currentMeasureGroupId])
using (AMO.ReferenceMeasureGroupDimension newReferenceMGDim = new AMO.ReferenceMeasureGroupDimension())
{
newReferenceMGDim.CubeDimensionID = pkTableId;
newReferenceMGDim.IntermediateCubeDimensionID = foreignTableId;
newReferenceMGDim.IntermediateGranularityAttributeID = foreignColumnId;
// Replicating attributes (columns) from dimension
foreach (AMO.CubeAttribute PKAttribute in modelCube.Dimensions[pkTableId].Attributes)
{
using (AMO.MeasureGroupAttribute PKMGAttribute = newReferenceMGDim.Attributes.Add(PKAttribute.AttributeID))
using (AMO.DataItem dataItem = new AMO.DataItem(pkTableId, PKAttribute.AttributeID, PKAttribute.Attribute.KeyColumns[0].DataType))
using (AMO.ColumnBinding columnBinding = new AMO.ColumnBinding(pkTableId, PKAttribute.AttributeID))
{
PKMGAttribute.KeyColumns.Add(dataItem);
PKMGAttribute.KeyColumns[0].Source = columnBinding;
}
}
newReferenceMGDim.Attributes[pkColumnId].Type = AMO.MeasureGroupAttributeType.Granularity;
// If relationship is not null or empty then this is a direct relationship and
// has to have Materialization.Regular
if (!relationshipId.IsNullOrEmptyOrWhitespace())
{
newReferenceMGDim.Materialization = AMO.ReferenceDimensionMaterialization.Regular;
newReferenceMGDim.RelationshipID = relationshipId;
}
else
{
newReferenceMGDim.Materialization = AMO.ReferenceDimensionMaterialization.Indirect;
}
// Adding the ReferenceMeasureGroupDimension to the measure group
currentMG.Dimensions.Add(newReferenceMGDim);
}
}
private static void setActiveRelationship(AMO.Cube currentCube, string MVTableName, string MVColumnName, string PKTableName, string relationshipID)
{
AMO.MeasureGroup currentMG = currentCube.MeasureGroups[MVTableName];
if (!currentMG.Dimensions.Contains(PKTableName))
{
AMO.ReferenceMeasureGroupDimension NewReferenceMGDim = new AMO.ReferenceMeasureGroupDimension();
NewReferenceMGDim.CubeDimensionID = PKTableName;
NewReferenceMGDim.IntermediateCubeDimensionID = MVTableName;
NewReferenceMGDim.Materialization = AMO.ReferenceDimensionMaterialization.Regular;
foreach (AMO.CubeAttribute PKAttribute in currentCube.Dimensions[PKTableName].Attributes)
{
AMO.MeasureGroupAttribute PKMGAttribute = NewReferenceMGDim.Attributes.Add(PKAttribute.AttributeID);
System.Data.OleDb.OleDbType PKMGAttributeType = PKAttribute.Attribute.KeyColumns[0].DataType;
PKMGAttribute.KeyColumns.Add(new AMO.DataItem(PKTableName, PKAttribute.AttributeID, PKMGAttributeType));
PKMGAttribute.KeyColumns[0].Source = new AMO.ColumnBinding(PKTableName, PKAttribute.AttributeID);
}
currentMG.Dimensions.Add(NewReferenceMGDim);
}
AMO.ReferenceMeasureGroupDimension currentReferenceMGDim = (AMO.ReferenceMeasureGroupDimension)currentMG.Dimensions[PKTableName];
currentReferenceMGDim.RelationshipID = relationshipID;
currentReferenceMGDim.IntermediateGranularityAttributeID = MVColumnName;
}
public static List <CubeAttribute> GetCubeAttributesWithSlice(string sQuerySubcubeVerbose, Microsoft.AnalysisServices.MeasureGroup mg)
{
List <CubeAttribute> list = new List <CubeAttribute>();
int iDimensionIndex = 0;
foreach (string sLine in sQuerySubcubeVerbose.Replace("\r\n", "\n").Replace("\r", "\n").Split(new char[] { '\n' }))
{
if (string.IsNullOrEmpty(sLine.Trim()))
{
break; //stop when we get to a blank line
}
System.Text.RegularExpressions.Match match = _regexVerboseCubeDimension.Match(sLine);
string sCubeDimensionName = match.Groups["dimension"].Value;
CubeDimension cd = mg.Parent.Dimensions.FindByName(sCubeDimensionName);
if (cd == null)
{
continue;
}
string sCubeDimensionID = cd.ID;
string sAttributes = match.Groups["attributes"].Value;
string[] arrAttributes = sAttributes.Split(new char[] { ' ' });
for (int iAttribute = 0; iAttribute < arrAttributes.Length && iAttribute < cd.Attributes.Count; iAttribute++)
{
string sAttributeSlice = arrAttributes[iAttribute];
if (sAttributeSlice == "0" || sAttributeSlice == "1" || sAttributeSlice == "*")
{
continue;
}
CubeAttribute ca = cd.Attributes[iAttribute];
list.Add(ca);
}
iDimensionIndex++;
}
return(list);
}
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);
}
}
//TODO: Add direct query support
/* Complete feature list, to be noted when complete support added
* - Database
* - Direct Query
* - Datasources (Done)
* - Tables (Done)
* - Translation of table (Done)
* - Attributes (Done)
* - Translation of Attribute (Done)
* - Hierarchies (Done)
* - Translation of Hierarchies (Done)
* - Levels (Done)
* - Translation of Levels (Done)
* - Measures (Done)
* - Translation of Measures (Done)
* - KPI's (Done)
* - Perspectives (Done)
* - Roles (Done)
* - Row Level Security (Done)
* - Members (Done)
* - Relationships (Done)
*/
/// <summary>
/// Given a 1200 or 1400 model, produces an equivelant 1103 model. Incompatible features are not added.
/// </summary>
/// <param name="TOMDatabase"></param>
/// <returns></returns>
public static AMO.Database ToAMODatabase(TOM.Database TOMDatabase)
{
/* The Database representing a Tabular model rarely has a 1 to 1 mapping between
* a TOM object and an AMO object.
*
* Some objects which DO have 1:1 mappings (at least, in a logical sense):
* - DataSources
* - Hierarchies
* - Levels
* - Perspectives
* - Partitions
* - Translations (however, they are structured differently)
* - Annotations (These can also accept XML nodes, not just strings)
*
* The following do NOT:
* - Tables
* - Columns
* - Measures
* - Relationships
* - Roles
*
* The following is, surprisingly, supported:
* - Tabular Actions
* - HideMemberIf
* - Display Folders
* - Custom Format strings
* - Translations
* - Similar to 1200, more than one column with the same source column
*
* Please note this does NOT produce a file able to be opened by Visual Studio.
* Unlike 1200 models, Visual Studio cannot open all valid 1103 models.
* It relies heavily on custom annotations, understood mainly through trial and error.
*
* In addition to these custom annotations, some features have no alternative (custom format strings), and
* are completely unsupported by both Visual Studio and BIDS.
*
* Display Folders, Tabular Actions, and Translations are all supported using BIDS.
*
* These drastically increase the database/file size, and as such are optionally added in a seperate function.
*/
TOM.Model TOMModel = TOMDatabase.Model;
//Create Database
AMO.Database AMODatabase = new AMO.Database(TOMDatabase.Name, TOMDatabase.Name);
//Initialise with default values.
AMODatabase.StorageEngineUsed = AMO.StorageEngineUsed.InMemory;
AMODatabase.CompatibilityLevel = SQL2012SP1;
//DataSource has 1:1 mapping with AMO object
#region DataSources
foreach (TOM.ProviderDataSource TOMDataSource in TOMModel.DataSources)
{
AMO.DataSource AMODataSource = new AMO.RelationalDataSource(TOMDataSource.Name, TOMDataSource.Name);
AMODataSource.Description = TOMDataSource.Description;
AMODataSource.ConnectionString = TOMDataSource.ConnectionString;
AMODataSource.ImpersonationInfo = new AMO.ImpersonationInfo();
switch (TOMDataSource.ImpersonationMode)
{
case TOM.ImpersonationMode.Default:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.Default;
break;
case TOM.ImpersonationMode.ImpersonateAccount:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateAccount;
break;
case TOM.ImpersonationMode.ImpersonateAnonymous:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateAnonymous;
break;
case TOM.ImpersonationMode.ImpersonateCurrentUser:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateCurrentUser;
break;
case TOM.ImpersonationMode.ImpersonateServiceAccount:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateServiceAccount;
break;
case TOM.ImpersonationMode.ImpersonateUnattendedAccount:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateUnattendedAccount;
break;
}
AMODataSource.ImpersonationInfo.Account = TOMDataSource.Account;
AMODataSource.ImpersonationInfo.Password = TOMDataSource.Password;
switch (TOMDataSource.Isolation)
{
case TOM.DatasourceIsolation.ReadCommitted:
AMODataSource.Isolation = AMO.DataSourceIsolation.ReadCommitted;
break;
case TOM.DatasourceIsolation.Snapshot:
AMODataSource.Isolation = AMO.DataSourceIsolation.Snapshot;
break;
}
//1 "tick" = 100 nanoseconds = 1*10^-7 seconds.
AMODataSource.Timeout = new TimeSpan(TOMDataSource.Timeout * 10000000);
AMODatabase.DataSources.Add(AMODataSource);
}
#endregion
/* The DSV is surprisingly simple.
* For each physical table (but NOT partition), a DataTable needs to be added to the DSV DataSet,
* with the same name as the table.
*
* Similarly, for each distinct source column, a DataColumn needs to be added to the corresponding DataTable
*/
#region DataSourceView
using (AMO.DataSourceView dsv = new AMO.DataSourceView(AMODatabase.DataSources[0].Name))
{
System.Data.DataSet Schema = new System.Data.DataSet(AMODatabase.DataSources[0].Name);
dsv.Schema = Schema;
dsv.DataSourceID = AMODatabase.DataSources[0].Name;
AMODatabase.DataSourceViews.Add(dsv);
}
#endregion
#region Create Cube
AMO.Cube Cube = new AMO.Cube(TOMModel.Name, TOMModel.Name);
Cube.Source = new AMO.DataSourceViewBinding(AMODatabase.DataSourceViews[0].ID);
Cube.StorageMode = AMO.StorageMode.InMemory;
//Create the MdxScript for holding DAX commands.
using (AMO.MdxScript Script = Cube.MdxScripts.Add(MDXScriptName, MDXScriptName))
{
//You MUST have a "default" MDX measure for some reason.
//We make sure to hide it - it serves no real purpose, besides its own existence
System.Text.StringBuilder InitialisationCommand = new System.Text.StringBuilder();
InitialisationCommand.AppendLine("CALCULATE;");
InitialisationCommand.AppendLine("CREATE MEMBER CURRENTCUBE.Measures.[_No measures defined] AS 1, VISIBLE = 0;");
InitialisationCommand.AppendLine("ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, Default_Member = [_No measures defined];");
Script.Commands.Add(new AMO.Command(InitialisationCommand.ToString()));
}
AMODatabase.Cubes.Add(Cube);
#endregion
#region Add Tables
foreach (TOM.Table TOMTable in TOMModel.Tables)
{
//Three "parts" to a Table:
//1. A System.Data.DataTable, with the same Name as the TOMTable
//2. A dimension with the same name as the TOMTable
//3. A measure group with the same name as the TOMTable,
System.Data.DataTable SchemaTable = new System.Data.DataTable(TOMTable.Name);
AMODatabase.DataSourceViews[0].Schema.Tables.Add(SchemaTable);
string RowNumberColumnName = string.Format(System.Globalization.CultureInfo.InvariantCulture, "RowNumber_{0}", System.Guid.NewGuid());
#region Add Table Dimension
try
{
using (AMO.Dimension Dimension = AMODatabase.Dimensions.Add(TOMTable.Name, TOMTable.Name))
{
Dimension.Source = new AMO.DataSourceViewBinding(AMODatabase.DataSourceViews[0].ID);
Dimension.StorageMode = AMO.DimensionStorageMode.InMemory;
Dimension.UnknownMember = AMO.UnknownMemberBehavior.AutomaticNull;
Dimension.UnknownMemberName = "Unknown";
using (Dimension.ErrorConfiguration = new AMO.ErrorConfiguration())
{
Dimension.ErrorConfiguration.KeyNotFound = AMO.ErrorOption.IgnoreError;
Dimension.ErrorConfiguration.KeyDuplicate = AMO.ErrorOption.ReportAndStop;
Dimension.ErrorConfiguration.NullKeyNotAllowed = AMO.ErrorOption.ReportAndStop;
}
Dimension.ProactiveCaching = new AMO.ProactiveCaching();
System.TimeSpan DefaultProactiveCachingTimeSpan = new System.TimeSpan(0, 0, -1);
Dimension.ProactiveCaching.SilenceInterval = DefaultProactiveCachingTimeSpan;
Dimension.ProactiveCaching.Latency = DefaultProactiveCachingTimeSpan;
Dimension.ProactiveCaching.SilenceOverrideInterval = DefaultProactiveCachingTimeSpan;
Dimension.ProactiveCaching.ForceRebuildInterval = DefaultProactiveCachingTimeSpan;
Dimension.ProactiveCaching.Source = new AMO.ProactiveCachingInheritedBinding();
Dimension.Description = TOMTable.Description;
// Define RowNumber
using (AMO.DimensionAttribute RowNumberDimAttribute = Dimension.Attributes.Add(RowNumberColumnName, RowNumberColumnName))
{
RowNumberDimAttribute.Type = AMO.AttributeType.RowNumber;
RowNumberDimAttribute.KeyUniquenessGuarantee = true;
RowNumberDimAttribute.Usage = AMO.AttributeUsage.Key;
RowNumberDimAttribute.KeyColumns.Add(new AMO.DataItem());
RowNumberDimAttribute.KeyColumns[0].DataType = System.Data.OleDb.OleDbType.Integer;
RowNumberDimAttribute.KeyColumns[0].DataSize = 4;
RowNumberDimAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Error;
RowNumberDimAttribute.KeyColumns[0].Source = new AMO.RowNumberBinding();
RowNumberDimAttribute.NameColumn = new AMO.DataItem();
RowNumberDimAttribute.NameColumn.DataType = System.Data.OleDb.OleDbType.WChar;
RowNumberDimAttribute.NameColumn.DataSize = 4;
RowNumberDimAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
RowNumberDimAttribute.NameColumn.Source = new AMO.RowNumberBinding();
RowNumberDimAttribute.OrderBy = AMO.OrderBy.Key;
RowNumberDimAttribute.AttributeHierarchyVisible = false;
}
// Add Translations
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMTable);
if (AMOTranslation != null)
{
Dimension.Translations.Add(AMOTranslation);
}
}
}
}
catch (Exception e)
{
throw new Exception(string.Format("The following error occurred creating the DImension for table {0}: {1}", TOMTable.Name, e.Message), e);
}
#endregion
#region Add Table MeasureGroup
try
{
using (AMO.MeasureGroup TableMeasureGroup = Cube.MeasureGroups.Add(TOMTable.Name, TOMTable.Name))
{
TableMeasureGroup.StorageMode = AMO.StorageMode.InMemory;
TableMeasureGroup.ProcessingMode = AMO.ProcessingMode.Regular;
// Add Default Measure
string DefaultMeasureID = string.Concat("_Count", TOMTable.Name);
using (AMO.Measure DefaultMeasure = TableMeasureGroup.Measures.Add(DefaultMeasureID, DefaultMeasureID))
using (AMO.RowBinding DefaultMeasureRowBinding = new AMO.RowBinding(TOMTable.Name))
using (AMO.DataItem DefaultMeasureSource = new AMO.DataItem(DefaultMeasureRowBinding))
{
DefaultMeasure.AggregateFunction = AMO.AggregationFunction.Count;
DefaultMeasure.DataType = AMO.MeasureDataType.BigInt;
DefaultMeasure.Visible = false;
DefaultMeasureSource.DataType = System.Data.OleDb.OleDbType.BigInt;
DefaultMeasure.Source = DefaultMeasureSource;
}
// Add Dimension to Measure Group
using (AMO.DegenerateMeasureGroupDimension DefaultMeasureGroupDimension = new AMO.DegenerateMeasureGroupDimension(TOMTable.Name))
using (AMO.MeasureGroupAttribute MeasureGroupAttribute = new AMO.MeasureGroupAttribute(RowNumberColumnName))
using (AMO.ColumnBinding RowNumberColumnBinding = new AMO.ColumnBinding(TOMTable.Name, RowNumberColumnName))
using (AMO.DataItem RowNumberKeyColumn = new AMO.DataItem(RowNumberColumnBinding))
{
DefaultMeasureGroupDimension.ShareDimensionStorage = AMO.StorageSharingMode.Shared;
DefaultMeasureGroupDimension.CubeDimensionID = TOMTable.Name;
MeasureGroupAttribute.Type = AMO.MeasureGroupAttributeType.Granularity;
RowNumberKeyColumn.DataType = System.Data.OleDb.OleDbType.Integer;
MeasureGroupAttribute.KeyColumns.Add(RowNumberKeyColumn);
DefaultMeasureGroupDimension.Attributes.Add(MeasureGroupAttribute);
TableMeasureGroup.Dimensions.Add(DefaultMeasureGroupDimension);
}
//Partitions have a 1:1 mapping
#region Partitions
foreach (TOM.Partition TOMPartition in TOMTable.Partitions)
{
using (AMO.Partition AMOPartition = new AMO.Partition(TOMPartition.Name, TOMPartition.Name))
{
AMOPartition.StorageMode = AMO.StorageMode.InMemory;
AMOPartition.ProcessingMode = AMO.ProcessingMode.Regular;
AMOPartition.Source = new AMO.QueryBinding(
((TOM.QueryPartitionSource)TOMPartition.Source).DataSource.Name,
((TOM.QueryPartitionSource)TOMPartition.Source).Query
);
AMOPartition.Type = AMO.PartitionType.Data;
TableMeasureGroup.Partitions.Add(AMOPartition);
}
}
#endregion
}
}
catch (Exception e)
{
throw new Exception(string.Format("The following error occurred creating the MeasureGroup for table {0}: {1}", TOMTable.Name, e.Message), e);
}
#endregion
//Add the dimension to the cube
Cube.Dimensions.Add(TOMTable.Name, TOMTable.Name, TOMTable.Name);
Cube.Dimensions[TOMTable.Name].Visible = !TOMTable.IsHidden;
/* No 1:1 mapping exists for columns. Each column consists of the following:
* - A DataColumn with the same name as the column,
* attached to the DataTable correlating to the Table, located in the DSV
* - An attribute with the same name as the column, added to the Dimension
* - An AttributeRelationship with the RowNumber column
*/
#region Add Columns
foreach (TOM.Column TOMColumn in TOMTable.Columns)
{
switch (TOMColumn.Type)
{
case TOM.ColumnType.Data:
//Add the DataColumn corresponding to the SourceColumn, if it does not already exist
TOM.DataColumn TOMDataColumn = (TOM.DataColumn)TOMColumn;
if (!SchemaTable.Columns.Contains(TOMDataColumn.SourceColumn))
{
SchemaTable.Columns.Add(new System.Data.DataColumn(((TOM.DataColumn)TOMColumn).SourceColumn));
}
System.Data.OleDb.OleDbType ColumnDataType = DataTypeHelper.ToOleDbType(TOMDataColumn.DataType);
AMO.DimensionAttribute NormalAttribute = AMODatabase.Dimensions[TOMTable.Name].Attributes.Add(TOMDataColumn.Name, TOMDataColumn.Name);
NormalAttribute.Usage = AMO.AttributeUsage.Regular;
NormalAttribute.KeyUniquenessGuarantee = false;
NormalAttribute.KeyColumns.Add(new AMO.DataItem(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName, ColumnDataType));
NormalAttribute.KeyColumns[0].Source = new AMO.ColumnBinding(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName);
NormalAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
NormalAttribute.NameColumn = new AMO.DataItem(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName, System.Data.OleDb.OleDbType.WChar);
NormalAttribute.NameColumn.Source = new AMO.ColumnBinding(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName);
NormalAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
NormalAttribute.OrderBy = AMO.OrderBy.Key;
AMO.AttributeRelationship NormalAttributeRelationship = AMODatabase.Dimensions[TOMTable.Name].Attributes[RowNumberColumnName].AttributeRelationships.Add(NormalAttribute.ID);
NormalAttribute.AttributeHierarchyVisible = !TOMDataColumn.IsHidden;
NormalAttribute.Description = TOMDataColumn.Description;
NormalAttribute.AttributeHierarchyDisplayFolder = TOMDataColumn.DisplayFolder;
//Add Translations to the CalculatedAttribute
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMColumn))
if (AMOTranslation != null)
{
NormalAttribute.Translations.Add(new AMO.AttributeTranslation {
Caption = AMOTranslation.Caption, Description = AMOTranslation.Description, DisplayFolder = AMOTranslation.DisplayFolder
});
}
}
NormalAttributeRelationship.Cardinality = AMO.Cardinality.Many;
NormalAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
break;
case TOM.ColumnType.Calculated:
TOM.CalculatedColumn TOMCalculatedColumn = (TOM.CalculatedColumn)TOMColumn;
System.Data.OleDb.OleDbType CalculatedColumnDataType = DataTypeHelper.ToOleDbType(TOMColumn.DataType);
//Add Attribute to the Dimension
AMO.Dimension dim = AMODatabase.Dimensions[TOMTable.Name];
AMO.DimensionAttribute CalculatedAttribute = dim.Attributes.Add(TOMCalculatedColumn.Name, TOMCalculatedColumn.Name);
CalculatedAttribute.Usage = AMO.AttributeUsage.Regular;
CalculatedAttribute.KeyUniquenessGuarantee = false;
CalculatedAttribute.KeyColumns.Add(new AMO.DataItem(TOMTable.Name, TOMCalculatedColumn.Name, CalculatedColumnDataType));
CalculatedAttribute.KeyColumns[0].Source = new AMO.ExpressionBinding(TOMCalculatedColumn.Expression);
CalculatedAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
CalculatedAttribute.NameColumn = new AMO.DataItem(TOMTable.Name, TOMCalculatedColumn.Name, System.Data.OleDb.OleDbType.WChar);
CalculatedAttribute.NameColumn.Source = new AMO.ExpressionBinding(TOMCalculatedColumn.Expression);
CalculatedAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
CalculatedAttribute.OrderBy = AMO.OrderBy.Key;
AMO.AttributeRelationship currentAttributeRelationship = dim.Attributes[RowNumberColumnName].AttributeRelationships.Add(CalculatedAttribute.ID);
CalculatedAttribute.AttributeHierarchyVisible = !TOMCalculatedColumn.IsHidden;
CalculatedAttribute.Description = TOMCalculatedColumn.Description;
CalculatedAttribute.AttributeHierarchyDisplayFolder = TOMCalculatedColumn.DisplayFolder;
//Add Translations to the CalculatedAttribute
//Loop through each culture, and add the translation associated with that culture.
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMColumn))
if (AMOTranslation != null)
{
CalculatedAttribute.Translations.Add(new AMO.AttributeTranslation {
Caption = AMOTranslation.Caption, Description = AMOTranslation.Description, DisplayFolder = AMOTranslation.DisplayFolder
});
}
}
currentAttributeRelationship.Cardinality = AMO.Cardinality.Many;
currentAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
//Add CalculatedColumn as attribute to the MeasureGroup
AMO.MeasureGroup mg = Cube.MeasureGroups[TOMTable.Name];
AMO.DegenerateMeasureGroupDimension currentMGDim = (AMO.DegenerateMeasureGroupDimension)mg.Dimensions[TOMTable.Name];
AMO.MeasureGroupAttribute mga = new AMO.MeasureGroupAttribute(TOMCalculatedColumn.Name);
mga.KeyColumns.Add(new AMO.DataItem(TOMTable.Name, TOMCalculatedColumn.Name, System.Data.OleDb.OleDbType.Empty));
mga.KeyColumns[0].Source = new AMO.ExpressionBinding(TOMCalculatedColumn.Expression);
currentMGDim.Attributes.Add(mga);
break;
default:
throw new System.NotImplementedException(string.Format("Cannot deploy Column of type {0}", TOMColumn.Type.ToString()));
}
}
#endregion
//Add sort by columns
foreach (TOM.Column TOMColumn in TOMTable.Columns)
{
if (TOMColumn.SortByColumn != null)
{
AMODatabase.Dimensions[TOMTable.Name].Attributes[TOMColumn.Name].OrderByAttributeID = TOMColumn.SortByColumn.Name;
}
}
#region Add Hierarchies
foreach (TOM.Hierarchy TOMHierarchy in TOMTable.Hierarchies)
{
//Create the Hierarchy, and add it
AMO.Hierarchy AMOHierarchy = AMODatabase.Dimensions[TOMTable.Name].Hierarchies.Add(TOMHierarchy.Name, TOMHierarchy.Name);
AMOHierarchy.Description = TOMHierarchy.Description;
AMOHierarchy.DisplayFolder = TOMHierarchy.DisplayFolder;
AMOHierarchy.AllMemberName = "All";
foreach (TOM.Level TOMLevel in TOMHierarchy.Levels)
{
AMO.Level AMOLevel = AMOHierarchy.Levels.Add(TOMLevel.Name);
AMOLevel.SourceAttribute = AMODatabase.Dimensions[TOMTable.Name].Attributes[TOMLevel.Column.Name];
AMOLevel.Description = TOMLevel.Description;
//Add Translations to the CalculatedAttribute
//Loop through each culture, and add the translation associated with that culture.
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMLevel))
if (AMOTranslation != null)
{
AMOLevel.Translations.Add(AMOTranslation);
}
}
}
}
#endregion
}
#endregion
#region Add Measures
using (AMO.MdxScript MdxScript = Cube.DefaultMdxScript)
{
//Create a "default" measure, required for the cube to function.
MdxScript.Commands.Add(new AMO.Command("CALCULATE;"
+ "CREATE MEMBER CURRENTCUBE.Measures.[_No measures defined] AS 1, VISIBLE = 0;"
+ "ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, Default_Member = [_No measures defined];"));
foreach (TOM.Table TOMTable in TOMModel.Tables)
{
foreach (TOM.Measure TOMMeasure in TOMTable.Measures)
{
//Create the Command, which contains the definition of the Measure
AMO.Command AMOCommand = new AMO.Command(string.Format("CREATE MEASURE '{0}'[{1}]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.Expression));
if (TOMMeasure.KPI != null)
{
#region Add KPI
//Start building the final command - we add to it as we go along
string FinalKPICreate = string.Format(
"CREATE KPI CURRENTCUBE.[{1}] AS Measures.[{1}], ASSOCIATED_MEASURE_GROUP = '{0}'",
TOMTable.Name,
TOMMeasure.Name
);
//Goal/Target
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TargetExpression))
{
AMOCommand.Text += Environment.NewLine + string.Format(string.Format("CREATE MEASURE '{0}'[_{1} Goal]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.KPI.TargetExpression));
FinalKPICreate += string.Format(", GOAL = Measures.[_{0} Goal]", TOMMeasure.Name.Replace("]", "]]"));
}
//Status
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusExpression))
{
AMOCommand.Text += Environment.NewLine + string.Format(string.Format("CREATE MEASURE '{0}'[_{1} Status]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.KPI.StatusExpression));
FinalKPICreate += string.Format(", STATUS = Measures.[_{0} Status]", TOMMeasure.Name.Replace("]", "]]"));
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusGraphic))
{
FinalKPICreate += string.Format(", STATUS_GRAPHIC = '{0}'", TOMMeasure.KPI.StatusGraphic);
}
}
//Trend
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TrendExpression))
{
AMOCommand.Text += Environment.NewLine + string.Format(string.Format("CREATE MEASURE '{0}'[_{1} Trend]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.KPI.TrendExpression));
FinalKPICreate += string.Format(", TREND = Measures.[_{0} Trend]", TOMMeasure.Name.Replace("]", "]]"));
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TrendGraphic))
{
FinalKPICreate += string.Format(", TREND_GRAPHIC = '{0}'", TOMMeasure.KPI.TrendGraphic);
}
}
AMOCommand.Text += Environment.NewLine + FinalKPICreate + ";";
//Create calculation properties, hiding the "fake" measures (if they exist)
//Target
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TargetExpression))
{
AMO.CalculationProperty TargetCalculationProperty = new AMO.CalculationProperty(string.Format("[_{0} Goal]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
TargetCalculationProperty.Description = TOMMeasure.KPI.TargetDescription;
TargetCalculationProperty.CalculationType = AMO.CalculationType.Member;
TargetCalculationProperty.Visible = false;
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TargetFormatString))
{
TargetCalculationProperty.FormatString = "'" + TOMMeasure.KPI.TargetFormatString + "'";
}
MdxScript.CalculationProperties.Add(TargetCalculationProperty);
}
//Status
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusExpression))
{
AMO.CalculationProperty StatusCalculationProperty = new AMO.CalculationProperty(string.Format("[_{0} Status]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
StatusCalculationProperty.Description = TOMMeasure.KPI.StatusDescription;
StatusCalculationProperty.CalculationType = AMO.CalculationType.Member;
StatusCalculationProperty.Visible = false;
MdxScript.CalculationProperties.Add(StatusCalculationProperty);
}
//Trend
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusExpression))
{
AMO.CalculationProperty TrendCalculationProperty = new AMO.CalculationProperty(string.Format("[_{0} Trend]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
TrendCalculationProperty.Description = TOMMeasure.KPI.TrendDescription;
TrendCalculationProperty.CalculationType = AMO.CalculationType.Member;
TrendCalculationProperty.Visible = false;
MdxScript.CalculationProperties.Add(TrendCalculationProperty);
}
//Create the KPI calculation property
AMO.CalculationProperty KPICalculationProperty = new AMO.CalculationProperty(string.Format("KPIs.[{0}]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
KPICalculationProperty.Description = TOMMeasure.KPI.Description;
KPICalculationProperty.CalculationType = AMO.CalculationType.Member;
MdxScript.CalculationProperties.Add(KPICalculationProperty);
#endregion
}
//Add the Command to the MdxScript
MdxScript.Commands.Add(AMOCommand);
//Create the Calculation Property, which contains the various properties of the Measure
AMO.CalculationProperty CalculationProperty = new AMO.CalculationProperty(string.Format("[{0}]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
CalculationProperty.Description = TOMMeasure.Description;
CalculationProperty.DisplayFolder = TOMMeasure.DisplayFolder;
CalculationProperty.CalculationType = AMO.CalculationType.Member;
CalculationProperty.Visible = !TOMMeasure.IsHidden;
if (!string.IsNullOrWhiteSpace(TOMMeasure.FormatString))
{
CalculationProperty.FormatString = "'" + TOMMeasure.FormatString + "'";
}
//Add Translations to the Calculation property
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMMeasure))
if (AMOTranslation != null)
{
CalculationProperty.Translations.Add(AMOTranslation);
}
}
//Finally, add the CalculationProperty to the MDX script
MdxScript.CalculationProperties.Add(CalculationProperty);
}
}
}
#endregion
#region Add Relationships
//Relationships are just TOO awful. They are placed in their own helper function as a result.
foreach (TOM.SingleColumnRelationship TOMRelationship in TOMModel.Relationships)
{
RelationshipHelper.CreateRelationship(TOMRelationship, AMODatabase);
}
#endregion
#region Add Perspectives
foreach (TOM.Perspective TOMPerspective in TOMModel.Perspectives)
{
AMO.Perspective AMOPerspective = new AMO.Perspective(TOMPerspective.Name);
foreach (TOM.PerspectiveTable TOMTable in TOMPerspective.PerspectiveTables)
{
//Add Perspective Dimension
AMO.PerspectiveDimension PerspectiveDimension = new AMO.PerspectiveDimension(TOMTable.Name);
//Perspective Columns
foreach (TOM.PerspectiveColumn TOMPerspectiveColumn in TOMTable.PerspectiveColumns)
{
PerspectiveDimension.Attributes.Add(TOMPerspectiveColumn.Name);
}
//Perspective Hierarchies
foreach (TOM.PerspectiveHierarchy TOMPerspectiveHierarchy in TOMTable.PerspectiveHierarchies)
{
PerspectiveDimension.Hierarchies.Add(TOMPerspectiveHierarchy.Name);
}
//Add Perspective MeasureGroup
AMO.PerspectiveMeasureGroup PerspectiveMeasureGroup = new AMO.PerspectiveMeasureGroup(TOMTable.Name);
//Perspective Measures
//In this case, ']' is NOT "double quoted", unlike the calculation references, s
foreach (TOM.PerspectiveMeasure TOMPerspectiveMeasure in TOMTable.PerspectiveMeasures)
{
AMOPerspective.Calculations.Add('[' + TOMPerspectiveMeasure.Name + ']');
}
//VS does not add KPI's to Perspectives, so I have no idea how to do this...
//TODO: Add KPIs to perspectives
}
AMODatabase.Cubes[0].Perspectives.Add(AMOPerspective);
}
#endregion
#region Roles
foreach (TOM.ModelRole TOMRole in TOMDatabase.Model.Roles)
{
//Ceate database role
AMO.Role DatabaseRole = AMODatabase.Roles.Add(TOMRole.Name);
DatabaseRole.Description = TOMRole.Description;
//Add Members to Database role
foreach (TOM.ModelRoleMember TOMMember in TOMRole.Members)
{
DatabaseRole.Members.Add(new AMO.RoleMember
{
Name = TOMMember.MemberName,
Sid = TOMMember.MemberID
});
}
//Add DatabasePermission
AMO.DatabasePermission DatabasePermission = AMODatabase.DatabasePermissions.Add(TOMRole.Name, TOMRole.Name, TOMRole.Name);
//Add CubePermission
AMO.CubePermission CubePermission = AMODatabase.Cubes[0].CubePermissions.Add(TOMRole.Name, TOMRole.Name, TOMRole.Name);
switch (TOMRole.ModelPermission)
{
case TOM.ModelPermission.Administrator:
//Add ReadDefinition, Read, and Administer to DatabasePermission
DatabasePermission.ReadDefinition = AMO.ReadDefinitionAccess.Allowed;
DatabasePermission.Read = AMO.ReadAccess.Allowed;
DatabasePermission.Administer = true;
//Nothing extra to add to CubePermission, that is not added after this
// switch statement
break;
case TOM.ModelPermission.None:
//Add no permissions to DatabasePermission.
break;
case TOM.ModelPermission.Read:
//Add only Read access to Database Permission
DatabasePermission.Read = AMO.ReadAccess.Allowed;
//CubePermission
CubePermission.Read = AMO.ReadAccess.Allowed;
break;
case TOM.ModelPermission.ReadRefresh:
DatabasePermission.Read = AMO.ReadAccess.Allowed;
DatabasePermission.Process = true;
//CubePermission
CubePermission.Read = AMO.ReadAccess.Allowed;
CubePermission.Process = true;
break;
case TOM.ModelPermission.Refresh:
DatabasePermission.Process = true;
//CubePermission
CubePermission.Process = true;
break;
}
CubePermission.ReadSourceData = AMO.ReadSourceDataAccess.None;
//Finally, add the row filters
foreach (TOM.TablePermission TablePermission in TOMRole.TablePermissions)
{
AMO.DimensionPermission DimensionPermission = new AMO.DimensionPermission(TOMRole.Name, TOMRole.Name, TOMRole.Name);
DimensionPermission.AllowedRowsExpression = TablePermission.FilterExpression;
AMODatabase.Dimensions.GetByName(TablePermission.Table.Name).DimensionPermissions.Add(DimensionPermission);
}
}
#endregion
return(AMODatabase);
}
public static void RelationshipAlterActive(AMO.Database tabularDatabase,
string pkTableName,
string pkColumnName,
string foreignTableName,
string foreignColumnName,
bool active,
bool updateInstance = true)
{
// Terminology note:
// - the Foreing side of the relationship is named the From end in AMO
// - the PK side of the relationship in named the To end in AMO
//
// Relationships flow FROM the foreign side of the relationship
// TO the primary key side of the relationship in AMO
// ==> [Inactive] Relationship information is stored in the Foreign Dimension object in a relationship object
// ==> [Active] Relationship information is stored in the MeasureGroup object of the table in a ReferenceMeasureGroupDimension object
//
// Major steps in adding a relationship
//
// - Validate required input arguments and other initial preparations
// - If Activating:
// - Verify relationship exist before Activating
// - If relationship doesn't exist throw an error
// - Verify no multipath or alternate path will be created by activating this relationship
// - If ReferenceMeasureGroupDimension, for the PK table, doesn't exist add it to the Dimensions collection in the MeasureGroup
// - replicate attributes (columns) from Dimension
// - If ReferenceMeasureGroupDimension exists, update relationshipID and IntermediateGranularity
// - To the Dimensions collection in the MeasureGroup
// - Add all other intermediate relationships from 'Ancestors' paths
// - Add all other intermediate relationships to the 'Descendants' paths --> Update MeasureGroups in the 'Descendants' paths
//
//
// T1 T2 T5 T6 T7
// ---- ----- ----- ----- -----
// |a1| <-- |a2 | |a5 | |a6 | |a7 |
// | | |a2i| <-- |a5i| /| | | |a7i|
// | | | | | | / ------| |a6i| <-- | |
// ---- ----- | | / | | | -----
// | | \ Rel56 | | |
// T3 T4 | | \ ------| | | T8
// ---- ----- | | \| | | -----
// |a3| <-- |a4 | | | | | <-- |a8 |
// | | |a4i| <-- |a5j| | | |a8i|
// | | | | | | | | | |
// ---- ----- ----- ----- -----
//
// \_________ ____________/ \________ ________/
// \/ \/
// Ancestors Descendants
//
//
// When adding Rel56, as active, to the model, on top of everything that already exists, you have to:
//
// --> Add a ReferenceMeasureGroupDimension or updating existing one: (T5, T6.a6i) will include: Materialization:=Regular, RelationshipId:=<relationship id>
//
// --> After adding (T5, T6.a6i) to T6, you have to:
//
// Add Add Add
// to T6 to T7 to T8
// ...... ...... ......
// (T5, T6.a6i) (T5, T6.a6i)
// (T2, T5.a5i) (T2, T5.a5i) (T2, T5.a5i)
// (T1, T2.a2i) (T1, T2.a2i) (T1, T2.a2i)
// (T4, T5.a5j) (T4, T5.a5j) (T4, T5.a5j)
// (T3, T4.a4i) (T3, T4.a4i) (T3, T4.a4i)
//
// --> as reference dimensions to the MeasureGroup
// --> because the relationships between T6<--T7 and T6<--T8 already exist, the materialization and relationship
// definitions were added at the time the relationship were created ==> no need to do anything like
// on T6.
//
// - If De-Activating
// - Verify relationship exist before De-Activating
// - If relationship doesn't exist throw an error
// - Remove All ReferenceMeasureGroupDimension, for the PK table and Down Below tables, from MeasureGroup
//
// Note: In AMO, strings as indexers refer to the ID of the object, not the name
//
// 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.
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (pkTableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("pkTableName");
}
if (pkColumnName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("pkColumnName");
}
if (foreignTableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("foreignTableName");
}
if (foreignColumnName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("foreignColumnName");
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
pkTableName = pkTableName.Trim();
pkColumnName = pkColumnName.Trim();
foreignTableName = foreignTableName.Trim();
foreignColumnName = foreignColumnName.Trim();
// - Obtain Id's
string pkTableId = tabularDatabase.Dimensions.GetByName(pkTableName).ID;
string pkColumnId = tabularDatabase.Dimensions[pkTableId].Attributes.GetByName(pkColumnName).ID;
string foreignTableId = tabularDatabase.Dimensions.GetByName(foreignTableName).ID;
string foreignColumnId = tabularDatabase.Dimensions[foreignTableId].Attributes.GetByName(foreignColumnName).ID;
#endregion
RelationshipGraph currentRelationshipsGraph;
// Branch on 'active'
// - true ==> set relationship to Active
// - false ==> set relationship to Inactive
switch (active)
{
case true:
#region set relationship to Active
// Verify relationship existence
// --> Throw an error if relationship doesn't exist
// - Define placeholder for the relationship id; it's needed later
string relationshipId = RelationshipTryGetRelationshipId(tabularDatabase, pkTableName, pkColumnName, foreignTableName, foreignColumnName);
if (relationshipId.IsNullOrEmptyOrWhitespace())
{
throw new InvalidOperationException(Resources.RelationshipDoesNotExistInvalidOperationException);
}
// Verify that activating this relationship does not violate the rule of no alternate paths when it will be created
// Build a graph of the actual state of relationships
currentRelationshipsGraph = new RelationshipGraph(tabularDatabase);
if (currentRelationshipsGraph.RelationshipAlternatePathExists(pkTableId, foreignTableId))
{
throw new InvalidOperationException(Resources.RelationshipViolatesAlternatePathRuleInvalidOperationException);
}
// The 'activeness' of a relationship is defined by the existence of a ReferenceMeasureGroupDimension
// that include Materialization as Regular
using (AMO.Cube modelCube = tabularDatabase.Cubes[0])
using (AMO.MeasureGroup foreignTableMG = modelCube.MeasureGroups[foreignTableId])
{
if (!foreignTableMG.Dimensions.Contains(pkTableId))
{
// Creating the ReferenceMeasureGroupDimension that defines the 'Activeness' of a relationship
RelationshipAddReferenceMeasureGroupDimension(tabularDatabase, foreignTableId, new RelationshipPair(new FullName(pkTableId, pkColumnId), new FullName(foreignTableId, foreignColumnId)), relationshipId);
// Adding Intermediate relationships for all tables in the paths from PrimaryKeyEndBelow for direct ForeignKeyEnd
foreach (RelationshipPair primaryKeyDownRelationship in currentRelationshipsGraph.RelationshipsListPrimaryKeyDown(pkTableId))
{
RelationshipAddReferenceMeasureGroupDimension(tabularDatabase, foreignTableId, primaryKeyDownRelationship);
}
// Adding Intermediate relationships for all tables in the paths from PrimaryKeyEndBelow in each of the tables
// that are ForeignKeyEndAbove
foreach (string foreignKeyTableUpId in currentRelationshipsGraph.TableListForeignKeyUp(foreignTableId))
{
using (AMO.MeasureGroup foreignKeyUpMG = tabularDatabase.Cubes[0].MeasureGroups[foreignKeyTableUpId])
{
RelationshipAddReferenceMeasureGroupDimension(tabularDatabase, foreignKeyUpMG.ID, new RelationshipPair(new FullName(pkTableId, pkColumnId), new FullName(foreignTableId, foreignColumnId)));
foreach (RelationshipPair primaryKeyDownRelationship in currentRelationshipsGraph.RelationshipsListPrimaryKeyDown(pkTableId))
{
RelationshipAddReferenceMeasureGroupDimension(tabularDatabase, foreignKeyUpMG.ID, primaryKeyDownRelationship);
}
}
}
}
else
{
// Because the ReferenceMeasureGroupDimension already exists, this probably means just a change on the foreign key used as Active
using (AMO.ReferenceMeasureGroupDimension currentReferenceMGDim = (AMO.ReferenceMeasureGroupDimension)foreignTableMG.Dimensions[pkTableId])
{
currentReferenceMGDim.RelationshipID = relationshipId;
currentReferenceMGDim.IntermediateGranularityAttributeID = foreignColumnId;
}
}
}
currentRelationshipsGraph.Clear();
break;
#endregion
case false:
#region set relationship to Inactive
// Verify relationship existence
// --> If relationship doesn't exist throw an error
if (!RelationshipExists(tabularDatabase, pkTableName, pkColumnName, foreignTableName, foreignColumnName))
{
throw new InvalidOperationException(Resources.RelationshipDoesNotExistInvalidOperationException);
}
// Build a graph of the actual state of relationships
currentRelationshipsGraph = new RelationshipGraph(tabularDatabase);
// Remove the ReferenceMeasureGroupDimension to remove the 'Activeness'
tabularDatabase.Cubes[0].MeasureGroups[foreignTableId].Dimensions.Remove(pkTableId);
// Removing Intermediate relationships for all tables in the paths from PrimaryKeyEndBelow for direct ForeignKeyEnd
foreach (string primaryKeyDownTableId in currentRelationshipsGraph.TableListPrimaryKeyDown(pkTableId))
{
tabularDatabase.Cubes[0].MeasureGroups[foreignTableId].Dimensions.Remove(primaryKeyDownTableId);
}
// Adding Intermediate relationships for all tables in the paths from PrimaryKeyEndBelow in each of the tables
// that are ForeignKeyEndAbove
foreach (string foreignKeyTableUpId in currentRelationshipsGraph.TableListForeignKeyUp(foreignTableId))
{
tabularDatabase.Cubes[0].MeasureGroups[foreignKeyTableUpId].Dimensions.Remove(pkTableId);
foreach (string primaryKeyDownTableId in currentRelationshipsGraph.TableListPrimaryKeyDown(pkTableId))
{
tabularDatabase.Cubes[0].MeasureGroups[foreignKeyTableUpId].Dimensions.Remove(primaryKeyDownTableId);
}
}
currentRelationshipsGraph.Clear();
break;
#endregion
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void PartitionAlterMerge(AMO.Database tabularDatabase,
string tableName,
string destinationPartitionName,
bool updateInstance = true,
params string[] sourcePartitionNames)
{
// Major steps in Merging Partitions from a table in the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Obtain source partitions Ids
// - Obtain destination partition
// - Merge source partitions into destination partition
// - Drop/delete source partitions (to eliminate duplication)
//
// 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.
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (destinationPartitionName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("destinationPartitionName");
}
bool sourcePartitionNamesEmpty = true;
foreach (string sourcePartitionName in sourcePartitionNames)
{
if (!sourcePartitionName.IsNullOrEmptyOrWhitespace())
{
sourcePartitionNamesEmpty = false;
break;
}
}
if (sourcePartitionNamesEmpty)
{
throw new ArgumentNullException("sourcePartitionNames");
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
destinationPartitionName = destinationPartitionName.Trim();
// - Obtain table name in DSV
string datasourceTableName = tabularDatabase.Dimensions.GetByName(tableName).ID;
// - Obtain source partitions Ids
// - Obtain destination partition
// - Merge source partitions into destination partition
// - Drop/delete source partitions (to eliminate duplication)
List <string> sourcePartitionIds = new List <string>();;
List <AMO.Partition> sourcePartitions = new List <AMO.Partition>();
using (AMO.MeasureGroup tableMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups[datasourceTableName])
using (AMO.Partition destinationPartition = tableMeasureGroup.Partitions.GetByName(destinationPartitionName))
{
foreach (string sourcePartitionName in sourcePartitionNames)
{
if (!sourcePartitionName.IsNullOrEmptyOrWhitespace())
{
sourcePartitionIds.Add(tableMeasureGroup.Partitions.GetByName(sourcePartitionName).ID);
sourcePartitions.Add(tableMeasureGroup.Partitions.GetByName(sourcePartitionName));
}
}
destinationPartition.Merge(sourcePartitions);
foreach (string sourcePartitionId in sourcePartitionIds)
{
tableMeasureGroup.Partitions.Remove(sourcePartitionId, true);
}
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void PartitionAlterConnection(AMO.Database tabularDatabase,
string tableName,
string partitionName,
string connectionName,
bool updateInstance = true)
{
// Major steps in updating or Altering the Connection or DataSource of a Partition in a table in the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Update Query Binding in Measure Group
//
// Note: This function only updates the DataSource or Connection; does not change the select statement
// of the partition
//
//
// 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.
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (partitionName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("partitionName");
}
if (connectionName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("connectionName");
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
partitionName = partitionName.Trim();
connectionName = connectionName.Trim();
// - Obtain table name in DSV
string datasourceTableName = tabularDatabase.Dimensions.GetByName(tableName).ID;
string connectionId = tabularDatabase.DataSources.GetByName(connectionName).ID;
using (AMO.MeasureGroup tableMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups[datasourceTableName])
using (AMO.Partition partition = tableMeasureGroup.Partitions.GetByName(partitionName))
{
AMO.QueryBinding qb = partition.Source as AMO.QueryBinding;
partition.Source = new AMO.QueryBinding(connectionId, qb.QueryDefinition);
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void PartitionAdd(AMO.Database tabularDatabase,
string tableName,
string partitionName,
string selectStatement,
bool updateInstance = true,
AMO.ProcessType?processPartition = null,
string alternateConnectionName = null)
{
// Major steps in adding a Partition to a table in the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Defining Datasource to use
// - Adding Partition to Measure Group
//
// 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.
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (partitionName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("partitionName");
}
if (selectStatement.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("selectStatement");
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
partitionName = partitionName.Trim();
selectStatement = selectStatement.Trim();
// - Obtain table name in DSV
string datasourceTableName = tabularDatabase.Dimensions.GetByName(tableName).ID;
// - Obtain DataSourceId
string dataSourceId;
if (alternateConnectionName == null)
{
dataSourceId = tabularDatabase.DataSourceViews[0].DataSourceID;
}
else
{
dataSourceId = tabularDatabase.DataSources.GetByName(alternateConnectionName.Trim()).ID;
}
using (AMO.MeasureGroup tableMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups[datasourceTableName])
using (AMO.Partition partition = new AMO.Partition(partitionName, partitionName))
{
partition.StorageMode = AMO.StorageMode.InMemory;
partition.ProcessingMode = AMO.ProcessingMode.Regular;
partition.Source = new AMO.QueryBinding(dataSourceId, selectStatement);
partition.Type = AMO.PartitionType.Data;
tableMeasureGroup.Partitions.Add(partition);
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
// Process default partition
if (processPartition != null)
{
// Throw exception if server instance is outdated and user requests process
if (!updateInstance)
{
throw new InvalidOperationException(Resources.ProcessRequestedForOutdatedModelInvalidOperationException);
}
// Now the partition can be processed according to the user request
PartitionProcess(tabularDatabase, tableName, partitionName, processPartition.Value);
}
}
public static void PartitionProcess(AMO.Database tabularDatabase,
string tableName,
string partitionName,
AMO.ProcessType processValue)
{
// Major steps in Processing a Partition in a table in the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Process partition
//
// Note: There are no validations on the ProcessType requested and whatever value is passed it's used
//
// Note: For partitions, in tabular models, the following ProcessType values are 'valid' or have sense:
// - ProcessDefault ==> verifies if data (at partition level) or recalc is required and issues coresponding internal process tasks
// - ProcessFull ==> forces data upload (on given partition) and recalc, regardless of table status
// - ProcessData ==> forces data upload only (on given partition); does not issue an internal recalc process task
// - ProcessClear ==> clears all table data (on given partition)
//
//
// 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.
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (partitionName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("partitionName");
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
partitionName = partitionName.Trim();
// - Obtain table name in DSV
string datasourceTableName = tabularDatabase.Dimensions.GetByName(tableName).ID;
// Process
using (AMO.MeasureGroup tableMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups[datasourceTableName])
using (AMO.Partition partition = tableMeasureGroup.Partitions.GetByName(partitionName))
{
partition.Process(processValue);
}
}
/// <summary>
/// Adds an 'empty' table to the tabular model; no calculatedColumn informationis added
/// </summary>
/// <param name="tabularDatabase">A reference to an AMO database object</param>
/// <param name="datasourceTableName">A string with the name of the table from where the data will come to populate the destination table</param>
/// <param name="tableName">A string with the name of the tabular table added</param>
public static void TableAddEmptyTable(AMO.Database tabularDatabase,
string datasourceTableName,
string tableName,
string defaultPartitionFilterClause = null,
bool updateInstance = true
)
{
// Table creation strategy:
// Because a table is a combination of a Dimension and a MeasureGroup
// there are different ways to create the table, here we have two
// possibilities
//
// A Create the entire Dimension object with attributes, add the
// reference to the dimension in the cube, create the entire
// MeasureGroup (associated to the dimension and attributes),
// add the default partition to the MeasureGroup, update Primary
// Key attribute from source table and, finally, process
// partition (if user wants it).
// This sequence of steps implies adding all table columns to
// the dimension and later iterate again over the same columns
// to add them to the 'degenerated' dimension of the MeasureGroup
//
// B Create an 'empty' Dimension object (with only the rownumber
// attribute), add the reference to the dimension in the cube,
// create the MeasureGroup object (based on the 'empty'dimension),
// add the default partition, add all columns in the source
// table to both dimension and degenerated dimension in
// MeasureGroup, update Primary Key and, finally, process
// partition (if user wants it).
// This sequence of steps implies creating the skeleton
// infrastructure of a table and later add all columns using
// ColumnAdd function.
// This is the approach used to create a table in this sample
//
// Major steps in creating a table in the database
//
// - Validate required input arguments
// - Verify there is 1 and only 1 cube in the database; as part of the initial conditions validations
//
// - Create empty local copy of Dimension object
// - Add Dimension reference to cube
// - Add empty MeasureGroup to cube
// - Adding default Measure to MeasureGroup
// - Add 'Dimension' to MeasureGroup
// - Add partition to MeasureGroup
//
// 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: 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.
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (datasourceTableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("datasourceTableName");
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
// Validate other intitial conditions: Verify there is only one cube in the database
if (tabularDatabase.Cubes.Count != 1)
{
throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, Resources.InvalidNumberOfCubesInvalidOperationException, tabularDatabase.Cubes.Count));
}
// Create empty local copy of Dimension object in two (2) steps:
// - Define Dimension general properties
// - Manualy add "RowNumber" calculatedColumn
//
// Define Dimension general properties
string rowNumberColumnName = string.Format(CultureInfo.InvariantCulture, "RowNumber_{0}", Guid.NewGuid()); // Making sure the RowNumber calculatedColumn has a unique name
using (AMO.Dimension tableDimension = tabularDatabase.Dimensions.Add(tableName, datasourceTableName))
{
tableDimension.Source = new AMO.DataSourceViewBinding(tabularDatabase.DataSourceViews[0].ID);
tableDimension.StorageMode = AMO.DimensionStorageMode.InMemory;
tableDimension.UnknownMember = AMO.UnknownMemberBehavior.AutomaticNull;
tableDimension.UnknownMemberName = "Unknown";
tableDimension.ErrorConfiguration = new AMO.ErrorConfiguration();
tableDimension.ErrorConfiguration.KeyNotFound = AMO.ErrorOption.IgnoreError;
tableDimension.ErrorConfiguration.KeyDuplicate = AMO.ErrorOption.ReportAndStop;
tableDimension.ErrorConfiguration.NullKeyNotAllowed = AMO.ErrorOption.ReportAndStop;
tableDimension.ProactiveCaching = new AMO.ProactiveCaching();
TimeSpan defaultProactiveChachingTimeSpan = new TimeSpan(0, 0, -1);
tableDimension.ProactiveCaching.SilenceInterval = defaultProactiveChachingTimeSpan;
tableDimension.ProactiveCaching.Latency = defaultProactiveChachingTimeSpan;
tableDimension.ProactiveCaching.SilenceOverrideInterval = defaultProactiveChachingTimeSpan;
tableDimension.ProactiveCaching.ForceRebuildInterval = defaultProactiveChachingTimeSpan;
tableDimension.ProactiveCaching.Source = new AMO.ProactiveCachingInheritedBinding();
// Manualy add a "RowNumber" attribute as the key attribute of the dimension
// "RowNumber" is a required calculatedColumn for a tabular model and has to be of type AMO.AttributeType.RowNumber and binding AMO.RowNumberBinding
// The name of the "RowNumber" attribute can be any name, as long as type and binding are correctly set
// By default the MS client tools set the calculatedColumn name and calculatedColumn ID of the RowNumber attribute to
// "RowNumber"; and, to "InternalRowNumber" if there is a collition with a user calculatedColumn named "RowNumber"
// In this sample, to avoid problems with any customer table that contains a calculatedColumn named "RowNumber" and/or "InternalRowNumber"
// the Name and Id value of the calculatedColumn (in the dimension object) will be renamed to
// 'RowNumber_<NewGuid()>';
// For that purpose the variable rowNumberColumnId was defined above
using (AMO.DimensionAttribute rowNumber = tableDimension.Attributes.Add(rowNumberColumnName, rowNumberColumnName))
{
rowNumber.Type = AMO.AttributeType.RowNumber;
rowNumber.KeyUniquenessGuarantee = true;
rowNumber.Usage = AMO.AttributeUsage.Key;
rowNumber.KeyColumns.Add(new AMO.DataItem());
rowNumber.KeyColumns[0].DataType = System.Data.OleDb.OleDbType.Integer;
rowNumber.KeyColumns[0].DataSize = 4;
rowNumber.KeyColumns[0].NullProcessing = AMO.NullProcessing.Error;
rowNumber.KeyColumns[0].Source = new AMO.RowNumberBinding();
rowNumber.NameColumn = new AMO.DataItem();
rowNumber.NameColumn.DataType = System.Data.OleDb.OleDbType.WChar;
rowNumber.NameColumn.DataSize = 4;
rowNumber.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
rowNumber.NameColumn.Source = new AMO.RowNumberBinding();
rowNumber.OrderBy = AMO.OrderBy.Key;
rowNumber.AttributeHierarchyVisible = false;
}
}
// Add Dimension reference to cube
tabularDatabase.Cubes[0].Dimensions.Add(datasourceTableName, tableName, datasourceTableName);
// Add empty MeasureGroup to cube
using (AMO.MeasureGroup tableMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups.Add(tableName, datasourceTableName))
{
tableMeasureGroup.StorageMode = AMO.StorageMode.InMemory;
tableMeasureGroup.ProcessingMode = AMO.ProcessingMode.Regular;
// Add default Measure to MeasureGroup
string defaultMeasureID = string.Concat("_Count ", tableName);
using (AMO.Measure defaultMeasure = tableMeasureGroup.Measures.Add(defaultMeasureID, defaultMeasureID))
using (AMO.RowBinding defaultMeasureRowBinding = new AMO.RowBinding(datasourceTableName))
using (AMO.DataItem defaultMeasureSource = new AMO.DataItem(defaultMeasureRowBinding))
{
defaultMeasure.AggregateFunction = AMO.AggregationFunction.Count;
defaultMeasure.DataType = AMO.MeasureDataType.BigInt;
defaultMeasure.Visible = false;
defaultMeasureSource.DataType = System.Data.OleDb.OleDbType.BigInt;
defaultMeasure.Source = defaultMeasureSource;
}
// Add 'Dimension' to MeasureGroup
using (AMO.DegenerateMeasureGroupDimension defaultMGDim = new AMO.DegenerateMeasureGroupDimension(tableName))
using (AMO.MeasureGroupAttribute mga = new AMO.MeasureGroupAttribute(rowNumberColumnName))
using (AMO.ColumnBinding rowNumberColumnBinding = new AMO.ColumnBinding(datasourceTableName, rowNumberColumnName))
using (AMO.DataItem rowNumberKeyColumn = new AMO.DataItem(rowNumberColumnBinding))
{
defaultMGDim.ShareDimensionStorage = AMO.StorageSharingMode.Shared;
defaultMGDim.CubeDimensionID = datasourceTableName;
mga.Type = AMO.MeasureGroupAttributeType.Granularity;
rowNumberKeyColumn.DataType = System.Data.OleDb.OleDbType.Integer;
mga.KeyColumns.Add(rowNumberKeyColumn);
defaultMGDim.Attributes.Add(mga);
tableMeasureGroup.Dimensions.Add(defaultMGDim);
}
// Add default partition to MeasureGroup
StringBuilder partitionSqlStatement = new StringBuilder();
partitionSqlStatement.Append("SELECT * FROM [");
partitionSqlStatement.Append(datasourceTableName);
partitionSqlStatement.Append("]");
if (defaultPartitionFilterClause != null)
{
partitionSqlStatement.Append(" WHERE ");
partitionSqlStatement.Append(defaultPartitionFilterClause);
}
// Create partition, but do not update server instance until comming back and procesing what the user requests in 'updateInstance'
// Note: As a best practice every time the object model is altered a database update needs to be issued; however, in this case
// to avoid multiple database updates while creating one major object (ie, Table) we are invoking PartitionAdd with NO updateInstance
PartitionAdd(tabularDatabase, tableName, tableName, partitionSqlStatement.ToString(), false);
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}