Getting Started with SQL Server Analysis Services

By:   |   Comments (3)   |   Related: > Analysis Services Tutorials


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.

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.

Next Steps
  • 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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, August 10, 2012 - 8:27:02 AM - Wajih Back To Top (18989)

If I am createing an SSAS Project and using i.e AdventureWorksDW Database. Does Analysis Services stores all the aggregates into Analysis Services database as well or does that calculates on run time?

Friday, June 22, 2012 - 7:56:31 PM - AndrewZ Back To Top (18181)

to bahar:

An OLAP cube does not easily exist in SQL. The structure is created in BIDS, not SQL. It is queried in MDX, not SQL. Ultimately, yes, underneath BIDS it is created with SQL, but Microsoft does not expose this code for viewing. Better to show the cude designer tables in BIDS. Looks like an ERD :-)

Saturday, May 26, 2012 - 9:44:22 AM - bahar Back To Top (17669)


how i can visualize cube in sql ?

i want to show cube to manager ..


get free sql tips
agree to terms