BW Cube Last Update Date in BO Dashboards
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:
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.
The data for the query can be obtained my modelling the dataflow as follows:
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:
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.
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.
Insert the Webi output in Dashboard Excel as follows:
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.
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:
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.