In which layer we need to give the joining and relationship at FM

[inu]

Hi sir,

I attended one interview, where they asked regarding joining, i answered, generally we join two table at Database layer.
me:DB layer(joining tables),
      Business layer(done all the requirements as per client),
      presentation layer(short cuts of all items in Business layer)

interviewer:suppose only two tables tab1, tab2. you joined two tables at Db layer as inner join. published the package. again requirement came where we need to give outer join, then where should be updated.

me: in this situation i will join two table at business layer with outer join.

interview: they didnot accept my response, and said then what about inner join.

me: ok, i will create one more business layer.

interviewer: how many business layer you will create , if 10s of requirements are there, you will create that many layers.

me: i said, yes, then nothing said. they said to me only two tables are there, think ;;;;

So i want to know , actually where we need to give the joining ,, tell me  guys.. it's irritating to me .. what should be the answer.



Warm Regards
Inam

[MFGF]

Hi sir,

I attended one interview, where they asked regarding joining, i answered, generally we join two table at Database layer.
me:DB layer(joining tables),
      Business layer(done all the requirements as per client),
      presentation layer(short cuts of all items in Business layer)

interviewer:suppose only two tables tab1, tab2. you joined two tables at Db layer as inner join. published the package. again requirement came where we need to give outer join, then where should be updated.

me: in this situation i will join two table at business layer with outer join.

interview: they didnot accept my response, and said then what about inner join.

me: ok, i will create one more business layer.

interviewer: how many business layer you will create , if 10s of requirements are there, you will create that many layers.

me: i said, yes, then nothing said. they said to me only two tables are there, think ;;;;

So i want to know , actually where we need to give the joining ,, tell me  guys.. it's irritating to me .. what should be the answer.



Warm Regards
Inam

It's not clear from your question whether the requirement is to retain the inner join behaviour in some instances and to use outer join in others, or whether the requirement is always to deliver outer join behaviour? Regardless, I would implement the joins in the foundation (database) layer.

MF.

[inu]

It's not clear from your question whether the requirement is to retain the inner join behaviour in some instances and to use outer join in others, or whether the requirement is always to deliver outer join behaviour? Regardless, I would implement the joins in the foundation (database) layer.

MF.

interviewer said only two tables are there
yes, 1st requirement needs to use inner join. and second outer join...both can be used as per requirement....

[Robl]

Firstly, if you need an outer join then someone has done a shoddy job of building a data warehouse. :)

If the model needed an Inner join and an Outer join between the same two tables then you would need either aliases of one or both tables, or create a second namespace instance of the DB layer.

There are a few join strategies around.
The key point though, is that you need to alias your tables before you join them to prevent any circular join paths.
You can create multiple instance of the same table in the database layer or you can create a 1:1 database layer then alias the tables in a join layer.
Much depends on the quality (or not) of the underlying database schema as to how much replication and aliasing and shared joins you'll need.
A good quality DB would just need the shared dims to be aliased and shouldn't need any outer joins.

If you're using the FM model as a source for a dimensional cube then the joins NEED to be in the DB layer or it just doesn't work.

[bdbits]

You may want outer joins. For example, you have a complex warehouse with highly conformed dimensions, but some of the fact tables may or may not have rows for a particular level in a dimension but you still want to show all of the dimension levels when reporting.

I have also built Transformer cubes off of existing models that had joins in the business layer. It worked fine, but other cubing technologies may have stricter requirements.

Clearly best practice is to put them in the database layer for many reasons, but sometimes you have to work with less than ideal models.

[inu]

Firstly, if you need an outer join then someone has done a shoddy job of building a data warehouse. :)

If the model needed an Inner join and an Outer join between the same two tables then you would need either aliases of one or both tables, or create a second namespace instance of the DB layer.

There are a few join strategies around.
The key point though, is that you need to alias your tables before you join them to prevent any circular join paths.
You can create multiple instance of the same table in the database layer or you can create a 1:1 database layer then alias the tables in a join layer.
Much depends on the quality (or not) of the underlying database schema as to how much replication and aliasing and shared joins you'll need.
A good quality DB would just need the shared dims to be aliased and shouldn't need any outer joins.

If you're using the FM model as a source for a dimensional cube then the joins NEED to be in the DB layer or it just doesn't work.

------------------------------------------------------------------------------------------------
Thanks Robl,
You are saying joining should be given at DB layer, if second requirement comes , then create alias of the tables...
so , if again more requirements are coming from clients regarding cardinality,,, then what needs to be done...
in this case, i think alias tables will increase the size of the project.
Cant we create join at presentation layer where shotcuts are created..????

Regards
Inam