“How do we push the conforming dimension join to the database and how do we push the conforming dimension join to the BI Server(how do we control this behavior)?”
First will try to answer "How do we model the same repository above to achieve the conforming dimension join to happen in the BI Server memory?"
Steps.
1. Create Physical and BMM layer joins as below between CHANNELS,SALES and COSTS tables.
2. Complete model upto Presentation layer and create a report with the below combinations.
3. With this join condition below is the query which gets executed and join happens at BI Server memory.
Here trying to answer the original question which Venkat has asked how do we force so that BI server execute join condition query at database level.
For this we just need to change some of the default properties of database in Physical layer of RPD, where default features will be set for each database under database properties "Features" tab.
Here are the 2 default features which we need to uncheck,
PERF_PREFER_MINIMAL_WITH_USAGE,PERF_PREFER_INTERNAL_STITCH_JOIN
With the above changes if we execute the same report again below query will be executed on database.
-------------------- Sending query to database named SH (id: <<15392>>):
WITH
SAWITH0 AS (select sum(T170.UNIT_COST) as c1,
T162.CHANNEL_DESC as c2
from
CHANNELS T162,
COSTS T170
where ( T162.CHANNEL_ID = T170.CHANNEL_ID )
group by T162.CHANNEL_DESC),
SAWITH1 AS (select sum(T251.QUANTITY_SOLD) as c1,
T162.CHANNEL_DESC as c2
from
CHANNELS T162,
SALES T251
where ( T162.CHANNEL_ID = T251.CHANNEL_ID )
group by T162.CHANNEL_DESC)
select distinct case when SAWITH1.c2 is not null then SAWITH1.c2 when SAWITH0.c2 is not null then SAWITH0.c2 end as c1,
SAWITH0.c1 as c2,
SAWITH1.c1 as c3
from
SAWITH0 full outer join SAWITH1 On SAWITH0.c2 = SAWITH1.c2
order by c1