Is this a Fan Trap?

building semantic layer
1

Hello,
I have a three table quene, connect and call.I made the following join and made check box mark in cardinality without setting cardinality.This is Inner join.I just want to know is this is a Fan Trap Problem?I am in initial stage of building a universe.

queuecall.ConnectTableID=connect.ID
connect.CallTableID=call.ID

Thanks in Advance.
Pooja

ktm :us: (BOB member since 2008-11-12)

2

You might want to read this post and some of the replies that follow.

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

3

Hi Pooja,

In best practice never put check box mark for cardinality, set them always manually.

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

4

Huh? The check box needs to be ticked to allow you to set the cardinality!

5

:yesnod: 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 :india: (BOB member since 2007-12-11)

6

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 :us: (BOB member since 2008-11-12)

7

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 :slovakia: (BOB member since 2003-11-27)

8

We have mainly measure from quene and call.
Thanks
Pooja

ktm :us: (BOB member since 2008-11-12)

9

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 :slovakia: (BOB member since 2003-11-27)

10

Thanks for Your Help Marek,Please find the snap Shot .

Thank You again.

Pooja

ktm :us: (BOB member since 2008-11-12)

11

Hi Pooja,

There is none :? Maybe you forgot to attach it?

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

12

Hello marek,
Please find the attached snap shot of my universe.
Thanks In Advance.
Pooja

ktm :us: (BOB member since 2008-11-12)

13

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 :slovakia: (BOB member since 2003-11-27)

14

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 :us: (BOB member since 2008-11-12)

15

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.

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:

  1. customers and orders (1 customer made/has N orders. And 1 order belongs to one and only one customer)
  2. customers and invoices (1 customer received N invoices. And 1 invoice was sent to one and only one customer)
  3. subscribers and calls (1 subscriber made N calls. And 1 call was made by one and only one subscriber)
  4. employees and salary details (1 employee had N salaries. And 1 salary was sent to (belongs to) one and only one employee)
  5. countries and cities (1 country has many (N) cities and 1 city belongs to one and only one country)

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.

You have more options:

  • you can ask for the data model the person that knows it or that designed it
  • you can guess what the cardinalities are based on the knowledge of the subject area. Like if you have invoices and invoice lines tables then you should know that 1 invoice has 1 or more invoice lines, so the cardinality between invoices and invoice lines tables is 1:N
  • you can try automatic cardinalities detection in Designer. Sometimes it gives correct results :wink: However, I don’t rely on this automatic process. It’s better to manually set the cardinalities based on your own knowledge of the data model based on which the universe is being built. But if you lack the knowledge of the data model then this automatic detection is better than nothing. However, be aware that it can deceive you :slight_smile:

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

16

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 :uk: (BOB member since 2009-10-16)

17

inselaffe,

Pooja mentioned in of their posts that they use a transactional database, no DWH there.

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

18

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 :us: (BOB member since 2008-11-12)

19

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 :slovakia: (BOB member since 2003-11-27)

20

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 :us: (BOB member since 2008-11-12)