You can edit the standard SQL generated by Essbase Studio Server for use during data loads. Your edited SQL can be selected to improve performance when loading data into an Essbase database.
When you edit the standard SQL generated by the Essbase Studio Server, use the following guidelines:
The order of columns in the SELECT clause of the edited SQL should match the order of columns in the SELECT clause of the standard SQL.
User-defined members in the dimension tagged Accounts must be listed in the SELECT clause of the user-defined SQL.
The number of edited SQL statements need not match the number of standard SQL statements.
If you have selected the “Use Custom SQL for data load” check box, and you have not copied over or edited one or more SQL statements, then data load is ignored for those statements. This allows you to exclude statements from data load.
To keep the SQL for a particular statement in a custom data load without editing it, copy it over to the editing pane while the Use Custom SQL for data loadcheck box is not selected.
The default number of columns created as a result of a data load is one column per dimension plus all additional data columns. If a member is prefixed with previous members of its dimension (for example, parent or all ancestors), more columns are returned.
If some columns in the data load SQL statements are NULL, you can add SQL statements to load the data at the next level in the outline. This is known as NULL Promotions.
Because data load SQL statements are tied to filter sets, you can write multiple sets of SQL statements, one for each filter set.
Data load SQL statements are associated with an outline.
The SQL table alias generation process skips reserved keywords for SQL. Be sure to verify that your user-defined SQL is not using reserved keywords.
If a data source column is bound to a level-0 member set, and contains both base members and shared members, it is possible that the data aggregated in the Essbase Studio-generated data load SQL for the specific base members (which have shared members in the same dimension) will be augmented at times, depending on the number of shared members. In this case, Oracle recommends that users define their own custom data load SQL as described in Overriding Standard Data Load SQL. This behavior is true both for Essbase models created from metaoutlines imported from Oracle Essbase Integration Services, and for Essbase models created from the beginning in Essbase Studio.
For example, using the TBC sample, the data aggregated for 100-10 in the standard data load SQL is twice as much as it should be (doubled) because 100-10 has shared members under “Diet”. To get the correct aggregated data, users can select the Use Custom SQL for data load option, and manually add one more “group by” column, “Family”, to the custom data load SQL.