Agg Views in Universe

building semantic layer
1

Hi,
I have designed Universe based on the views and some are multiple views for each table.
I have 3 views (base view, Calculated View and Aggregated View) in the database and needs to be defined in the Universe, Do i need take all three views in the Universe or just the base view ?. If i have to take all 3 views then its needs to be joined ?.
Thanks for any ideas,
Joy

Joy (BOB member since 2004-07-22)

2

Try to read up on @Aggregate_Aware or try a search on BOB.

In brief:
Unless you are using materialized views (Oracle with query rewrite option) or something similar for your DBMS you will have to include all the views/tables into your universe and join them to any “shared”/conformed dimension tables. You then would have to use contexts to resolve the loops.

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

3

Thanks Andreas,
I have read the AGG_Aware notes, but still worried and surprised about this simple issue.
Why should i need to include all views (base, calculated and aggregated) which is created based on one or two base tables ?. Cant we do the same calculation and aggregated methods in the Universe ?. My friend also told me the same to include all views in the Universe. In that case, I have around 122 views in the database and only 47 tables.
Does it make any sense to take all 122 views in the Universe ?.
Any other suggestions are really appreciated.
Thanks,

Joy (BOB member since 2004-07-22)

4

You first mentioned 3 views, now 122 … big difference. I think that you may have 3 per table in many cases, but 122 total views would cause me to ask the same question you have.

Here is the analysis that I would do. What does the view do? If it simply joins tables and provides a list of fields to use, then there is no need to include the view because you can do the joins in Designer. If the view does simple calculations (variance between fields, for example) that too can be done in Designer. If the view just does aggregation (like sum or count) again objects can be created in Designer for those. If you need the base amount as well as aggregated amounts, you can create two objects (one aggregated, one not). Maybe the view creates a subset of data with certain conditions. You can create pre-defined conditions for those. All of this assumes you have a suitable dimensional data model, of course.

There certainly are more complex cases where a view makes sense. The other thing to consider is the user impact. If the user today chooses from the 122 views to create reports, they will have a steeper learning curve if they now have to choose the correct objects / conditions. I personally think that “each object once and only once” is preferable to a multitude of views, but only you can make the assessment if the learning curve is worth it.

Just an opposing “view” to your question.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

5

Dwayne,
Thanks for the response and you are right, they’ve created most of the views for simple calculations like (Min, Max, Avg, Min Date, Max Date, Agg by monthly and deviation). As you said, we can manipulate the same in the Designer. There are couple of complicated chain of views and its better to take those in the designer instead of doing lot of calcs on the designer.

I have one simple scenario,
I have a view called - A.View (Main View customized with other multiple views and tables)

A.View (with B.View and C.View)

–B.View
----B1.View
--------B1a.Tbl
--------B1b.Tbl
--------B1c.Tbl

–C.View
----C1.View
--------C1a.Tbl
--------C1b.Tbl
--------C1c.Tbl

If i consider the A.view in the designer, do i need to join or can i leave this as independent, because the base tables are already joined/linked in the database.

Sorry for all confusion, Today, i’m really going nuts…
–Thanks once again.

Joy (BOB member since 2004-07-22)

6

As far as I am aware tables are never “joined” somehow by default in the database (unless specified in a SQL statement). The only thing close to this is referential integrity regarding foreign keys (or maybe natural joins in Oracle for example).

Regarding your A.View:
You do not have to join B.view and C.view, because A.view is defined as a join between B.view and C.view - this is part of the definition of A.view.

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

7

Thanks Andreas,
I also thinking the same, but just confused and scared. I’m still in Planning phase only and didnt start to design the Universe. Just have done some samples.

I need your last suggestion for this thread.
Which is the best strategy to build the universe ? (built-in or manual)

  • Because we dont have Data Model and its very hard to define joins manually in the Universe. If i take built-in then it cannot be modified.
    Little confused @#$ – Please help.

Thanks for all your help,

Joy (BOB member since 2004-07-22)

8

What do you mean by “built-in”?

Been there, done that, do not like it, but sometimes cannot help it.

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

9

Andreas,
I mean Join Strategies (Automatic Detection and Manually), so just wondering which would be the better approach to select.
If I take the Automatic Detection then i cannot modify the structure in the designer, if i take the option Manual then its hard to join each and every table/view in the designer.
Thanks once again.

Joy (BOB member since 2004-07-22)