dimension data for an information management requirements specification
and want practical timesaving suggestions?
A dimensional model shows facts and dimensions, which are used for
reporting and analytical purposes. There is no logical
representation of this model. It is a “physical” model but
sometimes it is shown in a logical version in the modeling
tool with English like names as opposed to physical names.
It is sometimes called a “star schema” which
permits multi-dimensional analysis.
Why is it
different from a logical data model?
A logical model shows the business rules that apply to an organizations
data. It contains entities, which are things of interest to a company
and relationships, which are relationships between entities. Customer,
order, product and sales territory might be considered
entities and each entity may be described by many attributes or other
A dimensional model contains facts and dimensions. The order
is a fact and the customer, sales territory and date are dimensions.
This means we can analyze orders by each of the dimensions that are
related to order (In this case, order date, product and sales
territory, and possibly sales representative).
A logical model depicts data in a “normalized” format. Essentially,
this means that each data element only appears once on the model.
(There are a many other technical ways to describe model
but that is outside the scope of requirements analysis).
A star schema allows duplication of data in the dimensions
because this helps improve reporting/analysis performance. The
following model is a simplified design for orders.
In the example above, the order amount is the fact and this model lets
us answer questions such as:
important for requirements analysis?
- How many products were sold this week, this
month, this quarter, etc;
- How many products were sold to each customer
- How many products were sold by sales
representatives, by sales territory, by sales area, etc
The business intelligence analyst needs to determine:
- Which facts are required for analysis;
- What questions need to be answered e.g. what
query profiles are required to satisfy the business needs; and
- How changing dimension data will be handled,
e.g. suppose sales representative “A” makes a sale in April.
Now suppose sales
representative "A" is replaced in June. Does the
business want to keep track of the sales by each of the two sales
representatives, or are they only concerned with sales by sales
If they need
to know sales by each representative, then we need to account for
something called a “slowly
changing dimension" and this needs to be included in the
Do we need
a model for the requirements specification?
Not really! We need
to know a list of facts and dimensions and a definition for each.
However, a star schema design is a “physical” model, which is
created in the architecture and analysis phase.
Dimension data requirements are documented on a dimensional model,
which shows analytical requirements for facts and
dimensions. The logical data model is used to help derive
dimensions. It is important to understand analytic
requirements, or at least the types of analytical questions, to
complete the requirements specifications