During the design phase of any OLAP project, we have to take all of those white-boarding sessions and put together a true design document. At the very least, you should build a basic diagram of the design before building the actual OLAP database. The biggest problem is that Visio doesn’t include any OLAP friendly shapes. Luckily for us, a pair of companies has put together excellent shapes and templates for this very purpose.
The two companies who have been kind enough to provide design aids are Microsoft and Symmetry Corporation. The Microsoft shapes are best used when designing Microsoft Analysis Services cubes where-as the Symmetry shapes are more generic. Both companies have done an excellent job putting together top notch design solutions for OLAP.
Symmetry Corporation claims that the Application Design for Analytical Processing Technologies (ADAPT) is the first of its kind shape set for Visio. I have been using them for quite some time and have yet to find a better alternative, so I am inclined to agree with their claim. ADAPT can be used on Visio 2000 and newer and you can download version 3.0 from the Symmetry website: http://www.symcorp.com/tech_expertise_design.html
Below, you can see a sampling of the available shapes found in ADAPT. The cube object allows you to input all of your dimensions as they relate to specific cubes. The dimension, hierarchy, and level shapes let you design the roll-up of a dimension. You can then use the various connector objects to provide context to how the levels relate to one another.
I generally use these shapes to build out each dimension before I actually start building the real cube in something like Essbase or Analysis Services. Here is an example of a full dimension design that is very common (time):
You will see two separate time hierarchies: fiscal and calendar. Additionally, there are two attributes to support month over year analysis without a physically split dimension. The double-arrow indicates that the members always roll up to the level above. The regular connector (the single down arrow) just indicates that there is a relationship with no roll-up. In this instance we would never roll up fiscal and calendar together. We would choose one or the other.
The Microsoft set of shapes is both more diverse and less diverse at the same time. The entire package includes a very large number of shapes, but those shapes are for the entire SQL Server product offering. The specific set of OLAP objects is a small sub-set that closely resembles ADAPT. It does of course have that “Microsoft Look” about it. This is probably the best overall solution for designing a Microsoft-based cube.
Below, you can see a sampling of the available shapes found in the Microsoft package. The cube object allows you to input all of your dimensions as they relate to specific cubes, much like ADAPT. The dimension and level shapes let you design the roll-up of a dimension (note the lack of a hierarchy shape). You can then use the various connector objects to provide context to how the levels relate to one another, also like ADAPT. You can download the set of shapes and stencils here as it seems to have disappeared from the Microsoft website.
The biggest downside of the Microsoft set is the lack of hierarchy and attributes shapes. These are both of great importance when building a Microsoft cube and as a result, I believe that they should have been included. As a result, even when building Analysis Services cube, I often prefer to use the ADAPT shape set over the Microsoft set. But, the Microsoft set includes a variety of other useful shapes for Integration Services and Server design that cannot be overlooked. I will close with an example of the same time dimension using the Microsoft set of shapes:
