Hi folks, here I am discussing various methods of combining different key figures from Multiple Reports. We often face scenarios where we need to show key figures, being calculated in separate queries only at runtime. Because of their complex calculations we have to approach this very systematically.
e.g. we have 5 cubes such as Quality, Production, Finance, Inventory and Sales.
Each of the cube's data is giving a 'Target' key figure, say Material and Plant wise:
1. Defect Ratio Target from Quality Cube
2. Production Target from Production Cube
3. Revenue Target from Finance Cube
4. Inventory Target from Inventory Cube
5. Sales Target from Sales Cube
I am taking these elements just for reference, to show how different key figure can come from various infoproviders. Actual scenario may well differ with this.
All 5 key figures are formula key figures and coming through the complex combination of RKFs and CKFs.
- Multiprovider Queries
- Workbooks with VB Macros/vlookup
- Analysis Process Designer
Without reloading data, first solution, which comes into mind, is to go for a Multiprovider.
Combining all these cubes in a multiprovider and using same formulas on query level to get desired target key figures based on Material and Plant.
But at many a times it may not seem feasible, as all these cubes are with different dimensions and structure. Union of all this data will not work while looking for Material and Plant wise data. Also those complex calculations at key figure level may be difficult to remap.
Workbooks with VB Macros/vlookup
Another workaround would be the use of Workbooks.
More then one queries can be inserted into a workbook. A link exist between BEx and BW Server hence the cell areas of the worksheet and the data of the InfoProvider upon which the query is based are well connected. This link stays in place until you interrupt it (See Settings).
For our scenario, we can place those 5 queries in a workbook and add one more worksheet with this. It will be a standard excel sheet where we can derive formatted results from other worksheets through excel formulas (vlookup and other airthematic formulas).
Queries with cube data:
Workbooks are same as Excel Workbooks, with all Excel functions available for enhancements.
Final Layout derived from Master Worksheet:
Workbooks can help a lot where specific formats of reports are required, with manipulation and adjustment on BW reports.
We need to check user acceptability for BEx interface instead of Web.
Analysis Process Designer
We can also achieve this using the Analysis Process Designer, or APD (RSAMWB).
The analysis process designer is a workbench with GUI for the extraction, transformation and loading of BW data. It is very simple and easy to use as it is supported with Drag & Drop functionality.
Basic Elements of APD:
1. Data sources,
3. Data Targets
For our problem, here is the step by step approach:
Create five tabular queries based on original queries, each will provide information for one target key figures.
Target Key figure (e.g. Defect Ratio Target)
Same way create queries for other target key figures.
If any variable entry is required (there should not be any user entry), create variant for it through transaction code RSRT.
Take Query as Data source in your first APD for Defect Ratio Target and assign it the query, which we created on Quality cube. Give variant information as well, if any.
According to the data available in query (may differ from required format), use transformations (e.g. Sorting or ABAP routine).Connect Data source and Transformation.
Create one transactional ODS with the format appropriate for Target key figure. Also create those Target key figures as data dictionary objects (simply info objects). It may be required to take monthly data then Calmonth would also come in query output. Key fields and data fields of ODS must be taken accordingly.
Now take Writing Data to a Transactional ODS Object as final data target. Assign the ODS, which you created in step 5 to this item for transferring transformed data.
Field assignment for the field has to done explicitly with inbound data flow arrow.
With a technical name, save this Analysis Process. You can also check and activate it here.
Execute this Analysis Process and data will be written to Data target.Perform the same steps for other queries also. You'll get 5 Analysis Process with 5 Transactional ODS.
Combined reporting on this data
Create a cube with all Target Key figures and write update rules from those Transactional ODSs to this cube and load.
Now just create a query on this cube, it will show all 5 target key figures with respect to Plant and Material from cube data.
This load can be automotive through process chains. APD can be executed with process type ABAP program (run the ABAP report RSAN_PROCESS_EXECUTE).
Defect Ratio Target)