The MicrosoftВ Data Warehouse Toolkit: With SQL ServerВ 2005 and the MicrosoftВ Business Intelligence Toolset

Developing BI applications sounds like a lot of work. Before we dive into the details of creating these applications, its worth reviewing the value you get from them to help justify that work. As it turns out, they add significant value in several ways.

• Business value: The process of identifying and creating BI applications based on business requirements almost guarantees that you will provide something of significant value to the business.

• Broad access: These applications provide data warehouse access for a broad, important user community. Remember, 90 percent of your knowledge workers will not develop the technical skills and data acumen needed to build their own reports . You must provide them with a means to get the information they need to make informed decisions.

• Early impact: BI applications demonstrate the value of the DW/BI system from day one. Business users across the organization can take advantage of the initial business process dimensional model as soon as you deploy it.

• Data validation: BI applications help validate the data warehouse content because they represent real-world analyses that bring together dimensions and facts in a way that hasnt happened prior to this point. That is, the ETL developers and data quality folks have been mostly focused on tables and columns . The BI applications bring the whole business process dimensional model together from a business perspective. For example, a report that compares sales by product type, gender, and store format would pull from three different dimensions: product, customer, and store. If youve done the underlying development of the dimensions and facts correctly, there should be no problem. Typically, you will uncover some data irregularities at this point.

• Query performance: Similar to data validation, the BI applications generate more complex queries than the basic testing that has taken place prior to this point. So much so, you should capture the SQL and MDX queries from the BI applications and use them to generate some of the ongoing performance metrics.

• Tool functionality: Because the BI applications are real business analyses, it is important that your front-end tool be able to handle them easily. Building BI applications provides an opportunity to test the ability of the tools to meet the business needs. The team can bring in a development expert from the vendors consulting organization (surely, you negotiated this as part of your purchase) to show them how to work around some of the rough edges of the product. In the worst case, the team may need to identify an alternative approach to create certain types of reports. At least this happens during development and testing, and not after the full rollout.

• Relationship building: Including your power users in the BI application development process is a great way to keep them excited about the DW/BI system and motivated to climb the learning curve. The users get early, supervised training on the reporting tool and the data, and the team gets extra help building the applications. (So maybe it isnt so helpful in terms of actually getting reports built, but the relationship part is worth the extra effort.) Make this process more fun by setting up a development lab where users and team members can all work and learn together. Bring donuts .

• Feedback: Finally, building BI applications helps the DW/BI team experience the impact of their design decisions. Many of the tradeoffs that were made during the design phase surface at this point. For example, it is now possible to estimate the full cost of decisions about pre-calculating a value versus letting the users calculate it in their reports. Consider having your data modelers and ETL developers participate in creating some of the BI applications. Experience is the best teacher.

We hope you were already planning to include BI applications as part of your DW/BI system development effort and this section has served only to highlight the wisdom of your plans. Now that you are appropriately motivated, lets dig into the application development process.