Universe DEsigner is not getting correct Cardinality

[Former Member]

I have two tables which are in a One-to-One relationship. But when I join them in Universe, the designer is saying invalid cardinality and detecting that join as one-to-many. I even created a primary key on both the tables to make sure it's one-to-one. Still it's throwing warning message as invalid Cardniality. Any ideas? btw, I am in BEXI 3.1

[Former Member]

This thread is now marked as "assumed answered". If you feel it is not correct, please let us know.

[Former Member]

The universe cardinality detection algorithm goes by data in the tables, not by keys or anything else you might define at the database. It's one of the many reasons why folks generally do not use the automatic detection option but instead set cardinalities manually. If you have empty tables, or if the data in the tables is not representative of what will be in your production system... either of these situations will cause the detection process to fail (at worst) or give inconsistent results.

Suppose you have table A and B joined in your universe. Here's how the process works. It runs three select statements:

select count(*) from A

select count(*) from B

select count(*) from A, B where {join expression}

Suppose there is a one-many relationship from A to B. We expect to see a lower count from A, a higher count from B, and a higher count from A + B after the join. Based on that result, Designer will assign the cardinality. If A or B is empty, or if the data doesn't match well, then the results of those count queries can be off and the cardinality is set wrong.

Best to just set it based on your knowledge / expectations of the data and ignore the detect option altogether.

[Former Member]

Well, I am up for setting it Manually. But when I do a "Check Integrity" it throws up a message about invalid cardinality[ I am sure it is not a show stoper] and I can move it in production. But I just want to make sure this will not create problems for end users.

My Scenario is different than what you specified.

Table A: Primary_key,column2,column3

Table B: Primary_key,column4,column5

There is Ton of data in those tables. primary key being the same in both tables, I thought Designer is intelligent enough to understand the relation. instead it comes up with one-to-many

I could just add these two into one single table [but that's not the solution I am looking for, for many valid reasons].

Thanks,

[amitrathi239]

Cardinality does not impact data.but again you are joining two tables and designer detect 1:M as you have to check your data in the table.there may be multiple instance in one table for one record.

[Former Member]

There is a reason the cardinality check is separate from the rest of the items in the overall Integrity Check window. Simply skip that step when you run that process and you are fine. Cardinality settings do not impact users at all. And if the detection process gets the answer wrong the first time, it will continue to get it wrong for the same reason.