why we give joins in report studio, if join has already been given at FM level

[inu]

Hi
Please elaborate with example , i m little bit confused, when and why we need to give the joining at report studio?. if it has already been in framework manager.

Thanks
Inam

[rih9000]

You don't need to specify joins in Report Studio, if they are already defined in FM  8)

[navissar]

FM allows an administrator to set up a unified model for the entire organisation. Joins in the data model are created, ideally, to best suite the query needs. Nonetheless, any solution that is generalised might not be sufficient for a specific case. It makes little sense to create relations in the model which only serve on one occasion, so the ability to define relationships has been given to the report author.
Consider the following example:
Each salesperson works in one store of a chain of stores. Some fact tables, such as sales target, only have sales person, whereas some fact tables are on branch level (incoming customers, for instance). Some facts have both - so for example a sales fact might have a key for store and a key for salesperson. The database keeps the data in two separate tables (it is possible to keep them in one table and use determinants for higher than grain joins, but over large data sets this method will infringe on report performance. Also, this type of modelling caters for an employee moving from one branch to the other).
Now, the sales fact is modelled in star scheme: the fact is joined to both the salesperson and the branch tables. However, each salesperson also has a branch key for the branch they're currently working in (this is type 1 slowly changing dimension).
When modelling star schema, dims are related to facts and that's that.
Now, you wish to pull a list which shows the current employees per branch. Using the existing data model, you'd have to go through the fact, making the query quite large (if your fact has a lot of rows, your joins will take longer). Also, going through the fact will fetch historic data: if Peter worked at the Atlantic branch until last week, but then moved to the Boston branch, joining through the fact will have Peter under both. You could theoretically filter your fact for just today, but if Peter didn't work or sell today he wouldn't be there at all (assuming inner joins). So, it would make better sense to join the dims salesperson and branch directly. However, this is just one report and doesn't merit changing the entire framework. So, report studio join it is.
Another case is when you would usually need an inner join (you only wish to see products that had been sold over the report period, for example), but for one report the request is to see all products whether they've been sold or not (it's easier to float up unsold products that way than by elimination...). Instead of modelling the whole thing twice, you could just have a join in the report itself.
Another example is when you wish to join the results of queries. For instance, over relational you're asked to show sales for this year in comparison with last year. You create one query filtered by this year, another filtered by last year, and join the results.
Creating in report joins is a strong tool which can allow a report author to give quality results with good performance for a specific need. Use it wisely.  :)

[Lynn]

That is a very good explanation!!