How to substract one row data from another

Scenario: The source data is coming in the form of ytd numbers. Below is an example of year to date (YTD) numbers:

ytd numbers.png

The user wants to see the monthly values instead of the YTD values, below is the data which the user wants to see in the report:

ytd numbers.png

Thus the monthly 2017.02 values are 2017.02 value – 2017.01 value.

Thus, the first month value will be as it is, the second month value will  be second month – first month, the third month value will be third month value – second month value.

Solution:

Continue reading →

How to read hierarchy child entries in DTP

Scenario: There is a requirement to load data with a filter on a certain infoObject.

This filter is a parent node of a hierarchy.

The data in the source is having rows with leaf nodes only for this infoObject.

See below diagram to get clarity:

hier.png

source data.png

From the above diagrams, observe that the data in the source cube is having only leaf nodes for the Region ZREG1. Continue reading →

New book – SAP Webi 4.0 Step by Step

Hi dear readers,

The ebook, SAP Webi 4.0 step by step is now available on Amazon at just $2.99.

The book is invaluable for hands on experience and quick update of your skills to SAP Webi.

SAP Webi is basically a reporting tool but has many more advantages over SAP BW Query Designer. It is much more  user friendly and flexible for building reports and dashboards.

Here is the link for the Kindle ebook in US Amazon market, you can also get it in your local Amazon

https://www.amazon.com/dp/B07682NSQN

 

New Ebook – SAP BODS Step by Step

Hi dear readers,

The ebook, SAP BODS step by step is now available on Amazon at just $2.99.

The book is invaluable for hands on experience and quick update of your skills to SAP Data Services.

SAP BODS is basically and ETL tool but has many more advantages over SAP BW. However, it still cannot replace SAP Standard BI extractors.

You will find BODS to be like a tool to extract and transfer records between two databases. Learning simple SQL for RDBMS will make it easy for you to understand BODS.

Here is the link for the Kindle ebook in US Amazon market, you can also get it in your local Amazon

https://www.amazon.com/dp/B071PBZJW4

Below is the link to buy the paperback version-

https://www.amazon.com/dp/1521385564

Case Transformation in SAP BODS

Case transform is a part of Platform set of Transform in data services. It deals with the branching logic i.e it separates source data into multiple output data sets based on a condition.

For example, the source data from different countries is diverted to separate country tables based on certain conditions.

The condition based on which the data is branched has two parts: Label and Expression.

The label is the path name to the target table and the expression has the SQL logic that separates the data.

For example, define a label Region_INDIA where expression is Employee.regionId = 1.

Here, Employee is the source table having regionId as a column.

To understand it more clearly, login to the data services. Create some sample data in Microsoft SQL Server like below: Continue reading →

How to transport an Export Datasource

An export datasource is created when you want to use an info provider to load data to another targets which may reside in the same system or different system. An export datasource is created by default for a DSO. But its not the case for cubes. Below I will discuss one such scenario where I created and transported an export datasource.

Scenario-

You want to load data from cube in one system to a cube in another system. Let’s say that the source system is a SAP BW system with id SRD and target system is another BW system with system id TGD

SRD – SRT – SRP (Source system landscape)
TGD – TGT – TGP (Target system landscape)

Now there is a cube in SRD system which has data. This data needs to be get loaded to a cube in TGD system based on certain filters and conditions. And this has to finally move to the production systems (SRP and TGP)

The way to do this is first to request for a TR (transport request) in both SRD and TGD systems.

Procedure-

Continue reading →

Transport failed with RC=12 error

Issue-

Sometimes while transporting changes from Dev to Quality system or from Quality to Production system, the TR fails with RC 12 error. This is a critical error which does not depend on the contents of the TR. There may be many scenarios leading to this error, I have mentioned one of them in my earlier post, this post is about one more such cases where I encountered this error-

Root Cause Analysis-

In this case, there was table space issue in the quality system and hence while transporting the changes, incomplete objects were transported resulting in a dump in the quality system and TR failing with RC=12 error-

Below is the screenshot of the error that we got in the TR-

rc12 error 3

Below is the screen shot of the dump which we were getting in the quality system-

rc12 error1

rc12 error2

Resolution-

We asked basis team to check the tablespace in the quality system. They extended the tablespace of the table mentioned in the error log/ short dump. The TR was then moved successfully.

Hope this helps..

Transformation not getting deleted in RSA1

Recently I came across a scenario where I was not able to delete an inactive transformation while doing development.

I created dso and loaded the data. Then I observed that the dso should have different infoobjects. In order to correct the dso, I deleted the data first. Then I added the new info objects and activated the dso.

The dso was activated successfully. However, the transformation and dtp became inactive. I deleted the dtp successfully.

I went to edit the transformation and it showed warnings like – rule is invalid. I pressed continue and after couple of warnings, I got a dump as shown below- Continue reading →

Migration of Datasources

In SAP source system (ECC), the datasource is the relevant object that makes source data available in a flat structure for data transfer into BW.

In the source system, the datasource can have the SAP Delivery Version or Active Version.

There are two types of datasources in BI:

R/3 TR RSDS or 3.x DS R/3 TR ISFS

Since the datasource cannot exist in both of these versions simultaneously, you have to choose the form while replicating them to BW.

Replicated 3.x datasources can be used as Emulated datasources in BW for migration purposes.

SAP recommends migration of 3.x datasources if you are using RDA DTPs or Direct Data Access DTPs as emulated datasources does not support these two types of DTPs. Continue reading →

Repartitioning (Infocube)

Partition of InfoCubes is done to divide the huge data in the fact table into several smaller, independent units. This improves the query performance.

InfoCube can only be partitioned using one of the two below criteria-

  • Based on month (0CALMONTH info object must be present in the cube)
  • Based on Fiscal period (0FISCPER info object must be present in the cube)

Partitioning and re-partitioning can be done even though the cube contains data. This concept was first introduced in SAP Netweaver BW 7.0 as a replacement of partitioning in 3.x

Please note that you do not partition the cube when you create it. Usually it is done at a later point in time when the cube has more than a million records. Continue reading →