nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Dynamic DB connection after logged into OBIEE

Selva Pandiyan

Hi All

We have a separate data sources for each country (around 40 countries). These data sources will have identical tables and country specific data inside

If an user belongs to more than one country.

How do I switch them from Country A to Country B.

Thanks

Selva

Note:

If an user belongs to only one country, we can dynamically do based on NQ Session variables at Data source/schema/password.

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

Hi,

If you set the session variable containing the connection details as a request variable (by checking the "enable any user to set the value" checkbox) you can then use a prompt in your dashboard to change the value and point to a different source on the fly.

Users with just one country will see a single (default selected) value in the prompt and so nothing change for them, users with more than one country will be able to switch the country using a prompt (and actually they will even not notice they are connecting to a different database, for them the prompt is just an attribute like any other they define as filter).

[Deleted User]

Blogged this years ago for Essbase but the concept is the same for any source you can imagine: http://dimensionality.ch/2009/10/21/obiee-101341-dynamic-data-source

Gianni Ceresa

Next time we have to put our answers together: words + link with images

[Deleted User]

Always the issue when I do otn on the mobile...i tend to not see/read the whole thread completely.

Selva Pandiyan

Hi Christian/Gianni

The solution is not working out.

We used the following query as initialization block

SELECT

DBNAME ,

DBLOGIN,

DBPASSWORD,

LOGIN_NAME

FROM

DB_DETAILS

WHERE LOGIN_NAME='VALUEOF(NQ_SESSION.OBILOGIN_NAME)'

We used OBILOGIN_NAME as the Request varaiable and also as the session variable.

The connection is always pointing to weblogic user..

Thanks

Selva

Selva Pandiyan

Hi Christian/Gianni

We enabled the checkbox to set by any user. on the Variables

Thanks

Selva

Gianni Ceresa

An init block is executed only once, so if you expect to call your init block every time the user want to switch country it will never work.

You asked how to switch from country A to country B and as said the only way to send back to the RPD a value from the front-end is by using a session variable which can be set by users and this variable is also called request variable in the prompt you create to define it.

For the details of your implementation we can't really help you as you are the one having your sources in front, so you must adapt the information received to make them work for you. Also because you didn't really provided any extra info, so difficult to tell you more than that.

[Deleted User]

Gianni Ceresa wrote:An init block is executed only once

yeah that's where part of your problem / confusion comes from...

Selva Pandiyan

Hi All

So we cant do the init block more than one time

What is the best alternative solution to connect 40 different schemas having the identical tables for the set of reports.

Thanks

Selva

Gianni Ceresa

A single DB account with SELECT permission on the 40 accounts and the schema name is set by the variable is an option.

Set the same password for the 40 different schemas (or the 40 different accounts with access to these schemas so the only piece requiring a variable is the username (schema).

Of course you can also import the 40 and use row level security or also adding a WHERE condition based on the variable to return only the data the user is allowed to see, but not the best option as you must import and map the 40 schemas (even if copy/paste can make the process quicker).