Microsoft Reporting Service and Oracle

Hi,
I am planning to use Microsoft Reporting Service with Oracle as my
database. Is this a possibility? What are the pros and cons. Also
Oracle is going to return me a huge volume of rows max in millions, can
Microsoft Reporting Service handle this?
What is the best alternative. I am aware that the record sets once
generated from Oracle are going to be residing in SQL Server. What is
the configuration of this SQL Server 2000 or 2005?Where would this SQL
Server reside? Would this SQL server reside on the webserver with other
ASP pages. As I plan to use asp .Net to display the report on the web.
Any help to these is greatly appreciated.
Thanks
SBFirst off, if Oracle is going to return millions of rows then do not use RS.
Really I don't think any reporting tool would work with millions of rows
returned very well but I guarantee you that RS will be awful. First off,
though, why would Oracle be returning millions of rows? Querying millions of
rows is no problem. I routinely query millions of rows in Sybase, in SQL
Server I go against a 80 million row table. BUT, the end result is a few
thousand if that. My reports are for humans to read and a human is not going
to read more than a 20-30 pages. Now, I do have sometimes the need for
30-50,000 rows being exported to Excel. Excel, though, can't handle millions
of rows either. So, again, what conceivable reason would you want that many
rows?
I suggest going here and reading up:
http://www.microsoft.com/sql/technologies/reporting/default.mspx
Short course. RS 2005 is a 2.0 framework asp.net application. RS comes with
a portal called Report Manager. It is optional whether you use the Report
Manager. You can integrate with RS two ways: URL integration and web
services. Visual Studio 2005 comes with a webform and a winform control
which I highly recommend if you are integrating RS into your own
application. As an ASP.Net application RS needs IIS somewhere, either on
your web server or on the database server (which means you have to have IIS
running on the database server). RS using SQL Server for its object caching
and metadata store. It can run on a separate machine than Reporting
Services. BUT, if you run in this configuration you need to have a SQL
Server license for the machine where RS is running.
Your data can come from darn near anywhere. I go extensively against Sybase
as well as SQL Server. You can use ODBC, OLEDB and native dotnet drivers
(Oracle and SQL Server only).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<sonali.bhavsar@.gmail.com> wrote in message
news:1141045456.743618.85290@.v46g2000cwv.googlegroups.com...
> Hi,
> I am planning to use Microsoft Reporting Service with Oracle as my
> database. Is this a possibility? What are the pros and cons. Also
> Oracle is going to return me a huge volume of rows max in millions, can
> Microsoft Reporting Service handle this?
> What is the best alternative. I am aware that the record sets once
> generated from Oracle are going to be residing in SQL Server. What is
> the configuration of this SQL Server 2000 or 2005?Where would this SQL
> Server reside? Would this SQL server reside on the webserver with other
> ASP pages. As I plan to use asp .Net to display the report on the web.
> Any help to these is greatly appreciated.
> Thanks
> SB
>|||Thanks Bruce for your information. These reports are going to be for
email archival kept over many years and consolidated information is
reported region and company wise. Due to its volume the need to export
these rows to excel is required.
>From what I gather as mentioned in your reply reporting servicecannot
handle large volume. To use this reporting service is SQL 2005 a must?
Thanks again
SB
Bruce L-C [MVP] wrote:
> First off, if Oracle is going to return millions of rows then do not use RS.
> Really I don't think any reporting tool would work with millions of rows
> returned very well but I guarantee you that RS will be awful. First off,
> though, why would Oracle be returning millions of rows? Querying millions of
> rows is no problem. I routinely query millions of rows in Sybase, in SQL
> Server I go against a 80 million row table. BUT, the end result is a few
> thousand if that. My reports are for humans to read and a human is not going
> to read more than a 20-30 pages. Now, I do have sometimes the need for
> 30-50,000 rows being exported to Excel. Excel, though, can't handle millions
> of rows either. So, again, what conceivable reason would you want that many
> rows?
> I suggest going here and reading up:
> http://www.microsoft.com/sql/technologies/reporting/default.mspx
> Short course. RS 2005 is a 2.0 framework asp.net application. RS comes with
> a portal called Report Manager. It is optional whether you use the Report
> Manager. You can integrate with RS two ways: URL integration and web
> services. Visual Studio 2005 comes with a webform and a winform control
> which I highly recommend if you are integrating RS into your own
> application. As an ASP.Net application RS needs IIS somewhere, either on
> your web server or on the database server (which means you have to have IIS
> running on the database server). RS using SQL Server for its object caching
> and metadata store. It can run on a separate machine than Reporting
> Services. BUT, if you run in this configuration you need to have a SQL
> Server license for the machine where RS is running.
> Your data can come from darn near anywhere. I go extensively against Sybase
> as well as SQL Server. You can use ODBC, OLEDB and native dotnet drivers
> (Oracle and SQL Server only).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> <sonali.bhavsar@.gmail.com> wrote in message
> news:1141045456.743618.85290@.v46g2000cwv.googlegroups.com...
> > Hi,
> >
> > I am planning to use Microsoft Reporting Service with Oracle as my
> > database. Is this a possibility? What are the pros and cons. Also
> > Oracle is going to return me a huge volume of rows max in millions, can
> > Microsoft Reporting Service handle this?
> > What is the best alternative. I am aware that the record sets once
> > generated from Oracle are going to be residing in SQL Server. What is
> > the configuration of this SQL Server 2000 or 2005?Where would this SQL
> > Server reside? Would this SQL server reside on the webserver with other
> > ASP pages. As I plan to use asp .Net to display the report on the web.
> >
> > Any help to these is greatly appreciated.
> >
> > Thanks
> > SB
> >|||Excel cannot take millions of rows. The limit is 64,000 I believe. If you
are going to do this then export in CSV ASCII format. Excel format (or PDF
as well) are order of magnitude slower.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<sonali.bhavsar@.gmail.com> wrote in message
news:1141065580.645968.51100@.z34g2000cwc.googlegroups.com...
> Thanks Bruce for your information. These reports are going to be for
> email archival kept over many years and consolidated information is
> reported region and company wise. Due to its volume the need to export
> these rows to excel is required.
>>From what I gather as mentioned in your reply reporting servicecannot
> handle large volume. To use this reporting service is SQL 2005 a must?
> Thanks again
> SB
> Bruce L-C [MVP] wrote:
>> First off, if Oracle is going to return millions of rows then do not use
>> RS.
>> Really I don't think any reporting tool would work with millions of rows
>> returned very well but I guarantee you that RS will be awful. First off,
>> though, why would Oracle be returning millions of rows? Querying millions
>> of
>> rows is no problem. I routinely query millions of rows in Sybase, in SQL
>> Server I go against a 80 million row table. BUT, the end result is a few
>> thousand if that. My reports are for humans to read and a human is not
>> going
>> to read more than a 20-30 pages. Now, I do have sometimes the need for
>> 30-50,000 rows being exported to Excel. Excel, though, can't handle
>> millions
>> of rows either. So, again, what conceivable reason would you want that
>> many
>> rows?
>> I suggest going here and reading up:
>> http://www.microsoft.com/sql/technologies/reporting/default.mspx
>> Short course. RS 2005 is a 2.0 framework asp.net application. RS comes
>> with
>> a portal called Report Manager. It is optional whether you use the Report
>> Manager. You can integrate with RS two ways: URL integration and web
>> services. Visual Studio 2005 comes with a webform and a winform control
>> which I highly recommend if you are integrating RS into your own
>> application. As an ASP.Net application RS needs IIS somewhere, either on
>> your web server or on the database server (which means you have to have
>> IIS
>> running on the database server). RS using SQL Server for its object
>> caching
>> and metadata store. It can run on a separate machine than Reporting
>> Services. BUT, if you run in this configuration you need to have a SQL
>> Server license for the machine where RS is running.
>> Your data can come from darn near anywhere. I go extensively against
>> Sybase
>> as well as SQL Server. You can use ODBC, OLEDB and native dotnet drivers
>> (Oracle and SQL Server only).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> <sonali.bhavsar@.gmail.com> wrote in message
>> news:1141045456.743618.85290@.v46g2000cwv.googlegroups.com...
>> > Hi,
>> >
>> > I am planning to use Microsoft Reporting Service with Oracle as my
>> > database. Is this a possibility? What are the pros and cons. Also
>> > Oracle is going to return me a huge volume of rows max in millions, can
>> > Microsoft Reporting Service handle this?
>> > What is the best alternative. I am aware that the record sets once
>> > generated from Oracle are going to be residing in SQL Server. What is
>> > the configuration of this SQL Server 2000 or 2005?Where would this SQL
>> > Server reside? Would this SQL server reside on the webserver with other
>> > ASP pages. As I plan to use asp .Net to display the report on the web.
>> >
>> > Any help to these is greatly appreciated.
>> >
>> > Thanks
>> > SB
>> >
>