OBIEE White Paper

Implementing OBIEE using Oracle DWM and a
Nearshore Development Model
Business Challenge
Our Client’s Senior Executives determined the company was required to revisit their IT investment. This
included a review of IT applications, architecture, infrastructure, organization and technology processes. An
overall IT strategic plan was developed and this plan identified several technology objectives and aligned them
to the business strategy. The Business/ IT Strategic plan recommended Global Business Intelligence as one of
the strategic initiatives to help address the report deficiency and to develop a set of Global Key Performance
Indicators as well as providing operational KPI’s for the business units.
The client had multiple operational datamarts developed with tools like Oracle Discover, SAS and Information
Builders each with custom analytics and reports. Although, they had plans to consolidate their ERP
applications, they still required a single Business Intelligence platform that would allow them to integrate data
from a variety of data sources and become a strategic business reporting platform.
They also had distributed systems all over the world and as such also had a variety of reporting systems with no
single source of truth. They choose OBIEE in order to address the following needs:
•
•
•
•
•
•
•
•
•
•
Simplify global business reporting footprint
Migrate current systems to strategic business reporting platform
One stop shop for all reporting needs
One version of the truth
Integrate all business reporting systems
Provide efficient service levels through timely reporting of customer data
Lower total cost of ownership for all business reporting solutions
Enable business innovation thru an integrated reporting infrastructure
Rollout the BI reporting solution to up to 3,250 associates
Develop an architecture to allow future systems to integrate into the DW
Scope of Project
The scope of this project included implementation and customization of a Global Business
Intelligence Solution
Applications
In order to meet the target production date the following modules of
Oracle Business Intelligence Enterprise Edition were implemented.
•
•
General Ledger & Profitability Analytics
Payables Analytics
•
•
•
•
•
•
•
•
•
Technology
Methodology
Receivables Analytics
Human Resources Compensation Analytics
Human Resources Operations & Compliance Analytics
Order Management Analytics
Order Fulfillment Analytics
Inventory Analytics
Procurement and Spend Analytics
Supplier Performance Analytics
CRM Analytics Suite (Sales/Service/Marketing)
Oracle OBIEE 10.1.3.2
•
Oracle DB 10G 10.2.0.3
•
Oracle BI Applications 7.9.4
•
Informatica Client and Server
•
Data Warehouse Administration Console (DAC)
A Customized version of Oracle's Data Warehouse Methodology Fast
Track (DWM FT) was used on this project.
Strategy
In order to meet the desired timeframe of 6 months and the goal of delivering the analytics with as little
customization as possible, we proposed two concurrent efforts. 1) The definition of the Global KPI’s 2) The
loading of the out-of-the-box analytics. These two efforts would converge during the conference room pilot
where the business team would have both a list of the Key Performance Indicators for the organization as well
as a populated set of OBIEE analytics that they could evaluate and determine if the analytics would address
their newly defined business requirements.
Definition of Global Key Performance Indicators
The Company had two Organizational units, North America and EMEA, that operated separately. The
Executives wanted to create a global view of the company that included metrics that would allow them to
measure the efficiency of the business as a whole. North America was using Oracle EBS with many
undocumented customizations. In addition, the business units had hundreds of custom reports that they
wanted to replace with a Business Intelligence Solution. EMEA was using a custom AS400 system to manage
their business. To handle their reporting requirements they built a custom reporting solution on SQL*Server
which provided many operational reports as well as some high-level reports for their Executives. The
challenges was to identify a way to move both teams from a reporting mindset which could require the project
team to duplicate hundreds of existing reports to a Key Performance Indicator mindset which corresponds to
the OBIEE / Datawarehouse architecture. Our approach was to conduct KPI workshops where the reports were
used as inputs to define what Key Performance Indicators would be needed from the Global reporting solution.
In order to set the stage for the workshops, our team developed a deliverable which documented the Key
Performance Indicators available in the OBIEE analytics. This proved to be a good starting point for the
workshops as well as helping to smooth the transition from the existing reports to the Oracle Analytics.
OBIEE Key Performance Indicators
To provide a good starting point for the Global KPI definition workshops we created a deliverable RA.100. This
deliverable lists the Out of the Box metrics provided by Oracle Business Intelligence Enterprise Edition. Its
intended audience is the Subject Matter Experts (SME) so they can compare OBIEE metrics with the metrics
they are currently using and/or they want to use in the future. We used the Metadata Repository and Product
documentation to extract the measures/metrics and provided this to the business as a starting point for the
workshops. Here are some example metrics:
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
§
# of PO # of Products # of Products Requiring Reorder # of Requisition Lines # of Requisition # Purchase Requisitions % Accepted % Chg Quarter Ago Historical Expected Revenue % Chg Quarter Ago Historical Opportunity Revenue % Chg Year Ago Actual Issue Quantity % Chg Year Ago PO Amount % Chg Year Ago Receipt Amount % Chg Year Ago Returned Amount % Chg Year Ago Returned Quantity % Chg Year Ago Standard Cost Amount Accepted Amount Accepted Quantity Actual Issue Quantity Actual Receipt Quantity AP Aging 1-‐30 Amount AP Aging 1-‐30 Amount to Total % AP Aging 31-‐60 Amount AP Aging 31-‐60 Amount to Total % AP Aging 61-‐90 Amount AP Aging 61-‐90 Amount to Total % AP Aging 90+ Amount AP Aging 90+ Amount to Total % AP Avg Invoice Days Outstanding AP Avg Invoice Days Overdue AP Avg Supplier Payment Days AP Total Payment Amount AP Turnover Approval Cycle Time Approved Expense Amount AR Overdue Amount to Total % AR Overdue Items to Total % AR Turnover Days Inventory Outstanding Days Payables Outstanding Days Sales Outstanding Gross Profit Historical Expected Revenue Inventory Turn Invoice Amount Invoice to List Price Rate § Invoiced COGS § Ship to Invoice Days Lag § Standard Cost Amount § Supplier Score § Revenue Amount Since many of the metrics were based on best practices and industry standards, they aligned well with what the
Organization wanted to measure and helped align the client’s requirements with what the OBIEE could deliver.
The teams combined the OBIEE measures with measures from their current reporting systems and defined
new KPI’s for the organization.
Example Global Metrics defined during the workshops:
Global
metrics
JIT/Value
Cash to Cash
Cycle Time
Value/BIQ
Service Level
[Inventory Days of
Supply + Days
Receivables
Outstanding - Days
Payable
Outstanding]
SL (Right Part, Right
Time, Right
Quantity) all
markets/products by
request date
SC Return on
Working
Capital
(Supply Chain
Revenue – COGS –
Supply Chain
Management Costs)
/ (Inventory +
Accounts Receivable
– Accounts Payable)
Value/BIQ
JIT/Waste
Waste/BIQ
SCM cost
Inventory
Turns
Waste/BIQ
Perfect Order
Fulfillment
72 d
>95%
[Cost to plan]+[Cost
to source]+[Cost to
deliver]+[Cost to
return]
Global Inventory
Turns, all inventory
[Total perfect
orders]/[Total
Orders]
<10.1%
>12
92%
The outputs of these workshops were Key Performance Indicators for both the Executives and the
corresponding business units for both North America and EMEA. In addition, the project team was able to
identify likely Analytics that could be used to satisfy these requirements and categorize them on the standard
DWM MoSCoW list deliverable.
Meanwhile Load the out of the Box Analytics
While the workshops were in session, the development team proceeded to load the analytics with minimal
configurations. The first step in doing this was the financial account grouping. We created a deliverable the
DA.20 to aid the Subject Matter Experts in performing the account grouping. In addition our development
team extracted the existing accounts and provided example spreadsheets that the SME’s could use to help
group the accounts. Here is an excerpt of the deliverable: Explanation of the out-‐of-‐the-‐box groups of accounts of Oracle Business Intelligence (OBIEE) Introduction
This document explains the accounts groups and the way they are involved in the output of the OBIEE analytics. It is critical that the General Ledger Account Numbers are correctly mapped to the Group Account Numbers (or domain values) as the metrics in the General Ledger reporting layer uses these values. The General Ledger accounts are categorized in specific groups of account numbers of different ranges. The out of the box grouping basically buckets each group, and reporting is done by applying the required calculations to the buckets. Each reporting has a separate set of calculations applied. The out of the box account grouping pre calculates the measures for the reports and thus the data has to be loaded accordingly. In order to have an accurate implementation of OBIEE, it is necessary to have the corresponding correlation between the Accounts used by Financial team and the Groups of Accounts that OBIEE provides out-‐of-‐the-‐box. To do so, it is necessary to populate the file File_group_acct_codes_ora11i.csv as explained below. You will also find an empty template of the file in page 5 to help you fill in the columns as needed. This file will be used during the OBIEE process of data loading so these groupings are reflected in the basic data and thus the Analytics show accurate calculated numbers. At the end of this document there is attached a compressed file with all the accounts at the ERP system so far. These accounts are the ones to be categorized under the OBIEE out-‐of-‐the-‐box groups of accounts using the template .csv file mentioned above. In the table below, in the first row, all accounts within the account number range from 1110 to 1110 containing a Set Of Books (SOB) Identifier (which in turn linked to a company/unit) equal to 101 are assigned to Cash. Each row maps all accounts within the specified account number range and with the given Set Of Books Identifier. SOB Id
From Acct
To Acct
Group_Acct_Num
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
1110
1210
1220
1240
1280
1340
1410
1471
1530
1630
1770
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1110
1210
1220
1240
1280
1340
1410
1471
1530
1630
1770
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
CASH
AR
AR
AR
OTHER CA
PPAID EXP
FG INV
WIP INV
PPE
ACC DEPCN
GOODWILL
SLS PAYROLL
MKTG PAYROLL
OTHER ASSET
SLS PAYROLL
R&D PAYROLL
GEN PAYROLL
EMP BENFT
EMP SUPP
CONT EXP
OTHER R&D EXP
CONT EXP
SOB Id
From Acct
To Acct
Group_Acct_Num
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
220220
2210
2370
2700
2990
2991
2992
2993
2994
2995
2996
2997
2998
2999
3200
3310
4110
4901
4902
4903
220220
2210
2370
2700
2990
2991
2992
2993
2994
2995
2996
2997
2998
2999
3200
3310
4110
4901
4902
4903
TAX
AP
MKGT PAYROLL
OTHER LIAB
ST BORR
LT DEBT
CASH
PREF STOCK
ACC LIAB
ST BORR
OTHER CL
LT DEBT
ST BORR
OTHER EQUITY
CMMN STOCK
RET EARNING
REVENUE
ST BORR
ACC LIAB
ACC LIAB
In the table below, in the first row, all accounts within the account number range from 1110 to 1110 containing a Set Of Books (SOB) Identifier (which in turn linked to a company/unit) equal to 101 are assigned to Cash. Each row maps all accounts within the specified account number range and with the given Set Of Books Identifier. SOB Id
From Acct
To Acct
Group_Acct_Num
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
1110
1210
1220
1240
1280
1340
1410
1471
1530
1630
1770
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
220220
2210
2370
2700
2990
2991
2992
2993
2994
2995
1110
1210
1220
1240
1280
1340
1410
1471
1530
1630
1770
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
220220
2210
2370
2700
2990
2991
2992
2993
2994
2995
CASH
AR
AR
AR
OTHER CA
PPAID EXP
FG INV
WIP INV
PPE
ACC DEPCN
GOODWILL
SLS PAYROLL
MKTG PAYROLL
OTHER ASSET
SLS PAYROLL
R&D PAYROLL
GEN PAYROLL
EMP BENFT
EMP SUPP
CONT EXP
OTHER R&D EXP
CONT EXP
TAX
AP
MKGT PAYROLL
OTHER LIAB
ST BORR
LT DEBT
CASH
PREF STOCK
ACC LIAB
ST BORR
SOB Id
From Acct
To Acct
Group_Acct_Num
101
101
101
101
101
101
101
101
101
101
2996
2997
2998
2999
3200
3310
4110
4901
4902
4903
2996
2997
2998
2999
3200
3310
4110
4901
4902
4903
OTHER CL
LT DEBT
ST BORR
OTHER EQUITY
CMMN STOCK
RET EARNING
REVENUE
ST BORR
ACC LIAB
ACC LIAB
This deliverable proved invaluable in allowing the development team to perform the customizations necessary
to load the financial data and demonstrate the out of the box analytics. For the European team, we used the
universal adaptors that corresponded to the Subject Areas and star schemas we were attempting to populate.
We created a matrix that showed which .csv files we needed to populate then extracted the corresponding data
from the source system into flat files. Here is an example of the mapping between .csv files and the
corresponding fact or dimension table:
FILE_INVENTORY_DAILY_BAL
file_inventory_daily_snp.csv
file_inventory_product.csv
file_ivr_menu.csv
file_job.csv
file_mvmnt_type.csv
file_org.csv
SDE_Universal_InventoryDailyBalanceFact
SDE_Universal_InventoryProductDimension
SDE_Universal_IVRMenuDimension
SDE_Universal_JobDimension
SDE_Universal_MovementTypeDimension
SDE_Universal_OrganizationDimension
These two efforts provided enough information for the technical team to independently load data from North
America and Europe and populate the out of the box analytics.
Nearshore Team Organization
We organized 2 Nearshore in Mexico City and Merida Mexico, to support North America which was an out-ofthe-box implementation of OBIEE, and the other for the EMEA team which used the Universal Adaptors to
load flat files from the AS400 legacy system. In addition we added on-site resources to modify and develop the
metadata, handle the integration with their LDAP solution and modify analytics. Our Business Analyst team
would travel back and forth between the client site and the nearshore sites in order to gather requirements and
communicate them to the delivery teams both onsite and nearshore. The technology used to allow the teams to
develop independently and then integrate the changes was the OBIEE Multi-User-Development or MUD.
CURRENT BI INFRASTRUCTURE:
The Oracle Business Intelligence warehouse was sourced from 2 different sources: Oracle Financials
platforms11.5.10 and an AS400 legacy system. There was a single repository which accommodated metadata
for both data sources.
CONSIDERATIONS:
A system column called DATASOURCE_NUM_ID stores a number value that identifies where the data
originated from. This field was used to distinguish data from different sources that was loaded in the same
database tables. Later by using Where Clause filter in the business models we recreated the separate data sets.
We then created subject areas in the business and presentation layers, and used those subject areas to create
distinct projects for MUD, which developers checked-out for their development activities.
This approach provided the following advantages: This architecture allowed our teams to develop
independently. We were able to provide separate analytics for North America and EMEA by creating filters
within the metadata and analytics. We were able to create the global view of the company desired by the
executives by removing the DATASOURCE_NUM_ID filters and combining values from North America and
EMEA.
DWM Process additions
We chose to use a customized version of DWM to meet the unique requirements of this project. DWM provided
the skeleton of tasks necessary to rapidly implement a datawarehousing project however it lacked OBIEE
product specific deliverables. In addition, we wanted to incrementally deliver useful functionality to the
business so we loaded the datawarehouse in the early phases of the project and then demonstrated the
available analytics in a Conference Room Pilot. We used the CRP process to determine how well the analytics
meet the requirements of the KPI’s which came out of the Global KPI definition process. This information was
used to determine which analytics needed to be modified or if the OBIEE KPI was sufficient to meet the
business requirement.
Conference Room Pilot
The CRP process provided a means to demonstrate the analytics that were loaded while the Global KPI
workshops were conducted. In order satisfy the curiosity of the business users as well as ensuring that we could
demonstrate the analytics within the allotted time, we created a deliverable for the workshops. We felt that we
had to include explanations of both the analytics and formulas so that we didn’t have to field product questions
for the duration of the workshop. The result of the CRP was a Gap Analysis document that was used by the
technical team to determine which type of customization or modification was necessary to meet the
requirement. We created or reused a deliverable from DWM to document changes to the source to target
mapping, the ETL, or Metadata, Dashboards and Analytics.
Sample Workshop
• Introductions/Roles
• Explanation of Scope Objectives Approach
• Vision Statement from Executive Sponsor
• Product Introduction
• Business Users Facilitated Group Discussion
• Review Analytics in the context of the global business process
• Identify Future Analytical Needs (Security, CDC, UAT)
• Identify methods for Global Reporting
• Document Identified Needs for inclusion in governance process
• Review of Discussion Items & Follow-up on Parking Lot Items
Post-Workshop Tasks
• Follow-up Discussions with Project Manager, Project Team, & Business Users
• Drafting of Workshop Deliverables
• Schedule Follow-up Meeting to present Workshop Deliverables to BI council/governance team
DWM FT CRP – TRW Requirements process
RF.120
[Scenarios Document]
CRP
DA.240
[Source / Target Map Specs]
TRW
Deliv.
DA.455
ETL
AC.233
Analytics, Dashboards, Metadata
RF.160
[Gap Analysis]
Example Content from customized deliverables:
RF.120 Introduction This document goes into details of the scenarios that will be utilized by the project team during the analytics Conference Room Pilots to be performed with the Subject Matter Experts (SMEs) for Finance Profitability. Practical Application This document would help the project team to perform a consistent demonstration of the OBIEE application with the purpose of capturing SMEs feedback in the document RA.160 regarding the Analytics to be shown that are specified in this list and loaded with client data. Scope This document is intended to help demonstrating OBIEE Analytics that have been chosen specifically for each Business Process and Functional Area and to help having a better understanding of the RA.160 document content by putting the requirements in context. This document explains the scenarios from Finance Profitability. Business Process – Finance (Profitability) Business Process: Finance Finance deals with all the accounting needs in a company and also manages the flow of funds in and out of the company by all possible means. Finance involves Account Receivables(AR), Account Payable(AP), General Ledger(GL) and Profitability. Functional Area: Profitability Profitability is expressed in terms of several popular numbers that measure one of two generic types of performance: "how much they make with what they've got" and "how much they make from what they take in" Scenario 01 -‐ Inventory Turnover Introduction Displays the Inventory Turnover and Inventory which is comprised of raw materials, finished goods, and work-‐in-‐
progress inventory for the given parameter criteria. Location Dashboard: Profitability Page: KPIs Analytic: Inventory Turnover Path: '/shared/Financials/General Ledger/Asset Usage/Inventory Turnover' Metrics This section contains the list of metrics and their formulas which are used in the Analytic. Analytic Detail
Source (table)
ERP
Source( table)
OBIEE
Fact
Dimensions
FND_ID_FLEX_SEGMENTS, FND_SEGMENT_ATTRIBUTE_VALUES, FND_ID_FLEX_STRUCTURES, GL_CODE_COMBINATIONS, GL_SETS_OF_BOOKS, GL_PERIODS, GL_BALANCES W_GL_GROUP_ACCOUNT_D, W_DAY_D, W_GL_BALANCE_A
Asset Turnover Ratios and Balance Sheet Statement
Time
Name
Inventory Turnover Metrics
Inventories Formula
GL Balance.internal - Daily Cost of Goods Sold * 365 / GL Balance.Inventories
GL Balance.Raw Material Inventory + GL Balance.Work in Process Inventory + GL
Balance.Finished Goods Inventory
Parameters This section contains the parameters, to be entered when launching the Analytic. Time •
•
•
Year = 2008. Month = Jan, Feb, Mar Quarter = Q1 Analytic This section contains a detailed explanation of each section, graph and the Analytic screenshot. Fig. 1. Inventory Turnover Screenshot Inventory Turnover This metric captures the Cost of Goods Sold to Inventories ratio to measure the efficiency of the firm’s inventory management. A higher ratio indicates that inventory does not remain in the warehouses or on the shelves but rather turns over rapidly from the time of acquisition to sale. Inventories This metric gives the total inventory, which is comprised of raw materials, finished goods, and work-‐in-‐progress inventory. Steps This section contains a list of steps to be followed when demonstrating the Analytic 1. Filter by 2008/Q1 RF.160 Introduction This document contains the description of the gaps captured during the Conference Room Pilot I for Supply Chain Management – Distribution Functional Area. Practical Application This document that contains the gaps captured during the Conference Room Pilot 1 for Supply Chain Management – Distribution Functional Area and will be the starting point for the discussions in order to prioritize the Gaps. Scope This document includes the following topics to support the Conference Room Pilot. RF_120_CONFERENCE_ROOM_PILOT_SCENARIOS_Distribution •
This document is intended to capture high-‐level requirements necessary for the production of the following Deliverables: RA. 160 Gap Analysis AC.223a Metadata Design AC.223b Dashboards and Reports Design DA.240 Source to Target DB Matrix DA.455 Data Acquisition ETL Components TE. 009 Testing requirements and approach TE.027 Integration and testing plan TE.036 Created Test Model •
•
•
•
•
•
•
•
Scenario Information This section lists the Scenarios used to carry out the Conference Room Pilot sessions with the Subject Matter Experts and described in the document RF.120 Id
Description
S2
Shipping Performance Trend : Shipment Performance
S3
Top 10 Orders not Shipped : Shipment Performance
S4
Order and Shipment Details : Shipment Performance
S5
Inventory Snapshot By Storage Location : Ship & Put away
S6
Top 10 Customers by Returns : Customer PPM
S1
Order Details : Warehouse Lead Time
General Gaps added to demand The following Gaps should be considered for all Analytics for the Purchasing Functional Area, although they are explained in “Other Requirements” section: Security implementation based on Company and Region Product Hierarchy European data to be loaded, and therefore, showed in the Analytics and filtered accordingly to the User Region. §
§
§
Gap Analysis A gap is a lack of functionality or information necessary to make the analytic useful. Type of Gaps Type of
Gap
Description
1
Analytics that provide useful information, however require some modification, “we
like this but” should be migrated to production and the issues prioritized and worked
for CRP II
2
Analytics that provide useful information however require an additional data source,
“we would like this if it had Asia”, should be migrated to production and prioritized
for CRP III
3
Analytics that are not useful, should be removed from the functional area with a
description as to why/how this decision was made
Gaps Defined Typ
e of
Gap
Id
Scenario
Id
G1
S1
Warehouse
Lead Time
1
G2
S2,S3,S4
Shipment
Performance
1
KPI
Entity
Affected
Sales
Order
Gap
Definition
SME
Priorit
y
(MoSC
oW)
In
Scope?
(Y/N/Pha
se)
Warehouse
Lead Time
Shipment
Performance
Requestor
Comments
Customization Approach
After the Conference Room Pilots were conducted we analyzed the specific gap type and organized the work
needed to be done by Dashboards (AC223 ), Metadata (AC223 B) or ETL ( DA455). The development team
performed the work and the customizations were demonstrated in the next iteration of the Conference Room
Pilot in order to validate the customization and receive sign-off to migrate the analytics to production. Analysis Process
Research
On Dashboards
Research
On Administration
Tool
Research
On Informatica
Sources
Customer
Layer
Dashboards
Analyze
Gap
Administration
Gaps Document
For Technical
Teams
Informatica
CRP Gaps
Develop Solution
Research
On Sources
Document
Technical
Solution(AC223B)
Document
Technical
Solution(AC223)
Document
Technical
Solution(DA455)
Sign off
Develop on
Correspondent
Layer(s)
Sign off
Sign off
Document
Functional
Solution
Develop on
Customer
Layer
Conclusion
With the combination of Client Executive leadership, Oracle DWM and the flexibility of our Nearshore
development teams we were able to provide the complete OBIEE solution.
•
•
•
•
•
•
Customized a methodology in accordance with Oracle’s DWM framework to define the project tasks and
deliverables to meet client expectations
Organized our Nearshore teams to support the clients Organization
Worked with the Business team to develop Global Key Performance Indicators in order to identify
which analytics should be presented via user workshop
Conducted 10 GAP Analysis user workshops to demonstrate production-ready analytics populated with
18 months of data from North American and European data sources
Created a metadata solution that allowed both separate and combined views of the corporate data
Customized analytics based on feedback received in Conference Room Pilots

Download Report