Step by step Analysis Process Design

[Former Member]

Applies to:

SAP NetWeaver BI 7.0 releases. For more information, visit the SAP Business Intelligence

Summary

This document explains you the idea about how to extract data from queries and tables, transform it in to required format and place it in output file (.csv) automatically by using APD process in SAP BW/BI.

Author:        Riyezuddin Syed 

Company:    Tata Consultancy Services, Bangalore, India

Created on:  27 March 2012

Author Bio: Riyezuddin Syed is currently working in Tata Consultancy Services as a SAP BI Consultant, he has a rich experience in SAP BW/BI Implementation as well as support projects.

Table of Contents

Introduction.

Analysis Process Designer (APD)

Scenario.

Step by Step Process:

Step 1:  Create BEx Queries.

Step 2:  Create APD Process.

Step 3:  Configure the data source in APD.

Step 4:  Use Filter to restrict Language Specific Description and Materials.

Step 5:  Joining Data from Different sources.

Step 6:  Define ABAP Routine to update Current date & Material Description.

Step 7:  Define Aggregation of Data.

Step 8:  Use Union to join the data sources.

Step 9:  Define the Data Target

Related Content

Introduction

Analysis Process Designer (APD)

In SAP Business Intelligence the Analysis Process Designer (APD) makes it possible to find and identify the hidden or complex relationships between data in a simple way. Various data transformations are provided for this purpose, such as statistical and mathematical calculations, and data cleansing or structuring processes.

The analysis results are saved in BW data targets, file or in a FTP system. They are available for all decision and application processes and thus can be decisive (strategically, tactically and operatively).

Scenario

In this scenario we are planning to merge data from two queries, two text data base tables and combine the output based on the required format. Let’s take an example of two queries which are having material, customer, fiscal year period and sales quantity values for two regions. Material description will be fetched from text database table. The below are conditions for getting the required output

ü  Restrict for only few materials

ü  Always it should be executed from 001.2010 to current month and year.

ü  Material description has to be displayed as 10 chars in the report output and without ‘-’

ü  Display the APD execution date.

Step by Step Process:

Step 1:  Create BEx Queries

Create 2 BEx Queries (i.e. Query1 & Query2) for each region wise, based on the required info objects and restricted from 001.2010 dates to current date.

Step 2:  Create APD Process

Go to RSA1 Click on  APD Icon. Under Analysis Process select  General right click ‘Create’ and give the Description and click on Save it will ask for Technical name, enter and save it. In APD we have Data sources, Transformations, and Data targets which can be used based on the requirement.

   

Step 3:  Configure the data source in APD.

APD will support different types of data sources like Attributes, Info Providers, Query, File and Database table. In this example we are using Query and Database table as sources.

i.)    Use Query as data source

Select the data source “Use Query to Read Data”  where we will give the Query names as data source. Drag & drop the “Use Query to Read Data” into Designer, then double click on it and give the Query description and click on “Choose query” to select info provider & query developed on it.

Here I have given data source description as ZTEST_QUERY1, select ZTEST query which is developed on ZTEST Infoprovider, as shown in the below screen shot.

Repeat the same for Second Query i.e. ZTEST_QUERY2 also, it will be displayed as below in the Designer.

i.)    Use Table as data source

In Query we can see the attributes associated with Characteristics, but in APD we can’t fetch the attributes along with Characteristics from the query. Here query will displays characteristics only.  So to fetch the material description we need to use material master data text table as source (i.e. /BIC/TMATERIAL)

Select & drag the ‘Read data from data base table’ object from the Data Sources. Right click the object and enter the Description and Database table names.Here I have given data source description as ZTEST_QUERY1 Material, see the below screen shot.

Repeat the same for Second table i.e. ZTEST_QUERY2 Material also; it will be displayed as below in the Designer.

Step 4:  Use Filter to restrict Language Specific Description and Materials

Material text table (i.e. /BIC/TMATERIAL) will have different languages, to restrict the language specific to English, and specific material restriction in the output we will use Filters. Select and drag the ‘Restrict amount of data’  filter from ‘Transformations’ and give Material table data source as Input to this filter.

i.)    Field Selection Tab

Enter the Description of the Filter. Here I have given filter description as ZTEST_QUERY1_Filter, see the below screen shot.

Move the list of fields on which filter has to be added from ‘Available fields’ area to ‘Fields to be Filtered’ area.

Here we are filtering based on Language Key and Material 

i.)    Filter Conditions Tab

The selected fields for filter will be displayed in this tab. Enter the selection values which needs to be filtered. Here we are Filtering Language key as ‘EN’ and Material list from 200-000-121 to 200-010-950.

Repeat the same for Second filter i.e. ZTEST_QUERY2_Filter also, it will be displayed as below in the Designer.

Step 5:  Joining Data from Different sources

Now we are having two data sources, one is from Query output and other one is Material Text table.

Select ‘Join data from two data sources (join)’  from ‘Transformations’ in to designer and connect this to Material text and Query outputs as inputs. Select it and right click go to properties and give the Description.

Then connect the respective fields to each other based on the requirement. Here we are joining Material in Query and material in Text table as key fields.

Repeat the same for Second Query i.e. ZTEST_QUERY2.

Step 6:  Define ABAP Routine to update Current date & Material Description

Material description only with 10 chars without ‘-‘ and Execution date (i.e. APD run date) has to be displayed in the output. To achieve this we need to pull ‘ABAP Routine’  object from the Transformations in to designer place.

Select ZTEST_QUERY1_connection as input to this.          

i.)  Right click the ‘ABAP Routine’ and select Properties and give the description (i.e. ZTEST_QUERY1_Routine) in General tab.

ii.) Select ‘Source Flds’ tab move the fields from Field list to Source Fields, if require move to Grouping Fields. Here we are moving material, fiscal year period and description

iii.)     Select ‘TargetFlds’ tab add a new filed as Execution Period to display the APD execution date and change the description of the fields as per the output displayed.

iv.)     Select ‘Routine’ tab add the code as shown in the below screen to format the fields, Execution Period and Material description.

Check the code and click on ‘Ok’.

Repeat the same for Second Query i.e. ZTEST_QUERY2 also.

Step 7:  Define Aggregation of Data

Aggregate the data based on material and customer, to do this selects the ‘Aggregate data’  object from transformations and place in designer. Connect ZTEST_QUERY1_Routine as input to this.

Right click on Aggregate data and select Properties.

Enter the Aggregation description as ‘ZTEST_QUERY1_Aggregation’. Based on grouping move the fields from Available fields panel to ‘Selected Grouping Fields’ and move the key figure which is to be aggregated  to ‘Selected aggregated Fields’.

Click on ‘Ok’ and save the APD. Repeat the same for Second Query i.e. ZTEST_QUERY2 also.

Step 8:  Use Union to join the data sources

To combine the two data sources we will use ‘Union’ object. Select ‘Unify Data from Two Data Sources (Union)’   drag into designer. Union will combine the two data sources based on similar properties.

Connect the ZTEST_QUERY1_Aggregation and ZTEST_QUERY2_Aggregation as input to Union and give the description as ZTEST_Union click ‘Ok’ and save the APD, it will be displayed as below in the Designer.

Step 9:  Define the Data Target

APD will support different types of data targets like Attributes, DSO,CRM attributes, Decision Tree, Clustering Model, Analysis model, Scoring model,3^rd party data mining model.In this example we are using File as data target.

So now we need to define the output file target. We can define target as Application server (i.e. using Logical file name) or Client location. In this example we are using target is Client Location.Select ‘Write data to File’  object from Data Targets and place it in the Designer, give the ZTEST_Union as input. Right click and select Properties. In The Data Target tab enter the Description as ‘ZTEST_Data_Target’, choose Client Work location and give the output file path.

Here we can select Write mode based on the requirement,(i.e. each time new file or over write the existing one). In this example we are selecting ‘Create File Again. If Already Available: Overwrite’. Click on CSV File Properties tab, enter the Data separators and Separator Field Name/InfoObject.

Once it is done click on ‘Ok’ and save the APD. Finally the APD looks like below:

Check the APD and activate and execute it. The below is the display of output file.

Related Content

BEx Query Designer

Analysis Process Designer

Administrator Workbench