Essbase

Get Involved. Join the Conversation.

Comments

  • Mike Larimer

    @CURRMBR is not allowed in a FIX statement.

  • Anviksha Shukla

    Hello Elisa,

    Your query isnt very clear, but if you are trying to select a dynamic time series member through Smart View. That is possible and can be done following the steps at this page:

    https://docs.oracle.com/cd/E89578_01/SVPBC/sel_dts.htm#SVPBC-adhoc489788

     

    Thanks,

    Anviksha

  • Anviksha Shukla

    You cannot use attribute members to define a replicated partition. For example, associated with the Market dimension, the Market Type attribute dimension members are Urban, Suburban, and Rural. You cannot define a partition on Urban, Suburban, or Rural, because a replicated partition contains dynamic data, not stored data. Therefore, an attempt to map attributes in replicated partitions results in an error message. However, you can use the WITHATTR command to replicate attribute data. Also, attribute dimensions can be used but in transparent partitioning only.

    Syntax would be : @ATTRIBUTE(Caffeinated_True)

    https://docs.oracle.com/cd/E57185_01/ESBTR/attribute.html

     

     

     

     

     

  • Anviksha Shukla

    You can use UDA or you can use do a one to one dimensional mapping to push data in replicated partitioning. Even @DESCENDANTS and @ LEVMBRS should work. What is the error you are getting?

  • Sébastien Roux

    Thank you Tim! Indeed IDCS is the right answer but I searched for it for 2 days almost.

    Guillaume Slee helped me to find out where IDCS was because I was tricked by the following message that appears on Oracle cloud infrastructure page and it was redirecting to a Federated Identity page where you can also create users & groups but not the right ones.

    User Management

    So if someone is lost remember that IDCS can be found from this page: https://myservices-<customer-name>.console.oraclecloud.com/mycloud/cloudportal/cloudHome

     

  • Tim Faitsch

    I have to admit I haven't had to do this yet but here goes anyway... It looks like you're in the jet UI. I think you need to manage users and groups in the Identity Cloud Service. Good luck!

  • Tim Faitsch

    The question really comes down to how flexible you want this to be. If you just want the moving sum of the last three months then you don't need to use @MOVSUM. Just add the members up in a formula: "Aug"+"Sep"+"Oct";. You could easily cross years with a cross dim operator. You could also drive it using substitution variables. That's probably the simplest way of doing this.

     

    But what if someone is looking at a prior year report? What if they want to compare Oct moving sum versus Oct moving sum from last year or Sep moving sum? You've got a few options:

     

    1. Add a "View" dimension. Load data into one stored member, add a moving sum dynamic member. This will be the most flexible as you can make it work for all accounts and all time periods. If the cube is already in production this is probably the least attractive option.

    2. Add a moving sum member for each of the 12 periods in the period dimension. I don't love this one, but it is an option.

    3. Add a moving sum member in the accounts dimension for each account that needs it. If the list of accounts that really need this analysis are small then this is a good option. But what happens when the users want moving average as well? Six month moving sum? etc...

     

    For crossing years, the @XRange function works great. If you're hard coding three months then you might have to use an if statement to handle January and February to grab the prior year for November and December.

  • Dzhao

    One more question, is there any reason that I should not use substation variable? users want to have rolling three month sum, so my thinking is that to make it dynamic.

    Also I test on @MOVSUM("2018",3, "Jan":&curMonth); it will apply to all account (chart of account), however this will limit to one year, 

  • Dzhao

    Tim,

    Thanks again. I have Account, Years, Period (Quarter-Month), and CostCenter, and my member formula (RollSum) is under Period (time dimension), so in this case, what should I put in my first parameter for MovSum if I want to apply to all account?

     

  • Tim Faitsch

    Are you getting some kind of error during partition validation? @LevMbrs("Accounts",0) should be fine.

  • Tim Faitsch

    I wouldn't use a substitution variable for the month. You can just name all of the months.

    1. If you put this formula on an account, you can only get the moving sum for a single account (otherwise how would Essbase know which account you wanted?). If you want to have a generic moving sum for all accounts (or some accounts) then you need to put it into a different dimension. I can't see your outline so I don't know what makes sense.

    2. You will need the @XRange() function for your third argument if you want it to cross years. Something like this: 

    @XRANGE("2018"->"Jan", "2020"->"Dec")
    
  • Dzhao

    Tim,

    Thank you so much for your reply. I actually tried this

    @MOVSUM("Total Expense",3, "Jan":&curMonth);

    it seems working, for example, it is return sum of total expense for Aug + Sep and Oct. However I am stuck on this

    1. I am trying to say that I want to rolling 3 month for all descendants of Net Income, so I change to @MOVSUM(@Descendants("Total Expense"),3, "Jan":&curMonth); so user can get 3 month rolling sum for whatever account they drill down to, however the one I have above is not working somehow, any advice on what I did wrong here?

    2. When it is crossing year, for example, Feb of 2020, the rolling sum of 3 month should be Dec of 2019 + Jan + Feb, and my formula not returning any value.

    Any pointers or new approach is appreciated.

     

     

  • Tim Faitsch

    I'd change the formula to use "Jan":"Dec". Does it give you a value for June?

  • Glenn Schwartzberg

    Why would you do it in the bat file and not MaxL. If you really needed to do it in the bat file, I think I would create a simple MaxL script with just the login statement with a cleartext ID and password then encrypt it. Then take the encrypted values and put them into your bat file then call the maxl startmaxl.bat -D xxxxxxxxxxx    encrypted userID encrypted password

    In the MaxL it would be something like login $Key $1 $key $2 on servername;

  • Anviksha Shukla

    You can find the MaxL here for partitioning : https://docs.oracle.com/cd/E57185_01/ESBTR/maxl_crepart_r.html

    For Automating the partitioning script, you can use something like the below script,

    echo off

     SET HOUR=%time:~0,2%

    SET dtStamp9=%date:~-4%%date:~4,2%%date:~7,2%0%time:~1,1%%time:~3,2%%time:~6,2%

    %ESSBASEPATH%\bin\essmsh.exe <Maxl Script name> %username% %pwd% %svrnm% %sourceapp% %targetapp% %dtStamp% %PartitionArea1% %PartitionArea2%