Hierarchies, levels and drilling-down

Goals:

• how to create a hierarchical dimension
• how to do drill-down through a hierarchy
• detailed level description

Level: basic.

We are going to use very similar data as in the previous examples. Difference is in two added columns: category code and sub-category code. They are simple letter codes for the categories and subcategories. Download this example file.

Hierarchy

Some dimensions can have multiple levels forming a hierarchy. For example dates have year, month, day; geography has country, region, city; product might have category, subcategory and the product.

In our example we have the item dimension with three levels of hierarchy: category, subcategory and line item:

Item dimension hierarchy.

The levels are defined in the model:

"levels": [
    {
        "name":"category",
        "label":"Category",
        "attributes": ["category"]
    },
    {
        "name":"subcategory",
        "label":"Sub-category",
        "attributes": ["subcategory"]
    },
    {
        "name":"line_item",
        "label":"Line Item",
        "attributes": ["line_item"]
    }
]

You can see a slight difference between this model description and the previous one: we didn’t just specify level names and didn’t let cubes to fill-in the defaults. Here we used explicit description of each level. name is level identifier, label is human-readable label of the level that can be used in end-user applications and attributes is list of attributes that belong to the level. The first attribute, if not specified otherwise, is the key attribute of the level.

Other level description attributes are key and label_attribute`. The key specifies attribute name which contains key for the level. Key is an id number, code or anything that uniquely identifies the dimension level. label_attribute is name of an attribute that contains human-readable value that can be displayed in user-interface elements such as tables or charts.

Preparation

Again, in short we need:

• data in a database
• logical model (see model file) prepared with appropriate mappings
• denormalized view for aggregated browsing (optional)

Implicit hierarchy

Try to remove the last level line_item from the model file and see what happens. Code still works, but displays only two levels. What does that mean? If metadata - logical model - is used properly in an application, then application can handle most of the model changes without any application modifications. That is, if you add new level or remove a level, there is no need to change your reporting application.

Summary

• hierarchies can have multiple levels
• a hierarchy level is identifier by a key attribute
• a hierarchy level can have multiple detail attributes and there is one special detail attribute: label attribute used for display in user interfaces

Multiple Hierarchies

Dimension can have multiple hierarchies defined. To use specific hierarchy for drilling down:

result = browser.aggregate(cell, drilldown = [("date", "dmy", None)])

The drilldown argument takes list of three element tuples in form: (dimension, hierarchy, level). The hierarchy and level are optional. If level is None, as in our example, then next level is used. If hierarchy is None then default hierarchy is used.

To sepcify hierarchy in cell cuts just pass hierarchy argument during cut construction. For example to specify cut through week 15 in year 2010:

cut = cubes.PointCut("date", [2010, 15], hierarchy="ywd")

Note

If drilling down a hierarchy and asking cubes for next implicit level the cuts should be using same hierarchy as drilldown. Otherwise exception is raised. For example: if cutting through year-month-day and asking for next level after year in year-week-day hierarchy, exception is raised.