Learning Data Warehousing and OLAP -part II
It has been a while since I wrote Learning Data Warehousing and OLAP -part I. So I want to recap by re-posting the bits that helped me conceptual OLAP. By the way, I understand the definitions I offer may not be concrete in all circumstances. In fact, I am sure that we all tend to use the terms without knowing their root or history. It is interesting to see which BI terms have become "sticky". And don't hesitate to offer a correction or suggestion on some of these definitions. :)
It helps to view the little diagram I created while reviewing the concepts.
Image is derived from Microsoft® SQL Server™ 2005 Analysis Services Step by Step (9780735621992). Microsoft Press. All rights reserved.
What is a data warehouse?
- A data warehouse is a repository for storing and analyzing numerical information. Core data in the data warehouse are typically numeric values that can be summarized (or aggregated). A great place to visit for learning this is the Data Warehousing Information Center.
- One reason for a database structure different from a transactional database structure is that pulling data can otherwise prove to be very, very expensive. Data warehouses allow you to store aggregated data -rather than ad-hoc summing with a resource-expensive query.
What is OLAP? How does it relate to a data warehouse?
- OLAP usually describes specialized tools that make warehouse data easily accessible.
- An OLAP cube is a logical structure that defines the metadata. It is a borrowed term to describe existing measure groups and dimension tables. A cube is a combination of all existing measure groups.
- A measure group is a another logical structure that defines metadata so that client tools can access the data. It is the group of measures that share the same grain. Each measure group contains the detail values stored in the fact table (copied or dynamically retrieved values).
- OLAP cubes contain lots of metadata; metadata in its simplest definition is data about data.
- Multidimensional expressions or MDX is a metadata-based query language that helps you query OLAP cubes.
What is SQL Server Analysis Services (SSAS)? How does it relate to OLAP?
- SSAS, once called OLAP Services, provides server technologies that help speed up query and reporting processing. Analysis Services implements OLAP with technologies that simplify and quicken the process of designing, creating, maintaining, and querying aggregate tables while avoiding data explosion issues. See Microsoft Press Microsoft® SQL Server™ 2005 Analysis Services Step by Step.
How does PerformancePoint Server relate to data warehouses, OLAP, or SSAS?
- SSAS data supplies the client tools in PerformancePoint Server with data which feeds into the larger business intelligence (BI) suite provided by Microsoft.
Now that I have made a few posts about getting the PerformancePoint certification and Microsoft Business Intelligence certification, I want to connect the two and show how PeroformancePoint fits into the bigger BI picture. I like doing this with with a diagram. The following diagram illustrates where PerformancePoint fits in the larger business intelligence picture.
Notice the data sources at the bottom are not all Microsoft products. Any data source can be used as long as you integrate data. PerformancePoint Server Planning data integration is simplified with a staging database and stored procedures that convert label-based data to ID-based data that reflects what is in the Planning application database. Click here to see the DI documentation.
Admittedly, this was an exercise for me to organize what I had learned and pass it on into a concise and clear explanation.
