Analysis Services Overview


Analysis Services and its predecessor OLAP services were originally designed as a reporting solution for the data contained in a data warehouse or data mart. The information stored in a data warehouse is not typically relational data but rather summary data that’s usually derived from a relational data store. This summary data is stored using a scheme that’s optimized for flexible and fast ad hoc reporting. These data warehousing schemas are stored as either snowflake or star schemas. The data in a data warehouse or data mart is processed using online analytical processing (OLAP) technology. Unlike relational technology, which derives results by reading and joining data when the query is issued, OLAP is optimized to navigate the summary data to quickly return results. Improved query performance is the reason behind the emergence of OLAP. For example, to come up with the local, regional, national, and worldwide sales totals for a given organization, a relational query might have to process hundreds of thousands or even millions of rows—a process that could be quite lengthy even on the fastest of systems. In contrast, OLAP, because it works primarily on summary information, might need to read only two or three data points in order to come up with the same answer. Obviously, this results in a huge performance gain. OLAP’s much faster performance enables ad hoc querying and processing of data that just isn’t feasible using traditional means of relational data access.

Rather than working with sets of related tables, OLAP technologies work with cubes, consisting of dimensions and measures. A dimension is a descriptive category. For instance, a dimension might be a geographical location or a product type. A measure is a quantitative value such as sales dollars, inventory amount, or total expenses. Aggregates derived from the original data source are stored in each cube cell. This method of organizing data makes it easy to filter data as well as making subsequent queries fast and efficient. However, there is a trade-off. While OLAP aggregates are a key to the query performance attainable in data warehouse queries, the cost of storing the aggregate data is disk storage. In fact, the number of aggregates can easily exceed the number of original detail rows. In addition, line-of-business applications do not typically natively store their data in OLAP databases. Instead, the data to load a data warehouse is usually extracted from relational databases and loaded to the OLAP database in a process called extraction, transformation, and loading (ETL).

OLAP Storage Types

Three primary methods are used to store the dimensional data used in data warehousing: multidimensional OLAP (MOLAP), relational OLAP (ROLAP), and hybrid OLAP (HOLAP). Each of these methods has its own characteristic data storage requirements and data retrieval speed. SQL Server 2005 supports all of these methods.

MOLAP

Multidimensional OLAP (MOLAP) stores dimension and fact data in a persistent data store using compressed indexes. Aggregates are stored to facilitate fast data access. MOLAP query engines are usually proprietary and optimized for the storage format used by the MOLAP data store. MOLAP offers faster query processing than ROLAP and usually requires less storage. However, it doesn’t scale as well and requires a separate database for storage.

ROLAP

Relational OLAP (ROLAP) stores aggregates in relational database tables. ROLAP use of the relational databases allows it to take advantage of existing database resources, plus it allows ROLAP applications to scale well. However, ROLAP’s use of tables to store aggregates usually requires more disk storage than MOLAP, and it is generally not as fast.

HOLAP

As its name suggests, hybrid OLAP (HOLAP) is a cross between MOLAP and ROLAP. Like ROLAP, HOLAP leaves the primary data stored in the source database. Like MOLAP, HOLAP stores aggregates in a persistent data store that’s separate from the primary relational database. This mix allows HOLAP to offer the advantages of both MOLAP and ROLAP. However, unlike MOLAP and ROLAP, which follow well-defined standards, HOLAP has no uniform implementation.

With a basic understanding of OLAP terminology behind us, let’s take a look at some of the new enhancements in SQL Server 2005’s Analysis Services.




Microsoft SQL Server 2005 New Features
Microsoft SQL Server 2005 New Features
ISBN: 0072227761
EAN: 2147483647
Year: 2005
Pages: 80
Authors: Michael Otey

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net