public bool VerifySelections()
{
try
{
if (VerifyServer())
{
if (srv.Databases.ContainsName(cmbDatabase.Text))
db = srv.Databases.GetByName(cmbDatabase.Text);
else
return false;
if (db == null || !db.Dimensions.ContainsName(cmbDimension.Text))
return false;
dim = db.Dimensions.GetByName(cmbDimension.Text);
for (int i = 0; i < PCAttributesToInclude.Count; i++)
if (!dim.Attributes.ContainsName(PCAttributesToInclude[i]))
{
PCAttributesToInclude.RemoveAt(i);
i--;
}
for (int i = 0; i < NonPCHierarchiesToInclude.Count; i++)
if (!dim.Attributes.ContainsName(NonPCHierarchiesToInclude[i]))
{
NonPCHierarchiesToInclude.RemoveAt(i);
i--;
}
return true;
}
return false;
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// Extracts column names of the specified table from database object
/// </summary>
/// <param name="tableName">Table name that owns columns function should return</param>
/// <returns>Returns list of column names</returns>
/// <exception cref="System.ArgumentException">Thrown when table name is not found in database.</exception>
/// <exception cref="System.InvalidOperationException">Thrown when database object is null.</exception>
public List <String> GetColumnNames(string tableName)
{
if (null == this._database)
{
throw new InvalidOperationException("Current database is NULL. GetColumnNames function cannot be called when database object is NULL");
}
List <string> columnNames = new List <string>();
Microsoft.AnalysisServices.Dimension dim = this._database.Dimensions.FindByName(tableName);
if (null == dim)
{
throw new ArgumentException(string.Format("Table '{0}' was not found in database {1}", tableName, this._database.Name), "tableName");
}
foreach (DimensionAttribute dimAttr in dim.Attributes)
{
if (_rowNumberColumnNames.Any(i => { return(string.Equals(i, dimAttr.Name, StringComparison.CurrentCultureIgnoreCase)); }))
{
continue; // Hide the RowNumber column
}
columnNames.Add(dimAttr.Name);
}
return(columnNames);
}
internal static bool CreateRelationship(TOM.SingleColumnRelationship TOMRelationship, AMO.Database Database)
{
AMO.Dimension ToDimension = Database.Dimensions[TOMRelationship.ToTable.Name];
AMO.DimensionAttribute ToAttribute = ToDimension.Attributes[TOMRelationship.ToColumn.Name];
AMO.Dimension FromDimension = Database.Dimensions[TOMRelationship.FromTable.Name];
if (ToDimension.Attributes[TOMRelationship.ToColumn.Name].Usage != AMO.AttributeUsage.Key)
{
SetPKColumn(Database, ToAttribute);
}
string RelationshipID = System.Guid.NewGuid().ToString();
AMO.Relationship AMORelationship = Database.Dimensions[TOMRelationship.FromTable.Name].Relationships.Add(RelationshipID);
AMORelationship.FromRelationshipEnd.DimensionID = TOMRelationship.FromTable.Name;
AMORelationship.FromRelationshipEnd.Attributes.Add(TOMRelationship.FromColumn.Name);
AMORelationship.FromRelationshipEnd.Multiplicity = AMO.Multiplicity.Many;
AMORelationship.FromRelationshipEnd.Role = string.Empty;
AMORelationship.ToRelationshipEnd.DimensionID = TOMRelationship.ToTable.Name;
AMORelationship.ToRelationshipEnd.Attributes.Add(TOMRelationship.ToColumn.Name);
AMORelationship.ToRelationshipEnd.Multiplicity = AMO.Multiplicity.One;
AMORelationship.ToRelationshipEnd.Role = string.Empty;
if (TOMRelationship.IsActive)
{
setActiveRelationship(Database.Cubes[0], TOMRelationship.FromTable.Name, TOMRelationship.FromColumn.Name, TOMRelationship.ToTable.Name, RelationshipID);
}
return(true);
}
public bool VerifySelections()
{
try
{
if (VerifyServer())
{
if (srv.Databases.ContainsName(cmbDatabase.Text))
{
db = srv.Databases.GetByName(cmbDatabase.Text);
}
else
{
return(false);
}
if (db == null || !db.Dimensions.ContainsName(cmbDimension.Text))
{
return(false);
}
dim = db.Dimensions.GetByName(cmbDimension.Text);
for (int i = 0; i < PCAttributesToInclude.Count; i++)
{
if (!dim.Attributes.ContainsName(PCAttributesToInclude[i]))
{
PCAttributesToInclude.RemoveAt(i);
i--;
}
}
for (int i = 0; i < NonPCHierarchiesToInclude.Count; i++)
{
if (!dim.Attributes.ContainsName(NonPCHierarchiesToInclude[i]))
{
NonPCHierarchiesToInclude.RemoveAt(i);
i--;
}
}
return(true);
}
return(false);
}
catch (Exception)
{
return(false);
}
}
public static bool PerspectiveContainsHierarchy(AMO.Database tabularDatabase,
string perspectiveName,
string tableName,
string hierarchyName)
{
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (perspectiveName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(PerspectiveStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (hierarchyName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(HierarchyStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
perspectiveName = perspectiveName.Trim();
tableName = tableName.Trim();
hierarchyName = hierarchyName.Trim();
#endregion
using (AMO.Dimension tableDimension = tabularDatabase.Dimensions.GetByName(tableName))
{
string tableId = tableDimension.ID;
string hierarchyId = tableDimension.Hierarchies.GetByName(hierarchyName).ID;
return(tabularDatabase.Cubes[0].Perspectives.GetByName(perspectiveName).Dimensions[tableId].Hierarchies.Contains(hierarchyId));
}
}
/// <summary>
/// Create cube dimension
/// </summary>
/// <param name="sqlHelper"></param>
/// <param name="asMeta"></param>
/// <param name="cubeDB"></param>
/// <param name="DSV"></param>
public void CREATE_CUBE_DIMENSION(DB_SQLHELPER_BASE sqlHelper
, AS_METADATA asMeta
, Microsoft.AnalysisServices.Database cubeDB
, Microsoft.AnalysisServices.DataSourceView DSV
)
{
try
{
sqlHelper.ADD_MESSAGE_LOG("[Create dimension] Starting create dimension"
, MESSAGE_TYPE.DIMENSION
, MESSAGE_RESULT_TYPE.Normal);
DataTable DimensionSet = asMeta.GET_SSAS_DIMENSION_SET(sqlHelper);
foreach (DataRow dimension_row in DimensionSet.Rows)
{
String DimensionID = dimension_row["dimension_id"].ToString();
String DimensionName = dimension_row["dimension_name"].ToString();
String DimensionType = dimension_row["dimension_type"].ToString();
String DataSourceName = dimension_row["dsv_schema_name"].ToString();
String dsvName = DSV.Name;
Microsoft.AnalysisServices.Dimension dim = AS_API.ADD_DIMENSION(sqlHelper, cubeDB, dsvName, DimensionID, DimensionName, DimensionType);
DataTable AttributeSet = asMeta.GET_SSAS_ATTRIBUTES_SET(sqlHelper, DimensionID);
if (AttributeSet == null || AttributeSet.Rows == null || AttributeSet.Rows.Count == 0)
{
sqlHelper.ADD_MESSAGE_LOG(
String.Format("[Create dimension] Dimension {0} has not any attributes, is it expected?", DimensionID)
, MESSAGE_TYPE.DIMENSION
, MESSAGE_RESULT_TYPE.Warning);
}
else
{
sqlHelper.ADD_MESSAGE_LOG(
String.Format("[Create dimension] Adding {0} attributeds for dimension {1}", AttributeSet.Rows.Count.ToString(), DimensionID)
, MESSAGE_TYPE.DIMENSION
, MESSAGE_RESULT_TYPE.Normal);
}
foreach (DataRow attribute_row in AttributeSet.Rows)
{
String AttributeID = attribute_row["attribute_id"].ToString();
String AttributeName = attribute_row["attribbute_name"].ToString();
String DSVSchemaName = attribute_row["dsv_schema_name"].ToString();
String DBColumn = attribute_row["key_column_db_column"].ToString();
String OleDbType = attribute_row["key_column_oledb_type"].ToString();
String AttributeUsage = attribute_row["attribute_usage"].ToString();
String NameColumn = attribute_row["name_column"].ToString();
String Visible = attribute_row["visible"].ToString();
String AttHierEnabled = attribute_row["atthier_enabled"].ToString();
String OrderBy = attribute_row["order_by"].ToString();
Microsoft.AnalysisServices.OrderBy attribute_order_by = Microsoft.AnalysisServices.OrderBy.Name;
if (OrderBy.ToLower() == "key")
{
attribute_order_by = Microsoft.AnalysisServices.OrderBy.Key;
}
AS_API.ADD_ATTRIBUTE_TO_DIMENSION(
sqlHelper,
DSV,
dim,
DataSourceName,
DBColumn,
AttributeID,
AttributeName,
AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME(OleDbType),
AS_API_HELPER.GET_SSAS_ATTRIBUTE_USAGE_BY_NAME(AttributeUsage),
NameColumn, Convert.ToBoolean(Visible),
Convert.ToBoolean(AttHierEnabled),
attribute_order_by
);
}
DataTable AttributeRelationShipSet = asMeta.GET_SSAS_ATTRIBUTE_RELATION_SHIPS_SET(sqlHelper, DimensionID);
sqlHelper.ADD_MESSAGE_LOG(String.Format("[Create dimension] Adding {0} attribute relationships for dimension {1}", AttributeRelationShipSet.Rows.Count.ToString(), DimensionID)
, MESSAGE_TYPE.ATTRIBUTE_RELATIONSHIP
, MESSAGE_RESULT_TYPE.Normal);
foreach (DataRow row in AttributeRelationShipSet.Rows)
{
String BasedAttributeID = row["based_attribute_id"].ToString();
String RelatedAttributeID = row["related_attribute_id"].ToString();
String RelationShipType = row["relationship_type"].ToString();
Microsoft.AnalysisServices.RelationshipType AttributeRelationShipType = AS_API_HELPER.GET_SSAS_ATTRIBUTE_RELATION_SHIP_TYPE_BY_NAME(RelationShipType);
AS_API.ADD_ATTRIBUTE_RELATIONSHIP(
sqlHelper,
dim,
BasedAttributeID,
RelatedAttributeID,
AttributeRelationShipType);
}
DataTable HierarchiesSet = asMeta.GET_SSAS_HIERARCHIES_SET(sqlHelper, DimensionID);
sqlHelper.ADD_MESSAGE_LOG(
String.Format("[Create dimension] Adding {0} hierarchy levels for dimension {1}", HierarchiesSet.Rows.Count.ToString(), DimensionID)
, MESSAGE_TYPE.HIERARCHIES
, MESSAGE_RESULT_TYPE.Normal);
foreach (DataRow row in HierarchiesSet.Rows)
{
String HierarchyName = row["hierarchy_name"].ToString();
String LevelName = row["level_name"].ToString();
String LevelID = row["level_id"].ToString();
String SourceAttributeID = row["source_attribute_id"].ToString();
AS_API.ADD_ATTRIBUTE_HIERACHIES(
sqlHelper,
dim,
HierarchyName,
LevelName,
SourceAttributeID);
sqlHelper.ADD_MESSAGE_LOG(
"[Create dimension->Hierarchy] |" + new string('_', Convert.ToInt16(LevelID)) + LevelName
, MESSAGE_TYPE.HIERARCHIES
, MESSAGE_RESULT_TYPE.Normal);
}
sqlHelper.ADD_MESSAGE_LOG(
"[Create dimension] Updating changes of dimension objects.."
, MESSAGE_TYPE.DIMENSION
, MESSAGE_RESULT_TYPE.Normal);
dim.Update();
sqlHelper.ADD_MESSAGE_LOG(
"[Create dimension] Succeed to add changes to dimension objects.."
, MESSAGE_TYPE.DIMENSION
, MESSAGE_RESULT_TYPE.Normal);
}
}
catch (Exception ex)
{
sqlHelper.ADD_MESSAGE_LOG("[Create dimension] " + ex.Message.ToString(), MESSAGE_TYPE.ADD_DIMENSION, MESSAGE_RESULT_TYPE.Error);
throw (ex);
}
}
public static void PerspectiveAlterHierarchyAdd(AMO.Database tabularDatabase,
string perspectiveName,
string tableName,
string hierarchyName,
bool updateInstance = true)
{
// Major steps in adding a Hierarchy to a Perspective in the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Adding hierarchy of table to perspective
//
// 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.
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (perspectiveName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(PerspectiveStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (hierarchyName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(HierarchyStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
perspectiveName = perspectiveName.Trim();
tableName = tableName.Trim();
hierarchyName = hierarchyName.Trim();
#endregion
using (AMO.Dimension tableDimension = tabularDatabase.Dimensions.GetByName(tableName))
{
string tableId = tableDimension.ID;
string hierarchyId = tableDimension.Hierarchies.GetByName(hierarchyName).ID;
using (AMO.Perspective perspective = tabularDatabase.Cubes[0].Perspectives.GetByName(perspectiveName))
using (AMO.PerspectiveDimension perspectiveTable = perspective.Dimensions[tableId])
{
perspectiveTable.Hierarchies.Add(hierarchyId);
}
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
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 HierarchyAlterLevelsAdd(AMO.Database tabularDatabase,
string tableName,
string hierarchyName,
bool updateInstance = true,
params LevelInfo[] levelInfo)
{
// Major steps in adding Levels to a Hierarchy in a table
//
// - Validate required input arguments
// - Other Initial preparations
// - Adding Levels to Hierarchy
//
// 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.
#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 (hierarchyName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(HierarchyStringName);
}
if (levelInfo == null || levelInfo.Length == 0)
{
throw new ArgumentNullException(LevelInfo);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
hierarchyName = hierarchyName.Trim();
#endregion
// [Codeplex issue # 8]
// [JPJofre, 2012-10-18]
// [Description: using (AMO.Hierarchy currentHierarchy = tableDimension.Hierarchies[hierarchyName]);
// ... it should be using (AMO.Hierarchy currentHierarchy = tableDimension.Hierarchies.GetByName(hierarchyName))]
// [Suggested fix: replace with tableDimension.Hierarchies.GetByName(hierarchyName)]
// Add levels
using (AMO.Dimension tableDimension = tabularDatabase.Dimensions.GetByName(tableName))
using (AMO.Hierarchy currentHierarchy = tableDimension.Hierarchies.GetByName(hierarchyName))
{
// Add levels to the hierarchy
foreach (LevelInfo level in levelInfo)
{
string attributeId = tableDimension.Attributes.GetByName(level.DefiningLevelColumnName).ID;
currentHierarchy.Levels.Add(level.LevelName).SourceAttributeID = attributeId;
}
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
//this method is where any future code changes will need to be made
//the decisions about what extra properties are show are configured here
void ConfigureProjectItemExtraProperties(ProjectItem pi)
{
if (pi == null)
{
return;
}
if (pi.Object is Dimension)
{
Microsoft.AnalysisServices.Dimension dim = (Microsoft.AnalysisServices.Dimension)pi.Object;
System.ComponentModel.TypeDescriptor.Refresh(dim);
SetAttribute(typeof(Dimension), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(dim, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Dimension), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(dim, "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Hierarchy), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(typeof(Hierarchy), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Hierarchy), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(DimensionAttribute), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(typeof(DimensionAttribute), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(DimensionAttribute), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Translation), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(typeof(Translation), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
}
else if (pi.Object is Cube)
{
Microsoft.AnalysisServices.Cube cube = (Microsoft.AnalysisServices.Cube)pi.Object;
System.ComponentModel.TypeDescriptor.Refresh(cube);
SetAttribute(typeof(Cube), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(cube, "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(cube, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Cube), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(MeasureGroup), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(typeof(MeasureGroup), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(MeasureGroup), "Annotations", new System.ComponentModel.ReadOnlyAttribute(false), true);
SetAttribute(typeof(MeasureGroup), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Measure), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(typeof(Measure), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Measure), "Annotations", new System.ComponentModel.ReadOnlyAttribute(false), true);
SetAttribute(typeof(Measure), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
foreach (MeasureGroup mg in cube.MeasureGroups)
{
if (mg.IsLinked)
{
SetAttribute(mg, "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(mg, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(mg, "Description", new System.ComponentModel.ReadOnlyAttribute(!bInEffect), true);
SetAttribute(mg, "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
foreach (Measure m in mg.Measures)
{
SetAttribute(m, "Description", new System.ComponentModel.ReadOnlyAttribute(!bInEffect), true);
SetAttribute(m, "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(m, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(m, "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(m, "FormatString", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
}
}
foreach (Partition p in mg.Partitions)
{
SetAttribute(p, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(p, "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
}
}
SetAttribute(typeof(Perspective), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(typeof(Perspective), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Perspective), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Translation), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(typeof(Translation), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Translation), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
}
}
/* Complete feature list, to be noted when complete support added
* - Database
* - Direct Query
* - Datasources (Done)
* - Tables (Done)
* - Translation of table (Done)
* - Columns (Done)
* - Translation of Column (Done)
* - Hierarchies (Done)
* - Translation of Hierarchies (Done)
* - Levels (Done)
* - Translation of Levels (Done)
* - Measures (Done)
* - Translation of Measures (Done)
* - KPI's
* - Perspectives (Done)
* - Roles (Done)
* - Row Level Security (Done)
* - Members (Done)
* - Relationships (Done)
*/
/// <summary>
/// Generates a 1200 Tabular Database, based on the provided 1103 Database.
/// </summary>
/// <param name="AMODatabase">The 1103 Database to create 1200 model from</param>
/// <param name="AddTabularEditorAnnotation">Whether to add an annotation to allow compatibility with TabularEditor</param>
/// <returns></returns>
public static TOM.Database Database(AMO.Database AMODatabase, bool AddTabularEditorAnnotation = true)
{
//Create the database
TOM.Database TOMDatabase = new TOM.Database(AMODatabase.Name);
//Create the model
TOMDatabase.Model = new TOM.Model();
TOMDatabase.Model.Name = AMODatabase.Cubes[0].Name;
#region DataSources
foreach (AMO.DataSource AMODataSource in AMODatabase.DataSources)
{
//Create the data source. We use the ProviderDataSource specifically
TOM.ProviderDataSource TOMDataSource = new TOM.ProviderDataSource
{
Description = AMODataSource.Description,
ConnectionString = AMODataSource.ConnectionString,
MaxConnections = AMODataSource.MaxActiveConnections,
Name = AMODataSource.Name,
Provider = AMODataSource.ManagedProvider,
Timeout = (int)AMODataSource.Timeout.TotalSeconds
};
//Convert AMO ImpersonationMode enum to TOM ImpersonationMode enum
switch (AMODataSource.ImpersonationInfo.ImpersonationMode)
{
case AMO.ImpersonationMode.Default:
TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.Default;
break;
case AMO.ImpersonationMode.ImpersonateAccount:
TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateAccount;
break;
case AMO.ImpersonationMode.ImpersonateAnonymous:
TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateAnonymous;
break;
case AMO.ImpersonationMode.ImpersonateCurrentUser:
TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateCurrentUser;
break;
case AMO.ImpersonationMode.ImpersonateServiceAccount:
TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateServiceAccount;
break;
case AMO.ImpersonationMode.ImpersonateUnattendedAccount:
TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateUnattendedAccount;
break;
}
//Convert AMO Isolation enum to TOM Isolation enum
switch (AMODataSource.Isolation)
{
case AMO.DataSourceIsolation.ReadCommitted:
TOMDataSource.Isolation = TOM.DatasourceIsolation.ReadCommitted;
break;
case AMO.DataSourceIsolation.Snapshot:
TOMDataSource.Isolation = TOM.DatasourceIsolation.Snapshot;
break;
}
//Add the DataSource
TOMDatabase.Model.DataSources.Add(TOMDataSource);
}
#endregion
foreach (AMO.Dimension Dimension in AMODatabase.Dimensions)
{
//Create the table
TOM.Table TOMTable = new TOM.Table();
TOMTable.Description = Dimension.Description;
TOMTable.Name = Dimension.Name;
TOMTable.IsHidden = !AMODatabase.Cubes[0].Dimensions.FindByName(Dimension.Name).Visible;
//Add Translations
foreach (AMO.Translation AMOTranslation in Dimension.Translations)
{
TranslationHelper.AddTOMTranslation(TOMDatabase, TOMTable, AMOTranslation);
}
#region Columns
foreach (AMO.DimensionAttribute Attribute in Dimension.Attributes)
{
if (Attribute.Type != AMO.AttributeType.RowNumber)
{
//Declare "generic" TOM Column, to be assigned to the "specific" column for reuse in
// assigning common properties
TOM.Column TOMColumn;
if (Attribute.NameColumn.Source is AMO.ExpressionBinding)
{
//Create the Calculated Column, and set specific properties
TOM.CalculatedColumn CalculatedColumn = new TOM.CalculatedColumn();
CalculatedColumn.Expression = ((AMO.ExpressionBinding)Attribute.NameColumn.Source).Expression;
//Set as TOMColumn so generic properties can be applied later
TOMColumn = CalculatedColumn;
}
else
{
//Create the Data Column, and set specific properties
TOM.DataColumn DataColumn = new TOM.DataColumn();
DataColumn.SourceColumn = ((AMO.ColumnBinding)Attribute.NameColumn.Source).ColumnID;
DataColumn.IsKey = Attribute.Usage == AMO.AttributeUsage.Key;
DataColumn.IsNullable = Attribute.KeyColumns[0].NullProcessing != AMO.NullProcessing.Error;
//Set as TOMColumn so generic properties can be applied later
TOMColumn = DataColumn;
}
//Generic Properties, shared between both Data Columns and Calculated Columns
TOMColumn.Name = Attribute.Name;
TOMColumn.Description = Attribute.Description;
TOMColumn.DisplayFolder = Attribute.AttributeHierarchyDisplayFolder;
TOMColumn.FormatString = Attribute.FormatString;
TOMColumn.IsHidden = !Attribute.AttributeHierarchyVisible;
TOMColumn.DataType = DataTypeHelper.ToTOMDataType(Attribute.KeyColumns[0].DataType);
//Add translations
foreach (AMO.Translation AMOTranslation in Attribute.Translations)
{
TranslationHelper.AddTOMTranslation(TOMDatabase, TOMColumn, AMOTranslation);
}
//Finally, add the Column to the Table
TOMTable.Columns.Add(TOMColumn);
}
}
//Add sort by columns last
//This is because we cannot add sort by columns referring to columns which do not exist yet
foreach (AMO.DimensionAttribute Attribute in Dimension.Attributes)
{
if (Attribute.Type != AMO.AttributeType.RowNumber && Attribute.OrderByAttribute != null)
{
TOMTable.Columns[Attribute.Name].SortByColumn = TOMTable.Columns[Attribute.OrderByAttribute.Name];
}
}
#endregion
//Add the Table
TOMDatabase.Model.Tables.Add(TOMTable);
#region Hierarchies
foreach (AMO.Hierarchy AMOHierarchy in Dimension.Hierarchies)
{
//Create the hierarchy
TOM.Hierarchy TOMHierarchy = new TOM.Hierarchy();
TOMHierarchy.Name = AMOHierarchy.Name;
TOMHierarchy.Description = AMOHierarchy.Description;
TOMHierarchy.DisplayFolder = AMOHierarchy.DisplayFolder;
//AMO Hierarchies are always visible, from what I can tell
TOMHierarchy.IsHidden = false;
//Add translations
foreach (AMO.Translation AMOTranslation in AMOHierarchy.Translations)
{
TranslationHelper.AddTOMTranslation(TOMDatabase, TOMHierarchy, AMOTranslation);
}
foreach (AMO.Level AMOLevel in AMOHierarchy.Levels)
{
//Create the level
TOM.Level TOMLevel = new TOM.Level();
TOMLevel.Name = AMOLevel.Name;
TOMLevel.Description = AMOLevel.Description;
TOMLevel.Column = TOMTable.Columns[AMOLevel.SourceAttribute.Name];
//Add translations
foreach (AMO.Translation AMOTranslation in AMOLevel.Translations)
{
TranslationHelper.AddTOMTranslation(TOMDatabase, TOMLevel, AMOTranslation);
}
//Add the Level
TOMHierarchy.Levels.Add(TOMLevel);
}
//Add the Hierarchy
TOMTable.Hierarchies.Add(TOMHierarchy);
}
#endregion
#region Partitions
foreach (AMO.Partition AMOPartition in AMODatabase.Cubes[0].MeasureGroups.GetByName(TOMTable.Name).Partitions)
{
//Create the partition
TOM.Partition TOMPartition = new TOM.Partition
{
Description = AMOPartition.Description,
//Add the query
Source = new TOM.QueryPartitionSource
{
DataSource = TOMDatabase.Model.DataSources[AMOPartition.DataSource.Name],
Query = ((AMO.QueryBinding)AMOPartition.Source).QueryDefinition
}
};
//Add the Partition
TOMTable.Partitions.Add(TOMPartition);
}
#endregion
}
#region Measures
foreach (AMO.Command Command in AMODatabase.Cubes[0].MdxScripts[0].Commands)
{
List <MDXCommand> Commands = MDXCommandParser.GetCommands(Command.Text);
if (Commands.Count > 0)
{
MDXCommand MainCommand = Commands[0];
if (MainCommand.Type == CommandType.CreateMeasure)
{
List <MDXString> Strings = MDXCommandParser.GetStrings(MainCommand.LHS);
//Throw exception if we do not have a valid CREATE MEASURE command.
/* We do not care if a command is claid, only if we can parse it.
* We need two strings:
* - A single quoted string, representing the table name
* - A square bracketed string, representing the measure name
* As long as they are present, we consider it valid.
*/
if (Strings.Count < 2 || Strings[0].Type != StringType.SingleQuote || Strings[1].Type != StringType.SquareBracket)
{
throw new System.Exception("A CREATE MEASURE statement must at least have two delimited elements (one table, one measure name)");
}
//First, single quoted string, is table name
string TableName = Strings[0].Text;
//Then, square-bracket delimited string is the measure name.
string MeasureName = Strings[1].Text;
//Find the existing CalculationProperty, containing the relevant properties of the Measure, if it exists
AMO.CalculationProperty CalculationProperty = AMODatabase.Cubes[0].MdxScripts[0].CalculationProperties.Find("[" + MeasureName.Replace("]", "]]") + "]");
//Create the Measure
TOM.Measure TOMMeasure = new TOM.Measure
{
Name = MeasureName,
Expression = MainCommand.RHS,
Description = CalculationProperty?.Description,
DisplayFolder = CalculationProperty?.DisplayFolder,
//AMO format string is wrapped in single quotes, so we need to get rid of them here
FormatString = CalculationProperty?.FormatString.Substring(1, CalculationProperty.FormatString.Length - 2),
IsHidden = CalculationProperty == null ? true : !CalculationProperty.Visible
};
//Add Translations
if (CalculationProperty != null)
{
foreach (AMO.Translation AMOTranslation in CalculationProperty.Translations)
{
TranslationHelper.AddTOMTranslation(TOMDatabase, TOMMeasure, AMOTranslation);
}
}
//Add the Measure
TOMDatabase.Model.Tables[TableName].Measures.Add(TOMMeasure);
}
}
}
#endregion
#region Perspectives
foreach (AMO.Perspective AMOPerspective in AMODatabase.Cubes[0].Perspectives)
{
//Create the Perspective
TOM.Perspective TOMPerspective = new TOM.Perspective
{
Name = AMOPerspective.Name,
Description = AMOPerspective.Description
};
foreach (AMO.PerspectiveDimension AMOPerspectiveDimension in AMOPerspective.Dimensions)
{
//Find the TOM Table equivelant to the AMO Perspective Dimension
TOM.Table TOMTable = TOMDatabase.Model.Tables[AMOPerspectiveDimension.Dimension.Name];
//Create the Perspective Table
TOM.PerspectiveTable TOMPerspectiveTable = new TOM.PerspectiveTable {
Table = TOMTable
};
//Add Columns
foreach (AMO.PerspectiveAttribute PerspectiveAttribute in AMOPerspectiveDimension.Attributes)
{
TOMPerspectiveTable.PerspectiveColumns.Add(new TOM.PerspectiveColumn
{
Column = TOMTable.Columns[PerspectiveAttribute.Attribute.Name]
});
}
//Add Hierarchies
foreach (AMO.PerspectiveHierarchy PerspectiveHierarchy in AMOPerspectiveDimension.Hierarchies)
{
TOMPerspectiveTable.PerspectiveHierarchies.Add(new TOM.PerspectiveHierarchy
{
Hierarchy = TOMTable.Hierarchies[PerspectiveHierarchy.Hierarchy.Name]
});
}
//Add Measures
foreach (AMO.PerspectiveCalculation PerspectiveCalculation in AMOPerspective.Calculations)
{
foreach (TOM.Measure TOMMeasure in TOMTable.Measures)
{
if (
PerspectiveCalculation.Type == AMO.PerspectiveCalculationType.Member &&
PerspectiveCalculation.Name == "[Measures].[" + TOMMeasure.Name + "]"
)
{
TOMPerspectiveTable.PerspectiveMeasures.Add(new TOM.PerspectiveMeasure {
Measure = TOMMeasure
});
}
}
}
//Add the Perspective Table
TOMPerspective.PerspectiveTables.Add(TOMPerspectiveTable);
}
//Add the Perspective
TOMDatabase.Model.Perspectives.Add(TOMPerspective);
}
#endregion
#region Roles
foreach (AMO.Role AMORole in AMODatabase.Roles)
{
//Create the Role
TOM.ModelRole TOMRole = new TOM.ModelRole
{
Name = AMORole.Name,
Description = AMORole.Description
};
//Determine the ModelPermission from the equivelant DatabasePermission
foreach (AMO.DatabasePermission Permission in AMODatabase.DatabasePermissions)
{
if (Permission.Role.Name == AMORole.Name)
{
if (Permission.Administer)
{
TOMRole.ModelPermission = TOM.ModelPermission.Administrator;
}
else if (Permission.Read == AMO.ReadAccess.Allowed && Permission.Process)
{
TOMRole.ModelPermission = TOM.ModelPermission.ReadRefresh;
}
else if (Permission.Read == AMO.ReadAccess.Allowed)
{
TOMRole.ModelPermission = TOM.ModelPermission.Read;
}
else if (Permission.Process)
{
TOMRole.ModelPermission = TOM.ModelPermission.Refresh;
}
else
{
TOMRole.ModelPermission = TOM.ModelPermission.None;
}
}
}
//Add the Row Level Security
foreach (AMO.Dimension Dimension in AMODatabase.Dimensions)
{
foreach (AMO.DimensionPermission DimensionPermission in Dimension.DimensionPermissions)
{
if (DimensionPermission.Role.Name == TOMRole.Name)
{
//Create the Table Permission
TOM.TablePermission TablePermission = new TOM.TablePermission
{
Table = TOMDatabase.Model.Tables[Dimension.Name],
FilterExpression = DimensionPermission.AllowedRowsExpression
};
//Add the Table Permission to the Role
TOMRole.TablePermissions.Add(TablePermission);
}
}
}
//Add Role Members
foreach (AMO.RoleMember AMOMember in AMORole.Members)
{
//Create the Role Member
TOM.ModelRoleMember TOMMember = new TOM.WindowsModelRoleMember
{
MemberID = AMOMember.Sid,
MemberName = AMOMember.Name
};
//Add the Member to the Role
TOMRole.Members.Add(TOMMember);
}
//Add the Role to the Database
TOMDatabase.Model.Roles.Add(TOMRole);
}
#endregion
#region Relationships
foreach (AMO.Dimension Dimension in AMODatabase.Dimensions)
{
foreach (AMO.Relationship AMORelationship in Dimension.Relationships)
{
//Get To and From columns
AMO.Dimension FromDimension = AMODatabase.Dimensions[AMORelationship.FromRelationshipEnd.DimensionID];
AMO.Dimension ToDimension = AMODatabase.Dimensions[AMORelationship.ToRelationshipEnd.DimensionID];
TOM.Table FromTable = TOMDatabase.Model.Tables[FromDimension.Name];
TOM.Column FromColumn = FromTable.Columns[FromDimension.Attributes[AMORelationship.FromRelationshipEnd.Attributes[0].AttributeID].Name];
TOM.Table ToTable = TOMDatabase.Model.Tables[ToDimension.Name];
TOM.Column ToColumn = ToTable.Columns[ToDimension.Attributes[AMORelationship.ToRelationshipEnd.Attributes[0].AttributeID].Name];
//Create the Relationship
TOM.SingleColumnRelationship TOMRelationship = new TOM.SingleColumnRelationship
{
FromColumn = FromColumn,
ToColumn = ToColumn,
//Set IsActive to false, and update later
IsActive = false
};
//Check if Relationship is active
foreach (AMO.MeasureGroupDimension MeasureGroupDimension in AMODatabase.Cubes[0].MeasureGroups.GetByName(FromTable.Name).Dimensions)
{
if (MeasureGroupDimension is AMO.ReferenceMeasureGroupDimension)
{
if (((AMO.ReferenceMeasureGroupDimension)MeasureGroupDimension).RelationshipID == AMORelationship.ID)
{
TOMRelationship.IsActive = true;
}
}
}
//Add the Relationship to the Database
TOMDatabase.Model.Relationships.Add(TOMRelationship);
}
}
#endregion
//Add TabularEditor compatibility annotation if necessary
if (AddTabularEditorAnnotation)
{
TOMDatabase.Model.Annotations.Add(new TOM.Annotation {
Name = "TabularEditor_CompatibilityVersion",
Value = AMODatabase.CompatibilityLevel.ToString()
});
}
//TODO: Handle KPIs
return(TOMDatabase);
}
/// <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);
}
}
