Chapter 1: Fundamentals of Business Intelligence (BI) reporting
After merging with Business Objects, SAP announced that it will retire many of the Business Explorer (BEx) tools by 2016. However, SAP customers are weighing their options, and many of them will move from SAP BEx tools to SAP BusinessObjects tools only if the new applications can fill gaps in functionality, rather than making that decision because SAP will eventually stop supporting BEx.The best approach for SAP customers is to consider each Business Objects tool individually and to bring these tools into the fold in a phased approach.
This chapter, from the second edition of the book SAP BEx Tools by Peter Scott, explores SAP BEx reporting tools, including BEx Analyzer, BEx Query Designer and BEx Web Analyzer, which are part of SAP NetWeaver Business Intelligence (BI) 7.0.
Author Peter Scott has more than ten years of business intelligence experience, with a mastery level in SAP BI reporting, SAP BI project management and Enterprise Information Strategy. He has directed over a dozen project lifecycles as a project manager, architect and lead. He serves as site expert for SearchSAP.com on the subject of Business Warehouse reporting.
SAP BEx Tools, Ch. 1
Table of contents:
SAP Business Explorer (BEx) and SAP BI reporting basics
Understanding and using SAP BEx reporting tools
The collection of reporting tools available with SAP NetWeaver Business Intelligence (BI) 7.0 is referred to as the Enterprise Reporting, Query, and Analysis Information Technology (IT) scenario. The core tools found in the BI suite are referred to as the Business Explorer (BEx). These BEx tools provide business users with the ability to design, create, analyze, and distribute data that results in improved decision-making capabilities.
The BEx suite is illustrated in Figure 1. The BEx suite is closely integrated with the SAP NetWeaver Portal and the Visual Composer modeling tool. Learning how to use the tools found in the BEx suite gives users the ability to create a query, format a report, publish Web applications, and distribute information to other business users.
The BEx suite consists of the following tools:
- BEx Query Designer
- BEx Report Designer
- BEx Web Application Designer (WAD)
- BEx Web Analyzer (Web-based tool)
- BEx Analyzer (Excel-based tool)
Spanning across this toolset is the BEx Broadcaster, which provides options for scheduling and distributing report output to a printer, a Portable Document Format (PDF) file, or an email address. This book provides details on all of the BEx tools, including some advanced reporting features. It will also discuss the merger with Business Objects (BOBJ) and provide some direction on how to move forward as SAP BOBJ consolidates the BEx tools with the BOBJ portfolio.
The BEx Report Designer tool is anew as of BI 7.0. It has been added to the suite to fill the gap around formatted reporting. Earlier criticisms of SAP BW usually highlighted its inability to create presentation-quality reports. The Report Designer serves this purpose by taking the output from the Query Designer -- a query definition -- and transforming it by changing fonts, text, row/column heights, colors, etc. It also allows a designer to insert text, graphics, charts, headers, and footers. The result is a formatted report that is highly optimized for printing or for using in a presentation. A screenshot of the BEx Report Designer is shown in Figure 3.
The BEx WAD is another standalone tool that a report designer can use to build reporting applications that are optimized for the Web. A what-you-see-is-what-you-get (WYSIWYG) interface allows for the integration of basic word processing capabilities with BI-specific content. Web items delivered by SAP include buttons, filters, dropdown boxes, analysis grids, charts, and maps. All of these Web items represent placeholders that get assigned to a DataProvider. It is typically used by SAP power users to create interactive applications that are intuitive for end users to work with. Highly advanced dashboards can be constructed. If required, the underlying HTML code can also be modified or enhanced. The BEx WAD is depicted in Figure 4.
The BEx Web Analyzer is an ad hoc analysis tool that is browser-based and provides zero footprints in terms of desktop application software because it requires no specific installation to use. The browser is linked to a specific SAP BI system and allows a user to drill down on a navigational state of a report and save this preferred view of the data for future use. OLAP functions, charting, broadcasting, exporting (to Excel or as a .csv file), and printing capabilities are all standard. A typical query executed in the BEx Web Analyzer is shown in Figure 5.
The BEx Analyzer is integrated with Microsoft Excel and is accessible by installing the SAP Graphical User Interface (GUI) add-on for BI. Data is embedded into Microsoft Excel workbooks providing users with drag-and-drop capabilities to drill down and filter the data using SAP BI OLAP functions. Excel functions and features can be used to augment the analysis or to provide additional capabilities. Users can also leverage Visual Basic Applications (VBA) to create customized programs. As of BI 7.0, the BEx Analyzer comes with a second SAP BI–delivered toolbar that provides a rich set of design tools that allow for interactive applications to be constructed in a manner similar to that found in the SAP WAD. By using the new design mode a user can configure items such as dropdown boxes, checkboxes, and planning functions. Figure 6 is an illustration of a BEx workbook embedded in the BEx Analyzer.
The tools that make up the BEx suite provide solutions for enterprise reporting, ad hoc OLAP analysis, and dashboarding. The following chapters describe the BEx suite in detail and allow business users to stretch the value of their investment in SAP BI. Becoming an expert in BI Reporting can be achieved faster if you allow yourself to test the limitations of each tool. Experiment with each tool and try out every option. At the end of the day the worst thing a report designer can do is create a less than ideal report that can be cleaned up and enhanced over time. Be curious!
Chapter 1: Fundamentals of Business Intelligence (BI) Reporting
It was once nearly impossible to extract information from applications. Corporate systems were disconnected, little historical information could be stored, and any data that was stored was often inaccessible. In addition, the information that was available lacked business process support. It is one thing to review data and quite another to link data to business processes that allow for exception reporting and alert notifications. These difficulties led to the concept of a Data Warehouse (DW).
1.1 Basics of Data Warehousing
Put simply, a DW serves as a decision support environment where corporate data can be quickly summarized at different operational levels.
Data warehousing is the process of choosing, migrating, cleaning, transforming, and storing data from disparate systems into one common location, whereby users can easily extract and analyze information for management decisions.
A DW is also commonly referred to as a:
- Data Mart
- Corporate Information Factory (CIF)
- Decision Support System (DSS)
- Business Intelligence (BI)
- Business Warehouse (BW)
One of the largest sources of data is an Enterprise Resource Planning (ERP) system. The process of migrating data from an ERP system to a DW is usually referred to as Extraction, Transformation, and Loading (ETL).
The multidimensional analysis offered by a DW, which enables DW users to analyze data trends, exceptions, and variances of interest, is called Online Analytical Processing (OLAP). Typical OLAP is used to answer questions such as:
- What was my best-selling product in January?
- What are my year-to-date Cost Center expenses?
- Why was I under my revenue target for March?
- Are we gaining or losing market share this month?
- How will my year-end look?
To compete, companies need to disseminate knowledge and information throughout their organization. To do that they have to contend with massive amounts of data that arrive from many different sources at various times. The ultimate goal of data warehousing is to manage this complexity and provide users with knowledge that gives them a competitive advantage and operational excellence.
The results of a successful data-warehousing initiative should include faster decision times, improved information quality, and greater strategic insight. With data warehousing, the data itself becomes a key asset to an organization, but only if decision makers can successfully access, understand, and leverage this new knowledge effectively.
Anyone in an organization that makes decisions needs BI; in other words, everybody. BI supports decisions at every level within an organization, whether the information is for an executive officer looking at Key Performance Indicators (KPIs) or for a finance manager to rationalize and monitor expenses by cost elements. The strategic nature of an executive dashboard and the tactical nature of a cost center analysis require data with the right level of detail and in the right context.
The concept of today's DW evolved from the concept of Management Information Systems (MIS) from IBM in the '60s and from a Harvard Business School article on Executive Information Systems (EIS) in the '70s. Data warehousing became prominent following the research and publications of Bill Inmon and Ralph Kimball. In 1958, Hans Peter Luhn defined BI systems in the IBM Journal. In the early 1990s, a Gartner analyst, Howard Dresner, popularized the term BI as a general term that describes using facts to improve decision making. Since then, BI and data warehousing have become a top spending priority for organizations and Chief Information Officers.
With numerous vendors in the data-warehousing space, BI tools have become standardized into familiar categories that are designed to report, analyze, and present data. These tools are generally categorized as:
- Reporting and Querying
- Web Applications
- Business Process Management (BPM)
- Data Mining
1.2 Basics of SAP BI
SAP BI is an end-to-end data-warehousing solution that is usually built on a threetier environment consisting of Development (DEV), Test (Quality Assurance (QA)), and Production (PROD) servers. This three-tier configuration separates development work from a live system, and allows for sufficient testing with real data in the QA system. Enhancements are transported through a well-defined process that moves from DEV to QA for testing, and then to PROD, where BI users can access information. This landscape is depicted in Figure 1.1.
SAP BI also has a three-tiered architecture. Figure 1.2 summarizes the architecture, which consists of the following layers:
- Presentation layer
Consists of the SAP Business Explorer (BEx) tools such as BEx Analyzer and BEx Web Application Designer (WAD).
- Database layer
Consists of InfoCubes, Data Store Objects (DSO), MultiCubes, and Master Data Objects that can be reported on. This layer also includes the Administrator Workbench and ETL capabilities.
- Source Systems layer
Consists of SAP ERP Central Component (ECC) or ERP systems, legacy systems, text files, or another SAP BI environment that serves at a Data Mart.
All SAP BI queries are constructed using the BEx tools, which request data from the BI database. The BI database follows an ETL process to populate InfoCubes, DSO, MultiCubes, and Master Data.
Objects for which queries can be created against or executed on using BEx tools are collectively referred to as InfoProviders. An InfoCube, which is a type of InfoProvider, is a subset or collection of data from the BI database that has logical relationships that allow users to report on many things simultaneously. The InfoCube is based on SAP's extended star schema model.
Queries are designed and developed with the BEx Query Designer. A completed query is referred to as a Data Provider. You can display the output of a query definition as either a Web report using a standard web browser, such as Microsoft's Internet Explorer 7, or view a query result using the BEx Analyzer, which is integrated with Microsoft Excel.
Many queries can be built from a single InfoProvider. A single query definition has a one-to-one relationship with its InfoProvider. The query results from a query definition are displayed on a web page, or in the BEx Analyzer. You can format and store query results with many different views, which can result in a many-to-one relationship between a query definition and the formatted query results. Figure 1.3 displays these relationships.
You'll find more downloadable excerpts from books by SAP experts in our SAP chapter download library.