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.

1. RSA maintain aggregates.png

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.

2. RSDDSTAT_DM.png

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 .

3. RSDDSTAT_OLAP.png

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

4.SQVI create query.png

3. Write a query title

4. Define Data source as “Table join”

5.SQVI table join.png

 

5. Add both table to project

6.SQVI add table.png

6. Join tables with STEPID only.

7.SQVI connecting tables.png

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.

7.SQVI list of fields.png

What was chosen can be seen here

8.SQVI list of fields ddd.png

And here:

9.SQVI list of fields ddd.png

8. Set Export as MS Excel

10.SQVI to excel.png

9. Save changes

11.SQVI to excel save.png

10. Run query

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

11.SQVI ograniczenie.png

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

11.SQVI wyswietlenie.png

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

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply