Is it ok for databases in the Physical Layer to share ODBC connections? — 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

Is it ok for databases in the Physical Layer to share ODBC connections?

Received Response
11
Views
3
Comments
Chris Arnold
Chris Arnold Rank 5 - Community Champion
edited Aug 13, 2024 5:21PM in Oracle Analytics Cloud and Server

Is it ok for databases in the Physical Layer to share ODBC connections? Or should each database in the Physical Layer have it's own ODBC connection built (System DSN)?

I'll try to explain -- 

I have inherited a RPD that was built previous to my employment.

In this RPD I am seeing that multiple databases that were set up in the Physical layer often hit the same SQL Server databases & same schemas. By that I mean...

RPD_DataBaseA -> SQLServer_DatabaseA.SchemaA

RPD_DataBaseB-> SQLServer_DatabaseA.SchemaA

RPD_DataBaseC-> SQLServer_DatabaseA.SchemaA

I don't see a problem with this. However, I see that a different ODBC connection was created for each RPD_Database. So they have it like...

RPD_DataBaseA -> ODBC_ConnectionA (this ODBC connection connects to SQLServer_DatabaseA.SchemaA)

RPD_DataBaseB -> ODBC_ConnectionB (this ODBC connection connects to SQLServer_DatabaseA.SchemaA)

RPD_DataBaseC -> ODBC_ConnectionC (this ODBC connection connects to SQLServer_DatabaseA.SchemaA)

Is the above good & proper practice?

I am thinking that I should simplify things & lowering the number of ODBC connections needed. I would like to change it to the following...

RPD_DataBaseA -> ODBC_ConnectionA

RPD_DataBaseB -> ODBC_ConnectionA

RPD_DataBaseC -> ODBC_ConnectionA

Is there a reason why I should not do this?

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    As always - it depends. Do you want to be extremely specific from day 1 and prepare immediately for potential changes in connections? Or are you ok with starting more generic and changing things after while in case differing requirements appear?

    I'd say the following: You should not do this if you see a possibility that databases A/B/C are in risk of changing their respective connectivity details. E.g. if they will move to a different server.

    The security is all in the RPD objects anyways so using a generic ODBC isn't much of an issue.

  • Chris Arnold
    Chris Arnold Rank 5 - Community Champion

    Thanks Christian. This gives me something to think about & bring back to my team for discussion.

    Thanks for clearing this up for me!

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Any time