//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); }
private static void MeasureAdd_2012SP1(AMO.Database tabularDatabase, string tableName, string measureName, string daxExpression, ColumnInfo?measureProperties = null) { // Major steps in adding a measure to a table in the database // // - Verify if the header of the Measures script needs to be created; create it if needed. // - Store the existing MdxScript in a 'temporary' variable // - Append the new Measure to the 'temporary' variable // - Replace existing MdxScript with 'temporary' variable // // Note: Measures are dynamic objects that live inside an MdxScript object in the cube // // IMPORTANT Note: Measures must not be created as native OLAP measure objects, from the MeasureGroup object. // !! Native OLAP measures cannot be: // • Referenced by DAX queries // • Referenced by calculated columns // • Referenced by other DAX measures // // 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 // // 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 of the model // // Note: In 2012 SP1 one Command script is used per measure // ==> This is different from 2012 RTM behavior, where all measures and KPIs were stored in Commands[1] // // 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. StringBuilder measuresText = new StringBuilder(); using (AMO.MdxScript mdxScript = tabularDatabase.Cubes[0].MdxScripts[MdxScriptStringName]) { AMO.Command measureCommand = new AMO.Command(); // The header of the Measures script needs to be created // Note: The following header is known to be needed by MS designer client tools measuresText.Append(MdxScriptHeader); // Add the Measure to the Text of the command measuresText.AppendLine(string.Format(CultureInfo.InvariantCulture, createMeasureTemplate, tableName, measureName, daxExpression)); // Replpace the existing script with the updated one measureCommand.Text = measuresText.ToString(); // Add measure command to mdxScript mdxScript.Commands.Add(measureCommand); // Update Measure porperties using (AMO.CalculationProperty cp = new AMO.CalculationProperty(measureName, AMO.CalculationType.Member)) { if (measureProperties != null) { cp.FormatString = measureProperties.Value.DataFormat; cp.Description = measureProperties.Value.Description; if (measureProperties.Value.Visible != null) { cp.Visible = measureProperties.Value.Visible.Value; } } mdxScript.CalculationProperties.Add(cp); } } }
private static void KpiAdd_2012SP1(AMO.Database tabularDatabase, string tableName, string measureName, string goalExpression, string statusExpression, string statusGraphicImageName) { // Major steps in adding a KPI to a table in the database // // - Validate required input arguments // - Other Initial preparations // - Store the existing MdxScript in a 'temporary' variable // - Append the new KPI elements to the 'temporary' variable // - Replace existing MdxScript with 'temporary' variable // - Update Calculation Properties for KPI elements // // Important Conceptual Note: // In Tabular Models a KPI is a Measure that has been promoted to KPI; so, the underlying measure still // exists and cannot be hide // // Note: Measures are dynamic objects that live inside an MdxScript object in the cube // // IMPORTANT Note: Measures must not be created as native OLAP measure objects, from the MeasureGroup object. // !! Native OLAP measures cannot be: // • Referenced by DAX queries // • Referenced by calculated columns // • Referenced by other DAX measures // // 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 // // 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 of the model // // Note: In 2012 SP1 and beyond, each measure and related KPI are stored in different Command script objects; one command script object per measure // ==> tabularDatabase.Cubes[0].MdxScripts["MdxScript"].Commands[n].Text contains the n-th Measure and related KPIs of 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. using (AMO.MdxScript mdxScript = tabularDatabase.Cubes[0].MdxScripts[MdxScriptStringName]) { Regex commandTypeExpression = new Regex(MeasurePattern, RegexOptions.CultureInvariant | RegexOptions.IgnoreCase | RegexOptions.Multiline); foreach (AMO.Command modelCommand in mdxScript.Commands) { if (commandTypeExpression.IsMatch(modelCommand.Text) && (string.Compare(measureName, commandTypeExpression.Match(modelCommand.Text).Groups[MeasureStringName].Value, StringComparison.InvariantCultureIgnoreCase) == 0)) { StringBuilder kpiCommand = new StringBuilder(modelCommand.Text); // Add the Goal 'Measure' to the list of commands in MdxScripts kpiCommand.AppendLine(string.Format(CultureInfo.InvariantCulture, createGoalMeasureTemplate, tableName, measureName, goalExpression) ); // Add the Status 'Measure' to the list of commands in MdxScripts kpiCommand.AppendLine(string.Format(CultureInfo.InvariantCulture, createStatusMeasureTemplate, tableName, measureName, statusExpression) ); // Add the KPI 'object' to the list of commands in MdxScripts kpiCommand.AppendLine(string.Format(CultureInfo.InvariantCulture, createKpiTemplate2, tableName, measureName, statusGraphicImageName)); // Replpace the existing script with the updated one modelCommand.Text = kpiCommand.ToString(); //Adding Calculation Property for the Goal member using (AMO.CalculationProperty cp = new AMO.CalculationProperty(string.Format(CultureInfo.InvariantCulture, goalMeasureNameTemplate, measureName), AMO.CalculationType.Member)) { cp.Visible = false; mdxScript.CalculationProperties.Add(cp); } //Adding Calculation Property for the Status member using (AMO.CalculationProperty cp = new AMO.CalculationProperty(string.Format(CultureInfo.InvariantCulture, statusMeasureNameTemplate, measureName), AMO.CalculationType.Member)) { cp.Visible = false; mdxScript.CalculationProperties.Add(cp); } //Adding Calculation Property for the KPI using (AMO.CalculationProperty cp = new AMO.CalculationProperty(string.Format(CultureInfo.InvariantCulture, kpiNameTemplate, measureName), AMO.CalculationType.Member)) { cp.Visible = true; mdxScript.CalculationProperties.Add(cp); } break; } } } }
/* 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); }