CAS Data Warehouse Presentation - CAS Data Warehouse by Ohio

VIEWS: 72 PAGES: 61

									Data Warehouse Presentation


Cas Data Warehouse
Overview Presentation
November 14th & 20th




    The OBM Data Warehouse    1
                   Data Warehouse Presentation
    Agenda

•    Introductions
•    CAS Data Warehouse Overview
•    CAS Data Warehouse
      – Data Flows
      – Dimensional Data Marts
      – Current Contents
      – Reports Available
      – Report Creation
      – FAQs
•    Break
•    Cognos Demo
•    Q&A

                          The OBM Data Warehouse   2
                 Data Warehouse Presentation
CAS Data Warehouse: Overview

• A data warehouse is a special purpose database used for business
  intelligence and reporting
   – Integrates data from multiple CAS subsystems
   – Loaded on a nightly basis
   – Read-only repositories for historical data optimized for data
      extraction and reporting
   – Will continue to be updated nightly until June 30, 2007 after which
      all updates will cease. It will then become the CAS Legacy Data
      Warehouse for OAKS FIN Release #3.




                        The OBM Data Warehouse                        3
                     Data Warehouse Presentation
             CAS Data Warehouse: Data Flows
                                     OBM Data Warehouse




                         Operational
                         Warehouse –
   OBM                     Staging                              OBM Data
Production      ETL                                             Warehouse
                           (OWS)            ETL Processes                        Business
 System      Processes                                      (CASDATAMART DATA)                  Cognos
                          Staging Tables                                         Metadata      COGNOS 8
                                                                                               ReportNet
                             Only - No                                           (Framework)
                         Reporting Access
CAS System




                                   The OBM Data Warehouse                                           4
                  Data Warehouse Presentation
CAS Data Warehouse: Dimensional Data Marts
                                    TRANS CODE DIMENSION


                         16   COM    COMMITTMENT



23         DETAIL        23   VOC    VOUCHER

           JOURNAL
45
           FACT



123456
                                       AGENCY DIMENSION

$5000.00
                         19   DAS      Department of Administrative Services



                         45   OBM      Office of Budget and Management




                     The OBM Data Warehouse                                    5
                             Data Warehouse Presentation
   CAS Data Warehouse: Current Contents
  •   Multi-Dimensional Warehouse: CAS Data Marts
        – The Data Warehouse Team builds Fact and Dimension tables using the
          Cognos Decision Stream tool.
        – These Fact and Dimension tables are created into subject area reporting
          packages. There is also a combined reporting package than joins all the
          reporting packages into one for cross subject area reporting. The current
          reporting packages are:
                                                                                   • OBM Project Reporting
                                           • OBM Vendor Agency Reporting           Package
• OBM Detail Journal Reporting Package
                                           Package
                                                                                   • OBM Rejected Vouchers
• OBM Summary Detail Journal Reporting
                                           • OBM Payment Card Reporting Package    Reporting Package
Package

                                           • OBM Trial Balance Reporting Package   • OBM Scheduled
• OBM Open Items Reporting Package
                                                                                   Vouchers Reporting
                                           • OBM Combined Reporting Package        Package
• OBM Vendor Statewide Reporting
Package
                                                                                   • OBM GAAP Detail
                                                                                   Reporting Package


                                         The OBM Data Warehouse                                         6
               Data Warehouse Presentation
CAS Data Warehouse: Reports Available

• Reports distributed automatically
   – RCASH04A – Non-GRF Posted Cash Transaction Listing
   – RRVSM07A – Detailed Statement of Revenue by Responsibility
     Center
   – RSPND05A – Vouchers Approval Status by Agency (Approved and
     Unapproved)
   – RTRBL01A – Summary Trial Balance by Agency by Fund
   – RTRBL07A – Daily Cash Balance by Agency by Fund
   – RCDSB03S – Vouchers Rejected by Voucher ID
   – RCDSB05A – Scheduled Vouchers Report



                      The OBM Data Warehouse                   7
                    Data Warehouse Presentation
    CAS Data Warehouse: Reports Available

•    Reports not distributed but run capable:
      – Spending by Object (special report)
      – RSPND04A – Open Encumbrance and Requisition Items by Agency by
        Fund
      – RSPND04I – Open Encumbrance and Requisition Items by Agency by
        Administrative Responsibility Center
      – RSPND04L – Open Encumbrance and Requisition Items by Agency by
        Line Item
      – RCPRJ03A – Capital Projects Lifetime Spending Actuals by Fiscal Year
        and Sub-Object
      – RCPRJ04A – Capital Projects Lifetime Spending Actuals by Fiscal Year
      – QVTOT01 – Vendor Payments by Fiscal Year
      – QVNDR01 – Vendor Multiple Address
      – RSPND15A - Expenditures By Spending Authority Code by Spending
        Responsibility Center for the Posting Fiscal Year
      – T105B – Generally Accepted Accounting Principles Report



                            The OBM Data Warehouse                             8
Data Warehouse Presentation




   The OBM Data Warehouse     9
             Data Warehouse Presentation
CAS Data Warehouse: Report Studio Presentation


This section of the presentation will cover the
  following topics:

   – Explore the Cognos Connection Web Portal
   – Build a simple report using Report Studio
   – Report on specific data using filters




                   The OBM Data Warehouse         10
          Data Warehouse Presentation
           Cognos Connection URL
•




    The URL is https://cognos.ohio.gov/cognos8/
                The OBM Data Warehouse            11
Data Warehouse Presentation
Cognos Connection Screen




    The OBM Data Warehouse   12
Data Warehouse Presentation
Cognos Connection Screen




    The OBM Data Warehouse   13
Data Warehouse Presentation
Cognos Connection Screen




    The OBM Data Warehouse   14
Data Warehouse Presentation
Cognos Connection Screen




    The OBM Data Warehouse   15
Data Warehouse Presentation
Cognos Connection Screen




    The OBM Data Warehouse   16
Data Warehouse Presentation
Cognos Connection Screen




    The OBM Data Warehouse   17
Data Warehouse Presentation
Cognos Connection Screen




    The OBM Data Warehouse   18
Data Warehouse Presentation
Cognos Connection Screen




    The OBM Data Warehouse   19
Data Warehouse Presentation
Cognos Connection Screen




    The OBM Data Warehouse   20
Data Warehouse Presentation
Cognos Connection Screen




    The OBM Data Warehouse   21
           Data Warehouse Presentation
Building A Report Studio Report – Fund Transactions




                The OBM Data Warehouse          22
           Data Warehouse Presentation
Building A Report Studio Report – Fund Transactions




                The OBM Data Warehouse          23
           Data Warehouse Presentation
Building A Report Studio Report – Fund Transactions




                The OBM Data Warehouse          24
           Data Warehouse Presentation
Building A Report Studio Report – Fund Transactions




                The OBM Data Warehouse          25
           Data Warehouse Presentation
Building A Report Studio Report – Fund Transactions




                The OBM Data Warehouse          26
           Data Warehouse Presentation
Building A Report Studio Report – Fund Transactions




                The OBM Data Warehouse          27
           Data Warehouse Presentation
Building A Report Studio Report – Fund Transactions




                The OBM Data Warehouse          28
           Data Warehouse Presentation
Building A Report Studio Report – Fund Transactions




                The OBM Data Warehouse          29
     Data Warehouse Presentation
Applying Filters – Fund Transactions




         The OBM Data Warehouse        30
     Data Warehouse Presentation
Applying Filters – Fund Transactions




         The OBM Data Warehouse        31
               Data Warehouse Presentation
Modify the Filter Expression and Validate the Filter – Fund Transactions




                      The OBM Data Warehouse                        32
      Data Warehouse Presentation
Additional Filters – Fund Transactions




          The OBM Data Warehouse         33
               Data Warehouse Presentation
Modify the Filter Expression and Validate the Filter – Fund Transactions




                      The OBM Data Warehouse                        34
      Data Warehouse Presentation
Additional Filters – Fund Transactions




          The OBM Data Warehouse         35
               Data Warehouse Presentation
Modify the Filter Expression and Validate the Filter – Fund Transactions




                      The OBM Data Warehouse                        36
Data Warehouse Presentation
Different Report Output




   The OBM Data Warehouse   37
Data Warehouse Presentation
PDF View of a Report




   The OBM Data Warehouse   38
Data Warehouse Presentation
  Saving The Report




   The OBM Data Warehouse   39
Data Warehouse Presentation
  Saving The Report




   The OBM Data Warehouse   40
     Data Warehouse Presentation
     Accessing A Saved Report




•.




         The OBM Data Warehouse   41
Data Warehouse Presentation
Running A Saved Report




   The OBM Data Warehouse   42
Data Warehouse Presentation
 Report Studio View




   The OBM Data Warehouse   43
Data Warehouse Presentation
 Re-running a Report




   The OBM Data Warehouse   44
Data Warehouse Presentation
HTML View of the Report




    The OBM Data Warehouse   45
                      Data Warehouse Presentation
    Frequently Asked Question and Sample Section

•    Enabling EXCEL Spreadsheet Processing in Microsoft Internet
     Explorer
          If the user is unable to view a Cognos report in EXCEL, then these
          instructions should resolve the problem. ** Note – If the User has any
          pop-up blockers such as Google or Yahoo toolbar, or even Internet Explorer’s
          pop-up blocker then these will need to be disabled.
•    Changing report field characteristics
          If the user is noticing that a field such as an amount field is not displaying the
          proper amount of decimal places, if the amount does not have cents, then use
          the information starting on page 50 to change this field.




                               The OBM Data Warehouse                                      46
               Data Warehouse Presentation
Enabling EXCEL Spreadsheet Processing in Microsoft Internet Explorer




                      The OBM Data Warehouse                      47
               Data Warehouse Presentation
Enabling EXCEL Spreadsheet Processing in Microsoft Internet Explorer




                      The OBM Data Warehouse                      48
               Data Warehouse Presentation
Enabling EXCEL Spreadsheet Processing in Microsoft Internet Explorer




                      The OBM Data Warehouse                      49
               Data Warehouse Presentation
Enabling EXCEL Spreadsheet Processing in Microsoft Internet Explorer




                      The OBM Data Warehouse                      50
               Data Warehouse Presentation
Enabling EXCEL Spreadsheet Processing in Microsoft Internet Explorer




                      The OBM Data Warehouse                      51
               Data Warehouse Presentation
Enabling EXCEL Spreadsheet Processing in Microsoft Internet Explorer




                      The OBM Data Warehouse                      52
               Data Warehouse Presentation
Enabling EXCEL Spreadsheet Processing in Microsoft Internet Explorer




                      The OBM Data Warehouse                      53
  Data Warehouse Presentation
Changing a Report Field Characteristic




        The OBM Data Warehouse           54
          Data Warehouse Presentation
Sample HTML Output – Changing a Report Field Characteristic




                 The OBM Data Warehouse                       55
          Data Warehouse Presentation
Sample HTML Output – Changing a Report Field Characteristic




                 The OBM Data Warehouse                       56
Data Warehouse Presentation




  Intermission




   The OBM Data Warehouse   57
                 Data Warehouse Presentation
Contact Information

Do you have follow-up questions?
Do you have a request for new user account setup’s?

Send them to: OBM.COGNOS@OBM.STATE.OH.US

These will be forwarded to the proper person at OBM for an answer to
  your questions!




                          Thank You
                       The OBM Data Warehouse                      58
Data Warehouse Presentation




   The OBM Data Warehouse   59
                Data Warehouse Presentation
Business Scenario

• Determine the open encumbrances including the open amount
  for a specific agency, in fiscal year 2006.




                      The OBM Data Warehouse                  60
         Data Warehouse Presentation
Questions and Answers




             The OBM Data Warehouse   61

								
To top