Hi
The scenario is,
I have 3 dim tables say D1,D2,D3 wih a Fact table F1 say. Now I have another table D11 say which is snowflaked with the D1 table.
Now my requirement is to have a SQL of the form
Select
D1.a1,
D2.a2,
D3.a3,
sum(F1.f1),
sum(F1.f2),
min(D11.a4)
from D1,D2,D3,D11,F1
where
D1.d1=F1.d1
and D2.d2=F1.d2
and D3.d3=F1.d3
and D11.d4=D1.d4
group by
D1.a1,
D2.a2,
D3.a3
How to do this? Note that D11.a4 is a varchar. If I create a fact on D11.a4, MSTR expects D11 as a fact table, tries to join D1,D2 and D3 with D11 resulting in incompatible SQL.
I can's create Min(D11.a4) as an attribute in report cause that will include the Min(D11.a4) in the group by clause which is incorrect.
Do I need to create an attribute on D11.a4 and then create a metric on it and use it in report?
If I have to use it a s a metric (based on an attribute or fact) then what level do I have to set for this metric?
Please help
Thanks
The scenario is,
I have 3 dim tables say D1,D2,D3 wih a Fact table F1 say. Now I have another table D11 say which is snowflaked with the D1 table.
Now my requirement is to have a SQL of the form
Select
D1.a1,
D2.a2,
D3.a3,
sum(F1.f1),
sum(F1.f2),
min(D11.a4)
from D1,D2,D3,D11,F1
where
D1.d1=F1.d1
and D2.d2=F1.d2
and D3.d3=F1.d3
and D11.d4=D1.d4
group by
D1.a1,
D2.a2,
D3.a3
How to do this? Note that D11.a4 is a varchar. If I create a fact on D11.a4, MSTR expects D11 as a fact table, tries to join D1,D2 and D3 with D11 resulting in incompatible SQL.
I can's create Min(D11.a4) as an attribute in report cause that will include the Min(D11.a4) in the group by clause which is incorrect.
Do I need to create an attribute on D11.a4 and then create a metric on it and use it in report?
If I have to use it a s a metric (based on an attribute or fact) then what level do I have to set for this metric?
Please help
Thanks