Well, I am not very much a database guy, however I developed some cool products/solutions in the past dependent on databases. Anyway, I am stuck at home (doctor's orders) and I am bored. I was looking something new to entertain myself, and decided to have a look deeper in SSAS.

Yesterday, I started blindly. Opened Visual Studio that is installed with SQL Server, and created an Analysis Services Project. You'll immediately notice the folders on the right. You can right click on each and start a wizard. That made my job a lot easier.

Before jumping further: I needed some data. Well, I don't like using sample data and asked a friend for his companies sales report. It was acually an Excel sheet, and every row has a line for a sale. OK I have a bunch of data - now what?

Data is valuable if you understand it. And understanding is easier if you group the data in smaller groups. It is harder, or at least not my choice, to run SQL queries on Excell sheets so I opened up another VS2005 instance and created an Integration Services project. Right clicked to the Packages folder and started importing wizard. Fairly simple and imported the whole excel sheet into a database table (SalesRawTable). Did practice some SQL queries to sort out the data (good to see that I still remember some clauses like SELECT DISTINCT). Collected products into a table including product group, division etc.; financial quarters into one table, all customer information including name, location etc. into one table; and sales data including prices into another. Build relationships with Primary/Foreign keys and connected all tables to sales table.

Right clicked and created a connection to database, created views, created the cube. That easy. In the cube creation wizard you will see a table where you will have to provide dimensions and measures. What I know is; dimension is something you would like to measure. So revenue would be a measure, and your products, customers and financial quarters would be your dimensions. This was what I know, and I built the cube accordingly.

 All I wanted was to see the revenues coming from each customer and when. This was accomplished. Now what we can add? Key Performance Indicators... KPIs are, I think valuable to see where you are standing and to where you are going. It is easy to turn your data into an instantly viewable form. This is also easy, and you can do it inside VS2005 and deploy KPIs into your cube. However, a small knowledge of MDX would be good - or you can check MSDN for some samples as I did. After writing your first KPI in VS2005, it is easy to deploy more.

In the meantime, I deployed Office SharePoint Server 2007 which I like most to present data. I did not used a farm topology, a single server deployment on my home server was fast and it is best for testing. On the Reports Center, I created a KPI list (listed under Custom Lists), and created KPIs coming from Analysis Services. Well SharePoint can list nested KPIs also, so I created parent - child relationships in my KPIs.

Right now, I have a data source (Excel sheet) which I can feed the data into a table and group that data (with Integration Services) and analyze that data (with Analysis Services) and publish some KPIs which shows an instant view of the company's status. My next steps are to use some BI features of SSAS and build some scorecards (those will show more data in a better way).