BW Cube Last Update Date in BO Dashboards
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:
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.
Regards,
Prabhith
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
Hi Manna,
Thanks for your comments.
BR,
Prabhith
Agree.
Excellent Work.....
THanks for sharing.....
Thanks for sharing best docs for us. Thanks for your efforts and time.
Thanks
Nice doc. Got to learn new thing.Thanks for sharring
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
Hi
Thanks a lot for your comments.
@Aakash; I have now added the modelling flow screenshot in the document.
BR
Prabhith
Hi Prabhith,
Thanks Prabhith its working. Data not maintained in the source tables 🙁 ...
Thanks
Aakash G
There is a workaround if you have not activated the BI content for the BI Administration Cockpit.
for your case you could do somthing like this:
Blog
(http://scn.sap.com/community/bi-platform/businessobjects-bi-for-sap/blog/2013/11/05/loading-date-of-bw-infoprovider-in-sap-bo)
(Inspired by this post)
Works fine 🙂
HTH
BTW: Nice job on the tutorial -Prabhith 🙂
Hi Prabhith,
Other great doc! Thanks for sharing!
Regards,
Carlos
Hi Prabhith,
Again, a very good document. Thanks for your time.
Regards
PK..
Great post. Very helpful.
HI ALL,
Thanks a lot for your comments.
BR
Prabhith
Hi Prabhith,
Once again, a very well presented document from you.
keep going...
Regards
Lethika..
Really good post and Great job... 🙂
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
Nice document .Learnt a new thing today 😀
Very good documentation, thanks!
Very Helpful and good one.Thanks for sharing
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.
Parvaneh Farzanegan
Yeah I guess you have to but as a workaround you could do it like I described here:
Loading Date of BW InfoProvider in SAP BO
Hth
Andreas
Thanks a lot Andreas