Introduction to Agile BI: Agile Data Modeling

Part 4 of 4

Bob Blackburn
2015-09-23

In part 4, we conclude this series with a brief introduction to agile data modeling. Read part 1 reviewing of the key differences between Agile and waterfall here, Part 2 modifying agile methods for a BI project here, and Part 3 converting a traditional development team to using agile methods here. 

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.

7 W’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, KPIs
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 ware. 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.



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.



As you can see, our logical 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.

Agile Data

Labels: ,