You might want to read this post and some of the replies that follow.
Dave Rathbun (BOB member since 2002-06-06)
You might want to read this post and some of the replies that follow.
Dave Rathbun (BOB member since 2002-06-06)
Hi Pooja,
In best practice never put check box mark for cardinality, set them always manually.
Rakesh_K (BOB member since 2007-12-11)
Huh? The check box needs to be ticked to allow you to set the cardinality!
I was talking about to setting cardinality manually and automatic.
Its better to set them manually.
Pooja,
In there 2 tables which one is dimension and which is fact table.
Rakesh_K (BOB member since 2007-12-11)
We don’t have fact and Dimension Table.This is Transactional Database.
I just want to know do we have fan trap in this join or we are OK with it.
Thanks
Pooja
ktm (BOB member since 2008-11-12)
I just want to know do we have fan trap in this join or we are OK with it.
Potentially, you may have a fan trap. But no one will tell you for sure without knowing more about your data model, cardinalities of joins, and where measures are coming from.
Marek Chladny (BOB member since 2003-11-27)
We have mainly measure from quene and call.
Thanks
Pooja
ktm (BOB member since 2008-11-12)
It’s really hard to help when you provide so little information.
But I hazard a guess - yes, you may have a fan trap so you should better check whether you really do have it and if yes then fix it.
update: Depending on the cardinalities of joins between the 3 tables, it’s possible that you may have fan trap but also you may have a chasm trap.
Marek Chladny (BOB member since 2003-11-27)
Thanks for Your Help Marek,Please find the snap Shot .
Thank You again.
Pooja
ktm (BOB member since 2008-11-12)
Hi Pooja,
Please find the snap Shot.
There is none :? Maybe you forgot to attach it?
Marek Chladny (BOB member since 2003-11-27)
Hello marek,
Please find the attached snap shot of my universe.
Thanks In Advance.
Pooja
ktm (BOB member since 2008-11-12)
OK, I repeat again in different words because maybe I was not clear before:
You can’t tell whether you have a fan trap or chasm trap or you don’t have a trap at all if you don’t know/display the cardinalities of joins.
Cardinalities of joins is the base criterion that will tell you - “yes, you may have a fan trap”.
Your attached picture does not show any cardinalities so it’s impossible to say for sure anything.
It’s also important to know where measures and dimensions come from.
Marek Chladny (BOB member since 2003-11-27)
Is it necessary to define cardinality between the tables?because it is INNER JOIN between the tables and mostly the dimension and measures are from quene table.how do i know the cardinality between the tables?Because i don’t have a Data model with me.?
Thanks
Pooja
ktm (BOB member since 2008-11-12)
Is it necessary to define cardinality between the tables?
No, it’s not necessary. The universe can work without them. However, you will not be sure that the universe gives correct results.
On the other hand, setting all cardinalities is considered a good practice and doing so helps the universe developer in a big way. If nothing else then at least it helps to identify possible traps.
…because it is INNER JOIN between the tables…
Whether a join is an inner join or an outer (left or right) join just tells you only a little about the cardinality.
To avoid any confusing definitions, I will give an example of what cardinality of a join is:
Imagine 2 tables - Invoices and Invoice_Lines. The Invoices table has a unique ID, it’s inv_id column. There is only one row per each invoice in the Invoices table. And every invoice is identified by its own unique inv_id.
Now, in the Invoice_Lines table there may be more rows per each invoice. Every row in this table represents an individual line/item from an invoice. You may have an invoice inv_id=1 with only 1 line and you may have an invoice inv_id=2 with 100 lines. That many rows (1 for the first invoice and 100 for the second invoice) are in the Invoice_Items table.
The Invoices and Invoice_Lines tables can be joined via inv_id column:
Invoices.inv_id = Invoice_Lines.inv_id
You can set the join as an inner join - it means that every invoice has at least one record in the Invoice_Items table. Or you can set the join as an outer join if there are also invoices that don’t have any invoice lines/items.
And now cardinality:
The cardinality of the join between these 2 tables is 1:N (read “1 to N”) and it means that every single row from the Invoices table has at least one and potentially N (where N is any number > 1) rows in the Invoice_Items table. This is very important to understand that 1 row from Invoices can have 1 or N rows in Invoice_Lines. When you take it from the other end, then every row from the Invoice_Lines table has 1 and only one row in the Invoices table.
So the cardinality of this join needs to be set as:
Invoices ---- 1:N ---- Invoice_Lines
You can visually represent it also like this
Invoices ----< Invoice_Lines
where the symbol “<” is at the N side of the join.
So setting a cardinality of a join in Designer means setting which table is at the “1” side of the 1:N join and which table is at the “N” side of the join.
Other typical examples of 1:N joins between 2 tables can be:
To make it a little bit more complicated, a join does not necessarily need to have the 1:N cardinality. You may have 2 tables where the join between them has a 1:1 cardinality. This means that 1 row from the first table has only 1 corresponding row in the second table and vice versa - 1 row from the second table has only 1 corresponding row in the first table.
how do i know the cardinality between the tables?Because i don’t have a Data model with me.?
You have more options:
Marek Chladny (BOB member since 2003-11-27)
there is one simple answer to the ‘problem’, and rakesh_k pointd you to it. the other way is: (something BO software will never teach ) learn:
-how is your dwh setup
-who wants your repors
-why (which helps with who…)
-MOST IMPORTANTLY: DO THEY NEED THE DATA?
google xir2 for c howson. else go back to dimensions.
not being …
good luck
inselaffe
inselaffe (BOB member since 2009-10-16)
inselaffe,
Pooja mentioned in of their posts that they use a transactional database, no DWH there.
Marek Chladny (BOB member since 2003-11-27)
Hello Marek,
I am getting “Unknown Cardinality”( DSG0003), when i tried to do Through the Designer for one table and for another one it is setted to be 1,N.
So, i decided to make one to one.Now my cardinality is
1,1 1,N
1,1 1,1
Thanks
Pooja
ktm (BOB member since 2008-11-12)
I am getting “Unknown Cardinality”( DSG0003), when i tried to do Through the Designer.
Did you try automatic cardinality detection?
Well, it does not work all the time. Sometimes the algorithm behind the detection process can’t decide what the cardinality of a join is. In such case you need to set the cardinality manually.
Marek Chladny (BOB member since 2003-11-27)
Yes, I tried to automatic cardinality detection and now i have set the cardinality between these tables as and i don’t think we have any join Problem here.
1)One to ONe (queuecall to Connect)
2)Many to one(connect to call)
Thanks
Pooja
ktm (BOB member since 2008-11-12)
Hi,
If the Connect table acts as a shared dimension for Call and Queuecall tables then you may have a chasm trap. The trap occurs when you have this scenario:
table 1 ---- N:1 ---- shared dimension ---- 1:N ---- table 2
In this case you need to create 2 context:
But as I said, it’s hard to suggest anything without knowing more details.
Check also this DEsigner’s FAQ about a chasm trap:
What is a chasm trap, why is it a problem, and how do I resolve it? Business Objects describes it best at this web site: http://tips.businessobjects.com/tips/unvdes/tip/ut001/ut001.htm The Chasm trap occurs when two “many to one” joins converge on a single table. It is a one - many - one relationship. You will get incorrect results when the following circumstances exist: A “many to one to many relationship” exists among three tables in the universe structure. The query includes obje…
Marek Chladny (BOB member since 2003-11-27)