In Business Intelligence & Analytics
Introduction to Cubes in Business Intelligence - read the full article about business intelligence, Business Intelligence & Analytics and Data analytics and consulting from InterSystems Learning Services on Qualified.One
Youtube Blogger
Database and data management vendors have worked for decades to increase the speed of analytics and reporting.
To understand cubes, it helps to step back in history and look at the relational database.
Relational databases were used for the first time in the 1970s to organize data.
To process its database queries, we need to use Structured Query Language, or SQL.
SQL REFERS TO TWO DIMENSIONS: columns and rows.
Because SQL was designed to handle two-dimensional tabular data, the terms column and row have meaning in SQL syntax.
InterSystems IRIS SQL is fast and can quickly get results, even from multiple tables.
However, the SQL developer needs to create the right indices, tune the tables, and craft the SQL queries in a way that will generate the fastest query plan.
When you design a cube, you are creating a data structure that allows for even faster data analysis.
Cubes optimize the most common access to the data and make it available for analysis by creating measures and dimensions instead of rows and columns.
InterSystems IRIS BI uses a query language called Multidimensional Expressions, or MDX, which uses a database management system.
The MDX query language is a calculation language with syntax similar to spreadsheet formulas, and it can process multiple dimensions in queries.
MDX is used for online analytical processing cubes, or OLAP cubes.
InterSystems IRIS BI uses OLAP cubes containing measures, which can be accessed at the detail dimension member level or aggregated at higher dimension levels to provide fast summary data.
Lets take a closer look at the elements that comprise a cube, measures and dimensions.
Measures are numerical values contained in the cubes, and dimensions are the structures that categorize measures and make it easier to answer business questions.
Lets consider a bank as an example.
A bank might include dimensions such as people, products, locations, and time to explain behavior within its branches.
Products might include credit cards and checking or savings accounts.
The time of opening or closing an account could be defined using months, weeks, or years.
Many dimensions contain a hierarchy, or levels of attributes that support drilling up and down.
For example, a dimension of Account Open Date could include a hierarchy of Year, Quarter, Month, and Days, allowing users to easily identify patterns.
Dimensions allow you to define ways to see, order, or filter data.
In addition to dimensions, cubes contain measures, which are specific pieces of data.
Measures tracked by a bank might include activities like the number of new accounts, and numerical information such as loan amounts.
In general, a measure is a property on which you can make calculations: you can compute a sum or average, for example.
A measure can come from the data sources or be calculated based on other measures or source expressions.
Examples of measures in the banking system are savings account balances and the number of accounts in the bank.
A fee can be calculated as a percentage of a balance.
Here, the value 1500 is a measure representing the number of credit cards sent to bank customers.
The dimension here is March, when the cards were delivered.
This example with a single measure and dimension is a bidimensional example used for simplicity; standard models include many more dimensions.
For example, the bank can add other dimensions like branch, country, or credit card type.
InterSystems IRIS BI creates physical cubes, allowing you to design your models and keep them up to date in a schedule that fits your business needs.
Other solutions, like InterSystems IRIS Adaptive Analytics, use virtual cubes that can be redesigned and deployed quickly while keeping the data in one place.
Virtual cubes are beneficial when a data model changes frequently.
Now that you understand the basics of cubes--including measures and dimensions--and the query languages of SQL and MDX, find out more about how to manage InterSystems IRIS BI performance on the InterSystems documentation website, docs.intersystems.com.
You can also get started with creating cubes by watching the InterSystems IRIS BI: Architect video series-- and see an overview of InterSystems IRIS BI-- by going to the InterSystems Learning website, learning.intersytems.com.
InterSystems Learning Services: Introduction to Cubes in Business Intelligence - Business Intelligence & Analytics