The BI Application Development Process

In this section, we describe the process of developing BI applications. The discussion centers on developing the standard reports that make up the majority of BI applications. The process for more complex analytic applications is essentially the same, with more work in the application development phase.

As you saw in Figure 8.1, the Lifecycle divides the overall application development process down into two steps: Specification and Development. We separate the steps because of their dependencies on other sections of the Lifecycle. The Specification step needs to happen as soon as possible after the requirements definition process is complete, before you forget the details. On the other hand, the Development step cannot begin until the other two tracks are mostly complete. You cannot start application development until you have determined your front-end tool strategy and selected a tool. You also need to have data available, so the data modeling and most of the ETL work need to be completed. If youre sourcing reports from Analysis Services, that database should be designed and deployed. Since the two BI application steps are so separated in time, its easiest to manage them as separate steps in the process.

Application Specification

The goal of the specification step is to capture what you learned about BI application needs during the requirements definition process in a way that can be quickly turned into real applications once the pieces are in place. Create these specifications as soon after gathering requirements as possible. The longer it takes, the harder it will be to remember the details. Its a good idea to include some of your key end users in this process of defining the applications, assigning priorities, and generally making sure you get it right. Application specification typically includes the following tasks :

• Creating a standard look-and-feel template

• Creating the target report list

• Creating a mock-up and documentation for each target report

• Designing the navigation framework

• Conducting the user review

Lets go through each of these tasks in a bit more detail.

Creating the Standard Template

People can find information more quickly if it is presented to them in a consistent fashion. If you read the newspaper at all, you are well aware of this (or at least you benefit from it). The information is grouped in categories: sports, business, lifestyle, and world news. Even though different newspapers generally carry much of the same information, each has its own format standards. You may even have had first-hand experience with the importance of standards and consistency. If your favorite magazine or newspaper ever changed its format, it probably caused you some level of discomfort. This is nothing compared to the discomfort the publications management felt in making the decision to change the format.

The front-end team is basically in the publishing business. You need to have your own format and content standards and use them consistently. Youll need standards at the portal level and at the individual document level. (We deal with the portal level in the section on navigation structure.) At the individual report level, create a template to identify the standard elements that will appear every report, including their locations and styles.

Its helpful to define the standard template before you begin listing the individual reports because the template will give you some context for defining the reports. The following standard elements need to be defined and in most cases included on every report that comes out of the DW/BI system:

• Report name : Create a clear, descriptive name for the report that communicates the contents of the report to the viewer.

• Report title: Develop standards for the information thats included in the title and how its displayed.

• Report body: Column/row layout of data, including:

  • Data justification: Right justified for numbers , left justified for row headers, right justified or centered for column headers.

  • Data precision: Dependent on datafigure it out for all your numeric fields.

  • Column and row heading format: Often bold or underlined to distinguish them from report data.

  • Background fills and colors.

  • Formatting of totals or subtotal breakout rows.

• Header and footer: The following items should be found somewhere in the header or footer. Create a standard layout, font, and justification scheme, and stick to it.

  • Report name.

  • Parameters used.

  • Navigation category.

  • Report notes: One of the most useful notes about a report is its exceptions, like Excludes intercompany sales.

  • Page numbering.

  • Report run time and date.

  • Data source(s): Generally which business process dimensional model(s) in the warehouse contributed to the data in the report, and whether that source was relational or OLAP.

  • Confidentiality statement.

  • DW/BI reference: name and logo.

• Report file name: The report definition file name is based on your standard file-naming convention. The file itself and any associated code should be under source control.

Figure 8.2 shows one way to lay these elements out on a page. The angle bracket signs (<>) and curly brackets ({}) indicate elements that are context sensitive. That is, they may be system variables , or parameters specific to the report that is being run.

Figure 8.2: Example standard template

Not all report information is displayed on the report itself. You will also need to create the following information for each report:

• User variables and other user interactions like drill downs , links, and so on.

• Report metadata, including description, calculations, derivations , author, date created, and so on.

• Security requirements, including a list or description of the security groups that can see the report.

• Execution cycle, if the report is to run automatically on a periodic basis.

• Execution trigger event, if the report is to be executed in response to a system event like the completion of the nightly HR data load.

• Delivery mechanisms, like email, web site, file directory, or printer.

• Delivery list, which is generally the name of an email distribution list.

• Standard output format, like text, html, PDF, Excel, or Word.

• Page orientation, size , and margin settings.

Observe that all of these elements are essentially report metadata . Therefore, this information should be kept in a metadata repository at some point, so it can be used during report creation or accessed by the users on demand when they need to understand more about a given report. Meanwhile, you can use a spreadsheet or text document while you are creating the specs . Metadata structures for Reporting Services are discussed in Chapter 13.

Creating the Target Report List

The first step in creating the target report list is to go back through the user interview documentation and pull out every reporting/analysis request, desire , fantasy, or hope that anyone expressed . As you make this list of candidate reports, give each report its own name and description. In addition, capture your best sense of its business value and the effort it will take to build. It also helps to make note of who asked for it, (there may be several people), and any other parties you think might benefit from it. These are the folks who will help you further define the report should it make it on the target list.

Its easiest to capture this list in a spreadsheet, like the example shown in Figure 8.3.

Figure 8.3: Example Candidate Report list

You need to understand the content, format, and priority of all these reports, as you add them to the list. The business value and level of effort are on a 1-10 scale, where 10 is high value or high effort. These need to be only relative estimates at this point. Theyre not meant to be converted directly to dollars or work hours. Its worth spending time on the names and descriptions because the better these are, the more likely youll be able to figure out what they mean later on. Part of creating the standard template includes coming up with some useful naming conventions for report types, like Topline, Trend, Time Series, Comparison, and so on. Figure 8.3 is a bit simplified in order to fit on the page. Your list will likely have more descriptive information.

Once you have a complete list of candidate reports, rank them in priority order. Group related reports that draw on the same data sources to speed the prioritization process. Conduct this prioritization with a small group of competent, interested business folks. Review the list with the group, making sure everyone understands the reports. In particular, spend some time reviewing (or assigning) the business value score. Some reports, like actual orders versus quota, might be particularly interesting to the VP of Sales, but they may get a lower priority than the reports that include only orders data because quota information may not be in the DW/BI system yet. If everyone agrees on the business value score, the rest is relatively easy.

After the whole list has been reviewed, re- sort the rows and set a cutoff point at about 10 to 15 reports down the list. This is your initial target list. Eyeball the results with the group to make sure all the reports on the target list deserve their success. Also, make sure all the reports that didnt make the target list deserved to be cut. Remember, this decision is often as political as it is logical, and just because a report doesnt make the initial list doesnt mean it wont get done. Many of these may be handed off to the experts from the departments (the power users) who were most interested in the reports.

You might need to expand the number of reports on the target list a bit, but make sure anyone who pushes for more reports understands that each report is a couple of days more work. Perhaps they would be willing to work on building those additional reports?

THE BASIC BUSINESS ANALYSIS CYCLE

Understanding how analysis is used to support decision making is a key part of being able to create useful reports. The BI team must be able to make meaningful contributions to the business, not just build reports from specs.

Business analysis usually happens in a repeating cycle. The following figure shows a progression from Plan to Implement to Monitor to Assess and back to Plan. The analysis cycle for any given business event can begin in any of the processes although it rarely starts in the Implement process.

PLAN

When the analysis is proactive, the cycle typically starts with the planning process. Planning involves gathering data from various business processes and external sources to build a business model for a new product, service, program, or any significant business activity. This data could include costs and revenue growth curves for existing, similar items; time to market estimates; seasonal factors; or any other relevant measures. Much of this input is based on historical data that can be found in the BI system. The planning process is often ad hoc in nature, driven by the specific attributes of the new program.

IMPLEMENT

Once the program is approved, the various business processes that will make it come to life kick in. These are the actual tasks of marketing, manufacturing, field training, and so on across the organizations value chain. Most of this activity happens outside the scope of the BI system; however, it is critical that a BI representative be involved to help implement the program with the appropriate measurement systems in mind. This is particularly true in e-commerce when it often happens that a company rolls out a new online service and has no real way to measure its usage. They can tell how many people sign up for the service and how much revenue it generates, but the actual usage data may be buried in the web logs, or not even really collected. The implementation process usually does not involve a significant amount of analysis.

MONITOR

Everything in the ongoing business must be monitored , but most of the ongoing activity is routine and can be managed by exception. Even monitoring new program activity is not much more than answering the question What is happening? in the early stages. These numbers will get a significant share of management attention, but it is difficult to understand the implications of the numbers and to develop meaningful trends until some amount of time has passed. It usually makes no sense to react to a number that is less than expected on the first day. It may take longer than planned for the market to find and understand the new product. Alternatively, the initial marketing campaign may have been mis- targeted . The monitoring process for ongoing activity is the primary role of standard reporting.

ASSESS

Once enough data is available, it becomes possible to assess the impact of the new program. It requires a significant amount of exploration to root out the interesting relationships in the data. This may even extend beyond the ad hoc tools to include data mining functions.

All too often, the analysis cycle actually begins in the Assess phase when the business is reacting to an external change. For example, if a competitor cuts the price of its product, the first response of a clever organization would be to assess whether or not the price cut had any impact on market share. Even if it does, further analysis may show that it makes sense to hold the price and lose some amount of market share.

In any case, once the impact of the event is understood , the organization can plan a response, which takes us back around to the top of the cycle. If appropriate analytical applications are available, they can help make the assessment process much easier. Otherwise, assessment is primarily ad hoc in nature.

 

Creating Report Specifications and Documentation

It may sound obvious, but during the application specification step, you should create a specification for each report. The report specification consists of the following components :

• Report template information as outlined previously

• Report mock-up (optional, but most helpful)

• User interaction list

• Detailed documentation

The report mock-ups are a great way to communicate the content and purpose of the reports. Beyond the mock-up itself, create two additional items to complete the spec: a user interaction list and detailed documentation. Well go through the report mock-up first, and then discuss the user interaction list and the detailed documentation.

Report Mock-Ups

The example report mock-up shown in Figure 8.4 is based on the standard template we created earlier. The difference is weve filled in the report structure for one of the reports on our target list (you can see another report mock-up example in Chapter 9).

Figure 8.4: Example Product Topline Performance Report mock-up

Its helpful to indicate several user interaction functions on the mock-up. For example, the double angle bracket signs (<<>>) indicate drill-down capabilitiesthat is, a user could click on an entry in this column or row header and drill down to the next level of detail. Weve found it useful to indicate the following functions on the mock-up. You may have additional needs, or prefer to use other indicators.

< >	User-entered variable
<<>>	Drillable field
{ }	Application entered variable (either from the system or metadata)
\\ \\	Link/URLlink to another report or documentation source
( )	Page or section break field
[ ]	Report template comments

User Interaction List

Although the function indicators on the report template tell you what kind of interaction is possible, they dont tell you what that interaction is like. The user interaction list identifies the nature and degree of interaction a user may have with a given report. This can range from None for a completely static, pre-run report to an extensive list of fields and behaviors for a fully interactive report. Capture the basic interactions: variable specification (and its sub-types: user entry or user selection), drill down, and field addition/replacement. Figure 8.5 shows an example user interaction list for the Product Topline Performance report shown in the mock-up in Figure 8.4.

Figure 8.5: Example user interaction list

Figure 8.5 shows a row on the user interaction list for each function indicator on the report mock-up. Include enough information so that someone whos building this report can use the mock-up and the user interaction list to do the job.

Most front-end tools provide a means for interacting with the user to gather user-specified values prior to report execution. In Figure 8.5, items 3 and 4 come from the initial prompt screen. Some tools are fairly rudimentary in their control choices; others allow you full control over the user interface. If your front-end tool has a great deal of flexibility, create a standard user interface template for user-provided values.

Detailed Documentation

Create detailed documentation to collect the information you havent captured elsewhere. Note the report category, the sources of the data in the report, the calculations for each column and row, and any exceptions or exclusions to build into the query. Additional information about the report might include creation and modification tracking, and an expiration date, if the report has a limited useful life. A good place to keep this is at the end of the user interaction list.

Designing the Navigation Framework

Once you know which reports to build, you need to categorize them so the users can find the information theyre looking for as quickly as possible. We call this organizing structure the navigation framework , or navigation hierarchy . Ideally, this structure is self-explanatory. That is, anyone who knows something about the business can generally find what they want fairly quickly.

The best approach weve found, and this may sound obvious, is to organize the reports by business process. If someone knows your business, even at a cursory level, they will be able to find what they need. There are a lot of additional design principles that come into play here, but we will leave them to the next chapter when we describe a simple navigation framework for Adventure Works Cycles.

Conducting the User Review

Once you have a solid set of application specs in place, it is extremely helpful to go over them with the user community. This design review covers a lot of ground. In it, you need to validate your choice of high-priority applications and test the clarity of the specificationsdo they make sense to the business folks? It involves users in the process, emphasizing their central role and developing their commitment. It also can keep people engaged in the project by giving them a sense for what will be possible in just a few short months. Leave time in your project plan to make any modifications to the specs that come out of this design review.

Once the specs are complete and have been reviewed, you can put them on the shelf, so to speak. But keep them close by because theyll be useful if you do any front-end tool evaluations. Whatever tool you choose should be able to easily handle the range of reports in the initial report set. Other than that, there isnt much more you can do with the specs until youre ready to begin the report development process.

Application Development

Its difficult to start the application development process before a lot of the DW/BI system infrastructure is in place. You need a reasonably representative subset of the data in the presentation database in the final model. The front-end tools must be selected and purchased. And of course, you must have completed the BI application specifications. Typically, all of these events dont occur until some time close to the system test process we describe in Chapter 14. As a result, we usually do the application development as part of the system testing. This makes sense because these reports are excellent test cases for several reasons: They represent a range of analyses, they are typically more complex than fake test reports, and they are real-world in that they are based on how users want to see the data.

The application development process itself is a fairly typical development effort. We usually cover the following steps within the Prepare-Build-Test-Rollout framework:

Prepare

• Install front-end tool(s).

• Set up end user data access security system.

• Create business metadata.

• Capture process metadata.

Build

• Build applications.

• Create the BI portal.

Test

• Unit test.

• System test.

• User test.

Rollout

• Publish.

• Monitor and maintain.

Lets examine each of these tasks in a bit more detail.

Install Front-End Tool(s)

Each front-end tool has its own system architecture and technical complexitiesgetting it functioning in good working order is often less than trivial. Installation might include components located on a whole range of application servers, desktops, web servers, and even in the database server. We described some of these options for Reporting Services in Chapter 4. See your administration manuals or contact your vendor if you are using any other front-end tools.

Set Up End User Security

As we discuss in Chapter 12, you should push for a security policy thats as simple as possible for the DW/BI system. Encourage the decision makers to put information in the open categoryavailable to everyone who can access the DW/BI system. Data is valuable only in context, and the richer the context, the more valuable the data.

There are legitimate reasons to limit access to certain data, especially personal information like credit card numbers or health care data. But often the drive to protect data is political: A sales manager doesnt want his colleagues to see how hes doing, for example. Data is an organizational asset and should be accessible to anyone who can use it to benefit the organization.

The ideal approach to security from the BI system perspective is to let someone else do the heavy lifting . As much as possible, leverage your IT organizations centralized security system when building your BI organizations security system. In a Microsoft environment, this typically means using Active Directory to create users and groups. Then the SQL Server database objects, Analysis Services database objects, Report Server objects, web pages, and front-end tools all work from the centralized security service. Users log on once and can move seamlessly from tool to tool and server to server without even knowing it.

We talk about some specific Reporting Services security techniques in Chapter 12.

Review Business Metadata

Business metadata is the descriptive information about the contents of the standard reports. You should already have the report-related metadata in the specs you created back in the application specification step. Pull these out and review the names, descriptions, titles, and other business metadata elements to make sure they still make sense. See Chapter 13 for more information on business metadata.

Capture Process Metadata

Process metadata is information about the execution of various processes in the DW/BI system. With regard to reporting, you need some way to monitor usage and performance of your standard report set. At a minimum, you need to know what reports are being used, and how long they take to execute. Over time, if certain reports are never used, you may want to take them out of the portal. If other reports are used often, and they do not perform well, examine them carefully to see how you can improve that performance. But if you dont capture usage statistics, you wont have any idea where to start.

Most of the front-end tools have their own logging system that you can tap into to monitor and tune the reporting process. Chapter 13 gives some basic metadata structures to hold Reporting Services process metadata. Make sure your logging mechanisms are in place and working properly. The earlier you do this, the better.

Build Applications

Finally, you get to have some fun! Actually building the applications takes relatively little time compared to the rest of the process. As we mentioned earlier, it also provides a great opportunity to build relationships with your user community. Set up a temporary lab (it could be the training room) and dedicate it to application development for as long as necessary. Bring in a group of power users to help build out the initial target list of reports. If you have managed expectations early on, these users should be excited about participating. They get early access to the tools and the data. Bring in donuts and lunch , and maybe have special hats or T-shirts made up. Start the day by conducting a brief training session on the tools, and reviewing the specs. Then start building. Encourage lots of interactioninteraction is the rationale for the labso everyone can learn from one another. Keep at least two lists of issues and difficulties: one for the data and one for the front-end tool.

If this is your first experience developing reports with your front-end tool, bring in an expert as a consultant toward the mid-point of the development process. Go through your list of issues and have the person show you how to solve or work around the problems youve encountered . You may have to actually pay for the consulting unless you negotiated it as part of the software purchase. Either way, get help from an expert. Its worth it.

Create the Navigation Portal

At the same time you are building the initial set of reports, you need to be building them a home as well. The question is what technology container you will put them in to present them to the user community. The simple answer is the web, but that doesnt narrow your choices too much. You could build your own web site from scratch, use a portal technology to give you the basic delivery infrastructure, or use the delivery vehicle provided by your tool of choice. The navigation portal is based on the navigation structure we outlined during the report specification process. Chapter 9 shows a simple example of a navigation portal for Adventure Works Cycles.

Unit Test

Test each report. Have someone else go through the report piece by piece, testing the calculations, report layout, user inputs, and results. If possible, compare the results to those from another, independent source from outside the DW/BI system. We cover testing in Chapter 14 when we describe the deployment process.

System Test

Once the report is deemed to work on a stand-alone basis, see how it works as part of the production process. Depending on your front-end tool, and the complexity of your warehouse environment, there could be a large number of elements that need to be tested . If it is supposed to run at a certain time or in response to a certain event, does it in fact do so? Does it publish its results properly? Can users see updated results where they expect to see them: on the web, on a file server, or in their email? If there is a problem with the process, do notifications go out on failure? Is there some indicator of successful completion, like an entry in the log files?

If you are supporting a large user community with these reports, you should plan for some stress testing as well. There are tools that will simulate multiple simultaneous users. Set them up and see what happens. Include ample time in your project plan to tune these applications for large user communities.

User Test

If users were involved in the development of the reports, this step may not be necessary. However, if the users have not yet seen the reports, or youd like to get reactions from non-technical users, include a task in your process to give them a chance to inspect and approve them. This may take the form of a demo to a group of users with time for questions and answers. Or, it may be something users can do from their desks, with a simple web survey form or email response. Some IT organizations require a formal signoff from a user representative. This makes us nervous. While it can be useful to have an official acceptance mechanism, it is often an indicator of a lack of trust between the two groups. We usually document the acceptance by marking the application development task complete in a project status meeting with user representatives present. Sometimes well send an email to the business project lead to specifically note the completion of the application development and verify they are happy with the results; this is usually framed in celebratory terms.

Publish

The initial release is primarily a public relations process. You need to notify users that the BI portal is now available and give them a link to check it out. We encourage you to hold short, one- hour orientation classes to teach people how to use the BI portal. If you have a good navigation framework, this class is unnecessary, at least for its stated purpose. An overview session is still extremely valuable because it allows you to educate usersincluding senior managementabout what data is available, what future plans are, where to go for help, and what they should expect from the BI system. In other words, this might be your only chance to indoctrinate them.

As soon as these reports are made available, users will begin to think of many other reports they would like to see. There are several ways to handle this. First, manage expectations up front by getting buy-in on the initial set during the specification process. This is much easier when you involve users in the definition of the original report set. Second, remind users they can learn to create their own reports. Have training classes scheduled as part of the deployment and encourage users to learn how to help themselves . Third, direct users to their departmental experts who may be able to create the reports they need. Fourth, if their requests look valuable, consider adding them to the candidate list. They may sort to the top in the second release. Finally, allocate hidden resources right from the start to create some of these additional reports.

As power users and departmental experts develop their own reports, they will want to share them with their co-workers . You will need to provide a place for them to do so, but it should be clearly identified as departmental reports. They do not carry the official BI system seal of approval.

These strategies allow you to be flexible and responsive at a crucial, early point in the users experience with the BI system. Remember your motto: Were from the Data Warehouse, and were here to help.

Maintenance

Maintenance is covered in detail in Chapter 15, but this section presents a few things to keep in mind. The first job in maintaining the BI applications is to make sure the existing report set is available and continues to perform as expected. There are plenty of reasons why their performance might degradechanges in the database, increased usage, or other processes running on the report server. In one case, we had to build out a separate report server for our standard reports because the users were scheduling hundreds of their own reports to execute overnight. The server was limited to running eight reports at a time, so the standard reports were waiting in the queue every morning. We could have simply raised the priority for the standard reports, but we didnt want to seem like we were pushing the users around.

In addition to performance, you need to monitor the content of the standard reports. Over time, many will become stalethey are no longer useful from a business perspective. Sometimes the reports are still useful, but some of the definitions have changed. The business has refined its understanding of the data and newer reports reflect this, but the older reports need to be updated.

The front-end team will also be adding new reports all the time. In fact, you should consider every request for an ad hoc analysis to be an opportunity to add a new report to the library. The more reports in the portal, the more likely a user will find one that provides needed information, as long as the portal is well designed. The price of this wealth of information is that the maintenance job grows as the report library grows.

Plan to have a report spring cleaning every year or so. Go through the whole library with the usage statistics data and see what gets used and what doesnt. Look at each report to make sure it still works and that it still has value. Involve users in this process to help assess the value of questionable reports. This is definitely one of those activities like cleaning the refrigeratorits unpleasant but important. If you dont do this, the BI system will begin to lose credibility with the users because more and more of the reports are out of date, or dont work. The portal begins to look like an abandoned property with broken windows and unsightly trash piled in the side yard. Not the image youre looking for.