private void AddTable(Tom.Table table) { var partitions = table.Partitions; Tom.PartitionSourceType tableType = (partitions?.Count > 0) ? (partitions[0].SourceType) : Tom.PartitionSourceType.None; bool isCalculatedTable = (tableType == Tom.PartitionSourceType.Calculated); bool isCalculationGroup = (tableType == Tom.PartitionSourceType.CalculationGroup); var partitionSource = (isCalculatedTable) ? partitions[0].Source as Tom.CalculatedPartitionSource : null; Dax.Metadata.Table daxTable = new Dax.Metadata.Table(DaxModel) { TableName = new Dax.Metadata.DaxName(table.Name), IsHidden = table.IsHidden, IsPrivate = table.IsPrivate, IsLocalDateTable = (table.Annotations.FirstOrDefault(a => a.Name == "__PBI_LocalDateTable" && a.Value == "true") != null), IsTemplateDateTable = (table.Annotations.FirstOrDefault(a => a.Name == "__PBI_TemplateDateTable" && a.Value == "true") != null), TableExpression = Dax.Metadata.DaxExpression.GetExpression(isCalculatedTable ? partitionSource.Expression : null), TableType = isCalculatedTable ? Table.TableSourceType.CalculatedTable.ToString() : (isCalculationGroup ? Table.TableSourceType.CalculationGroup.ToString() : null), Description = table.Description }; foreach (var column in table.Columns) { AddColumn(daxTable, column); } foreach (var measure in table.Measures) { AddMeasure(daxTable, measure); } foreach (var hierarchy in table.Hierarchies) { AddUserHierarchy(daxTable, hierarchy); } // Add calculation groups and calculation items if (table.CalculationGroup != null) { var calcGroup = new CalculationGroup(daxTable) { Precedence = table.CalculationGroup.Precedence }; foreach (var calcItem in table.CalculationGroup.CalculationItems) { AddCalculationItem(calcGroup, calcItem); } daxTable.CalculationGroup = calcGroup; // Set the first column of the table that is not a RowNumber as a calculation group attribute foreach (var column in daxTable.Columns) { if (!column.IsRowNumber) { column.IsCalculationGroupAttribute = true; break; } } } DaxModel.Tables.Add(daxTable); }
/// <summary> /// Creates a new Calculated Table and adds it to the specified Model. /// Also creates the underlying metadataobject and adds it to the TOM tree. /// </summary> public static CalculatedTable CreateNew(Model parent, string name = null, string expression = null) { var metadataObject = new TOM.Table(); if (parent.Model.Database.CompatibilityLevel >= 1540) { metadataObject.LineageTag = Guid.NewGuid().ToString(); } metadataObject.Name = parent.Tables.GetNewName(string.IsNullOrWhiteSpace(name) ? "New Calculated Table" : name); var tomPartition = new TOM.Partition(); tomPartition.Mode = TOM.ModeType.Import; tomPartition.Name = metadataObject.Name; tomPartition.Source = new TOM.CalculatedPartitionSource(); metadataObject.Partitions.Add(tomPartition); var obj = new CalculatedTable(metadataObject); parent.Tables.Add(obj); obj.Init(); if (!string.IsNullOrWhiteSpace(expression)) { obj.Expression = expression; } return(obj); }
internal new static CalculatedTable CreateFromMetadata(Model parent, TOM.Table metadataObject) { if (metadataObject.GetSourceType() != TOM.PartitionSourceType.Calculated) { throw new ArgumentException("Provided metadataObject is not a Calculated Table."); } // Generate a new LineageTag if an object with the provided lineage tag already exists: if (!string.IsNullOrEmpty(metadataObject.LineageTag)) { if (parent.Handler.CompatibilityLevel < 1540) { metadataObject.LineageTag = null; } else if (parent.MetadataObject.Tables.FindByLineageTag(metadataObject.LineageTag) != metadataObject) { metadataObject.LineageTag = Guid.NewGuid().ToString(); } } var obj = new CalculatedTable(metadataObject); parent.Tables.Add(obj); obj.Init(); return(obj); }
public new static CalculationGroupTable CreateNew(Model parent, string name = null) { parent.DiscourageImplicitMeasures = true; var metadataObject = new TOM.Table() { CalculationGroup = new TOM.CalculationGroup() }; if (parent.Model.Database.CompatibilityLevel >= 1540) { metadataObject.LineageTag = Guid.NewGuid().ToString(); } var nameCol = new TOM.DataColumn { Name = "Name", DataType = TOM.DataType.String, SourceColumn = "Name" }; var ordinalCol = new TOM.DataColumn { Name = "Ordinal", DataType = TOM.DataType.Int64, IsHidden = true, SourceColumn = "Ordinal" }; metadataObject.Columns.Add(nameCol); metadataObject.Columns.Add(ordinalCol); nameCol.SortByColumn = ordinalCol; metadataObject.Partitions.Add(new TOM.Partition { Source = new TOM.CalculationGroupSource(), Mode = TOM.ModeType.Import }); metadataObject.Name = parent.Tables.GetNewName(string.IsNullOrWhiteSpace(name) ? "New Calculation Group" : name); return(CreateFromMetadata(parent, metadataObject)); }
public static Table GetTable(TabModel.Table t, List <TabModel.Measure> unsupportedMeasures) { var table = new Table() { Name = t.Name, Columns = new List <Column>(), Measures = new List <Measure>() }; foreach (var c in t.Columns) { table.Columns.Add(GetColumn(c)); } foreach (var m in t.Measures) { if (!unsupportedMeasures.Contains(m)) { table.Measures.Add( new Measure { Name = m.Name, Expression = m.Expression, IsHidden = m.IsHidden, FormatString = m.FormatString, Description = m.Description } ); } } return(table); }
/// <summary> /// Initializes a new instance of the Table class using multiple parameters. /// </summary> /// <param name="parentTabularModel">TabularModel object that the Table object belongs to.</param> /// <param name="tomTable">Tabular Object Model Table object abtstracted by the Table class.</param> public Table(TabularModel parentTabularModel, Tom.Table tomTable) : base(tomTable, parentTabularModel) { _parentTabularModel = parentTabularModel; _tomTable = tomTable; PopulateProperties(); }
private static Microsoft.AnalysisServices.Tabular.Table CreateTable(string tableName, string tableDescription) { var table = new Microsoft.AnalysisServices.Tabular.Table() { Name = tableName, Description = tableDescription }; return(table); }
/// <summary> /// Create a relationship for the Table object, with validation to ensure referential integrity. /// </summary> /// <param name="relationshipSource">Relationship object from the source tabular model.</param> /// <param name="toTomTableSource">Tabular Object Model Table representing "to table" in the relationship.</param> /// <param name="relationshipName">Name of the relationship to be created.</param> /// <param name="warningMessage">Warning message to return to caller.</param> /// <returns>Boolean indicating if creation was successful.</returns> public bool CreateRelationshipWithValidation(Relationship relationshipSource, Tom.Table toTomTableSource, string relationshipName, ref string warningMessage) { SingleColumnRelationship tabularRelationshipSource = relationshipSource.TomRelationship; // Check if "to" table exists (don't need to check "from" table as we are in the "from" table) ... if (!_parentTabularModel.Tables.ContainsName(toTomTableSource.Name)) { warningMessage = $"Unable to create Relationship {relationshipName} because (considering changes) parent table not found in target model."; return(false); } // does the required child column exist? In this case need to check child column as user might have skipped Update of table meaning columns are out of sync. if (!_tomTable.Columns.ContainsName(tabularRelationshipSource.FromColumn.Name)) { warningMessage = $"Unable to create Relationship {relationshipName} because (considering changes) child column not found in target model."; return(false); } // does the required "to" column exist? Tom.Table toTableTarget = _parentTabularModel.Tables.FindByName(tabularRelationshipSource.ToTable.Name).TomTable; if ( (toTableTarget == null) || (!toTableTarget.Columns.ContainsName(tabularRelationshipSource.ToColumn.Name)) ) { warningMessage = $"Unable to create Relationship {relationshipName} because (considering changes) parent column not found in target model."; return(false); } if (this.IsCalculationGroup || _parentTabularModel.Tables.FindByName(tabularRelationshipSource.ToTable.Name).IsCalculationGroup) { warningMessage = $"Unable to create Relationship {relationshipName} because one or more tables is a calculation group."; return(false); } // Delete the target relationship with same tables/columns if still there. Not using RemoveByInternalName in case internal name is actually different. if (this.Relationships.ContainsName(relationshipSource.Name)) { warningMessage = $"Unable to create Relationship {relationshipName} because (considering changes) relationship already exists in target model."; return(false); } // at this point we know we will update the relationship SingleColumnRelationship relationshipTarget = new SingleColumnRelationship(); tabularRelationshipSource.CopyTo(relationshipTarget); //decouple from original table to the current one relationshipTarget.FromColumn = this.TomTable.Columns.Find(relationshipTarget.FromColumn.Name); relationshipTarget.ToColumn = toTableTarget.Columns.Find(relationshipTarget.ToColumn.Name); CreateRelationship(relationshipTarget); return(true); }
public static ReferenceTable Create(TOM.Table table) { return(new ReferenceTable { Name = table.Name, Description = table.Description, Columns = table.Columns.Select(c => ReferenceColumn.Create(c)).ToArray(), Measures = table.Measures.Select(m => ReferenceMeasure.Create(m)).ToArray(), Hierarchies = table.Hierarchies.Select(h => ReferenceHierarchy.Create(h)).ToArray() }); }
static void AddSalesRegionMeasures() { // DAX query to be submitted totabuar database engine String query = "EVALUATE( VALUES(Customers[Sales Region]) )"; AdomdConnection adomdConnection = new AdomdConnection(connectString); adomdConnection.Open(); AdomdCommand adomdCommand = new AdomdCommand(query, adomdConnection); AdomdDataReader reader = adomdCommand.ExecuteReader(); // open connection use TOM to create new measures TOM.Server server = new TOM.Server(); server.Connect(connectString); TOM.Model model = server.Databases[0].Model; TOM.Table salesTable = model.Tables["Sales"]; String measureDescription = "Auto Measures"; // delete any previously created "Auto" measures foreach (TOM.Measure m in salesTable.Measures) { if (m.Description == measureDescription) { salesTable.Measures.Remove(m); model.SaveChanges(); } } // Create the new measures while (reader.Read()) { String SalesRegion = reader.GetValue(0).ToString(); String measureName = $"{SalesRegion} Sales"; TOM.Measure measure = new TOM.Measure() { Name = measureName, Description = measureDescription, DisplayFolder = "Auto Measures", FormatString = "$#,##0", Expression = $@"CALCULATE( SUM(Sales[SalesAmount]), Customers[Sales Region] = ""{SalesRegion}"" )" }; salesTable.Measures.Add(measure); } model.SaveChanges(); reader.Dispose(); adomdConnection.Close(); }
internal new static CalculatedTable CreateFromMetadata(Model parent, TOM.Table metadataObject) { if (metadataObject.GetSourceType() != TOM.PartitionSourceType.Calculated) { throw new ArgumentException("Provided metadataObject is not a Calculated Table."); } var obj = new CalculatedTable(metadataObject); parent.Tables.Add(obj); obj.Init(); return(obj); }
internal new static CalculationGroupTable CreateFromMetadata(Model parent, TOM.Table metadataObject) { if (metadataObject.GetSourceType() != TOM.PartitionSourceType.CalculationGroup) { throw new ArgumentException("Provided metadataObject is not a Calculation Group Table."); } var obj = new CalculationGroupTable(metadataObject); parent.Tables.Add(obj); obj.Init(); //obj.NameField = new CalculationGroupAttribute(obj.DataColumns.First()); return(obj); }
public new static CalculationGroupTable CreateNew(Model parent, string name = null) { parent.DiscourageImplicitMeasures = true; var metadataObject = new TOM.Table() { CalculationGroup = new TOM.CalculationGroup() }; metadataObject.Columns.Add(new TOM.DataColumn { Name = "Attribute", DataType = TOM.DataType.String }); metadataObject.Partitions.Add(new TOM.Partition { Source = new TOM.CalculationGroupSource() }); metadataObject.Name = parent.Tables.GetNewName(string.IsNullOrWhiteSpace(name) ? "New Calculation Group" : name); return(CreateFromMetadata(parent, metadataObject)); }
/// <summary> /// Creates a new Calculated Table and adds it to the specified Model. /// Also creates the underlying metadataobject and adds it to the TOM tree. /// </summary> public static CalculatedTable CreateNew(Model parent, string name = null, string expression = null) { var metadataObject = new TOM.Table(); metadataObject.Name = parent.Tables.GetNewName(string.IsNullOrWhiteSpace(name) ? "New Calculated Table" : name); var obj = new CalculatedTable(metadataObject); parent.Tables.Add(obj); obj.Init(); if (!string.IsNullOrWhiteSpace(expression)) { obj.Expression = expression; } return(obj); }
static partial void InitMetadata(TOM.Table metadataObject, Model parent) { if (metadataObject.Partitions.Count > 0) { return; } var modelHasStructuredDataSources = parent.DataSources.Any(ds => ds.Type == DataSourceType.Structured); var modelHasPowerQueryPartitions = parent.Tables.Any(t => t.Partitions.Any(p => p.SourceType == PartitionSourceType.M)); var modelHasProviderDataSources = parent.DataSources.Any(ds => ds.Type == DataSourceType.Provider); // Ensure the table is initialized with a single partition. If the model does not contain any Provider data sources, the type // of partition added to the table is determind by the following logic: if (parent.Handler.CompatibilityLevel >= 1400 && !modelHasProviderDataSources && (modelHasStructuredDataSources || modelHasPowerQueryPartitions || parent.Handler.Settings.UsePowerQueryPartitionsByDefault) ) { var tomPartition = new TOM.Partition { Name = metadataObject.Name, Source = new TOM.MPartitionSource() }; metadataObject.Partitions.Add(tomPartition); } else { var tomPartition = new TOM.Partition { Name = metadataObject.Name }; var qps = new TOM.QueryPartitionSource(); tomPartition.Source = qps; if (!parent.DataSources.Any(ds => ds.Type == DataSourceType.Provider)) { parent.AddDataSource(); } qps.DataSource = parent.DataSources.FirstOrDefault(ds => ds.Type == DataSourceType.Provider).MetadataObject; metadataObject.Partitions.Add(tomPartition); } }
/// <summary> /// Creates a new Calculated Table and adds it to the specified Model. /// Also creates the underlying metadataobject and adds it to the TOM tree. /// </summary> public static CalculatedTable CreateNew(Model parent, string name = null, string expression = null) { var metadataObject = new TOM.Table(); metadataObject.Name = parent.Tables.GetNewName(string.IsNullOrWhiteSpace(name) ? "New Calculated Table" : name); var tomPartition = new TOM.Partition(); tomPartition.Mode = TOM.ModeType.Import; tomPartition.Name = metadataObject.Name; tomPartition.Source = new TOM.CalculatedPartitionSource(); metadataObject.Partitions.Add(tomPartition); var obj = new CalculatedTable(metadataObject); parent.Tables.Add(obj); obj.Init(); if (!string.IsNullOrWhiteSpace(expression)) { obj.Expression = expression; } return(obj); }
/* 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); }
public Table(SSAS14.Table table) { this.table14 = table; }
protected CalculatedTable(TOM.Table tableMetadataObject) : base(tableMetadataObject) { }
public static void CreateAnalysisServiceDatabase(string svr, string db, List <Table> tblList) { string ConnectionString = "DataSource=.\\MSSQLAS"; // // The using syntax ensures the correct use of the // Microsoft.AnalysisServices.Tabular.Server object. // using (Server server = new Server()) { server.Connect(ConnectionString); // // Generate a new database name and use GetNewName // to ensure the database name is unique. // string newDatabaseName = server.Databases.GetNewName("AW Handmade v1.1"); // // Instantiate a new // Microsoft.AnalysisServices.Tabular.Database object. // var dbWithDataSource = new Database() { Name = newDatabaseName, ID = newDatabaseName, CompatibilityLevel = 1200, StorageEngineUsed = StorageEngineUsed.TabularMetadata, }; // // Add a Microsoft.AnalysisServices.Tabular.Model object to the // database, which acts as a root for all other Tabular metadata objects. // dbWithDataSource.Model = new Model() { Name = "AW Handmade Model", Description = "A Tabular data model at the 1200 compatibility level." }; // // Add a Microsoft.AnalysisServices.Tabular.ProviderDataSource object // to the data Model object created in the previous step. The connection // string of the data source object in this example // points to an instance of the AdventureWorks2014 SQL Server database. // dbWithDataSource.Model.DataSources.Add(new ProviderDataSource() { Name = "SQL Server Data Source Example", Description = "A data source definition that uses explicit Windows credentials for authentication against SQL Server.", ConnectionString = "Provider=SQLNCLI11;Data Source=" + svr + ";Initial Catalog=" + db + ";Integrated Security=SSPI;Persist Security Info=false", ImpersonationMode = Microsoft.AnalysisServices.Tabular.ImpersonationMode.ImpersonateAccount, Account = @".\duy", Password = "******", }); // // Add the new database object to the server's // Databases connection and submit the changes // with full expansion to the server. // foreach (Table tbl in tblList) { Microsoft.AnalysisServices.Tabular.Table ttbl = new Microsoft.AnalysisServices.Tabular.Table(); ttbl.Name = tbl.TableName; var sel = SelectedColumns(svr, db, tbl); foreach (Microsoft.AnalysisServices.Tabular.DataColumn dc in sel) { ttbl.Columns.Add(dc); } ttbl.Partitions.Add(new Partition() { Name = "All", Source = new QueryPartitionSource() { DataSource = dbWithDataSource.Model.DataSources["SQL Server Data Source Example"], Query = "Select * from " + ttbl.Name } }); dbWithDataSource.Model.Tables.Add(ttbl); } server.Databases.Add(dbWithDataSource); dbWithDataSource.Update(UpdateOptions.ExpandFull); Console.Write("Database "); Console.ForegroundColor = ConsoleColor.Yellow; Console.Write(dbWithDataSource.Name); Console.ResetColor(); Console.WriteLine(" created successfully."); Console.WriteLine("The data model includes the following data source definitions:"); Console.ForegroundColor = ConsoleColor.Yellow; foreach (DataSource ds in dbWithDataSource.Model.DataSources) { Console.WriteLine("\tData source name:\t\t{0}", ds.Name); Console.WriteLine("\tData source description:\t{0}", ds.Description); } Console.ResetColor(); Console.WriteLine(); } Console.WriteLine("Press Enter to close this console window."); Console.ReadLine(); }
/* * [Browsable(false)] * public CalculationGroupAttribute NameField { get; private set; } * [Browsable(false)] * public CalculationGroupAttribute OrdinalField { get; private set; } */ CalculationGroupTable(TOM.Table table) : base(table) { }
public static bool IsCalculatedOrCalculationGroup(this TOM.Table table) { var sourceType = GetSourceType(table); return(sourceType == TOM.PartitionSourceType.Calculated || sourceType == TOM.PartitionSourceType.CalculationGroup); }
public static TOM.PartitionSourceType GetSourceType(this TOM.Table table) { return(table.Partitions.FirstOrDefault()?.SourceType ?? TOM.PartitionSourceType.None); }