3 confirmed Dimensions and 2 fact tables

[Former Member]

Hi experts,

how can we connect 3 confirmed Dimensions and 2 fact tables without loops and traps please give me a solution

[Former Member · Accepted solution]

In this scenario,

Create two contexts.

Context 1 : Fact 1 + 3 dimensions

Context2: Fact2 + 3 dimensions.

The above solution is if you are going to create reports only one fact and other dimensions.

If you want to create reports by making joins with Fact table, you should go for aliases.

Regards

Gowtham

[Former Member · Accepted solution]

Thanks for the reply Gowtham,

Here I am using both fact tables with two contexts each context for each fact table,

upto this OK but you said The above solution is if you are going to create reports only one fact and other dimensions.

If you want to create reports by making joins with Fact table, you should go for aliases,

Here I am Using two fact tables in a report I am using 1 Measure from 1 Fact table 5 Measures from 2nd Fact table in the report Iam making 2 queries for 2 Fact tables and I am using merge dimension option to combine the quries.

I think this will be ok with context. but why you suggested aliases I didnot get can you please clarify the doubt.

Thanks.

[Former Member · Accepted solution]

Hi Ramesh,

Whatever the solution you are following is the best one.

But if you don't want to go for merge dimensions, then you can think of aliasing.

Regards

Gowtham

[Former Member · Accepted solution]

Gowtham,

I don't think that you correctly understand what the use of aliases is.

Contexts should be used when the dimension has the same meaning within its join to both fact tables.

Aliases should be used to create a new relationship between tables.

If I have a product that is sold to a customer and delivered from a supplier then the product table will belong in the customer_sales fact table context and in the supplier_deliveries fact table context.

Your date table, though would need to be aliased to represent a relationship of the date that the product was sold to the customer and a second alias to represent the date that the product was delivered by the supplier.

I hope that clears it up for both of you.

Regards,

Mark

[Former Member · Accepted solution]

Mark,

I know.. what is an alias and what is a context.

And I can give tens of similar examples what you gave here.

But at the end, how want to use them is entirely depends on our requirement.

Business Objects is giving best practices. Probably we can follow. But when some thing is not achievible in normal way, we can tweak and use the same.

One such example is Aggregate_Aware we generally use for measure objects. But we can use the same for dimension objects too.

Regards

Gowtham

[Former Member · Accepted solution]

That example isn't a tweak, it's standard practice. It has been for the 15 years I've been using the tool anyway

ramesh, is one fact table an aggregation of the other or are they two separate fact types, e.g. order_header_fact and order_line_fact or order_fact and shipments_fact?

If they are different and you have conformed dimensions then you will create loops. Loops in themselves are not problematic. They are resolved by contexts. This is one of the core pieces of functionality that you must embrace when designing universes and it follows consistent rules. With correct universe parameter settings, the SQL generated will be split between the two contexts. If there is a balance in the dimension objects (they all apply to both contexts) then you will see the two SQL statements associated with a join. If one (or more) of the objects does not apply to all contexts, then you will see "synchronisation".

Traps, as you suggest are avoided by using contexts - defining one context for each of your fact tables will completely get rid of the chasm trap that just joining your five tables together would have created.

Regards,

Mark

[Former Member · Accepted solution]

I could use some more explanation.

What is a fact table and what is a dimension table? I have never heard tables use those terms before.

If you could give a simple example showing how to add the context and aliases, that would be a HUGE help to me. I have been unable to find really any information on contexts for Universe Designer. I know SQL quite well, and have been using it for almost a decade, but I have just recently started using Interactive Analysis (a client wants me to set up a universe for them to report on).

Does using contexts change how you make reports in Interactive Analysis? How do you specify which context to use?

Does anyone know of some good resources to learn more about these things? I have watched all the tutorials, and read through all the help files on Interactive Analysis and Universe Designer, and looked through google, but still have found no real information on why and how to add contexts.

Thanks!!

[amitrathi239 · Accepted solution]

Hi,

Dimensions Tables : Dimension tables are typically small, ranging from a few to several thousand rows. Occasionally dimensions can grow fairly large, however. For example, a large credit card company could have a customer dimension with millions of rows. Dimension table structure is typically very lean, for example customer dimension could look like following:

Customer_key

Customer_full_name

Customer_city

Customer_state

Customer_country

Fact Tables :a fact table consists of the measurements, metrics or facts of a business process. It is often located at the centre of a star schema or a snowflake schema, surrounded by dimension tables.

Fact tables contain keys to dimension tables as well as measurable facts that data analysts would want to examine. For example, a store selling automotive parts might have a fact table recording a sale of each item. The fact table of an educational entity could track credit hours awarded to students. A bakery could have a fact table that records manufacturing of various baked goods.

Context Versus Alias Overview :

http://www.dagira.com/2009/07/22/context-versus-alias-overview/

How to create context :

http://www.bidw.org/business-objects/universe-design/understanding-context-and-its-use-in-business-o...

You can also look on the eFashion universe for more information.

Thanks,

Amit

[Former Member]

Hi,

I have one Querry i.e In BO universe Five dimension & five fact tables .one fact table is join with five dimension tables like that other fact tables join with other dimension tables.so how can i resolue this loop?

[Former Member]

Please create a new discussion.. this is an old discussion which has already been answered..

[Former Member]

When you have conformed dimensions and multiple fact tables, you get loops. They are resolved by contexts. That's the textbook way of resolving them. If you avoid using them, you won't build correct universes. Contexts are very simple once you understand them and always follow the same set of rules.

Aliases are used when dimension tables are used for different things. The most common example is a calendar table, which would be aliased for the different dates in a fact table - an alias for sale date, one for ship date, one for payment date and so on. Failing to create an alias in this circumstance will mean that your query will run but will only return information where transactions are sold, paid for and shipped on the same date.