Chapter 10. Analysis Services

Analysis Services is an engine that provides near-real-time and real-time business information for your users. Business intelligence (BI), also known as business information, is the process of pulling useful information from the data your systems store. In this chapter, I explain what BI is along with some common business intelligence scenarios and how you can use SQL Server 2005 features to implement them. The first section of the chapter is not meant to be an exhaustive treatment of the subject; but you do need a background on what a BI system is and a basic understanding of how it works to manage it properly. I give you an overview that will bring you up to speed on the major concepts.

To create the physical layout that forms a BI "landscape," you will use what you have learned in previous chapters. Everything from sizing a system, how Integration Services works with the process, and how you can present your data by using what you know about Reporting Services is involved with a BI implementation.

The second part of the chapter deals with administering the BI system. I also explain the security implications that are unique to Analysis Services, as well as the monitoring and optimization techniques you can use with it.

There is a great deal of information to cover on Analysis Services. In this chapter, you will get a good overview of the concepts involved as well as a rundown on the parts of the product and how you can manage them. I also explain a little about the maintenance for a multidimensional database, and I finish up with a complete example of the entire process.

Although this chapter introduces many new concepts, if you want to implement a full BI system, you should read up on Analysis Services in Books Online and then check out a few good books on the subject. Even then, it is best to get experienced help in creating a BI system.

The best definition I have heard for BI is the act of turning data into information. That is a different process from storing, securing, and controlling application data as explained throughout the book. Using BI is more strategic than an online transaction processing (OLTP) system.

A good way to visualize this difference is to think of how you interact with your bank. Your bank can tell you how much money you have in each of your accounts, they can move the money around for you, and they can even report it in paper money or coins. You can have the bank change the money into another currency, they allow you to remove the money you have, and they accept new deposits. There is almost no limit to the way they can manipulate the cash and credit in your accounts.

What the bank cannot do is tell you what the amount in the accounts means. They just do not know whether having a certain amount in an account is good or bad. They will not tell you that you need to move money from one account to another, or to invest in a certain real estate transaction. The interpretation of the amounts and how the money should be used is up to you.

An OLTP database-based system is similar to what the bank does. It stores data; allows inserts, updates, and deletes; and can even change the format of the data. However, a standard database application does not try to say what the data means. Just like the bank, interpretation is up to the end user.

A complete BI system does, however, try to determine the meaning of data. By using various tools and combinations of the data along with numeric analysis, the system explores trends, aggregations, and key measurements to show the users how the data should be interpreted. That is a tall order, but as you will see, there are various mechanisms that allow the users to automatically derive meaning, or information, from data.

Microsoft SQL Server 2005 contains everything you need to collect, store, analyze, and present data in this way. Microsoft's implementation of BI goes further than many systems try to present. In most BI systems, the focus is on strategic, analytic, corporate-level data. Microsoft extends the BI definition to include strategic, tactical, and even operational data.

OLTP systems have functions that almost everyone in the company uses, but there is a different audience for the BI system. The user for BI systems is most often a strategic decision makerin most organizations, the middle to top-tier management staff. These users need special training to work with a BI system because the system is more of a tool than just another way of creating output.

A defining measure of a "deep" BI system is that it is strategic and not as much tactical in use. Strategic decisions involve whether to expand the business, what products or services to offer, and how best to meet those goals. Tactical decisions involve pricing, personnel, and production or service rates. Although SQL Server 2005 can be used for tactical and even operational data, the focus is usually on strategic.