Bjarne Berg March 22th, 2006
A billing analysis Data Warehouse
University of North Carolina at Charlotte
2
What We will cover
• Requirement analysis
• Logical design
• Physical design
• Clean and integrating data
• Analysis
• Security design
3
This is a sales and billing data warehouse to meet the following requirements:
Analyze sales by customer groups (key, value, preferred and quality)
What items are sold to what customer group by Universal Product Codes (UPC) and what payment term are used by the various sales organizations.
• Analyze plant utilization by shipment point and customer location
Are we using the right plants for sales to the customers, or are we cross shipping items (potential cost savings)
• Pricing analysis
Are different profit centers using substantial different pricing procedures and what are the impacts on Net sales, excluding allocated costs (aka “subtotal A”).
• Returns analysis
What material groups are most likely to be returned (see credit reason code), and are there differences between the plants, customer groups or material groups.
Scope and Purpose
4
Functional specifications are real and given by International Paper
Create Functional
specs
Peer Review
Complete?
Complete?
Peer Review
Complete?
Complete?Structured
walkthrough
Approved?
Configuration
Unit Testing
Integration
Testing
System Testing
Structured
walkthrough
Approved?
No
No
No
No
No
Yes
Yes
Yes
Yes
Yes
Yes
No
Create Technical
specs
5
Hardware and Network
•The hardware and BW system is located in Houston Texas and owned by the mid-sized consulting firm MyITgroup Ltd.
•The source system is an SAP R/3 demo system located in India (Bangalore), owned by MyITgroup Ltd and loaded with sample demo data as provided by SAP.
•The Network access is through Citrix and VPN.
•The web-server located in Houston is doing the rendering of the web reports
6
What We will cover
• Requirement analysis
• Logical design
• Physical design
• Clean and integrating data
• Analysis
• Security design
7
Billing
Number of billing documentsNumber biling line itemsBilled item quantityNet weightSubtotal 1Subtotal 2Subtotal 3Subtotal 4Subtotal 5Subtotal 6Subtotal ANet valueCostTax amountVolume
Customer
Sold-toShip-toBill-toPayerCustomer classCustomer group~ Customer country~ Customer region~ Customer postal code~ Customer industry code 1End user
Material
Material numberMaterial enteredMaterial groupItem categoryProduct hierarchyEAN/UPC
Time
Calendar yearCalendar monthCalendar weekCalendar day
Unit
Currency KeyUnit of MeasureBase unit of measureSales unit of measureVolume unit of measureWeight unit of measure
Billing information
Billing documentBilling itemBilling typeBilling categoryBilling dateCreation dateCancel indicatorOutput medium~ Batch billing indicatorDebit/credit reason codeBiling categoryReference documentPayment termsCancelled billing documentDivison for the order headerPricing procedure
Organization
Company codeDivisionDistribution channelSales organizationSales group
Logistics
PlantShipping/receiving point
Document details
Sales order document typeSales dealSales docuement
Accounting
Cost centerProfit centerControlling areaAccount assignment group
Personnel
Sales rep number
LEGEND
Delivered in standard extractorsDelivered in LO extractorNot in delivered Content -but in R-3
Logical data model & enhancements
Entity Name Attribute Name DefinitionAccounting Account assignment group The group internal to the organization where
this account has been assigned (i.e. profit center or cost center).
Accounting Profit center A general ledger account that identifies a sub-set of the organization.
Billing MSF Thousands of square feet of sold items (roll length x roll width)
Billing Numeric ERDAT The julian date of when the order was entered
Billing Numeric FKDAT The julian date of when the bill was created
Billing information ~ Batch billing indicator A navigational attribute to indicate if the account is billed periodically.
Billing information Cancelled billing doc An indicator to show is the bill has been cancelled (all line items).
Billing information Debit/credit reason code A code to show why a debit or credit was generated against this bill.
Billing information Output medium Shows if the bill is created as PDF, EDI, paper, PS or doc format
Billing information Payment terms Shows when the bill should be paid and what pre-paid discounts may be applied.
Billing information Pricing procedure Contains the numeric indicator for how the bill was generated and what taxes were applied. This is the PK for the procedure table.
Billing information Reference document Any contract or external document that is basis for this bill
Customer ~ Customer country Navigational attribute to show the customer country location
Customer ~ Customer industry cd 1 Navigational attribute to show the customer's SIC code (1st priority).
Customer Customer industry code 2 Attribute to show the customer's SIC code (2nd priority).
Customer Customer industry code 5 Attribute to show the customer's SIC code (5th group code).
Customer ~ Customer postal code Navigational attribute to show the customer's local postal code
Customer ~ Customer region Navigational attribute to show the customer' region location based on the sales organizational hierarchy.
Customer Credit account The G/L account to credited for this sale
Customer Customer class The customer class based on Key, Preferred, Value & general
Customer End user The end user of the products. Used to track re-sales through distributors
Document details Sales order document type Shows if the sales order that is basis for the bill was generated, contracted, auto ship, VMI or requested.
Material Customer PO number The Purchase order placed by customer (non-native SAP number).
Material Material class The group of material being billed (see material hierarchy -BOM)
Organization Sales channel The type of channel being used to generate the sales. This comes from the sales order based on the sales organizational hierarchy.
Personnel Account executive The identifier of the sales manager assigned to the customer
Personnel Agent The identifier of the sales person who executed the sales order.
Document details Sales document category A logical groping of sales order types.
Colored fields indicate enhanced fields to standard content
8
What We will cover
• Requirement analysis
• Logical design
• Physical design
• Clean and integrating data
• Analysis
• Security design
9
Data Mapping
The data was mapped from the source to the transfer structure which was created using Application Link Enabler through Active Data Objects (ALE-ADO)
The data was again mapped from the transfer structure (as stored in the persistent storage area-PSA) to the InfoObject in the InfoCube. A list of this mapping is enclosed below
InfoObject Description Data Element Field in Transfer Structure
Source
0DISTR_CHAN Distribution channel VTWEG VTWEG VTWEG
0REVERSEDOC Cancel STORNO STORNO STORNO
0VTYPE Value type RSVTYPE WRTTP WRTTP 0VERSION Version VRSIO VRSIO VRSIO
0BILL_NUM Billing document VBELN_VF VBELN VBELN 0BILL_ITEM Item POSNR_VF POSNR POSNR
0BILL_TYPE Billing type FKART FKART FKART 0BILL_CAT Billing category FKTYP FKTYP FKTYP
0DOC_CATEG SD document category
VBTYP VBTYP VBTYP
0SALESORG Sales organization VKORG VKORG VKORG
0PROV_GROUP Commission group PROVG PROVG PROVG
0SALES_GRP Sales group VKGRP VKGRP VKGRP 0SALES_OFF Sales office VKBUR VKBUR VKBUR
0DIV_HEAD Division SPART_AK SPARA SPARA 0MATERIAL Material MATNR MATNR MATNR
0MATL_GROUP Material group MATKL MATKL MATKL 0BATCH Batch CHARG CHARG CHARG
0STGR_LOC Storage location LGORT LGORT LGORT
0CUST_GRP1 Customer group 1 KVGR1 KVGR1 KVGR1 0REBATE_GRP Volume rebate group BONUS BONUS BONUS
0PRICE_DATE Pricing date PRSDT PRSDT PRSDT 0SERV_DATE Serv.rendered date FBUDA FBUDA FBUDA
0ITEM_CATEG Item category PSTYV PSTYV PSTYV 0ITM_TYPE Item type POSAR POSAR POSAR
0PROD_HIER Product hierarchy PRODH PRODH PRODH
0SHIP_POINT Shipping point VSTEL VSTEL VSTEL 0DIVISION Division SPART SPART SPART
0PLANT Plant WERKS WERKS WERKS 0COSTCENTER Cost center KOSTL KOSTL KOSTL
0CREATEDBY Created by ERNAM ERNAM ERNAM 0CREA_TIME Time ERZET ERZET ERZET
0WBS_ELEMT PSP element PS_POSID PS_POSID PS_POSID
InfoObject Description Data Element Field in Transfer Structure
Source
0MATL_GRP_2 Material group 2 MVGR2 MVGR2 MVGR2
0MATL_GRP_3 Material group 3 MVGR3 MVGR3 MVGR3
0MATL_GRP_4 Material group 4 MVGR4 MVGR4 MVGR4 0MATL_GRP_5 Material group 5 MVGR5 MVGR5 MVGR5
0MATL_GRP_1 Material group 1 MVGR1 MVGR1 MVGR1 0CUST_GRP5 Customer group 5 KVGR5 KVGR5 KVGR5
0CUST_GRP4 Customer group 4 KVGR4 KVGR4 KVGR4 0CUST_GRP3 Customer group 3 KVGR3 KVGR3 KVGR3
0CO_AREA Controlling area KOKRS KOKRS KOKRS
0REFER_ITM Reference item VGPOS VGPOS VGPOS 0REFER_DOC Ref. document VGBEL VGBEL VGBEL
0S_ORD_ITEM Item POSNR_VA AUPOS AUPOS 0DOC_NUMBER Sales document VBELN_VA AUBEL AUBEL
0SALESEMPLY Sales employee VRTNR PVRTNR PVRTNR 0FORWAGENT Forwarding agent SPDNR PSPDNR PSPDNR
0BILLTOPRTY Bill-to party KUNRE PKUNRE PKUNRE
0SHIP_TO Ship-to party KUNWE PKUNWE PKUNWE 0CUST_GRP2 Customer group 2 KVGR2 KVGR2 KVGR2
0COMP_CODE Company code BUKRS BUKRS BUKRS 0PAYER Payer KUNRG KUNRG KUNRG
0SOLD_TO Sold-to party KUNAG KUNAG KUNAG 0CUST_GROUP Customer group KDGRP KDGRP KDGRP
0SALES_DIST Sales district BZIRK BZIRK BZIRK
0BILL_DATE Billing date FKDAT FKDAT FKDAT 0CREATEDON Created on ERDAT ERDAT ERDAT
0CH_ON Changed on AEDAT AEDAT AEDAT 0STAT_DATE Statistics date STADAT STADAT STADAT
Similar mapping tables was completed for key figures, unit of
measures and time characteristics
10
Navigational Attributes and Hierarchies
The following navigational attributes and external hierarchies are available to the InfoCube as part of the standard content
InfoObject Description Navigation attributes available?
External hierarchy available?
0D_CO_CODE Company code Yes No
0D_COUNTRY Country Yes No
0D_DIS_CHAN Distribution channel No No
0D_DIV Division No No
0D_INDUSTRY Industry code Yes No
0D_MATERIAL Material Yes Yes
0D_MTLGROUP Material group Yes No
0D_PROD_HIE Product hierarchy No Yes
0D_SALE_EMP Sales employee No No
0D_SALE_ORG Sales organization No No
0D_SOLD_TO Sold-to party Yes No
0D_VERSION Version No No
11
What We will cover
• Requirement analysis
• Logical design
• Physical design
• Clean and integrating data
• Analysis
• Security design
12
ETL Processing
Source: SAP, Sdn BW 2005
Masterdata extract programs - Customer - Material - Profit center
Transactional extract programs - Billing document header - Billing document line items - Credit memos - Debit memos
Dimensions data extracts - logistics - document details - accounting - organization (+ personnel)
Other dimensions are derived from document data
13
What We will cover
• Requirement analysis
• Logical design
• Physical design
• Clean and integrating data
• Analysis
• Security design
14
Query, web template, exceptions, calculated key figure and MDX cache
The BW queries was created using the Business Explorer (BEx) query designer tool from SAP, and published into a standard web template.
Exception rules were written to identify orders where 25% of more of the items were returned by the customer.
“Returned items’ is a calculated Key Figure (CKF) and is not physically stored in the infocube (structure).
Through the use of the reporting agent tool, the global query is published to the MDX
cache and the data set is therefore controlled by the OLAP engine for maximized speed
when subsets of the query is executed
15
What We will cover
• Requirement analysis
• Logical design
• Physical design
• Clean and integrating data
• Analysis
• Security design
16
Pre-Delivered SAP BW Workbooks by Security Roles.
Security is LDAP implemented Role based security that is shared with the transactional system and enforced through a web portal
(A) Sales Manager
(B) Key Account Manager
(C) Sales Employee
(D) Product Manager
(E) Sales Controller
(F) Sales planner
(1) Short-term profit
Incoming orders per division
Incoming orders per customer
Incoming orders per sales org.
Incoming orders – product analysis
(2) Long-term profit
Sales org. fulfillment rate
Correct order processing – sales org.
Order- and sales values per sales org.
Correct order processing – customer analysis
Order- and sales values per customer
Fulfillment rate per customer
(3) Determining profit for the period
Billing documents per sales org.
Order-, delivery- and sales quantities per customer
Order-, delivery- and sales values per sales org.
Billing documents – product analysis
(4) Early-warning information
Credit memos per sales org.
Credit memos per customer
Returns per customer
Returns per sales org.
Credit memos – product analysis
Returns by product
Deliveries per sales org. Deliveries – customer analysis
Pre-delivered queries to the Infocube use this security and the pre-delivered roles.
The new queries has to associated to a base-role or a composit- role to assure that SSO is maintained.
No new roles will be created as part of this project
17
Your Turn!!!