Creating left joins and cardinalities !!!

[former_member672670]

Hie guys,

I am designing a universe based on a MySQL query in DEV to create a report. There's quite a few left joins in the SQL one after the other. My question is - How do I know which table to place the left join on. Is it the table after the Outer Join statement in the SQL? Also, since the data in DEV is not complete, how do i define the cardinalities for my joins? Do i have to even define the cardinalities? Thanks.

[Former Member · Accepted solution]

Defining cardinalities

Do not use DETECT CARDINALITIES. It is an inaccurate algorithm.

Use the knowledge within your organisation - a data schema, a person, etc. that knows how the cardinalities should be.

Cardinalities are there to make the DETECT CONTEXTS functionality work as well as giving a visual support for the designer.

You can define cardinalities without any data at all, just double click on the join and set them in the dialog box, along with the outer joins. Be warned though, you will need to set the outer join before defining a complex join (e.g. ProductID = ProductID and SalesDate between ProductStartDate and ProductEndDate)

[former_member672670 · Accepted solution]

On what basis do i define the 1 to Many/ Many to 1 relationships between the tables? The data is incomplete for most of the tables, but I can see repeating column values in some tables.

[Former Member · Accepted solution]

A lot of it is basic understanding of your data model

Let's consider an organisational structure for a retailer in the UK as an example.

It may have Regions, Counties, Cities and Branches as the four points in its hierarchy.

For example, a Branch may be Market Street.

This branch is in the city of Manchester, which is in the county of Greater Manchester , which is in the North West England region

If you have four tables that relate to these then you would have the join structure:

Region -- 1:many --< County -- 1:many --< City -- 1:many --< Branch

A branch would receive deliveries and sell the products in those deliveries.

So one Branch would have many sales (1:many) and many deliveries

Similarly you  would need a product table and products don't belong to a branch but are delivered to many different branches.

Have a think about your data and try and understand what it does. Never be afraid of asking for help on this if you haven't designed the database. After all, your design decisions didn't go into the database, somebody else's did. If there's a data schema somewhere, use it.

If you have any specific examples, then we can work through them here.

[Former Member]

Hi Jeevan,

Though you dont have data in the database tables; however, you could set the left outer joins on the basis of your understanding of data model and assumption on kind of data which is about to flow in.

Regards,

Yuvraj

[Former Member]

I will suggest to go through the IDT tutorials  below

outer join : http://scn.sap.com/docs/DOC-22058

cardinalities :

auto :http://scn.sap.com/docs/DOC-22058

manual : http://scn.sap.com/docs/DOC-22033

If you still have questions after going through this please post ..

[Former Member]

Hi Jeevan,

Please open pdf available on below link.

http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41_info_design_tool_en.pdf

Refere chapter 10 : Working with Data Foundations.

Your most of the questions will be clear.

Please let us know in case of any doubt.

Regards,

Chetan