internal static bool CreateRelationship(TOM.SingleColumnRelationship TOMRelationship, AMO.Database Database)
{
AMO.Dimension ToDimension = Database.Dimensions[TOMRelationship.ToTable.Name];
AMO.DimensionAttribute ToAttribute = ToDimension.Attributes[TOMRelationship.ToColumn.Name];
AMO.Dimension FromDimension = Database.Dimensions[TOMRelationship.FromTable.Name];
if (ToDimension.Attributes[TOMRelationship.ToColumn.Name].Usage != AMO.AttributeUsage.Key)
{
SetPKColumn(Database, ToAttribute);
}
string RelationshipID = System.Guid.NewGuid().ToString();
AMO.Relationship AMORelationship = Database.Dimensions[TOMRelationship.FromTable.Name].Relationships.Add(RelationshipID);
AMORelationship.FromRelationshipEnd.DimensionID = TOMRelationship.FromTable.Name;
AMORelationship.FromRelationshipEnd.Attributes.Add(TOMRelationship.FromColumn.Name);
AMORelationship.FromRelationshipEnd.Multiplicity = AMO.Multiplicity.Many;
AMORelationship.FromRelationshipEnd.Role = string.Empty;
AMORelationship.ToRelationshipEnd.DimensionID = TOMRelationship.ToTable.Name;
AMORelationship.ToRelationshipEnd.Attributes.Add(TOMRelationship.ToColumn.Name);
AMORelationship.ToRelationshipEnd.Multiplicity = AMO.Multiplicity.One;
AMORelationship.ToRelationshipEnd.Role = string.Empty;
if (TOMRelationship.IsActive)
{
setActiveRelationship(Database.Cubes[0], TOMRelationship.FromTable.Name, TOMRelationship.FromColumn.Name, TOMRelationship.ToTable.Name, RelationshipID);
}
return(true);
}
/// <summary>
/// Relationship for compatibility level 1100 (Multidimensional/XML)
/// </summary>
/// <param name="relationship"></param>
public ModelAnalyzerRelationship(Microsoft.AnalysisServices.Relationship relationship)
{
IsActive = (relationship.ActiveState == ActiveState.ActiveStateActive);
Name = relationship.ID;
CrossFilteringBehavior = relationship.CrossFilterDirection.ToString();
FromTable = relationship.FromRelationshipEnd.DimensionID;
FromColumn = null; // TODO - we don't know this for compatibility level 1100
FromCardinality = null; // TODO - we don't know this for compatibility level 1100
ToTable = relationship.ToRelationshipEnd.DimensionID;
ToColumn = null; // TODO - we don't know this for compatibility level 1100
ToCardinality = null; // TODO - we don't know this for compatibility level 1100
}
/// <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="amoRelationship">Analysis Management Objects Relationship object abtstracted by the Relationship class.</param>
/// <param name="copiedFromSource">Boolean indicating whether the relationship was copied from the source TabularModel object.</param>
public Relationship(Table table, Amo.Relationship amoRelationship, bool copiedFromSource = false)
{
_table = table;
_amoRelationship = amoRelationship;
// parentTable is actually the FK (child) table in the relationship
Dimension dimPK = table.TabularModel.AmoDatabase.Dimensions.Find(amoRelationship.ToRelationshipEnd.DimensionID);
_childTableName = table.Name;
_childColumnName = table.AmoDimension.Attributes.Find(amoRelationship.FromRelationshipEnd.Attributes[0].AttributeID).Name;
_parentTableName = dimPK.Name;
_parentColumnName = dimPK.Attributes.Find(amoRelationship.ToRelationshipEnd.Attributes[0].AttributeID).Name;
//_name = "'" + _childTableName + "'[" + _childColumnName + "]" + "' -> '" + _parentTableName + "'[" + _parentColumnName + "]";
_name = "'" + _childTableName + "' -> '" + _parentTableName + "'";
_longName = _childTableName + "'[" + _childColumnName + "] => '" + _parentTableName + "'[" + _parentColumnName + "]";
_objectDefinition = "Foreign Key Column: '" + _childTableName + "'[" + _childColumnName + "]\n" +
"Primary Key Column: '" + _parentTableName + "'[" + _parentColumnName + "]\n";
_copiedFromSource = copiedFromSource;
}
private static void ProcessMdRelationships(Microsoft.AnalysisServices.Database db, DataSet result)
{
var rels = new List <ModelAnalyzerRelationship>();
foreach (Dimension dim in db.Dimensions)
{
foreach (var scanRel in dim.Relationships)
{
// Check whether the relationship is a known type, otherwise skips it
Microsoft.AnalysisServices.Relationship rel = scanRel as Microsoft.AnalysisServices.Relationship;
if (rel != null)
{
rels.Add(new ModelAnalyzerRelationship(rel));
}
}
}
var relationshipsTable = rels.ToDataTable();
relationshipsTable.TableName = "Relationships";
result.Tables.Add(relationshipsTable);
}
/// <summary>
/// Create a relationship for the Table object.
/// </summary>
/// <param name="amoRelationshipSource"></param>
/// <param name="parentDimSource"></param>
/// <param name="relationshipName"></param>
/// <param name="warningMessage"></param>
/// <param name="active"></param>
/// <returns></returns>
public bool CreateRelationship(Microsoft.AnalysisServices.Relationship amoRelationshipSource, Dimension parentDimSource, string relationshipName, ref string warningMessage, bool active)
{
// Do the required tables exist?
// Child table is guaranteed to exist - we are in an instance of it. Just need to ensure the FromRelationshipEnd.DimensionID is correct
amoRelationshipSource.FromRelationshipEnd.DimensionID = _amoDimension.ID;
// Parent table need to check ...
if (_parentTabularModel.Tables.ContainsName(parentDimSource.Name))
{
//plug in the substitute id for the parent and move on with my life (I have a life you know)
amoRelationshipSource.ToRelationshipEnd.DimensionID = _parentTabularModel.Tables.FindByName(parentDimSource.Name).Id;
}
else
{
warningMessage = "Unable to create Relationship " + relationshipName + " because (considering changes) parent table not found in target model.";
return(false);
}
Dimension childDimSource = (Dimension)amoRelationshipSource.Parent;
Dimension childDimTarget = _amoDimension;
//Dimension parentDimSource = ... //had to pass in as parameter
Dimension parentDimTarget = _parentTabularModel.Tables.FindById(amoRelationshipSource.ToRelationshipEnd.DimensionID).AmoDimension;
// do the required columns exist?
Microsoft.AnalysisServices.Relationship amoRelationshipSourceTemp = null; // can't modify attribute values while in dim collection, so (might) need a temporary holder
foreach (RelationshipEndAttribute childDimAttributeSource in amoRelationshipSource.FromRelationshipEnd.Attributes)
{
DimensionAttribute childDimAttributeTarget = childDimTarget.Attributes.FindByName(childDimSource.Attributes[childDimAttributeSource.AttributeID].Name);
if (childDimAttributeTarget == null)
{
warningMessage = "Unable to create Relationship " + relationshipName + " because (considering changes) child column not found in target model.";
return(false);
}
// just in case the attribute ids are not the same between source/target (though obviously the names are the same), then set the correct id (below) in the source relationship and everything will just work
if (childDimAttributeSource.AttributeID != childDimAttributeTarget.ID)
{
amoRelationshipSourceTemp = amoRelationshipSource.Clone();
RelationshipEndAttribute childDimAttributeSourceTemp = childDimAttributeSource.Clone();
childDimAttributeSourceTemp.AttributeID = childDimAttributeTarget.ID;
amoRelationshipSourceTemp.FromRelationshipEnd.Attributes.Remove(childDimAttributeSource.AttributeID);
amoRelationshipSourceTemp.FromRelationshipEnd.Attributes.Add(childDimAttributeSourceTemp);
}
}
// now check parent columns
foreach (RelationshipEndAttribute parentDimAttributeSource in amoRelationshipSource.ToRelationshipEnd.Attributes)
{
if (!parentDimSource.Attributes.Contains(parentDimAttributeSource.AttributeID))
{
warningMessage = "Unable to create Relationship " + relationshipName + " because (considering changes) parent column not found in target model.";
return(false);
}
DimensionAttribute parentDimAttributeTarget = parentDimTarget.Attributes.FindByName(parentDimSource.Attributes[parentDimAttributeSource.AttributeID].Name);
if (parentDimAttributeTarget == null)
{
warningMessage = "Unable to create Relationship " + relationshipName + " because (considering changes) parent column not found in target model.";
return(false);
}
////does the parent column allow non-unique values? (if so, won't work as a parent in the relationship)
//if (!( parentDimTarget.Attributes.Contains("RowNumber") &&
// parentDimTarget.Attributes["RowNumber"].AttributeRelationships.Contains(parentDimAttributeTarget.ID) &&
// parentDimTarget.Attributes["RowNumber"].AttributeRelationships[parentDimAttributeTarget.ID].Cardinality == Cardinality.One ))
//{
// warningMessage = "Unable to create Relationship " + relationshipName + " because (considering changes) parent column allows non-unique values.";
// return false;
//}
// just in case the attribute ids are not the same between source/target (though obviously the names are the same), then set the correct id (below) in the source relationship and everything will just work
if (parentDimAttributeSource.AttributeID != parentDimAttributeTarget.ID)
{
if (amoRelationshipSourceTemp == null)
{
amoRelationshipSourceTemp = amoRelationshipSource.Clone();
}
RelationshipEndAttribute parentDimAttributeSourceTemp = parentDimAttributeSource.Clone();
parentDimAttributeSourceTemp.AttributeID = parentDimAttributeTarget.ID;
amoRelationshipSourceTemp.ToRelationshipEnd.Attributes.Remove(parentDimAttributeSource.AttributeID);
amoRelationshipSourceTemp.ToRelationshipEnd.Attributes.Add(parentDimAttributeSourceTemp);
}
}
if (amoRelationshipSourceTemp != null) //i.e. we had to replace at least one attribute id
{
childDimSource.Relationships.Remove(amoRelationshipSource.ID);
childDimSource.Relationships.Add(amoRelationshipSourceTemp);
amoRelationshipSource = amoRelationshipSourceTemp;
}
// is there already a relationship with the same tables/columns?
bool foundMatch = false;
foreach (Relationship relationshipTarget in _relationships)
{
// has same parent table?
if (relationshipTarget.AmoRelationship.ToRelationshipEnd.DimensionID == amoRelationshipSource.ToRelationshipEnd.DimensionID)
{
// check columns
bool columnsMatch = true;
foreach (RelationshipEndAttribute attribute in amoRelationshipSource.FromRelationshipEnd.Attributes)
{
if (!relationshipTarget.AmoRelationship.FromRelationshipEnd.Attributes.Contains(attribute.AttributeID))
{
columnsMatch = false;
}
}
foreach (RelationshipEndAttribute attribute in amoRelationshipSource.ToRelationshipEnd.Attributes)
{
if (!relationshipTarget.AmoRelationship.ToRelationshipEnd.Attributes.Contains(attribute.AttributeID))
{
columnsMatch = false;
}
}
if (columnsMatch)
{
foundMatch = true;
}
}
}
if (foundMatch)
{
warningMessage = "Unable to create Relationship " + relationshipName + " because (considering changes) relationship already exists in target model.";
return(false);
}
// at this point we know we will add the relationship, but need to check that parent column only allows unique values. If not, change it.
foreach (RelationshipEndAttribute parentDimAttributeSource in amoRelationshipSource.ToRelationshipEnd.Attributes)
{
DimensionAttribute parentDimAttributeTarget = parentDimTarget.Attributes.FindByName(parentDimSource.Attributes[parentDimAttributeSource.AttributeID].Name);
//(already checked for existence of parentDimAttributeTarget above)
if (parentDimTarget.Attributes.Contains("RowNumber") &&
parentDimTarget.Attributes["RowNumber"].AttributeRelationships.Contains(parentDimAttributeTarget.ID) &&
parentDimTarget.Attributes["RowNumber"].AttributeRelationships[parentDimAttributeTarget.ID].Cardinality != Cardinality.One)
{
parentDimTarget.Attributes["RowNumber"].AttributeRelationships[parentDimAttributeTarget.ID].Cardinality = Cardinality.One;
foreach (DataItem di in parentDimAttributeTarget.KeyColumns)
{
di.NullProcessing = NullProcessing.Error;
}
if (_parentTabularModel.AmoDatabase.Cubes.Count > 0)
{
foreach (MeasureGroup mg in _parentTabularModel.AmoDatabase.Cubes[0].MeasureGroups)
{
if (mg.ID == parentDimTarget.ID)
{
foreach (MeasureGroupDimension mgd in mg.Dimensions)
{
if (mgd.CubeDimensionID == parentDimTarget.ID && mgd is DegenerateMeasureGroupDimension)
{
foreach (MeasureGroupAttribute mga in ((DegenerateMeasureGroupDimension)mgd).Attributes)
{
if (mga.AttributeID == parentDimAttributeTarget.ID)
{
mga.KeyColumns[0].NullProcessing = NullProcessing.Error;
}
}
}
}
}
}
}
}
}
// at this point we know we will add the relationship
Microsoft.AnalysisServices.Relationship relationshipClone = amoRelationshipSource.Clone();
// but first check if there is an existing relationship with same id
if (_parentTabularModel.ContainsRelationship(relationshipClone.ID))
{
//Id already exists, but still need to add because different definition - this is due to clever clog users changing table names that were originially in both source and target
string oldRelationshipId = relationshipClone.ID;
relationshipClone.ID = Convert.ToString(Guid.NewGuid());
}
if (active && !_parentTabularModel.ActiveRelationshipIds.Contains(relationshipClone.ID))
{
_parentTabularModel.ActiveRelationshipIds.Add(relationshipClone.ID);
}
_amoDimension.Relationships.Add(relationshipClone);
_relationships.Add(new Relationship(this, relationshipClone, copiedFromSource: true));
return(true);
}
public static void RelationshipAdd(AMO.Database tabularDatabase,
string pkTableName,
string pkColumnName,
string foreignTableName,
string foreignColumnName,
bool active = true,
bool updateInstance = true)
{
// Terminology note:
// - the Foreign 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
// ==> Relationship information is stored in the Foreign Dimension object
//
// Major steps in adding a relationship
//
// - Validate required input arguments and other initial preparations
// - Verify relationship doesn't exist before creating it
// - Add relationship to the Foreign dimension object
// - Set relationship to active, if requested (default setting)
//
// Note: In AMO, strings as indexers refer to the ID of the object, not the name
//
#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
// Verify relationship existence
// --> throw exception if relationship already exists
if (RelationshipExists(tabularDatabase, pkTableName, pkColumnName, foreignTableName, foreignColumnName))
{
throw new InvalidOperationException(Resources.RelationshipAlreadyExistInvalidOperationException);
}
// Add relationship
// First, create the INACTIVE relationship definitions in the MultipleValues end of the relationship
string newRelationshipID = Guid.NewGuid().ToString();
AMO.Relationship newRelationship = tabularDatabase.Dimensions[foreignTableId].Relationships.Add(newRelationshipID);
newRelationship.FromRelationshipEnd.DimensionID = foreignTableId;
newRelationship.FromRelationshipEnd.Attributes.Add(foreignColumnId);
newRelationship.FromRelationshipEnd.Multiplicity = AMO.Multiplicity.Many;
newRelationship.FromRelationshipEnd.Role = string.Empty;
newRelationship.ToRelationshipEnd.DimensionID = pkTableId;
newRelationship.ToRelationshipEnd.Attributes.Add(pkColumnId);
newRelationship.ToRelationshipEnd.Multiplicity = AMO.Multiplicity.One;
newRelationship.ToRelationshipEnd.Role = string.Empty;
// Set relationship to active
if (active)
{
RelationshipAlterActive(tabularDatabase, pkTableName, pkColumnName, foreignTableName, foreignColumnName, active, false);
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
