Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need your help for creating one table. I need to create one table with structure:
DIMENSION MEASURE MEASURE
A F11 F12
B F21 F22
C F31 F32
D F41 F42
The fact is that i don't have this dimension like dimension from database. A, B, C and D are names of this measures, that I don't have in database in one dimension. But I want make one table that will include all this names of measures, and every field has different formula.
How can I make it?
Thank you,
Best regards from Bosnia and Herzegovina
May I offer another example to see if I understand you correctly? Are you trying to arrive at something like this:
Measure Type 2017 2016
Sales 1000 500
Units Sold 12000 7000
Customers 500 300
and you are starting with a fact table that looks like this?
Year Sales Units Sold Customers
2016 500 7000 300
2017 1000 12000 500
If so, you could do something to manufacture a dimension with the measure type and manipulate your data to the final format. There are a few ways you could do this. For example,
Result:
Load
'Sales' as [Measure Type],
Year,
Sum(Sales) As sum_amount
From Source_Data_Set
Group By
Year
;
Concatenate
Load
'Units Sold' as [Measure Type],
Year,
Sum([Units Sold]) As sum_amount
From Source_Data_Set
Group By
Year
;
Concatenate
Load
'Customers' as [Measure Type],
Year,
Sum([Customers]) As sum_amount
From Source_Data_Set
Group By
Year
;
You could then create a crosstab of Measure Type x Year and aggregate the sum_amount.
Or, I could have misunderstood you and just wasted five minutes of your life. Sorry.