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 order/presentation in virtual table (result set from viewor UDF)

I was just messing around with some ad hoc views and table returning
UDFs today so I could look at and print out data from a small table
and noticed something strange.

If I stick my select statement into a View the columns are returned in
the order I specify in the SELECT, but if the same statement is in a UDF
(so I can specify a parameter), the columns are not returned in the
order specified in statement.

I know that relations don't have a specified column order, but it was my
understanding that a SELECT statement could be used to define how you
want your data presented. Views seem to respect the order specified in
the SELECT, but functions don't.

What am I missing? Is there some way to force the order of the columns
returned from a SELECT?

View:

CREATE VIEW dbo.View1
AS
SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,
ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE '4.2.%')

Column order from this view:
Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,
ElseStmt, NextStmt

Function:

ALTER FUNCTION dbo.Function1
(@.SearchPrm varchar(255))
RETURNS TABLE
AS
RETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,
ChildStmt, ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE @.SearchPrm) )

Column order from this function:
Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,
ParentStmt, ForStmt

Table:
(I know that this table isn't entirely normalized, but it serves my
purposes to have a matrix instead of a fully normalized relation):

CREATE TABLE dbo.tblStmt (
StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,
Ident VARCHAR(255),
Text TEXT,
ErrorText TEXT,
Type INT,
ParentStmt VARCHAR(255),
ChildStmt VARCHAR(255),
IfStmt VARCHAR(255),
ForStmt VARCHAR(255),
ThenStmt VARCHAR(255),
ElseStmt VARCHAR(255),
NextStmt VARCHAR(255),
FullName VARCHAR(255),
LocalName VARCHAR(255),
Method INT
)

INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')

INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')

INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')

INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')I forgot to mention an important detail. I'm creating the VIEW and
FUNCTION within the context of a Microsoft Access ADP file that's
pointed at the SQL Server 2000 database in question.

If I execute this statement in Query Analyzer:
SELECT * FROM dbo.Function1('4.2.%')
the columns are output as specified in the column list inside the
Function definition (Ident, Text, ..., etc.).

If I double-click on the Function's object in MS Access and enter 4.2.%
in the prompt for the parameter, then the column list is output in the
strange order as noted below (Type, Text, ElseStmt, ..., etc.).

So, this may actually be a Microsoft Access problem, but if anyone has
any information, I'd appreciate it. Thanks.

Beowulf wrote:

Quote:

Originally Posted by

I was just messing around with some ad hoc views and table returning
UDFs today so I could look at and print out data from a small table
and noticed something strange.
>
If I stick my select statement into a View the columns are returned in
the order I specify in the SELECT, but if the same statement is in a UDF
(so I can specify a parameter), the columns are not returned in the
order specified in statement.
>
I know that relations don't have a specified column order, but it was my
understanding that a SELECT statement could be used to define how you
want your data presented. Views seem to respect the order specified in
the SELECT, but functions don't.
>
What am I missing? Is there some way to force the order of the columns
returned from a SELECT?
>
View:
>
CREATE VIEW dbo.View1
AS
SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,
ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE '4.2.%')
>
Column order from this view:
Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,
ElseStmt, NextStmt
>
Function:
>
ALTER FUNCTION dbo.Function1
(@.SearchPrm varchar(255))
RETURNS TABLE
AS
RETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,
ChildStmt, ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE @.SearchPrm) )
>
Column order from this function:
Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,
ParentStmt, ForStmt
>
Table:
(I know that this table isn't entirely normalized, but it serves my
purposes to have a matrix instead of a fully normalized relation):
>
CREATE TABLE dbo.tblStmt (
StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,
Ident VARCHAR(255),
Text TEXT,
ErrorText TEXT,
Type INT,
ParentStmt VARCHAR(255),
ChildStmt VARCHAR(255),
IfStmt VARCHAR(255),
ForStmt VARCHAR(255),
ThenStmt VARCHAR(255),
ElseStmt VARCHAR(255),
NextStmt VARCHAR(255),
FullName VARCHAR(255),
LocalName VARCHAR(255),
Method INT
)
>
INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')
>
INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')
>
INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')
>
INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')

|||It is indeed an MS Access problem (specific to ADP-s). You can reorder
the columns in the resulting datasheet freely and Access remembers the
position and width of the columns when you open the table/view/function
again. Access usually asks "Do you want to save changes to the layout
of function '...' ?". If you respond "yes", it stores this information
in the database, using extended properties for the objects and for the
columns.

Razvan|||Razvan Socol wrote:

Quote:

Originally Posted by

It is indeed an MS Access problem (specific to ADP-s). You can reorder
the columns in the resulting datasheet freely and Access remembers the
position and width of the columns when you open the table/view/function
again. Access usually asks "Do you want to save changes to the layout
of function '...' ?". If you respond "yes", it stores this information
in the database, using extended properties for the objects and for the
columns.

Thanks for the reply, even though the question turned out to be off-topic.