Outer Join Help 2

[138006]

Hi,

I want implement an outer join in Microstrategy 7.2 (Database is Oracle 8.1.7). I am simulated the problem in Microstrategy Tutorial.

In Microstrategy Tutorial Database, let us take three tables ORDER_FACT,LU_ORDER and LU_CUSTOMER.

Now my objective is to generate an SQL which will look like the following:

select a12.[CUSTOMER_ID] AS CUSTOMER_ID,
max(a13.[CUST_LAST_NAME]) AS CUST_LAST_NAME,
max(a13.[CUST_FIRST_NAME]) AS CUST_FIRST_NAME,
sum(a11.[QTY_SOLD]) AS WJXBFS1
from [ORDER_FACT] a11,
[LU_ORDER] a12,
[LU_CUSTOMER] a13
where a11.[ORDER_ID](+) = a12.[ORDER_ID] and
a12.[CUSTOMER_ID](+) = a13.[CUSTOMER_ID]
group by a12.[CUSTOMER_ID]


So it will give me all the customers irrespective of they have order against them or not.

I have tried this by creating a metric on QTY_SOLD and changing VLDB properties : Metric Join Type : Outer Join. But when I drag Customer and this newly created metric in a report, this is not giving me the intended result. It is creating the SQL as described above but WITHOUT the outer join.

How can I do this? Of course I have an attribute as Customer(already present in MS Tutorial) where join is defined between LU_CUSTOMER and LU_ORDER. Similarly I have another attribute called Order(already present in MS Tutorial) where join is defined between LU_ORDER and ORDER_FACT.

 

[chael]

You might want to get MicroStrategy to do this in 3 passes:

1st pass: select all instances from fact table where customer_id = 'x'

2nd pass: select all instances from fact table where order_id = 'y'

3rd pass: force outer join on the final pass (a VLDB setting).

Also, I assume Customer is a parent of Order. What happens when you put Order on the report?

Chael

 

[entaroadun]

The metric join setting doesn't affect the join used to calculate the metric. It defines the join used to consolidate separate metrics.

Instead, you need to set the attribute join settings in the report data options. For the Customer attribute, set the join setting to "Include all after applying filter" (or something like that).

 

[Z3]

Or, use VLDB setting "preserve lookup table blah blah blah".