Excal as OLAP application


Excel: Contents	VBA: Contents	Home	Search	Contacts	Articles
What is NEW in this web site			Consulting Services

Definitions

OLAP (On Line Analytical Processing):
An OLAP application allows a user to access, extract, organize and analyze data on line. The great new thing in OLAP is the Data Cube. What is a DATA CUBE?

Data Cube:
A database holds records (rows of data) as they were entered by users (ex: cash register, manual entry of data from a sheet of paper) or by machines (electronic counter on a conveyer) or by a user with a machine (bar code reader). Very few people look at the raw data except to make corrections. Most data consumers want to look at sub-totals. A DATA CUBE is a database of sub-totals. To do so, there are members of dimensions that are hierarchized.

Dimension:
A dimension is a collection of data of the same type, allowing the construction of a DATA CUBE. Time, Location, Product, Quantities and Amounts are classic dimensions. I am not showing you what it looks like in a data cube because a data cube is an entity that transcends the human intellect. In a regular database, it would look like this:

Time	Location	Product	Quantities	Amounts
01/03/2003	Chicago	Dress 9087	1	167
01/07/2003	New York	Pants 6888	1	88
01/04/2003	Paris	Blouse 3543	1	56
01/06/2003	Los Angeles	Shirt 67654	1	22
01/05/2003	Boston	Tie 45355	1	9

Imagine that you have millions of such records, the analysts need sub-totals (quantities and/or amounts) by date, by location, by product. That is what is done in a data cube. You end up with a database of sub-totals.

An OLAP application is not a database in which you enter data, it is an application that reads one or more databases, combines the data and generates sub-totals

After "dimension" and to finish this presentation, I need to impose on you two more terms: members and hierarchy.

Members:
A member is an element of a dimension. ex.: the "dress 9087" is a member of the dimension "Product", "Chicago" is a member of the dimension "Location", "01/06/2003" is a member of the dimension "Time", etc.

Hierarchy:
In each dimension, members can be organized based on a hierarchy. For example, a subtotal for "Ladies Garments" can be created including figures for "Dress 9087", "Blouse 3543", . In this case, the member "Dress 9087" becomes a child of "Ladies Garments" and hence "Ladies Garments" would be a parent of "Dress 9087". "Chicago" could be a child of a sub-total "USA". You can create as many levels of hierarchy that you like to analyse the data by country, by year, by types of garments, etc. These new big members do not show in the original database, you create them in the data cube. This possibility or grouping the members allow new analysis of the data and can lead to very valuable business decisions.

OLAPing

OLAP applications (EssBase, Cognos, Business Object, OLAP on SQL Server, etc.) thus organize the data for the analysts and the report designers. Most of the work is done on the main server making the individual analysis and reports less complex and less computing time hungry on the desktop stations.

The data sits in a data warehouse (centralized database like Oracle, Sybase, SQL server, Access) or in many different databases. The OLAP application can either extract the data from the database or can read text files downloaded from the database. It then creates the data cubes overnight on a server accessible to analysts and report designers. The data cubes can be immense or small, can serve corporate or departmental needs.

Reporting, Analyzing and Charting

Now we have a better set of data, what is left is accessibility of the data and reporting capabilities of the application.

If the data cube is as inaccessible to users as was the database we are back to square one after spending a lot of money and having created one more level of maintenance. The user must be able to access the data with his reporting tool. Most reporting applications within database programs are not convivial (including Microsoft Access) and reporting applications within OLAP are the same. This is my opinion and the opinion of hundreds of my clients working with all kinds of databases and OLAP applications. External reporting applications like Crystal Report are complicated as is Microsoft Query within Excel.

Once the data is organized, one has to design analysis, reports and charts. To this end, Excel is the best tool and almost everybody is familiar with it. With Excel and the add-in created by Essbase, you will be creating a reporting framework that is convivial to users. I have worked downstream form Essbase and I consider this "team" (Excel/Essbase) to be the best tool for data analysts, report designers and users who need to produce reports daily, weekly and yearly.

Discover the SUMPRODUCT and the INDEX/MATCH formulas that allow you to create customized reports, discover Pivot Tables for very fine tuned analysis and,compare.

There are reports and reporting applications, discover VBA, the programming language within Excel that allows you to develop very complex reporting application or that allows you to automate the production of large quantities of reports.


Excel: Contents	VBA: Contents	Home	Search	Contacts	Articles
Resources Workbooks			Consulting Services