Best Practices - Designer

building semantic layer
1

I’ve been asked which way we should set up our universe…and I’m truly at a loss as to which of the different options would work better than the other and under what circumstances.

Here’s the scenario…we have a couple hundred (400?) Oracle tables. Everything is contained in the same database, same connection. Some tables are very specialized to an application. Others are used throughout all of the applications.

What is the preferred method of setting up the universe? Would you create a universe based on each application? Would you create a big universe and manage it by access to the classes? Would you create several small universes and link them together? What are the pros and cons of each of the different methods I could use?

Eileen King :us: (BOB member since 2002-07-10)

2

I don’t want to give you the REAL long answer.

I would break it up by application. However, I wouldn’t have a separate universe for each fact. I would put the ones together that make sense.

I don’t like linked universes. You really need to look at the requirements. Your different user communities will be able to tell you what they need. Gather that info and then build universes that make sense. Some will be combined, some won’t.

Steve Krandel :us: (BOB member since 2002-06-25)

3

I can bet that if you ask users they will say ‘ONE UNIVERSE’ …

dcdas :us: (BOB member since 2002-06-17)

4

:reallymad: Rant on - You never ask users an implementation question. Rant off

You ask them for their business requirements and build according to that. It is our job as IT professionals to make the implementation decisions.

A huge universe will not work well and will be maintenance nightmare. Very few users will ever need more than a small subset of your data, so why have it all in one universe. Give them smaller bites and they’ll use it successfully. Give them one huge gulp and they’ll choke. Your project will be a failure. :wah: I’ve seen it happen over and over.

Steve Krandel :us: (BOB member since 2002-06-25)

5

I agree with you Steve, but if you give users more universes, then you need to train them on running multiple dataproviders and linking them if they want to get data from 2 universe in one query…and I have not SEEN many INTELLIGENT users who will learn this trick fast…

dcdas :us: (BOB member since 2002-06-17)

6

Sorry, but I respectfully disagree.

Most users don’t ever need to do that. If you put the things together that users need, then you don’t have to link data providers. I think it’s MUCH easier to train a user as to which universe(s) to use than it is to teach them to sift through a huge muddled universe to pick out what they want.

Yes, you will have to train some power users. But, if you build your universes for the bigger community, I think you’ll see success much sooner.

Steve Krandel :us: (BOB member since 2002-06-25)

7

Now I have to do the same what my smily is doing>>>

dcdas :us: (BOB member since 2002-06-17)

8

Eileen,
Trying to get any decent reporting system out of 400 tables isn’t fun. I bet it is also highly normalized (Chasm traps, Join Problems, Contexts errors). That type of Transactional system is real good for getting data into it (Nice fast Insert statements), but is horrible at getting data out of it, e.g. Reporting and/or building BO Universes for reporting.

The transactional system that I’m working with was/is about the same size and they had the same problem. My clients solution was to hire the firm that I work for and we built a classic Kimball designed Star Schema (Check out “The Data Warehouse Toolkit” by Ralph Kimball). So now we have 6 Fact tables broke out by business needs, holding about 60 plus measures. We have two levels of granularity, one at a summary level and one at a detail level. E.G. the summary tables have about 2.5 million records and the details have 10~200 million records. The Fact tables are then surrounded by a core group of 8 dimensions - Time - Product - Promotion - Status - Customer - Sales Org. - etc…

From the BO Designer perspective, I take the 9 Core Dimensions and create a CORE.UNV. This allows me to keep all the core dimensions in one universe. None of the dimensions are joined. Any filters and Alaising is also applied here. The main benefit is that I when I make one change to an object in the CORE.UNV it is automatically cascaded to all the derived universes.

I then group the Fact tables by Business Need and link them to the Core Dimensions. Here is the Example:

Core Universe: CORE.UNV
Comprised of dimension Tables ( D_TIME, D_PROD, D_STATUS, D_PROMOTION, etc… )

Derived Universe: PLANNED_EVENTS.UNV
Fact Tables: (F_SUMMARY, F_DETAIL )
Linked to CORE.UNV for the Dimension Records

Joins: All joins are done in the Derived Universe. You set up Contexts between the Two fact tables. Got to also define aggregate navigation and Aggregate Awareness with your measures(Which fact table to I pull my data from?)…

The above solution allows me to Navigate any of the Natural hierarchies which occur in the Dimensions Records (E.G. Year-Quarter-Month-Week- Day) and seamlessly pull data from any of the 60 plus measures in the corresponding fact table (Summary or Detail ).

Pitfalls:
*Data Modeling: Need to understand your source data and requirements, Build a Logical then Physical model. Define granularity… hire a pro…shameless plug :slight_smile:
*Data Extract Tool: Get an out of the box tool to help with the data extracting… E.G. ETI, Informatica. Its WELL worth it! :yesnod:
*Universe Linking: Things can get all messed up when you compound the situation by working with multiple domains (DEV/TEST/PROD). E.G. you can have the fact tables pointing to TEST and the Dimensions records pointing to PROD. :confused:

If you want some screenshots of our Universe Design let me know I would gladly share them with you.
-Dave
:+1:

David Smith :us: (BOB member since 2002-07-10)

9

Djemal and Chris,
Attached you will find part of the Universe Design document that I put together. Hope it makes sense. If you are familiar with BO Designer then I suspect it will. Its basically walks you through the CORE/Linked process, Contexts and @AA Functions that we have set. This is just one of 4 Universes which sit on top of a Star Schema (Oracle 8.17) which contains 8 Fact Tables (4 pairs of Summary/Details) and as of today 9 Dimensions(Time~Product~Status~Customer~etc).

To answer Djemals questions I prefer to keep the Universes Separated by Business Segment. Ours are like this:

Baseline Sales Universe ( 2 Fact Tables - Summary / Detail ) - One Core Universe
Actual Sales Universe ( 2 Fact Tables - Summary / Detail ) - One Core Universe
Planned Sales Universe ( 2 Fact Tables - Summary / Detail ) - One Core Universe
Tactics Sales Universe ( 2 Fact Tables - Summary / Detail ) - One Core Universe

All 4 Universes share the SAME CORE DIMESNIONS. Thus we can Link Data Providers on the Front end Full Client Application with no problem. Report Example: We need to show By Customer, Time and Product Baseline sales Vs. Actual Sales.

PRO to this Design:
:yesnod: -Keeps the design real neat, no potential of creating Context errors. Allows simple Drill Through(Slice and Dice) using the @AA function. We have 6 levels of Hierarchy in the Customer Dimension, so we can run reports at any level. Level 1 to 5 are against the Summary table and the last Level 6 is against the Detail Fact Table.

CONS to this Design:
:nonod: -Webi can only use one Universe and cant Link Data Providers. So our design would be a problem. If We are asked to provide Adhoc Webi capability to the users, then I will have to join some of the Universes above into one main ADHOC Universe.

We went the separate Universe route since all the reports would be done in Full Client and viewed only in Webi. Thus the report developers(me :mrgreen: ) would be experienced with Data Provider Linking. Conversely, if the requirement was more ADHOC based reporting then our path would be more difficult.

Any how if you have any other questions let me know, would be happy to help.
-Dave
NuVista Consulting Group

PS… I’m going to paste this into the original BO Forum, but E-mail you the PowerPoint 2000 Slides.
PSS… If anyone wants to see the PowerPoint slide just drop me an e-mail.

David Smith :us: (BOB member since 2002-07-10)

10

We also have a similar situation where in we have a set of conformed dimensions being used by various "Star"s. The problem we had was that our source system is a hierarchical structure and our users wanted all the three flavours of history -

  1. Current
  2. History Re-instated
  3. As of Date

To do so, the universe that contains the dimensions and there relationship via a ‘Factless’ fact table, has 3 contexts each for one flavour of history. Now, if we use the approach of linked universe, the contexts would have to be recreated, which is as good/ bad as creating the universe from scratch.

The flip side of it, as Dave mentioned is the ignorance of our users to be able to link universes and be aware of "You can link only Measure and Detail objects " fact.

I am currently working on finding out a right mix of the two approaches.

Any other suggestions … workaround…??

Anjan Roy (BOB member since 2002-07-10)

11

Dave: as you have probably figured out, Bob doesn’t currently support file attachments. It’s something that’s being considered. But if you have a web site, or access to some method of internet storage, you can store your documents elsewhere and add a link to your post. Sounds like you have some interesting stuff, if you can manage that.

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)

12

My 2 cents here…

Most users do NOT understand contexts, or why they get prompted to select a context under certain circumstances. Even when we named them something very simple, like “Bookings” or “Billings” for our Order Management universe, it still confused the heck out of most of them.

If you are going to allow users to Create or Edit Documents, this is probably something you’d want to consider if you are building a universe with contexts.

  • Judy

JMulders :us: (BOB member since 2002-06-20)

13

I think training of the end users is key…with good training and a good universe design most of these problems will be addressed.

E.g.: Contexts - in a well defined universe most users will never be prompted for contexts since BO is able to figure out most of the time according to the result objects which context the user is interested in (predefined conditions can help here, too!) - I believe this is one of Steve Krandel’s favorite topics :lol:

Andreas :de: (BOB member since 2002-06-20)

14

I think that mulitple is the way to go. When I first stared working with BO at my currently company they had one huge universe for everything. It was a big disaster. :hb:

It looked like a big spider web, there were line criss crossing every which way. And if I recall it had over 100 loops. Part of the probem was the consultants that set it up didn’t know what they were doing. Not to bash consultants, we just had ones that didn’t know what they were doing. Our company should have done a little more research.

I spent a lot of time dividing them out into smaller universes. I seperate them by business category. I.e we have sales, inventory, stock ledger, plan univeres. And forsee some more in the future. I train our users on the universes they need, and they seem to understand most of the time which universe to pick for what they need. I think multiple smaller universes are much easier to maintain.

Stacy Woodring (BOB member since 2002-08-15)

15

I have created pre-defined filters called ‘Reporting Type Filters’. They are built to avoid the context prompting.

We are planning to train the users on the usage of these.

Anjan Roy (BOB member since 2002-07-10)

16

Hi Karl,

I think of it like this…

Aliases should be used when the same dimension table is being used for multiple purposes - especially within the same query. For example, say you are reporting on hospital information - patients and dates of visits to the hospital, plus information on their physicians. The same doctor might perform different roles at different times. For one visit, he will be the primary doctor, on another visit he might be a consulting doctor, etc. Then you would probably want to Alias the doctor table so that you could show all the roles the doctor played.

Contexts can be used when you have a star schema with fact tables and the dimensions associated to them. In this case, it’s not one table playing different roles, it’s more like all the dimension tables are playing different roles based on which fact table you are querying.

For more details you can query the old List Server; there was quite a bit of discussion on it about Contexts vs. Aliases.

Hope that helps,
Judy

JMulders :us: (BOB member since 2002-06-20)

17

Dave:

I have a question on Contexts. Is it true that only if you have more than one fact tables, you use context. If I have only one fact table, I don’t need to. Right? Even if when I use the “Detect Contexts” from the toolbar, it displays a few contexts. Also, as for the “Unknown Cardianality”, does it really matter? I just use the join which is right based on the database.

The reason I am asking this is that I found the queries in one report are not synchronized automatically through the . I manually link the queries, but when I open the cube again, I didn’t find the cube. Also, If I want to drag some objects from the detailed query into the higher level objects table, it won’t allow me. I wonder what is going wrong? Maybe something wrong with the universe? I checked there is no loop, no structure error except some “Unkown Cardinality”.

Thank you so much for your insight and help.

Lilly

Lilly J (BOB member since 2002-08-22)

18

Lilly,

You need to set the caridnalities in order to correctly use the detect cardinality wizard. However, I wouldn’t use the wizard anyway. You should build the contexts you need on your own.

In general, you would use a context for each fact. Use aliases for dimension tables that are involved in loops.

If you only have 1 fact, then what is causing your loops?

If your contexts are correctly defined, you should see “Join” queries when you look at the SQL. Otherwise, you’ll see “Synchroniztion” queries. Those are generally problems and will behave the way you saw.

Steve Krandel :us: (BOB member since 2002-06-25)

19

Hi, Steve:

You mean I do need those contexts detected in Designer, even if there is only one fact table. But I have the higher level objects and lower level ones. I don’t have any loop, except some “Unknown Cardinalities”. I have set the relationships already.

Thank you so much.

Lilly

Lilly J (BOB member since 2002-08-22)

20

That’s very possible. Without seeing your universe, I can’t make that determination. However, if you set the cardinality “correctly”, then the wizard may not detec any contexts.

Steve Krandel :us: (BOB member since 2002-06-25)