Analytical databases (OLAP)
Analytical databases (OLAP) come in a number of styles but they share three common attributes:
- designed and optimised for efficient querying
- intuitive data model for ad hoc analysis
- simpler and more powerful expression language for defining typical analytical calculations
Analytical databases provide faster and more flexible query analysis than can be achieved through an RDBMS with equivalent hardware. They are able to do this because the data model is not concerned with supporting the ongoing maintenance and integrity of the data. Analytical databases are one of the most exciting areas of BI with the latest products able to consume huge quantities of data whilst maintaining very fast query response times. For this reason, they are well suited for ad hoc query or interactive dashboards and reports.
Analytical Database Tools
CMBI have deep expertise in creating analytical databases. There are a number of very good technologies each with their own strengths but they all share very similar properties from a development perspective. We provide design and development expertise in the tools below. If you have not already made your selection we can help with creating an assessment criteria for your business needs.
- Analysis Services 2005-2012 (SSAS)
- Power Pivot and Analysis Services Tabular 2012
- Cognos TM1
- Qlikview
- Tableau
Data modelling
The logical data model for an analytical database typically stores more information (metadata) about the data than a relational model. For instance, it may store the display order for a series of attributes. A simple example is the days of the week. Rather than displaying them in alphabetical order, we may specify the sort order as Monday, Tuesday, Wednesday…Sunday. This additional information increases the usability of the data model for ad hoc analysis.
Many databases use a multidimensional data model comprised of dimensions (representing things like customers and products) and fact tables (representing transactions and events like sales). Some data models make no logical distinction between master data and transaction data, and allow slice and dice on any attribute in the database.
Rich metadata in your analytical database is essential for presenting an intuitive interface to users. CMBI have many years experience in user facing data models for a whole range of industries and business functions including credit and market risk, law firm practice management, budgeting and forecasting, profitability, general ledger, sales, human resources, fraud and many more.
Physical data model and performance
Most analytical databases store at least part of the data in memory; compile and manage frequently requested queries and data aggregations; and implement a highly compressed and indexed physical data model. These factors all contribute to the exceptional query response times that analytical databases provide.
If you are having performance problems with an existing database, Contact CMBI for a 1 day performance healthcheck.
Query language
CMBI can provide development, training and support in the most popular query and expression languages for analytical databases including
- MDX, an industry standard language used by SSAS, TM1, Hyperion, Tableau and many more
- DAX, the expression language used by Power Pivot and AS Tabular
- TM1 rules, including Feeder optimisation