SAP BI Blog about SAP BW/4HANA, Analysis for Office and SAP HANA - by Tobias Meyer
I hope you can help me, to find out if there is any interesting about a book that covers BW Backend topics. I think about a book which covers SAP HANA, AMDP/ABAP, BW Administration and so on.
Thanks for your time.
I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
In my current project I have a file to import, which only delivers me a string with a length of 1000. I also get a description what is in this file. Like the following points:
The file looks like: 0000123456London………………………. So I have a file and a description how to decode this file. Now I need to find a way to separate this information into
InfoObjects. I build a Z-Table which contains the decode information.
The content look like the following example.
After I filled all my necessary information into the table, I started to write my code in an expert routine. First I need some variables and internal tables.
1 2 3 4 5 6 7 |
DATA: lt_table0 TYPE TABLE OF z_dec, ls_table0 LIKE LINE OF lt_table0, lt_table1 TYPE TABLE OF z_dec, ls_table1 LIKE LINE OF lt_table1, lv_start TYPE i. FIELD-SYMBOLS: <fv_source> TYPE any. |
After the variable declaration, we have to select our data and sort the internal tables.
1 2 3 4 5 |
SELECT * FROM z_dec INTO TABLE lt_table0 WHERE recordtype = 0. SELECT * FROM z_dec INTO TABLE lt_table1 WHERE recordtype = 1. SORT lt_table0 BY startfrom ASCENDING. SORT lt_table1 BY startfrom ASCENDING. |
Now we loop over the SOURCE_PACKAGE and loop over our internal table to assign all values.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
LOOP AT SOURCE_PACKAGE ASSIGNING <source_fields>. IF <source_fields>-field1(1) = 0. LOOP AT lt_table0 INTO ls_table0. "First I need to find the start of my information lv_start = ls_table0-startfrom - 1. "Now I assign the infoobject to my result structure ASSIGN COMPONENT ls_table0-infoobject OF STRUCTURE RESULT_FIELDS TO <fv_source>. "Here I write the value of string into the corresponding infoobject <fv_source> = <source_fields>-field1+lv_start(ls_table0-length). ENDLOOP. "Here we append the RESULT_FIELDS to the RESULT_PACKAGE. APPEND RESULT_FIELDS TO RESULT_PACKAGE. ELSEIF <source_fields>-field1(1) = 1. LOOP AT lt_table1 INTO ls_table1. "First I need to find the start of my information lv_start = ls_table1-startfrom - 1. "Now I assign the infoobject to my result structure ASSIGN COMPONENT ls_table1-infoobject OF STRUCTURE RESULT_FIELDS TO <fv_source>. "Here I write the value of string into the corresponding infoobject <fv_source> = <source_fields>-field1+lv_start(ls_table1-length). ENDLOOP. "Here we append the RESULT_FIELDS to the RESULT_PACKAGE. APPEND RESULT_FIELDS TO RESULT_PACKAGE. ENDIF. ENDLOOP. |
So when we now see the import file, which looks like the following.
The result looks like the a normal import.
You can download the whole source code from github. If
you have questions, feel free to ask.
These posts might also be interesting:
I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
Lately, all my posts started with „In my current project“, so now something else, even if it was developed in the current project. The problem we are facing with is that we get a data extraction
which deliver us only the new data records, not the reverse data record.
So we need to build your „own reversal entry“ to set the values of a record to 0. For this we write our ADSO in itself with a formula which delivers the negative value.
I build a transformation which the formula above and the magic will be done in the DTP filter. First, let me briefly explain our data model. We use the following layers:
But back to the magic. The routine is writen in the DTP Transformation Layer in Transformation Layer.
First I determine all requests from the Acquistion Layer DSO.
1 2 |
"Determine all requests from source DSO SELECT DISTINCT ( reqtsn ) FROM /bic/avf0aperia1 INTO TABLE lt_requests. |
After I have all requests in my internal table, I sort it descending to get the right order.
1 2 |
"Sort Table descending SORT lt_requests DESCENDING BY request. |
Now I select the first request. It is the newst.
1 2 3 4 |
LOOP AT lt_requests INTO ls_requests FROM 1 TO 1. "Puffer last request lv_last_request = ls_requests-request. ENDLOOP. |
Now we know the right request and select all data from the advanced DSO (Aquisition Layer).
1 |
SELECT DISTINCT field1 field2 calyear field3 FROM DSO_AL INTO TABLE lt_new_data WHERE reqtsn = lv_last_request. |
After we now have all necessary information in our internal table, we have to fill the DTP filter.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DATA: l_idx LIKE sy-tabix. READ TABLE l_t_range WITH KEY fieldname = 'field2'. l_idx = sy-tabix. LOOP AT lt_new_data ASSIGNING <fs_new_data>. l_t_range-fieldname = 'field2'. l_t_range-iobjnm = 'field2'. l_t_range-sign = 'I'. l_t_range-option = 'EQ'. l_t_range-low = <fs_new_data>-field2. IF l_idx <> 0. MODIFY l_t_range INDEX l_idx. ELSE. APPEND l_t_range. ENDIF. ENDLOOP. |
After I execute the DTP and activate the request in the transformation layer advanced DSO you see there are only 18 entries which are corrected.
When you now look into the active table of the advanced DSO, you see all values which are shown above are now 0.
The changelog also show the new, the before and the after image for the entries. So you can understand what was booked.
And when we now execute all DTPs to write the data from the Acquistion Layer to the Tranformation Layer, we see that there are only 18 entries which are new.
And also the new booked values are the same we have above in our second extract.
As you can see it is very easy to create your own reversal entry if the source system don’t deliver one. I hope someone can need it.
I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
In my current project we have a lots of source systems which delivers our data in a key figure model. In a normal case is this not a problem. But we need the values of the key figures in a
hierarchy. So there are two ways to realize it. First way would be we build a structure with restricted key figures and get our hierarchy. Here is the problem, the customer will need different
queries to realize his needs. If there is a change, we need to adjust different queries (each has a light different hierarchy) and the maintainace is immense.
Another way is to use an account model. Here is your first Problem, we only get a key figure model and it is not so easy to use rule groups or a simply routine, because we get around 100 key
figures. So it isn’t easy as Denis wrote in his post (this post is in German). But I found, really I can’t belive I found an article on the former SCN. This post decribes how to transform
a key figure based model to an account based model with some ABAP coding and an expert routine. So I think that must be the answer to my problem, but it isn’t so well writen that I could use it.
But the start was done. I need a Z-Table and some ABAP. First I created my Z-Table with four fields:
AREA is necessary to use this table in different deparments, SOURCE_KF represents the source key figure for example /BIC/ZGROSSVAL and the
ACCOUNTPOSITION is the technical key of the account model, for example 15.
This was the easy part. Now I need some ABAP and here I had some struggle.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Data: lt_account type HASHED TABLE OF z_account WITH UNIQUE KEY source_kf, ls_account like LINE OF lt_account. FIELD-SYMBOLS: <fv_source> type any. Select * from z_account into table lt_account WHERE area = 'SOMETHING'. LOOP AT SOURCE_PACKAGE ASSIGNING <source_fields>. MOVE-CORRESPONDING <source_fields> to RESULT_FIELDS. Loop at lt_account into ls_account. RESULT_FIELDS-/BIC/VF0CBPOS = ls_account-berichtsposition. ASSIGN COMPONENT ls_account-source_kf of STRUCTURE <source_fields> to <fv_source>. RESULT_FIELDS-/bic/vf0kbehw = <fv_source>. Append RESULT_FIELDS TO RESULT_PACKAGE. Endloop. Endloop. |
The Problem was the ASSIGN COMPONENT part, which I didn’t know how to work, but a colleague helped me here. So that’s it. The Z-Table can be modified to fulfill your needs, but the basic code
should help you to realize a solution.
I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
In my current project I created with a collegue a really cool function to analyze a string with 1333 characters. We are using a BW 7.4 SP 17 on HANA. First we have to build an Advanced DataStoreObject with a Field which has a length of 1333. For further understanding, we call the
field Field_1333. As data type I used SSTRING.
The Advanced DataStoreObject will be included into a CompositeProvider. The Problem is the CompositeProvider cannot work with a field which has the length of 1333.
So my colleague decided to build an Open ODS View on top of the Advanced DSO. Therefor you have to activate the checkbox External SAP HANA View. My Open ODS View contains only
the field „Field_1333“, Number of Records and one InfoObject I need for my query. As you can see, the Open ODS View shows a length of 250 for the field „Field_1333“. This is only a
display problem.
When you look into the data, the Information is complete.
Now I use the Open ODS View in my CompositeProvider and here we go, the field is now available for my query.
Now we build a query on the CompositeProvider and add the necessary InfoObject into the rows and a restricted key figure into the columns with a variable for the field „Field_1333“. This variable
is used to fill the customer exit variable of the field „Field_1333“.
Now you have the opportunity to fill a lot of values separated by comma. The customer exit sorts all values by ascending and fill the variable with ‚*‘ & value & ‚*‘
ls_e_range-low = ls_e_range-low && ‚*‘ && ls_code-ccode && ‚*‘.
Now we can combine different combinations and get a result. This result is used as a PreQuery in my main development. So I am able to build a complex analytics application and give the user the
opportunity to analyze his data deeply.
I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
I know that I don’t publish a lot of new posts the last few weeks. The reason is I am writing on my diploma thesis. The title is „S/4HANA versus BW/4HANA – Zukunft der Datenanalyse“. My deadline
is in the middle of September so I have to write a lot these days. At the moment, I have access to a BW/4HANA instance in the cloud and I want to share how you could export your development
before you terminate the instance. First you have to log on with the SAP* user in the client 000. Go to the transaction stms and select the System Overview.
After you select the System Overview, choose SAP System > Create > Virtual System.
Return to the stms transaction and select Transport Routes.
Open it in change mode and select Open Configuration > Standard Configuration > Development and Production System. Assign the current system as Develeopment System and the virtual system as
Production System.
The route will be created. Now saveand activate the configuration. Now you can release your transport. After you have released your transport, have a look into the DIR_TRANS
directory in the transaction al11. There you find the folder cofiles and data. In this folders you find the necessary files. You can download
these files with the function module ARCHIVFILE_SERVER_TO_CLIENT. If you want to import the files again, use the function module ARCHIVFILE_CLIENT_TO_SERVER.
I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
I described in a earlier post how to use BW/4HANA on Amazon AWS. But if you just need a developing system for some time
and don’t want to use BW/4HANA, you can use the BW 7.5 SP2 developer edition on a virtual machine.
There are a good post on the SCN. First you have to download Oracle VirtualBox. After that you have
to download openSUSE. If you are a Linux Newbie you can read the following post how to install openSUSE on VirtualBox. The BW 7.5 SP2 developer edition can be found
here.
After you have downloaded everything and your openSUSE is running you should read this post to get the installation of the BW done. I needed a BW 7.5 for the new
edition of my book, so it was a nice variety for me.
These posts might also be interesting:
I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
In my current project, we have to deal with 30.000.000 records for the initialization run and about 10.000.000 records each day. The big problem is we have no SAP HANA system for BW. So, we have
to build a perfect data model to load the data and push it through the BW to deliver it to another DWH system. First I want to describe how our first steps worked and then which parameter we
changed to make it more efficient. I will also describe where we had our problems and how we have solved them. So let’s go.
We receive the data from a Bank Analyzer (BA) system and have to put it into a DataStore-Object (DSO) to get a delta logic. So, we designed a Semantic Partitioning Object (SPO) with the criteria
0FISCPER. We don’t want to build it on 0CALMONTH because this can nobody administrate. In the routine, we have to do some logic for master data because we get a master data record not every day,
so we decided to build a DSO which it build like a time-dependent InfoObject. The reason why we would use a time-dependent InfoObject is, because we also have 10.000.000 records and this is hard
to execute the attribute changes for so many records.
So when we get a new record we set the valid-to date to 31.12.9999 and when we receive a new entry for the same record, we adjust the valid-to date to new start date minus 1. In case we have to
make a build the records for the transaction data again, we have a clean history.
Our first load from the data source into the data acquisition layer for the 30.000.000 records last 3 hours. Plus activation of almost 2 hours. So that we have
the data only in the first layer of our data model took us 5 hours + 1 hour to extract it from the source system.
After we had the data in the data acquisition layer we want to load it into InfoCubes for reporting and add also some logic. We build a SPO again, but this time we separated it into 0FISCPER and
accounting system (AS). So we have now instead of two cubes (2016 and 2017) now four cubes (2016 AS1, 2016 AS2, 2017 AS1, 2017 AS2). So next year we get two new cubes and so on.
Now we have only around 15.000.000 records for each accounting system. Just a note, the test data is only for 2016 so we have no data for 2017. The initial load of the data from the changelog of
the DSO took us more than 16 hours.
Until a user can execute his report with the new data, he had to wait about 22 hours. So we have to analyze what our problems were:
So our big issue was that it took us round 7 minutes for one package of 50.000 entries into the InfoCube. So, we analyzed the data model which exists from a former project with the report
SAP_INFOCUBE_DESIGNS. And it showed us that we have three terrible dimensions. After we fixed the design of the InfoCube we load the data again and it took us now
3 hours. This is an improvement of to 19% of the original time. So it is very important how your data model is designed. Especially when you have to deal with
mass data.
Besides the data model we also add number range buffering for DIM IDs and SIDs. For more information have a consider SAP Note 857998. This also improve our activation time, but
more about that later. So now we have to deal with the 3 hours of data loading from the data source to our DSO.
We looked into our start- and end-routines and adjust our ABAP code so now we were down from 3 hours to 42 minutes. This is now only 23% of the
original time. These two big improvements saved us about 15 hours. So we are now down from 22 to 7 hours for the processing. Besides our data
model and ABAP correction, we also increased our parallel processing. For this open the RSA1 and click on the administration tab and select under Current Settings >> DataStore
Objects. You can also execute the report RSODSO_MAINTAIN_SETTINGS.
Select now your DSO and you can edit the parameter on the right side. For example, we choose the Package Size Activation with 50.000 and the Package Size SID Creation with 50.000. Besides these
two sizes we also adjust the parallel processing of activation and SID generation to 10 processes. This maybe depends how many processes your system have.
And we also increased the parallel processing of the DTP to 7 from 3 processes.
The next step was to improve the activation time. Our first try was 1 hour 54 Minutes. After we add the buffering of number ranges, we went down to
1 hour 25 Minutes. It is a benefit of 29 Minutes or round 26%.
So we have now a data loading time down from 23 hours to 6 hours 30 minutes. So we are now only need 28 % from the time of our
first try. This is really impressive, because you have to keep in mind, that we have no HANA system.
These posts might also be interesting:
I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
I want to document a BW data model and searched for a Microsoft Visio shape, but I found nothing. So I build my own Visio shapes. At the
moment there are the following types available:
I used them in my last blogpost about Copy Queries to a new MultiProvider.
The elements have also shape-data which can be filled. For example DataSource has the following shape data:
A DataTransferProcess has for example:
This is a first draw and I will add some new items and shape data in the future. So if someone need a Microsoft Visio Shape for SAP NetWeaver
BW feel free to contact me.
These posts might also be interesting:
I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
In my current project, we want to separate the current MultiProvider with VirtualProvider underneath into one MultiProvider with VirtualProvider and one MultiProvider without VirtualProvider.
This step is necessary, because we receive a lot of data and don’t want to push all these data through the VirtualProvider. The VirtualProvider only add one field which we haven’t got in our
InfoCubes and it isn’t necessary in all queries just a few.
So we decided to split the existing MultiProvider into two. This means, we have to transfer some of the existing queries from the current MultiProvider to the new one. Because we don’t want to
build them, especially when the MultiProviders have the same structure.
You can either use the transaction SE37 with the function module RSZ_I_COPY_QRY_TO_CUBE_SINGLE or use the transaction RSZC. I will describe both
ways. Open the transaction SE37 and execute the function module RSZ_I_COPY_QRY_TO_CUBE_SINGLE. You should see the following screen.
Into the field I_SOURCE_COMPUID you have to fill the COMPUID which you can find out with the table V_REP_JOIN. The field
I_SOURCE_INFOCUBE is your source provider and the I_TARGET_INFOCUBE is your destination provider. You have also to remove the X in
I_CHECK_COMPLIANCE. Now you can execute the function module and your query is copied to the new MultiProvider.
The other way is to use the transaction RSZC. If you use it, you should see the following screen.
This screen explains itself. Into Source InfoProvider you have to put your source MultiProvider and into Target InfoProvider the destination. You can choose what
you want to copy. For example:
The transaction RSZC makes it very easy to copy a lot of queries at once. I hope this help someone, so you haven’t to build all your queries again on a new MultiProvider.
I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.