How can I create OLAP report that could retrieve data from two different servers ?

bateg
bateg used Ask the Experts™
on
I have two different servers, and I would like to create report that could combines data from two different databases each located in a different server.
Please note that the DB structure is identical but the data stored in each db is different, for example we have HR employees data is stored in Server 1 & Marketing employees data is stored in Server 2, different Employee_ID of course, the report should have both employees data (as measures) and the Employee_ID as a shared dimension, but I don't know how to do that.

We are using SQL server 2000 & analysis services enterprise edition SP4.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
I suggest you create one OLAP SQL database having all your fact and dimmension tables on one of the servers where you also have Analisys Server installed. After that you can populate this OLAP database from both OLTP dbs by using linked server and DTS packages daily/incremental and create/run all your cubes of it.

Author

Commented:
I am having this constant issue with the linked servers, always fail to create one and even when I succeed , only the master  DB is transferred (though I specify the DB I need to use ) and I fail to save the query in a regular view .
I am totally new in the linked server thing and though I posted a question earlier on how to create a linked server the post didn’t help to create what I need.

Can you take me through the whole process step by step on how to create linked server using the wizard in enterprise manager.

Thanks
lcohanDatabase Analyst

Commented:
Ok lets start from scratch and create first linked server then you should be able to use them in any SQL query by fully qualifying the object name like linked_server_name.db_name.schema.object_name
I assume that both servers are on the same NT domain therefor we could use either a NT login account with SA rights on the remote SQL box or a new dedicated SQL login with SA rights on the remote SQL box  and to be used for the linked server purpose only. No matter which one you choose the process should be the same and I’ll just call it link_login but please make sure it EXITS on the remote SQL servers under Security and is part of the sysadmin server role.
Use SQL Server Enterprise Manager to Create a linked server definition using the SQL Server Enterprise Manager Console tree and the Linked Servers node (under the Security folder). Define the name (you could use an IP but I suggest you create an alias name for it under network client utility), provider properties, server options, and security options for the linked server. As both are SQL servers I suggest you use “Microsoft OLE DB Provider for SQL Server” and under security use the link_login account created to handle all the activity for these two servers – use “Be made using this security context”.
You should be able now to run a query like below on the SQL server where you added your linked server to test it and from here on…you can write any queries against it or use it in a DTS connection manager.
Select top 10 * from linked_server_name.db_name.schema.object_name

Author

Commented:
I failed to create the linked server so I used instead the DTSes and union statement to create the shared dimensions, thx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial