Business Intelligence: Hyper Cubing

Relational databases have been functional as life-lines for information

workers for decades now. They deliver results no doubt, but it is also a fact

that it is not easy (if not tough) to query and analyze information stored in

them, on the fly and with respect to multiple metrics. You would run queries for

analysis that would measure one or more KPI (Key Performance Indicators) for

your business and this would be against a particular metric. For example it

would either be sales data for a period of time or in a region or for a product.

How about analyzing the information of say sales figure for a product with

respect to a region over a period of time, or say sales figures for the same

product in a particular outlet of a particular region in a time period. Well as

for just a relational design, you might have to make sure that your sales table

has all these columns. And if they are not there, adding columns and values to

them would be the only solution left.

It is places like these where using OLAP (On Line Analytical Processing) can

give you all the information you want while keeping multiple variables in your

queries. In the previous part of the series, we talked about how data warehouses

can help information access and knowledge discovery, and subsequently your

business analytics.

Applies To: CIOs, CTOs USP: OLAP systems basics, benefits and how to go about implementation Primary Link: www.olap.com Google Keywords: hypercubing, FASMI, OLAP

We also gave a brief overview of implementing a DW using MS SQL Server 2000.

In this part of the series, we will take you through the basic concepts related

to OLAP, how to go about it, what do you need for such an implementation and a

simple demo of how it can be done using the Business Intelligence Development

Studio provided with MS SQL Server 2005.

What is OLAP?



OLAP is a technique that enables you to analyze data with respect to multiple
metrics that are termed as 'Dimensions'. As it is a part of BI

implementation you need not worry about where the data is coming from (source)

or how is it presented (the format). In an OLAP setup, the entire data is

categorized into two types: Dimensions and Measures. Measures represent the data

that are measurable or countable in numeric terms. For example, the number of

units sold, the inventory stock, number of employees, and gallons of oil are all

examples of what can be included as a measure. Dimensions on the other hand are

non-numeric quantities but carry relevance in that they form one of the aspects

with respect to which other factors are to be measured. Examples are sales area

or region, type of product etc.

Multiple dimensions are organized in an OLAP system to give what is called a

'Multidimensional Cube' or simply a 'Cube'. A cube is a data-structure

that stores measures along predefined dimensions. The dimensions of a cube are

further organized in hierarchies. For example, daily figures aggregate to

monthly sales, monthly sales aggregate to give the annual sales revenue. In

cubes, each of these measures (also called variables) have multiple dimensions

(also called axes).

Decision makers can query OLAP databases using Cubes having each metric along its dimensions

For example, the sales revenue measure can have dimensions of salesperson,

sales area, and outlet. Once defined, the cube pre-calculates and aggregates

values for these measures at each hierarchy of these dimensions. And this

pre-calculation is what makes OLAP quick. OLAP's speed is one of the major

characteristics associated with such a system.

Taking a step further



Now that you know about OLAP, cubes, dimensions and measures let us look at some
more jargon that form an integral part of OLAP implementations. We exemplified

cubes with three dimensions only in the preceding section. Although a 'cube'

has just three dimensions by geometrical definition, an OLAP Cube can have more

than one dimension. How many it has, depends on the complexity of your table

structure as well as your information workers' need. Such a cube is termed as

a 'Hypercube'. OLAP systems have an OLAP server and OLAP client software.



An OLAP server is a high-capacity, multi-user data manipulation engine
specifically designed to support and operate on multidimensional data

structures. The server houses all the cubes. OLAP clients are applications that

request multidimensional data from the OLAP server for manipulation,

calculations or analysis.

Measures and Dimensions are not the only part of a cube or hypercube. The

measures stored in a cube can be used as inputs in expressions for calculating

values for further variables on the fly. These expressions are termed as 'Formulas'

and the resultant variables are termed as 'Calculated Members'.

The most distinguishing characteristic for identifying a calculated member is

that it is always a resultant of existing measures in a Cube. What all these

facilitate in the end is termed as an Ad-hoc Analysis of data. Ad-hoc analysis

allows users generate new queries without defining anything in advance. This is

the most important resultant of an OLAP system, as ad-hoc queries do not exist

in the purely relational data model.

Star or Snowflake Models



You do not specifically need a data warehouse for OLAP. Your existing relational
models can also act as a source for cubes and hyper cubes. A Snow Flake schema

is a special, de-normalized data model used by relational databases to provide a

multidimensional data structure for OLAP applications.

Several dimension tables surround the fact table. A fact table is a table

that record the metrics of a KPI and provides independent attributes using which

different dimensions can be analyzed. This model is central to the

implementation.

The Physical Model of our database will contain several tables, with fields connected with relationships

Our sample database



For our sample, we have a holistic multi-location multi-store supply chain. This
lets us address several entities like clients, products, orders, outlets and

time. A real-life deployment would be more complex, involving more parameters.

In our database, we have tables for these entities: customer, order, outlet,

product and sales. The order table has basic fields like order number, date the

order was made, customer number and product code, and quantity. Then we have a

product table with the product number, name and unit price. Likewise there are

tables for customers and outlets.

First steps



Let's implement a cube in this database step-by-step. The first step is to get
a database with tables and columns. These tables are a low-level form of

relational databases. Our retail company wants to view monthly and weekly

reports. Someone feeds in data into a spreadsheet as rows and columns.

These relational databases only provide limited capability. If you want the

performance report for a particular time frame or for some specific customer,

product and location then you have to pull in all the data and work on it all

over again.

Creating these reports according to different requirements creates a lot of

work and number of sheets increase with every additional relationship added. The

structured tables from a database can be changed into a star schema which lets

you interconnect the different fields in it. These tables are linked together

using their inter-dependencies. For example, our order table is dependent upon

customer and product tables for customer id and product id respectively. Now the

sales table we created depends on the product, customer, outlet and order

tables. In our case, the dimensions invariably are Customer, Product, Order and

Outlet while the sole fact table is the Sales table.

Cubing



On top of the relational database we will build a cube for multi dimensional
analyses. As a general procedure you can take time and product on two axes and

as the third axis either you can put employee or outlet. This in turn provides

the data based on performance of an outlet for products in any given time frame.

If you choose to have the product performance over time for employee then

instead of changing the whole table, only one coordinate has to be changed.

These dimensions can have many different parameters and thousands of entries. In

a cube, there are three faces and each of these faces depicts a relation between

two of its dimensions.

Putting up a fourth dimension would let you make a hypercube for advanced

relationships and queries. Instead of creating a domain restricted to months or

years, it is more useful to create a larger 'time' domain. This domain will

invariably include all time domains for which you need to track the reports.

Further, the hierarchy system is to be put in place for every product in the

section it comes under. The locations can be categorized for region, zone,

country, and so on as this helps in finding the relevant data when queried. The

only problem faced while defining these hierarchies are dependencies between

them. These dependencies can also cascade. As the data grows, it becomes easier

for reports to have hierarchical patterns. This would let you look at the whole

picture instead of in small subsets.

The aggregate data from these hierarchical systems can be easily obtained as

the data is stored based on data hierarchy. The attributes in these dimensions

are to be defined like the product size, weight, etc. These attributes help in

narrowing the approach and making cube more versatile, by adding as much content

as it can. There are some calculations that would still exist and will require

inputs from existing measures. For example, over here you might need calculating

product revenues depending upon taxes and VAT. In such cases you need to

correlate two dimensions for calculative analysis depending on a derived

measure. With all the structures defined you can go ahead with deployment of the

cube using the specific tool that your database or warehouse vendor provides.

You can also use temporal calculations for some special cases. Using temporal

calculations, cases can be formulated for checking out on factors such as what

would be the effect on sales given one of the measures carried a different value

than what you have currently. You will also be able to compare two particular

entities using some calculations. And finally using conditional soothing i.e.

predicting trends based on assumed values of measures, and market situations,

you will be able to get the market trend for coming months.

Building a cube for OLAP is not an isolated exercise as we have outlined just

the concepts behind cubing. In the real world, you would be first running

processes to cleanup and get all the data into one universal format, followed by

defining a model that better suits the OLAP paradigm in case your existing model

does not suffice. Whatever be the processes involved, if carried out

meticulously, it is one exercise that would not only change but also ease the

way information workers look and execute queries for information retrieval or

even analysis.

Anadi Misra and Anubhav Verma