Multiplying two cubes via ETL

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

    • Multiplying two cubes via ETL

      Hey everyone,

      is there any way to multiply two cubes via ETL?

      For example, I have (simplified):

      Cube A
      Dimensions: Time, Produkt, Price, Region

      Cube B
      Dimensions: Time, Produkt, Quantity, Region


      I would like now to get the Sales figure by multiplying Price x Quantity in ETL

      Via Rules it is not possible, since Cubes are very large (Jedox said it itself).


      Best Regards,
      Emil Abdullayev.
      In love with Jedox, since 2018 :)

      Best Regards / Mit freundlichen Grüßen
      Emil Abdullayev

    • Hi everyone,

      finally, I could figure out how to calculate values via ETL and tested it on a small cube. Now I would like to test it on a productive system. But unfortunately, ETL gives me the error:

      Cannot import Data into Cube Vertrieb_Plan: splashing is not possible,"too many cells", "parameter 'splashMode' value '2'"(Olap server error code: 5012)

      What does that exactly means? Is there too many dimension elements, or what?
      In love with Jedox, since 2018 :)

      Best Regards / Mit freundlichen Grüßen
      Emil Abdullayev

    • Hi Emil,

      You could do this via an ETL process of:

      Step 1: Extract the 2 cubes
      Step 2: Use a table join transformation referring to each cube, and joining with the conditions of Time, Product and Region being equal
      Step 3: Use a field transform, referring to the table join. In here there are many options in which you can manipulate the data. If you create a new 'Function' and select the 'Groovy' type (which is a programming language)
      Step 4: In the function editor, choose the 'Price' and 'Quantity' inputs. Define them as type 'Double' and give them an Alias to refer to them in the code (For example I gave the name for Price as 'P' and Quantity as 'Q')
      Step 5: In the script editor use the code: return P*Q
      Step 6: Your field transform is now complete. Create a 'Cube Load' referring to the field transform. You can point the result to a new cube - say called 'Sales'. You can then execute the load and the result should be the multiple of the 2 values

      I persume you used a similar style of process.

      As for your issue loading into the cube, I would first check all of the dimensions are for the target cube. As well as checking your join has returned the expected number of rows. It sounds as if you are working with quite a large dataset!

    • StephenJK wrote:

      Hi Emil,

      You could do this via an ETL process of:

      Step 1: Extract the 2 cubes
      Step 2: Use a table join transformation referring to each cube, and joining with the conditions of Time, Product and Region being equal
      Step 3: Use a field transform, referring to the table join. In here there are many options in which you can manipulate the data. If you create a new 'Function' and select the 'Groovy' type (which is a programming language)
      Step 4: In the function editor, choose the 'Price' and 'Quantity' inputs. Define them as type 'Double' and give them an Alias to refer to them in the code (For example I gave the name for Price as 'P' and Quantity as 'Q')
      Step 5: In the script editor use the code: return P*Q
      Step 6: Your field transform is now complete. Create a 'Cube Load' referring to the field transform. You can point the result to a new cube - say called 'Sales'. You can then execute the load and the result should be the multiple of the 2 values

      I persume you used a similar style of process.

      As for your issue loading into the cube, I would first check all of the dimensions are for the target cube. As well as checking your join has returned the expected number of rows. It sounds as if you are working with quite a large dataset!
      Hey,

      thanks for the reply. Yeap, exactly! I did it in a very similar way (extract, transformation, groovy). I think I found the issue - though you can see data till the cube load in data preview, it does not load it into the cube. The problem was "Mode" settings in cube extract, right in the beginning. You should select the right modes (onlyNodes, nodestoBases and etc.), even if you select the wrong ones, you are able to see the data, but it wont load.


      I have two very large dimensions: one is with ~ 300,000 elements, other one 100,000 elements, and 4 other with ~1000. I did it recently on a small scale, not for every element, but will try it for all and let you know whether it works.


      P.S - with Rules( KPI ) it did not work.

      Regards,
      Emil.
      In love with Jedox, since 2018 :)

      Best Regards / Mit freundlichen Grüßen
      Emil Abdullayev

    • ... It doesn't seem to work... I need some ideas, guys... I see the data till the Load, in transformations, and it loads the data preview for about 3-5 seconds, but the loading process itself takes about 20 minutes with the "Read timeout" error.... Any ideas appreciated.
      In love with Jedox, since 2018 :)

      Best Regards / Mit freundlichen Grüßen
      Emil Abdullayev