Transcript Slide 1

The Florida State University
Transactional Reporting in the real
world with OBIEE
Session #27777
March 2, 2010
[NQODBC] [SQL_STATE: S1000]
[nQSError: 10058] A general error has
occurred.
[nQSError: 14026] Unable to navigate
requested expression. Please fix the
metadata consistency warnings.
The Florida State University
Current Enrollment 40,255
Florida State University
…is a premier, comprehensive,
graduate research university,
with both law and medical
schools.
 Annual Operating Budget: $1.1B
 Over 40,000 students
 Over 13,000 employees
 Over 13,000 biweekly paychecks
 Over $18 million in biweekly payroll
Overview
•
•
•
•
•
•
•
•
•
Introduction to FSU’s BI Initiative
Overview of FSU’s OBIEE Implementation
Current BI State
What is Transactional/OLTP Reporting?
Physical/Logical Layer Deployment Issues
Answers… Just turn it on!
Tuning in the real World; Why is it SOOOO slow?
Closing Tips
Questions & Comments
FSU and Oracle PeopleSoft
• Implemented Financials 8.4, Portal 8.8, and
EPM 8.8 in June 2004
• Implemented HR/Payroll 8.8 in Dec 2004
• Upgraded HR & EPM Suites to 8.9 in April 2006
• Upgraded FI Suite to 8.9 in Nov 2006
• Upgraded EPM & Portal Suites to 9.0 in Nov 2007
• Upgraded HR Suite to 9.0 in Oct 2008
• Upgraded FI Suite to 9.0 in April 2009
• Deployed EPM 9.0 & OBIEE 10.1.3.3(Windows) in
March 2008
• Upgraded OBIEE 10.1.3.4(Linux) in April 2009
FSU’s BI Profile
• Deployed new BI Solution 2 years ago
• Solution meets the reporting needs of our the major
Administrative organizations on Campus
• Consists of 25 Dashboards and 27 Subject Areas
• Over 1200 distinct users & 2.5 Million Requests
• Dashboard Consumption & Self Service Reporting
• Currently in Phase III of our Deployment
(Standardization of Campus BI on OBIEE)
• 2009 Oracle Innovation Award Recipient
Current BI Development State
What is Transactional Reporting?
Simply put, reporting against any source database
which stores information outside of the normal
constructs of a dimensional data warehouse model.
Achieving success… The easy way!
• Planning of Subject areas is a must
– Business to source column mapping is key
• You just can’t live without self service reporting…
unless you have an unlimited budget for report
developers.
• Stop, Drop and Roll is no way to deploy a BI
Solution
• Subject Areas should be released in a phased
approach
Achieving success… The easy way!
• Stars are the Goal… Even in the transactional world
Achieving success… The easy way!
• Subject area content should be grouped by business
process.
– Subject Areas typically align tightly with
Dashboard Structure.
– A Self Service user should be able to get at the
data he/she needs to create business appropriate
reports without tracking through 50 tables.
– To many subject areas and you have another BIG
problem; complexity.
Physical/Logical Layer Issues
Now for all of the “Techie” stuff!
Physical Layer Issues
• Circular Joins
– Typically happens when a table has
more than one route to complete a
join.
– Always import tables without FKs
turned on.
– Can be resolved easily with aliases
– Aliases should always be named in a
fashion which relates them to the
logical layer subject matter.
Physical Layer Issues
• Nulls
– It is absolutely imperative to set NULL flags
correctly.
Typical OBIEE Psuedo SQL
Select fields from
Select (Detail Rows) DR,
Select (SubTotal Rows) SR
Where DR.SubTotal Field = SR.SubTotal Field
Nullable Join
nvl(DR.c1 , 88.0) = nvl(SR.c1 , 88.0)
and nvl(DR.c1 , 99.0) = nvl(SR.c1 , 99.0)
Physical Layer Issues
• Nulls
– The 88.0 and 99.0 are auto generated based on the field being
null.
– For Char/Varchar fields a ‘q’ or ‘z’ are used.
– Very dangerous especially if the join field contains the above
null replacement characters.
– Null when not nullable – Correct answer but can’t use index
– Nullable when not null – Incorrect answers as an equal join
would be used thereby removing rows from the result set
which could be relevant.
Physical Layer Issues
• Recommended Join Structure
– Get it right the first time, physical joins are typically
never touched after initial implementation.
– All joins should be PK/FK
– This will (in most cases) guarantee insulation against
typical errors which OBIEE generates due to not
using standard dimensional model
– Only ONE PK should exist on each table.
– Driving tables are your friend; as long as you know
the data structure.
Physical Layer Issues
• Row level security
– Delivered is handled via joins to SJT tables
– In our case we found it to be better performing by
using the content filtering options on a logical table
source.
– Must be applied to each pseudo fact table in order to
achieve row level security
– Removes the need for a join by simply placing the
restriction in a where clause.
– Must use Repository variable(s) in order to use this
method.
Physical Layer Issues
• Federated Joins
– Federated joins should be avoided at ALL costs.
– If reduction in federated joins isn’t possible; you
should always set driving tables in order to reduce
cost
– Tune MAX_PARAMETERS_PER_DRIVE_JOIN to
control how many in list operators can be sent per
query of a drive.
– Tune MAX_QUERIES_PER_DRIVE_JOIN to control
the number of queries can be sent to formulate a
driving join result set.
Physical Layer Issues
• Poorly Tuned Database features
– Just because it’s the default doesn’t mean it’s correct!
– Most defaults take a very reserved approach as to
limit errors in the BI Server.
– Common objects which should be investigated are:
COUNT_DISTINCT_SUPPORTED
DERIVED_TABLES_SUPPORTED
CASE Statement Support
Running Aggregate support(IE, Sum,Count,etc)
Logical Layer Issues
• Calculations in the Logical Layer
– Keep one thing in mind when developing Logical objects; keep
the objects as close to the database as possible.
Ex: Given the below Case statements, imagine having 20 or
so fields in the same scenario listed below. Those fields
join to 5 smaller code lookup type tables. You create and
answers document which has 5 fields used in the filter and
return all 20 fields with a sum on each one.
Case when “Field1” = ‘A’ then 1 else 0 end
Case when “Field2” = ‘A’ then 1 else 0 end
Case when “Field3” = ‘A’ then 1 else 0 end
Logical Layer Issues
• Problems?
– The resulting SQL would contain somewhere in the
neighborhood of 4.25 MILLION characters.
– The compile time for OBIEE to even generate the SQL could
well exceed 70 seconds
– The BI Server process is pegged at 100% just to generate this
SQL query for the 70 seconds mentioned above.
-------------------- Logical Query Summary Stats: Elapsed time 74,
Response time 74, Compilation time 72 (seconds)
Logical Layer Issues
• So what was the fix?
– Stacking of calculations on a case statement isn’t very wise if
the corresponding case can be handled in a database view. The
best mix seems to be around 2~3 calculations deep and you
should look at other alternatives.
– Aliasing tables to resolve “Fan Traps” which OBIEE didn’t
know how to handle.
– DB Features of support “CASE” logic; There are 2 of these
both on the DB Features tab.
• The Result:
-------------------- Logical Query Summary Stats: Elapsed time 4,
Response time 4, Compilation time 0 (seconds)
Answers Issues
Answers… Can I just turn it on?
Answers Issues
• Query Limits? What’s that?
– It’s not nice to lock those performing
transactions out of the system because an answers
user didn’t understand the meaning of a filter.
– Even properly trained developers still have
“whoopsie” moments!
– We’re actively running reports against the
Transactional System… need I say more?
Answers Issues
• Query Limits! Query Limits! Query Limits!
– An Answers Self Service user should not be
retrieving 10,000 rows through the web in a
properly designed transactional reporting system.
– Is set in the physical layer of the repository based
on the role/groups a user belongs to and can
limit based on row count, execution time or
write back abilities
Answers Issues
• nAminG conVentionS!
– Consistent naming of core business related fields.
– Default aggregation standards by field type
should be defined at planning stage.
– Rename object names in the logical and
presentation layer instead of setting display
name.
– Set display name doesn’t account for formulas
which refer to the actual column name and not
the display name
Answers Issues
• Metadata Generator
– Manually Automatic process from the admin tool, which
must be generated and uploaded to a specific location on
the Presentation Server.
– Allows users to dig into a presentation column and view
the lineage about the column
Answers Issues
• Core Developer Subject Area
– Composed of many tables sometimes between 50-100
– Allows a developer to test reporting options across all subject
matter areas related to a specific database connection.
– Most newly modeled tables start in this area, with subsequent
copies to smaller business related subject areas.
– Allows for testing of data anomalies
– Can reduce need for direct data warehouse access by developers
– Typically only available to developers who are creating university
wide dashboards
Closing
• Plan the Subject Areas instead of them planning
you.
• Solid Physical/Logical Design
- Joins, Aggregation, Security, NULL
• Make sure your DB features are set based on the
database you’re connecting to
• Query Limits; Gotta have em.
• Don’t get view happy in the database!
• Error Message Troubleshooting
•
http://download.oracle.com/otndocs/products/bi/bi-ee/docs/784/AnyMsg.pdf
Contact
Reggie Gentle
BI Architect
Enterprise Resource Planning (ERP)
Florida State University
[email protected]
Thanks for attending Session #27777. I value your
feedback. Please complete the session survey.
This presentation and all Alliance 2010 presentations are
available for download from the Conference Site
Presentations from previous meetings are also available