Hierarchy Representation (Tabular)

Hierarchies are a mechanism to provide a richer drill-up and drill-down experience to the end user.

Hierarchy Representation

In tabular object models a hierarchy is navigation path from one attribute to another based on values selected by the user,

Hierarchy in AMO

When using AMO to manage a Tabular Model table there is a one-to-one object match for a hierarchy in AMO; in AMO a hierarchy is represented the Hierarchy object.

The following code snippet shows how to add a hierarchy to an existing tabular model. The code assumes you have an AMO database object, newDatabase, and an AMO cube object, modelCube.

        private void addHierarchy(
                            AMO.Database newDatabase
                         ,  AMO.Cube modelCube
                         ,  string tableName
                         ,  string hierarchyName
                         ,  string levelsText
                     )
        {
            //Validate input
            if (string.IsNullOrEmpty(hierarchyName) || string.IsNullOrEmpty(levelsText))
            {
                MessageBox.Show(String.Format("Hierarchy Name or Layout must be provided."), "AMO to Tabular message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            if (!overwriteHierarchy.Checked && newDatabase.Dimensions[tableName].Hierarchies.Contains(hierarchyName))
            {
                MessageBox.Show(String.Format("Hierarchy already exists.\nGive a new name or enable overwriting"), "AMO to Tabular message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            try
            {
                if (newDatabase.Dimensions[tableName].Hierarchies.Contains(hierarchyName))
                {
                    //Hierarchy exists... deleting it to write it later
                    newDatabase.Dimensions[tableName].Hierarchies.Remove(hierarchyName, true);
                    newDatabase.Dimensions[tableName].Update(AMO.UpdateOptions.AlterDependents);
                }
                AMO.Hierarchy currentHierarchy = newDatabase.Dimensions[tableName].Hierarchies.Add(hierarchyName, hierarchyName);
                currentHierarchy.AllMemberName = string.Format("(All of {0})", hierarchyName);
                //Parse hierarchyLayout
                using (StringReader levels = new StringReader(levelsText))
                {
                    //Each line:
                    //  must come with: The columnId of the attribute in the dimension --> this represents the SourceAttributeID
                    //  optional: A display name for the Level (if this argument doesn't come the default is the SourceAttributeID)
                    string line;
                    while ((line = levels.ReadLine()) != null)
                    {
                        if (string.IsNullOrEmpty(line) || string.IsNullOrWhiteSpace(line)) continue;
                        line = line.Trim();
                        string[] hierarchyData = line.Split(',');
                        if (string.IsNullOrEmpty(hierarchyData[0])) continue; //first argument cannot be empty or blank, 
                                                                              //assume is a blank line and ignore it
                        string levelSourceAttributeID = hierarchyData[0].Trim();
                        string levelID = (hierarchyData.Length > 1 && !string.IsNullOrEmpty(hierarchyData[1])) ? hierarchyData[1].Trim() : levelSourceAttributeID;
                        currentHierarchy.Levels.Add(levelID).SourceAttributeID = levelSourceAttributeID;
                    }
                }
                newDatabase.Dimensions[tableName].Update(AMO.UpdateOptions.AlterDependents);

            }
            catch (Exception ex)
            {
                MessageBox.Show(String.Format("Error creating hierarchy [{0}].\nError message: {1}", newHierarchyName.Text, ex.Message), "AMO to Tabular message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                if (newDatabase.Dimensions[tableName].Hierarchies.Contains(hierarchyName))
                {
                    //Hierarchy was created but exception prevented complete hierarchy to be written... deleting incomplete hierarchy
                    newDatabase.Dimensions[tableName].Hierarchies.Remove(hierarchyName, true);
                    newDatabase.Dimensions[tableName].Update(AMO.UpdateOptions.AlterDependents);
                }

            }
            newDatabase.Dimensions[tableName].Process(AMO.ProcessType.ProcessFull);
            modelCube.MeasureGroups[tableName].Process(AMO.ProcessType.ProcessFull);
        }

AMO2Tabular sample

To have an understanding on how to use AMO to create and manipulate hierarchy representations see the source code of the AMO to Tabular sample; specifically check in the following source file: AddHierarchies.cs. 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.