Design strategy for conserving data: push rules vs . enterprise rules or?

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

    • Design strategy for conserving data: push rules vs . enterprise rules or?

      Pest or Cholera, what do you recommend?

      as I learned from another thread, there is no practically feasable way to write enterprise rule based output to a cube and by that preserving the information from getting "overruled" and changed next time you access your report.

      This does not seem to be a major obstacle as long as you do "just" budgeting etc. where rules do not have to be changed to frequently.

      As soon as you get into daily business planing (in my situation it is quite complex product calculation) you are on the fast paced side where enterprise rules need to be adjusted frequently.

      This cause the following situation:
      rules you have setup for your calculation to i.e. calculate Jan plan need to be changed/adjusted to meet february's calculation requirements. Once the rules have been changed/adjusted, Jan values will be calculated based on the adjusted Feb rules, hence deliver wrong results for the past month Jan.
      This is because there is no feature to maintain rule versions and apply them to different scenario's (i.e. month; ). (Off course you can use palo.data...and apply them to a particular dimension element only i.e. a month; but you will end up with tons of rules in a cube and for sure you will pretty soon loose control over this mess)

      Push rules, which allow for writing values to the cube (instead of dynamically creating/calculating the output on the fly whenever you open a report), do have the tremendous disadvantage that - as far as I have tested it - create problems if you do not apply them on a base element level. Using them means to a lot of manual data "pushing" (into the cube) on the base element level.

      I still do not want to believe that there is no way to freeze data (even rule based data created on the fly) and write them to cube (i.e. a budget will be setup for a whole year and the final budget version may not be touched again) but still keep them available for comparism purposes.
      By the way: saving a database version (i.e. release Januar) won't do it eather because if you add dimension elements i.e. new products in the follwing month you will run into problems when you try to do a budget actual respectively a month to month comparism between to DBs....

      Thank you!
    • RE: Design strategy for conserving data: push rules vs . enterprise rules or?

      Hi Marty,

      Did you have a look at tyfus?

      You can define your rules by period (by adding the period element(s) to the area in the rule-definition).

      Could it also be possible to define your rules a bit more generic, but parametrized, with look-ups in the rules to (period specific) parameters in a parameter cube?


      Michel
    • Hi Michel,

      thank you for spending your time on my issue.

      If I understand you correctly the "typhus" approach (see attachment - that's what you mean, right?) will lead to a very long list of rules per cube and hence is pretty hard to manage. Let's say I do have a cube with 25 rules. Every month, 10 rules will have to be adjusted to meet new calc. requirements. That would leave me with 125 rules by the end of the year. It's not the amount of rules I fear but rather the fact that I will loose control (i.e. ..order/sequence of rules...) etc.

      But I am very interested in your other approach but - to be honest - I am not deep enough into palo to quite understand how to set this up. If you get a chance, I would be very glad if you could post a quick example how this might look like (look-ups...)

      Thanks again
      Marty
      Images
      • typhus.jpg

        26.37 kB, 465×350, viewed 3,266 times
    • Hi Marty,

      I wouldn't recommended the first ("typhus") option.

      Regarding the second option:
      Suppose you have a three dimensional cube "Variables", built on the dimensions "year", "month" and "parameter". This cube contains the monthly parameter values.
      In your planning cube you can use the monthly parameter values in the rules by using the PALO.DATA function.
      If you f.i. want to calculate a variable "X" in your planning cube, where "X" is calculated based on the value of variable "Y" in the planning cube multiplied with a month dependend value, you could write a rule like:

      ['X'] = N: ['Y'] * PALO.DATA("yourdatabase", "Variables", !'Year', !'month', "param1")

      Where the "param1" element contains the needed parameter value. The elements !'Year' and !'month' are variable in the rule and are instantiated with the year and month of the cell for which the rule is calculating.
      The N: in the rule restricts the rule to calcultions on base level cells only.

      Michel
    • Thank you Michel! very helpful (I assume for other forum members too!).
      In my particular case I will have to stick with typhus :P

      Please allow me one final question with respect to to reliefing my typhus a bit.
      I tried the following: I applied the rule to a target area which I have restricted a little bit (i.e. just apply it to "Jan" - see screenshot in the previous posting). It worked fine and the rule affected only the Month "Jan")
      In a second step I tried to apply it to "Qtr 1", the parent (see attachment). In this situation the rule had been applied to Qtr1 only but not to the subsequent children (month Jan through Mar).
      Is there any way to tell palo to apply it not just to the named dimension element but at the same time to it's children? Otherwise you get a pretty long list..... (As I just found out, you can not have a "long list" since it is not possible to add more then one element of one and the same dimension to the "target-side" of the rule...which means you could not say "apply rule to "Jan", "Feb", "Mar" only.....!!!! Does this mean you have to setup a individual rule for every single base element meaning one rule for Jan, one for Feb and one for Mar?)

      Thx
      Marty
      Images
      • typhus1.jpg

        24.89 kB, 447×374, viewed 1,249 times

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

    • Hi Marty,

      With this approach you're going to end up with a lot of maintenance on your rules. It is always best to define the logic as generic as possible and use parameters to influence the working of the rules.
      But regarding your questions it is maybe of help if I explain some more on rules.

      In a rule you define the target area to which the rule applies on the left side and the formula that calculates the value for the target area on the right side of the equal sign ("="). The more items make up your target area the more specific (smaller) the area is. PALO reads the rules from top to bottom - the formula of the first target area that matches the cell that is being calculated will be applied to calculate the cell value. So in your rules set the more specific rules should preceed the more generic rule.

      In your example you could have the rules:
      ['Cost of Sales', '2009', 'Qtr. 1'] = 1
      ['Cost of Sales', '2009'] = 2

      With these rules the Cost of Sales for 'Qtr. 1' in 2009 will get the value 1 and the Cost of Sales for all other periods in 2009 will get the value 2. If the order of these rules is reversed all periods will get value 2, the second, more specfic, will never be applied.
      Please note that the rule in your attached picture will not calculate when 'Qtr. 1' is a consolidated element (which I assume it is). With the 'N:' qualifer in the rule the rule will never be applied as any cell with 'Qtr. 1' will be a consolidated cell (and N: restricts the rule to base level elements only).
      Another thing to these rules: I prefer to avoid using hardcoded values in rules - and use parameter cubes that hold these values instead.

      In the formula part of the rule you have a lot of functions available to define the calculations. With f.i. the IF function you can test for a certain condition and only apply the rule if this condition has been met. You can do look-ups to parameter or attribute cubes (you could f.i. use attributes to mark specific elements), you can check whether an element making up the target cell is a child in a certain consolidation, etc. There even is a function STET() that defines that no rule calculation applies to a specific area (so the cell in this area are free for manual input).

      All these 'building blocks' should help you to create a flexible rules set.

      Hope this helps a bit.


      Michel
    • Regarding your Jan / Q1 issue you could use

      PALO.EISCHILD(Server/Database,Dimension,Consolidated Element,Element)

      to determine whether a month belongs to Q1 and thus have one rule only for Q1 months.

      Alternatively you could have an attribute against each month showing a parent quarter and access that attribute using palo.data(), thus alos allowing you a single rule for all periods that share the common attribute.

      I'm not sure why "typhus" is a problem :P

      If your business application really is that complex then there is no way of getting away from having to model that reality.

      Thank goodness for tools like Palo that allow you to do that with minimum cost and maximum efficiency.

      The area type calculation so well described my Michel above really is powerful and means you have the most elegant solutions available!
      Best wishes

      John Hobson
      The Planning Factory, Lytham, UK
      www.planfact.co.uk
    • Michel, Pommie thank you for helpful advise.
      I am aware of many functions since I have spent quite a bit of time reading through the palo 2.5 manual. Unfortuately there is not much about functions. Pretty much no valuable examples that help you learn how to apply different type of functions. If you are aware of any other source please let me know.
      Well, a "FUNCTION" forum where people can post their "function application approaches and ideas" would be a good starting point.

      Pommie: I have tried your Palo.EISCHILD idea but couldn't get it working yet. If you have an example at hand it would be great (...I do not feel comfortable steeling even more of your time....)

      Thank you guys!
    • Hi Marty

      Sorry to take so long to reply again

      As regards EISCHILD, here is a rule that works for all monts in Q1 in the Biker demo

      ['Units','Budget','Central Discount Store','2005'] = N:IF(PALO.EISCHILD("BIKER","Months","Qtr 1",!'Months'),1,STET())

      You need to note that eischild will only look down one level. There is no PALO equivalent to the TM1 "elisanc" dunction which is what is needed if you want to do multi-level parent chaecks

      when this was dicusses before we got

      "There's a feature request for this one, but it won't be in the initial 3.0 version."

      Does anyone from Jedox have an update here?
      Best wishes

      John Hobson
      The Planning Factory, Lytham, UK
      www.planfact.co.uk