Many companies let technically savvy end users — “power users” — develop queries in BW. In fact, power users routinely develop their own new queries in the BW production environment. The complexity and technical expertise required in the R/3 production environment prevents this practice. However, businesses increasingly feel it is worth the risks associated with letting folks outside of the BW team create some of their own tools in the data warehouse’s production environment to meet the demands and pressures of daily operations.

Handing off some developmental chores lowers the overall cost of BW ownership by reducing the BW and Basis teams’ workload. User acceptance is immediate for those who create their own queries, so certain rework activities go away and query lead times are dramatically shortened. This productivity gain accelerates the growth of the data warehouse and increases the return on investment in BW.

Typically, the development environment is the place for creating queries, not in production. Developing in the production environment can lead to transport problems associated with unsynchronized systems, which can affect testing new product features and software upgrades. In addition, working in production lacks the control and standardization offered in the development environment. Taking such risks, however, may be worthwhile because creating queries in production slashes lead times, enhances “customer satisfaction,” and provides users with more appreciation for the effort involved in query development.

You must scrutinize several areas before allowing users to create queries in the BW production environment. Like most IT policies, these are typically matters of personal preference. I will provide you with the key issues to keep in mind before allowing users to create their own queries in the production environment, such as:

• What are the risks?
• What queries should users develop?
• How do you identify competent users?
• What resources are needed?
• What naming conventions, training, and documentation should be used?
• Can you prevent system degradation?
• How can you synchronize the development and production environments?
• What are the security issues?

Key Risks

Clearly, the risks associated with power users creating queries can outweigh the benefits if the appropriate controls and processes are not in place. Allowing end users to create queries in the production environment poses a real threat to the performance of your system, and, of course, the increased access to data adds to the security risks.

The production environment does not provide the degree of control and standardization found in the development environment. It is more difficult, for example, to ensure that users adequately test their results, and users cannot be guaranteed that their queries will not be deleted or modified when they are created in production. Transport and system problems as well as trouble associated with synchronizing your development and production systems also are more likely.

Running untested queries is often inefficient and it is possible that query quality (and quantity!) will suffer if several users create the same report multiple times. Moreover, end-user queries may not take advantage of more advanced existing BW reporting objects such as variables, restricted key figures, calculated key figures, and others that BW neophytes may not know about. Queries created by less knowledgeable users are likely to result in unpredictable reports, because these users do not understand all the technical aspects of your system such as the underlying star schema, business logic, or query design techniques.

The risks associated with power users creating queries can counter any benefits if your policy is not implemented correctly. You need to think carefully about which end users you enlist for developmental duties in the BW production environment as well as the when, where, and how these activities are done.

Query Development

The first decision is figuring out which types of queries users should create. I recommend restricting users to those that can be generated with the standard Business Explorer (BEx) front- end, or with similar third-party reporting tools. More technically advanced queries such as InfoSet Queries, Web Application Designer, and SAP Enterprise Portal should be reserved for the BW design and support team.

Ad hoc queries are ideal for power users to create. Typically employed by a small group to answer urgent reporting requirements or one-time information requests, these queries are not necessarily easier or safer for end users to develop However, their advantages are worth the risk. User-generated ad hoc queries provide efficiency and flexibility in reporting. They can reduce the burden on the BW development/maintenance team by eliminating one-time information requests or requests that may not be the highest priority.

The list of queries that users should not be allowed to create is much longer. Standard queries, for example, are used by a large number of people on a frequent basis. To guarantee reporting integrity, these queries should be created and changed only within the safety of the development environment by the BW team, and follow the standard transport path to production. The exception would be if a standard query needs to be changed urgently, when qualified users including power users can be allowed to save the query to a new query and make modifications as needed. For example, a user may want to add columns or rows to an existing report.

Because they require advanced BW functions and special processing, complex queries, both standard and ad hoc, present technical difficulties and should remain the exclusive domain of the BW team. Likewise, global reporting objects are used in multiple queries and include global structures, variables, restricted and calculated key figures, and other objects. Only the BW team should be able to change global reporting objects, and those changes must be made in the development system to prevent transport issues, unwanted changes, and unpredictable results.

Identify Competent Users

Now that you know what type of queries users can create, you need to identify the people in your organization who you can trust to build them properly. The tasks users perform on your BW system reveal whether they have enough technical knowledge to produce analytical tools or they are simply consumers of information.

I categorize typical users as report consumers with R/3 access who are reasonably knowledgeable about Microsoft Office applications like Word and Excel. These users frequently create or customize sophisticated spreadsheets. Senior management and executives, on the other hand, are more occasional users, and their normal interaction with a computer is limited to email and the Web. Occasional users have limited ability or desire to create or change their own reports. Neither typical nor occasional users are candidates to create queries in the production.

Power users, along with their close cousins, super users, are the folks you want to enlist. Both groups are made up of the people from outside of the IT organization who maintain high query usage and time on the BW system. Super users are power users who also have authorization for areas across a company. In addition to BW, power users work with statistical or analytical tools such as Brio, Cognos, Business Objects, Access, SPSS, and advanced Excel spreadsheets.

Power users demonstrate advanced analytical capabilities such as creating reports in a legacy system or data warehouse. These users are often willing to learn a new language to develop sophisticated queries. Power users also gather and distribute information to various groups and departments, and they possess a significant knowledge of the business processes and rules. For the remainder of this article, “power users” refers to both power and super users.

Resources and Support

Bringing power users into the query creation process changes the BW team’s production support role in a number of ways. You might find your team focusing more of its reporting efforts on the creation and updating of standard queries and global objects. The BW team will also be required to train and mentor BW users and power users as well as review user-developed queries.

As users grow more sophisticated, the BW team can expect requests for new queries and query changes to become more complex. End users may begin clamoring for new MultiProviders, transfer rules, or other things that require significant development efforts. You must manage users’ expectations early as they hone their skills, and they need to realize their time-consuming demands are weighed against the business benefits of such requests.

Over time, power users can play more of a role in query design and functional support. They can evolve into the point of contact for occasional and typical users regarding questions and initial query-enhancement requests. Ad hoc queries can be tested and validated by power users, who contact the BW team whenever needed. Encourage the exchange of ideas and best practices between power users in related applications across regions and countries, and schedule regular meetings or conference calls between these users from various parts of your company.

Once users have been approved to develop queries in the production environment, establish clear-cut procedures for creating and enhancing queries. Figure 1 maps the process for accommodating a new report request. Additionally, you may want to implement an approval process for the queries created by power users in production. For instance, a query may be made available immediately to the power user who created it, but must reviewed and approved before being rolled out to others.

Figure 1

Query-enhancement request process

Training and Documentation

As more users create queries in production and the number of queries in the system increases, training and documentation becomes more important than ever. Base-user training in BW reporting should provide background information and best practices in both the data and tools, and include areas such as:

• What BW is and is not
• Why BW was implemented
• BW terminology, icons, and screens
• How to log on and execute BW reports
• Finding and reusing existing reports
• Report navigation and formatting
• Online analysis and hierarchies

Depending on your reporting strategy, it might be advantageous to have at least two levels of training: end- and power-user training. These may be combined when delivering base training; however, power-user training will include more advanced components than end-user training.

Power users should get instruction in terms of best practices for query design as well as information about BW architecture and data modeling. It is essential that power users be aware of existing reporting objects including variables, and understand your firm’s naming conventions. They should also be able to check InfoCube contents using the Manage tool. Power users should know how to reconcile query results to the InfoCube contents as well as save a query/workbook as a new query/workbook. (For more tips, see, “22 Ways to Boost BEx Query Performance.”)

Coordinate your training strategy with queries, reports, and reporting object documentation for all queries enterprise-wide, including those done in R/3 and any other reporting environments along with BW. Provide users with guidelines and documentation such as report lists so they can work with existing reports in BW and in R/3. Your documentation should include materials that let power users know about existing global reporting objects, including variables and restricted and calculated key figures, so they can identify what these objects are and how they can be employed.

Highlight related queries so power users can easily spot other variations that may meet their reporting needs. Encourage them to create, share, and use query documentation by posting it to the company intranet. Remember to use expiration dates and life expectancies so that out-of-date queries and those that are no longer required can be deleted. This enforces proper migration procedures from development to production.

Use the metadata repository when creating documentation. If a query is embedded in a workbook, remember that documentation at the query level can be added to one of the workbook sheets. As the number of reports increases, it is prudent to establish a report catalog. It will be a valuable tool for all users to find and reuse existing queries and assist in new query development. The report catalog provides documentation for all BW and R/3 queries and reports.

Information such as a query's technical name and intent ought to be in the report catalog as well as its short and long description. Make sure to provide the author's name and the contact person responsible for the query. Creation and expiration date along with the query's expected lifetime are important, along with information such as its expected frequency (one time, weekly, monthly, daily, etc.), target population (finance, all business users, etc.), region, and country. Include any special instructions or notes in the catalog along with related queries in both R/3 and BW systems and their source (InfoCube for BW or table/program name in R/3). Also note the key figures and characteristics used in the report.

Based on this report catalog, consider implementing a finder tool that allows users to search for queries using required fields, areas, and so on. The report finder need not be complex and can be created in a simple spreadsheet. It also can be more robust and offer search engine accessiblity from your intranet or portal.

System Performance

Establish a well-defined procedure to control the quantity and quality of BW queries and minimize any potential performance impact on your system. Appoint a senior IT person as a quality management controller to take responsibility for monitoring the overall production of queries. In addition to fielding end-user feedback and complaints regarding query performance, I recommend that the quality management controller develop and disseminate a quality checklist and process flow chart to ensure reporting standards are met and maintained.

As noted earlier, the responsibility of the BW team shifts from query development to data warehouse administration as users become more self-sufficient in query design. As users grow more BW-dependent and use the system more frequently, the need for data accuracy and integrity intensifies and is increasingly critical. Procedures should be put in place to control and audit data loads and database integrity. What may appear to a user as a reporting issue may in fact be a data load or data integrity issue, so monitoring is essential. Automate your data load and error control with process chains or third-party tools.

You should also use BW monitoring tools such as the data load monitor and transaction RSRV to check problems in the data. Be vigilant for data that results in erroneous reporting. Manual table inspections or exception reporting can reveal “not-assigned values,” which cause problems, along with negative values or other values that do not meet business rules, such as values outside of an acceptable range. BW statistics also can be used to identify performance problems and queries that are no longer in use.

It is a good practice to set the loads to Reporting not available to prevent reporting on erroneous or missing data. If the problems cannot be resolved in a timely manner, e.g., before the start of the business day, notify the users when reporting is not available due to load errors. Reports delayed because of erroneous or missing data may result in end-user frustration. However, this practice is much better than losing end-user confidence because the data warehouse generates dubious reports.

Regular tuning and system optimization maximize the system’s reporting performance and ensure end user satisfaction and efficiency. Remember to faithfully follow optimization procedures such as managing aggregates by creating new ones and deleting those that are not used. Redesign your current BW setup for query optimization by including line item dimensions on large dimensions, optimizing extract rules, using more ODSs in place of InfoCubes, tracking inefficient queries, and redesigning or deleting queries as needed. Also, convert ad hoc queries that are general or global in nature into standard queries.

Synchronizing Development and Production

Nowhere are the risks associated with letting power users create queries more apparent than when it comes to managing transports between development and production. If, for example, a reporting object is initially created in production, then created in development and transported to production, the two versions will produce unpredictable behaviors in reports and data. There is also the risk of overwriting queries, workbooks, or objects when transporting roles or BW objects from development to production-related queries/workbooks if they exist in both systems.

Best practices demand that development and production systems be synchronized for all queries and reporting objects. The two must be in sync to ensure proper system testing, optimization, development, and upgrading. Both BW team members and power users should avoid creating global reporting objects and standard or complex queries in production to eliminate the potential synchronization and overwrite issues. Periodically recreate ad hoc queries that were created in production in the development system, then retransport those that are successfully validated using your standard transport process. Three options are available to do so:

• For a small number of queries, recreate them manually in the development system.
• Copy the production database into the development system. While this is the recommended method for synchronizing the two systems, a database copy may not be a practical option for a small number of queries. Moreover, it may not be feasible, such as when work under development has not been transported to production and would be overwritten by a database copy.
• Transport backward from production to development. This is not recommended by SAP, but offers an alternative when recreation and database copying are not options. Transporting backward has its own set of risks. Aside from the danger of overwriting queries, you may also face errors if the master data used in the query being transported is available in production but not in development. For example, if restricted key figures using a hierarchy node exist in production but not in development, there will be problems with the transport. For these cases, you may be better off not transporting backward, but rather recreating the query in development.

Two methods can be employed for copying objects backward from production to development. First, ask your Basis team to create a new transport route and development class for your system. With BW 3.0, you also can use the XML metadata exchange function to transport between the two environments. Figure 2 displays the screen to select the query for uploading in XML to the BW development server. This command is found under the Transport tab in the Administrator Workbench. XML Export functionality should not be relied on to replace the actual transport process. It can be used, however, as an effective work around to avoid the additional setup required by the backward transports, such as when only a small number of objects need to be transported.

Figure 2

XML export to transport queries from production to development

Security Issues and Authorization Requirements

Security policies must be adjusted to control the creation of queries in production, but remember that the need for security ought to be balanced with the need for users’ flexibility. Your goal, then, is to provide that freedom and flexibility to the power users so they can become self-sufficient in query development and access, while preventing any breach in security.

You can base your security policies on a combination of one or more levels, such as naming conventions and roles, to provide some degree of flexibility. InfoObjects can be used by adding key figure restrictions that prevent users from accessing sensitive data such as commissions, salary, and other information. Likewise, providing users with access to certain InfoAreas such as sales InfoCubes while restricting them from finance InfoCubes is recommended. Restrictions must also be in place for certain queries and reports to keep sensitive files such balance sheet and income statement reports out of the wrong hands.

The most important security consideration is to limit the width of the roles, e.g., number and type of objects that can be created or changed in production. Authorization may be based upon the query naming conventions to enforce the use of the query name spaces. Table 1 describes the possible actions relating to query design based on the suggested naming convention (see the sidebar, “Establishing a Naming Convention Users Use!”).

Your security policies should reflect many of the issues raised throughout this article. For example, it is critical to prevent users from creating or changing global reporting objects such as restricted key figures, variables, or global structures. Only the BW team should have the ability to create or change these, and changes should be done in development to follow the usual transport path. Conversely, you should allow users to share ad hoc queries created by others based on authorization levels — for example, InfoAreas, InfoCubes, or key figures.

You may want to grant one power user the authorization to write queries in production, and create a group of users with similar analytical requirements to be dependent on that power user for ad hoc reports. Authorization to InfoAreas, key figures, and reports for these users is based on that of the authorized power user.

Allow power users to execute queries in defined areas and save these as a query if modifications are needed. Every author and BW team ought to able to create, read, change, execute, and rename ad hoc queries. An exception would apply to new users who should not be allowed to delete their own queries to guard against unwanted deletions. Authorization can be granted later after they have more experience. Regularly review user access to ensure the proper users are authorized to access data and areas at InfoObject and query/report level.

Getting Started

You now understand the benefits, risks, and implications of letting users create queries in the BW production system, and you should be ready to start implementing the processes and policies needed. Before actually opening the production system to the users, consider the following:

Train users and power users in query optimization guidelines, and develop a quality-control procedure manual. Develop a help desk as well as a query enhancement process along with performance monitoring and tuning processes. Define security requirements and appoint responsibilities. Make sure to test and validate your security policies as well as your transport procedures and query/workbook deletion processes.

You may want to start small by implementing a pilot site, and allow only a small number of power users to develop queries in production. Expect your biggest challenge and priority to be in the area of optimization to maintain acceptable query performance to the users.

Establishing a Naming Convention Users Use

To avoid redundancies and lower user frustrations, it is important to adopt meaningful and consistent naming conventions that make finding queries and objects easy. Descriptions should be used to identify objects and queries including key figures, dimensions, and variables. In both the Web Query Designer and BEx, query descriptions and technical names are listed in alphabetical order, so users can more easily find their queries if they start their query descriptions with the first three initials.

Up to 30 characters may be used as a technical query name, so it is a good practice to develop a naming convention that differentiates queries created by the BW support/design team from those ad hoc queries created by power users to prevent overwriting of respective queries. The table provides a suggested naming convention.

Note that using Z or Y is a good practice, as it may prevent overwriting queries and custom reporting objects during an upgrade or a support packs implementation. However, query naming conventions are a personal preference and there is absolutely no reason why queries have to use a Z prefix. Likewise, it is not mandatory that queries need an InfoCube name, since they can be found in the respective InfoCube upon opening a query in the query designer tool. End users probably do not care about InfoCube names and may be more comfortable with just having their name or one of the user type designators in the table.

Catherine Roze

Catherine Roze is a senior BI consultant with more than seven years of full life cycle experience in SAP reporting and SAP NetWeaver BW with special focus on SAP NetWeaver BW enterprise reporting, performance optimization, and SAP NetWeaver BW Accelerator. A seasoned BI professional, she has contributed to more than 15 SAP NetWeaver BW implementation projects for Fortune 500 companies. Catherine holds an MBA and MS (industrial technology) and is the author of SAP BW Certification: A Business Information Warehouse Study Guide.

You may contact the author at cmroze@yahoo.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.