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); } }