Monitoring BW loading status through Web Intelligence via email
How many times did a user come to you and tell you something is wrong with the report or the dashboard, then you go to check on the source infoproviders and find out that they have failed for some reason?
In a fast paced environment that is highly dependent on BW, you can not afford being informed by users that something went wrong, you need to be in full control of your environment, know immediately when something fails, so that you can minimise the amount of work needed to get everything back to normal.
Monitoring process chains can be easy in case they were few or dependant, you can set up the chain to send you an email in case of its failure, but what if you were doing some modifications and forgot to reschedule it then you notice when its too late. What if you had an environment with too many 3.x data flows where the scheduling occurs within the Infopackage itself, scattered all over the info areas in your system?
In my opinion the best way to monitor the loading jobs in your system is through tracking the execution of the infopackages and DTPs
In this post I will be walking you through developing a Web intelligence report, informing you about the last loading time and the last status of every infopackage and DTP in your system.
The result will be a PDF sent to you daily via email, as the screenshot below:
Step by step guide:
|RSREQDONE||Transactional||Main table, contains all the infopackages and DTP requests in the system with their execution time and status.|
|RSLDTDONE||Texts||Contains Infopackages texts.|
|RSBKDTPTH||Texts||Contains DTP texts.|
Create generic data sources based on the 3 mentioned tables:
- Go to tcode RSO2, you will most probably need an access key, ask your basis team to provide one.
- You will just need to enter the table name, the structure will be fetched automatically once you click save.
Once you click save you will be taken to a screen where you can identify the selection fields, pick fields DATUM and LOGDPID, these are the date and infopackage technical ID fields.
Note that you can pick anything else that will be helpful in your point of view.
Repeat the mentioned steps for the remaining 2 text tables.
Creation of target infoproviders:
I created infoobjects and key figures for the fields I thought would be useful for now or in the future.
|Technical Name||Description||Type||With Texts|
|ZDTP||Data Transfer Process||CHAR(30)||Y|
|ZLNSTRANS||Number of lines transfered||Number|
I chose to make a virtual info cube based on DTP, so that it doesn’t depend on any loading process, each time the query gets executed it will go fetch the data from the data source.
The structure is as follows:
|ZINFOPACK||ZINFOPACK_TEXT (Table: RSLDTDONE)||
LOGDPID -> ZINFOPACK
LANGU -> 0LANGU
TEXTIP -> 0TXTLG
|ZDTP||ZDTP_TEXT (Table: RSBKDTPTH)||
LOGDPID -> ZDTP
LANGU -> 0LANGU
TEXTIP -> 0TXTLG
Transactional, datasource ZINFOPACK_STATUS (Table: RSREQDONE):
|ZINFOPACK||LOGDPID -> ZINFOPACK||
IF SOURCE_FIELDS-LOGDPID NP ‘*DTP*’.
RESULT = SOURCE_FIELDS-LOGDPID.
RESULT = ”.
|LOGPID contains transactions for both Infopackages and DTPs I wanted to separate them.|
|ZDTP||LOGDPID -> ZDTP||
IF SOURCE_FIELDS-LOGDPID CP ‘*DTP*’.
RESULT = SOURCE_FIELDS-LOGDPID.
RESULT = ”.
|DTPs are always prefixed with DTP_, CP stands for contains pattern, equivalent to Like in sql, NP stands for doesn’t contain pattern.|
|0CALDAY||TIMSTAMPBEGIN -> 0CALDAY|
|ZTIMSTRT||TIMSTAMPBEGIN -> ZTIMSTRT||
DATA: TIM TYPE C
|Time stamp truncation doesn’t happen automatically.|
|ZTIMEND||TIMSTAMPBEGIN -> ZTIMEND||
DATA: TIM TYPE C LENGTH 15.
TIM = SOURCE_FIELDS-TIMESTAMPEND.
RESULT = TIM+8(6).
|ZTGTSTATE||TSTATUS -> ZTGTSTATE|
|ZLNSTRANS||RECORDS -> ZLNSTRANS|
|ZSRC||ODSNAME -> ZSRC||
DATA: LEN TYPE N LENGTH 2.
LEN = STRLEN( SOURCE_FIELDS-ODSNAME ).
LEN = LEN – 3.
RESULT = SOURCE_FIELDS-ODSNAME(LEN).
To remove _MA from the end of the datasource’s name.
Provides the datasource for the infopackages only.
|ZIPAKSTAT||QMSTATUS -> ZIPAKSTAT||Can be skipped, use TSTATUS for both IOBJs.|
|ZUSERNAME||QMUNAME||Providers the user name for the DTP execution only.|
Let’s start off with the query restrictions, as shown below:
- E4385 is my username, as I don’t want to see anything executed by me.
- Target status, let me explain what each code means:
|@08\||Successful Processing – Inactive request|
|@09\||Waiting – Inactive request|
|@0A\||Error – Inactive request|
We don’t need to see the inactive requests, hence I excluded @08\, @09\ and @0A\.
- Finally, this is the most important filter as the provider is virtual, I chose to retrieve all the days starting from today until 2 weeks back, I chose 2 weeks in case I was on a vacation or in case I didn’t notice something, it’s good to have yourself buffered.
This is the query structure:
Finally, the Web Intelligence Report:
The main aim of all of this was to create something very simple that I could go through every morning in a couple of seconds and spot if something was wrong.
Trying to simplify the output as much as I could, I chose those fields, technical ID, description, last loading status and the last loading date for this package, user name field for the DTPs, to make sure they were not some test activities done by BW developers (This can be avoided by filtering on BWALEREMOTE and UNASSIGNED in the Bex Query, filtration on the UNASSIGNED will be required as all the infopackages come with UNASSIGNED user).
The report has 2 sheets, one for the infopackages and another for the data transfer processes, as shown below:
There are 2 simple tricks:
First, converting the status codes into images, which was done through conditional formatting rules, we need to create 3 of them, one for successful status, one for warning and last one for error, a prerequisite for this is taking a screenshot of the 3 images from the the load monitor within BW, you can use any image of course, this is how the format rule will look like.
Common step in the 3 rules, click on format, align text right and make its colour white.
Make sure that all 3 rules are applied, go to the status cell, then navigate to Analysis->Conditional
The second trick is showing the max date for each infopackage or DTP, regardless of its status, let me clarify this point.
Suppose we have an infopackage that got stuck in the wait status, or that failed and then you ran it manually and it succeeded on the same day, you will have 3 entries on the max date for this infopackage, each one of them is a unique row as it represents the max date for each status, if this is readable to you, you can leave it as it is, however it will be easier skim and scan through all the records quickly if you eliminate them, these are the steps to filter them out.
- Sort the table by the infopackage ID, ascending or descending however you like.
- Make a variable measure and put the formula below:=If [Infopackage – Key] <> Previous([Infopackage – Key]) Then Max([Calendar day]) ForEach([Infopackage – Key];[Infopackage – Long Text];[Target Status]) Else 0This will flag the duplicate record with a 0 instead of the max date.
- Filter out the 0 values
Note that you will need to create another measure for the DTP sheet and repeat the previous steps.
This works only with measures, I tried doing the same with the dimension flagging those records with an X, filtration did not work.
Save the report on your BI platform and schedule it to be sent to you daily whenever you want in PDF format.
Done!! You are all set.
- Pay attention to the date, just because everything is green doesn’t mean that nothing went wrong, if you notice a delay go check that object out.
- An alternative way to filtering out the duplicates is sorting descending by Status, you will have red ones, followed by yellow then green, this will be the safest way to spot out the failures.
This report proven to be very useful to me, so I thought I would share it with you, this community has guided me through a lot and the best way to thank you is by contributing back, I hope I have made everything clear enough and I hope it serves you as it did to me.