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

Let the Professionals Help You Out - Outsource
As your website grows in terms of attracting more footfalls, generating more business, and providing more content; it will demand more time and attention from you to continue performing. As a businessperson, it is advisable that you concentrate on your core competency, which is the reason why you created the website in the first place.
Is Your Business Healthy?
Most business owners I work with want to grow their businesses. I guess this probably applies to most businesses in general.
Dividing The Loot
It is when the going gets better, that the going gets tough. This enigmatic sentence bears explanation: when a firm is in dire straits, in the throes of a crisis, or is a loss maker - conflicts between the shareholders (partners) are rare.
Employee Performance - If You Want the Best, Get Personal!
This is a story about a man and three dogs.I walk a lot - usually for about half to three-quarters of an hour most mornings.
Employee Turnover: Is It Eating Up Your Profits?
Keeping the cost of doing business down, yet providing a quality product or service, is one of the most critical components of success for today's leader. What many fail to realize is that employee turnover can represent a very substantial price tag to a company's productivity and its bottom line.
Factoring Financing: How to Grow Your Business Without Debt or Loans
What is factoring?Accounts receivable financing, also known as factoring, is a powerful financial tool that has fueled the growth and success of a number of companies. Factoring enables companies to capitalize on their unpaid receivables by selling them to a factoring company for immediate payment.
A Leadership Screw Driver: The 90 Day Improvement Plan
I was talking with first-line supervisors in a utility company about how to deal with poor performing employees."You've gotta put the screws to him!" suggested one supervisor to his colleague who was having trouble managing one particular poor performer.
Is Your Management Style Assisting or Hurting Your Business?
Many times business owners can have significant differences in management styles that can deter the growth of both the employees and the business.Employees can have differing needs that require differing methods of management as well.
Todays Employees and Their New Needs: What You Need to Know
Today's employees are not only interested in security or in pay and fringe benefits. They are often placing increased value on being recognized and involved in reaching personal growth goals.
3 Simple Things the Best Managers Do - And You Can Too!
If it's so simple, why don't managers all over the globe get this right, every time? Well, because it's so simple, it seems too easy, so busy managers squeeze a lot more in, time after time. And that makes things much more complex - just the way a manager should be.
Knowledge Management - Capturing And Structuring Knowledge Into Reusable Assets
Many organizations have an approach for identifying and recording lessons learned, perhaps as part of a post-project review or similar process. Unfortunately, lessons learned reports have a tendency to end up on a shelf gathering dust, or lost in the un-chartered corner of a fileserver somewhere.
Are you NICE or do you CARE?
Are you NICE or do you CARE?Most people and most managers want to be nice. After all, it's easier to be nice than to not be nice.
When Code Freeze Turns To Code Slush
Time To Market (TTM) is a vital concept that every executive understands. Releasing a product to market before your competition gives you a significant first-mover advantage.
Creativity and Innovation Management: The Creative State
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.
Qualities of a Great Manager
In the call center environment we are often only as successful as the people we hire. While our front line employees are critical to our business, choosing the right managers powerfully impact your success.
Managing People - No One Shows You What To Do
Imagine the following scenario - you pay a visit to yourdoctor one day and in the course of the conversation he orshe lets it slip that they have no formal medicalqualification. However, everything's okay because they'vebeen involved in the "doctoring" business for years, hadlots of experience and have read several books on thesubject; I bet you'd be out of there like a shot.
Managing Client Relationships
Managing Client Relationships: Even the best run organizations occasionally run into difficult situations with clients, consultants, and vendors. Often times it is not just a business process that has gone a-rye, it is the relationship of the people managing the situation.
Collaboration: 3 Keys to Keeping Your Documents from Getting Lost in the Shuffle
Often, collaborating documents in a team or in a business can feel like a complex sports play gone wrong. The ball gets passed off to the wrong person, dropped, or even lost.
The Top 10 Things They Don't Teach You In Business School
Here are 10 subjects that academia should be teaching their students in business school:1. Generate revenue for your companyWhat academia doesn't teach you is that the real purpose organizations hire you is to generate revenue.