BusinessObjects Board

Accessing 2 databases at once

building semantic layer
#1

Hello,

I have a requirement to write reports across 2 separate databases (indeed, separate DB servers). Specifically, one of these databases is a SAP installation on MS SQL, and the other is a student records system also on MS SQL.

Both systems use the same key to identify an individual.

What is the best way to allow access to the data in a single report? Is it possible to link 2 universes which relate to different databases?

Many thanks,

David

dra4 (BOB member since 2006-01-20)

#2

Hi,

One way is to create two queries within the same report and synchronize ata.

Regards

#3

Hi,
if the objects are the same then you can use different queries to pull the data from different sources and synchronize the data at report level.
cheers

mockingbird :uk: (BOB member since 2007-02-07)

#4

I was hoping to make this as simple as possible, so that users with only basic training could produce reports. Is data synchornisation the only route?

Thanks,

David

dra4 (BOB member since 2006-01-20)

#5

Options:

A) One universe can only use one database connection at a time.
So you would need two universes, and then create two data providers.

See also this classic: Solving the Mystery of Data Provider Synchronization (by S. Bickerton)

B) You could also look at creating a datamart or a virtual datamart using the Business Objects product called Data Federator.

C) Since this is all within MS SQL Server, you can setup MS SQL Server to be able to access another MS SQL Server and its databases. This would then be transparent to BusinessObjects (similar to the DBlink feature in Oracle).

If I was you I would explore option C)
:mrgreen:

Andreas :de: (BOB member since 2002-06-20)

#6

Yeah, Option 3 does sound the best :lol:

Do you have any pointers to good sources of information on this, or is it best to just google around?

Thanks,

David

dra4 (BOB member since 2006-01-20)

#7

Try www.Microsoft.com and search for “SQL Server BOL” (Books Online).

Andreas :de: (BOB member since 2002-06-20)

#8

Isn’t anyone looking at Data Federator? This allows one universe to access multiple physical data sources like you described above. Unlike synchronized data providers within reports, it also handles the caching in a more efficient way. It’s still relatively new for Business Objects, but came from their acquisition of EII vendor Medience awhile back.

Regards,
Cindi

cindihowson (BOB member since 2002-08-30)

#9

Interesting issue, as I’ve been trying to find the EXACT same solution for about 3 months now…

We already have our servers linked, and can write stored procedures, do inserts and updates between them, which is what I THINK your saying in Option “C”, however Business Objects still will not utilize this. We even went so far as creating a view on one server pointing to data on another… the query we wrote works fine if run in SQL Analyzer but not in Business objects. BOL doesn’t actually help unless you know exactly what your looking for. If there is another way to link the servers that business Objects does understand, please give us a bit more details.

Therefore we’ve been trying to go the Data Federator route… however our rep won’t seem to return our emails nor give us any information about this product. (We even opened a ticket as suggested once and it was summarily closed!)

If you find a solution with or without Federator, please post your solution here so others can use it.

JPetlev (BOB member since 2006-11-01)

#10

Now, bare with me…

I think the best way for me to solve my problem, along with my user security problem (in another thread…) is:

  1. Get a new MS SQL Server set up.

  2. Set up links to my other DBs

  3. Set up any other local lookup tables as needed

  4. Set up user security on this server, synced with BO logins

  5. Enjoy!

The only question then is, can I set up one universe to use a single login to the DB, and another universe to use logins according to to user. Also, whilst I’m asking questions :smiley: , can I allow users access to run corporate reports from a universe, but not have that universe displayed in the list when they are selecting a universe to write thier own reports?

Many thanks,

David

dra4 (BOB member since 2006-01-20)

#11

Unless someone has found a way around this, the answer I’ve seen from experience is NO. This will not work.

Andreas’ sugestion for option “C” won’t work that I’ve found. The problem lies in the connection string for the universe. You may use the same SQL security parameters, but part of that connection string it uses internally is the server name itself. Therefore you end up with 2 connection strings, and a universe can only point to one.

As I mentioned before, we tried to fool Business Objects via a view which looked at the other server, but it didn’t like that either.

I believe the only option when dealing with multiple physical servers will be to use Federator, but I know almost nothing about it at this point.

EDIT: Wow I totally read your question wrong :slight_smile: Since it wasn’t the same question as the original post… heh. To answer your question at the top, I don’t believe BO can use a User login and have it pass through to SQL, but you should be able to achieve the same results by using BO’s own security layer in the CMC, which can control what elements a given BO user can see or access. Also I believe that if a user does not have access to a universe it will not show up in the list… however there is currently no way to ‘group’ lists into any sort of categories such as “Hidden” and "Adhoc available’ , though that would be nice :slight_smile:

JPetlev (BOB member since 2006-11-01)

#12

[quote=“JPetlev”]

I’m a bit confused as your experience is very different from what Andreas suggests. If it is any help, I work in a small IT department, and I have good working relationships with the DBA and systems managers. User logins and passwords can be altered at will!

Do you think I am going to be able to link DBs, or will I need data federator?

Thanks,

David

dra4 (BOB member since 2006-01-20)

#13

[quote=“dra4”]

We’re also a small shop… small enough we have no DBA exactly :slight_smile: I tried every which way I could to get it to work cross machine and failed. If Andreas knows this will work for sure, he hasn’t said, I believe his post was more of a ‘this should work’, based upon his 2nd post about Books online, but I could be wrong.

I’ve given up and am going the Federator route, and have left another message for someone to find me a new BO rep just today :slight_smile:

My only concern is going to be the cost of Federator vs the cost of duplicating databases on multiple machines. (Luckily I have the server space to do this at the moment)

JPetlev (BOB member since 2006-11-01)