There are three fundamental steps in building a data warehouse for a BI solution (see Why you need Business Intelligence):
- Identify the data the users interact with, or would like to interact with, via interviews. Make sure to determine the granularity of the data that is needed, if Type 2 SCD are needed, and how often the data in the warehouse needs to be updated (i.e. real-time, daily, weekly)
- Develop a data model to provide the data at the appropriate granularity
- Determine how the data will be sourced to this data model
For the user interviews mentioned in step #1, a list of questions should be asked. Here are the questions that I like to use:
- How many users will be using the BI solution?
- What types of users do you have? (Static report users, analysts, etc.)
- How will they interact with the system? (i.e. interactive data exploration vs. static report consumption)
- Where will the content be delivered? (Web, email, mobile, portal, etc.)
- Is there a minimum response time?
- How many users will be accessing the system concurrently?
- Who are the key and/or influential users? (Their acceptance is very helpful in attaining overall acceptance.)
- What are the goals of the users in implementing BI? Why are those goals important to the business?
- What is their tolerance for error? Some groups, such as finance, generally have zero tolerance for error. Others may be willing to tolerate small inaccuracies in the numbers. Data Quality is an issue but depending on the tolerance for error, may be delayed to some degree
- What is the group’s willingness to work with BI? Are they excited or skeptical? Do they view it as a help or a threat?
- What tools will be used? Is training required or are users already familiar with the tool(s)?
- What infrastructure is in place and what is required? Infrastructure includes two components, physical and human. Do you have the servers and hardware necessary? Do you have the people and processes in place to support the solution?
- Who will be doing the development? Will it be done in-house by resident IT staff or will it be outsourced?
- Do you have a BI Roadmap which outlines short-term vs. long-term needs and provides a guideline for all BI related activities?
- 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?
- 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 data availability?
- Do you spend most of your time on analysis or preparation for analysis?
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 making the transition from the results of the interviews to the draft data model, it might be helpful to work through a bus 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.
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.