BusinessObjects Board

Cardinality

Hi,

I am new to Universe Design. I am wondering how cardinality setting is important? Would I get wrong result if I don’t set the cardinality or wrong cardinality? Just wondering…

Thanks,
-rk


rit372002 :us: (BOB member since 2006-11-02)

Cardinality settings don’t typically affect the generation of SQL. For example, your join logic does not look like:

table1.id (one) = table2.id (many)

But cardinality settings are used by the context and alias and loop resolution options within Designer, and also for seperating out SQL for the “Multiple SQL Statements For Each Measure” option.

In short, you can make a perfectly valid universe without a single cardinality setting. But it’s better if you set them all; for one thing, the research required to do so will help you understand the relationships between your tables better.


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

Does the Manual and Automatic Cardinality setting differ.

When do you have to set it manually instead of automatic.


prasad.mlpn (BOB member since 2007-05-28)

As best practice you should set the join cardinality manually according to the data model you have. You do have a data model, right? :wink:


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

is it necessary that cardinality for universe should be same as cardanlity of data model.


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

Yes. And I think that’s why we have a data model.


Jansi :india: (BOB member since 2008-05-12)

I agree to what jansi says. We need to have cardinality similar ot the data model. BUT, if every universe developer should set the manual cardinality based on the data model, then why have a automatically detect cardinality ? Im trying to understnad when and why should BO detect cardinality that is DIFFERENT from the data base ?

thanks,
sija


sijasethu :india: (BOB member since 2009-01-07)

In best practice its good to have same cardnality in universe as data model, but some times we may need to change according to our requirements.

comments please.


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

Your data model should be designed for your requirement and so your data model has to be followed while designing the universe.


Jansi :india: (BOB member since 2008-05-12)

jansi, its not necessary to join tables in universe as they are joined in data model.


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

Are you concluding something? Or are you asking whether it is necessary to join the tables as per the data model?

If it is latter, I would follow the data model always.


Jansi :india: (BOB member since 2008-05-12)

just asking.


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

Follow the data model.


Jansi :india: (BOB member since 2008-05-12)

If every universe developer should set the manual cardinality based on the data model, then why have a automatically detect cardinality ? Im trying to understnad when and why should BO detect cardinality that is DIFFERENT from the data base ?

:hb:


sijasethu :india: (BOB member since 2009-01-07)

i have the same concern too. If we have to make same cardnality and same join based on data model then what is the need of a universe designer.


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

You could have read ‘Learn why it is useful to set cardinalities manually’ from the Designer help.


Jansi :india: (BOB member since 2008-05-12)

Ok guys!!!

‘Manually setting up the cardinality’ vs ‘Automatically detect cardinality’

Lets suppose, there are two tables named ‘CUSTOMERS’ and ‘ORDERS’. These tables are joined on ‘CUSTOMER_ID’. One customer can have multiple orders and it is a 1:N relation.

Lets suppose both tables have some data in them. The way the data is stored in both tables are each customer has only one order. If you automatically detect the cardinality, then you will end up seeing a 1:1 relation. Because BO detects the cardinality based on the data and join condition between both tables.

But in real, we know that their cardinality is wrong … it is 1:N… So, you end up manually changing or setting up the cardinality.

If you dont have data in those tables and you detect cardinality… it will return unknown cardinality. To avoid these situations we setup cardinality manually (represetned in data model).

Hope you are now clear about the need of this best practice…

Let me know, if you are not clear… I will try my best agian.

Cheers,
Praneeth. :slight_smile:


pranpad (BOB member since 2008-01-24)

That’s one of Designer features that confuses many universe developers. Just stop relying on it, stop using, set the cardinalities manually based on your knowledge of a data model and you will be perfectly fine. :slight_smile:


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

Automatic detection of cardinalities - don’t bother! If you’re building a model in dev with sample data, your detection algorithm will be worthless. If you’re detecting over a live data warehouse, sit back and have several cups of tea while it hopefully gets it right!

Your job as a universe Designer is to make life easy for the end user, not for yourself. That’s why universe Designers are typically paid more than reporters. Your job is to understand the data model and create a universe over the model to support the users’ requirements for reporting.

Always follow the data model? Not necessarily. Usually, yes. The main difference I would say is when you encounter a one-to-one join in the data model. If you detect contexts then you should set it as a one-to-many join in the direction that suits your context detection. Bear in mind that context detection basically chases the manys out from the fact tables and you will know which way to set your cardinalities.

Other areas that will vary from the data model included alias usage (date table being used for many purposes) and chasm trap resolution (measures on higher granularity tables - e.g. order line item retail values and and order header cost values) resulting in aliases for other purposes. Consider also setups for handling aggregates as well as short-cut joins and stub/self-restricting joins.

" i have the same concern too. If we have to make same cardnality and same join based on data model then what is the need of a universe designer."

simple to enforce security and hide data base complexity from end user(by creating classes)


kbharadbo (BOB member since 2009-05-19)