BI reports - Multiple data sets inside data model driven by one major query/data set

Srdjan Ostojic

Description

Main Query/Data set that can feed other data sets inside same data model. Other Child data sets can see parent data sets as custom/internal files.

Use Case and Business Need

Multiple data sets inside data model driven by one major query / data set.

Oracle currently allow you to make multiple data sets inside one data model. However, in one data set you can have logic that already exist in other data sets because you are doing “similar” but different results… Repeating similar logic, opening and querying same file (over and over again) to get different results in different data sets are time and processor consuming.

Why not allowing creating Main Query (data set should have flag that defines it is Main Query with all codes) that will have WITH statement and all logic that brings all necessary info for other data sets. It will be faster, then opening and selecting same stuff over and over again.  Other data sets should be able to see Main Query results from WITH created subqueries and they should be able to do

 

Select something

from MainQry.Subquery1

inner join MainQry.Subquery2   on …

left join MainQuery.Subquery3 on …

where …

 

I am talking about speed here. Functionality, to do once some files. One Main Query will have a lot of “sliced and diced“ parts inside (temporary table results) ready after  first execution. All other data sets can reach then and treat as custom files.  
 

Original Idea Number: 9b65f5fe9c