Last Refresh Query

To understand when the data in the Pricing Dashboard was last updated, a dedicated BW query with below information is also feeding the Pricing Dashboard. 

• Source query name
• Source query description
• Main Infoprovider
• Part provider
• Date and Time of the last refresh in BW 

A BW query can be built on several providers, gathered in a Multiprovider, a Composite Provider or any other virtual object.

Therefore the choice is made for a single query to get the latest request loaded in time, whatever the part provider is.

Query technical information

Query technical name : QV_BW_QRY_CPCOPC00_0001

Composite Provider : CPCOPC00

HANA view : CV_FMCO_TECH_TIMESTAMP

Query design

There is only one Infoobject used  in the query (0BWAPPLNM) to allow the use of a selection variable on it (V_APPLNM).

Other objects are simple fields to display the data.

Query selections

The input variable V_APPLNM must be populated with the list of Query technical names to analyze.

Query result

The list of queries in input is displayed, with the relevant information in lines.

The refresh date and time is a Timestamp format yyyymmdd hhmmss.

Composite Provider

The Composite Provider CPCOPC00 is the source of the Refresh Query.

It is built on the HANA Calculation View CV_FMCO_TECH_TIMESTAMP.

Infoobject 0BWAPPLNM is assigned to the Input Parameter IP_QUERIES in order to propagate the query selection into the lower layer of the CV.

HANA Calculation View

The Calculation View CV_FMCO_TECH_TIMESTAMP is extracting the refresh information for each query to analyze.

First Layer

Projection_1 and Projection_5 : Select the list of Main providers in table RSRREPDIR for query IDs from Input Parameter IP_QUERIES.

Left Join_1 with table RSZELTTXT to get description for each query.

Second Layer

Projection_3 : Table RSDCUBEMULTI to get dependent objects for a Multiprovider.

Left Join_3 with the list of Main providers from Join_1 to get dependent objects.

Projection_4 : Table RSOOBJXREF to get dependent objects for a Composite Provider or ADSO.

Left Join_2 with the list of Main providers from Join_1 to get dependent objects.

Projection_6 : Left Join_5 with the list of dependent objects from Join_2 to get next dependent objects.

Projection_8 : Master Data C_GLBFILT to force fix dependent objects for a Composite Provider built on CV (No selectable information in XML format).

Left Join_6 with the list of Main providers from Join_1 to get dependent objects.

Third Layer

Union_1 : Merge information from Main providers and dependent objects.

Projection_2 : Table RSDINFOPROVDATA to get last request loaded date and time of a Provider.

Left Join_4 with the union_1 of Main providers and dependent objects.

Fourth Layer

Projection_7 : To exclude redundant lines without part provider.

Rank_1 : To select the last updated Part Provider Type of a Main Provider (sorted descending on Timestamp).

Rank_2 : To select the first Part Provider Type of a Main Provider (1RST, ADSO, CUBE).

Master Data Global Filter C_GLBFILT

In order to force fix dependent objects for a Composite Provider built on CV (No selectable information in XML format), the Master Data C_GLBFILT is used with below inputs.

Here the Composite Provider CPCOPA03 will be completed with part providers DBCOPA34, DBCOPA41, DBCOPA36...etc

This list will be merged with all others part providers found in the CV for the Main Provider, and the last refreshed part provider will be displayed in the query result.