Understanding the SQL Server Analysis Services engine

Microsoft SQL Server released Analysis Services in 2000 as an OLAP cube tool. This tool is a mature service of Microsoft technologies in BI that provides the ability to develop cubes and create dimensions and measures. Creating hierarchies (such as the product category hierarchy) based on attributes of dimensions are also possible within SSAS.

Microsoft introduced a new language named MDX to query the SSAS cube. This querying language has the ability to query cube-structured data and work with hierarchies. Also, many Microsoft-reporting and dashboard tools support this language.

Bottom of text cut off BI Semantic Model (BISM) in SQL Server 2012. BISM is the data modeling layer between the source database/data warehouse and the data visualization layer. There are two methods for modeling data in BISM, of which SSAS Multidimensional will be covered in this chapter and SSAS Tabular will be covered in the next chapter.

Take a look at the following diagram:

Analysis Services is an engine that runs SSAS databases on the server. After the deployment of an SSAS database (which contains cubes, dimensions, measures, security, and some other information that you will dig into in chapters related to data mining), the metadata and structure of cubes will reside on the SSAS server. Then, a processing step is required to load data from a data warehouse into the SSAS cube structure. This step usually runs on a schedule at the end of ETL processing after loading data into the data warehouse (we will talk about ETL in Chapter 4, ETL with Integration Services.

This process will take place when a client wants to get data from a cube. The client tool queries data from the SSAS cube using MDX queries. MDX queries will be compiled, parsed, and executed in the SSAS engine. Hence, some results in a multidimensional format will be returned to the client tools. Then, the reporting client tools, for example, SSAS, will show information to the user as the report and dashboard layout are formatted.

Note that the query will get data from the cube structure and not from the source data warehouse. So the data will be too important in order to load data from the data warehouse into the SSAS cube with a processing step because it will keep data updated and more accurate.

As the data entries will be stored in the cube, data security will be an important aspect. Fortunately, SSAS provides an extensive security mechanism that delivers role-based and row-level security on each cell of the cube, and security can be applied through the administration or development tools. You can read more about SSAS security at http://technet.microsoft.com/en-us/library/ms174840.aspx.