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

Regaining Control - Nine Steps for New Managers
My client had faced the same challenge, which was frustrating as well as intimidating for him as well - yet he was determined to break the mould.With my background in a similar business, I have faced this several times.
Eight Skills of Highly Successful Consultants
With deference to Dr. Covey and his very popular Seven Habits of Highly Effective People (all habits that will make us better consultants!), here are eight skills that all of us as consultants can work on to improve.
Tales from the Corporate Frontlines: Finding The Perfect Balance
This article relates to the Work/Life Balance competency, which investigates how your staff feels with regard to the balance between work and personal life. It explores issues such as priority of family and hours on the job, also covered in this competency.
What You Should Know if People Dont Buy From You and People Dont Visit Your Web Site
It is essential to understand what work and what do not work when you run business, especially through Internet. 1.
Creativity and Innovation Management - Money Doesnt Do It
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.
ISO 9001 and Total Quality Management
Total Quality ManagementTotal Quality Management, or TQM, has become one of the most frequently discussed topics in current business literature. Because of the competitive pressures created by Japanese companies, quality became a competitive weapon in the 1980s in most industries.
Project Management - The Traveling Product Manager
Various studies - and common sense - indicate that involving customers increases the likelihood that your product will meet customer requirements (I hope these studies weren't funded with my tax dollars!)Although the above statement sounds exceedingly obvious, the fact of the matter is many software projects lack customer input. Consider the following example:Week 0: Red, our friendly project mangler, is in charge of the next release of his organization's flagship product.
Protect Your Organizations Proprietary Information
The other day one of our overseas clients called in a state of near panic, to ask a question. At issue was whether they had unwittingly violated U.
Meetings and Road Trips
Managing a meeting is like setting off on a long car trip with friends or family. You need to plan your route, pay attention to the rules of the road, consider what will keep your passengers engaged and occupied, and always remember you have to get back home at the end.
Six Sigma Training 101 - Better Management Basics
What is Six Sigma?Six Sigma is a quality management program that is designed to achieve a "six sigma" level of quality for products. In the mid 1980s, Motorola pioneered Six Sigma and it has since been adopted by many other companies and manufacturers.
Relationship Building - 5 Tips and 5 Questions
And is isn't hard - it's more about focusing on people, who they are and what interests them. And that's just where you spend your time.
5 Tips For Creating Great Jobs
1) Create A Powerful "Mission Statement"-When your business mission is clear, every part of your business will improve, because you have a clear, definite major purpose. You will more easily attract people that believe in your mission.
Knowledge Management - Creating a Sustainable Yellow Pages System
How can I "know who knows" None of us can personally know more than around 250 people, yet we want our companies to be smart, learning organisations where it's easy to find the right person to talk to. This is why many organisations create "yellow pages" applications, which enable employees to find and contact other staff with particular expertise and skills.
Can Your Corporate Policy Pass the Monkeys, Bananas, and Water-spray Experiment?
Five monkeys were placed in a cage. A banana was hung on a string and a ladder was placed below it.
Service Level Agreement (SLA) Boot Camp
Service Level Agreements, or "SLA's" are tricky but useful mechanisms for managing the risk of an on-going relationship with IT service providers. Unfortunately, most SLA's that show up in service contracts as worthless, cosmetic paper additions.
Use QA As Your First Step To Outsourcing
Quality Assurance, or QA, is often given short shrift in a software development organization, especially when budgets are tight. When debating the software development budget at one of my software companies, the CEO finally asked, "Well, do you really want to hire a QA guy, or a programmer to add features to the software?"It was a tough choice.
Are Your Meetings Smart?
Soon after I finished a brief seminar on how to accomplish more in less time every day, Roger shook my hand and said, "I can use what you said. But there is one thing you didn't talk about.
Selecting Top Talent: Improve Your Batting Average
I recently gave a presentation to a group of business and community leaders on how to select talent to grow their organization. Given the expense associated with recruiting top performers and the high cost of making poor choices, you would think that those responsible for hiring would follow a systematic process that results in high quality hiring.
How You Can Learn to be a Better Manager
When you first take over a department, expectations are usually high but operations are sometimes in disarray. The staff is disorganized, goals aren't being met, and hours are spent on unproductive tasks.
Conflict at 36,000 ft
This was supposed to be one of those sleepy flights that leaves late, crosses two time zones, and arrives at 11:00 p.m.