Jedox 4.0.0 OLAP Modeler needs better rules parser/debugger...common mistakes in creating new rules.

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

    • Jedox 4.0.0 OLAP Modeler needs better rules parser/debugger...common mistakes in creating new rules.

      I'm using the OLAP modeler to create new rules.


      I'm noticing I'm spending a lot of time fighting the rules parser/GUI developing new rules. Most likely this is because I have an incomplete model for how to code rules and/or I'm trying to code by example (where my examples are coming from the Jedox OLAP Manual and this Forum.)


      I've come up with the following list of observervations:



      0. Palo function use “.” in rules and “_” in macros. E.g. PALO.DATA() vs PALO_DATA(). Function names appear to be case insensitive.
      1. All dimensions should be enclosed in single quotes: ‘Time’, ‘Location’, ‘Server’.
      2. All Databases and Cube names should be enclosed in double quotes: “localhost/ParquetDev”, “Demo”, “MachineSupplyCube”. If you make a mistake, the UI won't tell you. You need to look at the olap_server.log file and even that error is cryptic.
      3. When developing new rules, always run tail -f logs/apache_access.log olap_server.log core.log This is where the rule failures show up.
      4. Don’t rely on the rule editor to detect Database/Cube name typos. If you make a mistake in a database name, you won’t see an error in any logs. The only error you’ll get will be when you refresh any spreadsheets/reports and see #NAME? in a cell. And, more insidiously, #NAME? will aggregate as a zero meaning you need to have a view that shows B: nodes otherwise, you may never notice any problems with your new rule. If you have doubts about your rule, convert it into a macro and try invoking it using a button. E.g.
      ['2010.4.12','actual'] = B:PALO.DATA("localhost/Dev","BADtable",'2011.1.01','someplace','AR','server','actual','quantity')
      This rule installs fine, but produces “#NAME?” in my spreadsheet.
      I converted this to a macro like this
      function Button1_Click ()
      {
      $app = application();
      error_log(" you asked for ".$app->PALO_DATA(
      'localhost/Dev',
      ‘BADtable’,
      '2011.1.01',
      ‘someplace,
      'AR',
      'server',
      'actual',
      'quantity'));
      }
      And when the macro is invoked, it gives a usable error:
      [2013/02/27 10:28:57] INFO [palo] exception caught: 'Libpalo_ng : Cube not found : Couldn't resolve cube name "BADtable" in database "Dev".'
      (NOTE: in the macro, I can use single or double quotes depending on whether or not I want to use string interpolation, but the Jedox Olap rule engine isn’t very happy when you switch the two.)
      5. Be very careful when you use the web rules editor. If you type in a long rule and click “apply” to save/apply it, if there is a syntax error, the rule will revert to its original saved form and you will lose all your changes. A slightly insidious feature is if you make a *small* change, e.g. changing a few single quotes to double quotes and happen to make a mistake, there is a very good chance you won’t notice that Jedox has reverted your change.... A very nasty feature. If you do make a mistake, you will get an error like:
      2013-02-27 11:00:36 WARNING: error code: 8001 description: parse error in rule message: syntax error, unexpected element_string at position 91 of line 1
      in the olap_server.log. It won’t help you very much unless you have a copy of the rules in an external editor (and probably a good idea to turn on M-x column-number-mode).
      6. While the “:offset()” shortcut for PALO.EOFFSET() appears to work with negative numbers, the long form doesn’t. PALO.EOFFSET(“Dev”,”Time”,!’Time’, -1) vs. PALO.EOFFSET(“Dev”,”Time”,!’Time’,1). The first one fails silently.
      7. Use !’Dimension’ as the ‘*’ globbing operator in PALO_ functions. Prepending “!” to a single-quoted dimension appears to have the effect of acting as a position filler when accesing a multi argument function like PALO.DATA(“localhost/Dev”,”Table”,!’Dimension1’,!’Dimension2’,"arealvalue",!’Dimension4’) Again, note the use of single quotes and double quotes. If you get the quoting wrong, the rule will fail silently.
      8. Since most functions in the Jedox Olap Manual don’t give example signatures, you need to download the SDK from Jedox’s website and look at the examples under PHP/sample/ConnectionString/*.php. Do grep -i esibling to learn how to use PALO_ESIBLING. They should really improve this. (Also, on closer reading, the definition for most of the functions are on 89-93 of the Olap manual).



      I'll add more as I find them

      The post was edited 1 time, last by byoshimi99999 ().

    • Hi Yoshimi,

      Thanks for posting these observations. Some of them items mentioned, are already on our roadmap and will be fixed/implemented in future versions. Please keep posting them.

      byoshimi99999 wrote:

      I'm using the OLAP modeler to create new rules.

      I'm noticing I'm spending a lot of time fighting the rules parser/GUI developing new rules. Most likely this is because I have an incomplete model for how to code rules and/or I'm trying to code by example (where my examples are coming from the Jedox OLAP Manual and this Forum.)

      I've come up with the following list of observervations:


      0. Palo function use “.” in rules and “_” in macros. E.g. PALO.DATA() vs PALO_DATA(). Function names appear to be case insensitive.
      1. All dimensions should be enclosed in single quotes: ‘Time’, ‘Location’, ‘Server’.
      2. All Databases and Cube names should be enclosed in double quotes: “localhost/ParquetDev”, “Demo”, “MachineSupplyCube”. If you make a mistake, the UI won't tell you. You need to look at the olap_server.log file and even that error is cryptic.
      3. When developing new rules, always run tail -f logs/apache_access.log olap_server.log core.log This is where the rule failures show up.
      4. Don’t rely on the rule editor to detect Database/Cube name typos. If you make a mistake in a database name, you won’t see an error in any logs. The only error you’ll get will be when you refresh any spreadsheets/reports and see #NAME? in a cell. And, more insidiously, #NAME? will aggregate as a zero meaning you need to have a view that shows B: nodes otherwise, you may never notice any problems with your new rule. If you have doubts about your rule, convert it into a macro and try invoking it using a button. E.g.
      ['2010.4.12','actual'] = B:PALO.DATA("localhost/Dev","BADtable",'2011.1.01','someplace','AR','server','actual','quantity')
      This rule installs fine, but produces “#NAME?” in my spreadsheet.
      I converted this to a macro like this
      function Button1_Click ()
      {
      $app = application();
      error_log(" you asked for ".$app->PALO_DATA(
      'localhost/Dev',
      ‘BADtable’,
      '2011.1.01',
      ‘someplace,
      'AR',
      'server',
      'actual',
      'quantity'));
      }
      And when the macro is invoked, it gives a usable error:
      [2013/02/27 10:28:57] INFO [palo] exception caught: 'Libpalo_ng : Cube not found : Couldn't resolve cube name "BADtable" in database "Dev".'
      (NOTE: in the macro, I can use single or double quotes depending on whether or not I want to use string interpolation, but the Jedox Olap rule engine isn’t very happy when you switch the two.)
      5. Be very careful when you use the web rules editor. If you type in a long rule and click “apply” to save/apply it, if there is a syntax error, the rule will revert to its original saved form and you will lose all your changes. A slightly insidious feature is if you make a *small* change, e.g. changing a few single quotes to double quotes and happen to make a mistake, there is a very good chance you won’t notice that Jedox has reverted your change.... A very nasty feature. If you do make a mistake, you will get an error like:
      2013-02-27 11:00:36 WARNING: error code: 8001 description: parse error in rule message: syntax error, unexpected element_string at position 91 of line 1
      in the olap_server.log. It won’t help you very much unless you have a copy of the rules in an external editor (and probably a good idea to turn on M-x column-number-mode).
      6. While the “:offset()” shortcut for PALO.EOFFSET() appears to work with negative numbers, the long form doesn’t. PALO.EOFFSET(“Dev”,”Time”,!’Time’, -1) vs. PALO.EOFFSET(“Dev”,”Time”,!’Time’,1). The first one fails silently.
      7. Use !’Dimension’ as the ‘*’ globbing operator in PALO_ functions. Prepending “!” to a single-quoted dimension appears to have the effect of acting as a position filler when accesing a multi argument function like PALO.DATA(“localhost/Dev”,”Table”,!’Dimension1’,!’Dimension2’,"arealvalue",!’Dimension4’) Again, note the use of single quotes and double quotes. If you get the quoting wrong, the rule will fail silently.
      8. Since most functions in the Jedox Olap Manual don’t give example signatures, you need to download the SDK from Jedox’s website and look at the examples under PHP/sample/ConnectionString/*.php. Do grep -i esibling to learn how to use PALO_ESIBLING. They should really improve this.


      I'll add more as I find them
      Mit freundlichen Gruessen/ With kind Regards / Meilleures salutations

      Vladislav Malicevic
      VP Development & Support
      Jedox AG
    • Hi,

      the rule syntax is quite old and is similar to other OLAP systems i worked with/on. Some of them have logical explanations, some not. I don't want to defend it, just explain motivations authors of the first parser had.
      1. All dimensions should be enclosed in single quotes: ‘Time’, ‘Location’, ‘Server’.
      2. All Databases and Cube names should be enclosed in double quotes: “localhost/ParquetDev”, “Demo”, “MachineSupplyCube”. If you make a mistake, the UI won't tell you. You need to look at the olap_server.log file and even that error is cryptic

      Single quotes are literals and are used at places where name of dimension or element is expected. When Palo detects these single quote strings it converts them internally to persistent IDs. Therefore you don't have to change rules, when you rename element in dimension. Sure it would be better if the OLAP can do the same while using one type of quotes. In last OLAP I was working on we solved it this way. Maybe in next version



      Single quotes are used only inside intracube cell reference square brackets: ['dim':'elem'] or after exclamation mark representing current element of a dimension !'dim' - nowhere else

      Double quotes are used at places where any dynamic string content can appear (e.g. cube name read from other OLAP cube)


      4. Don’t rely on the rule editor to detect Database/Cube name typos. If you make a mistake in a database name, you won’t see an error in any logs. The only error you’ll get will be when you refresh any spreadsheets/reports and see #NAME? in a cell. And, more insidiously, #NAME? will aggregate as a zero meaning you need to have a view that shows B: nodes otherwise, you may never notice any problems with your new rule. If you have doubts about your rule, convert it into a macro and try invoking it using a button. E.g.
      ['2010.4.12','actual'] = B: PALO.DATA("localhost/Dev","BADtable",'2011.1.01','someplace','AR','server','actual','quantity')
      This rule installs fine, but produces “#NAME?” in my spreadsheet.

      Because PALO is calculating rules on the fly it cannot know that the cube you have specified will not exist at the moment of calculations. Therefore your expectation to get the error at the rule definition time makes no sense. Sure we could add some validation of rules against current server state, but there was no requirement for it yet.

      If you right click on the cell showing #NAME? you should be able to get more detailed error information.

      Web rules editor provides very limited functionality. I hope it will be improved in future.

      I agree that OLAP Rules need big improvement in editing, debugging, profiling and documentation. Some of the requirements are already on tasklist.

      Thank you for your input, we appreciate it

      regards

      Jiri
    • Hi Jiri,

      I just tried right clicking on a "#NAME?" error on the web spreadsheet and I get this:

      It doesn't look like there are any options to get more details on this error (the error is related to a rule I installed on a cube).

      I'll go back to debugging with the olap_server.log/core.logs
      Images
      • NAMEerror.png

        14.24 kB, 253×324, viewed 286 times
    • How to debug rules.

      1. Create a rule.
      2. Rule fails silently. Results you expect either evaluate to "#Name?" or "0". Actually, rule failure, depending on the complexity, will first evaluate to "#Name?" and after hitting Data -> Refresh Data will evaluate to 0. I think the reason for this is that #Name? implies that the Olap server is busy applying a rule and the cells with "#Name?" are potential targets. The cells evaluate back to 0 or their original value on hitting Data -> Refresh Data when the rule fails on all cells and the results are rolled back. NOTE: this is just speculation since none of the rule application or roll-backs are visible in the olap_server.logs.
      3. Change the rule. Instead of creating the *most* generic rule like ['a'] = palo.data['db','cube_b',!'x'] * ['d'], create the *most* specific rule you can. e.g. ['a','b','c','d','e'] = palo.data['db','cube',"a","b","c"] * ['a','b','c','d','f'] In this case, my original cube has 5 dimensions and cube_b has 3 dimensions. Also, note that the difference between the input and output is a single parameter ('changed 'e' to 'f').
      4. If this doesn't evaluate, further subdivide the calculation. Try ['a','b','c','d','e'] = palo.data['db','cube',"a","b","c"] and a different rule for ['a','b','c','d','e1'] = ['a','b','c','d','f'] This will allow you to determine which side of the rule is causing a problem.
      5. Finally, when you do get a working rule, you can start changing the parameters back one by one.

      I've used this technique pretty successfully to help debug problems with rules.

      Just remember. The "#NAME?" is your flag indicating there is a problem with your rules. Its not *completely* obvious what that problem is, but will give you some hint that your rulebase has issues
    • More rule tips:

      This is how I normally go about iteratively refining a rule I'm developing. Note, this normally works best if you're dealing with a fairly small subset of a cube (so you can perturb the rule and quickly go back to refresh your Jedox spreadsheet view.)

      ['Measures':'Price'] = C: "HERE"

      ['Measures':'Price'] = C: MID(!'Services',1,4)

      ['Measures':'Price'] = C:IF(MID(!'Services',1,4) == "CUM_","AGGREGATE","NOT AFFECTED")

      ['Measures':'Price'] = C:IF(MID(!'Services',1,4) == "CUM_",SUBSTITUTE(!'Services',"CUM_",""),112)

      The technique here is to build incrementally. The first rule highlights which cells would be affected, its a good idea to prove to yourself that you're not changing more cells that you originally thought.

      Second one tests what a function will return for these cells.

      Third iteration, shows how your function works with an IF() statement, and highlights those places that would be affected (and more importantly, those that won't be).

      BTW, most people start by writing a complex rule and use techniques similar to the ones above to determine why their rules don't compile/work as expected. I'd recommended constructing rules bottom up if you're relatively new to OLAP since many of the interactions aren't always obvious.