Import of data where measure is stored per month in a column

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

    • Import of data where measure is stored per month in a column

      Hello,

      I have a problem, where the solution is simple I think, but I can't find it :(

      There are two problems:

      1. Transfomation of data (I know the data is the same for each month, it's just a very simple example)
      We have an 18 month rolling forecast. I get the data in the following format:
      Input Currency Output Currency M01 M02 M03 … M18
      USD CHF 1,060389 1,060389 1,060389 1,060389
      USD CZK 0,048093 0,048093 0,048093 0,048093
      USD DKK 0,164029 0,164029 0,164029 0,164029
      USD EUR 1,22145 1,22145 1,22145 1,22145
      USD GBP 1,3829 1,3829 1,3829 1,3829
      USD HRK 0,163959 0,163959 0,163959 0,163959
      USD HUF 0,00389 0,00389 0,00389 0,00389
      USD JPY 0,9341 0,9341 0,9341 0,9341

      I want to store the data in a cube having Input Currency, Output Currency, Month and Rate. So M01 column is Month1, M02 column is Month2 and so on until M18. But not as differnet measures. The measure is always Rate.

      In a relational thinking I would transform the data to:
      Input Currency Output Currency Month Rate
      USD CHF M01 1,060389
      USD CZK M01 0,048093
      USD DKK M01 0,164029
      USD EUR M01 1,22145
      USD GBP M01 1,3829
      USD HRK M01 0,163959
      USD HUF M01 0,00389
      USD JPY M01 0,9341
      USD CHF M02 1,060389
      USD CZK M02 0,048093
      USD DKK M02 0,164029
      USD EUR M02 1,22145
      USD GBP M02 1,3829
      USD HRK M02 0,163959
      USD HUF M02 0,00389
      USD JPY M02 0,9341

      Any ideas on this ?


      The second question is based on the first question:
      When we make our forecast in the beginning of march, the first planning month is March. So M01 is 2018-03, M02 is 2018-04 and so on.
      In Sepember e.g. the first planning month is September so M01 is 2018-09 and so on.
      I have a second file which translates M01 to the month.
      M Month
      M01 2018-03
      M02 2018-04
      M03 2018-05
      … …
      M18 2019-08

      This needs to be combined. At the end the real month (e.g. 2018-03) should be stored in dimension month.


      PLEASE DON'T TELL ME TO CHANGE THE STRUCTURE OF THE ORIGINAL DATA. THIS IS FROM AN EXTERNAL SYSTEM WHERE I HAVE NO CHANCE FOR CHANGES.



      Thanks a lot in advance for your help !!!
    • Here is what you can do:
      ETL -> TableNormalization for the first source you mentioned configured like:
      - Normalization Field: Month
      - Measure Value: #Value (this ist default)
      - in the table at the end of the transform the first column contains M01, M02, M03 ...
      - the second column also contains: M01, M02, M03 (according to the first column)
      - Aggregation column could be "sum"

      Output will be:
      Input Currency Output Currency Month #Value
      USD CHF M01 1,060389
      USD CZK M01 0,048093
      USD DKK M01 0,164029
      USD EUR M01 1,22145
      USD GBP M01 1,3829
      USD HRK M01 0,163959
      USD HUF M01 0,00389
      USD JPY M01 0,9341
      USD CHF M02 1,060389
      USD CZK M02 0,048093
      USD DKK M02 0,164029
      USD EUR M02 1,22145
      USD GBP M02 1,3829
      USD HRK M02 0,163959
      USD HUF M02 0,00389
      USD JPY M02 0,9341

      You will need to add another field in this transform containing the measure (you named it "Rate"). I dont know the dimension name containing "Rate", lets assume its "Currency_Measure".

      Now the result will look like:
      Input Currency Output Currency Month Currency_Rate #Value
      USD CHF M01 Rate 1,060389
      USD CZK M01 Rate 0,048093
      USD DKK M01 Rate 0,164029
      USD EUR M01 Rate 1,22145
      USD GBP M01 Rate 1,3829
      USD HRK M01 Rate 0,163959
      USD HUF M01 Rate 0,00389
      USD JPY M01 Rate 0,9341
      USD CHF M02 Rate 1,060389
      USD CZK M02 Rate 0,048093
      USD DKK M02 Rate 0,164029
      USD EUR M02 Rate 1,22145
      USD GBP M02 Rate 1,3829
      USD HRK M02 Rate 0,163959
      USD HUF M02 Rate 0,00389
      USD JPY M02 Rate 0,9341

      Next step:
      Create a TableJoin joining the result and the table for translation of months. Where keys are "M" from the lookup table and "Month" from the result. Alternatively you can achieve this "translation" using a FieldTransform with Lookup-Function.

      Result:
      Input Currency Output Currency Month Currency_Rate #Value
      USD CHF 2018-03 Rate 1,060389
      USD CZK 2018-03 Rate 0,048093
      USD DKK 2018-03 Rate 0,164029
      USD EUR 2018-03 Rate 1,22145
      USD GBP 2018-03 Rate 1,3829
      USD HRK 2018-03 Rate 0,163959
      USD HUF 2018-03 Rate 0,00389
      USD JPY 2018-03 Rate 0,9341
      USD CHF 2018-04 Rate 1,060389
      USD CZK 2018-04 Rate 0,048093
      USD DKK 2018-04 Rate 0,164029
      USD EUR 2018-04 Rate 1,22145
      USD GBP 2018-04 Rate 1,3829
      USD HRK 2018-04 Rate 0,163959
      USD HUF 2018-04 Rate 0,00389
      USD JPY 2018-04 Rate 0,9341
      ...

      These knowledgebase articles should help:
      knowledgebase.jedox.com/knowle…sform-tablenormalisation/
      knowledgebase.jedox.com/knowledgebase/transform-tablejoin/
      knowledgebase.jedox.com/knowledgebase/etl-function-lookup/