Introduction to Analysis Services
This post is intended to introduce Analysis Services 2005/2008 foundational concepts.
SQL Server Analysis Services is a component included in the Microsoft SQL Server product, and its use is fully covered under the same license (which applies to Integration Services and Reporting Services as well). Like the database engine, SSAS has a range of features stratified by Express, Workgroup, Standard Edition and Enterprise Editions.
SSAS is an OLAP, multi-dimensional database. While a relational data warehouse can also be described as OLAP, products such SSAS, Cognos and Essbase have fundamental differences. These OLAP servers:
· Aggregate data from a variety of sources into a compressed format optimized for query response
· Emphasize end-user navigation with modeling capabilities such as hierarchies, or drill-paths
· Employ MDX as the standard query language for retrieving result sets
· Use XMLA as the standard communication mechanism between clients and servers
· Automate the aggregation of granular data
OLAP browsing tools, such as Excel Pivot tables, enable users to drag in measures (facts in DW terminology) and constrain and slice them by dimensional attributes (columns in a dimensional DW table). This is a typical browsing session of the AdventureWorks cube in Management Studio:
The metaphor of a cube, or hypercube, is often used to describe the structure of OLAP databases:
Another common metaphor is an Excel spreadsheet. The value stored in cell B12 on page 2 of a spreadsheet could be thought of as linked to 3 dimensions: columns (A- …), rows (1- …) and pages (1-…). Common dimensions in a cube are likely to be Time, Customer, Account, Promotion and Location.
Each distinct value within a dimension is termed a member – so in our spreadsheet example, the rows A, B and C are each members of the rows dimension. In a similar manner, Jan 1st would be a member of the Time dimension.
This explanation introduces the idea of dimensionality, but is inadequate to understanding cube structure in SSAS 2005/2008. Dimensions, such as Time, function mostly as containers of attributes. Attributes can be thought of as the columns in a dimensional DW table, while the set of distinct values of that column are its members. A typical time dimension may have 20 or more attributes: Day, Week, Month, Year, DayOfWeek, Fiscal Day, Fiscal Quarter, etc. It is the unique combination of attributes from all dimensions within a cube that truly identifies fact data.
With those conceptual notes about SSAS cubes out of the way, here’s a simple way to map your relational data warehouse understanding to Analysis Services:
· Fact tables in a typical relational star schema become measure groups
· Individual columns in the fact table (excluding foreign keys to the related dimension) become measures
· Dimensional tables become dimensions
· Columns in your dimensional tables become attributes
· Attributes within dimensions may be arranged in hierarchies – these hierarchies utilize the underlying attributes, but present drill-paths to end-users. For example, arranging Year-Quarter-Month-Day attributes into a hierarchy allows users to drag that hierarchy onto the columns or rows of a pivot table and expand or collapse levels of the hierarchy to see different summations of the fact data.
Visual Studio is the development environment used to design and modify cube structures. In BI parlance, the environment goes by the acronym BIDS (Business Intelligence Development Studio) – which translates to additional SSAS, SSRS and SSIS project types available in the Visual Studio environment.
Here’s a screenshot of a simple SSAS project: