|
Whether
you are implementing a simple report writer product,
a more sophisticated OLAP solution, or a data
mining tool, a properly designed supporting data
warehouse will enhance ease of use and query performance
for your end users. Analytic Vision has extensive
experience in designing (and developing) dimensional
models to optimize the business intelligence environments
for many companies. While we base many of our
design techniques on the "Ralph Kimball"
approach, we realize that some theoretical design
solutions are not necessarily the best real-world
solutions. We optimize our dimensional models
based on each client’s environment and the
data access tools that they have selected.
What
is a Dimensional Model?
A dimensional model is a database design that
is optimized for ease of use and query performance,
with end-user reporting and ad hoc analysis in
mind. An operational database is typically a normalized
database that has hundreds of files and is optimized
for transaction processing. A dimensional model
is typically a star-schema database design that
minimizes the number of tables and only holds
data elements required for reporting and analysis.
Each star schema is based on a subject area of
the business such as sales analysis or inventory
analysis. A star-schema data model consists of
Dimension tables and Fact tables.
•
A Dimension table identifies a major perspective
of a business data set such as Customers
• A Fact table holds the metrics for
a given subject area such as units sold
Here
is a simple representation of a star-schema database,
which offers the following benefits:
• Fewer tables improves query performance
by having fewer joins
• Fewer tables improves ease of use for end
users doing ad hoc analysis
• Field names utilize end-user terminology
instead of generic ERP package names
• Loading of multidimensional databases is
simplified, and low level drill-through is enabled
• Implementing the data warehouse on a separate
box improves performance for both the production
and reporting environments
• Reporting consistency is achieved through
conformed dimensions
|
