in BismNormalizer/BismNormalizer/TabularCompare/MultidimensionalMetadata/Table.cs [579:776]
public bool CreateRelationship(Relationship relationshipSource, Dimension parentDimSource, string relationshipName, ref string warningMessage)
=> this.CreateRelationship(relationshipSource.AmoRelationship, parentDimSource, relationshipName, ref warningMessage, relationshipSource.IsActive);
/// <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;
}