0

How to create formula on account members when Essbase is the data source?

Hello,

I hope I can explain this clearly.

We have an MDX query on an Essbase datasource. One of the dimensions in our cube is an Account dimension, and we want to create a formula in Incorta, on two accounts. For example, Revenue account member divided by Cost account member.

When we load the MDX query, Revenue and Cost become two values in the Account column, but they cannot be selected in any formula builder. How do we build a formula off this in Incorta? 

Thank you, Kevin

6replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Kevin - to accomplish this you will need to isolate the values into their own columns. You can do this in Incorta by creating a derived table that has just values for the two columns you need and then derive the ratio based on these columns. Alternately, you can ad a WITH statement to the MDX generating the data extract. This will derive the value using the Essbase engine and deliver the ratio to Incorta. Can you send me a small, sample data set and the ratio you are trying to derive I can provide an example for both options. 

    Like
    • Mike Nader 

      Like
    • Mike Nader Hello Mike,

      I thought I might have to do something like that, but I do not know what a derived table is. Below is a copy of a MDX query, with three account members, Revenue, Cost, Person_Days_Fed. I could break this into three MDX queries, one for each account. If I joined the three tables resulting from the queries on the other dimensions, I suspect I could create formulas for Revenue/Person_Days_Fed and Costs/Person_Days_Fed. 

      Can the query below be modified to bring in three columns, one for each account? Or what is meant by a derived table or WITH clause? I do not know how to send you a data set.

      SELECT NON EMPTY 
      CROSSJOIN ({[Qtr1]:[Qtr4]},
      CROSSJOIN({[Budget],[&Curr_Fcst]},
      CROSSJOIN({DESCENDANTS([Total_Location],LEVELS([Total_Location],0))},
      CROSSJOIN({DESCENDANTS([Total_Department],LEVELS([Total_Department],0))},
      CROSSJOIN({[Revenue],[Costs],[Person_Days_Fed_U]},
      CROSSJOIN({[Periodic],[YTD]},
      CROSSJOIN({[Civeo_Group],[Civeo_Group_AUS],[Civeo_Group_NAM],[CORP_WORLDWIDE]},{[&Curr_Yr]:[&Next_Yr1]} )))))))
      On Rows,
      {[FM Entity Curr Total]} on Columns
      FROM [ESCONSOL].[ESCONSOL]
      WHERE ([FM ICP Top],[Total_Source],[Income_Statement_Detail])

       

      Can I ask some other questions? The MDX syntax supported by Incorta seems to be different from the Essbase MDX, so I am having difficulty determining how to do things. In the above query, we would like to bring in all members under Total_Location and Total_Department. What is the syntax for that? 

      The following items should work in MDX, but are being rejected. Is there something similar that Incorta will accept?

      [member].Generations(2)  - I want members two generations below the named member

      [member].Level(0) -I want level 0 members under the named member

      [member].Generation -I want members at the same generation as the named member

      [member].Level - I want members at the same level as the named member. This is the same as the above, I think.

       

      Thank you, Kevin

      Like
  • Kevin,

     

    We simply send the MDX statement to Essbase and have Essbase parse the statement and respond. Incorta does not do anything with the MDX directly. I will take a look at the example MDX you send and create an example containing the WITH clause. I will also respond to your other questions. Look for an update late tomorrow. 

     

    Thanks,

    Mike

    Like
  • Kevin,

    You had asked about altering your MDX query to pivot the accounts to the columns. Unfortunately, when Incorta takes the feed from Essbase it converts the grid into a list of records with a single column "value". The table becomes long as opposed to wide. There are not columns for the individual accounts. This is advantageous for some activities, but limits the type of calculation you are trying to do.  This gives you 4 options:

    • Leverage a native Essbase extract and load it as a flat file to the schema. If you are using a BSO cube this is really simple leveraging a calc export. This would replace the MDX extract you are creating in the Incorta UI. If you are generating a larger extract, for example with scores of accounts, then this would be the most scalable option.
    • If you are leveraging a smaller series of accounts, then you could put a WITH statement on the MDX and let the Essbase engine calculate the value. I am including an example of a WITH clause in your MDX statement further down in this response. I am also including the answers to the MDX questions you asked above.
    • Use a SQL query, within Incorta, against the Incorta engine to generate a new table, select the accounts you need, and generate a calculation against that table or within the SQL the generates it.
    • If you are using Incorta 4.x, you can use the Incorta table option to generate a filtered list table with the exact columns and calculations you need. This is very easy to do in 4.x with a graphical UI and the resulting report/table output becomes part of your schema. If you are on 4.x, then this is the easiest option from within Incorta. Please let me know if you are on 4.x and if you would like to see a demo of this feature (it is my favorite feature of the new release). 

    Example of the WITH Clause in MDX

    WITH MEMBER [Measures].[Cost%] as ‘[Costs]/[Revenue]’
    SELECT NON EMPTY 
    CROSSJOIN ({[Qtr1]:[Qtr4]},
    CROSSJOIN({[Budget],[&Curr_Fcst]},
    CROSSJOIN({DESCENDANTS([Total_Location],LEVELS([Total_Location],0))},
    CROSSJOIN({DESCENDANTS([Total_Department],LEVELS([Total_Department],0))},
    CROSSJOIN({[Revenue],[Costs],[Person_Days_Fed_U],[Cost%]},
    CROSSJOIN({[Periodic],[YTD]},
    CROSSJOIN({[Civeo_Group],[Civeo_Group_AUS],[Civeo_Group_NAM],[CORP_WORLDWIDE]},{[&Curr_Yr]:[&Next_Yr1]} )))))))
    On Rows,
    {[FM Entity Curr Total]} on Columns
    FROM [ESCONSOL].[ESCONSOL]
    WHERE ([FM ICP Top],[Total_Source],[Income_Statement_Detail])

    Note: In the preceding example, I have to declare the calculated member to be part of the same dimension in which the other accounts exist. For purposes of this example, I called the dimension "Measures". This needs to be change to match the dimension name in your outline. Once the WITH statement is declared, you then just include the derived account in the MDX selection. Both are highlighted in the example shown above. 

    In respect to the specific MDX questions you asked. See my answers below. It is important to realize that Incorta does not reject the MDX. Essbase rejects the MDX and Incorta relays the error. The syntax you are using may be slightly off. Essbase MDX can differ slightly from MDX against SSAS, for example.

    • [member].Generations(2)  - I want members two generations below the named member

      • This is the correct syntax for the GENERATIONS designation. I tested it in both Essbase and in Incorta. My table loads fine. If you are hitting and error with this syntax, it is likely a syntactical error around that line.  Please send me the MDX statement and I can see if I can troubleshoot it. 

    • [member].Level(0) -I want level 0 members under the named member

      • You are missing the plural on this line. The syntax is [member].levels(0). I use this quite frequently when issuing an MDX statement from Incorta. Again, if you are running into an issue, please send me the MDX statement. 

    • [member].Generation -I want members at the same generation as the named member

      • When using this function, you must include a ".members" call at the end of it. For example {[East].generation.members}. I have tested this against both Essbase and issued from Incorta.

    • [member].Level - I want members at the same level as the named member. 

      • This is the same issue at the GENERATION function. Just add the ".members" call at the end.  {[East].level.members}. I have tested this against both Essbase and issued from Incorta.

    Please let me know if you run into any more errors or if you want to get on a webcast to go through some of these. 

     

    Thanks

    Mike

    Like
    • Mike Nader Hello Mike,

      Thank you for your suggestions. I know you and Godwin are communicating to get us some help.

      I tried the exact query you gave, except I changed "Measures" to "Account", which is our dimension. I tried fooling around with syntax as well. The Essbase compiler either does not like the "WITH MEMBER", or more likely, it does not like the "Cost%" member that we are trying to create. I found MDX examples, and your syntax is exactly as documented, so I am sure the problem is on my end. I know the MDX compiles without the "WITH MEMBER" line and the "Cost%" member. This is the entire error message.

       

      INC_005006001:Failed to load data from [ESCONSOL_DEV] at [dc1devhypess01] due to [com.essbase.api.base.EssException: Cannot perform cube view operation. Essbase Error(1260046): Unknown Member ? used in query] with properties [[type, Essbase], [server, dc1devhypess01], [app, esconsol], [database, esconsol], [type, mdx], [query, WITH MEMBER [Account].[Cost%] as ‘[Costs]/[Revenue]’ SELECT NON EMPTY CROSSJOIN ({[Qtr1]:[Qtr4]}, CROSSJOIN({[Budget],[&Curr_Fcst]}, CROSSJOIN({DESCENDANTS([Total_Location],LEVELS([Total_Location],0))}, CROSSJOIN({DESCENDANTS([Total_Department],LEVELS([Total_Department],0))}, CROSSJOIN({[Revenue],[Costs],[Cost%]}, CROSSJOIN({[Periodic],[YTD]}, CROSSJOIN({[Civeo_Group],[Civeo_Group_AUS],[Civeo_Group_NAM],[CORP_WORLDWIDE]},{[&Curr_Yr]:[&Next_Yr1]} ))))))) On Rows, {[FM Entity Curr Total]} on Columns FROM [ESCONSOL].[ESCONSOL] WHERE ([FM ICP Top],[Total_Source],[Income_Statement_Detail])], [discoveryQuery, WITH MEMBER [Account].[Cost%] as ‘[Costs]/[Revenue]’ SELECT NON EMPTY CROSSJOIN ({[Qtr1]:[Qtr4]}, CROSSJOIN({[Budget],[&Curr_Fcst]}, CROSSJOIN({DESCENDANTS([Total_Location],LEVELS([Total_Location],0))}, CROSSJOIN({DESCENDANTS([Total_Department],LEVELS([Total_Department],0))}, CROSSJOIN({[Revenue],[Costs],[Cost%]}, CROSSJOIN({[Periodic],[YTD]}, CROSSJOIN({[Civeo_Group],[Civeo_Group_AUS],[Civeo_Group_NAM],[CORP_WORLDWIDE]},{[&Curr_Yr]:[&Next_Yr1]} ))))))) On Rows, {[FM Entity Curr Total]} on Columns FROM [ESCONSOL].[ESCONSOL] WHERE ([FM ICP Top],[Total_Source],[Income_Statement_Detail])], [error, Cannot perform cube view operation. Essbase Error(1260046): Unknown Member ? used in query]]

      Thank you, Kevin

      Like
Like Follow
  • 3 yrs agoLast active
  • 6Replies
  • 270Views
  • 2 Following

Product Announcement

A new community experience is coming! If you would like to have beta access to provide feedback, please contact us at community@incorta.com.