Hey guys how are you? Let’s take a look today in the opposite of S01EP12 situation, in fact we’ll use the same example again to show how can we convert a string in a list of values in a easy and dynamic way, starting with this query here:
I’ll transform this query in a with and I’ll use REGEXP to put this back into a list of values. This is very useful when we extract metadata from essbase for example, because essbase exports the UDA’s as a list of values. Of coarse this has many uses other than this but let’s keep this one in mind.
Now what we need to do is to split the strings by comma in this case, then the idea is to count the amount of commas we have in a row and split the strings by that amount.
The idea here is to use the REGEXP_COUNT to count how many words we have in between the commas and then use it to multiply the rows in the CONNECT BY LEVEL. For example, if we have 3 words, the connect by will create 3 rows of the same row, one with the LEVEL = 1 another with the LEVEL =2 and the last one with LEVEL=3.
With that we just need to use the REGEXP_SUBSTR to extract the words based in the LEVEL, this way we’ll have the REGEXP_SUBSTR(STR, ‘[^,]+’, 1, LEVEL (that will be 1 for the first row, 2 for the second and 3 for the third one).
Today it’ll be a quick tip for you guys that like/need to query the Planning repository.
The Planning repository stores both the Plan Type and the Consolidation in a very particular way, in fact this is true for a lot of other things like security, form properties etc… but I’ll focus these 2, that are the more often used and the solution is the same for all of them anyways.
If we take a look in the HSP_PLAN_TYPE table we’ll have something similar to this (depending in how many plan types you have in your app).
As we can see Planning stores in this table all the plan types that were created when we setup the application. In my case I have 4 plan types and we can have up to 5 BSO plan types in a Planning app. Now, if we join the HSP_OBJECT and the HSP_MEMBER filtering the OBJECT_TYPE = 2 we can take a look in all the dimensions we set in the repository.
The USED_IN columns is the column that says to planning in witch plan type that member will exists. The interesting thing here is that, you don’t see the PLAN_TYPE ID that you supposed to right? And that is because a member can exists in more than 1 plan type right, and if we use the PLAN_TYPE ID straight, we would need one row for each plan type right?
Instead, we have just one row but we also have the ability to tell Planning where that member should exists, and we can do that by summing the PLAN_TYPE ID’s together. In the example above, the Account dimension exists in all 4 plan types (1+2+4+8 = 15). Now the Products dimension exists only in one plan type (1), and by the number you can say that is the Pnl Plan type.
you seen the idea here is to check if a PLAN_TYPE ID exists inside that number we have here in the USED_IN column. Another example is the Employee dimension that has the USED_IN set as 8. The only number that will fit in here is the 8 itself (1+2+4 = 7, 1+8 = 9…) meaning the Plan type is WrkForce.
I think the most used way for us to figure out if a number exists inside another number is to use MOD.
CASE WHEN MOD(USED_IN,2)>=1 THEN 1 ELSE 0 END PT_1
CASE WHEN MOD(USED_IN,4)>=2 THEN 1 ELSE 0 END PT_2
CASE WHEN MOD(USED_IN,8)>=4 THEN 1 ELSE 0 END PT_3
CASE WHEN MOD(USED_IN,16)>=8 THEN 1 ELSE 0 END PT_4
CASE WHEN MOD(USED_IN,32)>=16 THEN 1 ELSE 0 END PT_5
The Oracle MOD(N,M) is used to return the remainder of a dividend divided by a divisor where:
N
Dividend.
M
Divisor.
Pictorial Presentation of MOD() function
Then in our case, we need to test if the USED_IN number contains the PLAN_TYPE ID on it, and for that we need to MOD it by rolling sum of the plan types + 1. To make it easier I’ll put that in numbers:
N = USED_IN = 31 (max number possible)
M = PLAN_TYPE ID = 1 (Pnl) what I want to test) + 1 = 2
MODE (31, 2) = 1
31/2 = 15 Reminder = 1
MOD = 1
What that is telling us is that if the MOD is = 1, the Plan type 1 exists in that number. I run a simulation just to show us when the Plan Type 1 does not exists in the USED_IN:
As we can see, the Plan Type 1 only exists in the odd possible results (as expected) what means in any other possible combination of the other 4 plan types he doesn’t exists (2, 4, 2+4=6, 8, 8+2=10, 8+4=12, 8+2+4=14, 16, 16+2=18, 16+4=20, 16+2+4=22, 16+8=24, 16+8+2=26, 16+8+4=28, 16+8+2+4=30).
The same is true for the other Plan types, you can try then out using the MOD. Now, this work well but there’s a way easier and clean way to do exactly the same thing using the function BITAND.
The BITAND function treats its inputs and its output as vectors of bits, the output is the bitwise AND of the inputs. Basically it performs below steps.
Converts the inputs into binary.
Performs a standard bitwise AND operation on these two strings.
Converts the binary result back into decimal and returns the value.
Ok, it looks more complicated now, but the good news is that to use is simpler than it sounds like. The main difference between this function and MOD is that MOD returns a boolean, BITAND return the value you asked if it’s true. Expanding my previous test using BITAND:
As you can see, with BITAND returning the number you asked for instead of 0 or 1 make it possible for us to Join the HSP_PLAN_TYPE with HSP_MEMBER using the USED_IN and the PLAN_TYPE in the BITAND Function as a Join:
As you can see, this is a far better way to split the members by Plan Type. And now we can see that the Dimension Products only exists in the Plan Type Pnl and that Entity exists in 4 different plan types. We don’t need to worry about any mathematics formula to create all our MODs, we just need to Join our Plan Type table with the BITAND of USED_IN by PLAN_TYPE.
The Consolidation is another place where you can use the exactly same thing. Instead of using something like this:
Today we will talk about something very important when we work with Hyperion Planning: its repository.
Today I can say that, if it was not for the Planning repository, 10% of the solutions/issues that I had in my clients should not have been implemented/fixed and 50% the solution/issues should take 10 times more to be implemented/fixed.
Well, as we are talking about integration in this post I will focus on this for now but I promises that I will talk about the repository itself in another time, focusing in Planning development, security and maintenance.
Let us start. First, what is the planning application repository? Basically it is used for manage all existing metadata in an planning application, which means, we can find all forms information, security, dimensions, settings, workflows and other stuffs of an application there and the best part is that we’ll find this in a relational table (normally oracle).
This allow us to easily get any information from any applications and, in an integration perspective, creating smarter integrations.
I said that because normally when we create an ODI interface to load metadata we basically use an ODI datastore for the mapping. as a source table to a planning dimension, and then load everything that we have in that table into planning.
However, is this a smart way to load metadata? I can say no. I have a good example in a big client that I’m currently working. They have an entity dimension with more than 23000 members, and in every maintenance cycle, only 1% of those entities changes or are added. That means that only 230 cost centers are new/changed every month. Well, in a normal ODI development, you will load all the 23000 members every month! And it is not only that: my client has seven applications, all of them with more or less the same size. Making the math, we would load more than 160000 members every month and only 1600 were needed to being loaded.
What can we do to solve that? Very simple solution. We only need to know what metadata already exists in the planning applications and compare it with the source metadata repository. The difference from this comparison is what we really need to load.
But how we can do that? With only two steps, one for extract data (that is the focus of this post) and other to compare the data (that we’ll talk in a later post of our ODI series).
Let’s start:
A Planning application repository has a central table where every object that you create in the application is stored. This table is the key source to extract metadata from the applications and its name is HSP_OBJECT. From this table we can get every information needed for the extract process and it makes easy to extract any necessary dimension information we need. In this table, we have six important columns:
OBJECT_NAME: This column stores the object name;
OBJECT_TYPE: This column stores the type of the member (Shared Member, Version, Entity, Time Period, Cube, Currency, Member, Planning Unit, Year, Attribute Dimension, Dimension, Alias, Folder, Form, Attribute Member, Group, User, Account, Calendar, Scenario, Currency, FX Table and User Defined Dimension Member);
OBJECT_ID: This is the ID of the Object;
PARENT_ID: This is the ID of the objects Parent;
GENERATION: This informs from what generation the object belongs;
HAS_CHILDREN: This informs if that object has a children.
We can notice that this table is based in a parent child relationship. This type of relationship is perfect in cases where we want to store a hierarchy inside of a table because in this way we do not need to know how many generations one hierarchy will have to create the table. We only need two columns with the parent and child IDs to rebuild that correlation between then. To achieve that Oracle database give us a very useful command: Connect by Prior … Start With.
The start with .. connect by clause can be used to select data that has a hierarchical relationship and Prior word is used to create recursive-condition. Long story short, everything we need to do is:
This command will retrieve all the Account Dimension hierarchy and if we need to rebuild the metadata from another dimension the only thing that we need to do is change the OBJECT_NAME to the desired dimension name, E.g. Entity. This query will be the core of our extract process. For now, we have the hierarchy built, but only this is not enough, as we need to have all information about the members in order to compare to our inbound table. We need to take the other metadata information from planning repository as well. We need to join all the following tables together to have everything that we need:
Repository Table Name
Extract Table
HSP_OBJECT
Member
Parent
Generation
Has_Children
Postion
HSP_OBJECT_TYPE
Dim_Type
HSP_MEMBER
Data Storage
Data Type
Two Pass Calculation
Aggregation (Plan1)
Aggregation (Plan2)
Aggregation (Plan3)
Aggregation (Wrkforce)
Aggregation (Capex)
HSP_ALIAS
Alias: Default
HSP_MEMBER_FORMULA
Formula
HSP_DIMENSION
Hier_Name
HSP_STRINGS
Description
HSP_ACCOUNT
Account Type
Time Balance
Skip Value
Exchange Rate Type
Variance Reporting
HSP_PLAN_TYPE
Plan Type (Plan1)
Plan Type (Plan2)
Plan Type (Plan3)
Plan Type (Wrkforce)
Plan Type (Capex)
Source Plan Type
HSP_ENTITY
Base Currency
HSP_ENUMERATION
Smart List
HSP_MEMBER_TO_ATTRIBUTE
Associated Attributes
HSP_UDA
UDA
This table contains the mapping of the tables and the information that you can find in the application repository. Two important things to say when you build the final extraction query from planning repository:
Remember that the HSP_OBJECT table has all the metadata information regarding all the objects in planning. We need to join almost all the above tables with themselves again to get the name of that particular object. E.g., HSP_ALIAS only contains the IDs to the alias; the alias itself is stored in HSP_OBJECT.
Remember to make LEFT JOINS to all those tables against HSP_OBJECT. Depending of the dimension type, we will not have anything stored in some tables. E.g., Account members does not have data stored in HSP_ENTITY table.
With this query ready, all we need to do is loop it passing the Dimension name to the core query mentioned above and it will extract all dimensions from planning.
DEV EPM 