Hi All,

In many BW-BO Dashboard integration Projects, it has been noticed that clients/customers look for the Underlying BW cube last updated dates along with KPI/s in the scorecards. The following document walks you through a step by step document for achieving that requirement.

In BW Side, we first need to create a Bex query whose output stores the cube names and their last updated dates

Use 0TCT_MC11 and create a query as follows:

/wp-content/uploads/2013/10/3_312254.png

Add all the required cubes(from where the KPI’s were derived and shown in the scorecard of dashboard) under the ‘BI object’ Type in Characteristic Restrictions Pane.

/wp-content/uploads/2013/10/4_312255.png

The data for the query can be obtained my modelling the dataflow as follows:

/wp-content/uploads/2013/10/1_313923.png

Now take the Bex query to BO Webi using BICS.

If the BW system name is compounded along with info provider names, create a variable as follows:

/wp-content/uploads/2013/10/10_312266.png

The last update from BW will be coming with date and time in concatenated form. Check with the client on what format they want the Last update date in dashboard and create variable accordingly.

In this scenario, I wanted the format to be converted from 04.10.2013 04:18:50 from BW to 04/10/13 in BO Dashboard.

The following variables can be created for meeting that requirement.

/wp-content/uploads/2013/10/9_312275.png

/wp-content/uploads/2013/10/8_312277.png

Now the Final output in WEBI will be as follows, which can further be taken to Dashboard Excel pane through Live office or Web service.

/wp-content/uploads/2013/10/5_312256.png

Insert the Webi output in Dashboard Excel as follows:

/wp-content/uploads/2013/10/14_312279.png

Against each KPI/Measure line item in the Score card, hard code the cube names(which can be hidden later) as follows:

In this case, Targets were coming from ZSD_C33 and Actuals from ZSD_C13.

Untitled.png

Now in the Last updated column write a VLOOKUP function which looks up the cube name in the excel inserted in previous step to bring the last refresh date. See screen shot below:

/wp-content/uploads/2013/10/13_312284.png

PS: Please be informed that this is just a illustration of how the whole process can be done. There might be many options for meeting the same requirement.

Hope this was helpful.

Regards,

Prabhith

To report this post you need to login first.

22 Comments

You must be Logged on to comment or reply to a post.

  1. Manna Das

    thanks for sharing this technical doc Prabhith, sometimes not only in BO even in BW/BI also requirement is to show the last updated records in cube. This doc will help to achieve the same 🙂 .

    Kind Regards

    Manna Das

    (0) 
    1. Aakash Gujja

      Hi Prabhith,

      Nice document thanks for sharing.

      I have tried it on my system.

      How we need to load the data in this multiprovider?? As it reads the data from 0TCT_VC11..What is the source data for 0TCT_VC11?

      Thanks

      Aakash

      (0) 
  2. James Batchelor

    Great idea/Good work around. 

    Its just unfortunate that something so simple is not part of the Webi/Dashboard in BOBJ when running the query.  Like it is in AAO or even old bex.

    I have to add another connector…bring in yet another set of data…to get a date. 

    jim

    (0) 
  3. Parvaneh Farzanegan

    Thank you for the great doc.

    one question, just because we want to add Last Loading date of info providers to our reports, do we have to grant the end users access to this query?

    I mean, the administration cockpit tool is supposed to be used by the administrators but now we have to allow end users to see this query in BO tools.

    (0) 

Leave a Reply