Using Temporal Join in Composite Provider in BW/4HANA

Introduction

◉ From SAP BW 7.4 and in BW/4HANA new Composite Providers are the main objects for define unions / joins of existing persistent or virtual data models.
◉ Composite Providers are successors of MultiProviders and BW InfoSets. In classic BW Warehouse only BW InfoSet were responsible for SQL Join between InfoProviders.
◉ From SAP BW 7.5 SP04 and in SAP BW/4HANA Composite Providers also support modeling of temporal joins in order to show time flows.

Demo Data Model

Let’s consider simple sales data model to demonstrate work of temporal join in HCPR.

Advanced DSO and InfoObjects in BWMT

Transaction sales data were loaded in aDSO ZAD_SALES.

Master data were loaded to time-dependent attributes of characterictics ZMANAGER and ZPRODUCT.

Composite Provider in BWMT

The aim of temporal join usage is to analyze sales volume with attribute values at date of actual sale transaction occurred, not at current date for example.

First we joined ZAD_SALES with ZMANAGER, don’t forget to select Key date ZDATE in aDSO. We had to add another time characteristic, because characteristic 0DATE weren’t allowed for key date selection.

Next step we joined result of first join J1 with ZPRODUCT:

As a result output we had:

Query in BWMT

We created a simple query for analyzing if temporal join is working correctly. Query definition is very simple.

Query Monitor 

First of all we started query in RSRT for analyzing join SQL statement. Temporal join restrictions were placed at WHERE. 

SELECT "J1ADSO2"."/BIC/ZSALESID" AS "K____5031",

       "J1IOBJ1"."/BIC/ZGRADE" AS "K____5032",

       "J1ADSO2"."/BIC/ZMANAGER" AS "K____5034",

       "J2IOBJ1"."/BIC/ZPRODMN" AS "K____5042",

       "J1ADSO2"."/BIC/ZPRODUCT" AS "K____5043",

       "J1ADSO2"."/BIC/ZDATE" AS "K____5065",

       "J1IOBJ1"."DATEFROM" AS "K____5077",

       "J1IOBJ1"."DATETO" AS "K____5078",

       "J2IOBJ1"."DATEFROM" AS "K____5086",

       "J2IOBJ1"."DATETO" AS "K____5087",

       SUM ("J1IOBJ1"."/BIC/ZBONUS") AS "Z____5033_SUM",

           SUM ("J2IOBJ1"."/BIC/ZPRICE") AS "Z____5035_SUM",

               SUM ("J1ADSO2"."/BIC/ZVOLUME") AS "Z____5054_SUM",

                   COUNT(*) AS "Z____1160_SUM"

FROM "/BIC/AZAD_SALES7" "J1ADSO2"

JOIN "/BIC/MZMANAGER" "J1IOBJ1" ON "J1ADSO2" . "/BIC/ZMANAGER" = "J1IOBJ1" . "/BIC/ZMANAGER"

JOIN "/BIC/MZPRODUCT" "J2IOBJ1" ON "J1ADSO2" . "/BIC/ZPRODUCT" = "J2IOBJ1" . "/BIC/ZPRODUCT"

WHERE "J1IOBJ1"."OBJVERS" = 'A'

  AND "J2IOBJ1"."OBJVERS" = 'A'

  AND "J1IOBJ1"."DATEFROM" <= "J2IOBJ1"."DATETO"

  AND "J2IOBJ1"."DATEFROM" <= "J1IOBJ1"."DATETO"

  AND "J1IOBJ1"."DATEFROM" <= "J1ADSO2"."/BIC/ZDATE"

  AND "J1ADSO2"."/BIC/ZDATE" <= "J1IOBJ1"."DATETO"

  AND "J2IOBJ1"."DATEFROM" <= "J1ADSO2"."/BIC/ZDATE"

  AND "J1ADSO2"."/BIC/ZDATE" <= "J2IOBJ1"."DATETO"

GROUP BY "J1ADSO2"."/BIC/ZSALESID",

         "J1IOBJ1"."/BIC/ZGRADE",

         "J1ADSO2"."/BIC/ZMANAGER",

         "J2IOBJ1"."/BIC/ZPRODMN",

         "J1ADSO2"."/BIC/ZPRODUCT",

         "J1ADSO2"."/BIC/ZDATE",

         "J1IOBJ1"."DATEFROM",

         "J1IOBJ1"."DATETO",

         "J2IOBJ1"."DATEFROM",

         "J2IOBJ1"."DATETO"

ORDER BY "K____5031" ASC,

         "K____5032" ASC,

         "K____5034" ASC,

         "K____5042" ASC,

         "K____5043" ASC,

         "K____5065" ASC,

         "K____5077" ASC,

         "K____5078" ASC,

         "K____5086" ASC,

         "K____5087" ASC

Analyze Data Result

We opened query in Analysis for Excel, resulted data showed that temporal join were performed correctly, e.i.:

◉ First manager in October had GRADE_05 and in November – GRADE_15.

◉ Price of products showed also different in October and in November.

Even if we exclude almost all characteristics, show only sales volumes by sales managers grades and product names. History perspective is still correct.

Performance and Notifications 

◉ Unfortunately processing of Temporal Join in HCPR is not currently pushed-down to HANA. It is “under discussion” status. 

◉ It means that it is working like old style BW InfoSet and performance are expected the same.

◉ During activation of HCPR with temporal join we had a reminder:

◉ In Query Monitor we didn’t get additional “HANA Calculation Engine Layer” Tab.

◉ In BWMT properties of HCPR and properties of BW Query weren’t changed

Continue reading

Submission Targets in BW Workspace

With a usual approach in data loading with minimal cleansing operation, a user has to create a transformation and data transfer process(DTP) for cleansing and loading the data.

But, With 750 SP04 on HANA, a new agile feature: Submittable providers in BW Workspace was introduced, Where the cleansing and loading of data to the Provider (Advanced DataStore Object) is possible without creating the transformation and data transfer process. Target users of BW Workspace (mainly business users) with modeling authorization can perform this in an agile manner, however  under control of BW administrator.

The process involves the Workspace Administration and Workspace Designer.  In the Workspace Administration, the user must define the BW Provider as Submission Target to which the local data should be loaded from the workspace. In the Provider submission settings for a provider, cleansing operation such as Filter, Master Data check etc..  can be pre-defined by administrator to guarantee a certain quality on uploaded data.

In the Workspace Designer, business user loads the local data to workspace through local provider and submits the data to the BW Provider added in the submission target area in Workspace Administration via Submit Data option.

How to load the local data from the file to the BW Provider (ADSO) in BW Workspace?

Create a BW Workspace from transaction: RSWSPW. In the Submission Targets tab, by opening the InfoProvider Tree, search for the provider and Add it to the submission area.

The fields in the ADSO can be taken as writeable and mandatory Fields. If the fields are marked as writeable, then only those fields are exposed for the data submission and mapping of fields from the local provider. If the fields are marked as mandatory, then those fields has to be mapped to the column from the local provider and they cannot be deselected from the workspace designer.

If none of the fields is selected as writeable or mandatory, then no field is exposed to data submissionà No this leads to an error. You have to select at least one writeable field. Non selection does not make any sense!!!!

Key fields of aDSO are by default marked as writeable and mandatory and cannot be changed

Cleansing operation can be done in 2 ways, First from Workspace administration – defining the submission setting for provider using the Provider Submission Settings option. In the Provider Submission Settings, the admin can select a field and click on Filter icon and filter can be defined for a field by adding a new line. If the Master Data flag is checked, at the submission of data, data integrity check is performed and if the data to be loaded doesn’t match with the Master data values of the Infoobject, an error is thrown for the submission of data. In this case the second way to specify additional cleansing operation can be applied. Namely by the business user in the Workspace Designer. User is able to define additional filter and cleansing rules to transform local file data. More about this will be explained later in this document.

Rules defined in Workspace Administration have to be fulfilled during data submission from local provider. In this way the admin can control the quality and values of data that lands in the aDSO.

To submit the data from the local provider to ADSO, go to Workspace Designer e.g. by executing the transaction NWBC. In the Workspace Designer, click on the submit data. In the Submit Data page, provide the Source (Local Provider) and Target provider (ADSO) name.

In the Field Mapping  step, fields from ADSO that are marked as mandatory in Workspace Administration, are pre-selected and deselection is disabled for those columns in the Workspace Designer, i.e these fields have to be mapped to the columns in the local provider. If the fields are not mandatory, then the selection column will be enabled for the user to enable for a field if the data has to be loaded to that field.

In the Field Mapping  step, additional filter and cleansing operations can be performed on local provider rows, by selecting a mapping pair and clicking on add icon for filter and define an additional filter. Filters set by admin appear also here but cannot be changed.  Cleansing operations such as, Checking the Master Data Values for the Field, Initialize the Values if master data values if not found in the local data, Checking the Conversion error if any, can be performed by defining the cleaning operation for  each single field. There is also the option to specify some rules that will apply for all fields by clicking on the General Cleansing button.

Once the Mapping is specified , checks of all the defined conditions are fulfilled, local provider data will be load to the target ADSO through a Request TSN. On submission, a Write API will be prepared and start writing to an inbound queue in ADSO. Once the data is written into inbound queue, the request can be activated to get the data in active table and can be used in the Queries thereafter. Whether data is directly activated or not can be controlled by Workspace administration.

Continue reading

SAP BW Modelling Tools: ODP - Source System Editor

With SAP BW Modelling tools 1.17, user can create any source system directly from Eclipse based SAP BW Modelling tools. This is editor is only available for SAP BW/4 HANA system whereas in  SAP BW on HANA system user still need to define source system via traditional SAP GUI interface.

In SAP BW/4 HANA, these are few source systems which are available: – ODP based source system (SAPI, CDS, HANA, BW, SLT), HANA source system (SAP HANA Local Database Schema, SAP HANA Smart Data Access, SAP HANA Tenant Database schema), BIG Data and local file source system. We will see how to create ODP-SAPI source system.

The creation of new source system can be done via file menu, context menu of the project, Data source node and from data source folder (E.g. ODP_SAP, ODP_BW, HANA_LOCAL and so on). With the new source system editor, user must select as which type of source system he is creating as ODP based, HANA local source system, etc.

While defining ODP based source system, user must choose as what is the connection of source system. There are three type of connection from which user can choose: –

1. RFC to same system: – To connect to same system with RFC connection.

2. RFC to different system: – Connect to other system like SAPI, BW via RFC connection.

3. HTTP connection: – Connect to other system via HTTP connection. Here the user should navigate to SOA manager to create logical port for the Web services- which must be of same name as of source system.

User also need to create connection to the system if the connection with same name as of source system is not present. In new source system maintenance we have three different RFC connection which can be edited with edit button. The status icon shows whether the connection is fine or broken. For Example, if the one of the connection is broken then status for that connection will represent the same with red cross sign and if connection is fine then the same is represented by green check sign.  There are 3 connection that can be seen in ODP based source system maintenance screen: –

1. Logical Destination: – Used to connect to Remote System.

2. Dialog Destination: – used to connect to remote system with a dialog user. This is optional.

3. Callback Destination: – used to connect from remote system to BW system.

With respect to ODP based source system user have to select the context of ODP source from the dropdown menu “ODP Context”. These are the context  :-

1. [SAPI] DataSources/Extractors

2. [BW] SAP NetWeaver Business Warehouse

3. [ABAP_CDS] ABAP Core Data Services

4. [HANA] HANA Information Views

Another important option in new source system editor is “Remote tree” check box. The Upload tree menu on toolbar of source system maintenance screen is associated with status of remote tree in source system.

It is enabled for only source system type ODP and only when Remote tree is enabled. This option is not available for all other source system types, e.g. HANA data source, file. In this case, a “local tree” is used which is shared by all source system which do not have a “remote tree” and (in case of ODP) not for all releases.  For ODP-SAPI, the upload tree functionality brings the Hierarchy defined in source system application component. For ODP-BW, it uploads brings the info areas created.

Continue reading

SAP BW/HANA – Data Flow Migration Tool with Data Transfer

Audience: 

This Blog is primarily aimed at SAP BW Consultants who are planning to prepare the system for BW/4 HANA and also looking for options to migrate existing objects(DSO, MP) to new HANA Objects(ADSO, CP).

Scope:

We have recently upgraded our BW System from BW 7.5 SP4 to BW 7.5 SP8 with BW/4 HANA Starter Add on Installed.

There are some good blogs on the conversion process to deliver BW/4 HANA System, In this blog post i want to mainly concentrate on the Data Flow Migration Tool With Data Transfer option which is available now with BW 7.5 SP8 with BW/4 HANA Starter Kit Installed.

Below are the details of Installed software component version.

In BW 7.5 SP4, we have used RSO_CONVERT_IPRO_TO_HCPR to convert a Multi provider to Composite provider, by retaining the Same Info Provider name and BEX Queries. As a next step, we have waited for the migration option for complete data flow to new objects with Data transfer. And finally we are there with this upgrade and we have successfully tested the functionality in our Sandbox System. There were couple of issues we have seen and worked with SAP for Solutions and there were pilot notes we applied.So below is how we have started.

1. First upgraded our system from BW 7.5 SP4 to BW 7.5 SP8 with an expectation of having this migration tool available. But we realized, this is not the only pre requisite and SAP is providing this migration tool with data as part of their journey to BW/4 HANA.

2. So we have installed the SAP BW/4 HANA Starter Kit, still no luck. Transfer tool was not yet available( Starter Kit can also be installed as part of the upgrade)

3. Then SAP has provided us with 2383530 – Note which had a .XML file and Z program(Z_SAP_BW_NOTE_ANALYZER) for analyzing the notes and then we have applied around 360 notes using the XML file in the note. Finally, we installed 2500045 – Pilot note to install “Transfer Toolkit”.

4. Finally we have the Transfer Tool Kit available in our sandbox system.

Note – We are still on the compatibility mode, we haven’t switched our system to B4H.

How to Use the Transfer Tool Kit

1. We can either use Program RS_B4HANA_TRF or STC01/STC02 and select Task List as SAP_BW4_TRANSFER_INPLACE.

2. Once you the this, it will create a new task List Run and you will see below options, where we need to defined the scope , objects mapping etc.

3. We define the scope for the migration, Which Multi Provider and its data flow we want to migrate. I have used a MP(0TCT_C25), which had a cube(ZTCT_C25) under it. So the expectation is to get this MP converted to a Composite provider and Cube migrated to ADSO with Data and Queries are retained in the new MP with no changes to Enterprise ID’s

4. Below is the object List, we can select and UN-select the objects to be transferred.

5. We can run the process after defining the cope and objects list. It will execute each and every step we saw in screenshot in step2 and migrates the data flow to new objects and also copies the data along with request ID’s( we can also add a break point at each step to review the process)

6. Now we see a composite provider with same name and ADSO with same name as cube. We can also see the data copied to new ADSO and also the request management.

Continue reading

How to change/create InfoObjects with RSD1 as of BW 7.5 SP04 on HANA

Introduction

InfoObject maintenance in the BW Modelling Tools (BW-MT) was made mandatory starting with SAP BW 7.5 SP4 on HANA. Since then it is not possible to maintain/create InfoObjects from the SAP GUI using the transaction RSD1. Displaying though still works.

This change surprised the one or the other as this is the first existing BW object which must be maintained in the BW-MT. Up to now only new objects like Composite Provider and Open ODS Views were only available there.

It was tested under release 7.5 SP04.

Please read the disclaimer carefully

Be careful, by following this. You basically tell the system that it is not a BW on HANA system.
You should also consider that there is some stuff which can only be maintained in the BW-MT, for instance transitive attributes.
It should be only used in case you are running into problems using the BW Modelling Tools (BW-MT).

Step by Step

Step 1: Go to tcode- RSD1

- Call transaction RSD1
- You will see that the transaction is in display mode, i.e. no create or maintain button.

Step 2: Enter nohdb into the OK Code field

- Enter “nohdb” into the as per the following screenshot and press enter

Now you can see Maintain button in below screen.

Continue reading

ABAP CDS in SAP BW

Introduction

ABAP Core Data Services (CDS) Views were introduced with SAP ABAP 7.40 SP05. CDS allows the creation of rather complex views that by far exceed the capabilities of database views created in SE11. Besides their role in the ABAP Programming Language, CDS Views are the technology behind the virtual data model on top of the SAP S/4HANA application tables which, e.g. is used in SAP S/4HANA Analytics.

This aspect – the role of CDS in SAP S/4HANA Analytics – frequently brings up the question whether or how CDS might be used in the context of SAP BW powered by SAP HANA or SAP BW/4HANA.

The purpose of this blog is to clarify the role of CDS in SAP BW.

ABAP CDS in SAP BW – the reporting perspective

Since CDS is used to build the Virtual Data Model in SAP S/4HANA, a frequently asked question is, whether CDS Views could also be used to build a virtual layer on top of BW InfoProviders, most prominently Advanced DSOs, which could be consumed in analytic front end tools like in the SAP S/4HANA environment. Or, whether CDS could be used to enhance a SAP BW data model with data from a Z-table. The clear answer to this is – for a number of reasons: No.

The SAP BW data model

Anyone familiar with the way SAP BW generates database tables from its high level (InfoObject based) data model, knows that SAP BW entities typically are split into multiple tables with implicit relationships managed by the BW application. In addition, many of these tables contain technical fields which are of critical importance when writing to or reading from the object. All the logic that is required to correctly interpret these fields and deliver correct and consistent query results, is part of the BW application which creates the correct JOINs and filters at query runtime. All the SAP BW modeling artefacts (e.g. CompositeProviders, Advanced DSOs) are built such that they are aware of all these aspects and relieve the user from a detailed understanding.

The SAP BW security concept

SAP BW secures data from un-authorized access via Analysis Authorizations. This is again a concept specifically designed with the SAP BW data model in mind and it differs significantly from standard filter-based database security concepts. And just as with the data model aspects above, the evaluation and interpretation of Analysis Authorizations is done in the SAP BW application. Therefore, accessing SAP BW data via SQL or OpenSQL (which is the standard way to consume CDS views), would completely bypass the security layers of SAP BW and open the way for un-authorized access, which is prohibitive in pretty much any real world scenario.

Mixed Scenarios

These aspects have since long been the main reasons why SAP does not support direct consumption of SAP BW generated tables. As you may be aware, SAP BW powered by SAP HANA or SAP BW/4HANA provide capabilities addressing specifically this point – opening up SAP BW for SQL consumption. The approach taken here is to generate SAP HANA Calculation Views from the SAP BW data model that incorporate all the dependencies within the data model and the correct interpretation of all the technical fields and creating SAP HANA Analytic Privileges from SAP BW Analysis Authorizations to ensure security.

Conversely, when it comes to integrating non-BW data (e.g. in a Z-table or Z-view) with the SAP BW data model, this is done via Open ODS Views, which – conceptually – put a “SAP BW-shell” around a table or view.

Thus, also in this environment, CDS does not play a role in SAP BW.

SAP BW Technical Content

Tracking and monitoring the operations of a SAP BW system is one of the areas, where CDS in fact is playing a role. For example, SAP BW comes with tables containing statistics on loading and querying data, status information about load requests, etc. With SAP BW 7.5 SP04, CDS based technical content is shipped that provides administrators/modelers with such information. Being built on CDS, this content is much leaner and simpler than traditional SAP BW technical content, does not require loading of the data to a SAP BW InfoProvider, or even activation of SAP BW objects.

Consuming CDS Views in SAP BW

SAP BW can, however, leverage content built using CDS Views – most prominently SAP S/4HANA Analytics. For one, there is a dedicated Source System type ODP-CDS in SAP BW (since release 7.4) that allows to load data from a CDS View into SAP BW. This integration currently does not provide all services known from the classic extractor based data provisioning, but in certain scenarios it can be a valid option. Secondly, SAP S/4HANA Analytics content can be virtually consumed and combined with data in SAP BW via Open ODS Views.

ABAP CDS in SAP BW – the ETL perspective

One specific use case where CDS views can play a role are the SQL expert routines in SAP BW Transformations. To simplify, for example, a complex look-up on customer specific Z-tables in a transformation, it can be very useful to define a CDS view on top of these tables and use that CDS view within the SQL export routine.

Continue reading