In order to create a process that is able to load any application and dimension using one single ODI interface we need to make some code changes to the KM that is responsible to load metadata into Hyperion Planning. First, we need to understand the ODI concept of a KM. KM is a set of instructions that will take the information from what exists in the source and target data stores of an ODI interface and construct a SQL command based in those data stores. In a nutshell the ODI KM is code generator based in the information that you set in the interfaces, data stores, topology and so on.
As we know the default Hyperion Integration KM is able to load only one application and dimension at a time because of the need of a target data store for each dimension in each application. If we take a deeper look in the KM to see what it does behind the scenes we will see something like this:
Figure 1 – KM behind the scenes.
Basically what the KM does is translate the Planning application data store to a SQL query, and as we know, we get this data store by reversing a Planning application inside ODI. Fair enough, but this also means that if we could somehow have the same information that ODI has to reverse this application dimension to a data store we could easily end up with the same SQL created from that data store information. As we already showed before we have the Planning application repository itself where all the information about a Hyperion application is stored. We only need to read this information to get the same information provided by the ODI data store.
Knowing this the only thing left is to change the default KM according to our needs, and for this we need to make three changes on it:
- Make the application name that it is going to be loaded dynamic;
- Make the dimension name that is going to be loaded dynamic;
- Change the way that the KM builds its SQL command that will load metadata to Hyperion Planning. Currently it builds its SQL command based on the source and target data stores and the interface mappings;
Figure 2– Default KM behind the scenes.
In Figure 2 we can see how a default planning integration KM works. Basically it has two main steps: “Prepare for loading” and “Load data into planning”. The first one is responsible to set all information regarding connections, log paths, load options and so on. The second step is responsible to retrieve all source data based in the interface mapping and the source/target data store and load it to planning. In our case, the application and dimension names resides on the first step and the SQL command resides in the second step so we already know where we need to change the code.
But we need to analyze further to know what exactly we need to change. For the application name ODI gets it from “<%=snpRef.getInfo(“DEST_CATALOG”)%>” API function that returns the application name based in the destination target store that is connected to a logical schema that finally resolves into a physical schema that contains the application name itself. If we change it to an ODI variable we will be able to encapsulate this interface into an ODI package and loop it passing the application name as a parameter, making it independent of the target data store topology information and giving us the a ability to load any Hyperion planning application using one single interface.
The dimension name follows the same logic: ODI gets it from “<%=snpRef.getTargetTable(“RES_NAME”)%>” API function that returns the resource name from the target data store that in this case is the dimension name itself. Again if we changed it to an ODI variable we will be able to encapsulate this interface into an ODI package and loop it passing the dimension name as a parameter, making it independent of the target data store resource name and enabling us to load any dimension with one interface.
The third part is the most complex one. ODI data stores for planning applications are so different from one dimension to another that they require one data store object for each dimension. In figure 10 we can see that ODI relies on “odiRef.getColList” API command to return all mappings done in the target dimension data store, which has the correct dimension format required to load that dimension metadata into planning.
So the big question is: How can we change the “Load data into planning” step to use a dynamic SQL to create dynamic interface mappings to load to any application/dimension? The answer is to rely again on the “Command on Source/Target” concept and on the planning repository metadata information.
Instead of getting the mapping information from the ODI data store object, we can query Planning repository to get the same mapping for all dimensions and applications being loaded. The result of this query is a formatted mapping, identically of what ODI would have generated if we used the default planning development, but with the big advantage of being entirely dynamic to any application and dimension.
Figure 3 – Dynamic KM behind the scenes.
In figure 3 we can see an example using an Attribute dimension. The command on source will query HSP_OBJECT and HSP_ATTRIBUTE_DIM of a given application (defined by #SCHEMA_APP variable) to retrieve information about one attribute dimension (defined by #DIMENSION variable). Those variables are passed from an external ODI package that will be used to loop all applications and dimensions that we want to load.
Table 1 – Dimensions Data Store information.
If we take a further look into all different data stores that a Planning application could have, we will see a pattern regarding the information that we need to send to Planning to load metadata depending of each dimension, as we can see in the Table 1.
The logic to create the dynamic mapping columns is exactly the same used to create the inbound and the extract tables. The only difference is that for the inbound and extract tables we need to put all columns together and for the KM mapping we need to, depending of the selected dimension, take the right information in the application repository. This information will help us to create the necessary mapping that contains the right source columns and the right alias of those columns, which will inform Planning about what that metadata column stands for.
Since our metadata tie out table contains standard columns for all dimensions we don’t need to worry about adjustments when we change to another dimension, and since our source metadata table already has the metadata information in the correct planning format, we don’t even need any kind of transformation here, it is just a matter to read from the metadata source table and load directly to Planning.
In the Figure 3 example we will use the SRC_MEMBER, SRC_PARENT and SRC_ALIAS as the mapping columns and for the Planning alias the only one that is dynamic is the member name alias that identifies the dimension name. To get this information we need to query the application repository looking for the attributes into HSP_ATTRIBUTE_DIM and for its name in HSP_OBJECT table, and finally we can use the OBJECT_NAME column to get the dimension name alias.
Executing this query we will get a one line mapping string that will be passed as a parameter (#MODEL) from “Command on Source” to “Command on Target” and will enable ODI to load metadata to that specific dimension/application. If we execute this interface and look at the query created in ODI operator we will see that the result is the same as a default KM would create but with the big advantage of being entirely dynamic. Following this logic, we would only need to change the value of the #SCHEMA_APP and #DIMENSION variables to get another application\dimension loaded.
Off course we need to work a little more to get the mapping for the other dimensions as Account or Entity, but the idea will be always the same: query the application repository to get the data store information depending on the dimension\application selected.
Table 1 – Dimensions Mapping information
In table 1 we can see all the possible mapping combination that we can have in a planning application for the mainly planning dimensions and we notice that some information are dynamic (dependent of the planning repository) and some are fixed. To put everything together in one single query here are some tips:
- The majority of the columns are fixed and can be obtained with a simple “select ‘Any string’ from dual”;
- The easiest way to create this SQL is to create separated SQLs for each different kind of information and put everything together using Unions statements;
- Split the final query in small queries to get the different categories presented in table 1;
- Use the MULTI_CURRENCY column in HSP_SYSTEMCFG table to find out if that application is a multicurrency one or not;
- For aggregations and plan type mapping we need to get the name of the plan type itself and for this we use the HSP_PLAN_TYPE table;
- When the query is ready you need to add a filter clause to filter the dimension from where that information belongs;
With the query ready the only missing step is to insert it into the “Command on Source” tab inside the Planning IKM and pass the string generated by it to the “Command on Target” tab as we can see in the figure 3.
This ends all the preparations that we need for learn how to build a ODI interface that will dynamically load metadata into any number of Planning applications.
Thanks you and see you in the next post.
DEV EPM 