2-in-1: RPD Magic and Hyperion Planning "Adapter"

2-in-1: RPD Magic and Hyperion Planning "Adapter"

The best way to explain the powerful modeling capabilities of OBIEE is with a real-use case, and for this session it will be the relational database of Hyperion Planning applications, not a model designed for reporting but for application needs. Use the advanced modeling capabilities of OBIEE to transform the relational schema of Hyperion Planning with what we call some "RPD magic." The target is to fill the gap when reporting against Planning, using only Essbase and missing the content of the relational database, to end with a federation of Essbase and the relational source without ETL, just with OBIEE and some magic.

Bf71450537acca19e045ae6f7febdf9a?s=128

Gianni Ceresa

June 22, 2015
Tweet

Transcript

  1. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com Gianni Ceresa, Rittman Mead ODTUG KScope15, June 2015 2-in-1: RPD Magic and Hyperion Planning “Adapter”
  2. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com Gianni Ceresa  Principal Consultant for Rittman Mead  based in Switzerland  About 8 years working with Oracle BI & EPM tools  Providing BI support on OTN forums  Blogger at http://www.rittmanmead.com/blog/  email: gianni.ceresa@rittmanmead.com  twitter: @G_Ceresa
  3. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com About Rittman Mead  Oracle BI and DW Gold partner  Winner of five UKOUG Partner of the Year awards in 2013 and 2014 - including BI  World leading specialist partner for technical excellence, solutions delivery and innovation in Oracle BI  Approximately 80 consultants worldwide  All expert in Oracle BI and DW  Offices in US (Atlanta), Europe, Australia and India  Skills in broad range of supporting Oracle tools:  OBIEE, OBIA, ODIEE, Essbase, Oracle OLAP  GoldenGate, Endeca
  4. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com What happen in the next 55 minutes ?  The original plan: 2-in-1: RPD Magic and Hyperion Planning “Adapter”  How to use the power of RPD modelling to implement a kind of Hyperion Planning “Adapter” using the content of the relation database of a Planning application But something happened … 11.1.1.9 is now GA …  The new plan: 2 topics in 1 session: The new Planning source in OBIEE 11.1.1.9 and some RPD “tricks”  With the new support for Hyperion Planning it’s better to use that new source instead of complex modelling as this is now the official supported way (and it’s easier)  Would be a shame to not talk about RPD, so let’s have a look at 2-3 things that aren’t really “magic” but not so common …
  5. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com Hyperion Planning finally supported  May 14, 2015 : OBIEE 11.1.1.9 is GA  One of the new features:  Access to Hyperion Planning Data Sources  What? How? Where?  Via an ADM “driver”  Not lot of details in the documentation, better to try it by myself
  6. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com But first, what is the challenge with Planning?  For most of the people Hyperion Planning is Essbase  Essbase store numeric figures  For reporting it’s often enough  But there is more  Each Planning app has a relational database  Contains a copy of the Essbase outline  Cells attributes  Special features of the UI and forms  For “operational” reporting on Planning these information are important
  7. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com A reminder: what is Hyperion Planning? “Enterprise Wide Planning, Budgeting and Forecasting” “Oracle Hyperion Planning is a centralized, Microsoft Office and Web-based planning, budgeting and forecasting solution that integrates financial and operational planning processes and improves business predictability.” (source: oracle.com)
  8. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com A reminder: what is Hyperion Planning?
  9. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com A reminder: what is Hyperion Planning?
  10. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com A reminder: what is Hyperion Planning?
  11. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com A reminder: what is Hyperion Planning?  1 Planning application = 1 schema = 142 tables (Planning 11.1.2.4)  Transactional application schema, multiple dependencies between objects
  12. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com A reminder: what is Hyperion Planning?  The outline is stored in few tables  HSP_OBJECT is a central object  parent-child structure - PARENT_ID = OBJECT_ID  members names  aliases  etc.
  13. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com Back to OBIEE: import Metadata  In the wizard a new type has been added for “Hyperion ADM”: Hyperion Planning  Only 3 fields : - URL (server, port and Planning application name) - Planning username - Planning password  AdminTool NQSConfig.INI  require JAVAHOST_HOSTNAME_OR_IP_ADDRESSES setting
  14. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com Import Metadata : error …  It doesn’t work …
  15. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com Import Metadata : there is a bug …  For the URL the suggested format is: adm:thin:com.hyperion.ap.hsp.HspAdmDriver:<Server>:<Port>:<Application>  Oracle published a “patch” for the documentation saying to change it: adm:thin:com.hyperion.ap.hsp.HspAdmDriver:<Server>%3A<Port>:<Application>  What changed? The port now uses%3A, the ASCII code for “ : “  Why do you need to change the “ : “ by its ASCII code?  Probably because “ : “ is the separator they use in the URL for all the other parameters and they have a piece of logic in place splitting the URL in parts based on that character. But the port is optional as you can also use the default HTTP 80 and not specify it…
  16. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com How does a Planning application look like in RPD?  Nothing really “exciting”…  Looks like an Essbase application  Parent-child dimensions  All the aliases are created  All the UDA are created  A list of measure  A standard “Value” one  Lot of attributes for the measure  Everything created by default, no real options available in AdminTool
  17. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com Does it work? Planning analysis sample in OBIEE
  18. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com Planning analysis sample in OBIEE
  19. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com But what is this ADM driver?  It just looks like a kind of webservice  TCP calls  AdminTool send pieces of XML via POST requests  ADM reply with even more XML  Session based - Login first and get a Session ID - Provide the Session ID in every single call - Logout at the end  Easy to read and understand XML - Would be easy to use the same ADM interface in custom developed applications
  20. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com ADM example – Test case  Extremely simple example to see how the new ADM source works  Dimension “Period”  List of member names  Filter - Generation = 4 - Level = 0  Expected result:  Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
  21. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com ADM example – OBIEE  OBIEE analysis  Really simple!  OBIEE cache = OFF  To observe the communication between OBIEE and Planning 2 useful tools:  TCPDUMP + TCPFLOW (tcpflow makes it easier to read)
  22. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com ADM example – TCP flow between OBIEE and Planning  call 1 = login  call 30 = query  In between list of all cubes  for each cube list all dims and all UDAs login query
  23. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com ADM example – How does the query looks like?  LSQL (from the logs) SELECT 0 s_0, "Plan1 - Plan1"."Period"."Period Name" s_1, SORTKEY("Plan1 - Plan1"."Period"."Period Name") s_2 FROM "Plan1 - Plan1“ WHERE (("Period"."Period Level" = 0) AND ("Period"."Period Gen" = 4)) ORDER BY 1, 3 ASC NULLS LAST, 2 ASC NULLS LAST FETCH FIRST 65001 ROWS ONLY
  24. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com ADM example – How does the query looks like?  Physical query (from the logs) Sending query to database named EPM_KScope_Vision (id: <<90097>>), connection pool named Connection Pool, logical request hash 31e70a60, physical request hash 9aeb05b2: [[ Plan1:en:SELECT * FROM "Period" WHERE FilterBySecurity(), AllMembers() ]]  Is this the real query sent via ADM?  Where are my filters on Generation = 4 and Level = 0 ?
  25. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com ADM example – How does the query looks like?  req_mbr_query has, between other XML attributes, one named QXML which contains the query for ADM: <?xml version="1.0" encoding="UTF-8"?> <COMPOSITEOPERATION TYPE="MemberQuery"> <OPERATION TYPE="Select"> <MULTIVALUE> <STRING>*</STRING> </MULTIVALUE> <STRING>Period</STRING> </OPERATION> <COMPOSITEOPERATION TYPE="Filter"> <COMPOSITEOPERATION TYPE="List"> <OPERATION TYPE="FilterBySecurity"></OPERATION> <OPERATION TYPE="AllMembers"></OPERATION> </COMPOSITEOPERATION> </COMPOSITEOPERATION> </COMPOSITEOPERATION>  It really looks like the physical query logged by OBIEE (just in a XML format)  And the filters on Generation = 4 and Level = 0 aren’t there…
  26. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com ADM example – Query result  result of the “req_mbr_query” call: <?xml version="1.0" standalone="yes"?> <res_mbr_query><sID>...</sID> <mbrList enc="1"> <formCell cellType="10" readOnly="false" queryGeneration="1" order="0" oldName="BegBalance" mbrId="50019" dimId="34" mbrName="BegBalance" dimName="Period" parentName="Period" layoutType="-1" locationStyle="0" parentId="34" objectType="34" baseMbrId="0" objdefId="-1" ordinal="0.0" hasChildren="false" generation="1" aliases="x---------x|x---------x" memberId="50019" level="0" dataStorage="0" consolOp2="5" consolOp4="5" consolOp1="5"/> <formCell cellType="10" readOnly="false" queryGeneration="3" order="0" oldName="Jan" mbrId="50023" dimId="34" mbrName="Jan" dimName="Period" parentName="Q1" layoutType="-1" locationStyle="0" parentId="50022" objectType="34" baseMbrId="0" objdefId="-1" ordinal="0.0" hasChildren="false" generation="3" memberId="50023" level="0" dataStorage="0"/> ….  All the members of the dimension are returned with some attributes, including Level and Generation  The filter will be done by OBIEE
  27. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com ADM vs Essbase : which one is better for performance?  The Essbase source is more “native”  OBIEE interact directly with Essbase generating MDX queries  With ADM OBIEE interact with a Java service on the Planning server  OBIEE doesn’t talk to Essbase or the relation database  The Java service is the only one aware of the Essbase application and relation database schema where the application is stored  Compile a mix of attributes from both sources and send data back - return ID from the database for dimensions, members, aliases etc.
  28. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com ADM vs Essbase : which one is better for performance?  The comparison: a simple list of members for a dimension (the 12 months)  ADM: tons of TCP calls in both directions, lot of data sent back to OBIEE - High CPU load (Java doing “something”) - Would be better with caching - But: when to refresh the cache? Not like a ETL job calling the cache purge script …  Essbase: less calls, less data sent between OBIEE and Essbase - MDX query produced by OBIEE  The natural conclusion would be that if only the content of the Essbase application is required in a report the Essbase source is better … (to be tested over time with a real use case)
  29. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com Second part – RPD “tricks”  RPD “magic” (but not so magic…)  In Essbase you have 2-pass calculation, in RPD you can have 2-pass modelling / SQL generation - Reusable “models” by using OBIEE as source for OBIEE  Fragmentation: extend the OOTB feature by a more flexible solution - the “WHERE clause” - also a solution for security implementation
  30. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com RPD – 2-pass modelling  Never thought it would be nice to “reuse” a RPD model to do another one based on it?  Or to be able to use some of the business model derived columns next to some physical tables to produces something else?  Or to split the modelling of a complex physical source (maybe an application or a pure transactional model) in multiple steps to be able to test and validate piece by piece the logic?  It’s possible!  And not just by some advanced RPD modelling  How?  5 steps…
  31. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com RPD – 2-pass modelling  1) Model some physical source(s) in a Business Model and the related Subject Area  2) Test it doing some analysis on it to get data  3) Write the required LSQL to get the info you look for  4) In the RPD create a new physical source using ODBC on OBIEE itself  5) Create a “view” source containing a LSQL query  Define columns based on the expected result (and data types)  Use it as a normal physical source object
  32. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com RPD – 2-pass modelling  The result?  Reusable RPD models  Example: flatten out a parent-child on a dynamic number of dimensions (for example the outline in the relational schema of an Hyperion Planning application)  A table contains parent-child objects (members)  A table contains the list of “root” elements (dimensions)  Re-use the logic to make a parent-child dimension flat for every single dimension without doing it by hand for every single dimension  A new dimension will require only to create a new alias or physical view object  If new levels required => add in a single place  NQS calls can be used in that way (as Christian Berg presented at KScope14)
  33. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com RPD – 2-pass modelling  Time to try it …
  34. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com RPD – 2-pass modelling : demo
  35. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com RPD – 2-pass modelling : demo
  36. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com RPD – Fragmentation  The default fragmentation support simple fragmentations rules  Multiple limits  The columns used in the fragmentation rule must be in the analysis - If an analysis doesn’t include any of the fragmentation column then all the LTS are matched - Time series functions aren’t able to use fragmented time dimensions correctly - Limited operators and logic for the condition
  37. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com RPD – Alternative fragmentation  Using the WHERE clause allow for more powerful conditions  Allow to implement “fake” fragmentation allowing the analysis to drive which LTS to use  Alternative implementation of security  Provide a secured dimension  In parallel to a non-secured one  The analysis can freely decide which dimension to use based on profile, roles etc.  Attention: less strict than row level security (the developer can use the non-secured LTS by mistake)  The downside is that all the LTS are used by the query and it’s the DB not returning any result based on WHERE conditions  Sending a kind of “WHERE 1=2” condition to avoid matching a LTS
  38. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com RPD – Alternative fragmentation  Time to try it …
  39. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com RPD – Alternative fragmentation : demo “Dim Org unit”.”Security” is a kind of fake column, exists only in the BMM and has hardcoded values (one for each LTS) and it’s used to have a “Fragmentation content” formula and avoid the Admin Tool to raise a warning when missing. Also used to drive the selection of the LTS from the Analysis itself if required.
  40. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com RPD – Alternative fragmentation : demo “Dim Calendar”.”Src table” is a kind of fake column, exists only in the BMM and has hardcoded values (one for each LTS) and it’s used to have a “Fragmentation content” formula and avoid the Admin Tool to raise a warning when missing. Also used to drive the selection of the LTS from the Analysis itself if required.
  41. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com RPD – Alternative fragmentation : demo
  42. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com Thank you for attending !
  43. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com Gianni Ceresa, Rittman Mead ODTUG KScope15, June 2015 2-in-1: RPD Magic and Hyperion Planning “Adapter”
  44. T : +44 (0) 1273 911 268 (UK) or (888)

    631-1410 (USA) or +61 3 9596 7186 (Australia & New Zealand) or +91 997 256 7970 (India) E : info@rittmanmead.com W : www.rittmanmead.com