How do you gather requirements for a Data Warehouse/Business Intelligence project ?
Typically, on a BI project, if you ask a business user, ‘what do you want’ you will get one of 2 responses.
- I don’t know.
- I want everything.
Which are, effectively, the same thing…
As much as it pains IT people to hear these responses, if you think about it, they are actually the right answer. From day to day, a business user, especially any analyst in a business, doesn’t know how they will want to view and analyse their data. They are responding to changing business environment on a day to day basis. The BI team’s job is to deliver the capability to analyse data in a variety of ways to these business users.
But how do you deliver everything? This is where the concept of Agile Data Warehouse development becomes relevant. The Agile Data Warehouse development process delivers bite sized pieces of the Data Warehouse to end users, on a regular basis. You can’t deliver ‘everything’ all at once but you can over time. The trick is to ensure that you have an overall plan (i.e. a Data Warehouse Bus Matrix) and prioritize which pieces are built in what order.
Gathering requirements for a Data Warehouse project is different to Operational systems. In Operational systems you can start with a blank sheet of paper, and build exactly what the user wants. On a Data Warehouse project you are highly constrained by what data your source systems produce. Therefore letting an end user go wild with all kinds of esoteric requirements can lead to horrible disappointment.
There is no silver bullet. Like on most projects, you have to work with what’s in front of you. Organisations undertaking BI projects all start with very different levels of vision, understanding and expertise. Each situation calls for a unique approach. However, one thing we find is true is that the requirements gathering process is as much an education process as a requirements gathering exercise. Most business users are unaware of the possibilities of BI, and will often describe their requirements in terms of what they have had before (e.g. A list all sales for June that I can import into Excel…)
Building a Data Warehouse is mostly about building capability, rather than delivering specific report outcomes. It’s a mistake to take a ‘Business Intelligence’ requirement (i.e. this Report, that report… etc) and build a data Warehouse just to satisfy the reporting requirement. Tomorrow, a new requirement might arise, which would fundamentally change the Data Warehouse (Usually the detail level, known as the grain, of a Fact table). Having a process which lacks flexibility is a show stopper. In fact, end users in surveys, sight lack of Data Warehouse flexibility as a major inhibitor to the usefulness of Business Intelligence in the enterprise.
Instead it’s important to continually model reality; business processes, business entities, and augment that with measures, KPIs etc.
Over time we have found a simultaneous Top down and Bottom Up approach works best. However you need to adjust your process based on the situation in front of you.
- Top Down. The Top down process involves studying the organisation, how it functions, and how it measures success. To start, we like to get copies of the Org Chart, the Annual Report and Strategic Plan. We examine existing reports to identify measures, facts, attribute and dimensions, and then sit down with as many managers and key business users as possible to ask open ended questions like:
- What are the functions of your dept.
- What are the Key Performance Measures. Is it Sales Margin, Revenue, Forecast variance etc.
- What is the definition of success for your dept.
- What are you trying to improve.
- How do you identify a problem.
- How do you know if you are improving.
- What don’t you know.
The end result is a presentation of a vision of how BI can benefit and improve the business.
- Bottom Up. The bottom up approach involves Data Driven Design and an agile Data Warehouse development. To achieve agile development we use Dimodelo Architect our Data Warehouse Automation Tool. In fact, the need to rapidly iterate through versions of the Data Warehouse in order to educate, gather feedback, and produce better outcomes for our clients is what drove us to create Dimodelo Architect. In a matter of days we can produce a version of the data warehouse, present mock ups to key end users, gather feedback, educate, and move forward to the next version.
Another approach we like is that proposed by Lawrence Corr in his book Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema. It is a step-by-step guide for capturing data warehousing/business intelligence (DW/BI) requirements and turning them into high performance dimensional models in the most direct way: by modelstorming (data modeling + brainstorming) with BI stakeholders. It describes BEAM✲, an agile approach to dimensional modeling, for improving communication between data warehouse designers, BI stakeholders and the whole DW/BI development team.