Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

What Is Analysis Services?

Microsoft Analysis Services is a middle-tier server for OLAP and data mining. Although Analysis Services is bundled with Microsoft SQL Server 2000, it requires a separate installation; however, the products work together to provide a data analysis platform. Figure 5 shows the data analysis workflow.

Figure 5Figure 5 Data analysis workflow.

The complete data analysis platform includes several tools that work together to provide great data analysis capabilities:

  • Data Transformation Services (DTS). A tool to extract, transform, and load data from operational systems to the relational data store. DTS helps with the process of automating data extraction from other sources; cleaning, aggregating and grouping data; and scheduling data extraction services.

  • Microsoft SQL Server 2000. The relational data storage used to create relational databases and data warehouses or data marts.

  • Microsoft Analysis Services. The OLAP engine for data analysis. It handles the multidimensional structures (cubes) used to store OLAP data.

  • Microsoft Analysis Manager. The administrative console for managing Microsoft Analysis Services, this is the equivalent of the SQL Server Enterprise Manager.

  • OLE DB. Drivers for accessing data on relational databases. Microsoft provides drivers for SQL Server 2000, Oracle 7.3 or later, and IBM DB2, and supports drivers provided by other third-party software vendors.

  • Clients. Several interfaces are supported by Microsoft Analysis Services to provide access to OLAP cubes. The PivotTable Service (included with Microsoft Excel) is the preferred client for accessing OLAP cubes.

Because the OLAP cubes are constructed based on the data stored in the relational data store, I recommend that the data be structured in a well-designed data warehouse or datamart; this will simplify the task of building the cubes and will improve performance when processing cubes. Microsoft Analysis Services organizes the data from the data source and precalculates all the possible aggregations based on the business rules that were defined. In this way, Analysis Services can provide fast answers to complex analytical queries.

OLAP cubes can be stored in three different ways:

  • The data and the aggregations are stored in a multidimensional structure—this is called multidimensional OLAP (MOLAP).

  • The data is stored in a relational database and the aggregations are stored in a multidimensional structure—this is called hybrid OLAP (HOLAP)

  • Data and aggregations are stored in the relational database—this is called relational OLAP (ROLAP).

The Microsoft Analysis Services server builds the cubes according to the fact and dimension tables of the data warehouse. The design of the data warehouse is critical when using MOLAP or ROLAP structures.

  • + Share This
  • 🔖 Save To Your Account