Complete OLAP infrastructure without Microsoft Analysis Services, part 1

Few weeks spent in the OLAP world searching for answer for following question: How to be able to do OLAP with no additional tools available?

Specifically: are we able to build a full OLAP infrastructure for clients who do not have Microsoft Analysis Services or any other enterprise OLAP severs?

For few last years our company used Microsoft Excel to perform online analysis of the relational data. As you know, Excel's capable of building pivot tables for any kind of relational data. There are three problems with Excel:

• it does not scale well (usually, several hundred thousands of records in the data source is enough to kill Excel)
• it does not like to be executed inside the application server (due to security and scalability reasons)
• you do rely on Excel and this can be too much for some clients

Few years ago Microsoft released Microsoft SQL Server 2000 PivotTable Services, a COM-based solution which could be used to built offline analytical databases (offline cubes) and process them with no Analysis Services available. The library is however not maintaned anymore.

Today, we have a new king of the ring: the Microsoft ADOMD.NET library. This one, and other core components of the infrastructure I will present are available on the Feature Pack for Microsoft SQL Server 2005 page.

First, to visualize our goals, let's examine following picture:

 

The left-most path is obvious and preferable for these implementations where the Analysis Services can be used as the analytical processing tool and any fully fledged external tool can be used to browse the analytical data.

The right-most path would be ideal for these implementations where neither Analytical Services nor any other external tool can be used but there is still demand to be able to do OLAP in favor of direct relational database querying.

Are you interested in the right-most path? Do you think it is impossible? Or you did try it but failed somewhere and were unable to complete the scenario? Or maybe you are just interested how to do it?

Just a remark before we start - this is not going to be the OLAP tutorial. I assume that you are aware of what OLAP is and why this is important to be able to browse multidimensional analytical data instead of heavy and ineffective queries on a relational database. If you are not aware of OLAP advantages over traditional database querying then stop reading right now because you will just not understand what I am talking about and why this is so effective and so cool to see how OLAP works and how it works with no Analysis Services in the background.