Edw-bi

Data Warehousing To BI Reporting Ajay Uppal

BI / BW TO-BE FOOTPRINT

Flat File Upload p2 | 25/04/09

Future?

Running the EDW – Real-Time Data Acquisition

p3 | 25/04/09

MIGRATING & UPGRADING TO SAP BI D A

F

B E

DWH SAP NetWeaver BI 7.0

p4 | 25/04/09

C

p5 | 25/04/09

TYPICAL SAP BI IMPLEMENTATION PLAN 1/2

p6 | 25/04/09

TYPICAL SAP BI IMPLEMENTATION PLAN 2/2

p7 | 25/04/09

Contents of PQP 1.

Project Quality Plan Overview

2.

Programme Overview

3.

Programme Organisation

4.

Programme Governance

5.

Programme Planning

6.

Programme Management

7.

Quality Assurance

8.

Technical

p8 | 25/04/09

Programme Quality & Communication Plan Mon

Tues

Wed

Thurs

Fri

Meetings with Domains

Meetings

Programme Directors Steering Group

MR PMs meet with R1 Manager

Programme Leadership Meeting

Weekly R1 Team meeting

Reports

MR PMs /Domain leads draft status report

Consolidated status reports

Final Programme Weekly status Report

Additional Exceptional Reporting (PMO)

RED

ORANGE

ISSUES

Issues exist which will prevent targets from being achieved

Issues exist which will prevent targets from being achieved; resolution action is being taken.

RISKS

The combined likelihood and potential impact mean that there is less than 50% confidence in reaching targets.

The combined likelihood and potential impact mean that there is less than 75% confidence in reaching targets.

p9 | 25/04/09

YELLOW

Issues exist but none in isolation will prevent overall targets from being achieved and resolution action is in hand. The combined likelihood and potential impact mean that there is less than 90% confidence in reaching targets.

GREEN

Issues identified have no immediate impact on schedule or business case; resolution is in hand The combined likelihood and potential impact mean that there is more than 90% confidence in reaching targets.

p10 | 25/04/09

Interaction with Programme Delivery Mechanisms

QA3 External panel reviews as required, Updates to Steering Group forum

QA2

Steering Group - Monthly Meeting

Leadership Meeting - Weekly Meeting, consolidated weekly reporting

Specific deep dive analysis of plan or areas of concern Provides guidance to Level 3 panel and support to Leadership

QA1

Programme “Domains” / Maintenance Releases Weekly Meeting and structured consistent reporting

Daily / ongoing whole team commitment Supported by Programme PMO. Documented in Project Quality Plan (see slide 6)

Workstream Level Activities Regular schedule of reporting and meetings, determined by Domain leads

p11 | 25/04/09

Quality Assurance Scope & Method What? (objectives and scope of responsibilities) QA 1 • • • QA2

QA3

To manage and deliver the programme using high quality processes and procedures. All Programme activities are in scope All Programme resources are responsible (with support from the Programme PMO)

When? (Scheduling)

How? (mechanism)

Who? (membership)

Ongoing – built into all activities.

•

Use of Centrica and Wipro quality methodologies. Evidenced via weekly reporting.

•

Whole Programme Team

Deep dives to be led by PMO drawing on resources appropriate to subject under analysis. Outcomes to be fed back into Programme Board meeting. Operates independently of Programme Leadership meeting but may draw on some of it’s members, and will provide feedback / support to it.

•

PMO led plan deep dives. Additional specialists to be invited to specific reviews, depending on topic areas being covered.

Some standard agenda items followed by feedback from QA2 deep dives plus further discussion. Identify areas of concern for QA2 team to investigate over following period

•

•

•

To assure that quality has been built into the programme Plan deep dives by use of programme and non-programme resource and monthly. expertise. Other  “Deep dive” reviews of the plan. reviews as  Targeted reviews of any specific areas of concern arising required. from the above

•

To answer the questions: Monthly / as  Is Programme fit for purpose? required.  Can we maintain / operate the Programme solution?  Will corporate systems / data remain secure?  Can the Business accept / absorb the change we deliver?

•

External panel review agenda to be determined as required, but likely to include:  Review of Programme plan/critical path  Top risks/issues  Review with Programme domain leads  Deeper investigations into key current issues p12 | 25/04/09

• •

•

•

•

Steering Group membership as described in PQP governance. Panel membership to be confirmed, subject to need and specific requirements definition.

Typical Timing & Communication Plan Timing

Audience

Channel

Message

By whom

Jan

BGB Wide

SBU Headl message

•Beacon is coming •Progress of UAT •Alignment with other initiatives

SW – Content AU – Production/Publis h

8th Jan

BGB Wide

Buddy Update

•Progress

SW – content and distribution

15th Jan

BGB Wide

Business Update

•Successes in December •Key milestones for January – Key focus •Any call to action/cascade of information team specific level

SW – Content AU – Production/Publis h

9th , 12th, 16th Jan

Leicester

Creative Initiative

•Market Stalls •Posters

SW- Event Mgmt AU – Publish Invite

16th, 23rd and 30th Jan

Leicester

Creative Initiative

•UAT Open House

AU- coordination and content of invite SW publish invite

22nd Jan

BGB wide

Intranet

•Training milestone message

AU – content SW – publish

22nd Jan

Beacon Team

Prog. Director Team update

•Progress and achievements

SW – Content SG – Publish

p13 | 25/04/09

Categorisation of Issues

Issues include: A change in requirements A change in the environment An anticipated risk occurring An unanticipated problem occurring A problem or error occurring in work under way or completed A query about any aspect of the Project

p14 | 25/04/09

SEVERITY HOW CONFIDENT ARE YOU THAT THIS WON’T AFFECT TIME / COST / QUALITY?

An issue must be assessed and scored for impact (negative or beneficial).

<50%

R

High impact on project time, cost or quality

<75%

O

Medium impact on project time, cost or quality

<90%

Y

Low impact on project time, cost or quality

>90%

G

Minimal impact on project time, cost or quality

Categorisation of Risks

Strategic/Commercial risks Economic/Financial Legal and Regulatory Organizational/ Management/ Human factors Technical/Operational/ Infrastructure

SEVERITY HOW CONFIDENT ARE YOU THAT THIS WON’T AFFECT TIME / COST / QUALITY?

Risks include:

ROYG Diagram

R

O

O

R

R

<75%

O

Y

O

O

R

Medium impact on project time, cost or quality

<90%

Y

G

Y

O

O

Low impact on project time, cost or quality

>90%

G

G

G

Y

O

Minimal impact on project time, cost or quality

G

Y

O

R

>90%

<90%

<75%

<50%

PROBABILITY HOW CONFIDENT ARE YOU THAT THIS WON’T OCCUR?

p15 | 25/04/09

High impact on project time, cost or quality

<50%

Programme Quality Plan Technical - Quality Audit Technical Specification OR Unit Tested and Signed off Code (Build/Bug Fix)

Table Details 1. Objects 2. Transport Number 3. Target System 4. Status 5.GAP ID 6. Criticality In Case of Technical Specifications Transport No will be "TS".

Input Details in MS Access Database with status "Ready for QA"

Urgent

Priority Normal

E-mail to Quality Audit Team

Check the error log in HP Open View Get list of objects from Table and change the status As " In Progress"

Build Team

QA Team

Tech. Spec

Perform Quality Review of the TS

Pass the comments (Track changes in document) to Build Team

Update MS Access & SAP QA databases with Status ("Passed" Or "Failed")

Status

Build

Execute the QA Reports in SAP

Existing Object

New Object

Perform QA using Change Version Mangment (Check Details in Open View)

Perform Quality Review of the code

Update MS Access & SAP QA databases with Status ("Passed" Or "Failed")

Failed

Modify the TS accordingly Passed

Status Passed

Close HP Openview form if previously failed

TS Review Complete

p16 | 25/04/09

Create/Change a form in HP Openview

Check previous review status of last objects

Ready for Trasnport

Failed

Typical BW Extraction & Data Model Finance

KPI IDs 158, 1764, 1922, 1764, 1754, 128, 129, 1013, 130, 1921, 1014, 1920, 1018, 1015, 1018, 1016, 150, 1022

Report: Aged Debtors 1) Summarised Query 2) Summarised Query - YTD 3) Debtor Comparison 4) Summary of Debt by Account Status 5) Summary of Debt by Account Status - YTD

KPI IDs 96, 988, 978, 968, 924,969

KPI IDs 1010

Report: Unmatched Items 1) Summary of Unmatched Items 2) Summary of Unmatched Items - YTD 3) Details of Unmatched Items by Account Type 4) Details of Unmatched Items by Account Type - YTD 5) Summary of Unmatched Items by Account Status 6) Summary of Unmatched Items by Account Status - TYD Report: Weekly Debt Report 1) Summay of Debt in Week 2) Ageing of Debt 3) Classification Breakdown of Debt

Report: Debtor Movements 1) Monthly Summarised Query 2) Monthly DSO 3) Debtors Monthly Movement by Account Type 4) Debtor Monthly Movement by Account Status 5) Weekly Summarised Query 6) Debtor Weekly Movement by Account Type 7) Debtor Weekly Movement by Account Status

InfoCube: Aggregated Open Items

InfoCube: Copy of 0FC_C07

InfoCube:NonCumulative KPI for Closing Balance

InfoProvider

MultiCube: Debt Ageing

Export DataSource

InfoCube: Monthly Debt Ageing

InfoCube: Disputed Debt

Export DataSource

InfoCube 0FC_C04

InfoCube: Customer Attributes InfoCube 0FC_C03

Weekly Update

Monthly Update

Weekly Update

Reporting

Report: External Agency Performance 1) Summary of Collection 2) Summary of Collection - YTD 3) Ageing of Collection 4) Oldest Invoice

Report: Disputed Debt 1) Disputed Debt

MultiCube: Billing Arragement

Export DataSource

InfoCube 0FC_C07

Report: Billing Arrangements 1) Billing Arrangement Value Ageing 2) Billing Arrangement Volume & Value

KPI's

KPI IDs 880, 881

Weekly Update

Weekly Update

ODS: Blocks in Week Once for Non-Cumalative Initialization

Staging

ODS: 0FC_DS02 Weekly Update

InfoCube: Age of Open Items Weekly Update

ODS: 0FC_DS03 Weekly Update

Weekly or Month end Update

ODS: Age of Open Items Details

Source 0FC_OP_01

p17 | 25/04/09

Custom DataSource

Custom DataSource

0FC_IPL_ITEM _01

0FC_IPL_HEAD _01

0FC_COLL_01

Typical Deliverable 1. BW DEVELOPMENT STANDARDS 1.3 Assumptions 1.4 BW Overview 1.5 BW Development Process 1.5.1 Identification of Reporting Requirements: 1.5.2 New Reporting Requirements: 1.5.3 Technical Assessment: 1.5.4 Data Model Document 1.5.5 Data Extraction Document 1.6 Risk Assessment 1.7 Sizing 1.8 IMG Settings 1.10 Query Development 1.11 Web Development 1.11.1 Web Reporting / Design 1.11.2 Standard ‘’Company’’ Web Ad-hoc Template 1.11.3 Standards for Custom Web Development 1.11.4 Centrica Web Object Library 1.11.5 Centrica Branding 1.11.6 Information Broadcasting 1.12 Loading Data 1.12.1 Process Chains 1.13 Naming Standards 1.13.1 Basic Considerations 1.14 Administrator Workbench – Naming Standards 1.14.1 InfoObject Catalog 1.14.2 InfoObject 1.14.3 InfoArea p18 | 25/04/09

1.14.4 Infocubes/ODS 1.14.5 Aggregate 1.14.6 Routines 1.14.7 InfoSource – Transaction Data 1.14.8 InfoSource – Master Data 1.14.9 InfoPackage 1.14.10 InfoPackage Group 1.14.11 InfoSet 1.15 Business Explorer – Naming Standards 1.15.1 Variables 1.15.2 Restricted/Calculated Key Figures 1.15.3 Structures 1.15.4 Query 1.15.5 Temp Query 1.15.6 Web Templates 1.15.7 Role 1.15.8 Workbook 1.15.9 Exception 1.15.10 Scheduling Package for Exceptions 1.15.11 Scheduling Package for print Settings 1.16 R/3 – Naming Standards 1.16.1 Datasource (Transaction Data) 1.16.2 Datasource (Master Data)

1.17 Process Chains – Naming Standards 1.17.1 Process Chain 1.17.2 Process Chain Folder 1.17.3 Variants for Process Types 1.17.4 Message 1.17.5 Process Type 1.18 Performance Considerations 1.18.1 MultiProviders 1.18.2 Aggregation 1.18.3 Pre Calculation 1.18.4 Reporting Agent 1.19 On/Off shore Development 1.20 Authorisations 1.21 Performance Review 1.22 Report Commentaries and BW documents 1.23 Variables

STREAMLINING BI ROLES & USERS BI Author  IT & Business User who need to create reports for others  Supports & trouble-shoot reports for others 2nd tier support  Publish reports to roles BI Analyst  Business Users who do their own analysis  Create reports for their own use  May present reports to others (using Bookmark) BI Consumer  Informational Consumers who run prepared reports  Doesn’t interact extensively with data  Diversified in many ways (skill-sets, guided navigations etc)  Standard Reports are developed by BI  Development Team – Web Reports, Crystal Reports (going forward)

p19 | 25/04/09

My own estimation guide BI & DWH component

Function Point Type

Simple (FP / Efforts in man-days)

Average

Complex

Staging Tables/ Non-SAP Extractors

External Interface Files

5 FP / 5 man-days

7 FP / 7 man-days

10 FP/ 10 man-days

Target Tables/ODS

Internal Logical Files

7 FP / 3.5 man-days

10 FP / 5 man-days

15 FP / 7.5 man-days

Dimension Tables

Internal Logical Files

7 FP / 3.5 man-days

10 FP / 5 man-days

15 FP / 7.5 man-days

Fact Tables

Internal Logical Files

7 FP / 3.5 man-days

10 FP / 5 man-days

15 FP / 7.5 man-days

Lookup Tables

Internal Logical Files

7 FP / 3.5 man-days

10 FP / 5 man-days

15 FP / 7.5 man-days

Mapping (ETL logic)

External Inputs

4 FP / 2 man-days

5 FP/ 2.5 man-days

7 FP/ 3.5 man-days

Process Alerts

External Outputs

3 FP/ 1.5 man-days

4 FP/ 2 man-days

6 FP/ 3 man-days

InfoCubes

Internal Logical Files

7 FP / 3.5 man-days

10 FP / 5 man-days

15 FP / 7.5 man-days

Multi-Cubes

External Outputs

3 FP/ 1.5 man-days

4 FP/ 2 man-days

6 FP/ 3 man-days

Queries

External Inquiries

4 FP / 2 man-days

5 FP/ 2.5 man-days

7 FP/ 3.5 man-days

.

Efforts cover design, development, documentation, security, testing, fix & deployment Exclude Project Management, Training & Environment Management p20 | 25/04/09

ROADMAP – SAP BI & BUSINESS OBJECTS

p21 | 25/04/09

TYPICAL SUPPORT MODEL LEVERAGING EXISTING SET-UP SAP Business Billing – Teeside

Leeds

Oxford

IS Service Call Routing Leicester

Cardiff

Windsor

Floor Walkers & Expert users

Query resolution & reporting

Daily Review Meeting

Business Support Team

Service Desk (123) (HPOPENVIEW)

Application Response Team Service Management

Problem Management

SAP Support team

p22 | 25/04/09

Incident Management

Change Management

SAP Operate Support team

P1 and P2 Calls

SAP Basis Support team

Non SAP Support team’s

Infrastructure Management