Over time, I have noticed a great increase in the complexity and number of elements included in dimensional models. We see increased sophistication of the business community and the ever-increasing size and complexity of data being captured by our operational systems. We need to balance many factors in order to deliver something quickly and be prepared for the future. The most successful projects keep the business community involved throughout the life of a project. Additionally, with the rate of change of technology, you don't want to be married too closely to a specific product.
How do you do it all? After working on literally hundreds of projects, I have developed an approach to dimensional modeling and a diagramming technique that will help you develop the model itself, facilitate discussion, foster communication and help you manage expectations. This month I will share the diagram notation, and next month I will delve into the process that has been successful over time.
The Business Dimensional Model
The purpose of the business dimensional model (BDM) is to separate the business discussion from technical implementation choices. It also provides a way to depict how users will interact with the data using today's business intelligence tools. The primary purpose is to develop the dimensional model in a format that will be useful to the business, yet detailed enough to support table design decisions.
Business Dimension Diagrams
Each dimension in the model is designed and documented separately. Figure 1.1 shows a date dimension where the organization's fiscal calendar is different than the regular calendar.
Figure 1.1: Example Date Dimension
In Figure 1.1, each business element is represented with a rectangle. The element that represents the lowest level of detail possible is shaded. The arrows show one-to-many relationships in a manner that is more comfortable for the users, rather than what we are more comfortable with (i.e., crow's-feet). This indicates drill paths through the hierarchies. Note that multiple hierarchies are supported and clearly visible.
If there is an element that the business community has requested that will not be part of the initial implementation (because you are not using that data source or perhaps it is not captured anywhere), we can note it here with a dotted box. This accomplishes two things: 1) it notes where we think it should fit in the model as a starting place for the future, and 2) it reminds all that it is a future element.
Figure 1.2 shows an excerpt from a table to be included with the dimension diagram to provide complete documentation.
Figure 1.2: Excerpt from a Table
Business Fact Groups
The second part of the BDM is the fact groups. The fact groups are where we define the specifics about each fact. I purposefully do not use the word "table" because we may implement the model using a multidimensional database technology. The business fact group represents facts that have identical dimensionality and grain.
A retail frequent shopper sales fact group is shown in Figure 2.1. The name of the fact group is at the center of the diagram. Each dimension that applies is included in the diagram and the specific grain is noted for each dimension. Each specific fact that belongs to this group is detailed in a supporting table as shown in Figure 2.2.
Figure 2.1: Retail Frequent Shopper Sales Fact Group
Figure 2.2: Supporting Table
If you have two facts that have the same dimensions but one is daily and the other is monthly you need to show these as two separate fact groups! You must design only single grain fact tables.
If a dimension does not apply to a fact group, simply leave the dimension off the diagram. Be careful; do not change the location of the dimensions from group to group. We are visual beings, and we remember things not just by the words, but often by the placement on a page. Keep this in mind for your own sanity and to minimize confusion in the business community. Keep the dimensions visually in the same place of the hub-and-spoke diagram for all of the business fact groups.
The entire model can be summarized with a data mart matrix. List each fact group as a row and each dimension as a column. Place an "X" in the appropriate columns for each dimension that is used by each fact group. This provides a quick summary of the entire model and highlights your conformed dimensions. You may have more than 25 dimensions and more than 30 fact groups.
Don't let the simplicity of the BDM diagrams fool you. A well-designed dimensional model should appear obvious to the business users. This is the result of many hours of hard work.
Please note, this approach and diagramming technique has been trademarked by StarSoft Solutions, Inc. If you use this approach, please include a reference "Trademarked StarSoft Solutions, Inc." somewhere in the documentation. There is no need to contact me to ask for permission, nor are there any licensing fees.
I will continue discussing the business dimensional model in future columns. I will share the process to develop a BDM, advanced notation and how to translate it into a logical and/or physical design.
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
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access