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

Keep The Faith - Transform The Fear
FEAR! To what extent does fear rule your life? How is fear controlling or motivating you? Bring up the topic and watch people react. Our lives incorporate so many beliefs about fear- reinforced by what we tell ourselves and what we hear from others.
My Companys Leadership Sucks!
Maybe it's the season or just a more buoyant job market; but lately I'm sure involved in a lot more discussions about leadership.I'm receiving more requests for help defining the key characteristics which make a great leader; and I'm hearing a lot of negative stuff from clients about their bosses.
A Man and His Razor
It is vain to do with more what can be done with less. William of Ockham. This is Ockham's famed Razor.
Loyalty, Motivation And Work-Life Balance
Managers who aren't loyal to their people can't expect loyalty in return. Companies that complain about employee loyalty have usually done nothing to earn loyalty, often routinely lying to employees, demanding sacrifices that are never rewarded, shunting them aside and casting them off in the name of good business.
Developing Your Mission
"The best Leader is one who knows how to pick good people to do what he or she wants done and self-restraint enough to keep from meddling with them while they do it." - Heidi Richards -Mission statements describe the purpose of an organization or a sub-group of it.
Marketing Miracle: Outsource Your Creative Services
The current economy is causing most companies to tighten their belts. With limited staffing and a restricted budget, how do you continue to promote your business? The simple solution is the occasional or ongoing use of outside resources such as graphic designers, writers, webmasters, and other creative talent.
Employee Discipline: How to Nip Problems in the Bud
Are you uncomfortable with delivering disciplinary action, even involving employees you know deserve it? You're not alone. Disciplinary action is one of the least favored tasks a supervisor must occasionally perform.
The Leadership Imperative: Making Your Leadership Your Life
Nearly all leaders I've encountered are underachievers. They're getting a fraction of the results they are capable of.
Give Yourself a Boss Day Gift, Part 2: Are You the Position or the Person?
Are you the position or the person?Bosses can lose their own identity sometimes. It's easy.
The 20/60/20 Rule Of Leadership. Dont Go Solving The Wrong Problems
Several decades ago, a passenger jet approached a Florida airport with the pilot and co-pilot struggling to fix what they thought was a malfunctioning landing gear. The landing-gear light was on, signaling that the gear was deployed; but both men did not hear it actually deploy.
Take The Guesswork Out Of Problem Solving
In today's chaotic world and uncertain economic times too many good people are burdened by too many problems that seem insurmountable and too many businesses find it increasingly difficult to compete effectively. The old traditional methods of finding solutions to these problems don't seem to work like they used to.
Survival of the Fittest: The Road to Human Extinction
I was watching a TV program some months ago about a biologist working in Central America who was dedicated to protecting the panther species from extinction. During the program I heard him make a comment that upon reflection I found had embedded in it seeds of wisdom that not even he was aware of.
Assessing Managers for International Competence
How do you select staff for international assignments? It's an important question because, no matter how effective and successful your employees may be at home, they cannot be guaranteed the same performance in a different culture-unless, that is, they can demonstrate some key competencies. But beware, these may be quite different from the competencies they need to succeed in their own environment.
Tales from the Corporate Frontlines: The Organizational Structure of Our Growing Business
This article relates to the organizational structure competency, commonly evaluated in employee satisfaction surveys. It shows how structural concerns can affect the typical employee workday, as well as feelings towards your organization's management hierarchy and reporting structure.
Managing People - Why Is It So Difficult?
Managing, supervising, being a team leader is the hardestjob in the world and I'll tell you why.Imagine what it's like to drive a car.
Data Delivers Credibility
Over the past couple of days I've been setting up visitor counters, so people in another organization can accurately count the number of people who visit their event.They got the idea (and the counters) from an association I belong to, and they, too, are learning how data delivers credibility.
Lets Make Training More Interesting!
Many HR managers believe that by sending their workers to participate in external training programs, they have fulfilled their responsibilities. This is not the best situation.
The Communications Myth
Living in the 21st Century is truly marvelous, isn't it? We live in a world of instant communications where everything we need to know is right at our fingertips. The moment anything of significance occurs it is instantly transferred around the globe making us the most well informed generation in the history of the world.
Managing Employees Is A Little Like Herding Cats
Q: I started my small business about a year ago and it's grown steadily. I like having my own business, but I'm having a tough time managing people.
Keeping Meetings On Track
We all have been in meetings with certain people who get our blood pressure to rise or just make us feel what a waste of time. Here are some of those people and hints on how you can maintain keep the meeting on track without coming across as a dictator or inept leader.