Microsoft Excel has always been a powerful sidecar for all ERP business users. No matter how great the reports available in ERP are, data always is being extracted to Excel spreadsheets so that it can be manipulated and summarized in pivot tables.
With SAP BusinessObjects Business Analytics for Microsoft Office, it is now easier to get the ERP data to Excel. You can now make a direct connection to SAP S/4HANA and get direct access to the SAP HANA Core Data Services (CDS) views in Excel. It means that users will have access to live data instead of an outdated version of the report exported a few days earlier.
An Overview of the SAP BusinessObjects Business Analytics
Any business user can install SAP BusinessObjects Business Analytics in his or her own computer in a few minutes, as it provides an auto installer. It can be downloaded directly by clicking the Download Software button (Figure 1) of the SAP Service MarketPlace support portal, but it requires a separate license for usage.
The SAP Service Marketplace support portal
(Note: A detailed installation and administration guide can be found on SAP Help (http://help.sap.com/boaa), but this topic is not part of the scope of this article.)
After the installation, the options ANALYSIS, EPM, and Data Manager are displayed on the Excel ribbon, as shown in Figure 2.
Excel after the SAP BusinessObjects Analysis, edition for Microsoft Office, installation
The EPM plugin allows you to import Microsoft Excel data to SAP BusinessObjects EPM solutions and build reports and spreadsheets based on this data, while the BPC plug-in connects the web client of SAP BusinessObjects Planning and Consolidation to Microsoft Office, allowing the execution of Microsoft Excel- or PowerPoint-related tasks for the BPC activities.
I focus specifically on the Analysis plug-in and how it can be used to generate manufacturing reports using the CDS views provided by the SAP S/4HANA Embedded Analytics.
What a CDS View Offers for SAP BusinessObjects Business Analytics
A CDS view is a virtual data model that can used to read data from an SAP HANA database. The concept of views has existed for a long time in ABAP. However, with a CDS view, the logic behind the view is pushed down into the database level instead of being executed into the application level.
The CDS view definition is made directly on SAP HANA, with a SQL-based data definition language (DDL). It means that the data relationship is defined on the database level, and therefore, data can be consumed directly from the database.
For example, if you are creating a custom application that will read data from SAP S/4HANA, you can simply build your own CDS view and read data directly from the database. In the past, you used to build an ABAP function module to read data from the database and then call it as a Remote Function Call (RFC).
For more information about CDS views, you can refer to Karl Kessler's article, “Enhanced ABAP Development with Core Data Services (CDS)."
The CDS view is not meant to be used only by custom applications or integrations with external systems. The CDS view can also be used internally by applications developed by SAP in SAP S/4HANA. There are many other possible uses for a CDS view.
In SAP S/4HANA, you can use a collection of CDS views to deliver Virtual Data Models that can be consumed in many different ways, such as Fiori apps, query browsers, or any BI application.
In the manufacturing area, you can use views specific for production orders as a basis for reading production order information on external systems and applications.
One example is the Manufacturing Order Item Cube (IPPMFGORDITEMC), which delivers information about the released production order items. You can access this view directly by executing transaction code SE11. This action displays the screen in Figure 3 in which you can see the technical details behind the Manufacturing Order Item Cube, such as the View Flds, the Table/Join Conditions, or the Selection Conditions.
CDS view Manufacturing Order Item Cube
You can see the details behind the data selected by this view by clicking the tabs Selection Conditions or Table/Join Conditions. In the Selection Conditions tab, you can find, for example, that only released orders will be selected on this view because there is a selection condition in which the field MFGORDERRELEASEDCODE should be equal to 1, as shown in Figure 4.
Selection Conditions details of view IPPMFGORDITEMC
You can also access the data retrieved by this view by executing transaction codes SE16 or SE16N, as shown in Figure 5.
Data retrieved by the CDS view Manufacturing Order Item Cube
Connecting Excel to an SAP S/4HANA System Using BusinessObjects Business Analytics
Now that you have read what a CDS view is and have been shown an example of a manufacturing CDS view, you are ready to learn how to use the BusinessObjects Analysis plug-in to connect Excel to an SAP S/4HANA system and read the information directly from this CDS view.
After you open Microsoft Excel and select the Analysis tab, the Insert Data Source button is available, as shown in Figure 6. Click Insert Data Source and then select the Select Data Source… option, as also shown in Figure 6.
Inserting a new data source
Click the Skip button in the SAP BusinessObjects logon pop-up screen shown in Figure 7, since a Web Service will not be used for the connection.
The SAP BusinessObjects logon screen
This action displays the next pop-up screen (Figure 8) in which you see a list of the systems available on your SAP logon. Select your desired system and click the Next button.
The screen in which you choose the desired system
After the selection of the desired system, you are prompted to input the client, user, and password to log in to the system, as shown in Figure 9. After you enter this information, click the OK button.
The screen to input the logon data
After you complete the logon, you can select the desired data source. Select the Area tab (Figure 10) to see an overview of the available data sources (CDS views) for each application area.
Data sources for the application Production Planning and Control area
After you double-click the desired data source (cube), you are ready to manipulate data in an Excel spreadsheet.
Building Reports for Production Orders
On a manufacturing plant, production order reports are generally used to control the production execution, the work in process, and operations completion. Usually, the order information system transactions are used (such as transaction code COOIS) and the content is exported to Microsoft Excel to be manipulated. The problem with this approach is that data will be quickly outdated.
With the SAP BusinessObjects Business Analytics you can have the production order data in Microsoft Excel extracted directly from the SAP HANA database. It means that a confirmation posted into a production order will be immediately reflected in Excel and the business users will be able to take decisions effectively, based on live data.
You have two different cubes for production orders: the Manufacturing Order Item Cube (technical name 2CPPMFGORDITEMC), where you retrieve header data for released production orders, and the Manufacturing Order Operation Cube (technical name 2CPPMFGORDEROPC), where you see data from production order operations. The first cube refers to the CDS view IPPMFGORDITEMC that is shown in the SAP S/4HANA system in Figure 3.
After establishing the connection to the system and selecting the data source, you have a spreadsheet that shows the total quantity of released orders in your system. Note in Figure 11 that you also have a design panel in the right side of the screen that shows the data source fields and options for columns, rows, and background filters.
An Excel spreadsheet showing the data source
Now that you have loaded the data source into your spreadsheet, you can drag and drop the data source fields into the columns and rows under the frame Analysis to view a more detailed report. For example, Figure 12 displays the spreadsheet after you drag the field Manufacturing Order and drop it into the rows. With this step, you can stratify your data and see each order in a different row.
A spreadsheet with each order in a different row
You can also drag and drop more fields to the row, such as the material number or basic start and finish dates, into the rows to see additional information on the report. You can also add a filter for any field by right-clicking the field and selecting the Filter by Member… option, as shown in Figure 13.
Adding a filter for a specific field
Figure 14 shows the spreadsheet with all those fields and a filter for the material number. Additionally, if you want to filter for a specific field, but don’t want to have it displayed on the spreadsheet, you can drag and drop it into the Background Filter area.
A spreadsheet with additional fields and a filter for the material number
Another available option enables you to swap the spreadsheet axis by clicking the button enclosed in the red box in Figure 15 to have a different view of the report. You can also save the results as you would save any other spreadsheet.
Spreadsheet with the axes swapped
You can also save this spreadsheet to continue working later. At any time, when you click the Refresh All button highlighted in Figure 16, data is read again from the CDS view and your spreadsheet is updated with newly created and changed orders.
Click the Refresh All button to read data from the CDS view
Now that you have the report from the production order header, you can add a new sheet and follow the same procedure, adding a data source for the Manufacturing Order Operation Cube. That way, you can work with the order header and the order operations at the same time. Figure 17 shows the spreadsheet with the operation data.
Spreadsheet with the operation data
Besides the reports for production orders, you can also build reports to see the forecast results and compare them with the sales figures using the Forecast Demand versus Sales of Material (Figure 10). This is important for the sales department to control the effectiveness of the forecast and again, live data will support the decisions taken by the business users.
This cube reads data from the CDS view IPPFCSTVERSUSSLS and it is basically the production requirements plan (planned independent requirements). After you select this cube, a pop-up screen appears in which you can input a date. You can read the forecast requirements in the future from the database until the date informed in this pop-up, as shown in Figure 18.
The pop-up screen in which the date is entered
Figure 19 shows the initial spreadsheet after you load the cube with the total forecast values.
Spreadsheet with the total forecast values
Following the same procedure that was executed to build the production order reports, you can drag and drop the Material, Plant, and Year Month fields into the columns to see the total forecast for each material and plant combination on a monthly basis. Figure 20 shows the spreadsheet after applying those changes.
Forecast values per material on a monthly basis
In this spreadsheet, the column FORCASTSALESDVTNINP represents the total quantity of planned independent requirements (forecast), whereas the column SOLDQUANTITY represents the sales orders.
What Else Can I Do with SAP BusinessObjects Business Analytics?
SAP S/4HANA delivers several standard CDS views, ready to be consumed directly by SAP BusinessObjects Business Analytics or by many different applications, such as SAP Lumira. In this article I have explored the options available for manufacturing, but there are views and cubes available for other application areas.
Besides that, instead of creating a custom report when you can’t find a suitable report for your needs in SAP S/4HANA, you can create your own CDS view to read data and use SAP BusinessObjects Business Analytics to read this information directly in Excel. It means that you reduce the development time and cost, as you won’t need to develop an interface. Most of the reports end up being exported to Excel anyway, and therefore, most users will be satisfied by being able to use it as an interface to read live data directly from the system.