How to achieve the BW infoprovider last refresh date in BO

[Prabhith]

Hi All,

In my current project, I have a scorecard requirement like below:

This sales performance indicator is a formula which comes in a query built on top of Multiprovider(MP)

And that MP contains 10 cubes (C1, C2, C3 to C10). The customer wants the last successful load date of the cube (say only C3) in the column LAST UPDATED.

For meeting the requirement, we build a query in BWon top of the MP 0TCT_MC11(see the output below),  which shows the complete list of the cubes used in the project and their respective last successful load date and time, but am a bit consumed how we can leverage the query for meeting the particular requirement in BO side.

Also, I am aware that we have all the relevant info in the Bex Analyser, which will look like below screenshot:

Is there any way that I can meet my requirement?Or is it not a achieavable one?

The last execution date and time function in BO will show only the status of the refresh of webi document.

I searched for the similar one in SDN and found the 2 relevant posts:

https://ideaplace.brightidea.com/ct/ct_a_view_idea.bix?idea_id=%7bA30D3675-04B1-49D8-8D50-75FD07089D...    --> What is the current status now?

http://scn.sap.com/thread/1544984    -->  Any new solution in the latest SP's or Patches?

Br,

Prabhith

[Prabhith · Accepted solution]

Hi All,

I have created a step by step document on how this requirement can be achieved.

Please find the link below:

http://scn.sap.com/docs/DOC-48514

Regards

Prabhith

[Prabhith]

Hi,

Thanks a lot for your suggestions.

Finally we have a solution for this.

In the external excel working space, against each KPI, we harcoded the cube names.

Parellely, we had created a webi report on the query built on top of the MP 0TCT_MC11(which basically gives the cube names and their last refresh time) and the webi report was also taken to the extrenal excel working space.

We had done a lookup from the hardcoded cube name to the query output and the last refresh date and time of the corresponding cube was taken back to planned space in the scorecard.

Clossing the loop here.

The query is now answered.

Br

Prabhith

[former_member184594]

This is what I did to meet the requirements.

1. RSICCONT table in BW contains data about refresh dates and times. I created a data source on top of this table using RSO2 transaction.

2. After creating the data source, I created DSO.

3. I created a multi source (federated) UNX on top of DSO.

4. In the data foundation of this universe I used this SQL to get the max dates and times for each cube to create a derived table.

SELECT @catalog('ODS_GNC')."PUBLIC"."IZODS_GNC"."ZICUBE",

max(toTimestamp(cast (@catalog('ODS_GNC')."PUBLIC"."IZODS_GNC"."0CALDAY" as varchar)+ ' '+cast(@catalog('ODS_GNC')."PUBLIC"."IZODS_GNC"."0TIME" as varchar)))

FROM @catalog('ODS_GNC')."PUBLIC"."IZODS_GNC"

GROUP BY @catalog('ODS_GNC')."PUBLIC"."IZODS_GNC"."ZICUBE"

5. I used this table to join DSO like below. (Black table is my derived table I created in the previous step.)

6. I created my objects on top of this data foundation. I created Last Refresh Date object based on the derived table I created in step 4.

7. I used this universe in my WebI reports and dashboards to have the Latest Refresh Date and Time.

[satyndr]

Hello Prabhith,

As per my understanding, you want to show the latest successfull load in

perticular cubes. and your Webi report is based on a Bex Query.

In that case you can include infocube 0TCT_VC11 in the multiprovider

on which your query is based. ( identify the required fields ) .

and restrict them against the cubes you want to display .

Thanks

Satendra