public RelationshipGraph(AMO.Database tabularDatabase)
{
foreach (TableInfo tableInfo in AMO2Tabular.TablesEnumerateFull(tabularDatabase))
{
using (AMO.MeasureGroup currentMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups[tableInfo.DataSourceName])
{
foreach (AMO.MeasureGroupDimension measureGroupDimension in currentMeasureGroup.Dimensions)
{
if (measureGroupDimension is AMO.ReferenceMeasureGroupDimension)
{
AMO.ReferenceMeasureGroupDimension referencedTable = measureGroupDimension as AMO.ReferenceMeasureGroupDimension;
string foreignTableId = referencedTable.IntermediateCubeDimensionID;
string foreignColumnId = referencedTable.IntermediateGranularityAttributeID;
string primaryKeyTableId = referencedTable.CubeDimensionID;
string primaryKeyColumnId = string.Empty;
foreach (AMO.MeasureGroupAttribute attribute in referencedTable.Attributes)
{
if (attribute.Type == AMO.MeasureGroupAttributeType.Granularity)
{
primaryKeyColumnId = attribute.AttributeID;
break;
}
}
FullName foreignKeyEnd = new FullName(foreignTableId, foreignColumnId);
FullName primaryKeyEnd = new FullName(primaryKeyTableId, primaryKeyColumnId);
RelationshipPair relationship = new RelationshipPair(primaryKeyEnd, foreignKeyEnd);
AddForeignKeyUpPair(primaryKeyTableId, relationship);
AddPrimaryKeyDownPair(foreignTableId, relationship);
}
}
}
}
}
private static void RelationshipAddReferenceMeasureGroupDimension(AMO.Database tabularDatabase,
string currentMeasureGroupId,
RelationshipPair relationshipPair,
string relationshipId = null)
{
string foreignTableId = relationshipPair.ForeignKeyEnd.TableId;
string foreignColumnId = relationshipPair.ForeignKeyEnd.ColumnId;
string pkTableId = relationshipPair.PrimaryKeyEnd.TableId;
string pkColumnId = relationshipPair.PrimaryKeyEnd.ColumnId;
// Creating the ReferenceMeasureGroupDimension that defines the 'Activeness' of a relationship
using (AMO.Cube modelCube = tabularDatabase.Cubes[0])
using (AMO.MeasureGroup currentMG = modelCube.MeasureGroups[currentMeasureGroupId])
using (AMO.ReferenceMeasureGroupDimension newReferenceMGDim = new AMO.ReferenceMeasureGroupDimension())
{
newReferenceMGDim.CubeDimensionID = pkTableId;
newReferenceMGDim.IntermediateCubeDimensionID = foreignTableId;
newReferenceMGDim.IntermediateGranularityAttributeID = foreignColumnId;
// Replicating attributes (columns) from dimension
foreach (AMO.CubeAttribute PKAttribute in modelCube.Dimensions[pkTableId].Attributes)
{
using (AMO.MeasureGroupAttribute PKMGAttribute = newReferenceMGDim.Attributes.Add(PKAttribute.AttributeID))
using (AMO.DataItem dataItem = new AMO.DataItem(pkTableId, PKAttribute.AttributeID, PKAttribute.Attribute.KeyColumns[0].DataType))
using (AMO.ColumnBinding columnBinding = new AMO.ColumnBinding(pkTableId, PKAttribute.AttributeID))
{
PKMGAttribute.KeyColumns.Add(dataItem);
PKMGAttribute.KeyColumns[0].Source = columnBinding;
}
}
newReferenceMGDim.Attributes[pkColumnId].Type = AMO.MeasureGroupAttributeType.Granularity;
// If relationship is not null or empty then this is a direct relationship and
// has to have Materialization.Regular
if (!relationshipId.IsNullOrEmptyOrWhitespace())
{
newReferenceMGDim.Materialization = AMO.ReferenceDimensionMaterialization.Regular;
newReferenceMGDim.RelationshipID = relationshipId;
}
else
{
newReferenceMGDim.Materialization = AMO.ReferenceDimensionMaterialization.Indirect;
}
// Adding the ReferenceMeasureGroupDimension to the measure group
currentMG.Dimensions.Add(newReferenceMGDim);
}
}
private static void setActiveRelationship(AMO.Cube currentCube, string MVTableName, string MVColumnName, string PKTableName, string relationshipID)
{
AMO.MeasureGroup currentMG = currentCube.MeasureGroups[MVTableName];
if (!currentMG.Dimensions.Contains(PKTableName))
{
AMO.ReferenceMeasureGroupDimension NewReferenceMGDim = new AMO.ReferenceMeasureGroupDimension();
NewReferenceMGDim.CubeDimensionID = PKTableName;
NewReferenceMGDim.IntermediateCubeDimensionID = MVTableName;
NewReferenceMGDim.Materialization = AMO.ReferenceDimensionMaterialization.Regular;
foreach (AMO.CubeAttribute PKAttribute in currentCube.Dimensions[PKTableName].Attributes)
{
AMO.MeasureGroupAttribute PKMGAttribute = NewReferenceMGDim.Attributes.Add(PKAttribute.AttributeID);
System.Data.OleDb.OleDbType PKMGAttributeType = PKAttribute.Attribute.KeyColumns[0].DataType;
PKMGAttribute.KeyColumns.Add(new AMO.DataItem(PKTableName, PKAttribute.AttributeID, PKMGAttributeType));
PKMGAttribute.KeyColumns[0].Source = new AMO.ColumnBinding(PKTableName, PKAttribute.AttributeID);
}
currentMG.Dimensions.Add(NewReferenceMGDim);
}
AMO.ReferenceMeasureGroupDimension currentReferenceMGDim = (AMO.ReferenceMeasureGroupDimension)currentMG.Dimensions[PKTableName];
currentReferenceMGDim.RelationshipID = relationshipID;
currentReferenceMGDim.IntermediateGranularityAttributeID = MVColumnName;
}
public static void RelationshipAlterActive(AMO.Database tabularDatabase,
string pkTableName,
string pkColumnName,
string foreignTableName,
string foreignColumnName,
bool active,
bool updateInstance = true)
{
// Terminology note:
// - the Foreing side of the relationship is named the From end in AMO
// - the PK side of the relationship in named the To end in AMO
//
// Relationships flow FROM the foreign side of the relationship
// TO the primary key side of the relationship in AMO
// ==> [Inactive] Relationship information is stored in the Foreign Dimension object in a relationship object
// ==> [Active] Relationship information is stored in the MeasureGroup object of the table in a ReferenceMeasureGroupDimension object
//
// Major steps in adding a relationship
//
// - Validate required input arguments and other initial preparations
// - If Activating:
// - Verify relationship exist before Activating
// - If relationship doesn't exist throw an error
// - Verify no multipath or alternate path will be created by activating this relationship
// - If ReferenceMeasureGroupDimension, for the PK table, doesn't exist add it to the Dimensions collection in the MeasureGroup
// - replicate attributes (columns) from Dimension
// - If ReferenceMeasureGroupDimension exists, update relationshipID and IntermediateGranularity
// - To the Dimensions collection in the MeasureGroup
// - Add all other intermediate relationships from 'Ancestors' paths
// - Add all other intermediate relationships to the 'Descendants' paths --> Update MeasureGroups in the 'Descendants' paths
//
//
// T1 T2 T5 T6 T7
// ---- ----- ----- ----- -----
// |a1| <-- |a2 | |a5 | |a6 | |a7 |
// | | |a2i| <-- |a5i| /| | | |a7i|
// | | | | | | / ------| |a6i| <-- | |
// ---- ----- | | / | | | -----
// | | \ Rel56 | | |
// T3 T4 | | \ ------| | | T8
// ---- ----- | | \| | | -----
// |a3| <-- |a4 | | | | | <-- |a8 |
// | | |a4i| <-- |a5j| | | |a8i|
// | | | | | | | | | |
// ---- ----- ----- ----- -----
//
// \_________ ____________/ \________ ________/
// \/ \/
// Ancestors Descendants
//
//
// When adding Rel56, as active, to the model, on top of everything that already exists, you have to:
//
// --> Add a ReferenceMeasureGroupDimension or updating existing one: (T5, T6.a6i) will include: Materialization:=Regular, RelationshipId:=<relationship id>
//
// --> After adding (T5, T6.a6i) to T6, you have to:
//
// Add Add Add
// to T6 to T7 to T8
// ...... ...... ......
// (T5, T6.a6i) (T5, T6.a6i)
// (T2, T5.a5i) (T2, T5.a5i) (T2, T5.a5i)
// (T1, T2.a2i) (T1, T2.a2i) (T1, T2.a2i)
// (T4, T5.a5j) (T4, T5.a5j) (T4, T5.a5j)
// (T3, T4.a4i) (T3, T4.a4i) (T3, T4.a4i)
//
// --> as reference dimensions to the MeasureGroup
// --> because the relationships between T6<--T7 and T6<--T8 already exist, the materialization and relationship
// definitions were added at the time the relationship were created ==> no need to do anything like
// on T6.
//
// - If De-Activating
// - Verify relationship exist before De-Activating
// - If relationship doesn't exist throw an error
// - Remove All ReferenceMeasureGroupDimension, for the PK table and Down Below tables, from MeasureGroup
//
// 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 in the model
//
//
// Note: Microsoft design tools use the following pattern to keep track of the
// datasource matching elements:
// DataSourceView->TableName <---> Dimension.ID, MeasureGroup.ID
// DataSourceView->ColumnName <---> Dimension->ColumnID, MeasureGroup.DegeneratedDimension->CoumnID
// So far, this sample follows the same pattern.
//
// WARNING: Breaking the above pattern when creating your
// own AMO to Tabular functions might lead to
// unpredictable behavior when using Microsoft
// Design tools in your models.
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (pkTableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("pkTableName");
}
if (pkColumnName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("pkColumnName");
}
if (foreignTableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("foreignTableName");
}
if (foreignColumnName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("foreignColumnName");
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
pkTableName = pkTableName.Trim();
pkColumnName = pkColumnName.Trim();
foreignTableName = foreignTableName.Trim();
foreignColumnName = foreignColumnName.Trim();
// - Obtain Id's
string pkTableId = tabularDatabase.Dimensions.GetByName(pkTableName).ID;
string pkColumnId = tabularDatabase.Dimensions[pkTableId].Attributes.GetByName(pkColumnName).ID;
string foreignTableId = tabularDatabase.Dimensions.GetByName(foreignTableName).ID;
string foreignColumnId = tabularDatabase.Dimensions[foreignTableId].Attributes.GetByName(foreignColumnName).ID;
#endregion
RelationshipGraph currentRelationshipsGraph;
// Branch on 'active'
// - true ==> set relationship to Active
// - false ==> set relationship to Inactive
switch (active)
{
case true:
#region set relationship to Active
// Verify relationship existence
// --> Throw an error if relationship doesn't exist
// - Define placeholder for the relationship id; it's needed later
string relationshipId = RelationshipTryGetRelationshipId(tabularDatabase, pkTableName, pkColumnName, foreignTableName, foreignColumnName);
if (relationshipId.IsNullOrEmptyOrWhitespace())
{
throw new InvalidOperationException(Resources.RelationshipDoesNotExistInvalidOperationException);
}
// Verify that activating this relationship does not violate the rule of no alternate paths when it will be created
// Build a graph of the actual state of relationships
currentRelationshipsGraph = new RelationshipGraph(tabularDatabase);
if (currentRelationshipsGraph.RelationshipAlternatePathExists(pkTableId, foreignTableId))
{
throw new InvalidOperationException(Resources.RelationshipViolatesAlternatePathRuleInvalidOperationException);
}
// The 'activeness' of a relationship is defined by the existence of a ReferenceMeasureGroupDimension
// that include Materialization as Regular
using (AMO.Cube modelCube = tabularDatabase.Cubes[0])
using (AMO.MeasureGroup foreignTableMG = modelCube.MeasureGroups[foreignTableId])
{
if (!foreignTableMG.Dimensions.Contains(pkTableId))
{
// Creating the ReferenceMeasureGroupDimension that defines the 'Activeness' of a relationship
RelationshipAddReferenceMeasureGroupDimension(tabularDatabase, foreignTableId, new RelationshipPair(new FullName(pkTableId, pkColumnId), new FullName(foreignTableId, foreignColumnId)), relationshipId);
// Adding Intermediate relationships for all tables in the paths from PrimaryKeyEndBelow for direct ForeignKeyEnd
foreach (RelationshipPair primaryKeyDownRelationship in currentRelationshipsGraph.RelationshipsListPrimaryKeyDown(pkTableId))
{
RelationshipAddReferenceMeasureGroupDimension(tabularDatabase, foreignTableId, primaryKeyDownRelationship);
}
// Adding Intermediate relationships for all tables in the paths from PrimaryKeyEndBelow in each of the tables
// that are ForeignKeyEndAbove
foreach (string foreignKeyTableUpId in currentRelationshipsGraph.TableListForeignKeyUp(foreignTableId))
{
using (AMO.MeasureGroup foreignKeyUpMG = tabularDatabase.Cubes[0].MeasureGroups[foreignKeyTableUpId])
{
RelationshipAddReferenceMeasureGroupDimension(tabularDatabase, foreignKeyUpMG.ID, new RelationshipPair(new FullName(pkTableId, pkColumnId), new FullName(foreignTableId, foreignColumnId)));
foreach (RelationshipPair primaryKeyDownRelationship in currentRelationshipsGraph.RelationshipsListPrimaryKeyDown(pkTableId))
{
RelationshipAddReferenceMeasureGroupDimension(tabularDatabase, foreignKeyUpMG.ID, primaryKeyDownRelationship);
}
}
}
}
else
{
// Because the ReferenceMeasureGroupDimension already exists, this probably means just a change on the foreign key used as Active
using (AMO.ReferenceMeasureGroupDimension currentReferenceMGDim = (AMO.ReferenceMeasureGroupDimension)foreignTableMG.Dimensions[pkTableId])
{
currentReferenceMGDim.RelationshipID = relationshipId;
currentReferenceMGDim.IntermediateGranularityAttributeID = foreignColumnId;
}
}
}
currentRelationshipsGraph.Clear();
break;
#endregion
case false:
#region set relationship to Inactive
// Verify relationship existence
// --> If relationship doesn't exist throw an error
if (!RelationshipExists(tabularDatabase, pkTableName, pkColumnName, foreignTableName, foreignColumnName))
{
throw new InvalidOperationException(Resources.RelationshipDoesNotExistInvalidOperationException);
}
// Build a graph of the actual state of relationships
currentRelationshipsGraph = new RelationshipGraph(tabularDatabase);
// Remove the ReferenceMeasureGroupDimension to remove the 'Activeness'
tabularDatabase.Cubes[0].MeasureGroups[foreignTableId].Dimensions.Remove(pkTableId);
// Removing Intermediate relationships for all tables in the paths from PrimaryKeyEndBelow for direct ForeignKeyEnd
foreach (string primaryKeyDownTableId in currentRelationshipsGraph.TableListPrimaryKeyDown(pkTableId))
{
tabularDatabase.Cubes[0].MeasureGroups[foreignTableId].Dimensions.Remove(primaryKeyDownTableId);
}
// Adding Intermediate relationships for all tables in the paths from PrimaryKeyEndBelow in each of the tables
// that are ForeignKeyEndAbove
foreach (string foreignKeyTableUpId in currentRelationshipsGraph.TableListForeignKeyUp(foreignTableId))
{
tabularDatabase.Cubes[0].MeasureGroups[foreignKeyTableUpId].Dimensions.Remove(pkTableId);
foreach (string primaryKeyDownTableId in currentRelationshipsGraph.TableListPrimaryKeyDown(pkTableId))
{
tabularDatabase.Cubes[0].MeasureGroups[foreignKeyTableUpId].Dimensions.Remove(primaryKeyDownTableId);
}
}
currentRelationshipsGraph.Clear();
break;
#endregion
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
