Transcript Sharepoint Webparts - Information Builders

Building a Data Warehouse from
SAP using iWay and WebFOCUS
O. Julian Plys PMP, Sunoco, Inc.
Carole Benoit, Tek Systems, Inc.
What you will learn
Why create a DW outside of SAP?
How can you use iWay tools for this
How to structure your data for easiest
extraction and efficient reporting
What WebFocus BI tools to use
The story of how we did this at Sunoco
Why create a DW from SAP?
Control & flexibility over data design
history merged with new data
merging with outside data
conformed dimensions
SCD – Slowly Changing Dimension
Reduce the transaction load on SAP separate transaction from queries
Flexibility in BI front end tools
Cost reduction
SQL development vs. BW development
License costs – SQL Server vs BW
A Little Data Warehouse History
1980’s – the Information Center
1990’s – the Data Warehouse
2000’s – New Tools, New Specialties
CRM – Customer Relationship Management
MDM – Master Data Management
Data Quality; Data Cleansing
Business Intelligence tools
It was 20
Years ago
today…
IBI taught
us how to
play…
The Sunoco Decision
We wanted the flexibility
We wanted the cost reduction
Decision: Build a reporting oriented Data
Warehouse alongside SAP implementation.
Use WebFocus reporting and BI tools to
expose SAP + other data to end users
The Action Plan
Connections to SAP – iWay
ETL processes shared between iWay and
SSIS
Data Warehouse design based on
Dimensional modeling techniques
Structured Reports
OLAP and AdHoc Tools – Report Assist
The iWay SAP Query Adapter
iWay Adapter Mgr
--Moves data from SAP using Query
Adapter to connect
`
Reports and
Reporting Applications
Analytical Processing
Enterprise DW SQL Server
Developer
WebFocus / Sharepoint
Web Servers
WebFocus
Reporting
Server
SAP Environment
iWay SAP R/3 Query Adapter
Sharepoint
Browser
-Talks to application for data and
other logical structures
-Can perform real-time extract
-SAP metadata
SAP Appl Server
SAP DB Server
ETL Processes
Move SAP data to Staging Area (MSSQL) in
raw form (overnight and near real time)
Use ETL tools to load dimensional structures
(SSIS)
Load dimensions first, then measures
Combine with outside data while loading
Error check and Log while loading
Data Design Principles
Dimensional models simplify SAP complexity
Further complexity simplified in views
Masters created from views
Dimensions and measures joined using
native SAP keys
Can perform SCD (Slowly Changing
Dimension)
VIQMEL
Notification Header
VIQMELST
PM Notification Selection by Status
Category
Notification # is PK
Join via OBJNR
Before – SAP
Structure
VIQMFE
PM Notification - Item
Items
VIQMFE2 is abbreviated
version of this table
VIQMFEL = VIQMFE + VIQMEL
VIQMUR
PM Notification: Item Cause
VIQMAMAL
Activities + Actions
Notifications Structure
SAP
VIQMSM
Notification – Tasks
Code Group
Activity Text = Short Text
Coding
Activity Code
Long Text Indicator = X
Long Text
STXH - STXL
Object = QMMA
Notification # + Item
ID = LTQM
STXL
Long Text field not
accessible to iWAY
VIQMMA
Notification: Activities
AFTER – DW Structure
NOTIFICATION
Status
Material
NOTIFICATION
Type
NOTIFICATION
Priority
Customer
F_NOTIFICATION
Division
Plant
Carrier
(D_VENDOR)
WebFocus Reporting
MRE Dashboard for standard reports
Every Dimensional Structure / View becomes
a Report Object
Each report documented on Dashboard page
Metadata is built into the WebFocus Master
Metadata comes from SAP Query Adapter (in
English !)
WebFocus BI Tools
OLAP Reports become embarrassingly
easy to create with dimensional model
and metadata in Master File Description
Every View becomes a Report Object
End User training customized with
existing report objects
Dramatis Personae
1 Project Manager
1.5 Analysts
1 Data Designer / ETL Specialist
1 Report Developer
The Cavalry Charge at the end
4 additional developers
2 additional analysts
3 IBI consultants
Time Frame
Jan 2006-Apr 2006
Interviews, Environment set
up
May 2006-Aug 2006
Analysis, Requirements
Sep 2006 – Nov 2006
Development, Testing and
Delivery
Dec 2006 – Feb 1, 2007
Intense Dev, Test, Deliver
Feb 2007 – Apr 2007
User training, New
Requirements, Project wrapup
More Information
iWay Manual = Adapter Administration for UNIX,
Windows, OpenVMS, i5/OS, and z/OS 7.6.4 – Chapter 54
Using the Adapter for SAP R/3-ECC
Ralph Kimball Books on DW design, ETL:
The Data Warehouse Lifecycle ToolKit, Ralph Kimball et
al., John Wiley & Sons, 1998 – 2nd edition available
The Data Warehouse ETL ToolKit, Ralph Kimball et al.,
John Wiley & Sons, 2004
The Data Warehouse Toolkit 2nd Edition, Ralph Kimball
et al., John Wiley & Sons, 2002
Questions