Analysis is at the heart of Business Intelligence; with it, you can put context to your data. SQL Server includes a very powerful engine for building multi-dimensional data structures that allow you to arrange, aggregate, and analyze your data, known as SQL Server Analysis Services. Collecting information for the sake of collecting it is a waste of time, money, and manpower. Using that information to discover trends, identify problems, and address shortfalls adds business value to the data.
SQL Server Analysis Services uses an Online Analytic Processing (OLAP) engine for building and storing multi-dimensional databases. In this chapter, you will learn about the basics of OLAP technology, the tools used to build OLAP databases, and the components used within.
OLAP databases are built around the concept of the cube. Cubes are multi-dimensional objects whose structures are defined by hierarchical objects known as dimensions. An example of a commonly used dimension is Date. Units of time can be divided or combined as needed based on the level or depth of data that will be stored in the database. For example, the Date dimension might consist of a decade level, a year level, a quarter level, and so on, all the way down to the day (or lower, if necessary).
Another important concept when working with cubes is the understanding that most of the data being accessed is aggregated, or at least can be aggregated. This means ...