Which Schema used

building semantic layer
1

Hi,

I have access to Designer and i would like to know on which schema basis my universe developed?? and Where i can see Aggregate awareness is used or not??

Please suggest me how to find … Please excuse me if am wrong…

Thanks

BOCP (BOB member since 2007-07-02)

2

Check the DB username in the universe connection. If the tables don’t have any owner then very likely they belong to the user from the universe connection. If there is an owner, then you know right away which schema the table are from.

Check in the menu Tools → Aggregate Navigation whether any incompatibility has been set. If none then very likely aggregate navigation is not used in the universe (unless the universe developer did use it but forgot to set the incompatibilities). Then check also all measure object whether they use @aggregate_aware() function.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Thanks Marek for letting me know.

  1. I can see DB user name in connection, where to see the Schema ??(Star/ Snow-Flake/Multiple Star etc…,)
  2. In Tools-> Aggregate Navigation nothing present and in measure objects also.
    Is it required to use Aggregate_awareness() in universe??

As you said, it’s for Incompatible objects, i got the same information while doing a search in BOB about @AA.

Please let me know…

Thanks

BOCP (BOB member since 2007-07-02)

4

Oh, if you mean the type of a schema (star schema, snow-flake schema, …) then it’s nowhere in the universe to be found. Universe does not store such information because it’s irrelevant for BO Designer (the tool). It is the universe designer/developer (a person) who needs to know the data model based on which the universe is built in order to build a correct and functional universe.

No, it’s not required at all. You can have fully functional universe without aggregate navigation. However, the aggregate navigation provides a very effective way how to optimize queries generated by DeskI/Webi. Read more about aggregate navigation in the Designer guide if you are not familiar with this concept.[/quote]

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

Thanks a ton Marek…!! :slight_smile:

I will read PDF and come back, if i have any difficulties in understanding.

Thanks

BOCP (BOB member since 2007-07-02)

6

  1. Look at the universe structure and see if you have central fact tables with dimension tables surrounding the fact-tables, keyword: dimensional modeling, R. Kimball

  2. No, @aggregate_aware is not necessary per se in a universe. @aggregate_aware allows you to transparently switch between tables, for example between a detail fact table (w/ each single bank transaction e.g.) and a summary table (bank transactions aggregated at a monthly level). This is typically done for performance reasons. The summary table must be created at the database level though.
    One must define aggregate navigation whenever using @aggregate_aware!
    I would recommend using query rewrite option in Oracle instead (other DBMS have similar functionality), this is totally transparent to BusinessObjects and does not require the use of @aggregate_aware at all. The switching to summary tables is handled completely by the DBMS (Database Management System).

Note:
All measures should use a SQL aggregate function (such as SUM, COUNT, etc.) in addition to setting the correct projection, see also here.

Otherwise, I kindly suggest to attend an official training class, topic: Designer.

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

7

Thanks Andreas…!! :slight_smile:

In theory prospective am aware of all. I would like to see all those in practical environment.

As you said, Dimensional modelling, in this case i wanna see how facts and dimensions are joined. I think am going out off.

One more question:

Cardinality: For few i clicked on Detect button for cardinality checking. It shown up 1:N but i can see it was made as N:1 .

What may be the reason??

Hope you can understand my problem and help me out… Please…!!

Thanks

BOCP (BOB member since 2007-07-02)

8

If a dimension table is connected to a dimension in the schema, its snowflake, else its star if all the dimensions are connected to only facts.

Rakesh_K :india: (BOB member since 2007-12-11)

9

As a hint - never rely on the automatic cardinality detection. Always set cardinalities of joins manually, based on YOUR knowledge of the data model that is used in the universe.

And to answer your question:
The automatic detection process calculates the number of records in a table A, then in a table B and then in the result of joining A-B. Based on the 3 values obtained this way, the process decides what the cardinality of the join between A and B is. As you can see, if in both tables you have only a sample of data (it’s typical in DEV environments) and not the real data then the automatic cardinality may detect wrong cardinalities.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

10

Thanks Marek…!! :lol:

  1. How i can see full Table display?? When click on table it showing up single column , next clicking Up and Down arrows to the right, next clicking back to first state.

  2. In Universe Parameters -->Summary tab, i saw Total No.of tables as: 50 but in structure i can see 57 (Joined and not joined). Why??

  3. I tried my end to findout facts and dimensions but not yet succeeded. Anyone please through some light …

  4. I can see Derived tables used

  5. Left and Right outer joins also.

How i can understand what was the exact reasons behind them??

Please help me out… Please…!!

Thanks

BOCP (BOB member since 2007-07-02)

11
  1. How i can see full Table display?? When click on table it showing up single column , next clicking Up and Down arrows to the right, next clicking back to first state.

CTRL+T/View - Change Table Display. When you see the view with the … at the bottom of the table, this is the view that allows you to drag the table from the bottom to show the full list of columns.

  1. In Universe Parameters -->Summary tab, i saw Total No.of tables as: 50 but in structure i can see 57 (Joined and not joined). Why??

At a guess, aliases?

  1. I tried my end to findout facts and dimensions but not yet succeeded. Anyone please through some light …

Is there a naming convention to the tables? Most data warehouse developers will add prefixes or suffixes to tables, e.g. DIM_DATE, DATE_REF or FT_SALES, F_INVOICES, ORDERS_FACT or similar. If this doesn’t exist, it may be the case that it’s an OLTP system.

  1. I can see Derived tables used

These are more often used in OLTP situations to simplify the design but without knowing the SQL driving it, it’s hard to say what its use is

  1. Left and Right outer joins also.

Again, rarely used in a well designed data warehouse where typically you’d get a -1 row in a dimension table. That is, as part of the ETL process if a row comes in with information that isn’t recognised or isn’t provided, -1 will be assigned as the surrogate key to the fact table from the dimension table. There will be a row in the dimension table for -1 for such unknowns to be mapped to in order to avoid outer joins and improve performance.

How i can understand what was the exact reasons behind them??

By asking someone at work! Seriously, if nobody knows about the database, how does it keep going? Who fixes it if it breaks and who adds new functionality to it?