How to display a list of queries that use a given aggregate – an example of QuickViewer usage.

Hi,

I decided to write this blog after I realized that it’s not so easy to check which queries use given aggregate. There was a need to optimize the aggregates which were created on different occassions but to do this I had to know which queries work with them. 

Checking if an aggregate was used by any query is possible in RSA1 in “Maintain aggregates” option against a given cube. We can see here how many times an aggregate was used since its last activation.

But we don’t see which exact query used an aggregate.

To check that two tables should be used:

1. RSDDSTAT_DM,

2. RSDDSTAT_OLAP.

We have to use both tables because none of them contain all the fields that we need i.e. an aggregate number and a query’s technical code.

In RSDDSTAT_DM we have an aggregate number and STEPUID field which we use to join that table with RSDDSTAT_OLAP.

We don’t have in this table the query technical code.

A query’s technical code can be found in RSDDSTAT_OLAP as well as STEPUID .

The simplest possible way (no ABAP, no need to define BW data source) to combine the data mentioned above is to use SAP QuickViewer functionality (TCode:SQVI).

All we need to do now is to define and run query joining the data.

1. Give a query code name

2. Start creating the query by presing “Create a query” button

3. Write a query title

4. Define Data source as “Table join”

5. Add both table to project

6. Join tables with STEPID only.

Press F3 to exit the window after all is done.

7. Define which fields should be displayed in a query and which should be used in filter. Use approprate checkboxes to do it.

What was chosen can be seen here

And here:

8. Set Export as MS Excel

9. Save changes

10. Run query

It’s important to restrict “Internal type of query …” to OLAP.

11. After data is displayed in MS Excel functionality for removing duplicates may be used.

Thank you for reading this blog.

I would be very grateful for any comment on it.

I’d like to give spacial thanks to Tomasz Piwowarski for helping me while creating a QuickViewer query.\

Regards, Leszek