As you know, the data in OLAP cubes has a multilevel hierarchical structure. This form of data representation is easy and comprehensible for humans. However, when the client application receives it from the OLAP data source, it is transformed into a flat table. Working with the data in such form is not very convenient for the end user, and Ranet OLAP groups the members in the dimensions in order to provide a more usable form to work with and analyze the data.
Ranet OLAP UI for WPF provides three group modes for the data in the pivot table: None, Group Neighbors, and Group To Hierarchy. Let’s review them in more detail on a real example.
We have applied member choice filter to different levels in the [Product Categories] hierarchy (Selected elements). Labeled as parent are the elements that have not been selected but are automatically included in the tab as parents of the selected elements (they are used for building the hierarchy).
No grouping rules are applied to the OLAP data in the pivot table, and the hierarchy members are shown in the same order they have been received by the pivot table upon the MDX query execution.
As you can see in the figure below, the elements appear in the table as a list in the same order they have been selected.
Group Neighbors mode groups elements in the rows and columns with the neighbor elements that have identical keys (KEY0). If the element in the row above is a parent to the element in question, they are organized in a tree-like hierarchical structure in the pivot table. You can see examples in the figure below:
- Hitch Rack – 4-Bike is a child element of the Bike Racks subcategory, while Bike Racks and Bike Stands subcategories are child elements of the Accessories product category;
- Mountain Bottle Cage, Road Bottle Cage, and Water Bottle – 30 oz. products are child elements of the Bottles and Cages subcategory (see the list of selected elements in the Member choice filter above), which in turn is a child element of the Accessories category. Bottles and Cages element is not selected in the Member choice filter, so there is no direct parent to the products in the pivot table, and they cannot be added to the hierarchy.
- Road Bikes subcategory is a child element of the Bikes category. However, there is another row between these elements, so they cannot be organized into a hierarchy.
Group To Hierarchy
Group To Hierarchy mode displays the elements according to the structure of the hierarchy in the OLAP cube used as the data source.
As you can see in the figure below, Road Bikes and Chains subcategories have been moved up in the pivot table and added to the Bikes and Components hierarchy trees, accordingly, as they are direct child members of these elements. At the same time, the elements that have no direct parents in the table, haven’t been added to any hierarchy. In order for an element to be added to the hierarchy, it needs to have a direct parent in the table (not an ancestor element).
Using different group modes can help users highlight different aspects of the data, depending on their requirements.
We hope this post was helpful to you. You can test this and other features of the pivot table control of Ranet OLAP for WPF in the free 30-day trial.