Combing Multiple Databases for Ad Hoc Reporting

Hey all. Sorry to ask such a basic question, but I am needing to combine data from about 3 different databases into 1 ad hoc report. I know that you can include multiple datasets, but I need this to act as 1 dataset so that the table data between all databases are related to one another.

I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.

Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).

Thanks!

Are those 3 db's on different servers/instances?

If yes, one way is to create linked servers to them, and do a BIG join of the 3 db's

say SELECT * FROM DB1, Server2.DB.dbo.table, Server3.DB.dbo.table WHERE blah blah

you get the idea, use the fully quantified names SERVER_NAME.DB_NAME.OWNER.TABLE_NAME

If they're on the same server/instance, then you can do the same, but omit the SERVER_NAME part and linked server part

That's my suggestion as I can't think of any good ways

|||

Yeah,

My first thought was to do that, but I guess I was hoping for a cleaner way to go about it. Some of these databases are quite large, so I worry about performance.

I have been tasked w/ creating ad hoc reporting that spans these databases "where ppl can report on anything", which seems a bit unrealistic considering the size of the databases involved.

I know that OLAP methods can reduce query speeds drastically, but my problem is that I know so very little about it. I guess my hope was making 3 cubes and linking them as a single datasource...

Thanks for the reply and I guess i will start querying... lol.

Nathan

|||

Hi,
Did you try the option of building OLAP cube from those 3 DBs and then generating a Report Model out of the cube. I think that is a clear option.
Thanks,
S Suresh

|||I am very new to the OLAP scene, although I am trying ramp up very quickly. I bought the Professional SQL server - Analysis Services 2005 w/ MDX and I am working through that and learning a decent amount (about 60% of the book is "point here and click" and does not explain much of the logic behind). My biggest question though is how to make a cube that spans 3 databases... Do I make 3 individual cubes and then make a cube to report on those 3 cubes? I have not found a place in the "wizards" where I can specify more than 1 datasource for a cube (or maybe I should just use the code-behind?). Does anyone have a good link for this kind of reporting or experience with this issue? Thanks!|||

I have a similar situation. Many copies of the same database structure (1000+) on multiple servers (7).

I need to run reports that reference data in all of the databases or selected groups of databases. Is this candidate for OLAP? We currently create queries using linked servers but as you can imagine - they take forever to run depending on the number of databases included.

We have just started to explore possible solutions. The real problem is that the reports need to run against real time data so I am assuming that whatever solution is proposed, it will have to connect to all of the current databases.

Any suggestions or comments would be appreciated!!

Michael

|||

Has anyone had this situation? I would have thought that it would have been common with large retail type organizations. If I had 200 retail locations around the country all running local databases of inventory but I wanted to be able to query all of them at anytime for current inventory status - how would I do it? I need the solution to be real time and not take a long time for the result set.

Thanks

Combing Multiple Databases for Ad Hoc Reporting

Hey all. Sorry to ask such a basic question, but I am needing to combine data from about 3 different databases into 1 ad hoc report. I know that you can include multiple datasets, but I need this to act as 1 dataset so that the table data between all databases are related to one another.

I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.

Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).

Thanks!

Are those 3 db's on different servers/instances?

If yes, one way is to create linked servers to them, and do a BIG join of the 3 db's

say SELECT * FROM DB1, Server2.DB.dbo.table, Server3.DB.dbo.table WHERE blah blah

you get the idea, use the fully quantified names SERVER_NAME.DB_NAME.OWNER.TABLE_NAME

If they're on the same server/instance, then you can do the same, but omit the SERVER_NAME part and linked server part

That's my suggestion as I can't think of any good ways

|||

Yeah,

My first thought was to do that, but I guess I was hoping for a cleaner way to go about it. Some of these databases are quite large, so I worry about performance.

I have been tasked w/ creating ad hoc reporting that spans these databases "where ppl can report on anything", which seems a bit unrealistic considering the size of the databases involved.

I know that OLAP methods can reduce query speeds drastically, but my problem is that I know so very little about it. I guess my hope was making 3 cubes and linking them as a single datasource...

Thanks for the reply and I guess i will start querying... lol.

Nathan

|||

Hi,

Did you try the option of building OLAP cube from those 3

DBs and then generating a Report Model out of the cube. I think that is

a clear option.

Thanks,

S Suresh

|||I am very new to the OLAP scene, although I am trying ramp up very quickly. I bought the Professional SQL server - Analysis Services 2005 w/ MDX and I am working through that and learning a decent amount (about 60% of the book is "point here and click" and does not explain much of the logic behind). My biggest question though is how to make a cube that spans 3 databases... Do I make 3 individual cubes and then make a cube to report on those 3 cubes? I have not found a place in the "wizards" where I can specify more than 1 datasource for a cube (or maybe I should just use the code-behind?). Does anyone have a good link for this kind of reporting or experience with this issue? Thanks!|||

I have a similar situation. Many copies of the same database structure (1000+) on multiple servers (7).

I need to run reports that reference data in all of the databases or selected groups of databases. Is this candidate for OLAP? We currently create queries using linked servers but as you can imagine - they take forever to run depending on the number of databases included.

We have just started to explore possible solutions. The real problem is that the reports need to run against real time data so I am assuming that whatever solution is proposed, it will have to connect to all of the current databases.

Any suggestions or comments would be appreciated!!

Michael

|||

Has anyone had this situation? I would have thought that it would have been common with large retail type organizations. If I had 200 retail locations around the country all running local databases of inventory but I wanted to be able to query all of them at anytime for current inventory status - how would I do it? I need the solution to be real time and not take a long time for the result set.

Thanks

Combing Multiple Databases for Ad Hoc Reporting

Hey all. Sorry to ask such a basic question, but I am needing to combine data from about 3 different databases into 1 ad hoc report. I know that you can include multiple datasets, but I need this to act as 1 dataset so that the table data between all databases are related to one another.

I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.

Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).

Thanks!

Are those 3 db's on different servers/instances?

If yes, one way is to create linked servers to them, and do a BIG join of the 3 db's

say SELECT * FROM DB1, Server2.DB.dbo.table, Server3.DB.dbo.table WHERE blah blah

you get the idea, use the fully quantified names SERVER_NAME.DB_NAME.OWNER.TABLE_NAME

If they're on the same server/instance, then you can do the same, but omit the SERVER_NAME part and linked server part

That's my suggestion as I can't think of any good ways

|||

Yeah,

My first thought was to do that, but I guess I was hoping for a cleaner way to go about it. Some of these databases are quite large, so I worry about performance.

I have been tasked w/ creating ad hoc reporting that spans these databases "where ppl can report on anything", which seems a bit unrealistic considering the size of the databases involved.

I know that OLAP methods can reduce query speeds drastically, but my problem is that I know so very little about it. I guess my hope was making 3 cubes and linking them as a single datasource...

Thanks for the reply and I guess i will start querying... lol.

Nathan

|||

Hi,

Did you try the option of building OLAP cube from those 3

DBs and then generating a Report Model out of the cube. I think that is

a clear option.

Thanks,

S Suresh

|||I am very new to the OLAP scene, although I am trying ramp up very quickly. I bought the Professional SQL server - Analysis Services 2005 w/ MDX and I am working through that and learning a decent amount (about 60% of the book is "point here and click" and does not explain much of the logic behind). My biggest question though is how to make a cube that spans 3 databases... Do I make 3 individual cubes and then make a cube to report on those 3 cubes? I have not found a place in the "wizards" where I can specify more than 1 datasource for a cube (or maybe I should just use the code-behind?). Does anyone have a good link for this kind of reporting or experience with this issue? Thanks!|||

I have a similar situation. Many copies of the same database structure (1000+) on multiple servers (7).

I need to run reports that reference data in all of the databases or selected groups of databases. Is this candidate for OLAP? We currently create queries using linked servers but as you can imagine - they take forever to run depending on the number of databases included.

We have just started to explore possible solutions. The real problem is that the reports need to run against real time data so I am assuming that whatever solution is proposed, it will have to connect to all of the current databases.

Any suggestions or comments would be appreciated!!

Michael

|||

Has anyone had this situation? I would have thought that it would have been common with large retail type organizations. If I had 200 retail locations around the country all running local databases of inventory but I wanted to be able to query all of them at anytime for current inventory status - how would I do it? I need the solution to be real time and not take a long time for the result set.

Thanks

sqlsql

Column Names - Modifing

Can the column names be change in SQL Express.

I am amist of pulling my hair out here, I have got a scenerio of needing to be able to move data around, my first thought was to have two columns, one for the data and the other for the column name. Once getting into the manipulation of the data, it occurred. MUCH EASIER to modify the column name in the data table rather the data.

Is it possible from VB to change the name of the column in the database table?

Thanks Again

Davids Learning

Use sp_rename

create table test
(
trestId int
)
go

exec sp_rename 'test.trestId','testId','Column'

go

select *
from test

testId
--

|||

Ok

You can call me a dummy here,

Can you explain this a little bit more. I havent done a whole lot with TSQL,

and also, is this in VB?

Very Confused

Davids Learning

|||

No, the stuff in bold is the code you would use from management studio (2005) or query analyzer (2000):

create table test
(
trestId int
)
go

exec sp_rename 'test.trestId','testId','Column'

go

select *
from test

The other stuff was setup to show you how it worked. If you don't know how to execute queries, you might ask for a prod in the right direction in the VB forums:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=32&SiteID=1

These forums are for how to write TSQL, which is its own language...