I am not understanding the role of Cardinality,
As far as i know Cardinality is a property of a join that describes how many rows in one table match rows in another table and The cardinality of a join does not have a role in the SQL generated when We run a query.
And i also read somewhere that Designer uses cardinalities to determine contexts and valid query paths.
So i am not getting what role cardinality plays here because its we who create the context and add the joins to the context, what designer does over here??
I have two questions in mind
1)How does the designer use Cardinality to determine contexts and valid query paths. What relation does cardinality have with context?
2)If i give a wrong cardinality there will be no effect on my SQL created so why is this feature?
Use of cardinality is to understand the business purpose and to better understand the data model and it helps the other developers after you to understand.
if you specify it incorrectly its not going to affect the SQL or the report.
Correct whatever you told does make sense and even i was having the same impression but when i was looking to designer guide it is written that âDesigner uses cardinalities to determine contexts and valid query pathsâ
You set your cardinalities to allow Designer to know which context(s) joins should belong to. When detecting contexts, Designer finds all tables that have only many joins going into them (typically all your fact tables in a multi-star schema). The algorithm then âchases outâ the many joins along paths to find all the joins it needs for each context
So A -< B -< C >- D -- F
would cause Designer to initially find tables C and E as the starting points.
The context for table C would find the joins C-D, C-B and B-A, while the context for table E would find the joins E-F and E-D. Thatâs because youâve set the cardinalities correctly; Designer doesnât know for itself what the cardinalities are and detect cardinalities typically gets a fair few wrong so donât use that, use the schema.
I am little bit new to universe designing so there may be i ask some stupid questions please bare with me
Actually i am creating a join and then manually(Right click add to context) adding it into the previously created contexts based on my model.
So In this scenario does still cardinality plays a role
Besides all what was said above, setting cardinalities right in a universe helps universe developer to visually check the universe and to find potential chasm and/or fan traps.
Without cardinalities being set, every table looks more or less the same and itâs difficult if not even impossible at all to tell where there could be a chasm/fan trap.
Actually i have four fact tables and multiple dimension tables and corresponding to ever fact table i have a context for example if i have a daily fact table i have a context called day and i add all those joins from the daily table to any dimension table in the context day.
Thatâs how i am currently working, i dont know whether its a correct approach or not but seems to be working.
So is it that i am manually doing all this things instead of relying on designer i am not able to find the importance of cardinality??
As Mark has said, cardinalities are needed for the generation of the relevant Contexts. So why donât you have Designer auto-generate the Contexts rather than doing it manually; itâs far quicker and more reliable. Others may disagree, but in 15 years I have very, very rarely (if ever) needed to manually modify / generate Contexts. Auto-generation should be your default.
So what i understood was cardinality plays a role in identifying fan traps and chasm traps by improving the visualization.
Cardinality also play a role in suggesting the context if you are using the designers default feature of context suggestion.
Please rectify me if i am wrong somewhere and please do add if you think cardinality also play any other role
Iâm being being a bit pedantic, but chasm and fan traps are identified through an analysis of the cardinalities that have been defined when the joins are created, not through visualisation.
Also, I would say that âcontext suggestionâ is an understatement; âcontext definitionâ being more appropriate. i.e. you would have to have very particular (and rare) reasons for changing the contexts from those automatically defined.
[quote:377b1becd7=âgu.rashmiâ]A is joined to B
A is joined to D
B is joined to C
C is joined to D
I think that as the dim table A is used twice, I should create an alias.
[/quote]
When you draw it on a paper you could see that every table is used twice (every table is joined to 2 other tables).
So whether some table is aliased or more contexts are needed depends on the purpose of the tables and on the cardinalities of joins between them.
From what you wrote (A, B, C are dimension tables) - it looks to me that any of these tables could be aliased. But it really depends on the purpose of tables. Does one of the A, B, C tables play more ârolesâ? Like a date dimension can play 1st role as an order date and 2nd role as a shipment date.
[quote:377b1becd7=âgu.rashmiâ]Also in a context, do we need to have min 2 joins atleast as it is collection of joins?
[/quote]
You can have a context with just one join.
(BOB member since 2010-12-10)
(BOB member since 2003-11-27)
(BOB member since 2002-09-13)