Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Developing a Dimensional Model w/ conformed dimensions and facts

ocash

Conformed Dimensions and Facts

Data marts are often developed to represent important systems within a company.  Over time additional data marts are added, and eventually there is a desire extract data across multiple marts.  Extracting data across data marts can be cumbersome and some times impossible if the data marts were not designed to share common dimensions, also known as conformed dimensions. Ideally since the dimension criteria would be the same for each data mart, selecting data across multiple marts would be as routine as selecting data from a single data mart.

Going hand and hand with conformed dimensions, conformed facts involve standardizing facts across multiple marts.  Adopting conformed facts eliminates the ambiguity of having facts that possess the same name but have different underlying calculations.

In this blog we use simple data marts to demonstrate the use of both conformed dimensions and facts but the same concept applies to EDW’s and AS Cubes as well.

Now, to see how conformed dimensions and facts are applied during development lets start at the beginning with our ER diagram.

Entity Relationship Diagrams

Creating an ER Diagram before developing a database system forces you to have a complete understanding of the system you are developing.  The process of creating the ER diagram forces you to identify the key components needed to represent the business as well as the relationships between those components.  Once you develop an adequate entity relationship diagram you can use that diagram to create a dimension model that takes advantage of both conformed dimensions and conformed facts.

Figure 1 (Above)

Let’s take the following business scenario as an example.  We are commissioned to design a data warehouse that models a small business with three departments that track store sales, online sales, and sales forecast.   An ER diagram representing each system is shown in Figures 2,3 and 4.  Each ER Diagram contains three basic components: entities, attributes, and relationships.  An entity can be defined as a person, object, place, or event for which data is collected, and it is represented by a rectangle.  An attribute is the data that is stored about a specific entity, and is represented as text within an entity.  A relationship describes how two or more entities interact with each other.  Relationships can be thought of as verbs, like Customer purchases a Product, and is represented as a line connecting two or more entities. In our ER diagrams we use crow’s feet notation, where the crow’s feet indicate cardinality.  Figure 1 is a brief description of cardinality notation.

The ER diagrams below describe the Store Sales, Online Sales, and Sales Forecast systems in our business scenario.  The caption for each figure describes each ER diagram in detail.

Figure 2 (Left) The Store Sales System consists of five entities: Customer, Product, Store, Sales Person, and Order.  There is a one-to-zero or many relationship between Customer and Order, as well as between Store and Order.  There is a zero or one-to-many relationship between Order and Product. There is a many-to-many relationship between Sales Person and Store.

 

Figure 3 (Left) The Online Sales System consists of four entities: Customer, Product, Store and Order.  There is a one-to-zero to many relationship between Customer and Order, as well as between Store and Orders.  There is a zero or one-to-many Orders and Product.

Figure 4 (Left) The Sales Forecast System consists of three entities: Store, Sales Forecast, and Product Department.  There is a one-to-zero or many relationship between Store and Sales Forecast. There is a one-to-many relationship between Department and Sales Forecast.

Convert an ER Diagram to a Dimensional Model

Now that we have an ER model of the three systems we need to convert that information to a form that is more useful to the data warehouse world.  We can use our ER Diagrams to develop dimensional models.

When creating our dimensional models from an ER diagram we want to:

  • Create central fact tables
  • Identify which facts will be included
  • Add surrogate keys to the dimension tables
  • Promote foreign keys in related tables to the fact table
  • Add the time dimension

We also want to examine our ER diagram and find common entities that the systems share.  For example, all three systems have a Store and Product entity.  The Store Sales and Online Sales systems both have a Customer entity.  The common entities between each system should be modeled as conformed dimensions.  The concept of conformed dimensions refers to multiple data marts having dimensions that are common to all.  Having conformed dimensions allow us to analyze facts across two or more data marts.

In addition to having conformed dimensions the system should also have conformed facts. Conformed facts involve standardizing the definition of terms across data marts.  For example the term “sales units” may actually refer to net sales units in one data mart and gross sales units in another.  Basically, the business rules governing sales values must be clearly understood, and a uniform definition of these values should be taken early on in the design and enforced.

Figures 10-12 show dimension models of the three systems.  Dimensions that are common to each system are identical, and facts with the same meaning have the same name.

Figure 10 (Left)  Dimensional model of the Store Sales System

Figure 11 (Left) Dimensional model of the Online Sales System

Figure 12 (Left) Dimensional model of the Forecast System

Related Articles

5 Comments. Leave new

I think this would be a good place to talk about what Kimball refers to as the bus architecture. It can be a useful technique to represent the conformed and non-confromed dimensions. I’ve used it before in customer presentations as a tool to explain exactly what a conformed dimension was and why they were important.

Tino, Figure 2. You have not represented Store and SalesPerson as a many to many relationship. Your representation i think will result in 1 to 1.

sry 2 say bt i am glad tht my uniersity ppl took this link and made it a question in my paper

Hi,

I could not access the images in this post. Can you please update the links to those images.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>