Custom schema definition

Private eazyBI

Overview

Private eazyBI can be integrated with customer specific databases or applications. Private eazyBI supports MySQL, PostgreSQL, MS SQL Server and Oracle database as data source. To be able to use eazyBI you need to have source data organized in star schema dimension and fact tables. Then on top of your star schema you will need to define eazyBI multidimensional schema (dimensions and measures) using mondrian-olap library. It requires some basic Ruby programming language knowledge as it is using Ruby syntax to define schema. After that you will be able to start to use eazyBI with your data and build reports, charts and dashboards.

Mondrian OLAP schema

eazyBI uses Mondrian OLAP Java library as a multi-dimensional query engine. This engine is embedded in mondrian-olap JRuby library and eazyBI uses mondrian-olap library for multi-dimensional schema definition and MDX query generation.

Read Mondrian OLAP schema documentation to learn about schema design principles and examples. Mondrian schema is defined as XML file but eazyBI uses mondrian-olap Ruby syntax to generate XML schema file. See mondrian-olap schema definition unit tests to see available mondrian-olap methods and what resulting Mondrian XML schema they generate.

eazybi.toml configuration

After defining custom schema with mondrian-olap you need to configure which eazyBI accounts should use this custom schema. See example in eazybi.toml.sample for this configuration:

# Settings for accounts with custom schemas
[accounts."FoodMart custom"]
# Specify driver if different than main database connection
# driver = "postgresql"
database = "foodmart"
# Specify username and password if different than for main database connection
# username = "foodmart"
# password = "foodmart"
# Specify relative path to custom schema definition file
schema_rb = "schemas/foodmart_schema.rb"
# OLAP schema cache timeout in minutes
# cache_timeout = 10

In accounts section use either account name or account ID (that you can see it in URLs after /accounts/) to identify account. Then specify database connection using driver, database, username and password settings. Then specify relative path (from eazybi_private/config directory) to custom mondrian-olap schema definition file.

Mondrian OLAP will cache query results in memory. If your data in database dimension and fact tables are changing then you want to ensure that Mondrian OLAP cache is cleared. There are two options to achive that:

• Specify cache_timeout setting and then after this timeout results cache will be cleared and Mondrian schema will be reloaded.
• Change last modified date of mondrian-olap schema definition file
  (e.g. on Unix based system for provided example do touch schemas/foodmart_schema.rb).
  On each request eazyBI will check last modified date of mondrian-olap schema definition file and if it changed then it will force clearing of Mondrian cache and reloading of mondrian-olap schema definition.

Recommendations for custom schema design

Enable drill through for custom schema

You can enable drill through action in report results cell actions (when you click on results table cell or chart point or area) – it will show detailed fact table rows from which corresponding cell value is calculated.

When defining custom schema you need to provide cube annotations which includes drill through configuration options. See config/schemas/foodmart_schema.rb (MySQL backup for the foodmart database that is used in examples can be downloaded here) example configuration:

cube 'Sales' do
  # ...
  annotations enable_drill_through: true,
    drill_through_return: [
      "[Time].[Year]", "[Time].[Month]", "[Time].[Day]",
      "[Customers].[Country]", "[Customers].[State Province]", "[Customers].[City]", "[Customers].[Name]",
      "[Products].[Product Family]", "[Products].[Brand Name]", "[Products].[Product Name]"
    ].join(","),
    drill_through_default_measures: [
	  "[Measures].[Unit Sales]",
      "[Measures].[Store Sales]",
      "[Measures].[Store Cost]"
    ].join(",")

• enable_drill_through: true will enable drill through for all cube reports
• If optional drill_through_return is provided (either as array or comma separated values string) then specified fields will be displayed in returned drill through results. Either dimension levels or measures can be included in this list, result sorting will be done by first fields. If return field list is not specified then by default all fields from cube definition will be shown in their schema definition order.
• If optional drill_through_default_measures list is specified then it will be used when drill through is done from cell containing calculated measure. When drill through is done from normal (non-calculated) measure cell then only this measure will be shown in drill through results.

Enable drill through by

If you want to define different sets of return fields for drill through cell actions then it is recommended to define the drill_through_by annotation instead of the previously described common drill_through_return annotation.

At first, define a drill_through_by cube annotation in the custom schema definition. You can specify multiple drill_through_by configurations. See example:

cube 'Sales' do
  # ...
  annotations drill_through_by: {
    'sales' => {
      display_name: 'sales',
      drill_through_return: [
        "[Time].[Year]", "[Time].[Month]", "[Time].[Day]",
        "[Customers].[Country]", "[Customers].[State Province]", "[Customers].[City]", "[Customers].[Name]",
        "[Products].[Product Family]", "[Products].[Brand Name]", "[Products].[Product Name]"
      ],
      group_by: true
    }
  }.to_json

• Define the list of returned fields in drill_through_return – it can include dimension levels, measures, as well as dimension member properties "Property(dimension_level,'property_name')" or dimension member names (instead of keys) "Name(dimension_level)"
• If the optional display_name is specified, then the drill through cell action will be displayed as Drill through display_name instead of Drill through cell.
• If the optional group_by is specified, then returned results will be grouped by all fields (except measures) specified in drill_through_return.
  

Then add the drill_through_by annotation for specific measures for which you would like to use these return fields.

cube 'Sales' do
  # ...
  measure 'Store Sales', column: 'store_sales', aggregator: 'sum', annotations: {
    drill_through_by: "sales"
  }

Enable drill through dimension levels

Instead of drill through to detailed fact table rows you can add drill through to selected dimension levels. Results will be the same as when selecting Drill across dimension level action in a table report but in this case results will open in a new popup. Drill through dimension level is useful with large detailed dimensions (like customers or issues or transactions).

In a custom schema definition add a drill_through_dimension_levels cube annotation with one or several dimension levels. By default in the user interface level name will be shown in a popup. If necessary you can override name in the popup with a drill_through_dimension_levels_display_names annotation. See example:

cube 'Sales' do
  # one level
  annotations drill_through_dimension_levels: "[Customers].[Name]"
  # or several levels
  annotations drill_through_dimension_levels: "[Customers].[Name],[Products].[Product Name]"
  # or specify also display names in the popup
  annotations drill_through_dimension_levels: "[Customers].[Name],[Products].[Product Name]",
    drill_through_dimension_levels_display_names: "Customer,Product"

Default non empty options for dimensions

If you have a large dimension then you can specify that when the dimension is moved to rows or columns then by default

• either Nonempty option will selected in the Rows section (default_nonempty_crossjoin option)
• or Hide empty rows or columns option will be selected (default_hide_empty option).

These options for large dimensions can help to avoid creation of too large result sets (which can result in timeout errors) in ad-hoc reports.

You can add these options as annotations for a dimension in a custom schema:

dimension 'Customers', foreign_key: 'customer_id' do
  # ...
  annotations default_nonempty_crossjoin: true, default_hide_empty: true

Enable "Go to source" action for level members

You can define for dimension level source data page URL that will be added as Go to source link in dimension member actions popup (which is shown when you click on dimension member in report results).

In custom schema definition you need to add source_url annotation and URL_ID property for this dimension. See example:

dimension 'Customers', foreign_key: 'customer_id' do
  hierarchy has_all: true, all_member_name: 'All Customers', primary_key: 'customer_id' do
    table 'customer'
    level 'Name', column: 'fullname', unique_members: true do
      property 'URL_ID', column: 'customer_id'
      annotations source_url: 'http://example.com/customers/{{id}}'
    end
  end
end

This definition for each dimension member will generate Go to source link with URL http://example.com/customers/{{id}} where {{id}} will be replaced with URL_ID property value (from column customer_id).

In addition if you need to substitute parent and child object IDs in generated URL you can use URL_ID and URL_SUB_ID properties, see example:

dimension 'Customers', foreign_key: 'customer_id' do
  hierarchy has_all: true, all_member_name: 'All Customers', primary_key: 'customer_id' do
    table 'customer'
    level 'Name', column: 'fullname', unique_members: true do
      property 'URL_ID', column: 'account_id'
      property 'URL_SUB_ID', column: 'customer_id'
      annotations source_url: 'http://example.com/accounts/{{id}}/customers/{{sub_id}}'
    end
  end
end

Dimension group annotation

If you have many dimensions in the cube and you want to hide some dimensions by default in the Analyze tab report designer, then you can add a group annotation for these dimension:

dimension 'Customers', foreign_key: 'customer_id' do
  # ...
  annotations group: "Other"

All dimensions with a group annotation will be sorted alphabetically in this group and can be shown or hidden when clicking the corresponding link.