Databases Reference
In-Depth Information
a great deal of confusion within the business community, then the model has
not yet captured the business perspective.
TIP
Don't do what is easier to implement, do what is easier for the business users.
Query Performance
The second, and equally important, goal of a dimensional model is to ensure
good query performance. If requests do not run in a timely manner, the data
warehouse will not be used and will not be helpful to the business. Dimensional
modeling takes the need for this query performance into account as part of the
inherent design approach. The data is organized in order to provide consistent
performance both for queries that are requested up front and for those that crop
up later. All possible queries cannot be defined in advance, so the technique
takes this into account to provide support for unpredictable access patterns.
Understanding Your Data
The requirements gathering process detailed in Chapter 6 identified the
different kinds of data that are needed for the DW. The prioritization process,
also discussed in Chapter 6, helps ensure that the project charter and scope
align with the business requirements, including analyses and the data sources
needed. Now, detailed data analysis is needed to really understand what
is stored in the operational databases that have been identified. A robust
operational database is likely to have hundreds of tables and thousands of
individual data elements.
A lot of legwork should be done by the core project team to determine
which data elements may be useful. The systems staff members responsible for
maintaining that application can be invaluable in this research. The objective
is to narrow down the sheer number of data elements that need to be studied.
This is also called
source data analysis
. This can range from running simple
queries to much more sophisticated analysis. At a minimum, simple queries
should be run against the application system's data structures. The types of
queries include the following:
Has this data element ever been populated?
When was the last time this table was updated?
What percentage of the rows in this table contain data?
What are the possible values for this data element?
What is the frequency with which these values are used?
