The goal here is to be able to summarize and extract various airline comparison report data with as much freedom as possible when specifying what/how various airline attributes should be returned. An obvious choice is the olap cube, allowing us to define dimensions representing the rows/cols of the table UI element or chart UI series, both used later for displaying the olap data. One can restrict dimension values to certain individual values or intervals. The report actual data defined by key figures can be transformed/modified with the help of run-time olap variables. This allows us to define one olap cube and present the same data in lots of different ways with almost no additional effort. The olap cube representation in SAP is InfoCube.
In Business Warehouse, before being able to perform cube queries, one needs to define the cube structure and populate it with existing data from an existing data source following a set of clearly defined transformation rules. These steps are detailed below.
I. Create a datasource
Run RSO2 – Maintain Generic Data Sources and create a new datasource. In the current report scenario, transactional data is used and the “Extraction from DB View” option is used with target table SFLIGHT.
II. Make the datasource available within the BW system
Run RSA1 – Data Warehousing Workbench, Modeling->Source Systems.
- under BI, select your source system, right click -> Display Data source tree
- right click on DataSources for <your_BI_system_name> and select Replicate DataSources.
- the new datasource will be created in the BI system, when prompted create it as DataSource (RSDS).
- activate newly created datasource
- right click ->create InfoPackage
- in newly created InfoPackage -> Schedule tab -> Start (start data load immendiately selected)
We now have the raw data present in the BW system. This is not used by BW in any way, it’s just the source for the final data BW will be using based on data transformations we need to define. Raw data entries can be the base for calculating an entire different set of final data entries depending on requirements.
III. Define how the final data will be stored.
- create an InfoArea to store all related InfoObjects under one location
- create InfoObject Catalogs under this InfoArea (Characteristics and Key Figures), activate them
- right click InfoObject Catalog -> Create InfoObject (attributes under the object catalog, the base unit)
IV. Define where the final data will be stored.
- click on InfoProvider left menu, you’ll see the created InfoArea there, right click create InfoCube
- add dimensions to cube by selecting an InfoObject catalog in the left area and dragging them under cube dimensions.Â activate it.
V. Transform data from raw to final via a transform object
- right click cube, Create Transformation… source of the transformation is the above datasource
- on next screen connect via mouse cursor datasource fields with target InfoObjects. save and activate the transformation.
- create data transfer process, right click cube->create data transfer process. save, activate and execute.
All data preparation is complete, using BEx Query Designer we can define queries to be used on the newly created InfoCube.
VI. Define queries.
Within BEx Query Designer we get to define cube queries by specifying query dimensions from the overall cube dimension. Query variables can be used in case we want to modify the query at run-time. This way we can restrict the dimension possible values, use predefined currency conversions (via RSCUR) based on an input currency or create new key values as function results from existing key values.
Query testing can be done via RSRT- Query Monitor.
This post is part of Airline Report.