.NET Reporting against and Orcale Database

[Larry Dooley]

Here's my issue. We've decided to replace a very critical (without it
the business would lose lots of money) departmental reporting system
with a built from scratch system based on .NET. The key component is a
datagrid (it really turned the CIO's head).

The issue is that the data source is Oracle. We've got a very complex
reporting system that produces dynamic sql and sends it to oracle and
takes back the result set and displays it. Each report has at least 3
filters and some more than a dozen. Each filter can either be a single
item, a list of 1 to N items or left blank. The idea is to push this
into Oracle stored procedures and get back a refcursor. The
filters/parameters effect not only the where clause, but can effect the
select clause, the from clause (what tables are queried) and when either
the from clause or select clause are effected the group by clause is
effected. This is a complex database (not large by data wharehouse
standards) with indexes and structure that are not friendly to
reporting. Speed of the reports is a critical issue. It's something
we've fought pretty sucessfully in the old system.

We've also got to have a batch component. That is a number of reports
need to run overnight and be available first thing in the morning. This
can't be just a single job that runs a bunch of reports. We need each
report to run separately. Oh and it needs to not start before certain
jobs are finished on the Oracle database.

Oh we've got a schedule of six months - nine months max and the clock is
ticking.

For the first issue. Has anyone done something similar. If so any key
problems with this (forget buy crystal, cognos etc. It's not cost they
are rounding error in our bottom line, but that decision is pretty set
also forget a data wharehouse - no time)

Second issue - does anyone know a good scheduler that will integrate
well with a .NET solution.

Thanks in advance.

[Jim Kennedy]

"Larry Dooley" <la*********@yahoo.com> wrote in message
news:Xn************************@216.196.97.142...

Here's my issue. We've decided to replace a very critical (without it
the business would lose lots of money) departmental reporting system
with a built from scratch system based on .NET. The key component is a
datagrid (it really turned the CIO's head).

The issue is that the data source is Oracle. We've got a very complex
reporting system that produces dynamic sql and sends it to oracle and
takes back the result set and displays it. Each report has at least 3
filters and some more than a dozen. Each filter can either be a single
item, a list of 1 to N items or left blank. The idea is to push this
into Oracle stored procedures and get back a refcursor. The
filters/parameters effect not only the where clause, but can effect the
select clause, the from clause (what tables are queried) and when either
the from clause or select clause are effected the group by clause is
effected. This is a complex database (not large by data wharehouse
standards) with indexes and structure that are not friendly to
reporting. Speed of the reports is a critical issue. It's something
we've fought pretty sucessfully in the old system.

We've also got to have a batch component. That is a number of reports
need to run overnight and be available first thing in the morning. This
can't be just a single job that runs a bunch of reports. We need each
report to run separately. Oh and it needs to not start before certain
jobs are finished on the Oracle database.

Oh we've got a schedule of six months - nine months max and the clock is
ticking.

For the first issue. Has anyone done something similar. If so any key
problems with this (forget buy crystal, cognos etc. It's not cost they
are rounding error in our bottom line, but that decision is pretty set
also forget a data wharehouse - no time)

Second issue - does anyone know a good scheduler that will integrate
well with a .NET solution.

Thanks in advance.

Sounds like someone picked a technology for its "coolness" (eg someone
wanted to learn the technology for their resume)

If you know what the reports look like then the datawarehouse isn't that
difficult and would vastly simplify queries and improve performance. If
this is so business critical and worth a ton of money then doing it right
with known techniques and best industry practices (learn from others who
have gone before) is going to take less time and give you a better result
than chasing a BSO. (bright shiny object) Building it from scratch is 2
orders of magnitude of effort greater than using a packaged application.
(datawarehouses are designed for reporting, OLTP database schemas are not as
easy to do cojmplex reporting out of. Also reporting out of your OLTP
system may cause a performance hit there with the OLTP system. DW's usually
have a lot of indexes especially bitmapped indexes - something you won't
have in an OLTP system.(bitmapped indexes)
I would seriosly reconsider your approuch. Yes, you can have stored
procedures return refcursors. I am hoping you have someone familiar with
how to do that.
Jim