How to force OBIEE Reports to use table partitions instead of Full Table Scan — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to force OBIEE Reports to use table partitions instead of Full Table Scan

Received Response
102
Views
11
Comments
3010666
3010666 Rank 2 - Community Beginner
edited Aug 13, 2024 7:03PM in Oracle Analytics Cloud and Server

I have a Report in OBIEE which access a view table(The physical Layer) in Repository.

The View table has access to one of physical table which is partitioned based on DISCOM or group namely 'MG,UG,DG and PG'

But when the report runs instead of using table partition DISCOM wise it does a full table scan.

It actually should work like this suppose a customer logins to the application into a particular DISCOM say 'MG' with its MG userid say "MG7801" the Report should fetch data

only for that particlular DISCOM not for all. Similar for others.

How to reflect table partitioning in Report or repository???

Regards

Debayani

«1

Answers

  • AkanshAgarwal
    AkanshAgarwal Rank 4 - Community Specialist

    Hi Debayani,

    You can try by using the fragmentation properties of the LTS.

    Regards,

    Akansh Agarwal

  • 3010666
    3010666 Rank 2 - Community Beginner

    Hi Akansh,

    How to  implement this fragmentation technique ? It will be helpful if you give few example.

    Also as which partition of Table it will access whether MG /UG/DG/PG partition of table is dynamic

    based on the User id of the customer. How can we implement the same?

    Regards,

    Debyani

  • 3010666
    3010666 Rank 2 - Community Beginner

    Hi ,

    This a sample query for your reference. The below query is in View table of Physical layer in Repositiory.

    I have added table partitioning for DG group "P1_DGVCL" but how to do that dynamically for other groups based on User login Id?

    SELECT COUNT(*) PAID_PENDING, 0 PAID_PENDING_RE, 0 NON_SURVEYED, 0 UNPAID_SURVEYED,

    ORG_ID, CONS_CATEGORY_ID, MONTH_ID, SR_TASK_ID, REGION_ID, SERVICE_TYPE_ID, SCHEME_ID, OSR.ACTIVE_YN ACTIVE_YN

    FROM RAPDRP_MIS_ODS.ODS_SR_TASK_STATUS PARTITION ("P1_DGVCL") OSR

    JOIN RAPDRP_MIS_ODS.ODS_CLASSIFICATION C ON OSR.SR_STATUS = C.CODE

    WHERE SR_TASK_NAME IN ('Firm Quotation','Estimate Payment')

    AND C.CLASSIFICATION_TYPE = 'SR_STATUS'

    AND C.ATTRB1 = 'OPEN'

    AND OSR.SR_TASK_STATUS IN ('CLOSED','CLOSE')

    AND OSR.SR_RECORD_ID IN(SELECT SR_RECORD_ID FROM RAPDRP_MIS_ODS.ODS_SR_TASK_STATUS

    WHERE SR_RECORD_ID= OSR.SR_RECORD_ID AND SR_TASK_NAME IN ('Release Connection') AND SR_TASK_STATUS NOT IN ('CLOSE', 'CLOSED'))

    GROUP BY 0, 0, 0, ORG_ID, CONS_CATEGORY_ID, MONTH_ID, SR_TASK_ID, REGION_ID, SERVICE_TYPE_ID, SCHEME_ID, OSR.ACTIVE_YN;

    Regards

    Debyani

  • AkanshAgarwal
    AkanshAgarwal Rank 4 - Community Specialist

    Hi,

    The way to implement this will to create alias of the tables based on the filter criteria.

    Create a logical table with the all the alias as Multiple LTS.

    And then fragment the tables based on the where condition of each table.

    A session variable can be used to select the fragment runtime i.e the fragment can be based on the session variable.

    I hope this helps.

    Regards,

    Akansh

  • rmoff
    rmoff Rank 6 - Analytics Lead

    What you're doing here is fundamentally wrong. There's no nicer way to put it. The point of OBIEE is that it generates dynamic SQL based on the user requests, and if you model your RPD correctly in line with your physical data model (partitioning etc) then the physical queries generated will be correct such that they take advantage of partition pruning etc.

    By putting a bunch of stuff in an inline view you're masking from OBIEE the true relationships within the data, so it's not surprising the generated SQL isn't what you were hoping for.

    Model your physical tables as individual objects, set up the joins correctly, and then if your user builds a request with a given filter on one of your partitioned columns and you have built the RPD correctly, the generated physical SQL will include the necessary predicate to trigger partition pruning.

  • 3010666
    3010666 Rank 2 - Community Beginner

    Hi,

    The concern is RDP is already designed (including physical tables, logical tables..etc) and it is not using the functionality of table partitioning.

    And now i have to implement this without redesigning the RPD from scratch.

    Can i use partition table selection dynamically using the session variables.??

    Regards

    Debyani

  • rmoff
    rmoff Rank 6 - Analytics Lead

    Technical debt. You're about to start accruing it in spades. Even more so if you try to "use partition table selection dynamically using the session variables".

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner 
    3010666 wrote:

And now i have to implement this without redesigning the RPD from scratch.

    To drive home Robin's point: That's basically making a bad thing worse. If you build your house on sand and things start falling apart it's pointless to repaint the walls.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    Use of information/data drives your design ... you HAVE a design that doesn't line up with your USE ... here's another vote to stop and assess and align your design to the required use.

  • Joel
    Joel Rank 8 - Analytics Strategist

    I Think all that needs to be said about this has already been said. The BI Server is only as clever as the repository model in a nutshell. If properly modelled, all underlying database performance indexes, partitions, hints etc. will be utilised in query generated by BI Server.

    You'll have mo other option but to redesign your repository or this will be the start of many issues for you and your users.