Best Data source for Crystal Reports

[Former Member]

Hi Experts,

We have decided to use Crystal Reports 2008, for our project & advised to use BO Universe(based on Dimensional model) as data source against using the Dimensional tables directly(in Oracle).

Since the Universes are based on Dimensional model, typically I have to link two or three Universes for each report. I am concerned about the query performance time involved for the Universe approach vs. using the Dimensional tables directly.

I understand Universe as Meta-Data layer that hides all the database complexity from the end-user, and is a favorable option for Adhoc reports created by end users.

But for developers who are going to create 400 odd reports, I don't see any advantage that Universe brings, compared to linking the tables at Crystal Designer.

And we do not want to write customized Stored Procedures for every report, so our options are Universe/ Oracle tables.

Appreciate any help from you on helping my process.

Thanks.

[Former Member · Accepted solution]

Still the question whether Direct SQL, SP or Universes for a Crystal Reports remain. The only answer available did not solve it.

[Former Member · Accepted solution]

IMHO, the Universe is fantastic for sophisticated end users (who have a general knowledge of logic or a logical mind) to build ad hoc reports. (Less sophisticated users should not be given the ability to design reports.) It should not be used for production reports that will run frequently, as performance can deteriorate quickly. (This is particularly true if you design the universe with the intent to build multiple reports off of one logical view. You tend to do a lot of extra work to gather data that is not needed for the task at hand. Our universe was designed this way - before I got here... I tend not to use it on new reports...)

There is no question in my mind that direct SQL will be the most performant way to develop the reports. In fact, I'd even suggest using SQL Commands as the basis for the reports so the database does all of the heavy lifting of table linking, record selection, sorting, and summarizing. That's what databases are built to do! (I know Crystal is supposed to push what it can to the database, but its definition of what the database can do is very generalized, so a lot ends up on the Crystal server.) This is particularly true if the Crystal server is not the same box as the database server; you don't want to pump huge volumes of data over the network! The downside to this is that some logic tends to get duplicated. This can be minimized by creating additional data (fields, tables, or aggregate tables) in the database, which are populated by one program (where the logic resides) and used by all of the reports. (For example, "definitions" based on data values should be table driven.)

HTH,

Carl

[ido_millet]

A couple of things to consider:

On the positive side, the Universe gives you a data security & abstraction layer that may be useful.

On the negative side, if you ever wish to use one of the many 3rd-party Crystal Reports utilities, viewers, and schedulers (see list at: http://www.kenhamady.com/bookmarks.html), those tools don't work with reports that use a Universe as a data source. Universe is not a supported data source in the Crystal runtime components provided by BO.

hth,

- Ido

[Former Member]

Thank you Ido, the only third party tool currently we might use for the project is Tidal scheduler, I will check whether the Crystal Report using Universe works with that.

My primary concern is with Performance of the query & whether I gain substantial factors for the additional development effort of Universe. From my experience, Universe queries take more time than queries against Oracle tables.

Precisely, I am looking for pros & cons like, 'Perform Grouping on Server' cannot be used in Universe approach, so the grouping always happen at Client.