Agile Data Modeling

This will be an introduction to Business Event Analysis and Modeling (BEAM); the agile data modeling approach developed by Lawrence Corr. One blog post cannot begin to cover the depth of this approach. To dive deeper, see his book Agile Data Warehouse Design.

Agile Data Modeling Key Principles:

1. Focus on Business process rather than reports
2. Avoid data dependency
3. Collaborative modeling engages stakeholders
4. JEDUF –Just Enough Design Up Front
5. Automated Testing and CI support agile methods

Define the Business Process

We define the business process by asking questions. For an agenda, we will use the 7 W’s.

We can then use the 7 W’s to interview the Subject Matter Expert (SME)

Who does what?
When?
Where?
How Many?
Why?
How?

For our example, we will use a bicycle shop to model the data warehouse. For those of you who are familiar with the sample SQL Server database Adventure Works and Adventure Works DW, we will use that as our answer key. But, you can picture this against any retail store.

Example: Who Does What? Customer buys a bike. Employee sells a bike. Also, Store stocks products. Can be any W.

During our discussions with the SMEs, we will fill in a spreadsheet. Everyone is comfortable reviewing information in this way and it will layout nicely the area we want to cover. After the SME has answered the questions, you will start to fill in the spreadsheet like this.

Each example by the SME fills in a row. Joe buys 2 bottles from Mike on 5/11/15 at Store 123.

As the modeler, you are determining tables from the second row. More detail will be added later. Your first pass at a logical model may look like this.

Now you can dig deeper to get more information about the dimensions you will be modeling. Use the following questions to drive the conversation.

1. What identifies each Customer/Product/(Dim)?
2. What do you want to report on?
3. What do you want to group on?
4. Can Customer have more than one address?
5. Is there a lookup table for this code?
6. Mandatory?
7. Missing?

These questions will help identify reporting needs as well as physical design attributes. After answering these questions, we can come up with a query model. This will be used to continue the discussion with the SME to make sure we are capturing their needs.

This format allows us to discuss report filters and groupings with SME. A requirement may be to see Philadelphia mountain bike sales by company over the last 13 months. We know we are going to capture that information. And, as a modeler, we can start thinking about hierarchies in the database.

The second pass at the logical design may look like this.

Now let’s compare our logical model to the Adventure Works DW.

Conclusion

As you can see, our logic design closely matches the final version of the data warehouse. There is still a lot of work to be done to get to the physical model; but, you have a great starting point in a very short amount of time.

Additional reading

Agile Data Warehouse Design by Lawrence Corr.
http://www.agiledata.org/

Originally published at www.capaxglobal.com.