Hi !
I was wondering if You could help me with this ....
I have to build a view for Tableau reporting.
I have only one dimension which is common for all source systems (core) which i would like to join with all of the Source systems.
After that i would like to union all of the source systems with country dimension.
If i do it manually its pretty simple-just need to add some codes in couple of places.
I was thinking if there is a way to generate all this union query.
So i would give lets say 3 (and later more) tables and as result would have automaticly generated union view.
Maybe some dynamic sql would do the trick ?
Any help appreciated.
WITH CORE AS /*one shared dimension*/
(
select country from MDM
)
,Q_A AS /*Source system A*/
(
select
cA1,
cA2
from A
)
,Q_B AS /*Source system b*/
(
select
cB1,
cB2
from B
)
,Q_C AS /*Source system C*/
(
select
cC1,
cC2
from B
)
,RESULTS AS
(
select
from core left join Q_A
union
select
from core left join Q_B
country
null
null
null
null
from core left join Q_C
)
select
FROM RESULTS