OLAP Data Cube Consulting & Optimization Services
The data structures required to optimize analysis and
reporting and the data structures required to optimize source application transactions can be (and
often are) very different. On Line Analytic Processing (OLAP) is a BI method that enables long-term analysis and
reporting of data that is stored in a data warehouse rather than the source relational,
transactional database that is optimized for On Line Transaction Processing (OLTP).
Regularly-scheduled ETL processes (Extract, Transform and Load), often daily, update the data
warehouse from the relational database.
This "separation of duties" assures that OLAP and OLTP processing remains separate
and do not interfere with one another.
OLAP Cubes are usually formed by adding up numerical data from relational databases and categorizing them based
on important business factors. For example, what day, week or month
events occurred – a time dimension – or the locations the event occurred – a geographic dimension –
or what product category that was involved at the event and location – a product dimension.
If you take a large detailed table of numerical data (your "Facts"),
and roll it up into sums, averages or other aggregations which are split out by categories e.g. time, location, product, or
any other "dimensions," you’ve got yourself a cube. The aggregations at the intersections of different
dimensions are your "Measures." The categories which form your axis are your "dimensions." Analysis
Services, specifically Business Intelligence Studio, makes it possible to build these cubes in very flexible ways.
Cubes = "N Dimensional" Data Models
In the physical world, we generally think in terms of 3 dimensions – length, width, and depth. If you’re scientifically inclined,
you may think of Time as a fourth dimension. With data cubes, the 3 dimensional connotation of physical reality goes away –
you can combine any number of dimensions to satisfy a specific business need. And the Time Dimension is often the most
important dimension of all, and is important in the majority of all cubes, because we almost always want to know how much
money, orders, sales, costs, people, products, services or other measures are performing along or within a certain time period i.e.
per month, per week, or at least per year.
Unlike physical dimensions, data dimensions can also have hierarchies. For example, Years contain
Quarters, Months and Weeks, which in turn contain days, days of the week, which in turn contains hours, minutes, seconds, etc.
By properly utilizing hierarchies in dimensions, we can optimize the processing of cubes – sometimes dramatically.
For more on how PCA can help you design cubes, contact us, or for more on cube optimization, see
Optimizing Data Cubes