Software Question
-
We are looking for a software package that will allow people to build adhoc reports/queries without the abilty to alter the data in the tables. It needs to be extremely easy to use and cheap. Something that might give them a choise of tables to query help them with joins when needed, like I mentioned very easy to use. We get a lot of one time request for reports and they think they can be designed in a minute or two but sometimes it takes a lot longer depending on the data they want. (I wish this was as easy as some people think it is)Any suggestions or comments would be welcomed.
Kurt Kracaw
-
Microsoft Access is quite easy to make query/reports with if you have/can setup linked tables (using readonly accounts).
-
I agree with Jo! Have them do it with Access and then perform a dual report for the "more complicated" easy reports and show them how their results may conflict with yours.
Proof is in the pudding...
I wasn't born stupid - I had to study.
-
The key word you use is Ad-Hoc I've seen tons of companys who think they need this, but it boils down to the fact the company just doesn't know what it wants, and decides not to bother to figure it out. I don't understand the need to just hit the database on a whim. There are solutions that provide ad hoc reporting but they are in the $50k or greater range and not all that easy to use.
Access is fine for this.
You can additionally create views that perform some of the more involved joins so the user does not have to have much knowledge of the data model.
-
A company I used to work used BI-Query (they rebranded it "Market Vision") from Hummingbird to do simple queries from their DB and let users structure results in a report "looking" format. Once it is all setup and the connections/links made, a first time user can usually create a quick report quickly and with little training. It is simple to use, graphical and semi-intuitive. We had success with it doing what you describe. I'm not sure of their costs for implementation (if you find out, I'd like to know). Granted we had a really OLD version...
http://www.hummingbird.com/products/bi/bi_query.html
Angel
-
I would like to point out that, although Access is not a bad choice for the solution, it is a potentially dangerous tool in the wrong hands.
Clearly, you can give proper access to base table data, preventing data modification, but you cannot prevent users from running "crap" queries against your database. You may find that a particular user is able to write a query that returns 900,000,000 rows by impoperly specifying a join term.
Or by being a troublemaker.
select * from sysobjects so1,sysobjects so2,sysobjects so3,sysobjects so4
In my production database, the optimizer thinks the query cost is 900,000,000. It would also let me run the thing.
If your database is "production", a couple bad queries could cause problems.
hth
jg
-
I came late to the posting but I would like to emphasize Jeff's point about poorly constructed queries bringing your database to its knees.
By their nature, inexperienced users with little or no knowledge of database design will present a danger even if they have read-only rights.
Would it be possible to copy your data (replication, log shipping, backup and restore, DTS) to another database and give them access only to this 'reporting' database? There will always be some latency but both sets of users get better performance.
If you want to put in a little time up front, you can create a UDM (Unified Data Model) and really impress the boss with the acronym. Basically, you create views and/or combine tables to make it easier for your report building users to form valid queries.
Hope this helps.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply