Design Utility

I had this idea to create a utility that can be used during the dimensional design process.

The design starts off from looking at the requirements.

1. What are the reports?
This is usually well known...


2. What metrics are there?
   This is sometimes lesser known as the business analyst may not recognize that different metrics are really the same, just rolled-up differently.
   e.g. He/She may have a "sales total metric" and a "branch sale for the month" metric.
   I suggest using two terms to differentiate here e.g. in the requirements document call it metric, in the design document call it measure. "measure" can then become sort of the "normalized metric.
   
   Start the design by collecting these, why not enter this information into two tables: Report and Metric
   
   
3. Collect the different levels where the metrics appear.
   
4. Figure out which levels belong to the same dimension.
5. What are the attributes that have to be displayed for each level?
6. Categorize the levels into hierarchies. Is there a need for more than one hierarchy per dimension?
   
   Store all this into table also. So far we added Level, Attribute, Dimension and Hierarchy, HierarchyLevel
   
   
7. Which metric appears at which level? What is the roll-up aggregation?
8. Which report uses which metric at which level?
   
   Yes... store this info also in tables. Add MetricLevel, ReportMetricLevel
   
   
9. ... and there you go!

You can use a simple Ms Access or OpenOffice Base database to store all this information.
Then you can create very interesting reports from this:

1. Dimension hierarchy
   Report on the Dimension, Hierarchy, Level and Attribute tables
   Will describe what dimensions to create and how to set them up
2. Cube design
   Report on the MetricLevel, Level, Hierarchy, Dimension tables
   Will describe what cubes to create, what are the roll-up aggregations
3. Report usage of metrics
   Report on the ReportMetricLevel, MetricLevel, Level, Metric, Report tables
   Will describe how the requirements are met and how each report will be built from the dimensional model
4. Star Schema design
   Report on the Metric, MetricLevel, Level, HierarchyLevel tables. Find the lowest granularity level in each hierarchy
   Will describe the star schema structure

A utility like this can be written in about a day or two in MsAccess.
It helps to verify the requirements and produces all sections (of what I can think of) of a "design document".

It can be reviewed and has sufficient information to start implementation.

Furthermore I would suggest to create a placeholder model i.e. one with no real values for measures, but all dimensions hooked up.
The bulk of the work goes into building the "real" metrics.

The placeholder model serves a double purpose:

• Development can be split between report authors and modelers, here modeling really means implementing the logic for each metric.
• A mock-up of each report can be created that in my opinion shoud still belong to the design phase. This can be reviewed with the customer and serves to verify both the design and the requirements.

If you are interested in this utility please let me know...