Creating the First Draft of a Business Dimensional Model

Last month, I discussed the basic constructs of the business dimensional model, including the notation for diagramming dimensions and fact groups. The notation is intended to be meaningful to the business community, yet provide enough detail to design table structures.

This month, we'll walk through the process to create your first full draft of a business dimensional model.

Once you have completed a thorough requirements-gathering process and selected the data sources for modeling, you need to make sure that you have the right people with you during the initial modeling sessions. The dimensional modeler will drive the sessions. Active participants include the analyst(s) who helped gather the business requirements and someone who is knowledgeable in the source system data. It is helpful to include a representative from the extract, transform and load (ETL)/staging team to capture the knowledge uncovered about the sources, placement of elements in the model and preliminary transformation rules. I typically do not include key businesspeople at this stage. I prefer to wait until we have a framework and more knowledge about what the model will look like. We will indeed need their help, but not yet.

You will save time and minimize interruptions if you gather what you need before you begin –­ this will ensure that your modeling sessions are as efficient and productive as possible. Materials you should have with you include:

  • Business requirements document
  • Data source documentation, data models and/or file layouts
  • Flip charts
  • Markers
  • Tape to hang flip chart (you could use self- sticking flip charts)
  • Spreadsheet with each source data element
  • Electronic means for documentation

To speed things up, arrange to have two computers in the room: one to document the diagrams as you go, and the other to document notes and comments on the source-to-target data map.
At this point, all participants should have at least an introductory knowledge of facts and dimensions. You will want to brainstorm about what dimensions and fact groups you might have. Don't be critical; simply jot down ideas as they flow. This should take approximately 10 to 15 minutes. Post the results near the front of the room; you will refer to these throughout the sessions.

Now, begin designing dimensions in earnest! I like to start with the period or date dimension. This follows common constructs from project to project. Name the dimension and identify the lowest possible level of detail, or grain, that this dimension could have. Think about how things really happen, not just what is captured in today's systems.

From here, begin to add attributes and identify hierarchies. Capture the most commonly discussed attributes and hierarchies. Also, review each column or field in the source system reference tables. Look on the transaction tables for descriptive data. When you have a good draft, post the newly diagrammed dimension on the wall and start working on another dimension. Look for the important or critical dimensions next, such as customer and product.

The goal is to create a first cut of all the dimensions, not to develop and polish the first one or two. Don't spend too much time on questions and issues that arise. If you have spent more than 10 or 15 minutes on an element, additional research will be needed in order to understand the element or be able to place it in the model correctly. Simply write down the questions and move on. You will address these issues later.

After working through the dimensions, you are now ready to begin working on fact groups. Pick a straightforward set of facts. Locate these facts in the source system to understand the dependencies and keys that identify the elements in the source system. Look at each dimension and ask, "Does this dimension apply to these facts? If so, what level of detail is appropriate?" Again, document questions or issues that take too long to resolve at this time.

It can take several days to obtain a rough draft of the model. The length of time will depend on the number of sources, the total number of elements and the level and quality of documentation about the sources. Less documentation or more elements increase the time required for the modeling process.

I have found that the longer I wait to document the model and formalize the issue log, the more difficult the job is. My business partner documents on the laptop as we model. Therefore, with an hour of review and clarification, we can print what was developed that day! You should also immediately update the issue log to translate any cryptic notes into complete sentences that fully describe the problem or question. The issue log should contain the issue number, topic, full description, date identified, person assigned, date closed and current status. Finally, notes should be added to the source-to-target map.

Now you have a complete first draft of your business dimensional model. Next month, we'll look at what you need to do to finalize the model.

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