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

The Seven Cs: Partnership Danger Signs - Conflict Becoming the Norm - Part 1
A series of articles exploring the seven critical areas that can indicate a partnership is in trouble.Conflict Becoming the Norm - Part 1Dr.
Uncommon Common Sense - Know What is the Most Important Thing in Your Business
Over 3 of my meetings with 3 business owners last week, what really shocked me was how uncommon common sense was. 2 out of 3 I spoke to do not know what is the most important thing in their business.
Invite Self-Managed Staff
"Treat people as if they were what they ought to be, and you help them to become what they are capable of being." -GoetheTwo hundred years ago, Johann Wolfgang Goethe, German poet and philosopher, knew how to inspire and interact with others: recognize the best in them and act upon those positive expectations.
Successful Business Decision Making
Some people make decisions without any difficulty, while others struggle. Are you having trouble making a decision? Do you feel overwhelmed by all the choices you have, and aren't sure how to pick just one? No matter how big or small those decisions might be, I can teach you how to strategically:-- Define your decision and come up with alternatives-- Determine what criteria you'll need to help you make the decision-- Make the decision"If we wait for the moment when everything, absolutely everything is ready, we shall never begin.
Procrastination and JDI!
In a management role procrastination can seriously hold back progress and demotivate individuals and teams who, full of innovation and drive to move forward, get frustrated and confused when action is held up.There are a number of steps that will help the procrastinating manager.
Diversity in the Workplace
As you look around your office, is everyone just like you? Probably not. The demographics of the American workforce have changed dramatically over the last 50 years.
When Business Becomes A Battlefield
"We have to be careful it's like a minefield out there""I like to lob the odd grenade into the meeting to shake things up""You have to watch your back all the time with her""We need to attack whilst they are in a weak position""There was blood all over the carpet after he had finished with them"What is your reaction when you are in situations where you hear these kind of messages? What sort of tone and atmosphere do you think they create?These words and phrases are becoming more common-place language amongst managers and are indicators of how things are done in their business. They are also indicative of the growing number of leaders who believe that running a business is like waging war or engaging in a military operation.
Are You At The Mercy Of Computer Geeks?
Many business owners are sabotaging their business without even realizing it. They are completely out of the loop when it comes to all technology aspects of their operation such as websites, computers and software.
Stomp Out Leeches In Your Company
Recently, I felt appalled to see a political candidate running on the motto of "Demand More!" That motto oozes extreme nerve, and implies some people think it is o.k.
Tales from the Corporate Frontlines: Providing Career Opportunity
This article relates to the Career Opportunity competency, commonly evaluated in employee satisfaction surveys. It explores issues such as internal growth opportunities, potential for advancement, career development importance, and the relationship between job performance and career advancement.
Problem-Solving Success Tip: Measure the Right Things.
Measure the right things. It's not enough just to measure-you have to measure the right things.
Is Your Company in Need of Family Therapy?
How Companies Are Like FamiliesLike a family, a company is a group of people who have an ongoing relationship with one another. Companies have several things in common with families:1.
Understanding Every Aspect of Your Organization
GET TO KNOW YOUR ORGANIZATION: If you don't understand an aspect of the organization or a procedure within it, ask. If you still don't understand, ask again.
Inspirational Power ( Part 1 )
The Fundamentals of Strategic Marketing, Some Key Traits for Greater EffectivenessProbably, the greatest challenge to marketing management in the next five Years will be to change quick and fast enough, in order to keep pace with new technologies, new markets and new corporate valuesAccording to the definition of the AMA, "Marketing is an organizational function and a set of processes for creating, communicating and delivering value to customers and for managing customer relationships in ways that benefit the organization and its stakeholders.
Steps to Build ISO 9001 Compliant Program
Implementing an ISO 9001 system represents a major effort. However, all of that effort can represent a significant shift for a business - from quantity to quality.
An Honest Look at Your Business
There is a difference between being comfortable and being in apathy.It is very comfortable to have a smooth running organization when you have a team that knows what to do and does it.
Five Reasons Why Leaders Fail to Create Successful Change
A bold title, don't you think? I mean, change is a complex thing, so to define five reasons seems to oversimplify the issue.Let me be clear.
Human Resource Employee Risk Profile - Management Risks Explained
Human Resource Employee Risk ProfileIs your business at risk? Do you want peace of mind?Please answer the following question honestly by drawing a circle around or shading in the column. If you can only answer part of the question in the affirmative, then you should select 'No' eg in Q1 if you have employment contracts for your employees and not for your management team then select 'No'.
Poly Bags and Pallet Shrouds - Just Get it Right
For many companies, procuring the right packaging, such as Poly Bagscan be daunting. This is especially true of polythene bags.
To Thine Own Self Be True--Its Better for Business: What Arthur Andersen Would Say to His Company
As a child, you probably heard, "to thine own self be true." But what does that really mean? When the newspapers are full of cheating and lying business owners, politicians, and academics, does it really make sense to maintain your integrity?To me, the answer is a clear, unwaffling YES! Without your integrity, you really don't have a business or a career--just a waiting game until you world comes crashing down around you.