Getting Started with SQL Server Analysis Services
SQL Server is packed with so many different components that finding the time to discover what is included as well as finding the time to learn about these different features is often a challenge. SQL Server is great as a relational engine, but there are two other parts of SQL Server that offer a wealth of functionality, automation and insight into your data. These two tools are Reporting Services and Analysis Services.
Reporting Services has only been available for a couple of years now and I see this feature used quite often which is great that people are taking advantage of this added functionality. The other tool Analysis Services has been around since SQL Server 7.0 and I still find a limited number of companies that are using these features. With SQL Server 2005 a lot of effort was put into making this tool more user friendly to build and to utilize. So if you have never used Analysis Services or if you have not used Analysis Services with SQL Servers 2005 take a look to find out how to get started?
As I mentioned a lot of effort was put into Analysis Services for SQL Server 2005. The problem is if you don't know how to use it or why to use it then you are probably never going to use it. So let's explore these two different areas:
Why use Analysis Services?
The problem with a relational database is that the data is just sitting there waiting for you to do something with it. You can write queries, reports or tie your application to the database, but understanding the data takes a lot more work then just these things. This is where Analysis Services come into play. This feature rich tool set offers insight into your data that you know exists, but just don't have an easy way of accessing. By writing queries and reports you can get the sense of certain things that you are looking for, but by utilizing Analysis Services you have the ease of maneuvering through your data with much less effort. This gives you insight to areas that are often overlooked or not even thought about.
Also, these tools allow you to put the power of analyzing data into someone else's hands, not the DBAs or the Develpoers. I know quite well the requests for new reports and new queries take time away from other DBA and Developer duties, but by using these tools you can put some of the control back on the end user for them to discover what is in the data.
How to use Analysis Services?
Getting started with Analysis Services is often a challenge in itself and this is probably why many people have shied away from it. One way to get up to speed is to use the tutorials Microsoft has put together that show you how to define, create and utilize Analysis Services. If you have never taken the time to explore Analysis Services or if you have not had the time to do this with SQL Server 2005 then here is your chance to delve in to something new.
The following is an outline of the 10 different lessons that have been put together. These lessons use sample data and sample projects that you can download here.
- Lesson 1: Defining a Data Source View within an Analysis Services Project
- Lesson 2: Defining and Deploying a Cube
- Lesson 3: Modifying Measures, Attributes and Hierarchies
- Lesson 4: Defining Advanced Attribute and Dimension Properties
- Lesson 5: Defining Relationships Between Dimensions and Measure Groups
- Lesson 6: Defining Calculations
- Lesson 7: Defining Key Performance Indicators (KPIs)
- Lesson 8: Defining Actions
- Lesson 9: Defining Perspectives and Translations
- Lesson 10: Defining Administrative Role
By going through the lessons you get a chance to use the new Business Intelligence Studio, but also get a quick way of understanding how to navigate through the tool. The lessons are already prepared, so if you want you can skip to a particular lesson or go through each lesson and build upon concepts you learn from the previous lesson.
Taking the time to go through the lessons will definitely give you a good sense of how to use Analysis Services, but it won't make you an expert nor will it give you insight into your data and how to setup things, but it will give you a jump start as to what is possible and hopefully will give you some ideas as to how this tool can be a great asset to your company.
- Take a look at the sample tutorials to get comfortable with Analysis Services
- Find a business user that has some interest in uncovering information from all the data that is collected and work them to define something they could use
- Start off with a small project or one concept to better understand the potential
- Take the plunge and use Analysis Services, why not it's free
- This is just the tip of the iceberg, so get started and keeping learning
- Take a look at these other articles
About the author
View all my tips