BI publisher data connection. ODBC

Question

I am trying to create ODBC on DV data connection.

for DSN should I put a entry from TNSNAMES.ora?

let me know.

Gianni Ceresa · Answer

@Tony_OAS_Apex_User ,

If you want to define a connection to an Oracle Database in DV, use the "Oracle Database" connection type, that's the easiest way to connect to an Oracle database (compared to ODBC).

If your Publisher data model should be a query on an Oracle database, the best solution is to define the connection to your database as a data source in Publisher itself (so not DV), and then build your Publisher data model as a SQL Query.

Doing this, you are bypassing any intermediate layer that you don't need: Publisher will directly connect to your database when doing its job. Using anything else (DV database, LSQL queries on BI Server etc.) means adding extra steps, extra pieces passing data left and right. If you have a corporate semantic model deployed then using a LSQL query make sense because it avoid you having to re-define all the business rules in your SQL query. But if this isn't the case and you just want to use a SQL query on your Oracle database, create this database as source in Publisher and then use it with a SQL Query directly. The direct approach also simplify security as you aren't touching DV pieces and their permissions do not matter.

RVohra · Answer

@Tony_OAS_Apex_User ,

In Oracle BI Publisher, access to:

* Data Models
* Data Sets
* Reports
* Folders

is controlled by catalog permissions, if regular user cannot see columns, they likely don’t have required permissions.

We give catalog item access permissions to application roles.

The permissions that we can assign to others vary depending on the type of content. To change permissions, the application role that we are assigned to must have the Change Permission privilege.

* On your home page, click Navigator , and then click Catalog.
* In the Catalog page, search for the content to which you want to assign permissions.
*

* In the Permissions dialog, click Add users/roles to access the Add Application Roles and Users dialog to add any required accounts. The roles and users inherit permissions from the roles of which they are members. For example, you can grant Full Control permission to the BIServiceAdministrator application role on the Sales Revenue analysis. This enables any user or application role with that role to have Full Control on the item. You can see the permissions that users and roles have on items (either granted directly or inherited). Click the Click to see effective permissions button in the Add Application Roles and Users dialog to display or hide a Permissions column to see the effective permissions for each row in the Selected Members table.
* In the Permissions dialog, click the Permissions list. Most of the items in the list are parent permissions and contain several child permissions.
* Optional: To build a specific list of permissions, click Custom. This option gives the user authority to bypass any permissions set on the folder that prevent the user from accessing the item from the Catalog or a dashboard. This option doesn’t change the folder permissions. For example, you can grant users the Traverse permission for the Test folder in the shared folder area. Then, they can access items embedded in dashboards stored in this folder. Also, they can access embedded items in dashboards stored in subfolders, such as /<shared folder area>/Test/Guest folder. However, users can’t access (meaning view, expand, or browse) the folder and subfolders from the Catalog.
* Click OK twice.

Hope it helps!

Tony_OAS_Apex_User · Answer

Thanks for your answers..

i just want to create odbc connection to oracle database to create dataset. so I can use it on BI publisher.

I am having some issue that data columns dosn't come up in BI Publisher as regular user.

It doesn't have to be odbc connection within DV, but i already have connection to Oracle database.

As a BI admin group user  I could see all the columns and data but as a regular user can't.

What permission control that? We don't want to give a admin role to the regular user.

Which permission let you see and work with it?