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

combine two columns

have a basic Q.
I have a table which contains two columns
froz_month and froz_year (yes the date has been split by the app into these
two)
I need to be able to "combine" these two back into one
like mmyyyy or yyyymm
I do not know what the proper sql statment is
I tried select froz_month + froz_year AS totdate
clearly that add's it together rather then giving me a combination
can anyone please clue me in on this
thanks
billBill
Lookup CONVERT () system function in the BOL
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:70F915F2-7119-4F9C-BD1D-5E4FA9ED58B7@.microsoft.com...
> have a basic Q.
> I have a table which contains two columns
> froz_month and froz_year (yes the date has been split by the app into
> these
> two)
> I need to be able to "combine" these two back into one
> like mmyyyy or yyyymm
> I do not know what the proper sql statment is
> I tried select froz_month + froz_year AS totdate
> clearly that add's it together rather then giving me a combination
> can anyone please clue me in on this
> thanks
> bill
>|||try...
select convert(varchar,froz_month ) + convert(varchar,froz_year) as totdate
from TABLE
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:70F915F2-7119-4F9C-BD1D-5E4FA9ED58B7@.microsoft.com...
> have a basic Q.
> I have a table which contains two columns
> froz_month and froz_year (yes the date has been split by the app into
> these
> two)
> I need to be able to "combine" these two back into one
> like mmyyyy or yyyymm
> I do not know what the proper sql statment is
> I tried select froz_month + froz_year AS totdate
> clearly that add's it together rather then giving me a combination
> can anyone please clue me in on this
> thanks
> bill
>sqlsql

Combine results into one field

If I do a basic query
select * from grouping where code='12345'
I get the results
(fields are code,result)
12345 aaaaa
12345 assas
12345 f5fgh
I NEED to get it as aaaaa,assas,f5fgh. Since code is all the same, I
just want one result. Possible?
*** Sent via Developersdex http://www.examnotes.net ***http://www.aspfaq.com/2529
"Joey Martin" <joey@.kytechs.com> wrote in message
news:eUn4NjrTGHA.4308@.TK2MSFTNGP10.phx.gbl...
> If I do a basic query
> select * from grouping where code='12345'
> I get the results
> (fields are code,result)
> 12345 aaaaa
> 12345 assas
> 12345 f5fgh
>
> I NEED to get it as aaaaa,assas,f5fgh. Since code is all the same, I
> just want one result. Possible?
> *** Sent via Developersdex http://www.examnotes.net ***

Combine many rows to one row?

Dear friends,

I have a problem that need some help from expert.Is there any way I could combine many rows into a row in Access using Visual Basic. I want to change the below table from TABLE A to TABLE B

TABLE A SampleCode Test Name Result ID Name Sex 9300105Peripheral Blood Film....
a few poikilocytes are present.S7585512EDHANDAPANI MAHESHM9300105Peripheral Blood Film....
No blast cells seen.S7585512EDHANDAPANI MAHESHM9300105Peripheral Blood Film....
microcytes, elongated cells andS7585512EDHANDAPANI MAHESHM9300105Peripheral Blood Film....
hypochromic but normocytic: . SomeS7585512EDHANDAPANI MAHESHM9300105Peripheral Blood Film....
Majority of rbcs appear slightlyS7585512EDHANDAPANI MAHESHM

Output:

TABLE B SampleCode Test Name Result ID Name Sex 9300105Peripheral Blood Film....
a few poikilocytes are present, No blast cells seen.microcytes, elongated cells and hypochromic but normocytic. Some Majority of rbcs appear slightlyS7585512EDHANDAPANI MAHESHM

Your help would be greatly appreciated

Thanks a lot,

Chicky

---

Chicky

You might want to give this thread from yesterday a look:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1335992&SiteID=1

Column summing other columns

Hi there
I've got this really basic question:
ocasionally I have to do something like this
select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
e.g. to sum up columns in another column.
it seems extremely unelegant, unfortunately using aliases in last
column ( SA+SB+SC) is impossible.
does it make sense performance-wise to leave such an operation to sql
server? will he optimize the query to prevent multiple calculation of
the same column sums, or should I rather sum the columns up on the
client side?
alos, isn't it possible to get rid of duplicating complicated
expressions by using some kind of alias if I want to use them in other
columns ?
thanks
Hp.
The following example is one way to simplify the calculation:
CREATE TABLE foo
(
a INT,
b INT,
c INT
)
INSERT foo SELECT 1, 1, 1
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 3, 4
INSERT foo SELECT 1, 3, 5
SELECT SA, SB, SC, SA + SB + SC
FROM (SELECT SUM(a) AS SA, SUM(b) AS SB, SUM(c) AS SC
FROMfoo) AS T1
HTH
- Peter Ward
WARDY IT Solutions
"H5N1" wrote:

> Hi there
> I've got this really basic question:
> ocasionally I have to do something like this
> select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
> e.g. to sum up columns in another column.
> it seems extremely unelegant, unfortunately using aliases in last
> column ( SA+SB+SC) is impossible.
>
> does it make sense performance-wise to leave such an operation to sql
> server? will he optimize the query to prevent multiple calculation of
> the same column sums, or should I rather sum the columns up on the
> client side?
> alos, isn't it possible to get rid of duplicating complicated
> expressions by using some kind of alias if I want to use them in other
> columns ?
> thanks
> Hp.
>

Column summing other columns

Hi there
I've got this really basic question:
ocasionally I have to do something like this
select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
e.g. to sum up columns in another column.
it seems extremely unelegant, unfortunately using aliases in last
column ( SA+SB+SC) is impossible.
does it make sense performance-wise to leave such an operation to sql
server? will he optimize the query to prevent multiple calculation of
the same column sums, or should I rather sum the columns up on the
client side?
alos, isn't it possible to get rid of duplicating complicated
expressions by using some kind of alias if I want to use them in other
columns ?
thanks
Hp.The following example is one way to simplify the calculation:
CREATE TABLE foo
(
a INT,
b INT,
c INT
)
INSERT foo SELECT 1, 1, 1
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 3, 4
INSERT foo SELECT 1, 3, 5
SELECT SA, SB, SC, SA + SB + SC
FROM (SELECT SUM(a) AS SA, SUM(b) AS SB, SUM(c) AS SC
FROM foo) AS T1
HTH
- Peter Ward
WARDY IT Solutions
"H5N1" wrote:

> Hi there
> I've got this really basic question:
> ocasionally I have to do something like this
> select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
> e.g. to sum up columns in another column.
> it seems extremely unelegant, unfortunately using aliases in last
> column ( SA+SB+SC) is impossible.
>
> does it make sense performance-wise to leave such an operation to sql
> server? will he optimize the query to prevent multiple calculation of
> the same column sums, or should I rather sum the columns up on the
> client side?
> alos, isn't it possible to get rid of duplicating complicated
> expressions by using some kind of alias if I want to use them in other
> columns ?
> thanks
> Hp.
>

Column summing other columns

Hi there
I've got this really basic question:
ocasionally I have to do something like this
select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
e.g. to sum up columns in another column.
it seems extremely unelegant, unfortunately using aliases in last
column ( SA+SB+SC) is impossible.
does it make sense performance-wise to leave such an operation to sql
server? will he optimize the query to prevent multiple calculation of
the same column sums, or should I rather sum the columns up on the
client side?
alos, isn't it possible to get rid of duplicating complicated
expressions by using some kind of alias if I want to use them in other
columns ?
thanks
Hp.The following example is one way to simplify the calculation:
CREATE TABLE foo
(
a INT,
b INT,
c INT
)
INSERT foo SELECT 1, 1, 1
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 3, 4
INSERT foo SELECT 1, 3, 5
SELECT SA, SB, SC, SA + SB + SC
FROM (SELECT SUM(a) AS SA, SUM(b) AS SB, SUM(c) AS SC
FROM foo) AS T1
HTH
- Peter Ward
WARDY IT Solutions
"H5N1" wrote:
> Hi there
> I've got this really basic question:
> ocasionally I have to do something like this
> select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
> e.g. to sum up columns in another column.
> it seems extremely unelegant, unfortunately using aliases in last
> column ( SA+SB+SC) is impossible.
>
> does it make sense performance-wise to leave such an operation to sql
> server? will he optimize the query to prevent multiple calculation of
> the same column sums, or should I rather sum the columns up on the
> client side?
> alos, isn't it possible to get rid of duplicating complicated
> expressions by using some kind of alias if I want to use them in other
> columns ?
> thanks
> Hp.
>