The ability to learn and adjust on an ongoing basis, a key benefit of agility, is extremely helpful for business intelligence and data warehouse initiatives.
If you’ve worked in business intelligence for any length of time, then you’ve experienced the ongoing discovery that happens with business intelligence efforts. Once you get a feel for the kind of information that business intelligence can provide, you come up with all kinds of things you can use the data for that you didn’t think of initially.
How can you experience the benefits of agility with business intelligence? In this post, I’d like to take a look at three ways you can use agility to improve the effectiveness of your business intelligence efforts.Express Your Outcomes as Questions and Decisions
Every initiative is more effective if you can describe success in terms of the outcomes you want to reach instead of the outputs you want to deliver.
With business intelligence initiatives, those outcomes are best described as questions you want to answer or decisions you want to make. When you describe outcomes in that way, you will experience the following benefits:You can deliver something quickly that provides your customers with value as you build out the rest of your data warehouse. When you go through a full pass of all the steps to deliver a data warehouse on a small subset of the overall data warehouse, you can learn some things that can be applied to the rest of the data warehouse. You can focus on only the data you need and avoid using data that will not be used, saving analysis, development and testing time. By organizing your work around specific outcomes, you can get a more useful indication of progress.
I’ve found that Socratic questioning is an effective way to uncover those questions and decisions. I recently wrote a post on BA Times that describes how you can elicit the questions users want to answer and the decisions they want to make.
You can capture these questions or decisions in the form of a user story (As a <who>, I want <what> so that <why>), job story (When <situation>, I want to <motivation> so that <outcome>) or other template of your choice. The format really doesn’t matter.
What does matter is that you identify distinct questions and decisions (outcomes) that are satisfied by big chunks of functionality you want to deliver (output). You can call the big chunks features, epics or whatever term you prefer--again, it doesn’t matter. I’ll use the term “big chunk” for the rest of this article.
By organizing your work around each big chunk, you can deliver the functionality that delivers the data formatted in a way necessary to answer a question or make a decision. If you focus on one big chunk at a time, deliver it to your customers and then get feedback on that big chunk, you can use that feedback to revise your approach to subsequent big chunks. You may decide to deliver different big chunks, the same big chunks in a different order or in another way altogether.
Whatever changes you make, you’ll find that you more effectively deliver the functionality necessary to answer questions and make decisions, and you can avoid unnecessary work.Incrementally Deliver Value
A common assumption about business intelligence is that you must get all the data warehouse functionality to its final before it can provide any value. However, you may find that you can provide value in many ways before getting to the final desired form of data access.
Even though your user ultimately wants to get to a daily report with all the data they need, you may find you can manually extract specific data from the source systems and place it in a single table that your users can manually query to get the answers to their question.
Once you verify that you have the correct data, you can automate the process, organize the data in a more user-friendly manner and perform necessary transformations to the data as you extract it from the original source systems and provide it to your users.
In his book “Agile Data Warehousing,” Ralph Hughes provides a way to slice up the big chunks of business intelligence work into smaller improvements to the process as described above. There are a variety of characteristics of both the user-facing data view and the back-end data storage that you can gradually improve to achieve a level of functionality that provides the most value for your users.
Here are a few of Hughes’ suggestions:Data View (Front End) Decomposition
How frequently is the user’s data view updated?
How can the user access the data?
● Single Table Access
● Mobilized access (linked tables)
● Defined navigation
● Picklist supported
What triggers a refresh of the data view?
● On demand at workstation
● On demand posted to server
● Scheduled refresh on server
What transformations (if any) happen between data storage and the data view?
● Direct data transfers
● Applied business rules
Data Storage (Back End) Decomposition
How frequently is data from the original sources updated?
When a refresh from the original sources occurs, how is the data updated?
● Direct Link
● Error recycling
● Schedule driven
● Manually invoked
● Incremental loads
What transformations (if any) happen between the original sources and the data storage?
● Direct data transfers
● Applied business rules
What layers in the architecture are updated when a data refresh occurs?
● Linking history tables
● Linking tables
● Fundamental tables
● Reference tables
● Fact tables
● Historical dimension tables
● Non-historical dimension tables
● End user access views
Offer access through a controlled querying environment where links between tables are pre-built
Provide data in a report that the user can refresh manually
Automatically deliver the report to the user on a regular schedule
The idea is to gradually improve on a specific characteristic one small chunk at a time. For example, you may start off with users, manually querying directly against the data storage using SQL. You can then improve this experience with small chunks that introduce each of the following changes:
After each step, get feedback from the users to determine if you have met their needs or if additional improvements are necessary.Adopt Different Technical Practices
To effectively deliver data warehouse functionality in an incremental fashion, you’re going to need to adopt some different technical practices than what you may be accustomed to from your past experience working on business intelligence initiatives.
First, it’s important to remember that you will change your database structures throughout the process of building the data warehouse, and that’s okay. To make that process as smooth as possible, it’s best to create Data Definition Language (DDL) scripts to make changes to the data structures as well as Data Manipulation Language (DML) scripts to update the data in those structures. Then, include those scripts in the version control along with the processes you use to run your ETL processes.
In this way, you have clear means of moving from one version of your data warehouse to another and you have a straightforward way to automate that process.
Another helpful practice is to create tests to include with the DDL and DML scripts to confirm that the changes to the database structures and the initial loads of data are performed properly.
You may also find it helpful to extend a test-driven approach to all the code you write for the data warehouse, including not only the scripts to make changes to the database structures, but also the processes you use to update the data warehouse on an ongoing basis.
Ken W. Collier’s book “Agile Analytics: A Value-Driven Approach to Business Intelligence and Data Warehousing” contains a great deal of information on how to apply techniques such as Test-Driven Development to a data warehousing environment.Agile Business Intelligence Helps You Learn
The main advantage of using an agile approach with your data warehouse is the ability to constantly refine your understanding of what your customers need and to make sure you spend your time on valuable work, delivering only the data that your customers need at the frequency they need it, organized in a way that is most useful for them.
Do you have experience approaching business intelligence in an agile fashion? Leave your experiences or questions in the comments section below.