Oracle Analytics Cloud and Server

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

Restricting BI server to Hit the database.

Received Response
51
Views
6
Comments
AbinashBehera
AbinashBehera Rank 5 - Community Champion

Hi All,

I have the requirement to have few columns as default in the Report Table and some columns will be in the Exclude Sections of the Table, So whenever user wants to add one more column to the report he/she can use the Include/Exclude option by right clicking on any Column Heading.

But every time I include a column BI server generates the physical query and hits the database, which is giving me a huge performance issue.

What I have observed, first time the query runs, it contains all the columns even if they are in Excluded part, So as per my understanding if the Excluded columns have already been fetched data, why BI server hitting the DB every time?

Pls through some light here...

regards,

Abinash

Answers

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

    Oracle bi has 2 mechanism to.manage cache:

    1. Oracle bi server cache:

    Do you see your enterprise.maanager if you cache.option.is checked.or not.

    How.to.setting up.cache, check this article:

    OBIEE 10G/11G - BI Server (Logical Query|Result) Cache [Gerardnico]

    2. Oracle bi presentation cache:

    And check in your.report in the advanced tab the presentation services cache.

    "Bypass oracle bi presentation services cache"

    IMG_20160328_060957.jpg

    Kind regards,

  • [Deleted User]
    [Deleted User] Rank 10 - Analytics Guru
    Abinash2707 wrote:
    
    So as per my understanding if the Excluded columns have already been fetched data, why BI server hitting the DB every time?
    

    Works as designed. "Excluded" is purely a visualization mechanism and NOT a mechanism to control what gets fetched from the database. Excluded columns will always be fetched from the data sources.

    Abinash2707 wrote:
    
    I have the requirement to have few columns as default in the Report Table and some columns will be in the Exclude Sections of the Table, So whenever user wants to add one more column to the report he/she can use the Include/Exclude option by right clicking on any Column Heading.
    

    What's the difference between what you're writing - i.e. people "selecting columns in the Results tab" and providing the users with a reduced set of attribute columns and facts in a restrained Subject Area and them properly utilizing the Criteria tab to construct their analyses?

    Why invent a workaround to how the product works and then ask why the product works as it should? The Results tab and Criteria tab are there for a reason and serve different purposes. Results is Results - it fetches them from the data source and displays them.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    "But every time I include a column BI server generates the physical query and hits the database, which is giving me a huge performance issue." - that's a RED flag!  

    Take a serious look at:  BI Forum 2014 preview - No Silver Bullets : OBIEE Performance in the Real World - Rittman Mead Consulting   thanks to @rmoff for his great thinking in this area. 

  • [Deleted User]
    [Deleted User] Rank 10 - Analytics Guru

    What's the relation to the OP? Are you proposing caching as a solution to performance problems?

  • AbinashBehera
    AbinashBehera Rank 5 - Community Champion

    Hi Christian, Thomas, Cesar,

    Thanks for the Reply!!

    Though I understand the behavior of BI server,.. "Excluded" is purely a visualization mechanism and NOT a mechanism to control what gets fetched from the database" .....There are some difficulties I am having at Criteria Tab which gets me into this approach, which I feel more suitable for the End users(Particularly considering my requirements.)

    Let me tell you the exact requirement....

    Consider I have total 50 Presentation columns out of which 20 columns will be in the Report by default when user runs it. Now I have some business logic to be applied on the basis of Prompt Values selected. So as I can not pass the Presentation Variable to RPD, I am writing some scripts in the EDIT Formula box of each column in the Criteria TAB. How?

    Now consider two scenario:

    1. Taking all the Columns into Criteria TAB and applying my Business script into it and keeping some 30 columns in excluded section for the end user. So that user can easily add those excluded column anytime into his/her reports.

    2. Taking only 20 columns into criteria TAB(should be by default) and applying business logic into it and showing to users. But there is a limitation here  when ever user wants to add more  columns into the Report for analysis purpose he/she has to go Criteria TAB by clicking EDIT button and drag the Column into it, which is OK as of now, but user  doesn't want to apply Business Logic into it(which is obvious, bcz End user won't like to write the scripts)

    So i went for Scenario 2, which seems bit easy for end users, but here comes the performance issue, every time one column included report runs again and it hits to data base.


    Is there any different and better approach for this...pls suggest


    Regards,

    Abinash

    @Christian Berg, @cesar.advincula.o , @Thomas Dodds

  • [Deleted User]
    [Deleted User] Rank 10 - Analytics Guru

    Well you gotta decide. Do you want to use the tool as it is constructed and supposed to be used or do you want to shoehorn it into how your users want to use it? If the latter than maybe go for something totally custom-built instead of a bought tool.

    This applies to any toolset out there by the way, I'm not talking about OBI with this. ANY tool has a way it's written and a way it's used which will never fit 100% to whatever users conjure up as ideas. Note that I'm not saying "requirements".

    Give me one valid reason why someone should have ANSWERS access - which is the analysis construction side of things rather than the consumption side of things but not use the tool.

    You can go for DV (Data Visualization, ex Visual Analyzer), you can go for Qlik, you can go for Microsoft's stack, you can go for Tableau, you can go for SAP - you'll always have the same "issue" of getting a "requirement" to make things work differently.