Original article publish on EzineArticles.com on 12/07/2007, http://EzineArticles.com/?id=866694
Data storage for OLAP (online analytical processing) takes the form of data cubes. These are specialized databases of hierarchical data. The real art in creating successful cubes is the acceptance of the end user. Crazy complex cubes may be the triumph of genius data gurus. But if no one uses the end product, the OLAP implementation is not successful. The key to remember: keep it simple, Sally!
We won’t go into all the details of creating data cubes, but we’ll step through a few tips to help ease the pain for the end user. These tips assume a basic knowledge of cube design, and are general enough to be applied to any of the commercial OLAP engines, such as MS Analysis Services, Cognos BI, etc.
- Create a few basic Measures. Measures are the target numeric fields that get aggregated, for example: revenue, expenses, and margins. Two rules of thumb here. First, keep the number of measures manageable. Around six is ideal. This is not for the developer’s ease, but for the end user. Too many measures create too many choices to ponder. There are cubes out there with dozens of measures. But few people know that because few end users bother to access those behemoths.Second, keep the aggregates to the basic functions of sums, averages, counts, and so on. Unless you truly need more complex statistical functions, most end users will glaze over such details. Again, keep the business client in mind. Often they are new to OLAP and are perplexed by nature of slicing and dicing data in a cube.
- Create only a few Dimensions. Just as with measures, the amount of dimensions should be kept to a manageable level. Four to six dimensions are ideal. Dimensions are the description fields organized in hierarchies that describe the numeric measures. A date dimension could start with a year as the highest level; the next level could be months, then days. Another dimension could be by location, starting at the top with the entire country, and drilling down to states, then cities.Dimensions are used to filter the cube data and also to slice and dice the data. Slicing and dicing is the terminology of pivoting columns and rows of data in a grid matrix. Too many dimensions can be very confusing to the end user. Often, many dimensions do not fit entirely on the screens of OLAP software tools. Unsuspected query results occur when the users do not realize some dimensions are still set as filters. It may sound trivial, but if you ever tried to use a cube with twenty dimensions you would experience sure brain overload.
- Create single subject, shallow Dimensions. Nothing adds more to a failed OLAP implementation than users who do not grasp the concepts. Dimensional data can be configured to contain any descriptive item at each level in the hierarchy. Don’t do it. Maintain the same subject for each dimension. A user can understand an organizational chart of company divisions, departments, and employees. A product hierarchy should only contain the product categories and groupings.This sounds like common sense, but can often be at odds with the project owners requesting the data cubes. Often is heard, “we always drill down our data from region, to salesperson, to product code.” The temptation is to create a dimension with exactly such levels; region, salesperson, product. But by creating such a dimension, that cube is forever limited to that drill down. When these different subjects are in separate dimensions, the cube is more flexible. And, the same drill down request is still possible.Also, avoid dimensions with excessive levels. Drilling down ten or fifteen levels is cumbersome and another pitfall to user acceptance. Three to four levels deep into a dimension’s hierarchy is ideal.
- Create multiple smaller cubes for different audiences. Just because you can create a huge data cube to accommodate every possible scenario, doesn’t mean you should. Best to create separate cubes, each with the short list of dimensions and measures, tailored to the specific audience. As with the other above tips, a simple uncluttered cube is much easier to consume.In a number of OLAP tools, virtual cubes (subsets of original cubes) can be created. This feature takes the advantage of dissecting large complicated cubes into manageable parts. Each virtual cube appears to the user as a regular cube. This feature is often overlooked, but can reduce the development time creating many cubes. Just remember to restrict access to the main data cube to only the most experienced OLAP analyzers.
The theme here is obvious. End users will not easily adopt to complicated and extremely detailed data cubes. OLAP software can be very expensive and success is measured by the significant value gained from that investment. Creating data cubes people will actually use is the first step to that success.
