Tuesday 18 February 2014

Custom Built Oracle/Hyperion Essbase ASO Aggregate Views

Query performance of large (billions of cells) Essbase ASO cubes is dependent on selecting proper aggregate views. The ideal is that most user queries hit a subset of the database (an aggregate view or a small slice of bottom level data) and not the entire database. This will result in fast queries and happy users.

This article explains how improve the query performance of ASO cubes by building custom aggregate views and provides a way of manually generating the aggregate view ID’s and view file.

Building aggregate views is a two-step process:

  1. Aggregate Selection -> selection of the aggregate views to be built
    Selecting aggregate views can be done in two ways:
    • “execute aggregate selection” maxl command which select the “best” aggregate views by analysing the cube data together with outline hints and captured user queries. MaxL statement “execute aggregate process” does the same thing as the aggregate selection only with fewer options. 
    • or if you know your dimensions, your data and the user queries you can build the aggregate views manually and save them to file. This article will focus on this task.

    For big databases already having an aggregate selection and not executing it every time saves a lot of time and allows for reduced maintenance windows.
    Aggregate view selection is single threaded while aggregate build is multi-threaded. This means that aggregate build can benefit from more cpu’s (Exalytics) while aggregate selection does not.
     
  1. Aggregate Build -> based on the selection step it builds/materialises aggregate views.
    It receives as inputs either a set of aggregate view id’s or a file with aggregate views.
    Performance of this step is mostly dependent on CALCPARALLEL settings, hardware, temp/default tablespace settings (cube outline and data of course).
    The aggregate build process can benefit from multiple CPU’s for the most part. Some parts of the aggregate build like the commit from the Temp tablespace to the Default tablespace are single threaded which is not great
Essbase aggregate selection process based on automatic data analysis and outline hints:
  • for small databases it’s perfect. Sometimes aggregate views are not even needed.
  • The default sample size to determine and estimate the size of aggregate views is 1,000,000 cells. This is fine for small cubes but needs to be adjusted for larger cubes using the Essbase CFG setting ASOSAMPLESIZEPERCENT. Increasing the value of this setting means more data will be analysed when determining what aggregate views meet selection criteria consequently the view selection and the size estimate is better.
  • for bigger databases with big data and big dimensionality the selection is not as good. Not as good can quickly become unusable. Essbase cannot possibly know what queries will be running. For example it cannot know that the Period dimension will be always/mostly be used as level 0 so aggregate views might select upper level on Period which makes those views useless (this is just an example as you can use Dynamic dimensions or outline hints).
  • It can take a long time (1-2 hrs) on big databases with big sample size (even when using an Exalytics box) and does not necessary provide the best aggregate views.
Essbase aggregate selection process based on captured user queries:
  • Allows the selection of ‘better’ aggregate views. Always a good idea. 
  • When a cube first goes live there is no user query data. The cube needs to be usable for the users to start generating query data. Some people have implemented scripts that execute MDX queries or do Essbase retrieves so that when the Aggregate Selection process runs Essbase will identify specific views. This works but it would be a lot easier the actually specify what views you want to aggregate.
Manual selection of Essbase aggregate views:
  • Allow the selection of those aggregate views which are specifically meant to maximise query performance and get the best possible user experience
  • Understating the dimensions, data, user queries and how user queries are affected by aggregate views is needed. This probably requires more in-depth knowledge and more development time then the automatic aggregate selection however the results can be from unusable cube to sub second reporting (or not, depends on the developer). 
  • Changes in dimension levels invalidate the aggregate views so they need to be recalculated. This process can be automated if needed.
To manually create aggregate views follow the steps:
  1. Get the outline ID of the cube
    Run Maxl command “execute aggregate selection on database ASOsamp.Sample selecting 1 views force_dump to view_file 'VF_FILE1';
    The outline for ASOSamp ID is “4142187940”. 
  2. Get dimension levels of the cube
    Run Maxl command “query database ASOsamp.Sample list aso_level_info;” 
  3. Go to http://aggregateviewbuilder.azurewebsites.net and input the outline id and dimensions levels.
    The inputs have been already set to use example ASOSamp application that ships with Hyperion however they can be changed to any database. 
  4. Build custom Aggregate View Input the aggregate view in the right side of the screen as follows:
    • An aggregate view is list of levels and each level corresponds to a dimension in the outline. 
    • One line represents an aggregate view
    • Each view is made up of comma separated levels. These levels will identify a specific slice in the database that will be aggregated.
    • Levels start from 0 and to a maximum of (Dimenion levels -1). So if the Time dimension has 4 levels then the aggregate view level can only have 0,1,2 or 3.
    • The image below shows all dimensions and levels in the ASOSamp applications and 5 aggregate views: 

  5. Generate aggregate view file and place it on the Essbase server in the ASOsamp.Sample folder.
    Press the “Generate Aggregate View File” and copy the contents of the bottom text box to a file called “VF_FILE1.csc”.
    Copy this file to the database folder (X:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\app\ASOsamp\Sample). 
  6. Run aggregate build
    MaxL to run aggregate build:
    execute aggregate build on database ASOsamp.Sample using view_file 'VF_FILE1';

  7. Check the size of the aggregate views and the performance of the cube.
    Maxl to check aggregates:
    set column_width 50;
    query database ASOsamp.Sample list existing_views;   

9 comments:

  1. I'm trying to use this process on a cube with 32 dimensions - can the web app be updated to allow for scrolling so more dimensions can be added?

    ReplyDelete
    Replies
    1. Done. I actually used it with 20+ dims and it works well.

      Delete
  2. How do I get this to work. I get:

    MAXL> execute aggregate selection on database EXP_RPT.EXP_RPT selecting 1 views force_dump to view_file 'VF_FILE1';

    view_id view_levels size_ratio_estimate query_cost outline_id
    +-------------------+-------------------+-------------------+-------------------+-------------------
    0 0, 0, 0, 0, 0, 0, 0 1 61678.66811329 3767249413

    WARNING - 1241024 - Possible string truncation in column 2.
    WARNING - 1241028 - Output column defined with warnings.
    OK/INFO - 1241044 - Records returned: [1].

    essmsh timestamp: Sat Jul 19 15:06:02 2014

    MAXL> query database EXP_RPT.EXP_RPT list aso_level_info;

    dimension num_levels
    +-------------------+-------------------
    PrimeASO 1
    PeriodASO 3
    Years 2
    Scenario 1
    Version 1
    Lead Medical Fundin 4
    Cost Category 5
    Segment 4
    Product 9
    Legal Entity 8
    Expense Center 30

    WARNING - 1241024 - Possible string truncation in column 1.
    WARNING - 1241028 - Output column defined with warnings.
    OK/INFO - 1241044 - Records returned: [11].

    ReplyDelete
  3. There was already a VF_FILE1 in the directory so I saved it as VF_FILE2.csc

    OK/INFO - 1051034 - Logging in user [karthik@Native Directory].
    OK/INFO - 1241001 - Logged in to Essbase.

    essmsh timestamp: Sat Jul 19 15:27:39 2014

    MAXL> execute aggregate build on database EXP_RPT.EXP_RPT using view_file 'VF_FILE2';

    ERROR - 1013176 - Corrupt viewfile [VF_FILE2].

    essmsh timestamp: Sat Jul 19 15:27:39 2014


    essmsh timestamp: Sat Jul 19 15:27:39 2014

    MAXL> query database EXP_RPT.EXP_RPT list existing_views;

    view_id view_levels size_ratio_estimate query_cost outline_id size_ratio_actual size_cells size_kb
    +-------------------------------------------------+-------------------------------------------------+-------------------------------------------------+-------------------------------------------------+-------------------------------------------------+-------------------------------------------------+-------------------------------------------------+-------------------------------------------------
    0 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0/0 1 61678.66811329 3767249413 1 524906356 8681152

    OK/INFO - 1241044 - Records returned: [1].

    essmsh timestamp: Sat Jul 19 15:27:39 2014

    MAXL> logout;

    User karthik is logged out

    essmsh timestamp: Sat Jul 19 15:27:39 2014

    ReplyDelete
  4. I put my dimension in the tool and I get this when I process:

    6
    3767249413
    0
    1
    Specified argument was out of the range of valid values.
    Parameter name: Level 3 cannot be greater than the dimension Years max level 2
    0.1
    Specified argument was out of the range of valid values.
    Parameter name: Level 3 cannot be greater than the dimension Years max level 2
    0.1
    Specified argument was out of the range of valid values.
    Parameter name: Level 1 cannot be greater than the dimension Version max level 1
    0.1
    Specified argument was out of the range of valid values.
    Parameter name: Level 1 cannot be greater than the dimension Scenario max level 1
    0.1
    Specified argument was out of the range of valid values.
    Parameter name: Level 3 cannot be greater than the dimension Years max level 2
    0.1

    ReplyDelete
    Replies
    1. For an outline with num_levels (1,3,2,1,1,4,5,4,9,8,30) the maximum view you can build is (0,2,1,0,0,3,4,3,8,7,29). This is because the counting starts from 0 not 1. So if the dimension has 2 levels it means you can use 0 and 1, if it has 3 levels you can use 0,1,2 and so on.

      I found that sometimes I get the "1013176 Corrupt viewfile" message if I don't put the a newline of the last row or because of file encoding or CRLF vs LF line breaks. You might want check this.

      The last dimension looks like it has a few of attribute dims. Each attribute will create at least 2 levels on top of the existing levels. You just need to be aware of this when building agg views in order to get the best performance.

      Delete
    2. I put in your recommended values and get same result:

      6
      3767249413
      0
      1
      Specified argument was out of the range of valid values.
      Parameter name: Level 3 cannot be greater than the dimension Years max level 0
      0.1
      Specified argument was out of the range of valid values.
      Parameter name: Level 3 cannot be greater than the dimension Years max level 0
      0.1
      Specified argument was out of the range of valid values.
      Parameter name: Level 1 cannot be greater than the dimension Version max level 0
      0.1
      Specified argument was out of the range of valid values.
      Parameter name: Level 1 cannot be greater than the dimension Years max level 0
      0.1
      Specified argument was out of the range of valid values.
      Parameter name: Level 3 cannot be greater than the dimension Years max level 0
      0.1

      Delete
  5. Very detail information. Thank you very much!!

    ReplyDelete
  6. Hi,

    I put in my information for my 30 dimension cube. I put in my outline id. When I click 'Generate Aggregate View File' I get:

    6
    1448389478
    0
    1
    View length 11 does not match the dimension count 30
    0.1
    View length 11 does not match the dimension count 30
    0.1
    View length 11 does not match the dimension count 30
    0.1
    View length 11 does not match the dimension count 30
    0.1
    View length 11 does not match the dimension count 30
    0.1

    ReplyDelete