How to implement flexible BPC Audit Reporting on a Standard Environment

[Former Member]

Audit Reporting takes long and deliver only approximately 1600 records per selection/request. On a bigger Environment with multiple Millions of Audit records and more than 1000 Users it is nearly not possible to find an input error over the WEB Admin page. The below solution are built on poor BW objects.

Prerequisite:

To implement the solution you need first to know your Audit and History table and to have an Audit table Maintenance strategy on place.

Audit Table are set to one day on the WEB Admin page.

The Data Package to archive the Audit data must run daily, so we do only have an record amount of a day. This is necessary to limit the data amount on the Audit tables as we will report later direct from those tables.

Solution:

On our case the tables below:

We will now create now InfoSet Queries for each Audit Table with the Header table UJU_AUDDATAHDR and Archive tables with UJU_AUDDATAHDR_A.

Now we are ready to create the extract structure for the Data acquisition on SBIW. On our example we have 8 Models and this will end up to 16 extract structures, each one for Audit and History pro model (see model).

Create now an extract Source for each Audit and History table (TA RSO2).

As on the reporting we like to be able to report with selections you need to specific the field for the selections (TA RSA6). This need only be done to the Audit Table extractions not to the History as those will be load as Full load into a reporting Cube after the Audit Package did run to put the records older than a day from Audit to History table.

The next step would be to create the InfoObjects.

As we like to select per User we will load the User name as attribute to the User ID Infoobject. This will be a full Mater data load from the view USER_ADDR table with an own created extract structure.

Now we are ready to create the Virtual InfoProvider for Audit and normal for History tables. You see that we have not only one Environment in our Audit reporting , we have 3. With a good naming convention you will be able to distinguish the virtual (last digit “A” for Audit) InfoProvider from the others (last digit “H” for History).

All those InfoProvider will now be combined on the MultiProvider.

All those InfoProvider will now be combined on the MultiProvider. Our queries will be built on this MultiProvider.

Create now a Process Chain to load “Full” to the History Cube. As example the Process Chan for BS (Balance Sheet) data load. Do not forgot to delete before the PSA and the data on the History InfoProviders (see Process Chain) , other way you will get double records and more. The last step is to fill the index into our BWA blade.

As last think about how many days you need the History data. In our case the Business requests 90 days. So we did create an Job how those delete the History table daily all records older than 90 days (TA SE38 with report UJU_DELETE_AUDIT_DATA).

Do now save this as variant and then use SM36 to define a BackGround job.

Create now an Bex Query on the MultiProvider and use Variable to filter to the Audit data. As we did load Mater Data to the USER ID we can select per User ID.

Report:

As we have an BWA attached the report (see above) runs only 5 second and delivers over 5000 rows.

I hope this will help you to manage and user the Audit records better.

Kind regards Dario