Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Getting Started with SQL Server Analysis Services


By:   |   Last Updated: 2007-01-30   |   Comments (3)   |   Related Tips: > Analysis Services Tutorials

Problem
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?

Solution
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


Last Updated: 2007-01-30


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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

Hi

how i can visualize cube in sql ?

i want to show cube to manager ..

 


Learn more about SQL Server tools