2-in-1 : rpd magic and hyperion planning "adapter"
TRANSCRIPT
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)
W : www.rittmanmead.com
Gianni Ceresa, Rittman Mead
ODTUG KScope15, June 2015
2-in-1: RPD Magic and Hyperion Planning “Adapter”
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)
W : www.rittmanmead.com
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)
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: [email protected]
twitter: @G_Ceresa
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)
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
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)
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 …
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)
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
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)
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
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)
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)
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)
W : www.rittmanmead.com
A reminder: what is Hyperion Planning?
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)
W : www.rittmanmead.com
A reminder: what is Hyperion Planning?
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)
W : www.rittmanmead.com
A reminder: what is Hyperion Planning?
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)
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
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)
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.
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)
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
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)
W : www.rittmanmead.com
Import Metadata : error …
It doesn’t work …
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)
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…
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)
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
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)
W : www.rittmanmead.com
Does it work? Planning analysis sample in OBIEE
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)
W : www.rittmanmead.com
Planning analysis sample in OBIEE
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)
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
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)
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
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)
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)
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)
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
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)
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
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)
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 ?
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)
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…
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)
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
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)
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.
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)
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)
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)
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
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)
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…
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)
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
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)
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)
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)
W : www.rittmanmead.com
RPD – 2-pass modelling
Time to try it …
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)
W : www.rittmanmead.com
RPD – 2-pass modelling : demo
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)
W : www.rittmanmead.com
RPD – 2-pass modelling : demo
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)
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
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)
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
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)
W : www.rittmanmead.com
RPD – Alternative fragmentation
Time to try it …
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)
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.
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)
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.
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)
W : www.rittmanmead.com
RPD – Alternative fragmentation : demo
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)
W : www.rittmanmead.com
Thank you for attending !
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)
W : www.rittmanmead.com
Gianni Ceresa, Rittman Mead
ODTUG KScope15, June 2015
2-in-1: RPD Magic and Hyperion Planning “Adapter”
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)
W : www.rittmanmead.com