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.
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, dramatically.
Data Cube Optimization
Analysis Services Consultants and Business Executives may encounter performance problems. Cubes are computationally intensive. They are big and complex beasts that require the right hardware resources under all conditions. It really does not matter how informative or “cool” a data cube is if it takes to long to process the cube, or is impractically slow to browse the cube or move dimensions around the cube, then the cube will not be used.
Practical Computer Applications (PCA) can help you find dimension, measure and aggregation configuration issues. We have experience to help you tune your cube, DW and the relational database design to improve cube processing time.
Improve Cube Processing Performance
Cube Processing are the actions required to transform, load and aggregate data into a cube form in SQL Analysis Services. Usually, Cube Processing is done as a batch process, at the server level. In all cases, optimizing cube processing speed is going to be a benefit overall. Our experience can help you find dimension, measure and aggregation configuration issues and help you tune your cube and/or tune your relational database design to improve cube processing time. Our Engineering consultants have many techniques for optimizing dimensions and measures in cubes that can be employed to improve cube performance. A Cube must be processed before it can be Browsed.
Improve Cube Browsing Performance
When an end user view and manipulates, pivots, and moves data around a user interface that looks at Cube Data, these actions are referred to as Cube Browsing. If a cube contains a significant amount of distinct data along any of its axis, or if the cube has not been properly designed or tuned, then users can be frustrated by the time required to manipulate the cube data. Our engineering consultants have experience in designing cubes that will slice and dice quickly and efficiently.
Cube Slicing & Dicing
Cube Slicing is an effective technique that can provide improvements in cube speed. Normally, a cube user does not need to see the entire cube at once. Users may only need to see a time slice (say last month’s data), or a geographic slice (my sales region for example) or similar. Effectively slicing cubes in such a way that the user only sees the portion of the cube they need has the effect of increasing speed dramatically!
We can help you determine the best back end and front end tools for your data and applications. Contact us directly at 617-527-4722 or visit the contact us page.