Cubes - OLAP Framework¶

Model¶

Logical model describes the data from user’s or analyst’s perspective: data how they are being measured, aggregated and reported. Model is independent of physical implementation of data. This physical independence makes it easier to focus on data instead on ways of how to get the data in understandable form.

More information about logical model can be found in the chapter Logical Model and Metadata.

See also developer’s reference.

Browser¶

Core of the Cubes analytics functionality is the aggregation browser. The browser module contains utility classes and functions for the browser to work.

More information about browser can be found in the chapter Slicing and Dicing. See also programming reference.

Backends¶

Backends provide the actual data aggregation and browsing functionality. Cubes comes with built-in ROLAP backend which uses SQL database using SQLAlchemy.

Framework has modular nature and supports multiple database backends, therefore different ways of cube computation and ways of browsing aggregated data.

Multiple backends can be used at once, even multiple sources from the same backend might be used in the analytical workspace.

More about existing backends can be found in the backends documentation. See also the backends programming reference reference.

Server¶

Cubes comes with built-in WSGI HTTP OLAP server called slicer - Command Line Tool and provides json API for most of the cubes framework functionality. The server is based on the Werkzeug WSGI framework.

More information about the Slicer server requests can be found in the chapter OLAP Server. See also programming reference of the server module.

Model¶

Download the tutorial model and save it as tutorial_model.json.

In the slicer.ini file specify the model:

[workspace]
model: tutorial_model.json

For more information about how to add more models to the workspace see the configuration documentation.

Equivalent in Python is:

>>> workspace.import_model("tutorial_model.json")

You might call import_model() with as many models as you need. Only limitation is that the public cubes and public dimensions should have unique names.

Aggregations¶

Browser is an object that does the actual aggregations and other data queries for a cube. To obtain one:

>>> browser = workspace.browser("ibrd_balance")

Compute the aggregate. Measure fields of AggregationResult have aggregation suffix. Also a total record count within the cell is included as record_count.

>>> result = browser.aggregate()
>>> result.summary["record_count"]
62
>>> result.summary["amount_sum"]
1116860

Now try some drill-down by year dimension:

>>> result = browser.aggregate(drilldown=["year"])
>>> for record in result:
...     print record
{u'record_count': 31, u'amount_sum': 550840, u'year': 2009}
{u'record_count': 31, u'amount_sum': 566020, u'year': 2010}

Drill-down by item category:

>>> result = browser.aggregate(drilldown=["item"])
>>> for record in result:
...     print record
{u'item.category': u'a', u'item.category_label': u'Assets', u'record_count': 32, u'amount_sum': 558430}
{u'item.category': u'e', u'item.category_label': u'Equity', u'record_count': 8, u'amount_sum': 77592}
{u'item.category': u'l', u'item.category_label': u'Liabilities', u'record_count': 22, u'amount_sum': 480838}

Mappings and Joins¶

One can specify shared mappings and joins on the model-level. Those mappings and joins are inherited by all the cubes in the model.

The mappigns dictionary of a cube is merged with model’s global mapping dictionary. Cube’s values overwrite the model’s values.

The joins can be considered as named templates. They should contain name property that will be referenced by a cube.

Visibility: The joins and mappings are local to a single model. They are not shared within the workspace.

"joins": [
    {
        "name": "date",
        "detail": "dim_date.date_id",
        "method": "match"
    }
]

"joins": [
    {
        "name": "date",
        "master": "fact_contract.contract_start_date_id",
    },
    {
        "name": "date",
        "master": "fact_sales.contract_sign_date_id",
    }
]

File Representation¶

The model can be represented either as a JSON file or as a directory with JSON files. The single-file model specification is just a dictionary with model properties. The model directory bundle should have the following content:

• model.json – model’s master metadata – same as single-file model
• dim_*.json – dimension metadata file – single dimension dictionary
• cube_*.json – cube metadata – single cube dictionary

The list of dimensions and cubes in the model.json are merged with the dimensions and cubes in the separate files. Avoid duplicate definitions.

Example directory bundle model:

model.cubesmodel/
    model.json
    dim_date.json
    dim_organization.json
    dim_category.json
    cube_contracts.json
    cube_events.json

Model Provider and External Models¶

If the model is provided from an external source, such as an API or a database, then name of the provider should be specified in provider.

The provider receives the model’s metadata and the model’s data store (if the provider so desires). Then the provider generates all the cubes and the dimensions.

Example of a model that is provided from an external source (Mixpanel):

{
    "name": "Events",
    "provider": "mixpanel"
}

Dimension Visibility¶

All dimensions from a static (file) model are shared in the workspace by default. That means that the dimensions can be reused freely among cubes from different models.

One can define a master model with dimensions only and no cubes. Then define one model per cube category, datamart or any other categorization. The models can share the master model dimensions.

To expose only certain dimensions from a model specify a list of dimension names in the public_dimensions model property. Only dimensions from the list can be shared by other cubes in the workspace.

Measures and Aggregates¶

Measure and measure aggregate

Measures are numerical properties of a fact. They might be represented, for example, as a table column. Measures are aggregated into measure aggregates. The measure is described as:

• name – measure identifier (required)
• label – human readable name to be displayed (localized)
• info – additional custom information (unspecified)
• aggregates – list of aggregate functions that are provided for this measure. This property is for generating default aggregates automatically. It is highly recommended to list the aggregates explicitly and avoid using this property.
• window_size – number of elements within a window for window functions such as moving average. If not provided and function requires it then 1 (one element) is assumed.

Example:

"measures": [
    {
        "name": "amount",
        "label": "Sales Amount"
    },
    {
        "name": "vat",
        "label": "VAT"
    }
]

Measure aggregate is a value computed by aggregating measures over facts. It’s properties are:

• name – aggregate identifier, such as: amount_sum, price_avg, total, record_count
• label – human readable label to be displayed (localized)
• measure – measure the aggregate is derived from, if it exists or it is known. Might be empty.
• function - name of an aggregate function applied to the measure, if known. For example: sum, min, max.
• window_size – number of elements within a window for window functions such as moving average. If not provided and function requires it then 1 (one element) is assumed.
• info – additional custom information (unspecified)

Example:

"aggregates": [
    {
        "name": "amount_sum",
        "label": "Total Sales Amount",
        "measure": "amount",
        "function": "sum"
    },
    {
        "name": "vat_sum",
        "label": "Total VAT",
        "measure": "vat",
        "function": "sum"
    },
    {
        "name": "item_count",
        "label": "Item Count",
        "function": "count"
    }
]

Note the last aggregate item_count – it counts number of the facts within a cell. No measure required as a source for the aggregate.

If no aggregates are specified, Cubes generates default aggregates from the measures. For a measure:

"measures": [
    {
        "name": "amount",
        "aggregates": ["sum", "min", "max"]
    }
]

The following aggregates are created:

"aggregates" = [
    {
        "name": "amount_sum",
        "measure": "amount",
        "function": "sum"
    },
    {
        "name": "amount_min",
        "measure": "amount",
        "function": "min"
    },
    {
        "name": "amount_max",
        "measure": "amount",
        "function": "max"
    }
]

If there is a list of aggregates already specified in the cube explicitly, both lists are merged together.

In previous version of Cubes there was omnipresent measure aggregate called record_count. It is no longer provided by default and has to be explicitly defined in the model. The name can be of any choice, it is not a built-in aggregate anymore. To keep the original behavior, the following aggregate should be added:

"aggregates": [
    {
        "name": "record_count",
        "function": "count"
    }
]

Customized Dimension Linking¶

It is possible to specify how dimensions are linked to the cube. The dimensions list might contain, besides dimension names, also a specification how the dimension is going to be used in the cube’s context. The specification might contain:

• hierarchies – list of hierarchies that are relevant for the cube. For example the date dimension might be defined as having day granularity, but some cubes might be only at the month level. To specify only relevant hierarchies use hierarchies metadata property:
• exclude_hierarchies – hierarchies to be excluded when cloning the original dimension. Use this instead of hierarchies if you would like to preserve most of the hierarchies and remove just a few.
• default_hierarchy_name – name of default hierarchy for a dimension in the context of the cube
• cardinality – cardinality of the dimension with regards to the cube. For example one cube might contain housands product types, another might have only a few, but they both share the same products dimension
• alias – how the dimension is going to be called in the cube. For example, you might have two date dimensions and name them start_date and end_date using the alias

Example:

{
    "name": "churn",

    "dimensions": [
        {"name": "date", "hierarchies": ["ym", "yqm"]},
        "customer",
        {"name": "date", "alias": "contract_date"}
    ],

    ...
}

The above cube will have three dimensions: date, customer and contract_date. The date dimension will have only two hierarchies with lowest granularity of month, the customer dimension will be assigned as-is and the contract_date dimension will be the same as the original date dimension.

Dimension Templates¶

If you are creating more dimensions with the same or similar structure, such as multiple dates or different types of organisational relationships, you might create a template dimension and then use it as base for the other dimensions:

"dimensions" = [
    {
        "name": "date",
        "levels": [...]
    },
    {
        "name": "creation_date",
        "template": "date"
    },
    {
        "name": "closing_date",
        "template": "date"
    }
]

All properties from the template dimension will be copied to the new dimension. Properties can be redefined in the new dimension. In that case, the old value is discarded. You might change levels, hierarchies or default hierarchy. There is no way how to add or drop a level from the template, all new levels have to be specified again if they are different than in the original template dimension. However, you might want to just redefine hierarchies to omit unnecessary levels.

Level¶

Dimension hierarchy levels are described as:

Fields marked with * are required.

If no attributes are specified then only one attribute is assumed with the same name as the level.

If no key is specified, then first attribute is assumed.

If no label_attribute is specified, then second attribute is assumed if level has more than one attribute, otherwise the first attribute is used.

Example of month level of date dimension:

{
    "month",
    "label": "Mesiac",
    "key": "month",
    "label_attribute": "month_name",
    "attributes": ["month", "month_name", "month_sname"]
},

Example of supplier level of supplier dimension:

{
    "name": "supplier",
    "label": "Dodávateľ",
    "key": "ico",
    "label_attribute": "name",
    "attributes": ["ico", "name", "address", "date_start", "date_end",
                    "legal_form", "ownership"]
}

Hierarchy¶

Hierarchies are described as:

Required is only name.

Example:

"hierarchies": [
    {
        "name": "default",
        "levels": ["year", "month"]
    },
    {
        "name": "ymd",
        "levels": ["year", "month", "day"]
    },
    {
        "name": "yqmd",
        "levels": ["year", "quarter", "month", "day"]
    }
]

Attributes¶

Dimension level attributes can be specified either as rich metadata or just simply as strings. If only string is specified, then all attribute metadata will have default values, label will be equal to the attribute name.

The optional order is used in aggregation browsing and reporting. If specified, then all queries will have results sorted by this field in specified direction. Level hierarchy is used to order ordered attributes. Only one ordered attribute should be specified per dimension level, otherwise the behavior is unpredictable. This natural (or default) order can be later overridden in reports by explicitly specified another ordering direction or attribute. Explicit order takes precedence before natural order.

For example, you might want to specify that all dates should be ordered by default:

"attributes" = [
    {"name" = "year", "order": "asc"}
]

Locales is a list of locale names. Say we have a CPV dimension (common procurement vocabulary - EU procurement subject hierarchy) and we are reporting in Slovak, English and Hungarian. The attributes will be therefore specified as:

"attributes" = [
    {
        "name" = "group_code"
    },
    {
        "name" = "group_name",
        "order": "asc",
        "locales" = ["sk", "en", "hu"]
    }
]

group name is localized, but group code is not. Also you can see that the result will always be sorted by group name alphabetical in ascending order.

In reports you do not specify locale for each localized attribute, you specify locale for whole report or browsing session. Report queries remain the same for all languages.

Roles¶

Some dimensions and levels might have special, but well known, roles. One example of a role is time. There might be more recognized roles in the future, for example geography.

Front-ends that respect roles might provide different user interface elements, such as date and time pickers for selecting values of a date/time dimension. For the date picker to work, the front-end has to know, which dimension represents date and which levels of the dimension represent calendar units such as year, month or day.

The role of a dimension has to be explicitly stated. Front-ends are not required to assume a dimension named date is really a full date dimension.

The level roles do not have to be mentioned explicitly, if the level name can be recognized to match a particuliar role. For example, in a dimension with role time level with name year will have automatically role year.

Level roles have to be specified when level names are in different language or for any reason don’t match english calendar unit names.

Currently there is only one recognized dimension role: time. Recognized level roles with their default assignment by level name are: year, quarter, month, day, hour, minute, second, week, weeknum, dow, isoyear, isoweek, isoweekday.

The key value of level with role week is expected to have format YYYY-MM-DD.

Simple Star Schema¶

Synopsis: Fact table has the same name as the cube, dimension tables have same names as dimensions.

Fact table is called sales, has one measure amount and two dimensions: store and product. Each dimension has two attributes.

"cubes": [
    {
        "name": "sales",
        "dimensions": ["product", "store"],
        "joins": [
            {"master":"product_id", "detail":"product.id"},
            {"master":"store_id", "detail":"store.id"}
        ]
    }
],
"dimensions": [
    { "name": "product", "attributes": ["code", "name"] },
    { "name": "store", "attributes": ["code", "address"] }
]

Simple Dimension¶

Synopsis: Dimension is represented only by one attribute, has no details, neither hierarchy.

Similar schema as Simple Star Schema Note the dimension year which is represented just by one numeroc attribute.

It is important that no attributes are specified for the dimension. There dimension will be referenced just by its name and dimension label is going to be used as attribute label as well.

"cubes": [
    {
        "name": "sales",
        "dimensions": ["product", "store", "year"],
        "joins": [
            {"master":"product_id", "detail":"product.id"},
            {"master":"store_id", "detail":"store.id"}
        ]
    }
],
"dimensions": [
    { "name": "product", "attributes": ["code", "name"] },
    { "name": "store", "attributes": ["code", "address"] }
    { "name": "year" }
]

Table Prefix¶

Synopsis: dimension tables share a common prefix, fact tables share common prefix.

In our example the dimension tables have prefix dim_ as in dim_product or dim_store and facts have prefix fact_ as in fact_sales.

There is no need to change the model, only the data store configuration. In Python code we specify the prefix during the data store registration in cubes.Workspace.register_store():

workspace = Workspace()
workspace.register_store("default", "sql",
                         url=DATABASE_URL,
                         dimension_prefix="dim_",
                         dimension_suffix="_dim",
                         fact_suffix="_fact",
                         fact_prefix="fact_")

When using the OLAP Server we specify the prefixes in the [store] section of the slicer.ini configuration file:

[store]
...
dimension_prefix="dim_"
fact_prefix="fact_"

Not Default Database Schema¶

Synopsis: all tables are stored in one common schema that is other than default database schema.

To specify database schema (in our example sales_datamart) in Python pass it in the schema argument of cubes.Workspace.register_store():

workspace = Workspace()
workspace.register_store("default", "sql",
                         url=DATABASE_URL,
                         schema="sales_datamart")

For the OLAP Server the schema is specifiedn in the [store] section of the slicer.ini configuration file:

[store]
...
schema="sales_datamart"

Separate Dimension Schema¶

Synopsis: dimension tables share one database schema and fact tables share another database schema

Dimensions can be stored in a different database schema than the fact table schema.

To specify database schema of dimensions (in our example dimensions) in Python pass it in the dimension_schema argument of cubes.Workspace.register_store():

workspace = Workspace()
workspace.register_store("default", "sql",
                                   url=DATABASE_URL,
                                   schema="facts",
                                   dimension_schema="dimensions")

For the OLAP Server the dimension schema is specifiedn in the [store] section of the slicer.ini configuration file:

[store]
...
schema="facts"
dimension_schema="dimensions"

Many-to-Many Relationship¶

Synopsis: One fact might have multiple dimension members assigned

There are several options how the case of multiple dimension members per fact can be solved. Each has it advantages and disadvantages. Here is one of them: using a bridge table.

This is our logical intention: there might be multiple representatives involved in an interaction cases:

We can solve the problem with adding a bridge table and by creating artificial level representative_group. This group is unique combination of representatives that were involved in an interaction.

The model looks like:

"cubes": [
    {
        "dimensions": ["representative", ...],
        "joins": [
            {
                "master":"representative_group_id",
                "detail":"bridge_representative.group_id"
            },
            {
                "master":"bridge_representative.representative_id",
                "detail":"representative.id"
            }
        ]
    }
],
"dimensions": [
    {
        "name": "representative",
        "levels": [
            { "name":"team" },
            { "name":"name", "nonadditive": "any"}
        ]
    }
]

You might have noticed that the bridge table is hidden – you can’t see it’s contents anywhere in the cube.

There is one problem with aggregations when such dimension is involved: by aggregating over any level that is not the most detailed (deepest) we might get double (multiple) counting of the dimension members. For this reason it is important to specify all higher levels as nonadditive for any other dimension. It his case, backends that are aware of the issue, might handle it appropriately.

Some front-ends might not even allow to aggregate by levels that are marked as nonadditivy.

Basic Attribute Mapping¶

Synopsis: table column has different name than a dimension attribute or a measure.

In our example we have a flat dimension called year, but the physical table column is “sales_year”. In addition we have a measure amount however respective physical column is named total_amount.

We define the mappings within a cube:

"cubes": [
    {
        "dimensions": [..., "year"],
        "measures": ["amount"],
        "mappings": {
            "year":"sales_year",
            "amount":"total_amount"]
        }
    }
],
"dimensions": [
    ...
    { "name": "year" }
]

Shared Dimension Table¶

Synopsis: multiple dimensions share the same dimension table

Clients and suppliers might share one table with all organisations and companies. We have to specify a table alias in the joins part of the cube definition. The table aliases should follow the same naming pattern as the other tables – that is, if we are using dimension prefix, then the alias should include the prefix as well:

If the alias follows dimension naming convention, as in the example, then no mapping is required.

"cubes": [
    {
        "name": "sales"
        "dimensions": ["supplier", "client"],
        "measures": ["amount"],
        "joins": [
            {
                "master":"supplier_id",
                "detail":"dim_organisation.id",
                "alias":"dim_supplier"
            },
            {
                "master":"client_id",
                "detail":"dim_organisation.id",
                "alias":"dim_client"
            }
        ]
    }
],
"dimensions": [
    {
      "name": "supplier",
      "attributes": ["id", "name", "address"]
    },
    {
      "name": "client",
      "attributes": ["id", "name", "address"
    }
]

Simple Hierarchy¶

Synopsis: Dimension has more than one level.

Product dimension has two levels: product category and product. The product category level is represented by two attributes category_code (as key) and category. The product has also two attributes: product_code and name.

"cubes": [
    {
        "dimensions": ["product", ...],
        "measures": ["amount"],
        "joins": [
            {"master":"product_id", "detail":"product.id"}
        ]
    }
],
"dimensions": [
    {
        "name": "product",
        "levels": [
            {
                "name":"category",
                "attributes": ["category_code", "category"]
            },
            {
                "name":"product",
                "attributes": ["code", "name"]
            }
        ]
    }
]

Multiple Hierarchies¶

Synopsis: Dimension has multiple ways how to organise levels into hierarchies.

Dimensions such as date (depicted below) or geography might have multiple ways of organizing their attributes into a hierarchy. The date can be composed of year-month-day or year-quarter-month-day.

To define multiple hierarchies, first define all possible levels. Then create list of hierarchies where you specify order of levels for that particular hierarchy.

The code example below is in the “dimensions” section of the model:

{
    "name":"date",
    "levels": [
        { "name": "year", "attributes": ["year"] },
        { "name": "quarter", "attributes": ["quarter"] },
        { "name": "month", "attributes": ["month", "month_name"] },
        { "name": "week", "attributes": ["week"] },
        { "name": "weekday", "attributes": ["weekday"] },
        { "name": "day", "attributes": ["day"] }
    ],
    "hierarchies": [
        {"name": "ymd", "levels":["year", "month", "day"]},
        {"name": "ym", "levels":["year", "month"]},
        {"name": "yqmd", "levels":["year", "quarter", "month", "day"]},
        {"name": "ywd", "levels":["year", "week", "weekday"]}
    ],
    "default_hierarchy_name": "ymd"
}

The default_hierarchy_name specifies which hierarchy will be used if not mentioned explicitly.

Multiple Tables for Dimension Levels¶

Synopsis: Each dimension level has a separate table

We have to join additional tables and map the attributes that are not in the “main” dimension table (table with the same name as the dimension):

"cubes": [
    {
        "dimensions": ["product", ...],
        "measures": ["amount"],
        "joins": [
            {"master":"product_id", "detail":"product.id"},
            {"master":"product.category_id", "detail":"category.id"}
        ],
        "mappings": {
            "product.category_code": "category.code",
            "product.category": "category.name"
        }
    }
],
"dimensions": [
    {
        "name": "product",
        "levels": [
            {
                "name":"category",
                "attributes": ["category_code", "category"]
            },
            {
                "name":"product",
                "attributes": ["code", "name"]
            }
        ]
    }
]

Model Labels¶

Synopsis: Labels for parts of model that are to be displayed to the user

Labels are used in report tables as column headings or as filter descriptions. Attribute (and column) names should be used only for report creation and despite being readable and understandable, they should not be presented to the user in the raw form.

Labels can be specified for any model object (cube, dimension, level, attribute) with the label attribute:

"cubes": [
    {
        "name": "sales",
        "label": "Product Sales",
        "dimensions": ["product", ...]
    }
],
"dimensions": [
    {
        "name": "product",
        "label": "Product",
        "attributes": [
            {"name": "code", "label": "Code"},
            {"name": "name", "label": "Product"},
            {"name": "price", "label": "Unit Price"},
        ]
    }
]

Key and Label Attribute¶

Synopsis: specify which attributes are going to be used for flitering (keys) and which are going to be displayed in the user interface (labels)

"dimensions": [
    {
        "name": "product",
        "levels": [
            {
                "name": "product",
                "attributes": ["code", "name", "price"]
                "key": "code",
                "label_attribute": "name"
            }
        ]
    }
]

Example use:

result = browser.aggregate(drilldown=["product"])

for row in result.table_rows("product"):
   print "%s: %s" % (row.label, row.record["amount_sum"])

Localized Data¶

Synopsis: attributes might have values in multiple languages

Dimension attributes might have language-specific content. In cubes it can be achieved by providing one column per language (denormalized localization). The default column name should be the same as the localized attribute name with locale suffix, for example if the reported attribute is called name then the columns should be name_en for English localization and name_hu for Hungarian localization.

"dimensions": [
     {
         "name": "product",
         "label": "Product",
         "attributes": [
             {"name": "code", "label": "Code"},
             {
                 "name": "name",
                 "label": "Product",
                 "locales": ["en", "fr", "es"]
             }
         ]
     }
 ]

Use in Python:

browser = workspace.browser(cube, locale="fr")

The browser instance will now use only the French localization of attributes if available.

In slicer server requests language can be specified by the lang= parameter in the URL.

The dimension attributes are referred in the same way, regardless of localization. No change to reports is necessary when a new language is added.

Notes:

• only one locale per browser instance – either switch the locale or create another browser
• when non-existing locale is requested, then the default (first in the list of the localized attribute) locale is used

Localized Model Labels¶

Synopsis: Labels of model objects, such as dimensions, levels or attributes are localized.

We have a reporting site that uses two languages: English and Slovak. We want all labels to be available in both of the languages. Also we have a product name that has to be localized.

First we define the model and specify that the default locale of the model is English (for this case). Note the locale property of the model, the label attributes and the locales of product.name attribute:

{
    "locale": "en",
    "cubes": [
        {
            "name": "sales",
            "label": "Product Sales",
            "dimensions": ["product"],
            "measures": [
                {"name": "amount", "label": "Amount"}
            ]
        }
    ],
    "dimensions": [
        {
            "name": "product",
            "label": "Product",
            "attributes": [
                {
                  "name": "code",
                  "label": "Code"
                },
                {
                  "name": "name",
                  "label": "Product",
                  "locales": ["en", "sk"]
                },
                {
                  "name": "price",
                  "label": "Unit Price"
                }
            ]
        }
    ]
}

Next we create a separate translation dictionary for the other locale, in our case it is Slovak or sk. If we are translating only labels, no descriptions or any other information, we can use the simplified form:

{
   "locale": "sk",
   "dimensions":
   {
      "product”:
      {
           "levels":
           {
              "product" : "Produkt"
           },
           "attributes" :
           {
               "code": "Kód produktu",
               "name": "Produkt",
               "price": "Jednotková cena"
           }
        }
    },
    "cubes":
    {
        "sales":
        {
            "measures":
            {
                "amount": "Suma"
            }
        }
    }
}

Full localization with detailed dictionaries looks like this:

{
   "locale": "sk",
   "dimensions":
   {
      "product”:
      {
           "levels":
           {
              "product" : { "label" : "Produkt"}
           },
           "attributes" :
           {
               "code": {"label": "Kód produktu"},
               "name": {"label": "Produkt"},
               "price": {"label": "Jednotková cena"}
           }
        }
    },
    "cubes":
    {
        "sales":
        {
            "measures":
            {
                "amount": {"label": "Suma"}
            }
        }
    }
}

To create a model with translations:

translations = {"sk": "model-sk.json"}
model = create_model("model.json", translations)

The model created this way will be in the default locale. To get localized version of the master model:

localized_model = model.localize("sk")

Note

The cubes.Workspace.browser() method creates a browser with appropriate model localization, no explicit request for localization is needed.

Drilldown¶

Drill-down – get more details, group the aggregation by dimension members.

For example “sales by month in 2010”:

cut = PointCut("date", [2010])
cell = Cell(cube, [cut])
result = browser.aggregate(cell, drilldown=["date"])

for row in result:
    print "%s: %s" % (row["date.year"], row["amount_sum"])

Slicer: /cube/sales/aggregate?cut=date:2010&drilldown=date

Pagination¶

Results can be paginated by specifying page and page_size arguments:

result = browser.aggregate(cell, drilldown, page=0, page_size=10)

Server: /cube/sales/aggregate?cell=...&drilldown=...&page=0&pagesize=10

Split¶

Provisional:

• aggregate(cell, drilldown, split)

Simple Authorization¶

Simple authorization based on JSON files: rights and roles. The rights file contains a dictionary with keys as user identities (user names, API keys, ...) and values as right descriptions.

The user right is described as:

• roles – list of of user’s role – user inherits the restrictions from the role
• allowed_cubes – list of cubes that the user can access (and no other cubes)
• denied_cubes – list of cubes that the user can not access (he can access the rest of cubes)
• cube_restrictions – a dictionary where keys are cube names and values are lists of cuts

The roles file has the same structure as the rights file, instead of users it defines inheritable roles. The roles can inherit properties from other roles.

Example of roles file:

{
    "retail": {
        "allowed_cubes": ["sales"]
    }
}

Rights file:

{
    "martin": {
        "roles": ["retail"],
    }
}

The rights file of the simple authorization method might contain a special guest role which will be used when no other identity is found. See the configuration documentation for more information.

json_record_limit¶

Number of rows to limit when generating JSON output with iterable objects, such as facts. Default is 1000. It is recommended to use alternate response format, such as CSV, to get more records.

modules¶

Space separated list of modules to be loaded. This is only used if run by the slicer command.

prettyprint¶

If set to true, JSON is serialized with indentation of 4 spaces. Set to true for demonstration purposes, omit or comment out option for production use.

host¶

Host or IP address where the server binds, defaults to localhost.

port¶

Port on which the server listens, defaults to 5000.

reload¶

Suitable for development only. Set to yes to enable Werkzeug reloader.

allow_cors_origin¶

Cross-origin resource sharing header. Other related headers are added as well, if this option is present.

authentication¶

Authentication method, see Authentication and Authorization below for more information.

pid_file¶

Path to a file where PID of the running server will be written. If not provided, no PID file is created.

Authorization¶

Localization configuration¶

File Locations¶

Logging configuration¶

Namespaces¶

If not specified otherwise, all cubes share the same default namespace. Their names within namespace should be unique.

path¶

Path to model .json file. See Logical Model and Metadata for more on model definition.

Data store properties¶

[store]
type = sql
url = postgresql://localhost/data
schema = cubes

[store]
type = mixpanel
model = mixpanel.json
api_key = 123456abcd
api_secret = 12345abcd

[store slicer1]
type = slicer
url = http://some.host:5000

[store slicer2]
type = slicer
url = http://other.host:5000

[store slicer3]
type = slicer
namespace = external
url = http://some.host:5000

[store slicer4]
type = slicer
namespace = default.
url = http://some.host:5000

Authorization¶

To configure authorization, you need to enable authorization in workspace section.

[workspace]
authorization = simple

[authorization]
rights_file = /path/to/access_rights.json

Simple authorization¶

The simple authorization has following configuration options:

Authentication¶

Example authentication via parameter passing:

[server]
authentication = pass_parameter

[authentication]
# additional authentication parameters
parameter = token

This configures server to expect a GET parameter token which will be passed on to authorization.

type¶

Type of query log. Required.

Valid options are:

default

Log using Cubes logger via Python logging module.

csv_file

Log into a CSV file. Specify the file name via path option.

json

Log into file as quasi-JSON file - each log record is valid JSON and records are separated by newlines. Specify the file name via path option.

sql

Log into a SQL table. SQL request logger options are:

• url – database URL
• table – database table
• dimensions_table – table with dimension use (optional)

If tables do not exist, they are created automatically.

Example query log configuration¶

This configuration will create three query loggers, all at once. query_log_one will emit to Python logging and will show in console if log_level is set to info or more verbose. query_log_two will log queries into CSV file /var/log/cubes/queries.csv. query_log_three will insert query log into table cubes_query_log in a PostgreSQL database named cubes_log located on a remote host named log_host.

[query_log_one]
type = default

[query_log_two]
type = csv
path = /var/log/cubes/queries.csv

[query_log_three]
type = sql
url = postgresql://log_host/cubes_log
table = cubes_query_log

Database Connection¶

(advanced topic)

To fine-tune the SQLAlchemy database connection some of the create_engine() parameters can be specified as sqlalchemy_PARAMETER:

• sqlalchemy_case_sensitive
• sqlalchemy_convert_unicode
• sqlalchemy_pool_size
• sqlalchemy_pool_recycle
• sqlalchemy_pool_timeout
• sqlalchemy_... ...

Please refer to the create_engine documentation for more information.

Implicit Mapping¶

With implicit mapping one can match a database schema with logical model and does not have to specify additional mapping metadata. Expected structure is star schema with one table per (denormalized) dimension.

Dimensions¶

In short: a dimension attribute customer.name maps to table customer and column name by default. A dimension without details and with just a single level such as is_hungry maps to the is_hungry column of the fact table.

By default, dimension tables are expected to have same name as dimensions and dimension table columns are expected to have same name as dimension attributes:

It is quite common practice that dimension tables have a prefix such as dim_ or dm_. Such prefix can be specified with dimension_prefix option.

The rules are:

• fact table should have same name as represented cube: fact table name = fact table prefix + fact table name
• dimension table should have same name as the represented dimension, for example: product (singular): dimension table name = dimension prefix + dimension name
• column name should have same name as dimension attribute: name, code, description
• references without dimension name in them are expected to be in the fact table, for example: amount, discount (see note below for simple flat dimensions)
• if attribute is localized, then there should be one column per localization and should have locale suffix: description_en, description_sk, description_fr (see below for more information)

Database Schemas¶

For databases that support schemas, such as PostgreSQL, option schema can be used to specify default database schema where all tables are going to be looked for.

In case you have dimensions stored in separate schema than fact table, you can specify that in dimension_schema. All dimension tables are going to be searched in that schema.

Explicit Mapping¶

If the schema does not match expectations of cubes, it is possible to explicitly specify how logical attributes are going to be mapped to their physical tables and columns. Mapping dictionary is a dictionary of logical attributes as keys and physical attributes (columns, fields) as values. The logical attributes references look like:

• dimensions_name.attribute_name, for example: geography.country_name or category.code
• fact_attribute_name, for example: amount or discount

Following mapping maps attribute name of dimension product to the column product_name of table dm_products.

"mappings": {
    "product.name": "dm_products.product_name"
}

If it is in different schema or any part of the reference contains a dot:

"mappings": {
    "product.name": {
            "schema": "sales",
            "table": "dm_products",
            "column": "product_name"
        }
}

Both, explicit and implicit mappings have ability to specify default database schema (if you are using Oracle, PostgreSQL or any other DB which supports schemas).

The mapping process process is like this:

"mappings": {
  "date.year": {"column":"date", "extract":"year"},
  "date.month": {"column":"date", "extract":"month"},
  "date.day": {"column":"date", "extract":"day"}
}

Localization¶

From physical point of view, the data localization is very trivial and requires language denormalization - that means that each language has to have its own column for each attribute.

Localizable attributes are those attributes that have locales specified in their definition. To map logical attributes which are localizable, use locale suffix for each locale. For example attribute name in dimension category has two locales: Slovak (sk) and English (en). Or for example product category can be in English, Slovak or German. It is specified in the model like this:

attributes = [
    {
        "name" = "category",
        "locales" = ["en", "sk", "de"]
    }
]

During the mapping process, localized logical reference is created first:

In short: if attribute is localizable and locale is requested, then locale suffix is added. If no such localization exists then default locale is used. Nothing happens to non-localizable attributes.

For such attribute, three columns should exist in the physical model. There are two ways how the columns should be named. They should have attribute name with locale suffix such as category_sk and category_en (_underscore_ because it is more common in table column names), if implicit mapping is used. You can name the columns as you like, but you have to provide explicit mapping in the mapping dictionary. The key for the localized logical attribute should have .locale suffix, such as product.category.sk for Slovak version of category attribute of dimension product. Here the _dot_ is used because dots separate logical reference parts.

Read more about Localization.

Mapping Process Summary¶

Following diagram describes how the mapping of logical to physical attributes is done in the star SQL browser (see cubes.backends.sql.StarBrowser):

logical to physical attribute mapping

The “red path” shows the most common scenario where defaults are used.

Join Description¶

Joins are defined as an ordered list (order is important) for every cube separately. The join description consists of reference to the master table and a table with details. Fact table is example of master table, dimension is example of a detail table (in a star schema).

The join specification is very simple, you define column reference for both: master and detail. The table reference is in the form table.`column`:

"joins" = [
    {
        "master": "fact_sales.product_key",
        "detail": "dim_product.key"
    }
]

As in mappings, if you have specific needs for explicitly mentioning database schema or any other reason where table.column reference is not enough, you might write:

"joins" = [
    {
        "master": "fact_sales.product_id",
        "detail": {
            "schema": "sales",
            "table": "dim_products",
            "column": "id"
        }
]

To specify a compound join key, the column value of a join specified as a dictionary can be an array denoting multiple keys. The above join would be specified as:

{
    "master": {
        "table": "fact_table",
        "column": ["dimension_id", "partition"]
    },
    "detail": {
        "table": "dimension",
        "column": ["id", "partition"]
    }
}

This will generate the following join:

FROM fact_table
INNER JOIN fact_table ON (
   fact_table.dimension_id = dimension_table.id
  AND fact_table.partition = dimension.partition
)

Join Order¶

Order of joins has to be from the master tables towards the details.

Aliases¶

What if you need to join same table twice or more times? For example, you have list of organizations and you want to use it as both: supplier and service consumer.

It can be done by specifying alias in the joins:

"joins" = [
    {
        "master": "contracts.supplier_id",
        "detail": "organisations.id",
        "alias": "suppliers"
    },
    {
        "master": "contracts.consumer_id",
        "detail": "organisations.id",
        "alias": "consumers"
    }
]

Note that with aliases, in the mappings you refer to the table by alias specified in the joins, not by real table name. So after aliasing tables with previous join specification, the mapping should look like:

"mappings": {
    "supplier.name": "suppliers.org_name",
    "consumer.name": "consumers.org_name"
}

For example, we have a fact table named fact_contracts and dimension table with categories named dm_categories. To join them we define following join specification:

"joins" = [
    {
        "master": "fact_contracts.category_id",
        "detail": "dm_categories.id"
     }
]

Join Methods and Outer Joins¶

(advanced topic)

Cubes supports three join methods match, detail and master.

match (default) – the keys from both master and detail tables have to match – INNER JOIN

master – the master might contain more keys than the detail, for example the fact table (as a master) might contain unknown or new dimension entries not in the dimension table yet. This is also known as LEFT OUTER JOIN.

detail – every member of the detail table will be always present. For example every date from a date dimension table. Alskoknown as RIGHT OUTER JOIN.

To join a date dimension table so that every date will be present in the output reports, regardless whether there are any facts or not for given date dimension member:

"joins" = [
    {
        "master": "fact_contracts.contract_date_id",
        "detail": "dim_date.id",
        "method": "detail"
     }
]

The detail Method and its Limitations¶

(advanced topic)

When at least one table is joined using the outer detail method during aggregation, the statement is composed from two nested statements or two join zones: master fact and outer detail.

Aggregate statement composition

The query builder analyses the schema and assigns a relationship of a table towards the fact. If a table is joined as detail or is behind a detail join it is considered to have a detail relationship towards the fact. Otherwise it has master/match relationship.

When this composed setting is used, then:

• aggregate functions are wrapped using COALESCE() to always return non-NULL values
• count aggregates are changed to count non-empty facts instead of all rows

Note

There should be no cut (path) that has some attributes in tables joined as master and others in a table joined as detail. Every cut (all the cut’s attributes) should fall into one of the two table zones: either the master or the outer detail. There might be cuts from different join zones, though.

Take this into account when designing the dimension hierarchies.

Named Join Templates¶

If multiple cubes share the same kinds of joins, for example with a dimension table, it is possible to define such joins at the model level. They will be considered as templates:

"joins": [
    { "name": "date", "detail": "dim_date.id" },
    { "name": "company", "detail": "dim_company.id" }
]

Then use the join in a cube:

"cubes": [
    {
        "name": "events",
        "joins": [
            { "name": "date", "master": "event_date_id" },
            { "name": "company", "master": "company_id" }
        ]
    }
]

Any property defined in the cube join will replace the model join template. You can also use the same named join multiple times in a cube, just give it different alias:

"cubes": [
    {
        "name": "contracts",
        "joins": [
            {
                "name": "date",
                "master": "contract_start_date_id",
                "alias": "dim_contract_start"
            },
            {
                "name": "date",
                "master": "contract_end_date_id",
                "alias": "dim_contract_end"
            }
        ]
    }
]

Model¶

Slicer backend generates the model on-the-fly from the source server. You have to specify that the provider is slicer:

{
    "provider": "slicer"
}

For more than one slicer, create one file per source Slicer server and specify the data store:

{
    "provider": "slicer",
    "store": "slicer_2"
}

Version¶

Request: GET /version

Return a server version.

{
    "version": "1.0"
}

Info¶

Request: GET /info

Return an information about the server and server’s data.

Content related keys:

• label – server’s name or label
• description – description of the served data
• copyright – copyright of the data, if any
• license – data license
• maintainer – name of the data maintainer, might be in format Name Surname <namesurname@domain.org>
• contributors - list of contributors
• keywords – list of keywords that describe the data
• related – list of related or “friendly” Slicer servers with other open data – a dictionary with keys label and url.
• visualizers – list of links to prepared visualisations of the server’s data – a dictionary with keys label and url.

Server related keys:

• authentication – authentication method, might be none, pass_parameter, http_basic_proxy or other. See Authorization and Authentication for more information
• json_record_limit - maximum number of records yielded for JSON responses
• cubes_version – Cubes framework version

Example:

{
    "description": "Some Open Data",
    "license": "Public Domain",
    "keywords": ["budget", "financial"],
    "authentication": "none",
    "json_record_limit": 1000,
    "cubes_version": "0.11.2"
}

List of Cubes¶

Request: GET /cubes

Get list of basic informatiob about served cubes. The cube description dictionaries contain keys: name, label, description and category.

[
    {
        "name": "contracts",
        "label": "Contracts",
        "description": "...",
        "category": "..."
    }
]

Cube Model¶

Request: GET /cube/<name>/model

Get model of a cube name. Returned structure is a dictionary with keys:

• name – cube name – used as server-wide cube identifier

• label – human readable name of the cube – to be displayed to the users (localized)

• description – optional textual cube description (localized)

• dimensions – list of dimension description dictionaries (see below)

• aggregates – list of measures aggregates (mostly computed values) that

  can be computed. Each item is a dictionary.

• measures – list of measure attributes (properties of facts). Each

  item is a dictionary. Example of a measure is: amount, price.

• details – list of attributes that contain fact details. Those attributes are provided only when getting a fact or a list of facts.

• info – a dictionary with additional metadata that can be used in the

  front-end. The contents of this dictionary is defined by the model creator and interpretation of values is left to the consumer.

• features (advanced) – a dictionary with features of the browser, such as available actions for the cube (“is fact listing possible?”)

Aggregate is the key numerical property of the cube from reporting perspective. It is described as a dictionary with keys:

• name – aggregate identifier, such as: amount_sum, price_avg, total, record_count
• label – human readable label to be displayed (localized)
• measure – measure the aggregate is derived from, if it exists or it is known. Might be empty.
• function - name of an aggregate function applied to the measure, if known. For example: sum, min, max.
• window_size – number of elements within a window for window functions such as moving average
• info – additional custom information (unspecified)

Aggregate names are used in the aggregates parameter of the /aggregate request.

Measure dictionary contains:

• name – measure identifier
• label – human readable name to be displayed (localized)
• aggregates – list of aggregate functions that are provided for this measure
• window_size – number of elements within a window for window functions such as moving average
• info – additional custom information (unspecified)

See more information about measures and aggregates in the /aggregate request description.

Example cube:

{
    "name": "contracts",
    "info": {},
    "label": "Contracts",
    "aggregates": [
        {
            "name": "amount_sum",
            "label": "Amount sum",
            "info": {},
            "function": "sum"
        },
        {
            "name": "record_count",
            "label": "Record count",
            "info": {},
            "function": "count"
        }
    ],

    "measures": [
        {
            "name": "amount",
            "label": "Amount",
            "info": {},
            "aggregates": [ "sum" ]
        }
    ],

    "details": [...],

    "dimensions": [...]
}

The dimension description dictionary:

• name – dimension identifier
• label – human readable dimension name (localized)
• is_flat – True if the dimension has only one level, otherwise False
• has_details – True if the dimension has more than one attribute
• default_hierarchy_name - name of default dimension hierarchy
• levels – list of level descriptions
• hierarchies – list of dimension hierarchies
• info – additional custom information (unspecified)
• cardinality – dimension cardinality
• role – dimension role (special treatment, for example time)
• category – dimension category

The level description:

• name – level identifier (within dimension context)
• label – human readable level name (localized)
• attributes – list of level’s attributes
• key – name of level’s key attribute (mostly the first attribute)
• label_attribute – name of an attribute that contains label for the level’s members (mostly the second attribute, if present)
• order_attribute – name of an attribute that the level should be ordered by (optional)
• order – order direction asc, desc or none.
• cardinality – symbolic approximation of the number of level’s members
• role – level role (special treatment)
• info – additional custom information (unspecified)

Cardinality values and their meaning:

• tiny – few values, each value can have it’s representation on the screen, recommended: up to 5.
• low – can be used in a list UI element, recommended 5 to 50 (if sorted)
• medium – UI element is a search/text field, recommended for more than 50 elements
• high – backends might refuse to yield results without explicit pagination or cut through this level.

Cells and Cuts¶

The cell - part of the cube we are aggregating or we are interested in - is specified by cuts. The cut in URL are given as single parameter cut which has following format:

Examples:

date:2004
date:2004,1
date:2004,1|class:5
date:2004,1,1|category:5,10,12|class:5

To specify a range where keys are sortable:

date:2004-2005
date:2004,1-2005,5

Open range:

date:2004,1,1-
date:-2005,5,10

Set cuts:

date:2005;2007

Dimension name is followed by colon :, each dimension cut is separated by |, and path for dimension levels is separated by a comma ,. Set cuts are separated by semicolons ;.

To specify other than default hierarchy use format dimension@hierarchy, the path then should contain values for specified hierarchy levels:

date@ywd:2004,25

Following image contains examples of cuts in URLs and how they change by browsing cube aggregates:

Example of how cuts in URL work and how they should be used in application view templates.

Aggregate¶

Request: GET /cube/<cube>/aggregate

Return aggregation result as JSON. The result will contain keys: summary and drilldown. The summary contains one row and represents aggregation of whole cell specified in the cut. The drilldown contains rows for each value of drilled-down dimension.

If no arguments are given, then whole cube is aggregated.

Parameters:

• cut - specification of cell, for example: cut=date:2004,1|category:2|entity:12345
• drilldown - dimension to be drilled down. For example drilldown=date will give rows for each value of next level of dimension date. You can explicitly specify level to drill down in form: dimension:level, such as: drilldown=date:month. To specify a hierarchy use dimension@hierarchy as in drilldown=date@ywd for implicit level or drilldown=date@ywd:week to explicitly specify level.
• aggregates – list of aggregates to be computed, separated by |, for example: aggergates=amount_sum|discount_avg|count
• measures – list of measures for which their respecive aggregates will be computed (see below). Separated by |, for example: aggergates=proce|discount
• page - page number for paginated results
• pagesize - size of a page for paginated results
• order - list of attributes to be ordered by
• split – split cell, same syntax as the cut, defines virtual binary (flag) dimension that inticates whether a cell belongs to the split cut (true) or not (false). The dimension attribute is called __within_split__. Consult the backend you are using for more information, whether this feature is supported or not.

Response:

A dictionary with keys:

• summary - dictionary of fields/values for summary aggregation
• cells - list of drilled-down cells with aggregated results
• total_cell_count - number of total cells in drilldown (after limit, before pagination). This value might not be present if it is disabled for computation on the server side.
• aggregates – list of aggregate names that were considered in the aggragation query
• cell - list of dictionaries describing the cell cuts
• levels – a dictionary where keys are dimension names and values is a list of levels the dimension was drilled-down to

Example for request /aggregate?drilldown=date&cut=item:a:

{
    "summary": {
        "count": 32,
        "amount_sum": 558430
    }
    "cells": [
        {
            "count": 16,
            "amount_sum": 275420,
            "date.year": 2009
        },
        {
            "count": 16,
            "amount_sum": 283010,
            "date.year": 2010
        }
    ],
    "aggregates": [
        "amount_sum",
        "count"
    ],
    "total_cell_count": 2,
    "cell": [
        {
            "path": [ "a" ],
            "type": "point",
            "dimension": "item",
            "invert": false,
            "level_depth": 1
        }
    ],
    "levels": { "date": [ "year" ] }
}

If pagination is used, then drilldown will not contain more than pagesize cells.

Note that not all backengs might implement total_cell_count or providing this information can be configurable therefore might be disabled (for example for performance reasons).

Facts¶

Request: GET /cube/<cube>/facts

Return all facts within a cell.

Parameters:

• cut - see /aggregate
• page, pagesize - paginate results
• order - order results
• format - result format: json (default; see note below), csv or json_lines.
• fields - comma separated list of fact fields, by default all fields are returned
• header – specify what kind of headers should be present in the csv output: names – raw field names (default), labels – human readable labels or none

The JSON response is a list of dictionaries where keys are attribute references (ref property of an attribute).

To use JSON formatted repsonse but don’t have the record limit json_lines format can be used. The result is one fact record in JSON format per line – JSON dictionaries separated by newline n character.

Single Fact¶

Request: GET /cube/<cube>/fact/<id>

Get single fact with specified id. For example: /fact/1024.

The response is a dictionary where keys are attribute references (ref property of an attribute).

Dimension members¶

Request: GET /cube/<cube>/members/<dimension>

Get dimension members used in cube.

Parameters:

• cut - see /aggregate

• depth - specify depth (number of levels) to retrieve. If not

  specified, then all levels are returned. Use this or level.

• level - deepest level to be retrieved – use this or depth.

• hierarchy – name of hierarchy to be considered, if not specified, then

  dimension’s default hierarchy is used

• page, pagesize - paginate results

• order - order results

Response: dictionary with keys dimension – dimension name, depth – level depth and data – list of records.

Example for /cube/facts/members/item?depth=1:

{
    "dimension": "item"
    "depth": 1,
    "hierarchy": "default",
    "data": [
        {
            "item.category": "a",
            "item.category_label": "Assets"
        },
        {
            "item.category": "e",
            "item.category_label": "Equity"
        },
        {
            "item.category": "l",
            "item.category_label": "Liabilities"
        }
    ],
}

Cell¶

Get details for a cell.

Request: GET /cube/<cube>/cell

Parameters:

• cut - see /aggregate

Response: a dictionary representation of a cell (see cubes.Cell.as_dict()) with keys cube and cuts. cube is cube name and cuts is a list of dictionary representations of cuts.

Each cut is represented as:

{
    // Cut type is one of: "point", "range" or "set"
    "type": cut_type,

    "dimension": cut_dimension_name,
    "level_depth": maximal_depth_of_the_cut,

    // Cut type specific keys.

    // Point cut:
    "path": [ ... ],
    "details": [ ... ]

    // Range cut:
    "from": [ ... ],
    "to": [ ... ],
    "details": { "from": [...], "to": [...] }

    // Set cut:
    "paths": [ [...], [...], ... ],
    "details": [ [...], [...], ... ]
}

Each element of the details path contains dimension attributes for the corresponding level. In addition in contains two more keys: _key and _label which (redundantly) contain values of key attribute and label attribute respectively.

Example for /cell?cut=item:a in the hello_world example:

{
    "cube": "irbd_balance",
    "cuts": [
        {
            "type": "point",
            "dimension": "item",
            "level_depth": 1
            "path": ["a"],
            "details": [
                {
                    "item.category": "a",
                    "item.category_label": "Assets",
                    "_key": "a",
                    "_label": "Assets"
                }
            ],
        }
    ]
}

Report¶

Request: GET /cube/<cube>/report

Process multiple request within one API call. The data should be a JSON containing report specification where keys are names of queries and values are dictionaries describing the queries.

report expects Content-type header to be set to application/json.

See Report for more information.

Search¶

Request: GET /cube/<cube>/search/dimension/<dimension>/<query>

Search values of dimensions for query. If dimension is _all then all dimensions are searched. Returns search results as list of dictionaries with attributes:

Parameters that can be used in any request:

• prettyprint - if set to true, space indentation is added to the JSON output

Roll-up¶

Report queries might contain rollup specification which will result in “rolling-up” one or more dimensions to desired level. This functionality is provided for cases when you would like to report at higher level of aggregation than the cell you provided is in. It works in similar way as drill down in /aggregate but in the opposite direction (it is like cd .. in a UNIX shell).

Example: You are reporting for year 2010, but you want to have a bar chart with all years. You specify rollup:

...
"rollup": "date",
...

Roll-up can be:

• a string - single dimension to be rolled up one level
• an array - list of dimension names to be rolled-up one level
• a dictionary where keys are dimension names and values are levels to be rolled up-to

Local Server¶

To run your local server, prepare server Configuration and run the server using the Slicer tool (see slicer - Command Line Tool):

slicer serve slicer.ini

Server requests¶

Example server request to get aggregate for whole cube:

$ curl http://localhost:5000/cube/procurements/aggregate?cut=date:2004

Reply:

{
    "drilldown": {},
    "summary": {
        "received_amount_sum": 399136450.0,
        "requested_amount_sum": 2394804837.56,
        "record_count": 4390
    }
}