Creating custom Hierarchy using flat file in SAP SPM 3.0 with BI 7.3
SAP SPM 3.0
This article discusses how to maintain a user defined hierarchy for a standard SPM info object and how to upload the data from flat file in SPM 3.0. This article gives the reader a step by step process for the development of hierarchy.
Author: Nikunj Kacha
Company: Stellium Inc.
Created on: 27 Feb,2013
Nikunj Kacha is currently working with Stellium Inc. as Associate BI Consultant. He has more than 1 year of experience in SPM and more than 4 years of experience in BW/BI.
Creating custom Hierarchy using flat file in SAP SPM 3.0
Hierarchies play very important role in reporting. SAP SPM supports hierarchies very well. Most of the hierarchies are maintained in SAP R3 source system, you just need to create the data source and extract them to SPM. However, in some cases you need create an hierarchy manually and load it from flat files. SAP also supports loading hierarchies from flat files.
With SAP SPM 3.0 version based on BW7.3, we are given a flexibility to load the hierarchies from flat file by creating transformations, Info Packages and DTP’s. This document explains the step by step procedure to load the Material Group hierarchy data, which is available in a Flat File, to Source system Category(0XALOCCAT) info object.
In this document i have explained everything from creation of hierarchy to adding it at reporting level in a well organized manner.
This article discusses how to maintain a user defined hierarchy for a Standard SPM Info Object and how to upload the data from flat file in SPM 3.0.
This article gives the reader a step by step process for the development of hierarchy and uploading the same to an SPM Info Object.
In order to make an SPM Info Object as hierarchy enabled there are few settings have to be done at the Info object maintenance level. In the Info Object Maintenance transaction, you have to set the flag for the hierarchy basic characteristic “with Hierarchies” and have defined the hierarchy’s properties.
- Hierarchies version depended
- Hierarchy not time depended
- Entire hierarchy is time depended
- Time Depended Hierarchy structure
- Intervals permitted in Hierarchy
- Reverse +/- signs for nodes
Info Object with master data can have hierarchies as part of master data. Many times it is required to load these hierarchies using flat files. These hierarchies are also known as characteristic hierarchies. Characteristic Hierarchy is tree-like structure for the characteristic values for a characteristic. Characteristic hierarchies are stored in their own data tables. Like master data, they can be used in all SPM Info Providers.
Characteristic hierarchies offer you options to create queries for reporting. In the Query Designer you can set characteristic hierarchies in the following ways:
- As a presentation hierarchy for a characteristic, if this needs to be hierarchically displayed
- As a selection for specific characteristic values, if a characteristic needs to be restricted for a hierarchy or for hierarchy nodes.
Structure of a Hierarchy
- Nodes – A hierarchy is created from nodes. The uppermost node is the root.
- Roots – A hierarchy can have several roots. They do not have superior nodes.
- Leaves – The leaves of a hierarchy consist of characteristic values for the basic characteristic and therefore can also have entries in the fact table. A leaf cannot have any sub nodes.
- Basic Characteristic – Hierarchies can be created only for those characteristics that do not reference other characteristics.
Hierarchy Properties A BW hierarchy has the following properties
- Hierarchies created for basic characteristic
- Hierarchies are stored in special master data tables. They behave in a similar way to master data, and can therefore be used and modified in Info Cube.
- You can define several hierarchies for a single characteristic
A hierarchy can have a maximum of 98 levels
Procedure for Hierarchy Creation
- You have assigned an Info Area to the characteristic in the SPM Info Object maintenance. The hierarchy becomes an Info Provider. For this you need to right click the Info area and select Insert characteristic as Info provider.
Than you need to enter the name of the characteristic which you need to add here and click continue button which is a green tick mark. Your info object will be added as an info provider.
We will load the Material group hierarchy to Source sys Category info object (0XALOCCAT)
The sample flat file, which has the Material Group hierarchy, is given below.
For data loading from a flat file we need to create a Flat file source system. Follow the below defined steps to create it.
Step 1:. Go to Source system tab under modeling. Right click on Folder name FILE. And click Create. Create Flat File Source System. Create a Flat File Source System ‘ZFILE’
Then enter the below details and click on continue. Your flat file source system will be created.
Logical System Name:- TEST_FILE
Source system Name:- Flat file source system
Step 2: Data Source Creation
Go to Data source tab under Modeling and right click on application component and select Create Data source.
On the Data source Creation Initial screen fill in all the required fields as below:
Data source: Z_TEST
Source System: TEST_FILE
Data Type Data source: – Hierarchies and click on continue
Fill in all the required details as per the screen shot attached.
Give Short, Medium, Long descriptions in the General Info tab. The screenshot is given below:
In Extraction tab, give the path of the file. In data format select ‘Hierarchy Separated by Separator’ and data separator as ‘,’ as this is a CSV file.
Also, give target info object as ‘0XALOCCAT’ as we are loading hierarchies for Category info object.
Next, click on create header.
Fill in the Hierarchy Name and Description as shown below:
Then click on save button.
In the File Structure tab, we can see the structure of the file. The CSV file, whose screenshot is given above, has been created using this structure.
In Segments/Fields, we can see the different segments. Hierarchy Header is the primary segment.
The screenshot of ‘Hierarchy Header’ is given below:
The screenshot of ‘Hierarchy Description’ segment is given below:
The screenshot of ‘Hierarchy Structure’ is given below:
The screenshot of ‘Descriptions for Text’ is given below:
Now, Preview the data under preview tab. Click on the button named READ PREVIEW DATA. You will need to activate the data source before reading the preview data. The screenshot is given below.
Step 3: Create Transformation
For creating the transformation you need to right click on the data source you just created and select create transformation
Under Target of transformation select the Object Type as ‘Info Object’ and subtype as ‘Hierarchies’. Mention the target as Info object name: 0XALOCCAT.
Under Source of the transformation select Object Type: Data source and Data source: Z_TEST and Source system as TEST_FILE.
There are four segments in total as mentioned earlier. So you need to map all the four Segments. You can select those from Target Segments Drop down Box.
The transformation screenshot for ‘Hierarchy Header’ is given below:
The transformation screenshot for ‘Hierarchy Description’ is given below:
The transformation screenshot for ‘Hierarchy Structure’ is given below:
Here, make sure that you map ‘NODENAME’ field to ‘0XALOCCAT’ info object .If you are maintaining data for any other external characteristics in the flat file ,then make sure that you map ‘NODENAME’ to them as well.
The transformation screenshot for ‘Descriptions for Text Nodes’ is given below:
How to Load a Hierarchy
As you now see that all the required components have been created so now we will move further to uploading Hierarchy.
Step 4: Create an Info package.
Go to the data source you just created and right click on that you will get an option to create an info package just click on that option.
Give Info Package Description as shown below and click on save.
Just recheck the settings in Extraction. Check the flat file you want to load once again under FILE NAME. The screenshot is given below:
Select the Source system Category Hierarchy in the Hierarchy Selection tab. The screenshot is given below:
Data will be loaded till PSA.
After that you can click on the Monitor tab to see if the data load was successful or not.
You will see the screen showing the number of records loaded till PSA
If you want to check whether the records loaded are correct or not than you can check that in PSA Maintenance.
Than you need to select Hierarchy Structure under FROM SEGMENT panel. And click on continue.
You will see the data in the below format.
Step 5: Create a Data Transfer Process (DTP)
Now do the same process to create the DTP. Right click on the Data source which you created and click on Create Data transfer Process. DTP will move the data from PSA to Info object Hierarchy.
Select the Subtype of Object as ‘Hierarchies’ and click on continue. The Screenshot is given below:
Now go on Update tab and Make sure ’Activate Hierarchy’ checkbox is selected.
Activate the DTP. Than go to execute tab and execute the DTP
After the execution of the DTP the screen will show the number of records loaded in the Hierarchy.
Than you need to go to the info provider tab under modeling and right click on the Info Object which has the hierarchy. Here we have loaded the Hierarchy to info object named 0XALOCCAT.
And then select Maintain Hierarchies.
Now here you need to double click on the Hierarchy (Source system Category Hi) which than will be displayed in full mode.
The below screen is displayed which shows that the hierarchy was loaded successfully and stored in SPM system.
Adding the hierarchy at report level
Open the query designer and the report in which you need this particular hierarchy to be displayed.
Then go to the Dimensions folder on the left panel and under that again navigate to Document/Product/Commodity/Category Dimension and select Source system Category and move it to free characteristics area (Drag and Drop) and then click on the save button ( Red circle in screenshot).
After the attribute being added to the free characteristic area we need to activate the hierarchy as well.
Click on the Source system Category info object and go to the hierarchy tab. After that click on the square button for selecting the hierarchy which you want in the report and after selecting it from the drop down list click ok. And then save the report.
After the hierarchy being added it will be ready for the report.
As the Hierarchy Bearing characteristic has been added to the query now we will got the SPM frontend and add the same to the report and check whether we get the determined values.
Before adding the field to the report we need to refresh the data sources so that we get the recent changes captured in the report as well.
Once you login into SPM, on the initial screen itself you will find the Quick links panel under that you need to click on Analysis administration.
Now you need to click on Refresh Data sources button, it will ask you for an option as in the grey color screenshot below, just click on Yes.
After it gets refreshed open the report in which you want the hierarchy to be added.
Just for exemplary purpose I am creating a new report. Go to the Dimensions tab and find out the source system category and there is a small box besides that so click on that and Click on the option “Move to Table” and it will be added as a dimension in the report. For measure I have taken Spend (follow the same method as in the dimension for adding spend measure to the report level). So, after adding click on the Run report button.
After the execution of the report you can see that the report has been displayed with the hierarchy.
If you want to drill down just click on the ( + ) button besides the values in source sys category which will take you to further nodes with its value in spend.
This content is purely based on a Case Study. Some references have been taken into consideration for hierarchy creation process only.
For more information visit sdn.sap.com
Disclaimer and Liability Notice
This document may discuss sample coding or other information that does not include SAP official interfaces and therefore is not supported by SAP. Changes made based on this information are not supported and can be overwritten during an upgrade.
SAP will not be held liable for any damages caused by using or misusing the information, code or methods suggested in this document, and anyone using these methods does so at his/her own risk.
SAP offers no guarantees and assumes no responsibility or liability of any type with respect to the content of this technical article or code sample, including any liability resulting from incompatibility between the content within this document and the materials and services offered by SAP. You agree that you will not hold, or seek to hold, SAP responsible or liable with respect to the content of this document.