Complete OLAP infrastructure without Microsoft Analysis Services, part 2

Over a half of a year ago I've promised to write a tutorial on OLAP which would require no analytical server. Let's then go back to the issue.

In this part of the tutorial we:

• prepare a source relational database
• build an analytical database with Analysis Services from the source database
• browse the analytical database

Building Source Relational Database

The source database is any relational database. I choose SQL Server 2005 for this tutorial, mainly because of SQL Management Studio but, in fact, an analytical database can be built using any relational dbms as the source.

In our source database we'll have three tables: DIVISION, PERSON and SALE:

Ultimately, the PERSON and DIVISION will became dimensions in analytical database and the SALE will became a measure.

Building Analytical Database from Relational Database

Launch Visual Studio 2005 and create new Business Intelligence Projects/Analysis Services Project. There are three steps to follow, easy if you follow the order presented in the Solution Explorer:

• define the data source so that the analytical database connects to the relational database for the relational data (Data Sources/New Data Source in the Solution Explorer)
• define the data source view (Data Source Views/New Data Source View) adding all three tables, PERSON, DIVISION and SALE
• define a cube (Cubes/New Cube) just following the wizard or making any adjustments
• deploy the analytical database

Browsing the Analytical Database

The analytical database can be browsed in many ways. Below is a screenshot of the cube seen in the Management Studio:

and an example MDX query:

select 

    [PERSON].[Name].MEMBERS on ROWS,

    [DIVISION].[Name].MEMBERS on COLUMNS

    from [Cube Source]

    where [Measures].[SALE Count]