New to this and need your help on this issue. Seems to be a simple one.
So I am trying to load the fact data into my Cognos Transformer cube. But before doing that I need to build the SQL. So to give a simple example, I have a fact table that has Foreignkeys from 8 dimensions and a single measure. What I need is to build is a SQL that loads the data for 4 dimensions and the Measure. So here is the fact table - Fact_Table (SK, FDim1, FDim2 , FDim3 ,FDim4 ,FDim5 ,FDim6 ,FDim7 ,FDim8 , Quantity)
Can you please guide me if the below simple query is the way to go ?
select Dim1.Col1, Dim2.Col1, Dim3.Col1, Dim4.Col1, Fact_Table.Quantity
from
Fact_Table F
left join Dim1 A ON A.KeyDim1 = F.FDim1
left join Dim2 B ON A.KeyDim2 = F.FDim2
left join Dim3 C ON A.KeyDim3 = F.FDim3
left join Dim4 D ON A.KeyDim4 = F.FDim4
I have not used groupby as I need to load the data at the detail level and let the cube aggregate it. I believe I am missing something and wanted the Gurus here to guide me:) Thanks so much
I think you should use groupby, because you are changing level of detalization (8 measures to 4), so you should groupby selected data by this 4 measures and summarize quantity