Does SAP BusinessObjects require a Universe for relational database?

Question

Goal: I wish users to be able to directly connect to a RDBMS (e.g., MS SQL Server) and do some queries with possible cross references.

Tool: SAP BusinessObjects XI Enterprise

Description:

The main reason is that Universe creation is pretty techy. Imagine the SQL DB structure changing frequently, may be even daily. Hense the synchronization issues.

Is BO capable of doing a cross reference using the BO query GUI usable by non-techy do generate a request like:

SELECT
    Classroom.Location
FROM
    Student,
    Classroom
WHERE
    Student.Name = 'Foo' AND
    Student.ClassroomName = Classroom.Name

...with only a ODBC connection and no Universe (or an autogenerated Universe)?

If yes, does it require foreign keys to be defined?

If no, is there a simple way to create and update (synch) a BO Universe directly from the DB structure? May be using their new XML format?

Answer 1

Good question.

Background

I have implemented one very large and "complex" banking database, 500+ tables, that the customer bought BO for. The "complex" is in quotes because although I created a pure 5NF (correctly Normalised to 5NF) RDB, and most developers and the power users did not find it "complex", some developers and users found it "complex". The first BO consultant could not even create a working Universe, and overran his budgeted one month. The second BO consultant created the entire Universe in 10 days. The whole structure (one 5NF RDB; 5 apps; one Universe; web reporting) all worked beautifully.

But as a result of that exercise, it was clear to me that although the Universe is very powerful, it is only required to overcome the impediments of an un-normalised database, or a data warehouse that has tables from many different source databases, which then need to be viewed together as one logical table. The first consultant was simply repeating what he was used to, doing his techie thing, and did not understand what a Normalised db meant. The second realisation was that BO Universe was simply not required for a true (normalised) RDB.

Therefore on the next large banking project, in which the RDB was pretty much 120% of the previous RDB, I advised against BO, and purchased Crystal Reports instead, which was much cheaper. It provided all the reports that users required, but it did not have the "slice and dice" capability or the data cube on the local PC. The only extra work I had to do was to provide a few Views to ease the "complex" bits of the RDB, all in a days work.

Since then, I have been involved in assignments that use BO, and fixed problems, but I have not used XI (and its auto-generated Universe). Certainly, a preponderance towards simple reporting tools, and avoiding the Universe altogether, which has been proved many times.

In general then, yes, BO Query GUI (even pre-XI) will absolutely read the RDB catalogue directly and you can create and execute any report you want from that, without an Universe. Your example is no sweat at all. "Cross references" are no sweat at all. The non-techie users can create and run such reports themselves. I have done scores of these, it takes minutes. Sometimes (eg. for Supertype-Subtype structures), creating Views eases this exercise even further.

Your Question

Exposes issues which are obstacles to that.

1. What is coming across is that you do not have a Relational Database. Pouring some data into a container called "relational DBMS" does not transform that content into a Relational Database.

   • one aspect of a true RDB is that all definitions are in the ISO/IEC/ANSI standard SQL catalogue.
   • if our "foreign keys" are not in the catalogue then you do not have Foreign Keys, you do not have Referential Integrity that is defined, maintained by the server.
   • you probably do not have Rules and Check Constraints either; therefore you do not have Data Integrity that is defined and maintained by the server.

2. Noting your comments regarding changing "db" structure. Evidently then, you have not normalised the data.

   • If the data was normalised correctly, then the structure will not change.
   • Sure, the structure will be extended (columns added; new tables added) but the existing structure of Entities and Attributes will not change, because they have been (a) modelled correctly and (b) normalised
   • therefore any app code written, or any BO Universe built (and reports created from that), are not vulnerable to such extensions to the RDB; they continue running merrily along.
   • Yes of course they cannot get at the new columns and new tables, but providing that is part of the extension; the point is the existing structure, and everything that was dependent on it, is stable.
   • Noting your example query. That is prima facie evidence of complete lack of normalisation: Student.ClassroomName is a denormalised column. Instead of existing once for every Student, it should exist once for each Classroom.
   • I am responding to your question only, but it should be noted that lack of normalisation will result in many other problems, not immediately related to your question: massive data duplication; Update Anomalies; lack of independence between the "database" and the "app" (changes in one will affect the other); lack of integrity (data and referential); lack of stability, and therefore a project that never ends.

3. Therefore you not only have some "structure" that changes almost daily, you have no structure in the "structure" of that, that does not change. That level of ongoing change is classic to the Prototype stage in a project; it has not yet settled down to the Development stage.

4. If you use BO, or the auto-generated Universe, you will have to auto-generate the Universe daily. And then re-create the report definition daily. The users may not like the idea of re-developing an Universe plus their reports daily. Normally they wait for the UAT stage of a project, if not the Production stage.

   • if you have Foreign Keys, since they are in the Standard SQL catalogue, BO will find them
   • if your do not have Foreign Keys, but you have some sort of "relation" between files, and some sort of naming convention from which such "relations" can be inferred, BO has a check box somewhere in the auto-generate window, that will "infer foreign keys from column names". Of course, it will find "relations" that you may not have intended.
   • if you do not have naming conventions, then there is nothing that BO can use to infer such "relations". there is only so much magic that a product can perform
   • and you still have the problem of "structure" changing all the time, so whatever magic you are relying on today may not work tomorrow.

Answer

Business Objects, Crystal reports, and all high end to low end report tools, are primarily written for Relational Databases, which reside in an ISO/IEC/ANSI Standard SQL DBMS. that means, if the definition is in the catalogue, they will find it. The higher end tools have various additional options (that's what you pay for) to assist with overcoming the limitations of sub-standard contents of a RDBMS, culminating in the Universe; but as you are aware takes a fair amount of effort and technical qualification to implement.

The best advise I can give you therefore, is to get a qualified modeller and model your data; such that it is stable, free of duplication, and your code is stable, etc, etc; such that simple (or heavy duty) report tools can be used to (a) define reports easily and (b) run those report definitions without changing them daily. You will find that the "structure" that changes daily, doesn't. What is changing daily is your understanding of the data.

Then, your wish will come true, the reports can be easily defined once, by the users, "cross references" and all, without an Universe, and they can be run whenever they like.

Related Material

This, your college or project, is not the first in the universe to be attempting to either (a) model their data or (b) implement a Database, relational or not. You may be interested in the work that other have already done in this area, as often much information is available free, in order to avoid re-inventing the wheel, especially if your project does not have qualified staff. Here is a simplified version (they are happy for me to publish a generic version but not the full customer-specific version) of a recent project I did for a local college; I wrote the RDB, they wrote the app.

Simplified College Data Model

Readers who are not familiar with the Relational Modelling Standard may find IDEF1X Notation useful.

Response to Comments

To be clear then. First a definition.

1. a Relational Database is, in chronological order, in the context of the last few days of 2010, with over 25 years of commonly available true relational technology [over 35 years of hard-to-use relational technology], for which there are many applicable Standards, and using such definitions (Wikipedia being unfit to provide said definitions, due to the lack of technical qualification in the contributors):

• adheres the the Relational Model as a principle

• Normalised to at least Third Normal Form (you need 5NF to be completely free of data duplication and Update Anomalies)

• complies with the various existing Standards (as applicable to each particular area)

• modelled by a qualified and capable modeller

• is implemented in ISO/IEC/ANSI Standard SQL (that's the Declarative Referential Integrity ala Foreign Key definitions; Rule and Check constraints; Domains; Datatypes)

• is Open Architecture (to be used by any application)

• treated as as a corporate asset, of substantial value

• and therefore reasonably secured against unauthorised access; data and referential integrity; uncontrolled change (unplanned changes affecting other users, etc).

Without that, you cannot enjoy the power, performance, ease of change, and ease of use, of a Relational Database.

2. What it is not, is the content of an RDBMS platform. Pouring unstructured or un-organised data into a container labelled "Relational Database Engine" does not magically transform the content into the label of the container.

Therefore, if it is reasonably (not perfect, not 100% Standard-complaint), a Relational Database, the BO Universe is definitely not required to access and use it to it full capability (limited only by functions of the report tool).

If it has no DRI (FK definitions), and no older style "defined keys" and no naming conventions (from which "relations can be derived) and no matching datatypes, then no report tool (or human being) will be able to find anything.

It is not just the FK definitions.

Depending on exactly which bits of a Relational Database has been implemented in the data heap, and on the capability of the report tool (how much the licence costs), some capability somewhere within the two ends of the spectrum, is possible. BO without the Universe is the best of breed for report tools; their Crystal Reports item is about half the grunt. The Universe is required to provide the database definitions for the non-database.

Then there is the duplication issue. Imagine how an user is going to feel when they find out that the data that they finally got through to, after 3 months, turns out to be a duplicate that no one keeps up-to-date.

"Database" Object Definition

If you have unqualified developers or end users implementing "tables" in the "database", then there is no limit to the obstacles and contradictions they place on themselves. ("Here, I've got an RDBMS but the content isn't; I've got BO but it can't; I've got encryption but I've copied the payroll data to five places, so that people can get at it when they forget their encryption key".) Every time I think I have seen the limit of insanity, someone posts a question on SO, and teaches me again that there is no limit to insanity.

BO via an ODBC connection is capable of doing JOIN (cross reference) without Universe as long as there are the correct FK defined?

(ODBC has nothing to do with it; it will operate the same via a native connection or via a browser.)

For that one time, re FKs defined correctly, yes. But the purpose of my long response is to identify the that are many other factors.

It isn't a BO or BO Universe question, it is "just how insane are the users' definitions and duplication". FKs could work sometimes and not others; could work today and not tomorrow.