Databases Reference
In-Depth Information
be worthwhile to explore other sources such as a corporate customer master
file or perhaps the policy administration system for additional customer data.
If you have many dimensions in your model, several are likely to have
deep hierarchies and dozens of attributes, but you may also find that several
dimensions have fewer than five, or perhaps just one attribute. Make sure you
have looked for other sources, and explored additional possible attributes or
hierarchies from the business community. If you can't find any, then this may
in fact just be what you have. One other avenue to explore is whether this
single attribute dimension really belongs within one of the other dimensions.
Guidelines for a Single Fact Group
In general, you are likely to find four to ten dimensions on any single fact
group. Fewer than four dimensions may indicate that the dimensions are
too big — with everything stuffed into a dimension. Too few dimensions may
indicate that other dimensions still need to be identified.
You can also go overboard with too many dimensions. This can affect
usability and perhaps impede the performance of the database. What is
reasonable? It is common for transaction detail fact groups to have between
ten and fifteen dimensions. Once you have more than fifteen dimensions,
you should be concerned. Consider whether several of the dimensions should
be combined. Over twenty dimensions and you could have some serious
performance issues. If after further analysis you determine that there are in
fact more than twenty dimensions, build extra time into your project schedule
to explore the performance ramifications.
How can you tell which dimensions should and can be combined? It is
important to look closely at the hierarchies. From a business perspective, you
want to be able to follow a hierarchy from the top to the natural bottom. For
example, the business may look at Regions, which are comprised of Districts
and then Zones. Finally, the Zones are made up of Sales Territories. Each of
these is part of the sales organization hierarchy. Suppose that Region, District,
and Zone attributes are stored in one dimension, while the Sales Territory is
in another. Walking through this example may be an indicator that perhaps
Sales Territories belong in the first dimension with the other attributes. The
best way to make this determination is to ask the business users what makes
sense to them.
Classic dimensional analysis leverages drilling up or down hierarchies.
When performing analysis against a design that has many dimensions, analysis
will be done by leaving some dimensions out of the query. For example,
for a travel reservation fact group, some people will want to understand
which travel agents or web reservation systems are driving more business.
Conversely, the business people who work with the hotel chains will want to
understand more about the number and type of reservations by hotel brand
and property location.
