Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606

    Question Reporting Architectures??

    Hi,

    I’m looking for different ideas for reporting techniques/architectures for SQL-based DBMS’s (e.g. Oracle).

    We currently are writing SQR programs to access our Oracle 8i product’s data. As this is a commercial program we can’t change tables (add/remove columns/indexes), create views, procedures, etc. and this has caused problems since 8i does not have the concept of a ‘temporary table’ like Sybase ASE or MS SQL Server.

    Our SQR programs typically run something like this:
    1) Go through each row in the customer table
    2) For each row, run a series of procedures to gather data or exclude customers (based on some criteria). These procedures typically live in a global include file but can also be in the report file itself.
    3) Output to a temporary file, then use Unix sort to sort the file
    4) Turn that file into a PDF or some other format

    The push is to move to a more modular approach. Instead of calling functions in include files it will do something like this:
    1) Program 1 will generate a list of IDs (the primary keys to the tables) based upon initial criteria
    2) Program 1 will pass that ID list to another series of programs that will pare down the list based upon some further criteria
    3) These subprograms will then pass a finalized list to the output program that will dump the data

    Any pros/cons to this approach? How else have people done reporting?

    My opinions:
    I dislike both our current approach and the proposed ideas. It has always been my experience that doing most everything in the DBMS as possible results in improved performance and maintainability over handling things like joins/sorts/etc. in an application language.

    Also, because you are doing queries on each row (instead of an exists/join/etc.) the query cost rises geometrically with each additional row (e.g. you have 10 rows in the base table, you end up running 10 * 5 + 1 queries to get 5 details plus the initial query). Obviously a join or an exists would be a single query and would scale better (b-trees are log2 n I think?).

    The idea that you go through every row and then exclude them from the final tally also seems foreign to me. I’ve always used NOT EXISTS or the like to handle it in the initial query.

    The application we are using is old-school and only supports the rule-based optimizer of Oracle. Because no one was aware of this until I arrived, SQL queries often table-scanned and were generally dog-slow, so the prevailing attitude here is that SQR is faster. I’ve begun teaching the optimizer hint to trigger the cost-based optimizer but the DBA doesn’t have any real statistics analysis routines set up so it is anyone’s guess how old the stats are. I’m trying to get him to at least update on a periodic basis.

    I’ve done reporting in the past on MS SQL Server and we handled it quite differently. A request would arrive for a report, and if we didn’t have the report already written we’d write a stored procedure to fetch the data needed. Then the report writers would draft a Crystal report to handle the output. It seems a good mix of the two ideas above.

    What I’d like to do is something like that. All the SQL would be in the DBMS, and then we’d use some sort of an application language to manipulate the results – right now if someone wants a report sorted by a different column we’d have to write a whole new report. If we could take an existing report and simply change the sort criteria (or if the application could sort on the client-end) that would be great. The long-term goal is to have reports on demand via a web interface, so maybe some .NET forms could accomplish this?

    Any ideas? We do not have a large budget so purchasing a new platform that is relatively expensive is not an option. Maybe we could squeeze something small (< $10K) but I don’t know.
    Thanks,

    Matt

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Reporting Architectures??

    Originally posted by MattR
    We currently are writing SQR programs to access our Oracle 8i product’s data. As this is a commercial program we can’t change tables (add/remove columns/indexes), create views, procedures, etc. and this has caused problems since 8i does not have the concept of a ‘temporary table’ like Sybase ASE or MS SQL Server.
    Can you not create views, procedures, global temporary tables etc. in a different schema that has read-only access to the product's tables and views?

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Maybe. I would have to talk to the Security Admin since he set up those restrictions in the first place.

    In any rate, I'd like to work within those constraints at least initially.
    Thanks,

    Matt

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by MattR
    Maybe. I would have to talk to the Security Admin since he set up those restrictions in the first place.

    In any rate, I'd like to work within those constraints at least initially.
    You would? I would prefer to work without such constraints until someone gives me a good reason not to. As you said yourself, data processing is best done within the DBMS (that's what its for!)

    I can see why the Security Admin would not want you creating or altering objects in the product's schema - that would no doubt invalidate your support contract. But I can see no objection to giving read-only access to those objects to another schema - indeed, this must already be the case, unless all users log in as the production schema owner!

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I certainly would not prefer to work with these constraints, but I do not wish to ignore them hoping the SA will give in to our request and when it comes down to ask him he says no.
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •