Microsoft Analysis Services

To round off the discussion of high availability coverage on the 70-431 exam, let's quickly discuss a topic that is not within Microsoft's own preparatory documentation for the exam but falls into the realm of elements that may still be covered on the exam, Microsoft Analysis Services.

SQL Server, in particular the Enterprise Edition, ships with many associated features that, when installed on the server, require administration. Anyone responsible for the administration of the server should minimally familiarize himself or herself with the additional objects and processes that are functioning on the server. These, of course, affect the overall performance of the system.

Microsoft SQL Server 2005 Analysis Services (SSAS) provides online analytical processing (OLAP) and data mining functionality for business intelligence solutions. End users perform queries from OLAP, reporting, and custom business intelligence applications to access the data. These queries provide a business view over the relational data. By using Analysis Services, users can mine their data to look for specific patterns and trends.

When you install a Microsoft SQL Server analysis server, you will add a number of databases, assemblies, processes, and control objects to the server. The additional databases need to have physical resources allocated to their storage. The processing of reports against the data and the routine processes of loading and configuring the data utilize resources that also need to be managed.

Addressing the elements that are associated with Analysis Services can become quite confusing because of the inconsistent naming convention. Within the support tools are additional control objects whose naming depends on where you look. Some of the Analysis Services objects use the prefix SSAS (for SQL Server Analysis Services), and others use MSAS (for Microsoft Analysis Services). This naming confusion is carried further, through the terminology that Microsoft uses for its data analysis/mart/warehouse/mining tools, services, and server.

The 70-431 exam has little to do with how the system functions under Analysis Services. It is beyond the scope of this book to explain the use of Analysis Services. However, you do need to know a few definitions, and you need to be able to recognize that an object is associated with Analysis Services and not with the core functionality of the database engine itself. Analysis Services has its own processing and is controlled by its own operating system service.

Exam Alert

Anytime you see MSAS or SSAS, a reference is being made to Analysis Services. You need to understand the terminology used within this product, and you should not confuse it with the SQL Server database engine processes and objects. On the exam you will often see the acronyms for these services.

When you create a database within the Analysis Services framework, that database is controlled by a different service than the standard user databases. The database itself is really quite different from those that are controlled by the database engine. In addition, the objects in the database are also dissimilar. A database within Analysis Services is usually and more appropriately called a data warehouse. A subset of the information maintained within the data warehouse is referred to as a data mart. This information is often organized by department or other business division.

The processing that occurs in the realm of Analysis Services is generally divided into two categories: data loading/building and data querying/reporting. Data loading and other configuration activities can be processor intensive and are usually scheduled for overnight operation or at some other time when the effect on the end users would be minimized. Data querying and reporting are usually ongoing, although the activity increases near the end of a fiscal period and at other times when budgets and inventory are being analyzed.