We report straight out of the relational database, no data warehouse or anything. So the Universe essentially mimics the ERD from the enterprise system. But ours is now spaghetti code due to derived tables, aliases, etc. etc.
Background and a few questions, just looking for some high level advice …
We have two main systems, both in the same Oracle d.b. One has ~300 tables and ~5,000 columns, 50,000 records for the primary entity (students). The other has ~200 tables, 4,000 columns, and 600,000 records for the main entity (prospective students).
We have 7 primary Universes for the 300-table system, and just one Universe for the ~200-table system.
I’m relatively new to BusinessObjects (3 years), but have a few basic questions as we consider a total re-design.
It seems to me that contexts should be avoided at all costs for Ad hoc users, they never understand it (the BO team barely understands them). Plus, it also seems to me that contexts can be avoided by building Oracle views which mimic a data warehouse, thus limiting (in the Universe) the joins, derived tables, and aliases.
So a secondary question is, isn’t it reasonable - and preferable - to build as much as possible in Oracle, so Universe design (and building business layers) is simplified? One benefit of this is that you’d re-use these views across multiple Universes, thus reducing your code footprint, maintenance, etc.
In our current environment, the one system with ~300 tables and 7 primary universes has many of the same objects in each Universe, but it’s a separate code base (Data Foundation), so code is replicated between the 7 Universes. For example, there’s a “student” folder in each of the 7 Universes, but as you can imagine over the course of 10+ years, they diverge in unity, and maintenance is 7 times what it could be if there was a common folder across Universes.
- So my main question here is, isn’t there a way to build a common layer that’s used among the 7 Universes? Thus reducing mainteance significantly (while improving consistency)?
New users almost always make this mistake. Contexts are EXTREMELY powerful. They are a pain. They can be difficult to get correct. There are instances that you cannot use them. But you are better off proving that you can’t use them, than trying to avoid them to begin with.
Yes, they are called Contexts. Ok, that is a bit simplistic. But they are one component in the solution.
If an end user ever sees a context issue, there is probably an error in the report or universe.
All that said, if you can get a good data warehouse to report off of, most of this will become significantly easier. Get a data warehousing expert to build it. Do not get a talented/good/experience/whatever DBA to do it. I am living that nightmare right now. The tools are the same but the thought process is very different.
Management usually says no to this, too much time/money/effort/change. But you would be amazed at the difference a good datamart makes. (It also keeps reports from interfering with daily data processing.)
I would like to know more, but that can be a topic for another day. I think it’s fair to say our implementation is poor. But I suppose if contexts are done right, it can actually reduce the complexity of a Universe, whereas trying to eliminate them causes unnecessary hoop-jumping.
Are you referring to a multi-dimensional db? Star schema and all that? I’ve never worked with them but our needs are not that complicated.
You don’t have to go multi-dimensional, but a good dw with a Star schema will make your job so much easier. I am not a data warehouse person myself, but I have implemented universes on top of traditional databases, poorly designed data warehouses, and well designed data warehouses. A well designed data warehouse makes our job so much easier it is almost like cheating.
Yes, that’s my take! Ours is way too complicated, and it was built for IT Report Developers, then simply opened up to end users without consideration for what they actually do. As a result, they are like a 1 skill level on a scale of 1 - 10.
I haven’t ever looked a this to any degree, so can’t comment. But really, all we do is print lists of students, grades, courses, etc. Hardly any analysis / analytics.
If you can’t get an actual Data Warehouse or DataMart, you can simulate one with derived tables. This is second best, but often easier to get buy in on.
IMHO, use Ralph Kimball’s methodology to design Star schemas. Put one context on each star. You might need an additional context or 2 for dimension only queries. BO and Kimball work really, really well together.
Your end users will jump from 1’s to 3’s overnight and be able to advance from there.
That’s what I’m looking for! I’ll check the book tomorrow and maybe post a quick followup. But that’s what I’m looking for. Do you use them to good effect?
(BOB member since 2015-06-18)
(BOB member since 2002-08-15)
(BOB member since 2006-03-30)