fact table and dimention table how to make a outer join?

[lincon]

I have a order master table named order and a fact table named order_sale,I want get the data like following:
database is mssql2000
select t1.orderID,t2.sale_qty from order t1 right outer join sale_qty t2 on t1.orderID=t2.orderID

I make a attribute order and a metric sale'qty,I change the attribute join type into outer join but still not get the sql statement.
Anyone tell me how to do it,thanks!

 

[entaroadun]

Do you mean

select t1.orderID,t2.sale_qty from order t1 left outer join order_sale t2 on t1.orderID=t2.orderID

instead?

There's 3 different outer join options. Which are you using?

 

[lincon]

not
select t1.orderID,t2.sale_qty from order t1 left outer join order_sale t2 on t1.orderID=t2.orderID

is
select t1.orderID,t2.sale_qty from order t1 right outer join order_sale t2 on t1.orderID=t2.orderID

I want get the all fact table data include not in dimention.

I choose each type but all not.
thanks!

 

[entaroadun]

Microstrategy doesn't directly support right outer joins from attribute to fact.

Try creating a view that has the right outer join within it and model the view in.

 

[lincon]

thanks,but change the right join into left join as following:

select t2.orderID,t1.sale_qty from order_sale t1 left outer join order t2 on t1.orderID=t2.orderID

Can Mstr support?

 

[sunnyphoenixier]

I would suggest you go to your report/metric VLDB property/Joins/. Select the option of preserve all lookup table elements. Then you will be able to keep all lookup table values.

Phoenixier

 

[entaroadun]

MSTR only has options to preserve elements from the attribute side, not the fact side. The issue is not left join vs. right join.

You will need to use a view. Good DW design shouldn't permit facts to exist without corresponding attributes though...

 

[lincon]

thanks all!

above only i give a example,now give my actual table struct,i don't know design the dimention.

t_db(id,item_id,from_cust_id,to_cust_id,pid)
t_so(tid,id,item_id,from_cust_id,to_cust_id,qty)
t_from_Customer(cust_id,cust_desc)
t_to_Customer(cust_id,cust_desc)
t_Product(item_id,item_desc)

now i want get the data like sql:

select t1.item_desc,t2.cus_desc from_customer,t5.cust_desc to_customer,t4.qty from t_Product t1,t_from_Customer t2,t_db t3,t_so t4,t_to_Customer t5 where t1.item_id=t4.item_id and t3.id=t4.id and t2.cust_id=t3.from_cust_id and t5.cust_id=t4.to_cust_id

union

select t1.item_desc,t2.cus_desc from_customer,t5.cust_desc to_customer,t4.qty from t_Product t1,t_from_Customer t2,t_so t4,t_to_Customer t5 where t1.item_id=t4.item_id and t2.cust_id=t4.from_cust_id and t5.cust_id=t4.to_cust_id

 

[entaroadun]

What are the roles of t_db and t_so?

 

[lincon]

t_db and t_so are fact table,t_db save the agent transaction each other, t_so save sales data agent sale the customer

 

[entaroadun]

OK... Why does the first pass have the two fact tables joining each other on ID?

 

[lincon]

in t_so if id is not missing(value is null) t_so.from_cust_id can find a match with t_db.to_cust_id by t_so.id=t_db.id,so the sale path is t_db.from_cust_id to t_db.to_cust_id to t_so.to_cust_id.

if t_so.id is null the sale path is t_so.from_cust_id to t_so.to_cust_id.

 

[entaroadun]

So, if id in t_so is not null, then there is definitely a match in t_db?

Does id uniquely define both tables?

 

[lincon]

t_db.id is uniquely and t_so not uniquely,because one t_db.to_cust_id may sale many t_so.to_cust_id.

 

[entaroadun]

Create a view:

select    d.id
  ,       s.item_id
  ,       d.from_cust_id
  ,       s.to_cust_id
  ,       s.qty
from      t_db as d
  join    t_so as s
    on    d.id = s.id
where     s.id is not null
union all
select    s.id
  ,       s.item_id
  ,       s.from_cust_id
  ,       s.to_cust_id
  ,       s.qty
from      t_so as s
where     s.id is null

Model this view into MSTR and attach your attributes and facts to it. Remove the attributes and facts from t_so and t_db.

This will get the result you want.

I still don't understand exactly what you mean by: "t_db save the agent transaction each other, t_so save sales data agent sale the customer

 

[lincon]

thanks,i use view also in MSTR,but i want MSTR how to use union.

agent can sale item to othe agent,one transaction is recorded in t_db;
agent sale item to final customer,these are recorded in t_so.

 

[entaroadun]

No unions in MSTR, except for processing multiple inserts into a temp table for consolidating metric passes with outer join settings on them. Sorry.

MSTR is an excellent SQL generation tool, but it isn't infinite. That's why we still use the RDBMS software. It would be dangerous and foolish to rely on any one tool to do everything.