Oracle Analytics Cloud and Server

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

Opaque View joins are not working as expected when Database is changed from DB2 to SQL Server

Accepted answer
51
Views
5
Comments
Rank 1 - Community Starter

The backend database used for OBIEE reports needs to be updated from DB2 to SQL Server. In RPD, i have created Physical, BMM and Presentation layers using SQL Server by mimicking existing design.

What i have observed is, the opaque views joins are not showing up in the SQL log.. These opaque views are joined with other physical tables as per existing design. The joins between physical tables and views which are sourced from DB are generating SQL with joins but not the opaque views created in physical layer.

Is there any limitation with SQL Server database on this feature..please share your thoughts!!

Welcome!

It looks like you're new here. Sign in or register to get started.

Best Answer

  • Rank 1 - Community Starter
    Answer ✓

    CREATE_VIEW_SUPPORTED is enabled already. I think without this feature , creating opaque view is not possible.

    I could create opaque view and set up joins with other tables/views. But these joins are not visible in the SQL(view log).

Answers

  • Rank 8 - Analytics Strategist

    Hi @User_0TRJO

    I have not tested but however did you see any Error from logs while running opaque view scenario?

    Can you share me your connection pool db-features parameters?

    Regards

    Arjun

  • Rank 8 - Analytics Strategist

    @User_0TRJO - Data sources such as XLS and non-relational data sources don't support opaque views and can't run the view deployment utility.

    To verify that opaque views are supported by a SQL sever data source, check whether the CREATE_VIEW_SUPPORTED SQL feature is selected in the Database dialog, in the Features tab.

  • Hi @Sowjanya Beeravolu ,

    Does the code in your opaque view work if you run it as-is in SQL Server?

    Are you actually using any column from the opaque view in your analysis/workbook request?

    I would also suggest to set LOGLEVEL=2 and check the query log to have a better understanding of what is happening behind the scenes.

  • Rank 7 - Analytics Coach

    @Sowjanya Beeravolu Did you give up on this topic? You received input from several people and it would be appreciated if you could follow up on this thread so that all forum users can benefit from it.

Welcome!

It looks like you're new here. Sign in or register to get started.