We are building a data mart for a hotel chain's call center. We have a huge customer information table in the source application where they store all sorts of customer related and booking related information. When I am designing the dimension do I keep the information in a denormalized form or break them in a logical mini dimensions. Total row count for customer related info may go over 10 million rows.


Steve Hoberman's Answer: There is no easy answer to this question. It "depends" maybe is the best answer. I could narrow down the answer a bit by saying it is probably very unlikely that your final design will be in fifth normal form or on the other extreme a single table. In my data modeling training classes (visit www.stevehoberman.com), I recommend first creating a fully normalized logical data model. Knowing how the data elements relate to each other will help you make the most intelligent design decisions. I believe in assuming your fully normalized logical data model equals your physical data model and then selectively introducing denormalization and other physical data modeling techniques into your design. Consider factors such as usage, rate of change, the reporting tool, ease of inserting rows and retrieving data, user friendliness. Deciding which denormalization techniques to apply is definitely a balancing act. One final thought, I would be very hesitant to keep booking related data (more event driven) in the same structures as the customer reference data.

Chuck Kelley's Answer: Take your pick. Do they want all the customer and booking information for this data mart for the call center? If so, then the only question you will need to address is performance. Is it better to read a million large rows or a million small rows for the majority of my most important queries? I would bet that you will end up with mini-dimensions.

Joe Oates' Answer: Without knowing the details of your specific situation, I have worked on a data warehouse project with similar requirements. First of all, you need to identify the data that your management wants kept to distinguish one customer from another (name, loyalty card number, address, etc.). Next create a demographics dimension to connect "slice and dice" criteria such as age group, income group, etc. Then you need to create one or more "customer satellite" dimensions to contain information such as there status, preferences, number of nights stayed per period, etc. The data about bookings and stays becomes one or more fact tables.

After you do this, you must decide how you will manage changes to the non-fact data. It is beyond the scope of this forum to go into much more detail, but I will point you to something that does go into detail.

Chapter 11 in The Data Warehouse Toolkit, Second Edition by Ralph Kimball et al, starting on page 235 describes a simplified view of what you are trying to do. This should get you started.

One crucial element of success is to make sure that you have an in-depth understanding of what questions the management and analysts who will be the end-consumers of the information expect to be answered. No matter how elegant a technical solution is, if it does not provide the answers that the end-consumers expect and need, it will be judged a failure. Chapter 4 in another Kimball book, The Data Warehouse Life Cycle Toolkit, describes the process of interviewing the right people to find out what they need. An additional thing that I have found particularly useful is to ask the interviewee to summarize what has been covered by giving you 20 questions and, if the data warehouse provides the answers, the interviewee will consider the data warehouse a success. Without these questions summarized and prioritized, it is often difficult to justify the effort and money expended. Also, you will need a good project plan so that things don't get out of hand.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access