Enhance the visualization of your data on a dashboard (based on BPC Data) by using the Hichert’s Success Rules for data visualization
In this article I’ll show you how to build a dashboard based on the Hichert’s Success Rules for data-visualization. I’ll use the graphomate Add-On which implements these success rules for SAP Dashboard Design.
The dashboard is built on data from a SAP BPC System and use for the connectivity with the BPC backend-system the EPM-Connector which comes with the EPM Add-In for MS Office.
1 Business Scenario
The business wants to compare the ACTUAL and FORECAST data (KPIs: Revenue and Cost) for the top 10 companies of the group. Furthermore, it wants to make some simulation for forecasting of the Costs (Manufacturing, Material, General Expense, Wages and Salaries…etc.) according to the variation of the sale quantities and the unit price for a given product.
At the backend, there is a SAP Business Planning and Consolidation, version for SAP NetWeaver (BPC for NW) system and for the frontend a dashboard created with the SAP Dashboard Design tool (formerly called Xcelsius) is preferred. Using a flash-based dashboard provides a zero footprint solution for viewing critical data in real-time. The dashboard user would not need anything more than flash player to view the data. This type of data visualization is very attractive to high level decision makers in the enterprise.
2 Software Versions
The required software for this scenario are the following (Only to build the dashboard. The versions mentioned here are the one I used for this tutorial):
- SAP Dashboard Design SP06 Patch 01
- Graphomate Enterprise version 1.3-F4 (Add On for Dashboard Design from graphomate GmbH)
The following software are optional (only if you intend to use BPC as backend system and the EPM Connector for the connectivity):
- SAP BPC 10 EPM Add-In SP14 Patch 02
- EPM Connector of EPM Add-In, (add-On for Dashboard Design, located in the EPM Add-In installation folder)
- SAP BPC 10 ODBO Client
- Microsoft Visual C++ 2005 SP1 Redistributable Package(x86)
For simply running and viewing the dashboard, only Flash Player is required.
All software (except for the Microsoft Visual C++ component) can be downloaded from the SAP Service Marketplace. It is suggested to always apply the latest support package for each software component.
3 Steps to build your dashboard
1- In SAP Dashboard Design, create a new dashboard and used a local connection to Log into the backend System (BPC server)
Enter your credential for the connection to the server and click Log On
2- Create a new BPC-Report in the Excel-sheet
Click on “New Report” in the EPM tab of the excel workspace.
The “Report Editor” dialog will then be launched. Here you can configure the report output.
Drag and drop the ENTITY.PARENTH1 node from the cube dimensions box into the “Row” box.
Drag and drop the CATEGORY.PARENTH1 and the ACCOUNT.PARENTH1 nodes from the cube dimensions box into the “Column” box.
Drag and drop all remaining dimensions except TIME and PRODUCT from the cube dimensions into the Page Header box. Note: The dimensions TIME AND PRODUCT will be configured in the dashboard as dynamic filters.
For each dimension in the row and column box, select the members by clicking the checkboxes, then clicking the “Right-Arrow” key to move the selections into the “Selected Members” box. Then click “Ok”.
Note: For the ENTITY dimension, you can just select the top 10 entities you want to show and eventually the parent node for the grouping/total.
For each dimension in the “Page Header” box, select one or many members to define the context of your report
3- The report is then generated in the excel spreadsheet. The values are retrieved from the BPC system and statically stored in the excel sheet.
4- Before starting to build the layout of your dashboard, it is recommended to prepare your excel sheet in order to facilitate your subsequent actions.
I recommend to first copy the data return by you report (actually those data you want to show on your dashboard i.e. the top 10 entities + the total) in a specific area on your sheet.
In the sheet, mark you different data area with different colors
Some Excel EPM-functions like the function EPMOlapMemberO() are not supported in Dashboard Design.
In order not to have the label value not showing on your dashboard, you have also copy the columns and rows where those functions are called to your dashboard data area.
Note: Further on, you can set a data connection to the BPC server to update the value for these fields (see my previous post on “How to use the EPM Connector to visualize BPC Data via Dashboard Design” in the “Extra Notes” section)
Lastly, it is important for better visualization to define an optimal “Scale” for your data in order to best use the available space for important information. You may have to convert you data in thousand (by dividing by 1000) or in Million (by dividing by 1000000) depending on how big or small your figures are.
5- Next, you will begin to build the layout of the dashboard starting with the first chart for the Revenue KPI.
Drag and drop the components for your dashboard from the components section on the left into the layout editor.
Start with the graphomate Enterprise component to build the chart for the comparison of the Revenue KPI.
Configure your component as follow:
On the “General” Tab
Under Chart, select the “Column/Bar” Chart type, then choose the structure orientation for the chart and check to show basic values (this is to allow to show both values ACTUAL and FORECAST on your chart). In addition to the base chart, check to see the percentage deviation chart and choose the “Needles” representation.
Under Data, click on the button next to the “Measures” input box and bind this to the cells where you want to hold your KPIs data from (i.e. actual Revenue). Then click the button next to “Basic values” input box and bind this to the cells from where you want to get your basic-KPIs for comparison (i.e. the forecast Revenue). The same way, bind the “Category labels” to the cells where you load you entities labels. Check to specify that in this case, positive deviation is good (for Revenue). Also check to display the category labels on your chart.
For the data type definition, you should have previously defined an area in your sheet for the data type for both KPIs (Actual and Forecast). Next bind the button next to the data type for each KPI to the corresponding cell range in the sheet.
The data type is useful in formatting the data accordingly to the definition you made in the setting for each type of data (AJ=Actual Year, PR=Forecast, VJ=Previous Year, PL=Planning).
These settings are made under the tab “Appearance > Data types”.
Under Label, click the button next to “Measures” and bind it to the cell from where you want to hold the label for the KPI (i.e. Actual). Do the same for the “Basic values”, this time bind it the label for the basic values (i.e. Forecast). Then bind the “Title” to the cell with the chart title (i.e. Revenue). Finally, check to display both the legends and the title.
You can continue if you need with other settings for your chart i.e. “Appearance” and “Behaviour”
You end up with a chart similar to the one on the figure below:
6- Repeat the previous action to build the chart for the Cost KPI.
This time, choose not to display the Category labels, since the both chart (Revenue and Cost will use the same). Also, do not check “Positive deviation is good” in the case of cost the positive deviation is not good.
The end result is then similar the one shown below.
7- Next drop on the layout the controls for the simulation.
Start with the a List box to allow to user the select an entiy on which the simulation will be based.
Configure the list box accordingly
Next drop 2 Gauges on the layout; one for the General Expense and the other for the Wales and Salaries. Then configure the Gauges to reflect the values of the General Expense and the Wales and salaries that you defined on your sheet.
Now add 4 horizontal sliders to allow the variation of the parameters quantity of sales, Sale Price/Item, Material Cost/Item and Manufacturing cost/Item. Then configure them to behave accordingly.
Also add a “Reset” button to reset the simulation to the initial values.
8- Finally, add another chart to show the variation of the Cost KPIs when the parameters change in the simulation.
Drop another graphomate Emterprise component on the layout and configure it like on the picture below (This time, use a waterfall diagram instead):
You end up with a Waterfall diagram showing how the different cost behave when the parameters (quantity of sales, sale price/item…etc.) change
9- You can also enhance the dashboard with controls to hold live data from the BPC server, to allow the user to select new filters and submit a new request for data and to write-back the simulation parameter into the database.
The complete figure looks as follow:
10- The model is now complete. You can test it out by clicking the “Preview” button.
Once the dashboard is loaded, you can select a specific product for the filter and click the “Submit” button. You will then hold form the BPC server the data related to this product. By selecting in the List Box an entity, you can make some simulation by changing the values of the different parameters. Afterward, you can save your change back in the BPC System.