February 1, 2007 at 10:40 am
Hi All,
I have a huge database with a lot of tables for reporting etc. I was wondering if anyone can recommend a reporting tool, that will be very scalable and easy to adapt to company use. These tables hold healthcare information records, so quite a lot of data etc
I look forward to reading from you all.
Thanks
February 1, 2007 at 12:07 pm
John,
SQL Server Reporting Services are a very good tool and come mostly free with SQL Server (see licensing for details when it is free and when it is not)
If you are talking about healthcare information, you may prefer a tool that requires more administration and a lot of setup but produces the security abstraction level: Business Objects.
Regards,Yelena Varsha
February 2, 2007 at 2:26 am
We use Reporting Services at the hospital where I work. Security is handled via Windows Groups, with one group relevant to patient-identifiable information, and one not. It works well for us.
February 2, 2007 at 3:31 am
You need to identify your requirements in far more detail in order to pick the appropriate tool. These will include not only presentation quality and security, but how the tool fits into the physical topology of your organisation. You also talk about VLDB - if you have multi-terabyte databases this is VLDB, if they are under 1 TB then it aint VLDB - and your requirements will be affected by this.
The business leader in reporting is Business Objects, and for good reasons. Their products have features that go beyond what SQL Reporting Services can do. However, if you do not need the extra that BO offers (remember REQUIREMENTS!!) then SQLRS may be all you need. MicroStrategy also have a useful product with its own strengths and weaknesses. However, when your requirements are clear then your shortlist of products will also become clear - it is then down to support costs and contract negotiations to get the best deal for you.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 2, 2007 at 5:11 am
February 2, 2007 at 5:24 am
I've had to "cope" with Business Objects in a couple of client sites, if you want to spend lots of money on something which basically is out dated and allows the query from hell scenario then look no further. My last four clients are all actively replacing BO with reporting services and Analysis Services ( which is a different level ) . John I hope you're not in the UK beacuse your post worries me deeply < grin >
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 2, 2007 at 9:01 am
February 6, 2007 at 3:26 am
February 6, 2007 at 3:59 am
As has been indicated it's not so much the tool or database but what is required as output.
You need to know what type of reports you want - for example publishing a report which you update every hour ( or 5 mins ) is better than letting 500 users all run the same report on your server every hour ( or 5 mins ) If you're planning to let a whole bunch of users ad-hoc reporting on a "large" database then I suggest a potential disaster awaits you.
It's vitally important to understand reporting before deploying or buying the tools - are you going to report direct from your operational database - this is an important factor for example.
You might consider finding a suitable unbiased consultant to advise you or talk to microsoft - pointless talking to the tool vendors as they'll convince you their product is right < grin >
I'm not sure you can get all your answers from a book. As Business Objects has been mentioned I'll outline what I see as a problem from the DBA point of view ( as we have this product currently at my client site ) Our database is designed as a secure database, so no table permissions, all stored procs access. BO can't work with that, it has to query the underlying schema using sql server system tables and procs ( in a secure env. these tables/procs would have public rights removed ) so we don't want table perms, so we have to create a set of views across the table - effectively an abstraction layer, we have to develop on another server and then deploy. BO can't work with image types from sql server so we can't display pictures stored in the database. ( but that's a minor point )
Now the users can query across a set of views - so we have a performance hit already - and it's now impossible to actively optimse the views for all combinations of searches, the larger the tables the worse the problem. OK I have fairly wide tables and I don't claim the database design is ideal - so if the reports are all fully defined great but a tablescan on a 10 million row table joined to another 5 million row table ?? The end result is that I get queries that generate upwards of 68 million io and this is only a 20gb database. The business users don't understand the DBA pain ( and to a certain degree why should they ) they ( the business ) have spent in excess of £250k on this tool which the vendor told them will do eveything the business needs, which it does , but not with any reasonable performance and with queries I cannot optimise because of the range of "generated" queries ( usually outer joins and often with leading wild card searches )
So you might want to consider a data warehouse of reporting database before you consider a reporting tool. Establish requirements.
In truth reporting and BI are difficult areas to reconcile with operational databases, just be careful, a wrong choice can make an operational database unusable.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 6, 2007 at 4:07 am
Colin talks sense; heed his words!
As I said previously, I use Reporting Services. However, in order to get an acceptable level of performance, we've had to build a reporting version of our database to hold data in a format that makes for quicker and easier querying. We initially used views, but came across severe performance problems, so built tables from the views and queried those; now the queries and reporting fly.
February 6, 2007 at 7:05 am
Hi,
For performance I would always recommend creating a Data Warehouse reporting environment and then put some kind of reporting technology on top of that. I personally use Business Object XI Release 2 as my reporting tool of choice.
I have worked on very large data warehouse implementations, including working with Dell on their global data warehouse.
You might want to have a read of the Kimball and Inman books on data warehouse design, this might help you.
Hope the above helps.
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply