creating reports using bi publisher and...
TRANSCRIPT
Creating reports using BI Publisher and FTI MavenWire
Dave Sanderson & Arvind Sabharwal
12 March 2015
Agenda
§ Designing a reporting solution for OTM § Walkthrough of FTI § Demo – Developing Custom Reports in OTM § An implementation approach for OTM Reporting § Questions
Reporting Requirements for OTM
Transportation Management
Management Information
Forecasting and Planning
Operational Reports
Status Reporting
§ Reporting on one, or a small number of, records
§ Uses real-time operational data
§ Little or no aggregation of records
§ Only uses data from single system
§ Fixed format outputs
§ Reports generated based on a small number of
parameters
§ Used by operational users as part of day-to-day
operational processes
§ Produced on request or on scheduled basis
§ Generally not available for historical data
§ e.g. Commercial Invoice, Transmittal Letter, Bill of
Lading, etc
Reporting Requirements for OTM
Transportation Management
Management Information
Forecasting and Planning
Operational Reports
Status Reporting
§ ‘Summary’ reporting on a large number of records
§ Reporting on an extract of operational data § Often combines data from multiple operational
systems § Data aggregated across a number of dimensions § Broad user base:
o KPIs & Dashboards for Execs o Rich analytical services for Business
Analysts o Summary and trend reports for Managers
o Standard reporting, audit & reconciliation for controllers/regulators
§ Maintains a deep data history § Broad range of outputs:
o structured formal reports (e.g. monthly management packs)
o standard dashboards o ‘self serve’ reports o highly interactive ‘slicing and dicing’, drill
down, drill across data for analytical purposes
Reporting Requirements for OTM
Transportation Management
Management Information
Forecasting and Planning
Operational Reports
Status Reporting
§ ‘What if’ and scenario planning
§ Uses simulated data (often generated using real
historical data as a baseline)
Reporting Requirements for OTM
Transportation Management
Management Information
Forecasting and Planning
Operational Reports
Status Reporting
§ (Real Time) ‘Control Tower’ Dashboards
§ (Real Time) Status Updates
§ ‘Track and Trace’
§ Push or Pull
§ ‘System’ clients
Reporting Solutions for OTM
ü Logis'cs Command Center
ü Custom Solu'on
Forecasting and Planning
ü Standard / Configured BI Publisher reports in OTM
ü Custom BI Publisher reports in OTM
Various solu'ons, depending on requirement: ü BAM Dashboards ü Web Services
exposing status update services
ü Etc….
Status Reporting
ü Fusion Transporta-on Intelligence
ü Integra'on with Strategic BI PlaLorm
Management Information
Operational Reports
Transportation Management
An OTM Reporting Reference Architecture
Management Information Overview
Management Informa'on Strategy – A Vision § Single trusted source of data that can be used for repor'ng across the business “one
version of the truth” § A plaLorm that will scale incrementally and support new business ini'a'ves § Users able to use self service repor'ng against a trusted data set § IT involvement only where there is addi'onal complexity § Clear data and process owners § Data Quality:
§ Central system for measuring data cleanliness § Solu'on self tests and reconciles itself against
control totals in source applica'ons § Robust error handling to keep data 100% complete
§ Technical Architecture: § Solu'on designed for repor'ng § Appropriate tools for end user repor'ng
§ Proac've support model: § Internal SLAs
A MI Reporting Reference Architecture
Security
ETL, Messaging & Metadata
BI/PM Tools
Informa-on Access
Data Sources
Data Warehouse
Informa-on Management
Informa-on Sources
A MI Reporting Reference Architecture
Security
ETL, Messaging & Metadata
BI/PM Tools Data Sources
Access & Performance Layer
Founda'on Data Layer
Staging Data Layer
External Data
Opera'onal Systems
Unstructured Data
COTS
Master Data
Temporary Data
Structures
Rejected Data
! Process Neutral 3NF model
Embedded Datamarts
Mul'-‐Dimensional Models
Summary Management
BI Applica'on Data
End-‐User Sandbox End-‐User Sandbox
End-‐User Sandbox
BI Abstrac'o
n and Que
ry Gen
era'
on
Services
Alerts Dashboards MD-‐Analysis Reports Query
FTI – Strategic MI for OTM
FTI
Sa'sfying the Management Informa'on Repor'ng
Requirements for OTM
Providing a founda'on for a strategic Management Informa'on Architecture
Pre-‐Canned Transporta'on Management
Reports, KPIs & Dashboards
Built on a robust scalable technical architecture
An effec've implementa'on will consider both the ‘tac'cal’ OTM MI requirements and the ‘strategic’ MI PlaLorm view of FTI !
FTI Demo
FTI Demo – A Terminology Primer
Dimensional Modelling at the heart of BI. It enables:
§ Ease-‐of-‐use § Accessibility of data § Op'mal query performance
Key concepts of Dimensional Modelling:
§ Facts (measures) -‐ typically numeric values that can be aggregated, i.e . Sales Amount.
§ Dimensions (context) -‐ groups of hierarchies and descriptors that define the facts, i.e, 'mestamp, product, register#, store, etc. are elements of dimensions
Implementing FTI – Designed to be extended
Supported and documented customisa'ons to FTI:
§ Extrac'ng addi'onal data from OTM
§ Changing the exis'ng metadata structure (adding new metrics, changing logical data sources, adding dimensions)
§ Crea'ng new dashboards or customizing out-‐of-‐the-‐box dashboards
§ Adding KPIs § Crea'ng custom BI Roles
Customisa'ons requiring a broader MI solu'on design:
§ Enriching FTI with data from external sources
Implementing FTI – A Recommended Approach
Ini'al Setup
Structured ‘CRP’ of FTI
Define Backlog
Itera've Delivery
§ Ini'al structured walkthrough of :
o standard FTI dashboards/reports/KPIs
o standard FTI data set for ad-‐hoc repor'ng
§ Deliverable: ini'al list of gaps in standard FTI dashboards and reports
§ ‘Vanilla’ Install of FTI
§ Non-‐produc'on environment, using produc'on dataset
§ Deliverable: FTI environment with representa've dataset to enable workshops to be run effec'vely
§ Define the addi'ons/extensions required to FTI to meet gaps
§ Deliverable: design of the extended FTI solu'on to meet the repor'ng requirements
§ Deliverable: priori'sed, es'mated ‘Product Backlog’ for delivery
§ Over a series of itera'ons, design, implement, test and deploy the technical solu'on to meet the func'onal gaps in FTI:
o Extend the ODI components for load of required missing data to make this available within the FTI dataset
o Extend the FTI Repository Defini'on to include the addi'onal data above
o Extend FTI Dashboards and Reports to meet func'onal requirements
§ Deliverable: Fully func'onal extended FTI Repor'ng Solu'on
Implementing FTI – Some Considerations
§ Complete ini'al MI requirements capture before assessing FTI including:
§ requirements of different user types
§ requirements of different departments
§ Consider func'onal fit of FTI Reports and Dashboards against requirements
§ Understand implica'on of extending the repor'ng solu'on – FTI has limited use license for OBIEE and ODI covering OTM standard data only
§ Availability of historical data o New OTM implementa'on?
o Exis'ng OTM implementa'on with strict data purging policy?
Immediate benefit of FTI may not be recognised – unless there are archived/legacy datasets that can be migrated to the FTI data warehouse; if so, build enough effort into legacy data migra'on.
§ If developing major customisa'on to FTI, consider developing a robust configura'on management and release process for OBIEE & ODI components
Implementing FTI – Adoption Patterns § Don’t adopt FTI; use
BI Publisher (or similar) for all repor'ng
ü Simple
ü Reports can be tailored to exact requirements
ü Access to full OTM data model
✗ Potential performance issues with complex reports
✗ Limited ability to provide dashboard/KPI reporting
✗ Limited ability to provide deep analytical capability
§ ‘Vanilla (or light customisa'on) adop'on of FTI
ü Simple
ü Harness benefits of strategic MI solution without significant implementation costs
ü Likely to be (relatively) upgrade-safe
✗ FTI may not be a good functional fit for MI reporting requirements
✗ Unable to utilise data from other operational systems or data sources
§ Heavy customisa'on of FTI
ü Opportunity to deliver good functional fit for MI requirements
ü FTI provides good template, accelerating delivery of MI solution
ü Potential to extend MI platform to include other operational systems or data sources
✗ FTI may not be a good functional fit for MI reporting requirements
✗ Unlikely to have simple upgrade path
✗ Likely to have license implications for OBIEE and ODI
§ Custom or Strategic solu'on for Transporta'on Management Repor'ng
ü Provide (or integrate with) strategic MI platform for the enterprise
ü Access to a broad set of data for effective analysis
✗ With no template to start with, implementation costs will be higher
✗ Won’t benefit from ‘best practice’ aspects of TM MI built in to FTI.
Learn more
Thank you Contact us Dave Sanderson [email protected] +44 7557 260 821 [email protected] www.mavenwire.com