/// <summary> /// Delete all associated relationships including those from other tables that refer to this table. /// </summary> /// <returns>Collection of all associated relationships that were deleted. Useful if updating tables as then need to add back.</returns> public List <SingleColumnRelationship> DeleteAllAssociatedRelationships() { List <SingleColumnRelationship> relationshipsToDelete = new List <SingleColumnRelationship>(); foreach (Table table in _parentTabularModel.Tables) { List <string> relationshipsToDeleteInternalNames = new List <string>(); foreach (Relationship relationship in table.Relationships) { if (relationship.FromTableName == this.Name || relationship.ToTableName == this.Name) { SingleColumnRelationship relationshipTarget = new SingleColumnRelationship(); relationship.TomRelationship.CopyTo(relationshipTarget); relationshipsToDelete.Add(relationshipTarget); relationshipsToDeleteInternalNames.Add(relationship.InternalName); } } foreach (string relationshipToDeleteInternalName in relationshipsToDeleteInternalNames) { table.DeleteRelationship(relationshipToDeleteInternalName); } } return(relationshipsToDelete); }
public static SingleColumnRelationship DeserializeSingleColumnRelationship(JObject json, Model model) { var tom = TOM.JsonSerializer.DeserializeObject <TOM.SingleColumnRelationship>(json.ToString()); tom.Name = Guid.NewGuid().ToString(); var relationship = SingleColumnRelationship.CreateFromMetadata(model, tom); return(relationship); }
/// <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); }
/// <summary> /// Relationship for compatibility level 1100 (Tabular/TOM) /// </summary> /// <param name="relationship"></param> public ModelAnalyzerRelationship(SingleColumnRelationship relationship) { IsActive = relationship.IsActive; Name = relationship.Name; CrossFilteringBehavior = relationship.CrossFilteringBehavior.ToString(); FromTable = relationship.FromTable.Name; FromColumn = relationship.FromColumn.Name; FromCardinality = relationship.FromCardinality.ToString(); ToTable = relationship.ToTable.Name; ToColumn = relationship.ToColumn.Name; ToCardinality = relationship.ToCardinality.ToString(); }
public static SingleColumnRelationship DeserializeSingleColumnRelationship(JObject json, Model model) { var tom = TOM.JsonSerializer.DeserializeObject <TOM.SingleColumnRelationship>(json.ToString(Formatting.None)); if (model.Relationships.TOM_ContainsName(tom.Name)) { tom.Name = Guid.NewGuid().ToString(); } var relationship = SingleColumnRelationship.CreateFromMetadata(model, tom); return(relationship); }
public NodeConnection AddRelationship(SingleColumnRelationship relationship) { var con = new NodeConnection() { InputNode = NodesByTable[relationship.FromTable], OutputNode = NodesByTable[relationship.ToTable], InputSocketName = relationship.FromColumn.Name, OutputSocketName = relationship.ToColumn.Name }; Connections.Add(con); return(con); }
/// <summary> /// Initializes a new instance of the Relationship class using multiple parameters. /// </summary> /// <param name="table">Table object that the Relationship belongs to.</param> /// <param name="tomRelationship">Tabular Object Model SingleColumnRelationship object abtstracted by the Relationship class.</param> /// <param name="copiedFromSource">Boolean indicating whether the relationship was copied from the source TabularModel object.</param> /// <param name="modifiedInternalName">Boolean indicating whether the TOM Relationship object Name property was changed to avoid name conflict.</param> /// <param name="oldInternalName">If the TOM Relationship object Name property was changed, this parameter shows the old value.</param> public Relationship(Table table, SingleColumnRelationship tomRelationship, bool copiedFromSource = false, bool modifiedInternalName = false, string oldInternalName = "") : base(tomRelationship) { _table = table; _tomRelationship = tomRelationship; //_relationshipName = $"'{_relationship.FromTable.Name}'->'{_relationship.ToTable.Name}'"; //_relationshipName = $"[{_relationship.FromColumn.Name}]->'{_relationship.ToTable.Name}'[{_relationship.ToColumn.Name}]"; _relationshipName = $"'{_tomRelationship.FromTable.Name}'[{_tomRelationship.FromColumn.Name}]->'{_tomRelationship.ToTable.Name}'[{_tomRelationship.ToColumn.Name}]"; _copiedFromSource = copiedFromSource; _modifiedInternalName = modifiedInternalName; _oldInternalName = oldInternalName; }
/// <summary> /// Create a relationship for the Table object. /// </summary> /// <param name="tomRelationshipTarget">Tabular Object Model SingleColumnRelationship object to be abstracted by the Relationship object being created.</param> public void CreateRelationship(SingleColumnRelationship tomRelationshipTarget) { bool modifiedInternalName = false; string oldInternalName = ""; // check if there is an existing relationship with same internal name if (_parentTabularModel.ContainsRelationshipByInternalName(tomRelationshipTarget.Name)) { modifiedInternalName = true; oldInternalName = tomRelationshipTarget.Name; tomRelationshipTarget.Name = Convert.ToString(Guid.NewGuid()); } _parentTabularModel.TomDatabase.Model.Relationships.Add(tomRelationshipTarget); _relationships.Add(new Relationship(this, tomRelationshipTarget, copiedFromSource: true, modifiedInternalName: modifiedInternalName, oldInternalName: oldInternalName)); }
public static void RestoreRelationshipsFromAnnotations(this Table table) { var relationshipsJson = table.GetAnnotation("TabularEditor_Relationships"); if (relationshipsJson == null) { return; } JArray rels = JArray.Parse(relationshipsJson); foreach (var rel in rels) { var relationship = TOM.JsonSerializer.DeserializeObject <TOM.SingleColumnRelationship>(rel.ToString()); SingleColumnRelationship.CreateFromMetadata(table.Model, relationship); } }
public static void RestoreRelationshipsFromAnnotations(this Table table) { var relationshipsJson = table.GetAnnotation(AnnotationHelper.ANN_RELATIONSHIPS); if (relationshipsJson == null) { return; } JArray rels = JArray.Parse(relationshipsJson); foreach (var rel in rels) { var relationship = TOM.JsonSerializer.DeserializeObject <TOM.SingleColumnRelationship>(rel.ToString()); SingleColumnRelationship.CreateFromMetadata(table.Model, relationship); } table.RemoveAnnotation(AnnotationHelper.ANN_RELATIONSHIPS, false); }
private static void ProcessTabularRelationships(Microsoft.AnalysisServices.Tabular.Model model, DataSet result) { var rels = new List <ModelAnalyzerRelationship>(); foreach (SingleColumnRelationship scanRel in model.Relationships) { // Check whether the relationship is a known type, otherwise skips it SingleColumnRelationship rel = scanRel as SingleColumnRelationship; if (rel != null) { rels.Add(new ModelAnalyzerRelationship(rel)); } } var relationshipsTable = rels.ToDataTable(); relationshipsTable.TableName = "Relationships"; result.Tables.Add(relationshipsTable); }
static void Main(string[] args) { string ConnectionString = "DataSource=localhost"; using (Server server = new Server()) { server.Connect(ConnectionString); // ALL FOLLOWING CODE GOES HERE string newDB = server.Databases.GetNewName("Wide World Importers"); var blankdatabase = new Database() { Name = newDB, ID = newDB, CompatibilityLevel = 1200, StorageEngineUsed = StorageEngineUsed.TabularMetadata, }; blankdatabase.Model = new Model() { Name = "Wide World Importers Model", Description = "The Wide World Imports Tabular data model at the 1200 compatibility level." }; blankdatabase.Model.DataSources.Add(new ProviderDataSource() { Name = "WideWorldImportersDW_Source", ConnectionString = "Provider=SQLNCLI11;Data Source=localhost;Initial Catalog=WideWorldImportersDW;Integrated Security=SSPI;Persist Security Info=false", ImpersonationMode = Microsoft.AnalysisServices.Tabular.ImpersonationMode.ImpersonateServiceAccount, }); //COLUMN DEFINITIONS //DIMENSION TABLES //DATE DataColumn Date_Date = new DataColumn() { Name = "Date", DataType = DataType.DateTime, SourceColumn = "Date", IsUnique = true, FormatString = "yyyy-mm-dd", }; DataColumn Date_MonthNumber = new DataColumn() { Name = "Month Number", DataType = DataType.Int64, SourceColumn = "MonthNumber", IsHidden = true, }; DataColumn Date_Month = new DataColumn() { Name = "Month", DataType = DataType.String, SourceColumn = "Month", SortByColumn = Date_MonthNumber }; DataColumn Date_Year = new DataColumn() { Name = "Year", DataType = DataType.String, SourceColumn = "Year", }; //EMPLOYEE DataColumn Employee_EmployeeKey = new DataColumn() { Name = "Employee Key", DataType = DataType.Int64, SourceColumn = "EmployeeKey", IsHidden = true, IsUnique = true, }; DataColumn Employee_EmployeeName = new DataColumn() { Name = "Employee Name", DataType = DataType.String, SourceColumn = "Employee", }; DataColumn Employee_WWIEmployeeID = new DataColumn() { Name = "WWI Employee ID", DataType = DataType.Int64, SourceColumn = "WWIEmployeeID", SummarizeBy = AggregateFunction.None, }; DataColumn Employee_IsSalesPerson = new DataColumn() { Name = "Is Sales Person", DataType = DataType.String, SourceColumn = "IsSalesPerson", }; //FACT TABLE //ORDER DataColumn Order_SalesPersonKey = new DataColumn() { Name = "Sales Person Key", DataType = DataType.Int64, SourceColumn = "SalesPersonKey", IsHidden = true, }; DataColumn Order_Date = new DataColumn() { Name = "Date", DataType = DataType.DateTime, SourceColumn = "OrderDateKey", IsHidden = true, }; DataColumn Order_TotalExcludingTax = new DataColumn() { Name = "TotalExcludingTax", DataType = DataType.Decimal, SourceColumn = "TotalExcludingTax", IsHidden = true, }; DataColumn Order_TaxAmount = new DataColumn() { Name = "TaxAmount", DataType = DataType.Decimal, SourceColumn = "TaxAmount", IsHidden = true, }; DataColumn Order_TotalIncludingTax = new DataColumn() { Name = "TotalIncludingTax", DataType = DataType.Decimal, SourceColumn = "TotalIncludingTax", IsHidden = true, }; Hierarchy H1 = new Hierarchy() { Name = "Calendar Year", }; H1.Levels.Add(new Level() { Column = Date_Year, Ordinal = 0, Name = Date_Year.Name }); H1.Levels.Add(new Level() { Column = Date_Month, Ordinal = 1, Name = Date_Month.Name }); H1.Levels.Add(new Level() { Column = Date_Date, Ordinal = 2, Name = Date_Date.Name }); //TABLES ------------------- //DATE blankdatabase.Model.Tables.Add(new Table() { Name = blankdatabase.Model.Tables.GetNewName("Date"), Partitions = { new Partition() { Name = "All Dates", Source = new QueryPartitionSource() { DataSource = blankdatabase.Model.DataSources["WideWorldImportersDW_Source"], Query = @"SELECT Date,Month,[Calendar Month Number] as MonthNumber,[Calendar Year] as Year FROM Dimension.Date", } } }, Columns = { Date_Date, Date_Year, Date_Month, Date_MonthNumber }, Hierarchies = { H1 } }); //EMPLOYEE blankdatabase.Model.Tables.Add(new Table() { Name = blankdatabase.Model.Tables.GetNewName("Employee"), Partitions = { new Partition() { Name = "All Employees", Source = new QueryPartitionSource() { DataSource = blankdatabase.Model.DataSources["WideWorldImportersDW_Source"], Query = @"SELECT [Employee Key] as EmployeeKey,Employee,[WWI Employee ID] as WWIEmployeeID,CASE [Is Salesperson] WHEN 1 THEN 'Yes' ELSE 'No' end as IsSalesPerson FROM Dimension.Employee", } } }, Columns = { Employee_EmployeeKey, Employee_EmployeeName, Employee_WWIEmployeeID, Employee_IsSalesPerson } }); //ORDERS blankdatabase.Model.Tables.Add(new Table() { Name = blankdatabase.Model.Tables.GetNewName("Orders"), Partitions = { new Partition() { Name = "All Orders", Source = new QueryPartitionSource() { DataSource = blankdatabase.Model.DataSources["WideWorldImportersDW_Source"], Query = @"SELECT [Salesperson Key] as SalesPersonKey,[Order Date Key] as OrderDateKey,[Total Excluding Tax] as TotalExcludingTax ,[Tax Amount] as TaxAmount,[Total Including Tax] as TotalIncludingTax FROM Fact.[Order]", } } }, Columns = { Order_SalesPersonKey, Order_Date, Order_TotalExcludingTax, Order_TaxAmount, Order_TotalIncludingTax }, Measures = { new Measure() { Name = "Total Excluding Tax", Expression = "SUM('Orders'[TotalExcludingTax])", FormatString = "#,###.##", }, new Measure() { Name = "Tax Amount", Expression = "SUM('Orders'[TaxAmount])", FormatString = "#,###.##", DisplayFolder = "Tax", }, new Measure() { Name = "Total Including Tax", Expression = "SUM('Orders'[TotalIncludingTax])", FormatString = "#,###.##", }, } }); SingleColumnRelationship relOrderToDate = new SingleColumnRelationship() { Name = "Order_Date_Date_Date", ToColumn = Date_Date, FromColumn = Order_Date, FromCardinality = RelationshipEndCardinality.Many, ToCardinality = RelationshipEndCardinality.One }; blankdatabase.Model.Relationships.Add(relOrderToDate); SingleColumnRelationship relOrderToEmployee = new SingleColumnRelationship() { Name = "Order_EmployeeKey_Employee_EmployeeKey", ToColumn = Employee_EmployeeKey, FromColumn = Order_SalesPersonKey, FromCardinality = RelationshipEndCardinality.Many, ToCardinality = RelationshipEndCardinality.One }; blankdatabase.Model.Relationships.Add(relOrderToEmployee); try { server.Databases.Add(blankdatabase); blankdatabase.Update(UpdateOptions.ExpandFull); Console.WriteLine("Deployed to server successfully"); } catch { Console.WriteLine("Deployed to server failed"); return; } blankdatabase.Model.RequestRefresh(Microsoft.AnalysisServices.Tabular.RefreshType.Full); //request data refresh blankdatabase.Update(UpdateOptions.ExpandFull); //execute data refresh Console.WriteLine("Data loaded..."); Console.Write("Database "); Console.ForegroundColor = ConsoleColor.Green; Console.Write(blankdatabase.Name); Console.ResetColor(); Console.WriteLine(" created successfully."); Console.WriteLine("The data model includes the following table definitions:"); Console.ForegroundColor = ConsoleColor.Yellow; foreach (Table tbl in blankdatabase.Model.Tables) { Console.WriteLine("\tTable name:\t\t{0}", tbl.Name); //Console.WriteLine("\ttbl description:\t{0}", tbl.Description); foreach (Measure measures in tbl.Measures) { Console.WriteLine("\tMeasure name:\t\t{0}", measures.Name); } foreach (Column columns in tbl.Columns) { Console.WriteLine("\tColumn name:\t\t{0}", columns.Name); } foreach (Hierarchy hierarchy in tbl.Hierarchies) { Console.WriteLine("\tHierachy name:\t\t{0}", hierarchy.Name); } } foreach (Relationship relationship in blankdatabase.Model.Relationships) { Console.WriteLine("\tRelationship name:\t\t{0}", relationship.Name); } Console.ResetColor(); Console.WriteLine(); Console.WriteLine("Press Enter to close this console window."); Console.ReadLine(); } }
public static string CardinalityText(this SingleColumnRelationship sr) { return($" {((sr.FromCardinality == RelationshipEndCardinality.Many) ? '*' : '1')}--{ ((sr.ToCardinality == RelationshipEndCardinality.Many) ? '*' : '1')} "); }