3 Responses to “10 Important Things to Improve ODI Integrations with Hyperion Planning Part 5 (Extracting existing metadata from Planning)”

  1. How would you handle a case where a member might have multiple UDA’s or Attributes? For the UDAs, I’m assuming they could be added in a comma separated array since ODI can handle them that way. However, for the attributes, when a planning app is reversed, there is (if I recall correctly) a column for each attribute. What are you guys doing in such a situation? Multiple columns for attributes in the Extract Table (which may be NULL)? Thanks.

    • Hi Matt how are you? Sorry for the late reply, I’m working on some projects in parallel and this is taking me a lot of time. About your question:
      The UDA is exactly as you say, a list splitted by ‘,’. The table that holds the udas is the HSP_UDA that contains the columns: UDA_ID,DIM_ID and UDA_VALUE. Everything you need to do is group the values by UDA_ID (the UDA_ID is related with the member that has the uda). the easiest way to do that is using the ListAGG command (only in oracle 11g). the sintax is “Listagg (UDA_VALUE,’,’)within group (order by UDA_ID). this will generate your list.
      About the attributes. There’re some ways to handle that. the easy way is to create the table that you’ll use to extract the planning metadata with one column for each possible attribute and in the extracting query you create a left join for each possible attribute! Yes, you’ll have a lot of nulls depeding of the application design.
      There’s a more dynamic way to do that, but I’ll post it later 🙂 I don’t want to take the fun now 🙂
      Thank you for you support.

      • Thanks for the reply–been out of the office or I would have responded earlier. Glad to see I was thinking along similar lines. Enjoyed your presentation at Kscope.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: