How to generate a Union in MSTr?

[138006]

Hi
I want to write 2 SQLS joined by UNION.

e.g.

(
SELECT D1.A1, D2.A2, 0, 0, sum(F1.A1), sum(F1.A2)
FROM D1, D2, F1
WHERE ........
GROUP BY D1.A1, D2.A2
UNION
SELECT D3.A1, D4.A2, sum(F2.A1), sum(F1.A2), 0, 0
FROM D3, D4, F2
WHERE ........
GROUP BY D3.A1, D4.A2
)

Any solutions?? Even if MSTR doesnt give this SQL, will there be any way to generate similar output using Custom Group or otherwise?
Thanks

 

[entaroadun]

Where do you come up with this stuff?

I assume that D1.A1 and D3.A1 are different attributes. If so, you're out of luck. MSTR will not merge multiple attributes. You can use a view and model them as the same attribute...

If they are the same attribute, you need to UNION the two dim tables with a view. MSTR requires that all elements of an attribute are on one logical table; they cannot be split across multiple logical tables. This is a requirement. Your data model will not work properly unless you have one dim table that contains all elements.

Use a view. UNION the two sets of tables in the view and put the zeroes you need in the fact columns.

Even if you could generate the GROUP BY and UNION structure you want, you would still be stuck trying to generate the zeroes for the metric values that you need. In this case, you would still have to alter the fact definition to provide a '0' on the appropriate tables.

In other words, there is no set of actions that you can take that doesn't require significant schema changes. Therefore, use a view; it's easier.