Cell Based Reporting For Effective Custom Reports And
Analytics
Rakesh Tej Kumar Kalahasthi, P. Radha Krishna
Abstract:
Business Intelligence (BI) deals with increasing the competitive advantage of a business, by intelligent use of available data for decision making and synthesizing useful knowledge from collections of data. Microsoft Excel is one of the most commonly used BI tools and it is ubiquitous as well as well understood. Excel PivotTables enables access and analyze SAP NetWeaver BI data. SAP BI 7.0 release introduced a new dimension tool, called Cell Based Reporting (CBR), for developing workbooks using Excel. This feature facilitates seamless integration of Excel with BI and produces a dynamic view of the reports for the desired selection parameters to the user. In this we study the CBR features of SAP BI and focus on development of CBR based financial analysis reports for the business. This study shows that CBR based reporting guarantees the increase in performance of reports by integration of Excel with SAP BI.
Introduction
Enterprises success depends on the effective use of information that was collected through various operational systems. Recent developments in information technology made business analytics with better insights of the data resulting in improved decision-making. Moreover, the business intelligence (BI) systems are now better integrated with transactional systems. Deployment of BI tools is directly linked with the company’s ongoing advancement, along with changing business requirements and an ever-increasing amount of data which defines the ability to create and distribute the desired reports. In today’s competitive environment businesses need quick business actions and thereby necessitates use of sophisticated and easy-to-use BI tools in order to generate required reports quickly and analyze the data effectively.
BI technology has coalesced in the last decade around the use of data warehouse and [2] Online Analytical Processing (OLAP). The analytical processor can store lots of historical data and tends to give summarized reports. Data Warehouse is a conventional approach to collect respective business data into a repository, where it is organized and validated to provide analyzed data through reports for decision making. [3] Just in a sentence it can be said as “Faster BI means better business decisions”
This paper addresses on-the-fly BI reporting capability through CBR provided by SAP BI 7.0. CBR triumph over the [1] pivot tables which can access and analyze SAP NetWeaver BI data in a static format. The CBR gives a dynamic approach for viewing the data. We considered financial statements of a chemical industry to show the viability of this technology under BI Platform. Financial statements deal with the information about the financial position, performance and changes in financial position of an enterprise that is useful in making economic decisions.Financial report is usually been made after a month, quarter or the completion of one year depending on the requisite. The reports such as balance sheet, income statement, cash flow statement and statement of
retained earnings are frequently considered for decision making.
The CBR approach presented in this paper for developing custom based reporting facilitates reduction in the turnaround time in developing reports as well as operational cost. The rest of the paper is organized as follows. Section 2 presents SAP BI CBR capabilities, Section 3 describes Case study, Section 4 presents the discussion, the paper concludes with Section 5.
Sap Bi Cell Based Reporting (Cbr)
SAP NetWeaver Business Intelligence [4] encompasses data-warehousing capabilities, which allows a user to work with powerful analysis and planning tools. It is useful to integrate data and transform it into more constructive manner to aid decision-making and provide actionable results by accessing timely business information.The SAP BI component, which is a part of the SAP NetWeaver platform, enables solutions for company-wide data warehousing, ad-hoc querying and reporting, analysis, and distribution of actionable insights. It also supports on- the-fly reporting capabilities to create, distribute, and print Web and spreadsheet-based reports in multiple formats.
CBR [5] [6] [7] has the capability of developing the report through BEx in customized format. It provides seamless integration with Excel and Intuitive Navigation for end users. CBR has a Design Mode to enable easy building of BI applications and allows direct use of Excel Formulas and formatting options. This feature also has a tight integration with BI Planning.
Data binding in the Business Explorer (BEx) Analyzer is provided by queries, query views, or InfoProviders. BI data can thus be analyzed in different ways and inserted in a Microsoft Excel workbook. Using the open dialog, business experts can select queries, query views, or InfoProviders or call the BEx Query Designer to create new queries. The layout and interaction of the workbooks can be adapted to suit user requirements. Formatting and formula functions from Microsoft Excel can also be used. The workbooks that are created can
be saved in favorites or made available to other users using the role concept. Workbooks can also be precalculated and sent by e-mail to make them available to other user groups. Information broadcasting functions are used for this purpose.
Case Study
A global giant in the chemicals industry makes products essential to everyday living. Their products, found in consumer and industrial applications, are manufactured in North America, Europe and Asia. They are one of the world’s largest producers of acetyl products, which are intermediate chemicals for nearly all major industries, as well as a leading global producer of high performance engineered polymers that are used in a variety of high-value end-use applications. Initially, for managing their financial data, they used Hyperion HFM (Hyperion Financial Management) system for their financial transactions.
Table.1 Different Modules implemented
Area Module Scope
Logistics- MM, PP,
QM, FICO, SD etc., SAP R/3 Transactional system. Customer Relations SAP CRM Customer Relationship Management system. Data Warehouse SAP BI Analytic System for detailed and
Summarized Reports. Planning, Budgeting
and Forecasting SAP SEM Planning and Consolidation system. Financial Management
Report HFM
Exclusive for financial management report in Hyperion
The Table.1 gives the information of
different technologies available for the entire
process. After the system is enhanced from
Hyperion to SAP BI, the data is pulled from
BI for all the required reports. The end users
are comfortable to see the report in Hyperion
format rather than the SAP BI BEx format. So
the requirement from their end has demanded
to use their earlier formats, but here the data
should not come from Hyperion system, but
it should be from SAP BI and SEM BCS
system. This situation leads to find out the
new dimensional tool given by SAP in BI 7.0
which is called Cell Based Reporting. Here
the MS Excel is integrated with the BI 7.0
where the data can be inserted through the cell
based formulae in any format. The data can be
viewed by the end user dynamically with the
desired selection parameters at any instance.
Methodology
Consider the 2 types of reports as shown in Figure.1 which client requires viewing for their entire organizational structure.
10 day Management summary 1)
5 day Detailed 2)
The Management summary reports are for top management people and detailed reports are for Item level people in the organization. Usually 5 day reports are generated after the first 5 working days of the current month to view the reports of previous month similarly 10 day reports are generated after the first 10 working days of the current month to view the reports of previous month. For example, to view reports of February, it takes first 5 or 10 working days (depending on the type of report) in March to generate the reports.
Steps involved in generation of 5 day reports: Operational data coming from SAP R/3 system •
is pulled into SEM Business Consolidations System (BCS) for consolidations. Here the data is consolidated.
The consolidated data is send to BI system to •
generate BEx reports.
The 5 day reports are for Item level people and so there are no planning reports in this cycle.
Figure.1 Block Diagram of the Reports
SEM BCS for Consolidations Operational System BPS for Planning and Forecasting of data BEx Reports in BI
Steps involved in generation of 10 day reports:
Operational data coming from SAP R/3 •
system is pulled into SEM BCS system for consolidations, here the data gets consolidated.
The consolidated data is send to Business •
Planning and Simulation (BPS) system to Plan and forecast data.
The planned data comes back to SEM BCS •
for consolidation and moves to BI system for BEx reporting.
The 10 day reports are for top management people. So this cycle reports contain planning, forecasting and budgeting.
Customer yearns for the existing report formats as their users are very much comfortable. So the BI data is inserted into the corresponding cells of Hyperion report format.
MS Excel is very much integrated with BI in Netweaver, by which the data can be inserted into any desired format with excel formula mode as explained below.
Excel Formula Mode
A.
A Seamless integration of native Excel formatting and formulas
Cells of the result set are described via an a.
Excel formula (BexGetData)
Excel formatting options can be fully b.
leveraged for BEx workbooks (e.g., blank lines, fonts, etc.)
Combining Excel formulae and Excel c.
formatting allows the design of high-end formatted solution workbooks as shown in Figure 2.
Figure.2 TWC report
The figure 2 gives the report on a custom format for Trade working capital report developed through the CBR.
Example
Consider the most usable report for the client “Trade working capital” (TWC). Under normal circumstances this report takes 5 man days to develop in BEx in order to complete that report. For the same report, CBR takes 2 man hours to complete with high performance. This report (previously in Hyperion format) has 13 rows and 6 columns, developing such a format in BEx could involve around 300 Cell Editors which occupies complex calculations and definitely hits the performance of the report.
Firstly, the query developed in BI should be drilled down to the pertinent level, which is considered as Dataprovider_1. This provider is called into the specific cell of Hyperion format taken as another worksheet using below excel formulae as shown in Figure.3:
BexGetData (“Dataprovider_1”,
$G$9,$B9).
Here, Dataprovider_1 is the default name given for the query drilled in the first worksheet. The above formula pulls value into BW report and appears in the Hyperion format Excel. The remaining process involves dragging the cells in excel, automatically data is pulled from the Dataprovider_1.
Figure.3 Excel integration
Every Cell of the query result set is automatically interpreted as an Excel Formula.
BExGetData
B.
BexAnalyzer offers the Excel function “BexGetData“. This allows access to every cell of the result set of an arbitrary data provider. The below are options available in excel integrated BI.
The context menu of the analysis grid offers a.
the option “Convert to Formula“. This converts the data of each cell into a well defined Excel formula.
The selection parameters of the formula are b.
derived from the key rows and columns of the respective cell.
By using this option, the Analysis Grid Item c.
can be deleted and Drag & Drop can be disabled. The number format of the data cells is set to standard.
This enables the end-user in having full flexibility of using Excel Formatting and Formulae on the data and the approach for the solution gives as desired by the user.
Discussions
Though the developer cost for CBR is high on an hourly basis to the client but, it saves huge man hours and operating cost in their business run. The performance of the reports touch base their desire and complicated reports are developed in very less time.
Cost Analysis
For Trade working capital report the total estimate without cell based report could be
5days*8hours*50$/resource= 0.002 M $. By using CBR technique the cost for the client was reduced to 2hour*70$/resource=0.00014 M $. Hence from the observation the difference between 2000$-140$=0.00186 M $ is saved for one report and similarly for the remaining 83 reports.
Consider average time per report out of 84 reports based on complexity as 3 man days if developed in BEx but if the same is developed in CBR it will take 4 man hours per report as shown in Table .2. Considering the difference
Table.2 Cost Analysis No. of
reportsNo. of hours* Total no of reports*cost/report
Total Amount in M $ BEx technique 84 3days*8hours*84reports*50$ 0.1008 M $ CBR 84 4hours*84reports*70$ 0.02352 M $
Savings for Client:
0.1008 M $-0.02352 M $ =0.07728 M $. The advantages of CBR are
SAP BI data can easily embed into MS Excel. •
Business users can utilize their Excel know-•
how and use Excel functionality on top of BI data. SAP NetWeaver 7.0 provides enhanced navigation features such as Drag & Drop. SAP NetWeaver 7.0 provides additional •
functionality such as a design mode and leverages Excel formatting and formula features.
SAP BI Planning is completely integrated •
SAP BI data has to be easily distributable. •
Hence, BEx Analyzer workbooks can be pre-calculated and distributed through Information Broadcasting
Saves time and cost for the development •
effort.
The CBR has MS Excel dependency and requirement of skilled resource. Because of these, the customer is able to use the custom excel formats which have completely replaced the standard BEx report formats. The end-user is comfortable in reformatting the workbook and adds further Excel calculations. Every cell of the query result set is automatically interpreted as an Excel formula due to which rate of dependencies increases.
This work describes how business experts can run complex analyses on BI data and use Microsoft Excel functions at the same time. Here, the BEx Analyzer is a design and analysis tool built into Microsoft Excel which is available for this purpose. With the BEx Analyzer, BI data can be presented and analyzed interactively in the Microsoft Excel environment.
Conclusions
CBR is a high performance tool released as add on in SAP Netweaver for BI. The proposed solution takes the report format specified by the user and pulls the financial data from SAP BI and SEM BCS systems and generates analytical reports in the desired format. This article gives how this technique is implemented for a chemical industry to view data in effective custom reporting formats through BEx Analyzer. CBR has a positive influence in different factors of business such as time, cost and manpower which moved SAP BI a step ahead in the current competitive world.
Bibliography
Amyn Rajan, ‘etal’ (2008) “Connecting to SAP
NetWeaver BI with Microsoft Excel 2007 PivotTables and ODBO”, 04 September 2008.
R. Kimball (1996), “The Data Warehouse Toolkit”, John Wiley & Sons, Inc., New York (1996).
The Need for Speed: Faster BI Means BetterBusiness Decisions New business intelligence tools enable users to leverage data in more productive ways- An exclusive report
from Business Week Research Services. SAP Gives Business Intelligence, Chapter 10,
http://media.techtarget.com/searchSAP/ downloads/netweaver_for_dummies_Ch10. pdf
EClassEric Schemer, Markus Ulke (2006) “Seamless Integration of SAP NetWeaver BI
and Excel with the SAP Business Explorer Analyzer”, SAP Contribution 31 October
2006.
Thomas Zurek (2008) “Native Excel 2007 On Top
Of Netweaver BI 7.0”, SAP AG, 29 January
2008.
Enhancement package 1 for SAP NetWeaver 7.0,
Product Management SAP NetWeaver BI October, 2008.
