bateg
asked on
How can I create OLAP report that could retrieve data from two different servers ?
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.
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.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.ob ject_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.ob ject_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
ASKER
I failed to create the linked server so I used instead the DTSes and union statement to create the shared dimensions, thx
ASKER
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