In my existing universe i wan to add one derived table

[Former Member]

Hi ,

In my existing universe i wan to add one derived table,Based upon the derived table i need to change some of my objects.In this scenario  I have 2 fact tables to which i  need to join my derived table,and those 2 Fact tables are connected with one dimension table.i made connections in between 2 fact tables and derived table.i involved that join in to the fact table context.But when i create report based up on the same my query is taking to much time and because of that my database server also got hanged.it is fetching almost 40 GB data...........      please do the needful. 

[former_member182521 · Accepted solution]

Hi Maheedhar,

Instead of creating derived table at Universe level can we create a materialized view at database level and use the same in your universe. Also try creating Agrregate tables in your database level for each level of granularity of objects used in the report. This enables your query to utilize these aggreagate tables instead of the Large fact tables.

This could help you for Aggregate awareness implementation.

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

Regards,

Mani

[Former Member · Accepted solution]

Hi,

My universe is not MSU,and database is Oracle.

i can create view but user told me to create Derived table only.

suggest me with promt answer.

Thanks & Regards,

Maheedhar Nadella.

[dennis_scoville4 · Accepted solution]

Since the user is "tying your hands" by not allowing views (or as I previously recommended a new table), then the indexing on the two Fact tables is the logical step. Make sure that there are indexes on the column(s) being joined on the Fact tables. This will speed up query performance. Another "trick" to use to ensuring that the Fact tables are being analyzed on a regular basis (we analyze Oracle Fact tables at least once per week -- some more frequent than that), so that you get contiguous indexes, instead of fragmented indexes.

[Former Member · Accepted solution]

Hi,

We resolved this issue by creating view.

Thanks Dennis for u r answer.

Thanks & Regards,

Maheedhar Nadella.

[dennis_scoville4]

There are a couple of things you can do:

1. Ensure that you have indexes on the columns being joined in the two Fact tables.
2. Model a new table in your data warehouse/data mart.

Since a derived table is essentially a database view, and database views are inherently inefficient for large datasets, Option 2 would be your best choice given that your results set is 40 GB.

[Henry_Banks]

Hi,

So it sounds to me like you're doing a multi source universe - can you confirm ?

For completeness, what's the datasource behind these tables (vendor)? 

if you have 2 large fact tables, with a poorly performing join.. well, that's your datamodelling issue for which SAP will have no standard answer.

If it is MSU, you can use the Data Federator Admin Tool to monitor, profile and tune the SQL exec plan.  but you might have to reconsider your design, rather than just optimise.

regards,

H