Introduction to Dimensional Modeling for Data Warehousing Part 2, Dimensional Modeling Principles


By Kostis Panayotakis


In part 1 of this article series, we described the general structure of a dimensional model. In the present article we shall describe the basic design principles of dimensional modeling. Dimensional modeling follows the four steps defined below. A. Selection of the business process (or processes), the performance of which shall be monitored. Business processes the performance of which is considered critical, and relevant data are sufficient (e.g. operations data derived from these processes), should be selected with priority. The selected business process, may relate to a single organizational unit, or spanning more than one organizational unit.

The capture of overlapping information by different departments which can lead to many versions of truth, is avoided through the capture of a single data stream for an ‘end-to-end’ process. B. Determination of the level of detail at which the process shall be monitored (also called grain statement). The grain statement is the first step in a dimensional model design. Examples of grain statement are:

• Each product sold (meaning: an entry shall be created in the fact table for each product sold)

• Each new service contract (e.g. insurance contract) o The daily snapshot of the stock in a pharmacy

• The accumulated capture of all facts of a transaction which has been completed in more than one steps (e.g. the lifecycle of a tax transaction: tax statement submission – statement control – tax clearance – payment – final payment)

Based on the grain statement, one can derive the facts which should be stored in the fact table as well as the ‘surrounding’ dimensions.

The level of detail captured should be the lowest possible (atomic level). The lowest level of detail, includes the full scope of informational dimensions related to an event.

As soon as a higher level of detail is selected, by aggregating atomic data, certain event dimensions are lost.

Dimensional models aim to capture measurements, according to the way an Analyst views data:o Events that took place at a certain moment in time o Periodic measurements which provide a snapshot of the situation at a given moment in time

• Complete view of a transaction which had more than one steps (did not start and complete at a single event)

C. Selection of the dimensions which form the event framework, within which the measurements were made. Common examples of dimensions are: date (or time) at which the event took place, Customer, product, branch office. The concrete definition of the level of grain facilitates the selection of dimensions. The lower the level of detail, the richer the set of dimensions which accompany the facts. D. Preliminary determination of the analysis methods to be implemented. Selection of the key performance indicators (KPI) for each monitored business process. Identification of the facts needed in order to derive these indicators. Given that conditions change, additional facts may be selected to be captured. These facts should relate to the same level of detail.

The dimensional model should be flexible enough, in order to allow its future enrichment with new facts in the fact table and new dimensional attributes. Copyright 2006 –– Κostis Panayotakis

View dimensional model examples from the Healthcare and Taxation sectors.

Κostis Panayotakis - http://www.pleroforea.com


More Resources

Unable to open RSS Feed $XMLfilename with error HTTP ERROR: 404, exiting

More Management Information:

Related Articles

Prepare A Winning CV
There is every chance that elements of this article may not be appropriate for everyone in every situation - and that's precisely the point! Every situation is different. No two projects are the same.
Delegation for Business Leaders - How Letting Go Works
A leader's role is to focus on those areas of operation where he or she can deliver the greatest value and this requires huge shifts in perspective of the role. Leaders differ from managers in terms of accountability.
Provisioning/User Management System Upgrades: Part I -- Ten Reasons Why Not To Do An Upgrade
Tommy Sherman daily monitors a helpdesk-provisioning queue for a large company. The current provisioning/user management system was written with homegrown software.
Make It Easy to Reply - Voice Mail That Works
If you are like most business people, voice mail has both simplified and complicated your life. On the good side, it helps you exchange information.
How to Fire an Employee
One of the most difficult tasks you will face as a business owner will be firing employees. Employees who consistently break the rules, do not perform the functions of their job, or cause difficulties for your business can be a strain on the work environment, your cash flow, and even disrupt your business from thriving and performing as expected.
Business Innovation - Value versus Quality
Creativity can be defined as problem identification and idea generation whilst innovation can be defined as idea selection, development and commercialisation.There are other useful definitions in this field, for example, creativity can be defined as consisting of a number of ideas, a number of diverse ideas and a number of novel ideas.
Hows Your HUB?
Marketing gurus are always coming up with new lingo but oftentimes they are restating the old tried and true concepts in new terms. Marketing students from the 70's and 80's will be familiar with the acronym USP.
Dont Let Your Measurements Mislead You
Don't Let Your Measurements Mislead YouThere aren't too many words that can strike as much fear and loathing into the hearts of your internal customers, and sometimes your own employees as the words "Operational Measurements". Operational Measurements often get a bad rap because of their misuse by well intended, but misinformed management.
Business Fails When We Do Not Talk
You may remember being told as a child, "Keep quiet!""Children should be seen, not heard," and "You talktoo much." You were a "good" kid if you kept quiet.
Doing More With Less
This is a bottom-line environment.Decreasing the downtime of revenue producing employees is a major concern.
5 Interviewing Mistakes That Can Lead To Hiring The Wrong Person
Mistake #1: Going with the flowInexperienced interviewers sometimes fall into the trap of letting the interview become "free form", spending different amounts of time on different questions, basing follow-up questions on on how the candidates answer. This can result in a candidate taking control of the interview and leading you where he or she wants to go, rather than where you can get the information you need.
The Death Spiral
Sometimes things just happen. Maybe we lose focus and take our eyes off the ball.
Problem-Solving Success Tip: Dont Leave Your Key Stakeholders Guessing
Communicate! Don't leave you key stakeholders guessing.We are generally not very good about keeping others informed about the progress we're making, especially if there isn't much.
4 Steps to Success In Life, Business, The Universe And Everything
Everyone wants to succeed in life. And no one starts a business of any sort, on-line or off-line, wanting to fail.
Five Steps to Successful Business Succession
The great majority of family businesses in North America are still owned and operated by descendants of the founder. The business acumen that these first, second, third, and sometimes fourth generation managers possess largely determines how much longer the business will remain under family control.
Summertime Blues
It's hard to believe the year will be half over in just a few weeks. All the planning you did is either turning out great, coming along slowly but surely or hasn't really gotten off the ground because other issues keep getting in the way.
CEO: The Key To Fix ingThe Marketing/Sales Collaboration Problem
We all know that achieving better alignment, synergy and cooperation between company marketing and sales departments is vital, but oh so elusive. Despite all the talk and more talk in the media and at national business gatherings, nothing significant ever seems to happen.
Is Your Employee Newsletter Management Propaganda?
It should not be. If it is an effective newsletter, it will serve the needs of readers (employees) as much as it serves the needs of the publisher (management).
Management Training: Are You Satisfied With The Results?
If you're not satfied with the results of your management training programs, maybe it's because you handle training as a 'one shot' event rather than a process of developing your people to be better managers.One of the common misconceptions is that a manager can take a course and voila he is now skilled in communicating with others, or managing his time or delegating work.
Critical Success Factors - Next
The Critical Success Factors Focusing on the things that make the biggest difference to your future prosperity. (Note, although this article was written in early 2002, it is totally relevant.