Friday, June 27, 2008

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]

4 comments:

Dipesh said...

Your article is excellent for generating the static cube. I was wondering if there is similar way for developing Mining Model without Analysis server as well

Wiktor Zychla said...

good question but unfortunately, I do not know the answer.

Dipesh said...

Thanks for your prompt reply. I did little research and learned that the AS2005 only provide support for clustering and Decision Tree in local cubes. While other algoritm mining model are not supported

Sam Kane said...

Here are this and some other articles on Local Cubes:

http://ssas-wiki.com/w/Articles#Local_Cube