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); } }