Access two database from a single report

[Shrinivas]

Hi Guys ,

I want to know that can we access two tables from two diffrent
database in a single project ?
I have need such that I have to access two attributes from two diffrent
database tables.

Pls Help me

Thanks
Shrinivas Vaddadi

[Shrinivas]

For more clearity , I am explanning it further.

I need a report having two attributes . these attributes has to created
on two tables from diffrent databases
Thanks
Shrinivas

[COOLGUY]

Hi Shrinivas,
I dont think if this is really possible .may be u can do thro Only i
think thro FreeForm SQL.

[Caio Gouveia]

Shrinivas,

Free Form SQL will not let you connect to 2 different databases at the
same time. The "Database Instance" drop down box will only allow you to
choose one. Here is an explanation why: when selecting columns from
different tables (regardless if they are in the same db or not) the SQL
engine will try to join the tables using the keys. Then it sends the
query (SQL) to the database to resolve it and return the values. So
MicroStrategy creates the SQL and the database executes it. Now, if you
have two tables from two different databases, where should you send the
query to? Database 1 or database 2?

Here is a workaround for this problem that I have been using very
successfully: link the two databases together. In DB2 this is called
Federation. In SQL Server this is called Linked Servers. Not sure what
it is called in Sybase and Oracle. Basically you stablish a connection
between different databases so in case Database 1 receives a query that
joins db1.table1 with db2.table2 it will know where to get the data
from...and then do the join.

In that scenario you will always point your project/report to one
database. Then you can create new attributes as if they were part of
the project OR write a free form sql.

There is another type of software in the market called EII (Enterprise
Information Integration) that does real time analysis from multiple
sources. An example is Digital Harbor. I took a look at their product
and it is very interesting. The only concern I have with that approach
is that they get the data from all different sources and then join in
memory. If the databases are very large this can be very costy...

I hope that helps,
Caio

[COOLGUY]

You will need to use Report Services Documents to pull data from
multiple data sources. The following are the high level steps you need
to use:

1) Create three distinct database instances, one for each database you
want to use.

2) Create freeform SQL reports to create the different forms of the
attributes you want map to the data on the three warehouses.

3) Use the freeform SQL reports as Dataset Objects within Report
Services Documents.

Useful Documentation
---------------------------
1) The following Tech note gives a concise description with screenshots
on how to use Freeform SQL reports, though it is from an Excel
perspective. The underlying idea is the same.

TN5700-80X-1919 - How to create a database in Microsoft Excel to use
with a Freeform SQL report in MicroStrategy Desktop 8.0.x

2) Chapter 3 - Creating Freeform SQL Reports in the Advanced Reporting
Guide (PDF), accessible from the Program Files > MicroStrategy >
Product Manuals

3) Information on Report Services Documents:
C:\Program Files\Common Files\MicroStrategy\Documentation\RSDocs.pdf
[The link from the Product Manuals page is broken in 8.0.1 and is fixed
in 8.0.2]

In that document, the section Chapter 4.(Advanced Documents) > Using
datasets in documents > Working with multiple dataset reports.