How to Integrate OBIEE11g and Essbase/EPM
Mark Rittman, Technical Director, Rittman Mead
Oracle Openworld 2012, San Francisco, September 2012
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Mark Rittman
• Mark Rittman, Co-Founder of Rittman Mead
• Oracle ACE Director, specialising in Oracle BI&DW
• 14 Years Experience with Oracle Technology
• Regular columnist for Oracle Magazine
• Author of forthcoming Oracle Press book on OBIEE 11g
• Writer for Rittman Mead Blog :
http://www.rittmanmead.com/blog
• Email : [email protected]
• Twitter : @markrittman
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
About Rittman Mead
• Oracle BI and DW gold partner
• World leading specialist partner for technical excellence, solutions delivery and
innovation in Oracle BI
• Approximately 30 consultants worldwide
• All expert in Oracle BI and DW
• UK based
• Offices in US, Europe (Belgium) and India
• Skills in broad range of supporting Oracle tools:
‣ OBIEE
‣ OBIA
‣ ODIEE
‣ Essbase, Oracle OLAP
‣ GoldenGate
‣ Exadata
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Oracle Business Intelligence 11gR1
• The latest release of Oracle’s business intelligence platform
• Replacement for Discoverer; full platform for analytics and dashboards
• Now at version 11.1.1.6.2 BP1 / 11.1.1.6.4; originally derived from Siebel Analytics
• 11gR1 new features focused on OLAP analysis, visualizations, manageability
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Web-Based Delivery of Analytics and Analysis
• Users access information and analytics through role-based dashboards
• Can also publish reports to PDF, distribute, or access through MS Office
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Web-Based Delivery of Analytics and Analysis
• Users access information and analytics through role-based dashboards
• Can also publish reports to PDF, distribute, or access through MS Office
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Web-Based Delivery of Analytics and Analysis
• Users access information and analytics through role-based dashboards
• Can also publish reports to PDF, distribute, or access through MS Office
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Web-Based Authoring Tools for “Power Users”
• Reports and analyses are created using web-based tools
• Point-and-click creation of tables, pivot-tables, charts, maps, gauges
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Web-Based Authoring Tools for “Power Users”
• Reports and analyses are created using web-based tools
• Point-and-click creation of tables, pivot-tables, charts, maps, gauges
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Integration with E-Business Suite and Other Applications
• OBIEE 11.1.1.6.x is the platform for Oracle Business Intelligence applications
• Pre-built reports, dashboards and analytics for EBS and other applications
• Pre-built dimensional data warehouse and ETL routines
• Extensible and open design
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Mobile and Other Advanced Visualizations
• Mobile client for Apple iPad and iPhone
• Delivery of reports and alerts via email, cell, pager etc
• High-density visualizations via a thin-client web dashboard
• Integration with MS Office and Open Office through SmartView
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
All Built Using Shared “Common Enterprise Information Model”
• All reporting tools can take their data from a shared metadata layer
• Defined as dimensional model, embeds common calculations and drill paths
• Subject-area and row-level security
• Three-layer design to provide
abstraction from underlying
complicated source systems
Simplification of the Data Model
Integration of Disparate DataSources
Addition of Business Logic and Calculations
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
OBIEE 11g Features for Hyperion Users
• Essbase as a data source for Oracle BI Repository
• Oracle Answers as a front-end for Essbase, HFM
• Integration of Essbase and relational data into drill-through models
• Oracle BI Repository as a data source for Essbase Studio
• Plus access to OBIEE dashboards & reporting
‣ Wide range of graphs and other visualizations
‣ Mapping and spatial integration
‣ Scorecards and KPIs
‣ Web Service and Application Integration
‣ Access to EBS, PSFT, Siebel and SAP data
through Oracle BI Applications
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Supported Essbase/Hyperion Releases
• Oracle Essbase 9.3.3+, 11.1.1.3.500, 11.1.2+
• HFM 9.3.3, 11.1.1.3, 11.1.2.x
• Planning - tbc
• See Oracle Fusion Middleware Supported System Configurations doc on OTN
‣ http://www.oracle.com/technetwork/middleware/ias/downloads/fusioncertification-100350.html
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Essbase Integration with OBIEE
• OBIEE can connect to multidimensional as well as relational sources
‣ Microsoft Analysis Services, SAP B/W, Essbase
• Import process converts the multidimensional view into a relational view
• Essbase cubes, dimensions, hierarchies converted to a logical relational star schema
• Essbase data can then be combined
with data from sources
• OBIEE 11g introduces new OLAP query
capabilities making Answers a first-class
ad-hoc query tool for Essbase cubes
• Other use case is to bring in budget,
forecast data etc from Essbase
and use to enhance relational data
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
New in OBIEE 11.1.1.6.2 BP1
• Just released - Essbase as an OBIEE aggregate persistence target
• Store OBIEE aggregates in an ASO cube,
automatically created and populated by
embedded Essbase Studio
• Closer integration around the install,
management with EM
• Narrow focus - aggregate persistence but pointer to further integration
• More details later on...
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
New Essbase Cube and Metadata Features in OBIEE 11gR1
• Essbase outlines are still imported in and converted to relational stars
• However outlines are now handled better
‣ Accounts dimensions keep their hierarchy
‣ Parent-child (value-based) hierarchies are supported
‣ Alias tables supported
‣ UDAs supported
• No longer any need to recreate as outline
evolves (additional generations, members)
• Makes OBIEE a more practical reporting
environment for financial-style Essbase cubes
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Importing an Essbase Cube Step 1 : Review Essbase Outline
• Review the Essbase outline using Essbase Administration Services
• Be aware of the number and type of dimensions
• Check usage of alias tables, UDAs etc
• Be sure to use correct Essbase versions
‣ Other releases will partially work, but
drilling etc may not work
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Importing an Essbase Cube Step 2 : Select Metadata Import
• Using BI Administration tool, select File > Import Metadata
• Enter connection details for Essbase Server (requires Essbase Client to be
installed on server and desktop)
• Select cube for import, check Import UDAs if UDAs are present in cube
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Importing an Essbase Cube Step 3 : Review Import
• Review imported cube metadata
‣ Accounts dimension is now a hierarchy
‣ Single measure dimensioned by all
dimensions
‣ Columns still based on outline
dimension generations
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Importing an Essbase Cube Step 4 : Convert To Parent-Child
• Imported level-based hierarchies can be convered to value-based
‣ Protects against outline evolution, new levels (generations) being added
• Right-click on dimension, select Properties > Levels,
then change Hierarchy Type to Value
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Importing an Essbase Cube Step 5 : Add Alias Columns
• Where aliases are present in the outline, these can be brought through to OBIEE
• Right-click on dimension, select Create Columns for Alias Table
• Select alias to create column for; alias then appears in physical database
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Importing an Essbase Cube Step 6 : Create Remainder of Model
• Once physical model is created, drag and drop over to business model and
mapping layer, and then presentation layer
• Essbase data is then ready
for reporting on using Answers
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
New Essbase Reporting Features in OBIEE 11g
• OBIEE 11g comes with a number of improvements for reporting against Essbase
• Hierarchical column provide OLAP-style
drilling against Essbase cubes
• Selection steps and hierarchical prompts
provided member-based selections
• UDAs can be used in filters
• Value-based hierarchies remove need
to query specific levels to select
members for analysis
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Essbase and Hierarchical Columns
• Essbase cubes now use Hierarchical Columns by default
• Full drill into detail (symmetric / asymmetric drilling)
• Combine with regular attribute columns
• Works across both
relational and OLAP sources
‣ MOLAP and ROLAP
analysis combined
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
P&L-Style Reports
• Though Financial Reporting is the most suitable tool for P&L-style reports, Answers
using hierarchical columns can reproduce a lot of their functionality
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Access to OBIEE Graphing, Visualisations
• Full access to all OBIEE visuals, maps, graphs, dashboard objects, scorecards etc
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Selection Steps and Essbase
• The OBIEE 11g Selection Steps feature can be used to select members
based on a hierarchy
• Select individual members, drill into hierarchy
• Select members based on
position in a hierarchy
‣ Select Children Of
‣ Select Ancestors of
‣ Keep/Remove members
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
New in OBIEE 11.1.1.6 : Add Selection Steps From Dashboard
• Selection steps can be added by the analysis developer
• Or can be added by users from the dashboard view
‣ Add-only, no ability to remove
‣ Subset of full range of selection steps
‣ Selections can be saved
for a particular user as a
dashboard customization
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Filtering against UDAs
• User-Defined Attributes can be accessed via filters
‣ UDAs are not displayable like columns, they instead act as filters
• Add filter based on UDA = 1 (true) or false (0)
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Displaying Aliases
• Aliases appear in the subject area just like other columns
‣ Have to be imported into the RPD, see previous steps
• Can be used just as any other column
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Handling Essbase Alternate Hierarchies
• Alternate hierarchies can be imported from Essbase ASO cubes (not BSO)
• Dimension must be multiple hierarchy enabled in the outline
• Multiple hierarchies are then imported into the RPD and created separately, based off of
the same logical dimension table
1
3
2
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Handling Attribute Dimensions
• Attribute dimensions are imported into the RPD just like regular Essbase dimensions
• Can be queried in analyses alongside regular dimension members/logical columns
• Typically attribute dimensions are nested within the “parent” dimension
within the presentation layer of the RPD
• Can be queried separate from the “parent” dimension, or alongside it
3
1
2
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Essbase Security and Filters
• Data filters and Meta Filters are respected by OBIEE
• SSO integration between Workspace and OBIEE Presentation Server removed in 11g
• SSO still possible but requires external solution (until 11.1.1.6+?)
‣ Shared Services and OBIEE both connecting to same external LDAP server
‣ External SSO over both OBIEE and EPM products
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
OBIEE 11g Integration with Workspace and Shared Services
• OBIEE 10g could be integrated with EPM Workspace to create a single “portal”
• Oracle BI Server authenticated against Shared Services using Custom Authenticator
• Point-to-Point SSO between Workspace and OBIEE (via CSS Token)
• But this doesn’t work with OBIEE 11g :-(
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
OBIEE 11g / Shared Services Integration Options
• Ideal solution is to connect OBIEE’s WLS security services to the LDAP
directory that was linked to Shared Services (OID, AD etc)
‣ Assumes users, groups not stored in Shared Services’ Native Directory
• Replace the Workspace/OBIEE point-to-point SSO with an overall SSO solution
‣ Oracle Access Manager, etc
• Still no solution for Workspace/OBIEE
portal integration (post 11.1.1.7?)
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Alternative Solutions for OBIEE / Shared Services Integration
• If it’s not possible to share the same LDAP directory, other alternatives are possible
• For example (example requires OBIEE 11.1.1.5+):
‣ Use the HSS Security API to sync Native Directory entries to tables
‣ Use SQLAuthenticator WLS adapter to perform authentication against user tables
‣ Use BISQLGroupProvider authenticator to obtain group membership from these
tables
• Sync from Shared Services to
DB tables every 5 mins
• Still Need to manually create and
map application roles
• Also still does not address SSO
requirement though (or
Workspace/OBIEE portal integration)
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
HSS Security Java API: Example Code for Extracting Groups Mbrs
protected void dumpNativeGroupMembers (String URL,String USER, String PASSWORD) {
try{
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(URL,USER,PASSWORD);
Get list of
conn.setAutoCommit(false);
groups from
CSSGroupIF[] cssGroupIF = cssAPI.getGroups(context, principal, "*");
HSS
CSSUserIF cssuser;
CSSGroupIF cssgroup;
String []user = null;
String []group = null;
Statement stmt = conn.createStatement();
Clear down
stmt.executeUpdate("DELETE GROUPS");
existing entries
stmt.executeUpdate("DELETE GROUPMEMBERS");
from tables
for (int i=0; i <cssGroupIF.length; i++){
try{
System.out.println ("EPM Groups + Name + Identity: "+ "+ "+ cssGroupIF[i].getName()+" + "+cssGroupIF[i].getIdentity());
Get list of
System.out.println("INSERT INTO GROUPS(G_NAME) VALUES ('"+cssGroupIF[i].getName()+"')");
groups from HSS
stmt.executeUpdate("INSERT INTO GROUPS(G_NAME) VALUES ('"+cssGroupIF[i].getName()+"')");
Populate
user = cssGroupIF[i].getUsersList(context, principal, true);
GROUPS table
for (int j=0; j < user.length; j++) {
try{
cssuser = cssAPI.getUserByIdentity(context, principal, user[j]);
System.out.println("Users in "+cssGroupIF[i].getName()+" Group: "+cssuser.getLoginName());
System.out.println("INSERT INTO GROUPMEMBERS(G_NAME,G_MEMBER) VALUES ('"+cssGroupIF[i].getName()+"','"+cssuser.getLoginName()+"')");
stmt.executeUpdate("INSERT INTO GROUPMEMBERS(G_NAME,G_MEMBER) VALUES ('"+cssGroupIF[i].getName()+"','"+cssuser.getLoginName()+"')");
}catch(Exception ex){System.out.println(ex.getMessage());}
}
Populate
group = cssGroupIF[i].getGroupList(principal, true);
GROUPMEMBERS
for (int k=0; k < group.length; k++) {
table
try{
cssgroup = cssAPI.getGroupByIdentity(context, principal, group[k]);
System.out.println("Groups in "+cssGroupIF[i].getName()+" Group: "+cssgroup.getName());
System.out.println("INSERT INTO GROUPMEMBERS(G_NAME,G_MEMBER) VALUES ('"+cssGroupIF[i].getName()+"','"+cssgroup.getName()+"')");
stmt.executeUpdate("INSERT INTO GROUPMEMBERS(G_NAME,G_MEMBER) VALUES ('"+cssGroupIF[i].getName()+"','"+cssgroup.getName()+"')");
}catch(Exception ex){System.out.println(ex.getMessage());}
}
}catch(Exception ex){System.out.println(ex.getMessage());}
Commit changes
//rs.close();
to tables
}
conn.commit();
}catch(Exception ex){
System.out.println(ex.getMessage());
}
}
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
New in OBIEE 11.1.1.6.2 BP1 : Aggregate Persistence in Essbase
• OBIEE’s Administration tool has the
ability to define aggregates, and then
persist them in an RDBMS
• Repository then uses these aggregates
to speed up dashboard queries
• Basis of the Summary Advisor used with
Oracle Exalytics In-Memory Machine
• Latest OBIEE bundle patch enables
storage of these aggregates in an
Essbase ASO database, using
headless Essbase Studio
User requests
from dashboard,
ad-hoc queries etc
HTTP(S)
BI Presentation Server
Component
Logical SQL
Physical
SQL, MDX
Results
Detail-level, and dynamicallycalculated
aggregate data
Oracle BI Server
Component
Pre-computed aggregated
data, stored in an Essbase
ASO database
Read model
Oracle BI Repository
Logical Business
Model
Physical
MDX
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
So How Does Aggregate Persistence in Essbase Work?
1. Developer/Administrator selects slice of business model to aggregate
2. Aggregate Persistence Wizard then creates a logical SQL script
3. Script is processed by BI Server using nqcmd and the BI Server ODBC client
4. BI Server uses Essbase Studio dmaservlet to create ASO outline, and rules file
5. Detail-level data sourced via BI Server logical model through to source databases
1
nqcmd script
containing logical
DDL and DML for
creating and populating
aggregate tables
Oracle BI Repository
Logical Business
Model
Read model
Developer uses
BI Administrator
tool to select
aggregates for
creation, with an
output of a logical
SQL script for DDL
and DML
Logical SQL
via BI Server
ODBC interface
2
Oracle BI Server
Component
XML request
Add aggregates
3
Physical
SQL, MDX
5
SQL queries
Create outline
Essbase Studio
4
dmaservlet
Essbase Server
Data load via
Essbase rules file
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Combined Install of OBIEE and Essbase with 11.1.1.6.2 BP1
• OBIEE 11.1.1.6, and the 11.1.1.6.2 BP1 bundle patch, include Essbase + administration
tools as part of the install bundle, but these are not by default enabled
• Needs to be patched to 11.1.1.6.2 BP1, then installed using options in a reponse file
• Configures Essbase as part of the OBIEE domain, with EAS and Studio also available
• Narrow use-case : only for Aggregate Persistence, cannot scale-up, repurpose etc
[DATAMART_AUTOMATION]
ESSBASE_STUDIO_URL = "http://localhost:9080/dma/dmaservlet";
ESSBASE_SERVER = "localhost";
DMA_DATABASE = "DMA_DB";
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
New Capabilities Provided by OBIEE 11g
• Horizontal (Drill-Across) and Vertical (Drill-down) Integration with Relational Data
• MapViewer Integration
• Scorecards and KPIs
• BI Publisher
• Action Framework
• Integration with EBS and other application data
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Drill-Across and Drill-Through To Relational Data
• One of the most powerful features in the OBIEE it is ability to combine data sources
• Multiple data sources presented as a single logical dimensional model
• In terms of Essbase and HFM integration there are three main use-cases:
1.Providing drill-down from an Essbase-based model to detail-level relational data
2.Displaying relational attributes and descriptive data alongside Essbase measures
3.Combining Essbase and relational measures into a single business model
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Setting up Vertical Federation (Drill-to-Relational Detail)
• Initially map in Essbase source (using wizard), then introduce relational data into model
• Be aware of Essbase unique naming requirements (may need to prefix relational name)
• Create level-based logical dimension
to indicate hierarchy levels
• Set content (hierarchy) level for Essbase
and Relational sources to indicate
granularity (how detailed they are)
• BI Server will then automatically switch
between Essbase and Relational sources,
depending on how detailed the report gets
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Reporting at Aggregated (Essbase) Level
• Reports that then run at the aggregated level will automatically use Essbase
• This is because it is mapped in to the Customer dimension at a higher (non-leaf) level
With
set [Channel3] as '[Channel].Generations(3).members'
set [Customers5] as '[Customers].Generations(5).members'
select
{ [Measures].[Amount]
} on columns,
NON EMPTY {crossjoin ({[Channel3]},{[Customers5]})} properties
ANCESTOR_NAMES, GEN_NUMBER on rows
from [SH.SH]
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Reporting at Detailed (Relational) Level
• Reports where the user drills to transaction level then switch the relational source
• This happens because the relational source is the only one that can provide
customer data at the required level
select D1.c1 as c1,
D1.c2 as c2, D1.c3 as c3, D1.c4 as c4,
D1.c5 as c5, D1.c6 as c6, D1.c7 as c7
from
(select T1915.COUNTRY_NAME as c1,
T1927.CUST_CITY as c2,
T1927.CUST_EMAIL as c3,
T1927.CUST_CREDIT_LIMIT as c4,
concat(concat(T1927.CUST_FIRST_NAME, ' - '),
T1927.CUST_LAST_NAME) as c5,
T203.CHANNEL_CLASS as c6,
sum(T1960.AMOUNT_SOLD) as c7,
T1927.CUST_ID as c8
from
COUNTRIES T1915,CUSTOMERS T1927,
CHANNELS T203,SALES T1960
where ( T203.CHANNEL_ID = T1960.CHANNEL_ID
and T1915.COUNTRY_ID = T1927.COUNTRY_ID
and T1915.COUNTRY_NAME = 'Australia'
and T1927.CUST_CITY = 'Darwin'
and T1927.CUST_ID = T1960.CUST_ID )
group by T203.CHANNEL_CLASS, T1915.COUNTRY_NAME,
T1927.CUST_CITY, T1927.CUST_CREDIT_LIMIT,
T1927.CUST_EMAIL, T1927.CUST_ID,
concat(concat(T1927.CUST_FIRST_NAME, ' - '),
T1927.CUST_LAST_NAME) ) D1
order by c1, c2, c3, c4, c5, c6
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Vertical Federation Limitations
• Only Essbase outlines that are balanced and level-based can be federated
‣ Unbalanced/ragged hierarchies do not consistently map to BMM columns
‣ Value-based (parent-child) hierarchies will not federate due to lack of “levels”
• Therefore Essbase cubes that “look like” relational cubes are the best to federate
• Typically sales analysis, and cubes built to support OBIEE
• There is no “refresh” facility to bring in changed outlines into OBIEE
• Therefore only “stable” outlines are suitable for OBIEE
• No issues over ASO, BSO etc
• Versions of Essbase supported back to 7.x
‣ Though you will need supported versions to be able to use hierarchical columns
‣ Essbase 9.3.3+, 11.1.1.3.500, 11.1.2+
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Setting up Horizontal Federation (Relational alongside Essbase)
• You may have actuals data in a relational database, and forecast/budget data in
Essbase
• It would be useful to report on these in a combined business model
• Measures of the same dimensionality can go
in the same fact table
• Measures of differing dimensionality go in
separate fact tables
• Users see a single combined data source
• Oracle BI Server combines the data at runtime
‣ New in 11g - can use LOOKUP function
to retrieve relational descriptors etc
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Displaying Essbase & Relational Data Together on Dashboards
• Relational and Essbase data can be displayed together in analyses, dashboards
• Navigation can be set up between Essbase aggregated data and relational detail
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
So Can OBIEE Replace WebAnalysis, Planning, Financial Mgmt etc?
• OBIEE analyses, dashboards can replace all functional aspects of Web Analysis
• FR is still preferable for non-trivial financial reports
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
... and What Issues Are Still Outstanding?
• A number of minor, and major, issues are still outstanding for Essbase / EPM users
• Some are minor and will be addressed with bug fixes, enhancements
‣ Requirement for multiple WLS homes, EM environments
‣ Some early-release bugs around value-based hierarchies, drilling, formulas etc
‣ Make sure you are on the latest release, with all patches - currently 11.1.1.6
• Some are just limitations of how things work
‣ Cannot federate value-based hierarchies
‣ Slower queries and analysis compared to Smartview + Essbase Add-in
• Some are fundamental and require re-engineering of OBIEE
‣ Complex and fragmented MDX when using hierarchical columns
‣ Excessively expensive MDX when using selection steps
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
The Hierarchical Columns and Pivoting Issue
• Hierarchical columns generate one logical query per grouping
• For SQL queries, these are combined into a single SQL statement
• For Essbase queries, each grouping generates it’s own MDX query, plus others
With
set [_Products1] as '{Distinct({[Products]})}'
set [_Products2] as 'Generate({[_Products1]},[Products].currentmember.children)'
select
{ [Measures].[Gross Revenue]
} on columns,
NON EMPTY {{[_Products2]}} properties GEN_NUMBER, [Products].[Memnor], [Products].[Ancestor_Names],
[Products].[MEMBER_UNIQUE_NAME], [Products].[LEVEL_NUMBER] on rows
from
With[BISAMPLE.Sample]
set [_Axis1Set] as '{Distinct({[Products]})}'
select
{} on columns, {[_Axis1Set]} properties GEN_NUMBER, [Products].[Memnor], [Products].
[MEMBER_UNIQUE_NAME] on rows
from [BISAMPLE.Sample]
With
set [_Axis1Set] as '{[Products].Generations(1).members}'
select
{} on columns, {[_Axis1Set]} properties GEN_NUMBER, [Products].[Memnor], [Products].
[MEMBER_UNIQUE_NAME] on rows
from [BISAMPLE.Sample]
1
2
3
With
set [_Axis1Set] as '{Distinct({[10002]})}'
select
{} on columns, {[_Axis1Set]} properties GEN_NUMBER, [Products].[Memnor], [Products].
[Ancestor_Names], [Products].[MEMBER_UNIQUE_NAME] on rows
from [BISAMPLE.Sample]
With
set [_Products2] as '{Distinct({[10002]})}'
set [_Products3] as 'Generate({[_Products2]},[Products].currentmember.children)'
select
{ [Measures].[Gross Revenue]
} on columns,
NON EMPTY {{[_Products3]}} properties GEN_NUMBER, [Products].[Memnor], [Products].[Ancestor_Names],
[Products].[MEMBER_UNIQUE_NAME], [Products].[LEVEL_NUMBER] on rows
from [BISAMPLE.Sample]
4
5
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Issue Compounded by > Hierarchical Column
• Issue becomes worse when >1 hierarchical column & pivoting is included in analysis &
• Many more sub-groupings are created leading to excessivly expensive SQL
• Workaround #1 : Size your system to expect greater numbers of small MDX queries
• Workaround #2 : Try to limit analyses to a single hierarchical column on row-edge
• Workaround #3 : Enable caching - 11g subquery caching can significantly improve perf.
=58 separate
MDX queries
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
The Selection Steps Issue
• Selection steps (member steps) are used extensively in 11g for Essbase data
• Provides an Essbase-like environment for filtering, selecting and calculating data
3
1
2
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Selection Steps Multiply the Number of Physical MDX Generated
• Issue is that selection steps will multiple the number of physical MDXs sent to Essbase
‣ Issues also affects relational sources, but SQLs are usually combined into one
• # logical SQLs = (# steps in Dim1 x # steps Dim 2 x # steps Dimn) + Grand Total SQL
• # MDX queries = 1 or 2 per logical SQL
• Coupled with hierarchical columns & pivoting, can cause excessive MDX query load
With
set [_Market2] as '{Distinct({[East], [West]})}'
set [_Year2] as '{Distinct({[Qtr1], [Qtr2]})}'
select
{ [Measures].[Sales]
} on columns,
NON EMPTY {crossjoin({[_Market2]},{[_Year2]})} properties GEN_NUMBER, [Market].[LEVEL_NUMBER],
With
[Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor], [Market].[MEMBER_UNIQUE_NAME], [Market].[Memnor], [Year].
set [_Market2]
as '[Market].Generations(2).members'
[LEVEL_NUMBER]
on rows
[_Year2] as '{Distinct({[Qtr1], [Qtr2]})}'
fromset
[Sample.Basic]
select
{ [Measures].[Sales]
With
} on columns,
set [_Market2]
as '[Market].Generations(2).members' properties GEN_NUMBER, [Year].[MEMBER_UNIQUE_NAME],
NON EMPTY {crossjoin({[_Market2]},{[_Year2]})}
set [Year].[Memnor],
[_Year2] as '{Distinct({[Qtr1],
[Qtr2]})}'
[Market].[MEMBER_UNIQUE_NAME],
[Year].[LEVEL_NUMBER] on rows
selectfrom [Sample.Basic]
{ [Measures].[Sales]
} on columns,
NON EMPTY {crossjoin({[_Market2]},{[_Year2]})} properties GEN_NUMBER, [Year].[MEMBER_UNIQUE_NAME],
[Year].[Memnor], [Market].[MEMBER_UNIQUE_NAME], [Year].[LEVEL_NUMBER] on rows
from [Sample.Basic]
With
set [_Market2] as '[Market].Generations(2).members'
set [_Year2] as '[Year].Generations(2).members'
select
{ [Measures].[Sales]
} on columns,
NON EMPTY {crossjoin({[_Market2]},{[_Year2]})} properties GEN_NUMBER, [Year].[MEMBER_UNIQUE_NAME],
[Market].[MEMBER_UNIQUE_NAME] on rows
from [Sample.Basic]
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
MDX Generation Improvements During 11g Timeline
• MDX generation has improved over the 11g releases
‣ Often through patches after the main point release
‣ Many in response to specific customer issues (varies by release and Essbase version)
• Post 11.1.1.5, some significant changes have started to appear
‣ Combining of MDX statements
‣ Improvements to MDX when pivot tables and selection steps combined
‣ Better handling of value-based hierarchies (with other value-based, and level-based)
• When dealing with sub-optimal MDX, ask yourself:
‣ Have I applied all the latest patches - engage with Oracle support, be on latest version
‣ Can I rework the RPD or analysis (filter instead of pivoting etc) to improve it?
‣ If you’re on an early point release (11.1.1.6 etc) are there known issues addressed
by the first, upcoming patch set (11.1.1.6.2 BP1), e.g. keep only/remove only steps
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
The Value-Based Hierarchy Issue (or “Gotcha”)
• Value-based (Parent-Child) hierarchy support are a useful addition in OBIEE 11g
• Allows member searches to work against single logical column
• More “natural” way of holding member data for Essbase users
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Value-Based Hierarchies and Vertical Federation
• Given the benefits of value-based hierarchies for changing outlines, customers
often enable them for all dimensions in OBIEE 11g ...
• ... and then want to set up drill-to-detail analyses using vertical federation
• ... which require defined levels to join logical table sources together
• ... which value-based hierarchies do not have
• Workaround : Analyze Essbase and Relational separately, link by actions that
pass hierarchical column selection values through hidden attribute columns
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
New Ways of Doing Things
• Most Essbase users access data through the Excel Add-in
• Uses Query-by-Example approach to retrieve data
• Combined with Excel flexibility, gives lots of options to access data
• To produce similar reports, different approaches are required with OBIEE
‣ Use of FILTER and time-series functions
‣ Adaptation for structured cross-tab style interface vs. free-form Excel
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Combining OBIEE Functions and Essbase Data
• Time-offsets can be generated using OBIEE Time-Series functions
‣ AGO, TODATE, PERIODROLLING
• Intersections of dimension members (Accounts, Scenarios) using FILTER function
• Combine OBIEE analytic functions with Essbase member references
• Requires learning OBIEE function syntax and combining with Essbase skills
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
OBIEE Analytic Function Examples
• FILTER(measure USING expr)
‣ Allows you to filter generic
measure by dimension member,
i.e. measure by Profit account,
and Budget scenario
• AGO, TODATE, PERIODROLLING
‣ Generate year-to-date,
period ago, rolling total
‣ Pushes down to equivalent MDX
• EVALUATE, EVALUTE_AGGR
‣ Leverage native MDX functions
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Excel Add-in/SmartView and OBIEE are Complementary Tools
• OBIEE does not remove need for Excel add-in or SmartView
• Common mistake is to think it’s “all-or-nothing”
• Still a role for Excel add-in for finance/analyst use
• OBIEE is great for mass-distribution of financial data, combining it with other data
• Accessing Essbase data requires combined Essbase / OBIEE skills
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
Summary
• OBIEE 11g introduces a number of new features Essbase customers were waiting for
‣ Proper OLAP analysis with hierarchical member selection
‣ Support for value-based hierarchies, so that outline changes don’t need re-import
‣ Support for multiple aliases, UDAs, multiple hierarchies, filters etc
• OBIEE also provides many additional features that will benefit Essbase customers
‣ Mapping, scorecards, better visualizations
‣ Action Framework, integration with Fusion Middleware
• But the 11g release also brings its own new issues
‣ Excessive number of MDX statements for pivoting OLAP-style queries
‣ Selection steps can exacerbate this issue by exponentially adding more MDX queries
‣ Value-based hierarchies have their own “gotchas”
• Don’t try to 100%-replace Excel Add-in With OBIEE
‣ Tools are complementary, have their own strengths
‣ Be prepared to learn OBIEE dev techniques as well as Essbase ones
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
More Information
• Thank you for attending this presentation
• More information can be found at http://www.rittmanmead.com
• Contact us at [email protected] or [email protected]
• Look out for our book, “Oracle Business Intelligence Developers Guide” due Q1 2012
• Follow-us on Twitter (@rittmanmead) or Facebook (facebook.com/rittmanmead)
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12
How to Integrate OBIEE11g and Essbase/EPM
Mark Rittman, Technical Director, Rittman Mead
Oracle Openworld 2012, San Francisco, September 2012
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Saturday, 29 September 12