Question Re: Dynamic Database Connection on Report Refresh in Technology Q&A

Dynamic Database Connection on Report Refresh

Former Member — Mon, 04 Oct 2010 10:21:38 GMT

Hi,

I have a WEBI Report & Universe, which is duplicated inorder to fetch data from diff database based on each country.

i.e. If i have 3 countries (as databases are diff) for each country i have got specific Report and Universe. Same User can generate the report for diff countries.

Is there a solution to have only single report and single Universe for multiple databases? i.e. Can i switch database connection dynamically at report refresh.

All, the possible solutions are welcome (including SDKs).

Best Regards,

Anu

Re: Dynamic Database Connection on Report Refresh

former_member142702 — Mon, 04 Oct 2010 11:13:46 GMT

An option is to use universe restrictions for that. But the database you connect to is defined through the group memebership of the user that is starting the report.

If this does not fit your use case you have to use an SDK based solution (custom build)

Another option is to use the Data Federator.

Regards,

Stratos

Edited by: Efstratios Karaivazoglou on Oct 4, 2010 1:14 PM

Re: Dynamic Database Connection on Report Refresh

MariannevL — Mon, 04 Oct 2010 17:47:04 GMT

To add on to the previous post,

you can off course ask a user to log on with a different user if he/she wants to see a different country.

That way you can use the universe restrictions.

Another option is to bring them all into one database.

I.e. dependent on which country the source database is...

In Oracle you can use a database link to link one oracle database to another.

You can pull in a fixed country name as extra column in all these.

Its also possible to union those tables and have the user select a country in selfrestricting join.

I'm sure there are similar options for other database platforms.

Good luck,

Marianne

Re: Dynamic Database Connection on Report Refresh

Former Member — Mon, 04 Oct 2010 20:14:01 GMT

You can implement Row-level security at Universe level, so that User who looged into Infoview and accessing Universe & WebI can see the data as per his/her access rights defined.

So far one can use Universe for one datasource. To implement what you are expecting from BO, to use multiple data providers for single Universe, we have to wait for some time, until next release of BO.

I think, you can use ETL process to get data from multiple data sources and on top of target database you can design your Universe.

Thank You!!

Re: Dynamic Database Connection on Report Refresh

former_member142702 — Mon, 04 Oct 2010 21:04:55 GMT

The product is already avaialable and it's called Data Federator.

Regards,

Stratos

Re: Dynamic Database Connection on Report Refresh

Former Member — Tue, 05 Oct 2010 02:50:01 GMT

Hi Guys,

Thank you all for the quick response.

Unfortunately these solution doesn't work in my case.

I cannot use DB links due to security restrictions in my firm. Nor I can load the data from multiple database to a single DB, and point my universe on that. Single User should be able to generate reports on multiple countries, hence i cannot use Universe restrictions also.

Currently, in XI R2 we have seperate Report & Universe for each country. We need a solution as maintenance is a big headache and we are migrating to XI R3.

I'm now left out with SDK, by which i can point Universe to diff database connection on report generation. Please help me with the below queries:-

1) Can I use SDK (to reset DB connection), while refreshing Report from InfoView? If so how to call the Program Object from the WebI Report on refresh?

2) If not, Do I have to go by end to end SDK implementation for Report refresh using SDK?

3) Any idea, when SAP will provide this feature (point Universe to multiple DB)?

Thanks again,

Best Regards,

Anu

Re: Dynamic Database Connection on Report Refresh

Former Member — Tue, 05 Oct 2010 03:08:24 GMT

Anu,

You have no choice.

Answers to your qns:

1. Nope.

2. Nope

3. After couple of months.

As of now, i can suggest the same. Implementing Row-level security at Universe level and Prompts (Predefined filters) for User and Country. which reduces maintanace effort and User will get a flexibility to choose Country what he/she needs to see the data Or create Prompts at query/report level, And at the same time he/she can save the Reports to Excel/PDF for analysis as well.

Hope you got me.

Thank You!!

Re: Dynamic Database Connection on Report Refresh

Former Member — Wed, 06 Oct 2010 03:15:49 GMT

Hi All,

Thanks for the quick reponse.

Hi BOCP-BOE,

Same user can generate the report for various countries (diff universe & diff databases). Hence can you please elaborate on

how to implement, Row-level security at Universe level and Prompts (Predefined filters) for User and Country.

Thanks again,

Anu

Re: Dynamic Database Connection on Report Refresh

Former Member — Wed, 06 Oct 2010 05:56:10 GMT

Use @Variable(BOUSER/BOPASSWORD) , to validate whether User has access to Universe or nor and at the same time how much rights he/she has to retrieve or see the data.

Use Filter icon at Universe to create Prompt Filter for the same and apply it on Universe.

If you have , User table and Country table, join them based on fields you have to validate whether the User is member of that Country or not.

Thank You!!

Re: Dynamic Database Connection on Report Refresh

Former Member — Wed, 06 Oct 2010 06:26:44 GMT

But this solution will work, only if data resides in single database. Please correct if i'm wrong.

In this case, there exists different database for each country.

Re: Dynamic Database Connection on Report Refresh

Former Member — Wed, 06 Oct 2010 08:39:12 GMT

Yep...!!

See Anu,

To do the same stuff available options are:

1. Introducing ETL

2. Waiting for BO XI4.0

Which we can't solves our problem as of now.

Alternative is:

-->> Using Multiproviders for Reporting.

Steps:

-->> As I told you in my prior posts, implement Filter(Prompt) for all Universes what you have, it may be on top of Oracle or SQL Server or DB2 or any.

-->> In Query panel, Use all dataproviders to get Report data. Here, it will Prompt for each Universe COZ you have applied for all.

-->> As you need single Report, Develop it based on Requirements spec and to accomplish it, you have to use "Merge Dimensions" to see summerized data that is coming from multiple dataproviders or DB's.

-->> If User is having valid rights then he/she can able to get data for the Countries. Otherwise he/she can see data only for which DB/DP he/she has access rights.

I hope you got my logic.

Thank You!!

Re: Dynamic Database Connection on Report Refresh

former_member142702 — Wed, 06 Oct 2010 21:36:34 GMT

You can use the Data Federator, include in it tables from all related databases and use in your universe the @variable('BOUSER') and/or the prompt() commands in order to retrieve the data from the appropriate table.

Federation will be part of the generic universe functionality in BO XI 4.0 still I am not sure if the functionality you require will be covered. As far as I know there ill be also an XI 4.0 version for the Data Federator product.

Regards,

Stratos

Edited by: Efstratios Karaivazoglou on Oct 6, 2010 11:36 PM

Edited by: Efstratios Karaivazoglou on Oct 6, 2010 11:37 PM

Re: Dynamic Database Connection on Report Refresh

Former Member — Thu, 07 Oct 2010 02:47:00 GMT

Thank you all.

I'm checking the solution with Data Federator and custom SDK.

Re: Dynamic Database Connection on Report Refresh

Former Member — Thu, 07 Oct 2010 02:51:31 GMT

Anu,

Did you try what I suggested?? It should work.

Let me know if you have any concerns.

Thank YOu!!

Re: Dynamic Database Connection on Report Refresh

Former Member — Thu, 27 Jan 2011 20:43:05 GMT

Hi Anu,

Have you find any solution on Dynamic connection from WEBI to Universe?

If you find please share, I am also same area working.

Thanks

Bharath