Using the Warehouse to Manage MMARS and LCM

1
Using the Warehouse to Manage MMARS and LCM

• October 5, 2006

2
Agenda

• Introduction
• State of the Warehouse and Support Information
  Helen McAfee (CIW)
• General Warehouse Overview Scott Olsen
• Financial Data Scott Olsen
• Payroll/LCM Data Elizabeth Faherty Dan Miguel
  (OSC Payroll)

3
Helen McAfee

• Query Performance
• Current Warehouse
• Warehouse Support
• User Group Meetings/Drop In Sessions
• CommonHelp
• MMARS Help Desk

4
CIW Website http//www.iw.state.ma.us/

• Applications
• Data
• Whats New? (Daily Opening Status)
• MMARS
• HRCMS
• LCM Predictive

5
General Warehouse Overview
6
Warehouse or MMARS?

• Why would I search for data in the Warehouse
  instead of MMARS?
• Warehouse Queries Offer
• More search criteria
• Ability to enter specific criteria
• Large amounts of data in one place
• CIW data can be exported to various formats
• Document details without having to open each
  document
• Ability to save. Queries can be saved and run at
  any time

7
Information Warehouse Security

• Security requests managed by Chelsea ITD Security
  Team
• CIW Security
• User ID MMARS UAID (example, CTRXXX)
• Password MMARS UAID Last Four (4) digits of
  the users social security number
• Passwords cannot be changed

8
Overview of Data in the CIW

• FUNCTIONAL Views
• Provide CIW end-users one-stop shopping across
  broad groups of data
• Support answers to common management questions
• Payments in FY2005 to Vendor VC123456789?
• What is the spending amount against a particular
  contract?
• Views have logical names tied to data content and
  include codes and descriptions
• Example M_AP_Vendor_Payments

9
Overview of Data in the CIW

• BASE views
• FACT views contain data that is counted, summed
  or measured
• REFERENCE views contain descriptive data about
  the fact data

10
Financial Data
11
Overview of Data in the CIW

• The terminology used in MMARS, LCM and HRCMS may
  not translate exactly to CIW terms
• WH_Data_Dictionary assists with translation

12
Overview of Data in the CIW

• WH_Data_Dictionary Table lists all tables and
  data elements in CIW with their definitions.
• Data Elements are terms that designate items in
  the warehouse, such as employee, organization and
  document code
• The Data Dictionary identifies each table on
  which a particular data element appears

13
Data Dictionary
TABLE NAME
FIELD NAME
BUSINESS FUNCTION
TABLE AND COLUMNDEFINITION
14
Overview of Data in the CIW

• The Information Warehouse contains data
• MMARS/LCM ? FY2005 to Present
• HR/CMS ? FY2000 to Present
• MMARS (Classic) ? FY1993 to 2004
• PCRS ? FY1995 to 2004
• PARIS ? FY1991 to 1995
• PMIS ? FY1982 to FY2000
• CAPS ? FY1988 to FY2000

15
Overview of Data in the CIW

• Departments can forecast based on existing data
• Users can manipulate the data and move
  information into different formats
• CIW can transform vast amounts of data into
  useful information

16
Overview of Data in the CIW

• Data in the CIW is organized by business
  functions which are grouped by tables to
  facilitate retrieval of information
• MMARS is categorized according to the following
  business functions

17
Overview of Data in the CIW
The Warehouse is categorized according to the
following business functions
18
Table Name Example
AP for Accounts Payable
M_AP_Vendor_Payments
Table Name
M for MMARS
19
Accounts Payable (AP)

• QuestionHow many payments have been made to
  Vendors in Period 1 of FY2007 by my department,
  from a particular appropriation?

20
Query Results
M_AP_Vendor_Payments
21
Accounts Receivable (AR)

• What were my open Accounts Receivable and
  collected Revenue by Department and fiscal year
  as of a given date?

22
Query Results
M_AR_Outstanding_Receivables_Detail
23
Budgets (BU)

• Can I create a report on current Appropriation
  amounts and attributes for a specific Budget
  Fiscal Year and specific agency?

24
Query Results
M_BU_Central_Budget_Structure
25
Procurement (PR)

• What are the current year encumbrances and the
  open or paid balances?

26
Query Results
M_PR_Encumbrance_Detail
27
Reference Tables

• Can I generate a list of my Programs along with
  Program Name, Short Name, and Sub Account Number
  from the Warehouse?

28
Query Results
M_Reference_Program
29
Payroll Warehouse

• Office of the Comptroller

30
Agenda

• Payroll Systems Overview
• Payroll Tables in the Warehouse
• Overview of Payroll Starter Queries
• Tips on Query Building
• Q A

31
Payroll Systems Overview

• HRCMS
• LCM
• MMARS

32
Payroll Tables in the Warehouse

• HRCMS Tables
• LCM Tables
• MMARS Tables

33
Overview of Payroll Starter Queries

• How much have we spent on payroll, by
  appropriation type, in FY07?
• From LCM labor history table

34
And a pie chart
35
More Payroll Queries

• I need projection to ensure payroll is fully
  funded before payroll runs for my state accounts
  for the remainder of FY2007.

36
Payroll Projection
37
(No Transcript)
38
More on Payroll Queries

• What is the estimated headcount and total of
  all Employees and Contractors paid out of Federal
  Grants this payperiod?

39
Payroll Queries

• How can I get employees and FTE counts on my last
  payroll?
• And you can get this by appropriation type, unit,
  and employment type as well.

40
Q A