How to aggregate data from SQL Server 2005

Question

I have about 150 000 rows of data written to a database everyday. These row represent outgoing articles for example. Now I need to show a graph using SSRS that show the average number of articles per day over time. I also need to have a information about the actual number of articles from yesterday.

The idea is to have a aggregated view on all our transactions and have something that can indicate that something is wrong (that we for example send out 20% less articles than the average).

My idea is to have yesterdays data moved into SSAS every night and there store the aggregated value of number of transactions and the actual number of transaction from yesterdays data. Using SSAS would hopefully speed up the reports.

Do you think this is the right idea? Should I skip SSAS and have reports straight on the raw data? I know how use reporting services on raw data using standard SQL queries but how would this change when querying SSAS? I don't know SSAS - where do I start ..?

Answer 1

The neat thing with SSAS is that you can get those indicators that you talk about quite easily either by creating calculated measures or by using KPIs.

I started with Delivering Business Intelligence with Microsoft SQL Server 2005. It had some good introduction, but unfortunately it's too verbose when it comes to the details. But if you want to understand SSAS, OLAP and reporting using this framework it's a good start.

Mosha Pasumansky has a blog on SSAS and MDX with great links.

Other than that I would recommend Microsofts Online books.

Answer 2

Are you sure you aren't mixing up SSAS (Analysis Services) and SSIS (integration services)?

SSAS is not an ETL, it is an OLAP tool.

SSIS is an ETL tool.

I agree with everything that Rowan said. I'm just confused by the terms.