Dynamic JOIN Path

[former_member190855]

Hi all

I have the following requirement - the universe needs to figure out the Join path based on a response to WebI prompt.

The promot can have two values: As-Is or As-Was

If chosen As-Is the fact table needs to join with the As-Is Start and End date columns from the dimension table.

If chosen As-Was the fact table needs to join with the As-was Start and End date columns from the dimension table.

Is this possible to achieve? May be by writing a case statement along with @prompt in the JOIN statement of the table?

Any ideas or suggestions?

I am in XI 3 environment.

Thanks in advance

Runali

[former_member185403 · Accepted solution]

Hi Runali,

Please check whether below approach works for you:

Say I have 4 columns in our dimension table “Dim1”

‘AS-IS Start’, ‘AS-IS End’, ‘AS-Was Start’, ‘AS-Was End’.

1. Create alias of the dimension table (Say, “Alias1”).
2. Create 2 joins:

             Join 1 involves ‘AS-IS Start’, ‘AS-IS End’ of “Dim1” and Fact Table. 

Join 2 involves ‘AS-Was Start’, ‘AS-Was End’ of “Alias1” and Fact Table.

3. Create 2 Contexts named AS-IS and AS-Was:

Context “AS-IS” involves “Join 1”.

Context “AS-Was” involves “Join 2”.

Context	Join	Join Columns	Table
“AS-IS”	“Join 1”	‘AS-IS Start’, ‘AS-IS End’ of “Dim1” and Fact Table	“Dim1”
“AS-Was”	“Join 2”	‘AS-Was Start’, ‘AS-Was End’ of “Alias1” and Fact Table	“Alias1”

When the WebI report is run and prompts for contexts, you can choose among the 2 contexts Context “AS-IS” and “AS-Was”.

Joins would apply based on appropriate context.

Regards,

[former_member190855 · Accepted solution]

THanks everyone for your reply. I took a similar approach to what Krishnendu suggested and used with @prompt.

THanks again.

[former_member4998]

Try this.

Need to create a derive table.

Select measure from table_IS

Where ‘IS’ = Prompt(IS/WAS)

Union all

Select measure from table_was

Where ‘was’ = Prompt(IS/WAS)

  And create a measure using this derive table

below is the sample code:

Declare

@a as varchar(4)

set @a='8-10'

select a1.ACT_ID, c1 from(

select  count(cs.ACT_ID) as ACT_ID,'2-4' as c1   FROM FACT TABLE cs

where cs.ACT_ID between 200000 and 400000

and '2-4' = @a

union all

Select count(cs.ACT_ID) as ACT_ID,'8-10' as c1

FROM FACT TABLE cs where cs.ACT_ID between 800000 and 1000000

and '8-10' = @a)a1

[Former Member]

Hi Runali,

I just tried with @Variable in Join condition and it is working fine.

This is the join condition I wrote between emp table and emp table alias(emp2).

(Case WHEN @Variable('Join Column')='EMPNO' AND EMP.EMPNO=EMP2.EMPNO THEN 1 WHEN @Variable('Join Column')='MGR' AND EMP.EMPNO=EMP2.MGR THEN 1 ELSE 0 END)=1

In the above what I'm trying to do is if I input "Join Column" value as EMPNO then the First condition will be true and it will execute the query based on emp.empno=emp2.empno

If I select the value as MGR then it will execute the join as Emp.empno=Emp2.MGR

I tested and it is working as expected.  In the above example I considered same table twice by creating alias.

I hope this helps.

Sampath

[former_member201488]

This is certainly the approach that was taught in the XI R3.1 Advanced Universe Design course and has worked successfully for me in the past.