Export (0) Print
Expand All

Relationship Representation (Tabular)

A relationship is a connection between two tables of data. The relationship establishes how the data in the two tables should be correlated.

See Relationship Representation (Tabular) for a detailed explanation on how to create and manipulate the relationship representation.

In tabular models, multiple relationships can be defined between two tables. When multiple relationships, between two tables, are defined only one can be defined as the default relationship for the model and it is named as the Active relationship; all other relationships are named as Inactive.

Relationship in AMO

In terms of AMO objects all inactive relationships have a representation of a one to one mapping relationship with Relationship and no other main AMO objects are required; for the active relationship other requirements exist and a mapping to the ReferenceMeasureGroupDimension is also required.

The following code snippets show how to create a relationship in Tabular models, how to activate a relationship and how to define a primary key in a table (other than "RowNumber"). To create an active relationship a primary key need to be defined in the primary key table - PKTableName - of the relationship (the one side of the relationship), the sample shown here creates the primary key on the PKColumnName if no primary key is defined in this column. Inactive relationships can be created without the need of having a primary key on the primary key column.

        private Boolean createRelationship(string PKTableName, string PKColumnName, string MVTableName, string MVColumnName, AMO.Database tabularDb, string cubeName, Boolean forceActive)
        {
            //verify input parameters
            if(     string.IsNullOrEmpty(PKTableName) || string.IsNullOrWhiteSpace(PKTableName)
                ||  string.IsNullOrEmpty(PKColumnName) || string.IsNullOrWhiteSpace(PKColumnName)
                ||  string.IsNullOrEmpty(MVTableName) || string.IsNullOrWhiteSpace(MVTableName)
                ||  string.IsNullOrEmpty(MVColumnName) || string.IsNullOrWhiteSpace(MVColumnName)
                ||  (tabularDb == null)
                ) return false;
            if(!tabularDb.Dimensions.ContainsName(PKTableName) || !tabularDb.Dimensions.ContainsName(MVTableName)) return false;
            if(!tabularDb.Dimensions[PKTableName].Attributes.ContainsName(PKColumnName) || !tabularDb.Dimensions[MVTableName].Attributes.ContainsName(MVColumnName)) return false;

            //Verify underlying cube structure
            if (!tabularDb.Cubes[cubeName].Dimensions.ContainsName(PKTableName) || !tabularDb.Cubes[cubeName].Dimensions.ContainsName(MVTableName)) return false; //Should return an exception!!
            if (!tabularDb.Cubes[cubeName].MeasureGroups.ContainsName(PKTableName) || !tabularDb.Cubes[cubeName].MeasureGroups.ContainsName(MVTableName)) return false; //Should return an exception!!
            
            //Make sure PKTableName.PKColumnName  is set as PK ==> <attribute>.usage == AMO.AttributeUsage.Key
            if (tabularDb.Dimensions[PKTableName].Attributes[PKColumnName].Usage != AMO.AttributeUsage.Key)
            {
                //... here we are 'fixing', if there is an issue with PKTableName.PKColumnName not beeing the PK of the table
                setPKColumn(tabularDb, PKTableName, PKColumnName);
            }



            //Terminology note: 
            // -   the many side of the relationship is named the From end in AMO
            // -   the PK side of the relationship in named the To end in AMO
            //
            //It seems relationships flow FROM the many side of the relationship in TO the primary key side of the relationship in AMO
            //            
            //Verify the relationship we are creating does not exist, regardless of name.
            //if it exists, return true (no need to recreate it)
            //if it doesn't exists it will be created after this validation

            
            
            // 
            foreach (AMO.Relationship currentRelationship in tabularDb.Dimensions[MVTableName].Relationships)
            {
                if ((currentRelationship.FromRelationshipEnd.Attributes[0].AttributeID == MVColumnName)
                    && (currentRelationship.ToRelationshipEnd.DimensionID == PKTableName)
                    && (currentRelationship.ToRelationshipEnd.Attributes[0].AttributeID == PKColumnName))
                {
                    if (forceActive)
                    {
                        //Activate the relationship 
                        setActiveRelationship(tabularDb.Cubes[cubeName], MVTableName, MVColumnName, PKTableName, currentRelationship.ID);
                        //Update server with changes made here
                        tabularDb.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.CreateOrReplace);
                        tabularDb.Cubes[cubeName].MeasureGroups[MVTableName].Process(AMO.ProcessType.ProcessFull);
                    }
                    return true;
                }
            }

            //A relationship like the one to be created does not exist; ergo, let's create it:

            //First, create the INACTIVE relationship definitions in the MultipleValues end of the relationship
            #region define unique name for relationship
            string newRelationshipID = string.Format("Relationship _{0}_{1}_ to _{2}_{3}_", MVTableName, MVColumnName, PKTableName, PKColumnName);
            int rootLen = newRelationshipID.Length;
            for (int i = 0; tabularDb.Dimensions[MVTableName].Relationships.Contains(newRelationshipID); )
            {
                newRelationshipID = string.Format("{0}_{1,8:0}", newRelationshipID.Substring(0, rootLen), i);
            }
            #endregion
            AMO.Relationship newRelationship = tabularDb.Dimensions[MVTableName].Relationships.Add(newRelationshipID);

            newRelationship.FromRelationshipEnd.DimensionID = MVTableName;
            newRelationship.FromRelationshipEnd.Attributes.Add(MVColumnName);
            newRelationship.FromRelationshipEnd.Multiplicity = AMO.Multiplicity.Many;
            newRelationship.FromRelationshipEnd.Role = string.Empty;
            newRelationship.ToRelationshipEnd.DimensionID = PKTableName;
            newRelationship.ToRelationshipEnd.Attributes.Add(PKColumnName);
            newRelationship.ToRelationshipEnd.Multiplicity = AMO.Multiplicity.One;
            newRelationship.ToRelationshipEnd.Role = string.Empty;

            //Update server to create relationship
            tabularDb.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
            tabularDb.Dimensions[MVTableName].Process(AMO.ProcessType.ProcessDefault);
            tabularDb.Dimensions[PKTableName].Process(AMO.ProcessType.ProcessDefault);



            //Second, activate the relationship if relationship is to be set as the active relationship: 'forceActive==true'
            //... an inactive relationship needs only to be created on the dimensions object
            if (forceActive)
            {
                //Activate the relationship 
                setActiveRelationship(tabularDb.Cubes[cubeName], MVTableName, MVColumnName, PKTableName, newRelationshipID);                
            }           
            return true;
        }

        private void setActiveRelationship(AMO.Cube currentCube, string MVTableName, string MVColumnName, string PKTableName, string relationshipID)
        {
            if (!currentCube.MeasureGroups.Contains(MVTableName))
            {
                throw new AMO.AmoException(string.Format("Cube [{0}] does not contain Measure Group [{1}]\nError activating relationship [{2}]: [{4}] <--- [{1}].[{3}]"
                                                        , currentCube.Name, MVTableName, relationshipID, MVColumnName, PKTableName));
            }
            AMO.MeasureGroup currentMG = currentCube.MeasureGroups[MVTableName];

            if (!currentMG.Dimensions.Contains(PKTableName))
            {
                AMO.ReferenceMeasureGroupDimension newReferenceMGDim = new AMO.ReferenceMeasureGroupDimension();
                newReferenceMGDim.CubeDimensionID = PKTableName;
                newReferenceMGDim.IntermediateCubeDimensionID = MVTableName;
                newReferenceMGDim.IntermediateGranularityAttributeID = MVColumnName;
                newReferenceMGDim.Materialization = AMO.ReferenceDimensionMaterialization.Regular;
                newReferenceMGDim.RelationshipID = relationshipID;
                foreach (AMO.CubeAttribute PKAttribute in currentCube.Dimensions[PKTableName].Attributes)
                {
                    AMO.MeasureGroupAttribute PKMGAttribute = newReferenceMGDim.Attributes.Add(PKAttribute.AttributeID);
                    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.ValidationErrorCollection errors = new AMO.ValidationErrorCollection();

                newReferenceMGDim.Validate(errors, true);
                if (errors.Count > 0)
                {
                    StringBuilder errorMessages = new StringBuilder();
                    foreach (AMO.ValidationError err in errors)
                    {
                        errorMessages.AppendLine(string.Format("At {2}: # {0} : {1}", err.ErrorCode, err.FullErrorText, err.Source));
                    }
                    throw new AMO.AmoException(errorMessages.ToString());
                }
                //Update changes in the server
                currentMG.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.CreateOrReplace);
            }
            else
            {
                AMO.ReferenceMeasureGroupDimension currentReferenceMGDim = (AMO.ReferenceMeasureGroupDimension)currentMG.Dimensions[PKTableName];
                currentReferenceMGDim.RelationshipID = relationshipID;
                currentReferenceMGDim.IntermediateGranularityAttributeID = MVColumnName;
                //Update changes in the server
                currentMG.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
            }
            //process MG to activate relationship
            currentMG.Process(AMO.ProcessType.ProcessFull);

        }

        private void setPKColumn(AMO.Database tabularDb, string PKTableName, string PKColumnName)
        {
                //Find all 'unwanted' Key attributes, remove their Key definitions and include the attributes in the ["RowNumber"].AttributeRelationships
                foreach (AMO.DimensionAttribute currentDimAttribute in tabularDb.Dimensions[PKTableName].Attributes)
                {
                    if ((currentDimAttribute.Usage == AMO.AttributeUsage.Key) && (currentDimAttribute.ID != PKColumnName))
                    {
                        currentDimAttribute.Usage = AMO.AttributeUsage.Regular;
                        if (currentDimAttribute.ID != "RowNumber")
                        {
                            currentDimAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
                            currentDimAttribute.AttributeRelationships.Clear();
                            if (!tabularDb.Dimensions[PKTableName].Attributes["RowNumber"].AttributeRelationships.ContainsName(currentDimAttribute.ID))
                            {
                                AMO.DimensionAttribute currentAttribute = tabularDb.Dimensions[PKTableName].Attributes[currentDimAttribute.ID];
                                AMO.AttributeRelationship currentAttributeRelationship = tabularDb.Dimensions[PKTableName].Attributes["RowNumber"].AttributeRelationships.Add(currentAttribute.ID);
                                currentAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
                            }
                            tabularDb.Dimensions[PKTableName].Attributes["RowNumber"].AttributeRelationships[currentDimAttribute.ID].Cardinality = AMO.Cardinality.Many;
                        }
                    }
                }

                //Remove PKColumnName from ["RowNumber"].AttributeRelationships
                int PKAtribRelationshipPosition = tabularDb.Dimensions[PKTableName].Attributes["RowNumber"].AttributeRelationships.IndexOf(PKColumnName);
                if (PKAtribRelationshipPosition != -1) tabularDb.Dimensions[PKTableName].Attributes["RowNumber"].AttributeRelationships.RemoveAt(PKAtribRelationshipPosition, true);

                //Define PKColumnName as Key and add ["RowNumber"] to PKColumnName.AttributeRelationships with cardinality of One
                tabularDb.Dimensions[PKTableName].Attributes[PKColumnName].Usage = AMO.AttributeUsage.Key;
                tabularDb.Dimensions[PKTableName].Attributes[PKColumnName].KeyColumns[0].NullProcessing = AMO.NullProcessing.Error;
                if (!tabularDb.Dimensions[PKTableName].Attributes[PKColumnName].AttributeRelationships.ContainsName("RowNumber"))
                {
                    AMO.DimensionAttribute currentAttribute = tabularDb.Dimensions[PKTableName].Attributes["RowNumber"];
                    AMO.AttributeRelationship currentAttributeRelationship = tabularDb.Dimensions[PKTableName].Attributes[PKColumnName].AttributeRelationships.Add(currentAttribute.ID);
                    currentAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
                }
                tabularDb.Dimensions[PKTableName].Attributes[PKColumnName].AttributeRelationships["RowNumber"].Cardinality = AMO.Cardinality.One;

                //Update Table before going creating the relationship
                tabularDb.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
                tabularDb.Dimensions[PKTableName].Process(AMO.ProcessType.ProcessDefault);              

        }

However, to have an understanding on how to use AMO to create and manipulate relationship representations see the source code of the AMO to Tabular sample. The sample is available at Codeplex. An important note about the code: the code is provided only as a support to the logical concepts explained here and should not be used in a production environment; nor should it be used for other purpose other than the pedagogical one.

Community Additions

ADD
Show:
© 2014 Microsoft