Regarding Defining Of Cardinality ............

[Former Member]

Hi,

I am new to Universe Designer and

From the Basic Knowledge that I have on universe designer and from the documents, I cam to know that it is mandatory to define a cardinality for the join between two tables.

Recently I have been into a support project and in the universe I can see joins but there are no cardinalities being defined for any of the join.

Please give me some idea on this..................

[Former Member]

HI..

Using cardinalities

Cardinality is a property of a join that describes how many rows in one table

match rows in another table.

Cardinality is expressed as the minimum and maximum number of rows in

a column at one end of a join, that have matching rows in the column at the

other end of the join.

The minimum and the maximum number of row matches can be equal to 0,

1, or N. A join represents a bidirectional relationship, so it must always have

two cardinalities, one for each end of the join.

Example: Cardinality of a join

The two tables Customer and Reservations are linked by a join.

The cardinalities in the above join can be expressed as follows:

How are cardinalities used In Designer?

The cardinality of a join does not have a role in the SQL generated when

you run a query. However, Designer uses cardinalities to determine contexts

and valid query paths.

A context is a collection of joins which provide a valid query path. You use

contexts to resolve join problems that can return too many or too few rows

because of the way that tables are linked in the target database. Contexts

are described in Detecting and Solving Join Problems

Contexts affect the SQL generated for a query as they either direct the end

user to take a particular join path, or solve a join path problem:

You need to verify that cardinalities are correctly set for all joins in your

schema to ensure that you have the correct contexts, and that you have

valid join paths.

Setting cardinalities can also help you understand how tables are related in

the database, and to graphically identify potential join path problems in your

schema.

You can set cardinalities manually and you can ask tool to detect automatically.

But you should know the Criteria to set cardinalities.

You evaluate the relationship between primary and foreign keys to determine

the cardinality for a join as follows:

I hope this explanation helped you to understand cardinalities

[Former Member]

It is not mandatory to set cardinalities. They do not directly affect the SQL generated.

Do not detect cardinalities either - the algorithm has always been poor for doing this - use a data diagram, data dictionary or person who knows the data schema

The two key benefits are:

1/ What you have already discovered. I'm sure you'd have understood the data schema far more easily with cardinalities showing the one-many relationships

2/ Context detection. The algorithm for this works perfectly, but only when all cardinalities are set. What the alogrithm does is start with tables that only have the many end of cardinalities (usually fact tables in a dimensional model) and goes up join paths that have a "one" defined at the other end (both many-to-one and one-to-one). Once a run can go no further, it collects all the joins that it has navigated into one context and repeats for all base tables that it finds.

As a general piece of advice, I strongly recommend that you go on a universe design course. Anyone can write a decent report from a well-designed universe but a badly-designed universe is the beginning of the end.

[former_member4998]

Hi

Cardinality is a property of a join that describes how many rows in one table match rows in another table. and it's expressed as the minimum and maximum number of rows in a column at one end of a join that have matching rows in the column at the other end of the join. The minimum and the maximum number of row matches can be equal to 0, 1, or N.

So cardinality with Un-Know or one-to-one (1,1) or one-to-many (1,N) or  many-to-one (N,1) or many-to-many (N,N)  will get the same results. And cardinality of a join does not have a role in the SQL generated when you run a query.

But we need to defining cardinality for all the joins in data foundation to detect the context. which are mainly detected by the CARDINALITIES.

Please find the below link with detailes expliation

http://scn.sap.com/docs/DOC-55523

Note: PLEASE mark the blog helpful & answered

[former_member540424]

Hi,

Cardinality settings in the universe are not necessary and it doesn't make any difference. But, it helps to detect loops, context in the universe.

So, its better to define the cardinality for the joins as a best practice.

All the best

[Former Member]

Hi,

Its not mandatory to define cardinalities between two tables but is a Best practice to do so.

If you set the cardinalities, in the integrity check, BO tool will allow to show you join divergence or possible Traps in your structure and also it helps for any third person to see and understand the relationship.

If you don't set it, it is not going to impact in anything as the sql generated is not dependent on cardinalities which are set in Universe structure.

Thanks

Gaurav

[Former Member]

Thanks for you answer,

and In the case If there is no cardinality defined for a join, then there will never be a Question of Traps, Am I correct.

If we are not defining any cardinalities for the joins then we need to concentrate only on resolving Loops.

Please correct me If I'm wrong.........

[Former Member]

No,

If there is no cardinality defined that mean, BO tool i.e. Universe Design Tool, by itself will not be able to detect whether there could be possible trap or not.

traps depends on data and relationship between two tables.

Cardinalities in universe structure is just a representation of the relationship.

For eg. Lets say a trap is there A--<B--<C in a,b and c table.

When you set cardinality in universe, and with this structure , when you do integrity check, a possible divergence(trap) message will be given by universe.

If you dont set the cardinality , trap will still be there only that Universe toll will not be able to detect it during integrity check.

Results in both cases will be same that is TRAP.

I hope it is more clear now.

Thanks

Gaurav

[Former Member]

Thank you,

Gaurav

It is clear to me now,

and one more thing making me confused over here is, In my project they have not set the cardinalities and the structure is in the form of chasm and Fan trap and as said by you, though uinverse tool does not recognise it as error, It will be error only na.

and as usually traps will result in more rows other than what expected...........

[Former Member]

Hi,

Yes if the data and joins are created with such relationships, there will be trap and data will be inflated i.e. more than expected.

You can refer below blog for more detail:

Business Objects: Chasm Trap

[Former Member]

Hello

if the cardinalities are not set , we can not detect the loops, so set cardinalities then find the loops and resolve the loops.

[Former Member]

Hi Gaurav,

As you said there is not impact on universe whether we define cardinalities or not,So what is the use of cardinalities? Why Some Universe Designer define the cardinalities ?

Ankit