3 Fact Tables - Union ALL?

[CoginAustin]

I have 3 fact tables:USA,JAPAN, UK
Each fact table has identical columns (item, quantity, price)
They are joined to multiple conformed dimensions (customer,company, products, time, etc.)

What I am wanting is a UNION ALL (sort of). I want price(fact) to be price for USA, JAPAN, and UK. I do not want to have to drag in price1, price2, price3(Framework wants to do a full outer join which will lead to this problem). The dimensions work fine. It is just the facts (price, quantity, etc..) that need to be joined in some way.

So the question is:
How can I merge the columns of 3 fact table into 1 result set using the same column names across the board while retaining my ability to independantly filter each fact star?

(Yes, that confused me as well)  ???

Report Examples
- Show me YTD sales(sum(price))  for Japan,UK, and USA by Item Number
- Show me YTD Sales(sum(price))  for Japan and USA, excluding Item 12345 from USA

[Merri]

Do you want to do this in Framework Manager or in Report Studio?

You could leave them as three separate fact tables in the model, and in Report Studio use Tabular Sets to perform the union join.
You can apply the filter in the tabular models, and then use tabular sets to do the union on the filtered tabular models. I'm not sure what the performance of this would be, but it should accomplish the union with independent filtering.

Good luck

[joshuawinter]

with union only distinct values are selected.  union all will select all values regardless.   create a fact query subject and write the following sql
select * from
fact 1
union all
select * from
fact 2
union all
select * from
fact 3

make sure that the datatypes are the same for all columns in each table.

[CoginAustin]

Thanks for the replies. I was just confirming UNION ALL was a valid option and the right way to go. I didn't want to be completely of base and wondered if someone had better approach :)