1

I have a PDI Transformation that begins with a 'Get rows from result' step. Each of the rows contains a field that has an MDX command as its value. I am using the 'Mondrian input' step and want to use that field in the MDX Query area, but it seems that value substitution only works with variables (not fields).

Does anyone know how I could use a field there instead of a variable? Is there a way to get the value of a field as a variable inside the Transformation?

Thank you very much in advance

Katerina Mpagouli
  • 171
  • 6

1 Answers1

1

It looks like this is not immediately possible. Here's a potential workaround:

Take your fields and combine them into a single string formatted like an array of values in MDX syntax and store this in a variable. Then access this variable in your Mondrian Input step to get the values.

In case you are unfamiliar with using variables in Kettle, you will need to set the variable before you use it. And by before I mean in a transform or job step that is guaranteed to run before the transform containing your Mondrian Input step. You won't be able to set the variable in the same transform as your Mondrian Input step and reliably get the value back due to out-of-order execution in transforms.

Gordon Seidoh Worley
  • 7,839
  • 6
  • 45
  • 82
  • Thank you for your response. I am familiar with using variables in Kettle and that was the first approach I used: My transformation did not start with a 'Get rows from result' step. Instead, it was passed the MDX query to execute as a variable (in its parameters) by another transformation executed before it. Using this approach I had no problems. The problem appears when I need to execute all the MDX queries generated by the previous Transformation together and get their results, instead of executing them one by one. In this case I have the queries as fields, not as variables. – Katerina Mpagouli Apr 09 '13 at 13:58
  • But you are right, both my questions are not immediately possible. – Katerina Mpagouli Apr 09 '13 at 14:04