Episode 10 – SQL Server Features- Analysis Services



This week:

In the news, Microsoft has announced that the Incremental Service Model will include Service Packs.

In this week’s feature, we’ll continue the introduction to SQL Server Features and talk about Analysis Services

The web link is on Facebook and SQL Server,

and this week’s tip is on Replication performance tuning.


News this week:

In July of 2007, Microsoft introduced the Incremental Servicing Model (ISM) which sends out a cumulative update for SQL Server 2005 every two months. Customer demand prompted a new Service Pack for SQL Server 2005, and Matthias Berndt of Microsoft announced that the ISM will include service packs as well. Find out more at http://tinyurl.com/6etkpz.



In a previous Podcast I started an overview of the major features you can find in SQL Server 2005. I’ll cover each feature in more depth in future Podcasts, but we need a place to start the discussion for those features, so we’ll cover the basics first. This week we’ll take a look at Analysis Services.


Analysis Services is an engine that processes analytical data – often called On-Line Analytical Processing , or OLAP. Most of us are familiar with systems that take single, discrete requests from users and then store and retrieve them. These systems are often called On-Line Transaction Processing systems, or OLTP. The databases for these systems are most often highly “normalized”, which means that the data is stored in many tables that are related to each other, and the data is repeated as little as possible. For instance, if you’re storing the name and address of someone in an OLTP database, you would probably store the first and last name in separate columns of one table and the address in another. You might even store a list of countries and states in yet another table, separate from the address record. This makes for very rapid updates, and allows you to make a change in one place and have it reflected automatically throughout the system. For instance, if my name pointed to an address record, you could simply change the address record without having to change anything else, and when the data is related back together, the single record can be shown multiple times based on one change.


But a system designed for analysis doesn’t change this way. In fact, in an analytical system, you might want to know that I had one address at one time, and another later on. Also, the system needs to be optimized for rapid reads, so separating the name and the address isn’t as useful – it slows the system down to join them together. Since you’re analyzing past data, things won’t change, so there’s little advantage to storing the items separately.


So have a design of a record that contains all of the information in one place makes for faster speed. This is called a “de-normalized” data structure, and you’ll often see this in an analytical system like Analysis Services. You might have heard this referred to as a “cube” structure, based on the questions you can ask of the data.


When you store data like this, you’re not actually as interested in the detail, but in how the detail “roll up” or aggregate, and how they relate to each other. Analysis Services helps you do just that – it is a system that takes a large store of data and allows you to see how it rolls up, how it is related, and it contains a special language (called Multi-Dimensional Expressions or MDX) to be able to query the data.


While Analysis Services processes these data structures and performs a great many calculations to be able to show you the data quickly, it’s only part of a “Business Intelligence” system. You need a place to store the data – you can use everything from SQL Server to Oracle and even Access – and you need a way to get the data from the source systems into these de-normalized structures. That’s where SQL Server Integration Services comes in – but that’s another podcast. You also need a way to display the data to various users. That’s where Reporting Services comes into play, and we’ll discuss that in another podcast as well.


With all of these pieces in place, let’s talk about the process for using Analysis Services. Again, this is an overview, so there is a lot of detail within these broad steps.


The installation for Analysis Services is on the same media as SQL Server. It is composed on a set of services, files and data storage, just like the engine for SQL Server.


With the installation complete, you come to one of the most difficult steps: Determining the data you need to collect. This will be driven largely by what the business or organization needs to analyze. In this step you need to identify all your data sources, and what they store.


Next, you’ll want to extract, transform and load the data from those source systems into your analytical data store. As I mentioned, SSIS can do this for you, but you’ll have to make decisions on things like a standard currency or a date and time standardization.


The next step is to create the “cube” structure of that data using Analysis Services. You’ll pick the way the cube is processed and how the data is stored.


With the cube defined, the next step is to process it. That tells Analysis Services to do all the calculations for the aggregations and other numerical pre-processing so that the queries run quickly for the users.


Finally, you need to distribute the information. Users can look at the data using a program you write using MDX queries, Microsoft Excel (which makes a great client for Analysis Services) or Reporting Services, or all three.


Sounds easy, doesn’t it? Well, you should know that many companies fail when they try to implement a Business Intelligence system, and it usually isn’t a technological problem. These systems can take a long time to implement, and many organizations don’t have the patience it requires to follow it through. But the reason I’ve seen that most systems fail is lack of planning. But the benefits of a good Business Intelligence system far outweigh the risks – I highly encourage you to install AS and follow the tutorials, and read through the information in Books Online. There are also lots of helps out there to create a successful project.


Web Link:

You may not be aware that Facebook runs on SQL Server. It’s a fascinating study on how a platform can be pushed to the max, and then scaled out to meet the need. Read about it at http://tinyurl.com/3wja88/.


Tip of the Week:

Last week we covered Replication in SQL Server. Whenever you replicate data, make sure you carefully examine any user-defined triggers on the Subscriber. If you include a lot of logic in triggers, you can slow the replication dramatically. For more, check out http://tinyurl.com/5d4jta. 

Skip to main content