Aggregation Browsing Backends¶
Backends for browsing aggregates of various data sources
SQL backend uses SQLAlchemy for generating queries. It supports all databases that the SQLAlchemy supports such as:
- Microsoft SQL Server
- 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.
- 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).
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.