/// <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')} ");
}
