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.

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.

Sample Data Cube & Business
Analytics Application Demos [Click to Run]