Agile Data Modeling - Capax Global

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.

7W's Data Example
Who People & Organizations Employee, Customer
What Things Product, Service
When Time Date, Calendar
Where Locations Store, Address
Why Reasons & Causality Promotion, Weather
How Transactions, Status Order ID (deg dim), Status
How Many Measures, KPI's Revenue, Quantity (Facts)



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.

 

Buys

Sells

On

at/from/to

with/for

for

in/using

Customer

Product

Sales Person

Sales Date

Location

Quantity

Reason

Manner

Who

What

Who

When

Where

How many

Why

How

Joe

Bottle

Mike

5/11/15

Store 123

2

Walk in

Order

Sally

Helmet

Dan

5/12/15

Store 456

1

Coupon 321

Order

?

Jersey

Karen

5/12/15

Store 123

2

Walk in

Order

 
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.

 

Order Quantity.png

 

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.

 

Query Model.png

 

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.

 

Order.png

 

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

 

AdventureWorksDW.png

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/