Direct Data Access DTP – SAP Remote Cube or Virtual InfoCube

This post is just for demonstrating the use of virtual infocubes and direct access DTPs as these are not used so frequently while modeling.

There is no data acquisition and the BI system connects remotely to the source when the query is called by the user. This is particularly useful where very less data need to be retrieved and it changes frequently.

There is also a concept of using Open ODS views if your BW system is based in HANA, where there is a similar access to source tables directly and queries are based on the source fields.

Now coming to this post example, I will demonstrate here virtual infocube which reads the data from a flat file stored in my local system using a direct access DTP. Whenever I change the data in the file and see the data in the Virtual cube, it displays the changes.

Aim: Data access from flat file source system using remote cube or virtual InfoCube.

T-codes: RSA1, RSA11, RSA13, RSA14, RSD1, RSD5, RSDS, RSDCUBE

Part 1: Data Modeling

Step 1: Create a flat file for the data like below-

Save the file as csv, and close.

Step 2: Logon to SAP BI

Step 3: Check for the source system (Tcode: RSA13). Select a file source system and in the context menu – select ‘Check’

msg: ‘Source system connection is ok’

Step 4: Create infoObjects (Tcodes: RSA14, RSD1, RSD5)

Example: ZCUST_BP, ZPROD_BP, ZPQTY_BP, ZSALES_BP, ZPRFT_BP, 0UNIT, 0CURRENCY, 0CALDAY

Step 5: Create datasource with Direct Data Access (Tcode: RSDS)

Step 5.1 Create an application component

Select ‘Datasources’. Select ‘Choose source system’. Select ‘File’. Select your flat file source system.

Select ‘Datasource’ root node and in the context menu, select ‘Create Application Component’.  E.g. ZAC_SALES_BP (Desc: Sales Application Component for Berger Paints)

Step 5.2: Create datasource for the file transaction data

Select your application component, context menu, select ‘Create datasource’.

Datasource: ZDS_BP

Source System: FLATFILE

Type of Datasource: Transaction data

Select ‘Continue’

Select ‘General Info’ tab and give the short text.

Select ‘Extraction’ tab and select the option ‘Direct Access: ‘ Select ‘Allowed’

Adapter: ‘Load text type file from local workstation.

Name of the file: Browse and choose your file.

Header rows to be ignored: 1

Data format: Comma separated CSV

Data separator: ,

Select ‘Proposal’ tab

Select ‘Load Example data’

Go to ‘Fields’ tab. Select ‘Yes’.

Assign infoobjects to the fields and then ‘activate’.

Continue with warnings.

Note: System generates a PSA table.

Select ‘Preview’ tab and select ‘Read preview data’

Step 6: Create virtual InfoCube (tcode: RSDCUBE)

Select ‘InfoProvider’ under Modeling.

Select your InfoArea, in the context menu ‘Select ‘Create virtual Provider’

Give InfoCube technical name and description: ZVIC_BP (Virtual Infoprovider for Berger  Paints)

InfoProvider type: Select ‘Based on Data transfer process for direct access’

Select ‘Create’.

Rename the dimensions and create new ones-

• Customer (include info object ZCUST_ID)
• Product (include info object ZPROD_ID)

In the key figures folder, select ‘InfoObject direct input’ and give the key figure info objects-

• ZSALES
• ZPROFIT
• ZPQTY

In the time dimension, give 0CALDAY

Select ‘Activate’

System generates fact tables and dimension tables. Press F3.

Observe the icon for the virtual provider.

Now create transformation and activate it. This completes the data model.

Part II: Perform direct data access

Here, no need to do data loads or extraction.

Step 1: Create DTP for direct data access. Activate it. Observe that the ‘Execute’ button is disabled.

Go to the infocube, and select ‘display data’.

Note that the data is retrieved from the source directly.

Exit from this screen by clicking on SAP logo and selecting ‘Stop transaction’

Step 2: Open the file on your system and modify the record.

Save and close the file.

Step 3: Perform ‘Display data’. Enter tcode ‘RSA11’ and expand your infoarea.

Select your virtual infocube, in the context menu, select ‘display data’

Cube displays the latest data from the source.