Decision Support User Group Meeting October 2004

1
Decision Support User Group Meeting
October 2004
Case Study Using ODBC Access to the EDW to
Automate Procurement Business Processes
2
Decision Support User Group Meeting
October 2004
Dan McCue, drmccue_at_uiuc.edu Facilities
Services (formerly OM) Business Intelligence
Specialist
3
Decision Support User Group Meeting
October 2004

• Day-to-day responsibilities
• Retrieve/download data (many sources)
• Provide it to business people who need it
• A link to a web site is typically preferred
  within FS
• Other methods spreadsheet or text file

4
Decision Support User Group Meeting
October 2004

• Why call this a Case Study?
• A distinct business need
• Not the only way to meet the need
• Still learning how to make best use of EDW data
  via ODBC

5
Decision Support User Group Meeting
October 2004

• The Business Need
• Who
• Sonya Chambers, Dir. of Procurement Services
• FS Staff
• What
• Sonyas staff creates requisitions for FS
• Need to know duration of requisition to PO
  process
• How
• Display information on an FS website

6
Decision Support User Group Meeting
October 2004

• The Business Need (as seen by a database guy)
• Procurement terminology
• Requisition (departmental request to make a
  purchase)
• Purchase Order (PO - authorization to purchase)
• Need to know how long it takes before requisition
  is converted to PO
• Want to display information on FS web site
• FS Procurement staff performance measurement
• FS general staff info about their requisitions

7
Decision Support User Group Meeting
October 2004

• The Challenges
• As a database guy I work with data structures,
  programming languages, and technical designations
  on a daily basis
• Unfamiliar with daily procurement business
  processes
• Unfamiliar with Banner forms and field labels
• Speak a different language than my business
  customers

8
Decision Support User Group Meeting
October 2004

• The Challenges
• Business managers and their staffs know the
  desired data is in Banner but often can only
  provide Banner form names and field labels
• There is no existing resource which translates
  directly from Banner form, to Banner table, to DS
  table
• Utilize the DS Metadata
• Contact the DS Finance Functional Area Coordinator

9
Decision Support User Group Meeting
October 2004

• The Challenges
• Determining the best approach to obtain, format,
  and display the data
• Business Objects WebIntelligence or 5i
• ODBC connection
• Depends on
• Availability of needed data in a Universe
  -vs-availability of needed data in the EDW tables
• Who needs access to the data once you have it

10
Decision Support User Group Meeting
October 2004

• Data Requirements
• Banner-specific data description
• Banner form name
• Caption of field on Banner form
• Description of data contained in each field
• Sample Report Layout
• Column headings
• Groupings of related columns
• Definitions of business calculations

11
Decision Support User Group Meeting
October 2004
Data Description
12
Decision Support User Group Meeting
October 2004
Example Report Layout
13
Decision Support User Group Meeting
October 2004

• Reporting Requirements
• Generate daily
• Post on a Facilities Services web site
• Use as a management report, tracking
• Productivity of Procurement processes and staff
• Start-to-finish metrics for the procurement
  process, from request to purchase order approval
• Added benefit FS staff can get updates on the
  web site, freeing Procurement staff from lookups

14
Decision Support User Group Meeting
October 2004

• Data Source Selection Considerations
• Not all fields needed were available in a
  Business Objects Universe
• More fields are available in EDW tables
• Some fields were not available in either
• Submitted official request to add fields to EDW
• Request was reviewed
• Certain fields were added
• Others were not (warehouse viability issues)

15
Decision Support User Group Meeting
October 2004

• Building The Query
• Map requested Banner fields to EDW columns
• Utilize available DS metadata resources
• Source-to-target mapping (Banner to EDW)
• EDW Table-Column listings
• Logical data model (entity-relationship diagram)
• Physical data model (entity-relationship diagram)
• Finance FAC helped work out business logic

16
Decision Support User Group Meeting
October 2004
Example Map Requested Data to EDW
17
Decision Support User Group Meeting
October 2004

• Issues Encountered During Data Mapping
• Which EDW column matches requested field?
• Several date columns available in the EDW
• Make sure column definition (in EDW Table-Column
  listing matches, names can be misleading (i.e.
  _TRAN_DATE implies transaction, but refers to
  last update in Banner, not date of business
  transaction)
• Data unavailable request discussed earlier

18
Decision Support User Group Meeting
October 2004

• Reviewing the Data
• EDW source data is an Oracle database
• ODBC connection available through
• Microsoft Excel (for quick data dumps)
• Microsoft Access (for limited SQL query
  functions)
• Microsoft SQL Server (for most query flexibility)
• Used SQL Server for most data manipulation
• Used Access for irregular data (NULLs, dates
  outside of SQL Server range)

19
Decision Support User Group Meeting
October 2004

• Development Process
• Pull source data into SQL Server
• Gain familiarity with the data
• Play nice in the sandbox no runaway queries
• Which rows?
• Tried all rows where reasonable based on volume
  of data (goal no data left behind)
• Queried only Facilities Services data

20
Decision Support User Group Meeting
October 2004

• Tables Queried for Requisition and PO Data
• T_POST_DOC (limited to Reqs and POs)
• T_REQ (all)
• T_PO (all)
• T_REQ_LINE_ITEM (all)
• T_PO_LINE_ITEM (all)
• T_OL_DETL (only records with FS org codes)
• Using date/time stamp on OL detail to simulate
  requisition approval date not REQ_TRAN_DATE

21
Decision Support User Group Meeting
October 2004

• First Query Strategy get all data at once
• Did not work due to realities of business process
• Business need all reqs but not all POs (outer
  join required) could not use T_REQ_PO_MAPPING
• 1 req can result in multiple POs
• 1 PO can be created to handle multiple reqs
• Tried using T_REQ_ACCTG_DETL and T_PO_ACCTG_DETL
  in query (not shown in example) no good way to
  match req line items to PO accounting detail
  records

22
Decision Support User Group Meeting
October 2004
Get It All Query SELECT T_POST_DOC_1.POST_DOC_N
BR AS REQ_NUMBER, T_POST_DOC_2.POST_DOC_NBR AS
PO_NUMBER, T_PO.PO_VEND_NAME AS
VENDOR,T_PO.PO_TYPE_IND AS PO_TYPE,
T_POST_DOC_2.POST_DOC_STATUS_IND AS PO_STATUS,
T_PO.PO_BYR_CD AS BUYER_CODE, T_PO.PO_RQSTR_NAME
AS REQ_NAME, T_PO.ORG_CD AS REQ_ORG_CD,
T_REQ.REQ_ORD_DT AS REQ_ORDER_DATE,
SUM(T_REQ_LINE_ITEM.REQ_LINE_ITEM_NET_AMT) AS
REQ_TOTAL, SUM(T_PO_LINE_ITEM.PO_LINE_ITEM_NET_AMT
) AS PO_TOTAL FROM T_POST_DOC T_POST_DOC_1,
T_REQ, T_REQ_LINE_ITEM, T_POST_DOC T_POST_DOC_2,
T_PO, T_PO_LINE_ITEM, T_REQ_PO_MAPPING WHERE T_POS
T_DOC_1.EDW_FIN_DOC_ID T_REQ.EDW_FIN_DOC_ID AND
T_REQ.EDW_FIN_DOC_ID T_REQ_LINE_ITEM.EDW_FIN_DOC
_ID AND T_REQ.EDW_FIN_DOC_ID
T_REQ_PO_MAPPING.REQ_EDW_FIN_DOC_ID
() AND T_POST_DOC_2.EDW_FIN_DOC_ID
T_PO.EDW_FIN_DOC_ID AND T_PO.EDW_FIN_DOC_ID
T_PO_LINE_ITEM.EDW_FIN_DOC_ID AND T_PO.EDW_FIN_DOC
_ID T_REQ_PO_MAPPING.PO_EDW_FIN_DOC_ID AND
(T_PO.ORG_CD IN ( All Facilities Services
Org Codes) GROUP BY T_POST_DOC_1.POST_DOC_NBR,
T_POST_DOC_2.POST_DOC_NBR, T_PO.PO_VEND_NAME,
T_PO.PO_TYPE_IND, T_POST_DOC_2.POST_DOC_STATUS_IND
, T_PO.PO_BYR_CD, T_PO.PO_RQSTR_NAME,
T_PO.ORG_CD, T_REQ.REQ_ORD_DT
23
Decision Support User Group Meeting
October 2004

• Second Query Strategy divide conquer
• Get all relevant requisition information
• Get all relevant purchase order information
• Combine the two into one unified data view
• Eases many-to-many relationship problem
• Makes outer join more straightforward (display
  all requisitions, and display PO information if
  it is available)

24
Decision Support User Group Meeting
October 2004
Requisition View (vReqInfo) SELECT TOP 100
PERCENT PD.POST_DOC_NBR AS REQ_NUMBER,
R.REQ_TRAN_DT AS REQ_TRAN_DATE, R.REQ_CLS_DT AS
REQ_CLOSE_DATE, R.REQ_CNCL_IND AS
REQ_CANCEL_IND, R.REQ_CLS_IND AS REQ_CLOSE_IND,
R.REQ_VEND_NAME, R.REQ_RQSTR_NAME, R.ORG_CD AS
REQ_ORG_CODE, R.RQSTR_TELE_AREA_CD AS
REQ_AREA_CODE, R.RQSTR_TELE_NBR AS
REQ_PHONE_NUMBER, R.RQSTR_TELE_EXT AS REQ_EXT,
RLI.REQ_LINE_ITEM_NBR, RLI.REQ_LINE_ITEM_NET_AMT
AS REQ_AMT, OD.OL_DETL_POST_DTTIME, OD.FY_CD,
RLI.PO_EDW_FIN_DOC_ID, OD.OL_DETL_EFF_DT FROM
dbo.T_POST_DOC PD INNER JOIN
dbo.T_REQ R ON PD.EDW_FIN_DOC_ID
R.EDW_FIN_DOC_ID INNER JOIN
dbo.T_REQ_LINE_ITEM RLI ON R.EDW_FIN_DOC_ID
RLI.EDW_FIN_DOC_ID LEFT OUTER JOIN
dbo.T_OL_DETL OD ON R.EDW_FIN_DOC_ID
OD.EDW_FIN_DOC_ID WHERE (R.ORG_CD IN (All
Facilities Services Org Codes )) AND
(R.COA_CD 1)
AND
(OD.OL_DETL_RULE_CLS_CD 'REQP') GROUP BY
PD.POST_DOC_NBR, R.REQ_TRAN_DT, R.REQ_CLS_DT,
R.REQ_CNCL_IND, R.REQ_CLS_IND, R.REQ_VEND_NAME,R.R
EQ_RQSTR_NAME, R.ORG_CD, R.RQSTR_TELE_AREA_CD,
R.RQSTR_TELE_NBR, R.RQSTR_TELE_EXT,
RLI.REQ_LINE_ITEM_NET_AMT, RLI.REQ_LINE_ITEM_NBR,
OD.OL_DETL_POST_DTTIME, OD.FY_CD,
RLI.PO_EDW_FIN_DOC_ID, OD.OL_DETL_EFF_DT ORDER BY
PD.POST_DOC_NBR, RLI.REQ_LINE_ITEM_NBR
25
Decision Support User Group Meeting
October 2004
Purchase Order View (vPOInfo) SELECT TOP 100
PERCENT PD.POST_DOC_NBR AS PO_NUMBER,
PD.POST_DOC_STATUS_IND AS PO_STATUS,
P.PO_TYPE_IND AS PO_TYPE, P.PO_BYR_CD AS
BUYER_CODE, P.PO_PRINT_DT AS PO_PRINT_DATE,
P.PO_VEND_NAME, P.PO_RQSTR_NAME, P.ORG_CD AS
PO_ORG_CODE, SUM(PLI.PO_LINE_ITEM_NET_AMT) AS
PO_AMT, PD.EDW_FIN_DOC_ID FROM dbo.T_PO P
INNER JOIN
dbo.T_PO_LINE_ITEM PLI ON P.EDW_FIN_DOC_ID
PLI.EDW_FIN_DOC_ID INNER JOIN
dbo.T_POST_DOC PD ON P.EDW_FIN_DOC_ID
PD.EDW_FIN_DOC_ID WHERE (P.ORG_CD IN (All
Facilities Services Org Codes )) GROUP BY
PD.POST_DOC_NBR, PD.POST_DOC_STATUS_IND,
P.PO_TYPE_IND, P.PO_BYR_CD, P.PO_PRINT_DT,
P.PO_VEND_NAME, P.PO_RQSTR_NAME, P.ORG_CD,
PD.EDW_FIN_DOC_ID
26
Decision Support User Group Meeting
October 2004
Unified View (vReqConvToPO) SELECT TOP 100
PERCENT R.REQ_NUMBER, R.REQ_TRAN_DATE,
R.REQ_CLOSE_DATE, R.REQ_CANCEL_IND,
R.REQ_CLOSE_IND, R.REQ_VEND_NAME,
R.REQ_RQSTR_NAME, R.REQ_ORG_CODE,
R.REQ_PHONE_NUMBER, R.REQ_EXT, SUM(R.REQ_AMT) AS
REQ_AMT, R.OL_DETL_POST_DTTIME AS OL_POST_DATE,
R.FY_CD AS OL_FY, P.PO_NUMBER, P.PO_TYPE,
P.BUYER_CODE, P.PO_PRINT_DATE, P.PO_VEND_NAME,
P.PO_RQSTR_NAME, P.PO_ORG_CODE, P.PO_AMT,
R.OL_DETL_EFF_DT AS OL_EFF_DATE FROM
dbo.vReqInfo R LEFT OUTER JOIN
dbo.vPOInfo P ON R.PO_EDW_FIN_DOC_ID
P.EDW_FIN_DOC_ID GROUP BY R.REQ_NUMBER,
R.REQ_TRAN_DATE, R.REQ_CLOSE_DATE,
R.REQ_CANCEL_IND, R.REQ_CLOSE_IND,
R.REQ_VEND_NAME, R.REQ_RQSTR_NAME,
R.REQ_ORG_CODE, R.REQ_PHONE_NUMBER, R.REQ_EXT,
R.OL_DETL_POST_DTTIME, R.FY_CD, P.PO_NUMBER,
P.PO_TYPE, P.BUYER_CODE, P.PO_PRINT_DATE,
P.PO_VEND_NAME, P.PO_RQSTR_NAME, P.PO_ORG_CODE,
P.PO_AMT, R.OL_DETL_EFF_DT
27
Decision Support User Group Meeting
October 2004

• From EDW Data To FS Web Site
• Many test queries, comparisons to Banner data and
  requirements revisions led to the final results
• One key characteristic of EDW data up-to-date
  through yesterday, todays Banner data is
  tomorrows EDW data

28
Decision Support User Group Meeting
October 2004

• From EDW Data To FS Web Site
• Wrote Active Server Page using VBScript
• Two different web page presentations
• Procurement Services staff (metrics)
• Facilities Services users (self-serve info)

29
Decision Support User Group Meeting
October 2004
Result
30
Decision Support User Group Meeting
October 2004

• Metrics
• Four date columns appear in this report
• Proc Start requisition created in Banner
• Proc Approval requisition approved in Banner
• CP Open Req Campus Purchasing begins work to
  convert requisition to PO
• CP Issue PO Campus Purchasing releases PO
  number to be provided to vendor

31
Decision Support User Group Meeting
October 2004

• Metrics
• Each date resides in an EDW column
• Proc Start T_REQ.REQ_TRAN_DATE
• Proc Approval T_OL_DETL.OL_DETL_POST_DTTIME
• CP Open Req T_REQ.REQ_CLOSE_DATE
• CP Issue PO T_PO.PO_PRINT_DATE

32
Decision Support User Group Meeting
October 2004

• Metrics
• 4 time intervals are calculated (in days) and
  displayed on the Procurement staff web page
• Proc Processing Days Proc Approval Proc Start
• CP In Queue Days CP Open Req Proc Approval
• CP PO Processing Days CP Issue PO CP Open Req
• Req to PO Processing Days CP Issue PO Proc
  Start

33
Decision Support User Group Meeting
October 2004

• Summary
• Business need required ODBC access to EDW
• Desired data was defined and mapped to EDW
• Query complexity required an extract of relevant
  data from EDW to local staging tables
• Duality of data (requisitions, purchase orders)
  and requirement of all requisitions led to
  division of effort 2 intermediate views
  combined into 1 unified final view

34
Decision Support User Group Meeting
October 2004

• Acknowledgements
• Sonya Chambers business requirements
• Beth Ladd DS Finance FAC
• DS Staff
• Directions for set up of ODBC connection to EDW
  Oracle database
• Metadata documentation (STM, Column/Table
  listings, data models)

35
Decision Support User Group Meeting
October 2004
Questions and/or comments? Dan
McCue drmccue_at_uiuc.edu