Dynamic Query Expansion
Application class PT_CUBQUERYCHUNK:Utilities:ExpandStructure is used to expand dynamic queries from dimension definitions. The
dimension definition holds values only for dynamic query entries to
which you have added new information that is, information not found
as a default value for the dimension members.
During cube population, the query is rerun and its members extracted to always send to Essbase the most up-to-date member list. This expansion of dynamic queries is completed with this class, which is called in the following way:
&ExpandStruct = create
PT_CUBQUERYCHUNK:Utilities:ExpandStructure(PSCUBWRK2.CUB_OUTLINEID, "");The first parameter is the outline to expand. This parameter expands every dimension in the outline. Alternatively, you can expand a single dimension by leaving the first parameter blank and passing in the dimension ID in the second parameter.
Post expansion, the return code of the expansion should be checked in the following way:
If &ExpandStruct.errorcode <> 0 then
/* report error */Message catalog entry 79,1103 contains the current list of expansion error codes. If new codes are added, this entry should be kept up to date.
The following tables include the current codes.
|
Code |
Error |
|---|---|
|
-21 |
Error opening a query. |
|
-22 |
Error retrieving presaved prompt values for a query. |
|
-23 |
Error obtaining selected column for a query. |
|
-42 |
Error tagging an entry in the internal dynamic buffer for a query. |
|
-30 |
Security error on a tree definition. |
|
-31 |
Error opening a tree. |
|
-32 |
Error finding the required root node on a tree definition. |
|
-41 |
Error: found dynamic tree leaf. |
|
-43 |
Error tagging an entry in the internal dynamic buffer for a tree. |
|
/* Leaf SQL Errors */ |
Error |
|---|---|
|
-51 |
Empty tree structure name. |
|
-52 |
Invalid tree structure. |
|
-53 |
Empty detail search record. |
|
-54 |
Empty detail field name. |
|
/* Duplicate checks */ |
Error |
|---|---|
|
-101 |
Level 0 members with same parent and identical names. |
|
-102 |
Invalid shared member reference. Either the first duplicate name had the shared option selected, or a subsequent duplicate name did not have the shared option selected. Shared members must reference a prior nonshared member. |
The expansion routing populates table PSCUBDIMSTRCOUT based on the dimension structural information found in table PSCUBDIMSTRUCT. The latter table contains references to dynamic queries and any of the saved override values for specific members.
After the dimension is expanded, PSCUBDIMSTRCOUT will contain a row for every returned row for every dynamic query, so it can grow quite large. Every time a dimension is expanded, any rows from prior runs of the expansion routine are purged. Because this table can grow quite large, its contents should not be displayed on a regular PIA page.
A good example of PeopleCode used to expand an outline can be seen in the Expand Outline utility page, as described previously.
For the actual process of creating cubes, the C++ program PS2ESSBASE calls the expansion class to always perform a clean expansion prior to sending in anything to Essbase.