ReportNet - Union ALL?

[KingCrab]

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

 

[JGirl]

Is there any reason you couldn't create a database query subject that has SQL which performs the UNION between the three tables, but also include an additional column in the SQL to determine the country, for example

SELECT 'UK' as country, price1, price2 
FROM UK
UNION
SELECT 'USA' as country, price1, price2 
FROM USA
UNION
SELECT 'Japan' as country, price1, price2
FROM JAPAN

This would allow you to merge all of the data into one query subject without an outer join, and the addition of the country column will allow you to filter by country where required, as in the examples you listed above.

This way you could have one star schema that contains all the data for all countries, which could be filtered by the 'Country' attribute in the fact table?

J

 

[JGirl]

Sorry KingCrab, just correcting my previous post..I had a brain lapse for a minute...you would need to use 'UNION ALL' instead of 'UNION' in the datasource query.

J

 

[KingCrab]

I am using that currently and was basically curious if someone had a better idea.