Introduction

OmniFi Data Mart is a platform and a framework that makes it possible to quickly and easily implement a custom Data Mart solution, i.e. a fully customized data repository for time-series and data analysis. In contrast to developing a data mart solution in-house, OmniFi Data Mart provides packaged functionality for designing the database models and deploying to an MSSQL database, as well as built-in ETL pipeline functionality.

OmniFi Data Mart makes no assumptions on the content of the data mart, and you are not limited to any pre-defined data model. The datamart can be used as a final resting place of data or as an intermediary step to an external data warehouse such as Snowflake.

Models and tables

Data models encapsulate all configuration related to a fact or dimension table in the mart database, including data source, parameters to the data source function, transformations, column configuration, table indexing etc.

Each definition of a data model is based on an OmniFi report. This allows you to rapidly envision, design and prototype the content of the data mart directly in Excel. Uploading the report workbook to the web portal, a data mart model is derived from the report, defining parameters, column formats, as well as an over-all principle of the model.

The model principles Time Series, Slowly Changing or Incremental define the basic anatomy of a model. Time Series is us intuitively used to model a time series fact table and requires e.g. a column to be designated as Figure Date (i.e. a column that identifies the time slice of the series). The principle also affects the deployed table and the behavior of the ETL pipeline. Time Series and Slowly Changing models produce version-handled tables that data is merged into, whereas Incremental models are note version handled and data is appended to the table using standard inserts.

The table entity represents a concrete table in the mart database. Creating a table in the OmniFi Web interface automatically deploys the table into the mart database based on the model specifications, and the table is automatically upgraded when the model changes.

OmniFi Data Mart supports working with any number of different database schemas within the same database, allowing separation between production tables from work-in-progress, or different usage areas or business departments. Deciding to use e.g. a Prod schema and a separate Test schema for testing allows an iterative approach to designing your data mart model.

OmniFi Data Mart is configured in OmniFi Web, using the Data Mart menu in OmniFi Web.

Models are created from reports on the Models page:

Models are published to data mart tables using the Tables page:

There are also technical Data Mart settings that can be configured in the OmniFi Web Administration. See Administration for more details.

Mart database

The database schema produced by OmniFi Data Mart is fully denormalized, without relational constraints. Any two tables involved in a relational constraint are logically dependent and consistency is enforced when loading data, affecting the order in which new data can be loaded. OmniFi Data Mart uses decoupled models that can be loaded concurrently and in any order without restrictions.

The Time Series and Slowly Changing principles implement version handling using the Temporal Table feature available in MSSQL Server 2016 and later, which corresponds to the Slowly Changing Dimension Type 4.

ETL pipeline

The Extract Transform Load pipeline is executed using the Scheduling facility in OmniFi Web. The OmniFi report makes up the extract and transform stages, and a separate process loads the data first into a separate staging table in the data mart. The data is then copied or merged into the published data mart table depending on the configured principle.

Infrastructure

An MSSQL Server 2016 database is required for use as the mart database. The mart database itself is not a part of, nor provided by OmniFi. This allows flexibility in deploying the mart database in a separate instance, in a SQL Server farm, in a cloud environment and also an existing database.

In the latter case, OmniFi Data Mart co-exists with other solutions in a larger context, providing e.g. Wallstreet Suite data to a larger data warehouse solution.

OmniFi Data Mart are fully integrated with OmniFi Web. All user interaction, configuration and monitoring are done using the web portal user interface.

The ETL pipeline is executed in OmniFi Web Backend and loaded into the mart database.

An existing instance of OmniFi Web can be configured to provide the data mart functionality, however depending on the current usage of reports and OmniFi Access API on that instance and the expected load from the ETL pipeline, it can be beneficial to deploy the data mart functionality as a separate instance to allow the various functional areas to operate independently, each with optimal performance.

Technical Requirements