4 Replies Latest reply: Apr 17, 2012 9:58 AM by Venu6 RSS

    Dynamic SQL generation when using Custom Folders

    Venu6
      Hi All,

      We have some custom folders ( we haven't created the views) where we have joined the fact table with multiple code dimensions so that users dont need to go across multiple folders. Currently when ever a user selects a field from the folder, It will automatically joins all the dims as per the folder defnition. Is there a way, when a user select only fact table fields from the folder then the sql will only access fact table (like making dynamic sql) leaving out the joins.

      I am not positive about able to doing it. But still posting the question to check if there is a workaround found by any one.

      Here is the hypothetical scenario:

      SQL for My Custom Folder:

      select
      emp.emp_id,
      emp.emp_name,
      emp. job_id,
      jobs. job_desc ,
      emp.location_id,
      location.location_desc,
      emp.salary
      from emp, jobs, location
      where emp.job_id=jobs.job_id
      and emp.location_id=location.location_id;

      In above example, when a user select only emp_id and emp_name, can discoverer only access emp table, leaving out the joins to jobs and location tables ?

      Appreciate your responses.
        • 1. Re: Dynamic SQL generation when using Custom Folders
          User23049
          Depending upon your particular version of Discoverer, you may be able to use SIMPLE or COMPLEX instead of CUSTOM folders in order to accomplish the " *item trimming* " / " *join trimming* " which you are seeking.

          See the following :

          OTN Forum discussion
          How to improve query performance by optimizing the SQL that Discoverer generates
          [http://docs.oracle.com/cd/E21764_01/bi.1111/b40107/performance.htm#i1018278]

          Blog article
          Performance improvements and SQL optimizations [Discoverer]
          [http://oraclebi.blogspot.com/2005/09/performance-improvements-and-sql.html]

          Oracle Documentation
          How to improve query performance by optimizing the SQL that Discoverer generates
          [http://docs.oracle.com/cd/E21764_01/bi.1111/b40107/performance.htm#i1018278]

          ...
          *10.3.4 How to improve query performance by optimizing the SQL that Discoverer generates*

          To improve query performance Discoverer optimizes the SQL that it generates, in the following three areas:

          item trimming
          Discoverer removes references to irrelevant or unused columns and expressions in the query SQL, improving performance. To enable item trimming, set the value of the user preference SQLItemTrim to 1.

          join trimming
          Discoverer detects and eliminates joins (where possible) from queries without affecting the result set, which improves query performance. To enable join trimming, set the value of the user preference SQLJoinTrim to 1.

          flattening
          Discoverer minimizes the use of inline views in the query SQL, which makes it easier for the database to efficiently parse the SQL and select an optimal execution path. To enable flattening, set the value of the user preference SQLFlatten to 1.

          For more information about:

          the user preferences SQLItemTrim, SQLJoinTrim, SQLFlatten, see Section 9.6, "List of Discoverer user preferences"

          the join properties to choose to improve performance, see the Oracle Fusion Middleware Administrator's Guide for Oracle Business Intelligence Discoverer
          ...

          HTH
          • 2. Re: Dynamic SQL generation when using Custom Folders
            Rod West
            Hi,

            There are 3 approaches you can take, which is best for you will depend on your database and Discoverer version and how much change you want to make:

            1. Database optimisation - If you are using 11g database and the query sent to the database does not reference a inner joined table in the first select of the query, for example, the query

            select
            emp.emp_id,
            emp.emp_name,
            emp. job_id,
            jobs. job_desc ,
            emp.location_id,
            emp.salary
            from emp, jobs, location
            where emp.job_id=jobs.job_id
            and emp.location_id=location.location_id;

            does not reference location table, then provided location_id is the primary key to location and emp.location_id has a validated and enabled foreign key constraint then the database query plan will not access the location table.

            2. Discoverer summary folders - You can manually define summary folders in Discoverer admin which contain custom queries with less table joins. So for example, you could put

            select
            emp.emp_id,
            emp.emp_name,
            emp. job_id,
            jobs. job_desc ,
            emp.location_id,
            emp.salary
            from emp, jobs
            where emp.job_id=jobs.job_id;

            in a custom summary folder and Discoverer would redirect the query to summary folder if location was not used. You can also do a similar thing at the database level using views and materialized views.

            3. Discoverer Complex folders - you can change your custom folder into a complex folder joining together two or more simple folders. If the columns mapped to a simple folder are not reference, then provided the joins and Discoverer preferences are set correctly the query generated should not reference the table in the simple folder.

            Rod West
            • 3. Re: Dynamic SQL generation when using Custom Folders
              Venu6
              Thanks Rod West. Complex Folders did the trick for us.
              • 4. Re: Dynamic SQL generation when using Custom Folders
                Venu6
                Thanks a lot. I am learning a new thing every day .