Aggregation Browsing Backends

Backends for browsing aggregates of various data sources

SQL

SQL backend uses SQLAlchemy for generating queries. It supports all databases that the SQLAlchemy supports such as:

  • Drizzle
  • Firebird
  • Informix
  • Microsoft SQL Server
  • MySQL
  • Oracle
  • PostgreSQL
  • SQLite
  • Sybase

Browser

class cubes.backends.sql.mapper.SnowflakeMapper(cube, mappings=None, locale=None, schema=None, fact_name=None, dimension_prefix=None, dimension_suffix=None, joins=None, dimension_schema=None, **options)

A snowflake schema mapper for a cube. The mapper creates required joins, resolves table names and maps logical references to tables and respective columns.

Attributes:

  • cube - mapped cube
  • mappings – dictionary containing mappings
  • simplify_dimension_references – references for flat dimensions (with one level and no details) will be just dimension names, no attribute name. Might be useful when using single-table schema, for example, with couple of one-column dimensions.
  • dimension_prefix – default prefix of dimension tables, if default table name is used in physical reference construction
  • dimension_suffix – default suffix of dimension tables, if default table name is used in physical reference construction
  • fact_name – fact name, if not specified then cube.name is used
  • schema – default database schema
  • dimension_schema – schema whre dimension tables are stored (if different than fact table schema)

mappings is a dictionary where keys are logical attribute references and values are table column references. The keys are mostly in the form:

  • attribute for measures and fact details
  • attribute.locale for localized fact details
  • dimension.attribute for dimension attributes
  • dimension.attribute.locale for localized dimension attributes

The values might be specified as strings in the form table.column (covering most of the cases) or as a dictionary with keys schema, table and column for more customized references.

physical(attribute, locale=None)

Returns physical reference as tuple for attribute, which should be an instance of cubes.model.Attribute. If there is no dimension specified in attribute, then fact table is assumed. The returned tuple has structure: (schema, table, column).

The algorithm to find physicl reference is as follows:

IF localization is requested:
    IF is attribute is localizable:
        IF requested locale is one of attribute locales
            USE requested locale
        ELSE
            USE default attribute locale
    ELSE
        do not localize

IF mappings exist:
    GET string for logical reference
    IF locale:
        append '.' and locale to the logical reference

    IF mapping value exists for localized logical reference
        USE value as reference

IF no mappings OR no mapping was found:
    column name is attribute name

    IF locale:
        append '_' and locale to the column name

    IF dimension specified:
        # Example: 'date.year' -> 'date.year'
        table name is dimension name

        IF there is dimension table prefix
            use the prefix for table name

    ELSE (if no dimension is specified):
        # Example: 'date' -> 'fact.date'
        table name is fact table name
physical_references(attributes, expand_locales=False)

Convert attributes to physical attributes. If expand_locales is True then physical reference for every attribute locale is returned.

relevant_joins(attributes, expand_locales=False)

Get relevant joins to the attributes - list of joins that are required to be able to acces specified attributes. attributes is a list of three element tuples: (schema, table, attribute).

table_map()

Return list of references to all tables. Keys are aliased tables: (schema, aliased_table_name) and values are real tables: (schema, table_name). Included is the fact table and all tables mentioned in joins.

To get list of all physical tables where aliased tablesare included only once:

finder = JoinFinder(cube, joins, fact_name)
tables = set(finder.table_map().keys())
tables_for_attributes(attributes, expand_locales=False)

Returns a list of tables – tuples (schema, table) that contain attributes.

Slicer

Mixpanel

Mongo DB