Rules Performance: Help with Markers

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

    • Rules Performance: Help with Markers

      Hello there,

      Previous I was an MIS Alea user hence very impressed with the Palo server. I have one major problem though and this is performance of the DB concerning when using rules between two cubes.

      My rules (only II) are simple.

      I have a data cube (11 dimensions) and a currencies cube (5 diemnsions). I want to convert all the data in my data cube into the the currency of choice using the rates from the currencies cube.

      I.e. i enter data in on a transaction currency i.e. T_EUR
      If I want to see the amount in CHF then the rule simply
      CHF = T_CHF * (currencies cube) CHF + T_EUR * (currencies cube) EUR.

      However the performance (currently working on a local server) is appalling, considering the amount of data I am trying to pull through. Under 1000 values for example, in an excel retrieve using palo.datav

      I was interested to learn about Markers, but am not able to currently incorporate then as I do not understand them properly and could not find too many resources.

      Any ideas - suggestions GREATLY appreciated... I cannot look at a white screen, "Excel not responding" anymore...

      Kind regards

      William
    • RE: Rules Performance: Help with Markers

      Hi,

      Thanks for the tip, I tried it and after 8 minutes of calculation it crashed out. I will try again though.

      (I am hoping to move away from local server v. soon, but I have heard this may not instantly solve the problem)

      I will play some more with my caches as well. Did I hear a rumour of a new release in November that will have improved rule functionality.

      A currency conversion rule for me is fundamental, I would like this to work.

      Thnaks again for the help, all adivce and tips greatly appreciated.

      William
    • RE: Rules Performance: Help with Markers

      Hi There,

      Sorry no more luck after testing this morning..

      Let me know what further information or data I could forward if anyone would like to have a look.

      My aim is to pull a simple P&L statement in a consolidated currency, and it just shuts down the computer.

      I will aim to get the DB on the server today, but I am worried that will not instantly fix it?

      As always many thnaks for any help.

      Kind regards

      William
    • RE: Rules Performance: Help with Markers

      Here It is, I hope i have posted the correct documents.

      Many thanks for this. There are only C. 500+ values in there I did for a quick test.

      I included the current rule as well as a txt file, I hope this can be of some help.

      William

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

    • RE: Rules Performance: Help with Markers

      I recognized that you are doing a quite complex calulation with two PALO.DATAs accessing the original cube (the one with the rule).

      I would expect something like this:

      ['EUR'] = N: ['CHF'] *PALO.DATA("DB","Currencies",!'Time',!'Scenario',"EUR","Kurs")

      For your DB:
      Did you try this one?
      ['CHF'] = N:['T_CHF']* PALO.DATA("somnium","OL Currencies",!'Time',!'Scenario',"T_CHF","Kurs") + ['T-EUR'] * PALO.DATA("somnium","OL Currencies",!'Time',!'Scenario',"T_EUR","Kurs")

      (You don't have to read values in the cube the rule is placed in with PALO.DATA-Formulas and can use [''] syntax instead).

      Holger

      The post was edited 2 times, last by h_decker ().

    • RE: Rules Performance: Help with Markers

      Hello there,

      Early results sadly not as promising as I hoped.

      Is it possibly I have the wrong settings, i.e. not enough ram or I must adjust the caches? My knowedlge is not too great here sadly.

      I get the same message in Excel 2007, Calculation is running 2 Processor(s) 0% same after 10 minutes...

      Then the normal open task manager white screen computer crash and some unsavoury words...
    • RE: Rules Performance: Help with Markers

      Now try

      ['CHF'] = N:[['T_CHF']]* PALO.DATA("somnium","OL Currencies",!'Time',!'Scenario',"T_CHF","Kurs") + [['T-EUR']] * PALO.DATA("somnium","OL Currencies",!'Time',!'Scenario',"T_EUR","Kurs")

      ==>see double brackets around T_CHF and T_EUR. These are markers for T_CHF and T_EUR. That means that rule is only executed for filled T_CHF and T_EUR cells. This should speed up. After setting them save the rule and restart server to be sure that markers are set....just in case. Then retry.

      BTW: Do you get an error message or just white screen which obviously means that excel with PALO is still calculating?


      Holger

      The post was edited 2 times, last by h_decker ().

    • RE: Rules Performance: Help with Markers

      Hi there,

      Thanks for your constant suggestions and help).

      I have now implemented the new rule:
      ['CHF'] = N:[['T_CHF']] * PALO.DATA("somnium","OL Currencies",!'Time',!'Scenario',"T_CHF","Kurs") + [['T_EUR']] * PALO.DATA("somnium","OL Currencies",!'Time',!'Scenario',"T_EUR","Kurs")

      The problem now is that it will not calculate CHF. If I pull the view on T_EUR or T_CHF, then I see the amounts enterred. Hpwever when I select CHF I see nothing.

      I have checked that there are currency rates etc in the currencies cube.

      When I remove the double bracket, it then goes to calculate, but whiote screen (no error message) indicating the calculation is taking a very long time!!

      Does it help if I change the order of the rule and calculate T_EUR first?
    • RE: Rules Performance: Help with Markers

      no sorry for the confusion.

      this is the only rule. I just thought if I rearranged this rule with the T_EUR calculation first.

      Currently CHF = T_CHF * T_CHF(currencies cube) + T_EUR * T_EUR(currencies cube)

      I tried CHF = T_EUR * T_EUR(currencies cube) + T_CHF * T_CHF(currencies cube).
    • RE: Rules Performance: Help with Markers

      It works for me. I entered a test rate of 2, entered 500 for T_CHF and got 1000 for CHF (which is of cource nonsense but demonstrates the techique) :)

      I made a screenshot of the views and attached them.

      A bigger testview with about 2000 cells and CHF chosen takes about 2 secs.

      Holger
      Images
      • curr_conv.jpg

        110.79 kB, 778×686, viewed 2,790 times

      The post was edited 2 times, last by h_decker ().

    • RE: Rules Performance: Help with Markers

      It works!!

      My problem was I loaded the currency values in the currency cube AFTER I uploaded the values in the main data cube. FOr some reason this causes a problem?

      Calculation time was a few seconds..

      I thnak you immensly. If there is somewhere I can donate to keep this service gong let me know as it was really valuable.

      THANK YOU HOLGER!!!