Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

Data Warehouse Training Manua New

VIEWS: 21 PAGES: 8

									                    Data Warehouse Training
                     Systems Coordination
                        T ra i n i n g A g e n d a

   What is a data warehouse?
   What are some examples of FRS account questions that can be answered by a data
    warehouse report?
   What pre-defined Excel reports are available to use with the data warehouse and
    how do I access them?
   How do I access the tables in the data warehouse through Excel?
   How do I write a customized query?




                   What is a data warehouse?
   Information from production databases stored in a separate area for querying
   May be accessed with Access, Excel, SAS, or Brio Query (this training focuses on
    Excel)
   Information is easily accessible. Most behind the scenes joining of tables is
    automatic.
   Free to all departments.




      What are some examples of FRS account
      questions that can be answered by a data
                 warehouse report?
                            Class Participation Time!




Data Warehouse Query Class Examples
Data Warehouse Example 1: Attribute Related: FS_ACCOUNT
What accounts have                                    (attribute) that
Is greater than   Is less than          Equals        Contains           Begins with

                                 (attribute value).

Data Warehouse Example 2: Transaction Related:FRS_TRANSACTION
Question:


Account_num

Object_code

Fiscal_year

Fiscal_year_month

Transaction_Descr

Transaction_Amt

Reference_1

Reference_2

Effect_cash

Data Warehouse Example 3: Balance Related: FS_ACCT_BALANCE
Question:


Acct_num

Acct_object_code

Fiscal_year

Fiscal_year_month

Data Warehouse Example 4: Combining tables : FS_ACCOUNT and
  FRS_TRANSACTION
Question:  For division 060, what expenditures (that effect cash) have been paid from object
code 4130?

Acct_Division =060

Acct_Object_code = 4130

Effect_cash = Y
    What pre-defined Excel reports are available?
 URL for Spreadsheets
  – ECU Home Page (www.ecu.edu)
  – Administration
  – Financial Services
  – Data Warehouse
  – Data Warehouse User Files
    – http://www.ecu.edu/financial_serv/data/User%20Files.htm




    What pre-defined Excel reports are available?
 Balance by Account
 Cash Transaction by Account
 Monthly Balance Compare
 Monthly Balance by Account (across FY)
 Monthly Budget Report (FBM90-91)




     How do I use the pre-defined Excel Sheets?
   If given the option, choose enable refresh when opening spreadsheet.
   Enter parameters (options) for the report.
   Follow directions to refresh data (not required in the monthly 90-91 budget
    spreadsheet)
   Follow directions to update pivot table
   Balances and transactions are up to date through the prior night.

                         Class Room Exercise 1
 Using a pre-defined Excel Report
  – Balance Exercise
  – Transaction Exercise
  – Monthly Budget Report Exercise




     How do I access the tables with MS Excel?
   Start Microsoft Excel
   Select from the menu - Data, Get External Data, New Database Query
   Select from the dialog box – DB2P, OK
   Type your user ID and password
    – DWH2463
    – Password:__________________
   CONGRATULATIONS, YOU ARE IN THE DATA WAREHOUSE NOW!



             How do I write a customized query?
 Step One: Asking the question
 Step Two: Deciding which table or combination of tables you will need
   – Detailed Transactions = FRS_TRANSACTION
   – Balances = FS_ACCT_BALANCE
   – Account Attribute Info = FS_ACCOUNT




             How do I write a customized query?
 Step 3: Selecting Data Warehouse Tables
   – Select the table you decided to access above by single clicking
   – Push the first arrow button (> symbol)
   – Field names should appear on the right
   – Repeat the steps above for each table you have chosen to access
   – Click the “Next >” button to continue




             How do I write a customized query?
   Step 4: Filtering Columns
    – Data must be filtered! The transaction table contains 20 Million + records.
    – Decide which columns you want to filter.
    – Select that column name from the column to filter list
    – Limit the data by selecting an operator (equals, does not equal, contains, greater than, etc.)
      from the first drop down box
    – Type a value in the second drop down box




             How do I write a customized query?
 Step 4: Filtering Columns
   – If you have multiple requirements for that column, select and/or button and
      repeat the above steps.
    – You may also choose another column to continue filtering the data.
   – When finished, select next to continue.




            How do I write a customized query?
 Step 5: Sort Order (Optional)
   – From the drop down list, if desired, choose a column name by which you prefer
     to sort the report.
   – Reports are sorted in ABC (ascending order). If you need descending order
     (ZYX) select the descending button.
   – You may sort by additional fields if desired.
   – Click next to continue


            How do I write a customized query?
 Step 6: THE BIG FINISH!
   – Select “Return Data to Microsoft Excel”
   – Push the finish button
   – Select OK
   – Wait while the “world” spins


       Repeat Steps 1 – 6 for Other Classroom
                     Examples
                  Using the Data
 Data may be used with any Excel function
 Adding and deleting data or columns will not effect the real data
 Try Data Subtotal function for any easy way to subtotal your data
 Try Data Pivot Table for reports that look like the ones on the web
                             Getting Assistance
 System Coordination
   – Penney Doughtie, 328-2663
   – Laura Foltz, 328-2463
                             Data Warehouse Helpful Tips

             Data Needed                                   Suggested Table
Detailed Transactions without Payroll transactions     FRS_TRANSACTION
Detailed Transactions including Payroll transactions   FS_TRANSACTION
Balances                                               FS_ACCT_BALANCE
Account Attribute Information                          FS_ACCOUNT
The Data Warehouse user id and password are the same as your ECU MVS Option D (Production
CICS/ESA) password. If you are prompted to update your password when creating a DWH query,
close the Excel window/spreadsheets. Sign onto option D (Production CICS/ESA) on ECU MVS
and change your password. Next, restart Excel. If you are locked out of the system, contact the
University‟s Help Desk at phone extension 6866.

The FRS_Transaction Table does not include payroll transactions.

The FS_Transaction Table contains all transactions including the (Transaction/Entry Code 067)
payroll transactions with social security numbers. The FS_Transaction Table is available for staff
members that have approved access to Human Resources data.

In the FS_Transaction table, the „CD Identification‟ field is the social security number.

In both the FS_Transaction Table and the FRS_Transaction Table, for Accounts Payable
transactions/entry code 048, the „CD Identification‟ field is the voucher number with a four-zero
prefix. Reference 2 has seven characters in the data field and represents the check number. The
first number is the second digit of the disbursement bank and the last six digits are the Accounts
Payable check number.

By using the field „Effect Cash‟ in the criteria as „equal to‟ capital Y, the data returned will only
include receipt transactions (03x), disbursement transactions (04x), and journal entry transactions
(06x). To retrieve only budget transactions (02x), use criteria „equal to‟ capital B. To retrieve
only encumbered transactions (05X), use criteria „equal to‟ capital E.

If you use the „Transaction Description‟ field to search for a vendor name, type the vendor name
exactly as it appears in FRS. The value is case sensitive and typically is upper case.

When using the batch date as the criteria, the date must be keyed as mm/dd/yyyy. All Data
Warehouse date fields have the same format.

To efficiently execute the query, use the key fields in the order that they appear in the tables on
page eight of the Data Warehouse Manual.
The FS_Transaction table and the FRS_Transaction table contains detailed data transactions from
July 1, 1995 through last night.

The FS_Acct_Balance table contains fiscal year summary account balances for the period July 1,
1995 to June 30, 2000. However, for the period beginning August 2000, each individual month
end balance is available.

                                      FRS Transaction Codes

Budget Transactions
020    Original Budget
021    Revised Budget
022    Budget Transfer
026    Budget Carried Forward From Prior Year
028    Last Year‟s Budget
029    Future Year‟s Budget

Receipt Transactions
030    Cash Receipt
032    Revenue Refund
033    Cash By Requisition
034    Cash Corrections
038    Cash Requisitions

Disbursement Transactions
040    Cash Disbursement
042    Transfer Out
044    Disbursement Adjustment
045    Canceled Check
046    Check from A/P to be Accrued (July)
047    Check from Financial Aid
048    Check from A/P
049    Check from Student Loans

Encumbrance Transactions
050    Miscellaneous Liquidation
052    Original Standing Order Requisition, Petition to Travel
053    Purchase Order from POS
054    Requisition from POS
055    Capital Improvement Enhancements
058    Miscellaneous Liquidation
059    Encumbrance that will Reject in FBD010 if Insufficient BBA

Journal Entry Transactions
060    Journal Entry
061    Prorates
062    Journal Entry to Override a Freeze Flag and Fin Aid EFT Batches
063    ID Charges
064    ID Charges to be Accrued
065    Reversals 13th Month
066    Accruals 13th Month
067    Payroll Transactions
068    Patient Billing/Sales Tax Entries
069    Compound Journal Entry
090    Bank Transfer Transaction
098    Beginning Balance Transaction


Data Warehouse Training Evaluation                Date:

1. How well did you understand the FRS terminology used in class?
     a. Not at all – I often felt confused about the terminology.
     b. Pretty well – I understood most of the terminology.
     c. Piece of cake – I understood all of the FRS terminology.
2. How well did you understand the Excel skills used in this class?
     a. Not at all – I often felt lost about where to click.
     b. Pretty well – After the examples, I was able to navigate.
     c. Piece of cake – Navigation was not an issue.
3. How often to you think you will use the Data Warehouse?
        a. Not at all – I did not see any application to my job.
        b. Pretty often – I‟ll be a casual user.
        c. All the time – I am excited about using what I learned today.
4.   How well prepared do you feel for using the Data Warehouse?
        a. Not at all – I would not know where to begin.
        b. Pretty well – With some practice, I think I can do this.
        c. Piece of cake – I‟m ready to try this back in my office!
5.   How easy was the instructor to understand?
        a. Not at all – I felt lost most of the class.
        b. Pretty well – I struggled with part of the presentation.
        c. Piece of cake – I feel like I understood the presentation.
6.   How helpful were the hand outs?
        a. Not at all – I will never use them again.
        b. Pretty helpful – With a lot of notes, the handouts will help.
        c. Essential – I will definitely refer back to the handouts.
7.   How helpful were the hands on exercises using the pre-defined excel reports from the web
     page?
        a. Not at all – I will never use them.
        b. Pretty helpful – I may use them again.
        c. Essential – Definitely include these in the next class.
8.   How helpful were the exercises worked as a class?
        a. Not at all – This did not add to my understanding.
        b. Pretty helpful – Working as a group helped me learn.
        c. Essential – Definitely include these in the next class.
9.   How helpful were the free time exercises worked at the end of class?
        a. Not at all – This did not add to my understanding.
        b. Pretty helpful – Working on my own helped me learn.
        c. Essential – Definitely include these in the next class.

My excel knowledge is                Basic          Intermediate         Advanced
My FRS knowledge is                  Basic          Intermediate         Advanced
I have attended FRS training.        No             Yes
The instructor was                   Poor           Average              Exceptional
The class was                        Poor           Average              Exceptional
The materials were                   Poor           Average              Exceptional

Ideas to improve the class:

Other comments:

                       __________________________________________

								
To top