sql - Enterprise Reporting Solutions

Question

question from:https://stackoverflow.com/questions/16804/enterprise-reporting-solutions

Answer 1

I've used Cognos Series 7, Cognos Series 8, Crystal Reports, Business Objects XI R2 WebIntelligence, Reporting Services 2000, Reporting Services 2005, and Reporting Services 2008. Here's my feedback on what I've learned:

Reporting Services 2008/2005/2000

PROS

1. Cost: Cheapest enterprise business intelligence solution if you are using MS SQL Server as a back-end. You also have a best-in-class ETL solution at no additional cost if you throw in SSIS.

2. Most Flexible: Most flexible reporting solution I've ever used. It has always met all my business needs, particularly in its latest incarnation.

3. Easily Scalable: We initially used this as a departmental solution supporting about 20 users. We eventually expanded it to cover a few thousand users. Despite having a really bad quality virtual server located in a remote data center, we were able to scale to about 50-100 concurrent user requests. On good hardware at a consulting gig, I was able to scale it to a larger set of concurrent users without any issues. I've also seen implementations where multiple SSRS servers were deployed in different countries and SSIS was used to synch the data in the back-ends. This allowed for solid performance in a distributed manner at almost no additional cost.

4. Source Control Integration: This is CRITICAL to me when developing reports with my business intelligence teams. No other BI suite offers an out-of-box solution for this that I've ever used. Every other platform I used either required purchasing a 3rd party add-in or required you to promote reports between separate development, test, and production environments.

5. Analysis Services: I like the tight integration with Analysis Services between SSRS and SSIS. I've read about instances where Oracle and DB2 quotes include installing a SQL Server 2005 Analysis Services server for OLAP cubes.

6. Discoverability: No system has better discoverability than SSRS. There are more books, forums, articles, and code sites on SSRS than any other BI suite that I've ever used. If I needed to figuire out how to do something in SSRS, I could almost always find it with a few minutes or hours of work.

CONS

1. IIS Required for SSRS 2005/2000: Older versions of SSRS required installing IIS on the database server. This was not permissible from an internal controls perspective when I worked at a large bank. We eventually implemented SSRS without authorized approval from IT operations and basically asked for forgiveness later. This is not an issue in SSRS 2008 since IIS is no longer required.

2. Report Builder: The web-based report builder was non-existant in SSRS 2000. The web-based report builder in SSRS 2005 was difficult to use and did not have enough functionality. The web-based report builder in SSRS 2008 is definitely better, but it is still too difficult to use for most business users.

3. Database Bias: It works best with Microsoft SQL Server. It isn't great with Oracle, DB2, and other back-ends.

Business Objects XI WebIntelligence

PROS

1. Ease of Use: Easiest to use for your average non-BI end-user for developing ad hoc reports.

2. Database Agnostic: Definitely a good solution if you expect to use Oracle, DB2, or another database back-end.

3. Performant: Very fast performance since most of the page navigations are basically file-system operations instead of database-calls.

CONS

1. Cost: Number one problem. If I want to scale up my implementation of Business Objects from 30 users to 1000 users, then SAP will make certain to charge you a few hundred thousands of dollars. And that's just for the Business Objects licenses. Add in the fact that you will also need database server licenses, you are now talking about a very expensive system. Of course, that could be the personal justification for getting Business Objects: if you can convince management to purchase a very expensive BI system, then you can probably convince management to pay for a large BI department.

2. No Source Control: Lack of out-of-the-box source control integration leads to errors in accidentally modifying and deploying old report definitions by mistake. The "work-around" for this is promote reports between environments -- a process that I do NOT like to do since it slows down report development and introduces environmental differences variables.

3. No HTML Email Support: You cannot send an HTML email via a schedule. I regularly do this in SSRS. You can buy an expensive 3rd party add-in to do this, but you shouldn't have to spend more money for this functionality.

4. Model Bias: Report development requires universes -- basically a data model. That's fine for ad hoc report development, but I prefer to use stored procedures to have full control of performance. I also like to build flat tables that are then queried to avoid costly complex joins during report run-time. It is silly to have to build universes that just contain flat tables that are only used by one report. You shouldn't have to build a model just to query a table. Store procedure support is also not supported out of the box without hacking the SQL Overrides.

5. Poor Parameter Support: Parameter support is terrible in BOXI WebIntelligence reports. Although I like the meta-data refresh options for general business users, it just isn't robust enough when trying to setup schedules. I almost always have to clone reports and alter the filters slightly which leads to unnecessary report definition duplication. SSRS beats this hands down, particularly since you can make the value and the label have different values -- unlike BOXI.

6. Inadequate Report Linking Support: I wanted to store one report definition in a central folder and then create linked reports for other users. However, I quickly found out end-users needed to have full rights on the parent object to use the object in their own folder. This defeated the entire purpose of using a linked report object. Give me SSRS!

7. Separate CMC: Why do you have to launch another application just to manage your object security? Worse, why isn't the functionality identical between CMC and InfoSys? For example, if you want to setup a scheduled report to retry on failed attempts, then you can specify the number of retries and the retry interval in CMC. However, you can't do this in InfoSys and you can't see the information either. InfoSys allows you to setup event-driven schedules and CMC does not support this feature.

8. Java Version Dependency: BOXI works great on end-user machines so long as they are running the same version of java as the server. However, once a newer version of java is installed on your machine, things starts to break. We're running Java 1.5 on our BOXI R2 server (the default java client) and almost everyone in the company is on Java 1.6. If you use Java 1.6, then prompts can freeze your IE and FoxFire sessions or crash your report builder unexpectedly.

9. Weak Discoverability: Aside from BOB (Business Objects Board), there isn't much out there on the Internet regarding troubleshooting Business Objects problems.

Cognos Series 8

PROS

1. Ease of Use: Although BOXI is easier to use for writing simple reports for general business users, Cognos is a close 2nd in this area.

2. Database Agnostic: Like BOXI this is definitely a good solution if you expect to use Oracle, DB2, or another database back-end.

3. FrameWork Manager: This is definitely a best-in-class meta-data repository. BOXI's universe builder wishes it was half as good. This tool is well suited to promoting packages across Development, Test, and Production environments.

CONS

1. Cost: Same issue as Business Objects. Similar cost structure. Similar database licensing requirements as well.

2. No Source Control: Same issue as Business Objects. I'm not aware of any 3rd party tools that resolve this issue, but they might exist.

3. Model Bias: Same issue as Business Objects. Has better support for stored procedures in FrameWork Manager, though.

4. Poor Parameter Support: Same issue as Business Objects. Has better support for creating prompt-pages if you can code in Java. Buggy behavior, though, when users click the back-button to return to the prompt-page. SSRS beats this out hands-down.

5. Inadequate Error Handling: Error messages in Cognos are nearly impossible to decipher. They generally give you a long negative number and a stack dump as part of the error message. I don't know how many times we "resolved" these error messages by rebuilding reports from scratch. For some reason, it is pretty easy to corrupt a report definition.

6. No Discoverability: It is very hard to track down any answers on how to troubleshoot problems or to implement functionality in Cognos. There just isn't adequate community support in Internet facing websites for the products.

As you can guess from my answer, I believe Microsoft's BI suite is the best platform on the market. However, I must state that most articles I've read on comparisons of BI suites usually do not rate Microsoft's offering as well as SAP's Business Objects and Cognos's Series 8 products. Also, I've also seen Microsoft come out on the bottom in internal reviews of BI Suites in two separate companies after they were review by the reigning CIO's. In both instances, though, it seemed like it all boiled down to wanting to be perceived as a major department that justified a large operating budget.