PALO.DATA on array

    PALO.DATA on array

    Hi everybody,

    In Jedox Web, I have a dynarange that filters database bikers, dimension Products, where text = "*Blue*44*".
    I use this to read the Sales by month. This is working fine (see attached screenshot and report).

    Now, let's say my goal is only the chart, which is 1 line made of 12 values. Currently, the dynarange makes me read the values for every element of the range. If I want to have more than 1 measure (like: cost of sales, margin or quantities), the number of values to read would be multiplied exponentially.

    Ideally, we should be able to use a PALO.DATA or PALO.DATA.SUM with an array of coordinates. I tried several options there, but I could not find the trick.

    I know this is feasible with a combination of php and jedox API:

    1/ Login with user = admin and password = md5(admin)
    127.0.0.1:7777/server/login?us…97a57a5a743894a0e4a801fc3

    This returns a <SID> (32 characters, before the first ; )

    2/ Use SID and retrieve list of databases
    127.0.0.1:7777/server/databases?sid=<SID>

    Find the database ID <DB_ID> (in my case: 6)

    3/ Use SID, database ID to read list of dimensions
    127.0.0.1:7777/database/dimens…id=<SID>&database=<DB_ID>

    Find the dimension ID <DIM_ID> for Products (in my case: 27)

    4/ Use SID, database ID and dimension ID to get list of products
    127.0.0.1:7777/dimension/eleme…DB_ID>&dimension=<DIM_ID>

    Filter to find the ones with *Blue*44* (in my case: 5, 27, 197, 226)

    5/ Repeat the dimension / elements process to find the element IDs for the order dimensions of the Orders2 cube
    • Years: dimension ID = 15; element "2010" = 3
    • Months: dimension ID = 17; element "Jan" = 2
    • Customers: dimension ID = 25; element "All Customers" = 0
    • Channels: dimension ID = 19; element "All Channels" = 0
    • Datatypes: dimension ID = 21; element "Actual" = 0
    • Measures: dimension ID = 23; element "Sales" = 2

    6/ Use the database ID to list all cubes
    127.0.0.1:7777/database/cubes?sid=<SID>&database=<DB_ID>

    Find the cube ID <CB_ID> for Orders2 (in my case: 33)

    7/ Read the values using arrays:
    127.0.0.1:7777/cell/area?sid=<…&cube=<CB_ID>&area=<area>

    In my case, the final query is:
    127.0.0.1:7777/cell/area?sid=<…=3,2,5:27:197:226,0,0,0,2

    This returns an array of 4 values

    8/ Sum all 4 values to get the result ...
    Start the process again or tweak the <area> for additional measures.

    Obviously, there should be an easier way to get there ...
    Anybody has an idea?

    Julien
    Images
    • screen_1.png

      65.27 kB, 1,261×768, viewed 302 times
    Files
    • Test_Array.zip

      (8.88 kB, downloaded 124 times, last: )
    Have you considered using measure selection - like dropdown based on which the underlying selection variable will change the perspective from one measure to other;
    you still would have in chart one line but you can toggle between different measures. This would be the easiest solution as it needs no macro;

    Btw - why you can't use palo functions inside php code, instead of calling the REST api ?

    Best regards,
    Noel.