PALO 2.0 - Creating rules

    PALO 2.0 - Creating rules

    At first: congratulation for the new version, rule creation has been the first item on my whishlist.

    I think it would be useful to create a new thread in the forum for creating rules.

    Summary of previous posts related to rules in 2.0:

    Hugo's detailed guide for rule writing:

    Originally posted by Hugo
    This is indeed a good question, from what I can tell the syntax is similar to that of TM1
    and takes the form of

    [source] = [destination]

    eg

    [] = 10 would set every value in a cube equal to 10

    You can use the following symbols (+,/,-,*)

    eg - The example below assumes you have created a new element in the measures dimension of the sales cube in the demo database called GrossProfitPercentage

    ['GrossProfitPercentage'] = ['Gross Profit']/['Cost of Sales']*100


    For maths operations you use the following symbols

    >= (greater than or equal to)
    > (greater than)
    <= (less than or equal to)
    < (less than)
    == equal to
    != not equal to
    <> not equal to
    TRUE - boolean true
    FALSE - foolean false


    You can use a comma to separate elements eg

    ['OpeningBalance','Jan2007'] = ['ClosingBalance','Dec2006']

    Please note that whilst you can write rules like the above you would probably be best performing an operation like this one with the copy function in splasher since that way you wouldn't be continually calculating figures which probably won't change any more.

    You can use functions within the rules eg

    This rule will give you a flag of 1 in the cell if the items make more than 40% gross profit margin
    ['HighMarginItem'] = if(['Gross Profit']/['Cost of Sales']*100>40,1,0)

    You can also restrict the area over which the rule fires by using C: and N: as part of the rule Whereby C refers to consolidated elements and N refers to leaf level (base level) members.

    eg

    [] = N: 50
    [] = C: 1000

    The rules above set every base element to 50 and all consolidations to 1000

    N: and C: can be embedded in rules

    eg

    ['Sales'] = N:['Units'] * ['Price']


    I'm not sure about external cube rules (eg pulling values into one cube from another cube or even if this is possible in the current version)
    Intercube rules are frequently used for this such as showing multiple currencies, whereby values may be lookedup in separate cubes. This is the syntax used in TM1

    eg ['Sales','April'] = ['Units','April'] * DB ('Price',!Region,!Product,'March')

    I also don't know how palo implements accelerators (alea term) or feeders (TM1 term) - these are basically server flags which control when the rule fires for increased performance

    eg If I had a rule such as

    ['Revenue'] = ['Price'] * ['Volume']

    then I would write an accelerator saying ['Volume'] => ['Revenue'] - This ensures that the rule will only fire when there is a volume (since anything times by nothing will be nothing it doesn't make sense to perform the calculation when there is no data)
    Basically accelerators or feeders are used to increase performance and handle cube sparsity.... Hopefully someone from Jedox can shed a little light on how we perform intercube rules and accelerators.


    Hugo's function list

    Ok I have been digging a little deeper, since my last examples weren't really of much use (well at least not in terms of performing powerful calculations).

    Here is a zip file containing an xml file and a style-sheet which will list all the functions, if you wish to see the descriptions in a language other than english then please change the value in covert.xsl to either:

    "english" or "german" -> doesn't look like any other languages are in there yet.

    To view a table of all the functions, simply save the two files and open the .xml file in your browser.

    Download Palo Function List




    My question:
    - Is it possible to use values from a different cube (e.g. DB function in TM1 or VLOOKUP in Excel)? I've tried to use PALO.DATA but without success.
    Hi,
    syntax is like this:

    ['elem'] = palo.data("demo2","#_opstmt_entity","local currency",!'opstmt_entity'),!'opstmt_year',!'opstmt_month')


    First argument is the dbname without leading server/, second argument is the cube. Then go on with the element similar to TM1.

    Holger
    Hi Holger et al,

    does anybody have an idea of the costs of such an access? From the demo database you took this example from I get the feeling that external access gets computationally very expensive. We are getting into the "best practise" section here again but ... ist it wise to design with external links or does it make a big difference to stay in the same cube?

    Thanks,
    Mario
    The main goal of getting data from external cube is to reduce the complexity of the database. For example: you can store your product prices in a cube with the following dimensions:
    - data dim
    - year
    - product
    - customer

    But the sales data is stored in the following dimensionality:
    - data dim
    - year
    - product
    - customer
    + month
    + day
    + version

    Therefore you create less "cells" in the database if you store the data which need less dimensionality in less complex cubes. It's easier to handle as well.

    I don't know the DB architecture of PALO but in other OLAP systems (i. e. TM1) there aren't appreciable differences if you use off-cube formulas or you store data in one cube.
    I'm testing PALO 2.0 with a small "dummy" database, therefore I have no experience yet with performance differences.
    Your example might fail with slowly changing dimension which certainly applies for a product prize in most circumstances. Ok, to me it is obvious when I would separate data into cubes (I would keep time dimensions usually in but there are plenty of those examples).
    Now with rules I just wanted to know if anybody experienced substantial drawbacks when keeping these separate cubes and calculated between them. If your experiences from TM1 may be considered to be indicative there is no need now to handle more complex cubes instead.

    Thanks,
    Mario
    Another common example is currency conversion where a relatively tiny currency rates cube (which mya be dimesnioned by time as well if requiuired) can be used to convert large cubes of data from one currency to another. The main benefit is in maintainability, where, in this example the currency conversion ratess need only be stored once.

    The concept is akin to normalisation in a relational database.
    Best wishes

    John Hobson
    The Planning Factory, Lytham, UK
    www.planfact.co.uk
    Yep, but I would not keep the conversion rate in a lookup without a time stamp. These are changing way to often. I would calculate conversions when writing into an analysis cube or perhaps alternatively (for scenarios starting with "what would have happened if ... ") keep this relational approach but then strictly along along a time line. You would not like to multiply historical finacial data with contemporary conversion rates but rather those which have been effective at this time.
    But normally I will not use Palo for OLTP, there are more sophisticated solutions for that.

    Ciao,
    Mario

    Palo Rules - Calculation Speed

    Hi,
    I was wondering if there is a difference in calculatiion speed if I did wrote my formulas individually or wrote it as a "Total. (assuming there are only 5 currencies in my dimension) eg.

    ['Base Value'] = ['Local Value'] * !'Exchange Rate Currency'

    OR

    ['Base Value'] = ['Local Value'] * ['USD']
    ['Base Value'] = ['Local Value'] * ['EUR']
    ['Base Value'] = ['Local Value'] * ['JPY']
    ['Base Value'] = ['Local Value'] * ['HKD']
    ['Base Value'] = ['Local Value'] * ['GBP']

    Is there any difference in the calculation speed for the 2 above rules?

    Regards,

    Jimmy