William wishes to thank Dan Stanford for his contributions to this month's column.
Requirements analysis is fundamental to the design and development of business intelligence. The user requirements drive decisions about the data to incorporate in the data warehouse, how to organize (dimension) the data and how often to refresh the data. User requirements also determine the type of tool required and how the user will interact with the data. Data granularity decisions should not be driven simply by the availability of data -- the users should have a need for the data beyond their current abilities.
Business intelligence requirements analysis has three fundamental steps:
- Identify the data and granularity the users interact with, or would like to interact with, via interviews.
- Develop a data model to provide the data at the appropriate granularity.
- Determine how the data will be sourced to this data model.
There are other related tasks which also need to be addressed as a result of the requirements analysis process such as: query/OLAP tool features to support the user's interactions with the data; hardware infrastructure required to load, store and provide data to the end user; source data analysis; archiving strategy; and sizing.
This column focuses on the tasks specific to requirements analysis.
Identify the Data and Granularity
The initial requirements are identified through interviews, with a representative set of end users. In preparation for the interviews, it's often useful for the end users to collect a sample of the reports they work with in reporting and analyzing their data as well as screen shots of any tools they use. In addition to the following questions, an appendix, available at www.mcknight-associates.com/downloads, contains detailed interview questions for both end users as well as the IT department.
- What are your business objectives?
- How would you interpret data set results?
- How should the data you work with be organized? Should it be organized by customer, product, geography and time? Should it be organized by account, salesperson, distribution channel and month?
- What are the hierarchies, rollups or aggregations used with these dimensions? Do customers roll up to geographies that roll up to total? Do products roll up to product groups? Do salespeople roll up to districts that roll up to regions? What types of summary reports do you work with?
- What are the measures or facts you work with (e.g., revenues, expenses, balances, variances, percent growth, percent of total)? How are they defined?
- Do you "filter" the data? Do you need data only at the top and bottom accounts? Do you review the performance of only certain types of products? Do you segment the data based on demographics?
- How often do you obtain refreshes of the data? Do you obtain them daily, weekly, monthly or quarterly? Do you need it this often?
- What tools do you use to interact with the data?
- Is the data clean?
- Do you receive the data in a timely fashion?
- Do the tools you use support your requirements?
- What types of things would you like to do that you can't do today?
- What is your tool functionality?
- What is your data availability?
- Do you spend most of your time on analysis or preparation for analysis?
The interviews should be written as a business requirements document (BRD) identifying the objective, purpose and scope of this business intelligence initiative and the data requirements as well as the functional requirements. This write-up should be validated with the end users.
In making the transition from the results of the interviews to the draft data model, it might be helpful to work through a fact/dimension table. Some practitioners call this a fact/qualifier matrix. In essence, it's a systematic way to organize the users' data requests using the columns of a grid to identify the facts the users interact with and the rows of a grid to identify the dimensions or qualifiers.
For example, if two of the information requests were to view weekly sales by product and geography, and another was to view monthly P&Ls by product and geography, our initial matrix might look like Figure 1.
Figure 1: Sample Fact/Qualifier Matrix
On further thought, we see that weekly and monthly are also dimensions or qualifiers, and our second iteration might look like Figure 2.
Figure 2: Second Iteration Matrix
On this next round of analysis, we also recognize that week and month are related and that we need to embellish the time hierarchy as shown in Figure 3.
Figure 3: Extended Time Hierarchy
We continue to work with the matrix through various iterations until we have a good understanding of how the facts are unique amongst themselves, their different dimensionalities (if they aren't different, they can "share" the same fact) and the dimensionality of each of them.
Once the business requirements document has been written up, the impact of this business intelligence initiative on the hardware infrastructure should be investigated. Will additional servers be needed? Will PCs need to be upgraded? How will the network be impacted? At this point, initial cost estimates and plans can be developed to deliver the desired end-user functionality.
Register or login for access to this item and much more
All Information Management content is archived after seven days.
Community members receive:
- All recent and archived articles
- Conference offers and updates
- A full menu of enewsletter options
- Web seminars, white papers, ebooks
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access