One click integration: OLAP/RDBMS/Online Service
A requirement that is very often communicated to us in client projects these days is “Data mash up”. The ability to integrate internal and external data sources more effectively and enabling more holistic insights.
One of the key challenges in the setup of analytical application processes is the side by side use of relational databases often referred to as the “data warehouse” and multidimensional data stores.
The data warehouse architecture is typically “data driven“ or in other words it typically involves integrating existing data from different sources. This architecture is suited for flexible analysis and often requires specific technical expertise by the analyst.
The other approach is “concept driven” i.e. enabling the analyst to enter data, conduct what if analyses and to modify structures (business modelling). This mostly involves multi-dimensional (also referred to as OLAP) databases that are optimised for use by non IT specialist business users. Often an “in memory” approach is used in this architecture that enables extremely fast response times when calculating query results across 3-20 dimensions with multiple aggregation hierarchies.
None of the above concepts is “better than the other” the use case typically depends on the requirements at hand and in most cases Managility recommends a combined approach that involves both architectures.
In recent times a third aspect to consider is the integration of commercial web based data services for example provided in the Azure Data Market or by other third party providers. This enables organisations to put their data in “perspective” for example by comparing it to temperature, population or financial markets details.
Historically the “keeping in sync” of these different “data worlds” required extensive development work of ETL (Extraction, Transformation and Loading) processes.
To address the integration challenges Managility has recently developed a new approach that synchronises OLAP and relational sources: The PowerSync framework a solution that we provide to our clients free of charge. PowerSync automatically converts Jedox cubes into a relational dimensional model with fact and dimension tables.
The benefits are much improved audit options (every OLAP transaction is a relational record than can be easily traced) and a comprehensive view on the data. The combined sources can now be analysed with existing data warehouse reporting solutions or any standard BI tool that supports relational access.
In addition Managility Power Sync supports new technologies like PowerPivot, a component that enables analysis of large data volume sources in Excel and the easy integration of external sources. As per the example below the Jedox Sales demo can be easily extended with population information to enable a relative metric measuring sales per population.
At the moment we have used the approach at a few early adoption clients with very good feedback. Please don’t hesitate to contact the Managility team or me for any further questions or to setup a demo with your data.
