Data Warehouse User's Guide

Document Sample
Data Warehouse User's Guide Powered By Docstoc
					Data Warehouse User’s Guide

  • Accessing the Data
    Warehouse
  • Pre-authored Standard
    Reports

Prepared by:
Virginia Department of Social Services
Division of Information Systems
November 2006
Data Warehouse User’s Guide



                                                   TABLE OF CONTENTS

1. Department of Social Services Data Warehouse ............................................ 3
   1.1 What are Pre-authored Standard Reports?..........................................................................3

2. Data Warehouse Overview................................................................................ 4
   2.1 Accessing the Data Warehouse and Security ......................................................................4
     2.1.1 Exercise: Logging on to the Data Warehouse ...............................................................4
   2.2 Data Warehouse Home Page ..............................................................................................7
     2.2.1 Exercise: Accessing Data Marts from the Public Folders Tab .......................................9

3. Pre-authored Standard Reports ..................................................................... 12
   3.1 The Report Prompt Page ...................................................................................................12
     3.1.1 Exercise: Accessing, Selecting Prompt Values and Running a Pre-authored Standard
     Report.....................................................................................................................................13
   3.2 The Standard Report Tool Bar/Home/Return Icons ...........................................................16
     3.2.1 Exercise: Convert a Pre-authored Standard Report to EXCEL...................................18
   3.3 Saving Reports...................................................................................................................20
     3.3.1 Exercise: Saving a Report to My Folders....................................................................21
   3.4 Printing Tips for Pre-authored Standard Reports ...............................................................22
   3.5 Email a Report ...................................................................................................................24
     3.5.1 Exercise: Email a Report ............................................................................................24




Virginia Department of Social Services                                                                                 Published November 2006
Division of Information Systems                                                                                                              2
Data Warehouse User’s Guide


1. Department of Social Services Data Warehouse
The purpose of this guide is to instruct Data Warehouse users on the following:

          how to access the Department of Social Services (DSS) Data Warehouse
          how to access Pre-authored Standard Reports
          how to run Pre-authored Standard Reports
          how to convert Pre-authored Standard Reports to other formats (PDF, EXCEL, CSV)
          how to save Pre-authored Standard Reports
          how to email Pre-authored Standard Reports

A separate user guide is available for Analysis Studio Cube users. A link is provided on the Data Warehouse
home page.

Note: Not all data marts have Pre-authored Standard Reports.

This training guide was designed for all data warehouse users and the exercises included in the guide use
data from several data marts. If, as you work through the exercises, you find you do not have access to the
data mart used in the exercise; simply select another data mart to work with.

1.1 What are Pre-authored Standard Reports?
Pre-authored Standard Reports are canned reports defined by the business user and are available in several
of the DSS data marts. The reports are accessible to all business users with authorized Data Warehouse
access to the business’s data mart. Most reports contain a prompt page that allows the user to customize the
report output by selecting a few report parameters such as locality and report month. Pre-authored Standard
reports generally display in HTML format but may be converted to PDF, EXCEL or comma separated value
(CSV) files. CSV files can be produced more quickly than EXCEL files and can also be read by EXCEL.
PDF is normally the best choice if you want to print the entire report, as the default format of HTML only
allows printing one page at a time.




Virginia Department of Social Services                                                   Published November 2006
Division of Information Systems                                                                                3
Data Warehouse User’s Guide


2. Data Warehouse Overview
2.1 Accessing the Data Warehouse and Security
The DSS Data Warehouse is accessed through the Data Warehouse link on the DSS Local Agency Home
Page (http://www.localagency.dss.state.va.us/).

The Data Warehouse is accessible to all authorized users with a valid Network (LDAP) User ID and
Password. If you need access to the Data Warehouse, the Data Warehouse Request Form is available from
the Data Warehouse Webpage. Until you have submitted the request form and have been assigned to the
proper groups by DIS Security, you will not be able to access any of the data warehouse applications.




                                                   Click on the Data
                                                   Warehouse link to access
                                                   the Data Warehouse
                                                   Webpage.




2.1.1 Exercise: Logging on to the Data Warehouse

                    From the Local Agency Home Page, Click on Data Warehouse (DW).
                    The Local Agency Data Warehouse home page displays.

                         The DW home page contains links to the following
                            Analysis Studio User’s Guide
                            DW Access Request Form
                            DW Questions and Answers
                            Instructions to DW and Standard Reports User’s Guide
                            Quick Start Guide to Accessing Medicaid Enrollee Reports
                            DW Log On.

                    From the Links box, Click on DW Log On.




Virginia Department of Social Services                                                 Published November 2006
Division of Information Systems                                                                              4
Data Warehouse User’s Guide




                                     Click on one of the links to access one
                                     of the (3) User Guides, the DW
                                     Access Request Form and the Data
                                     Warehouse Question.




               The Data Warehouse Log on Screen displays.

                    Enter your User ID.
                    Enter your LDAP Password.
                    Click OK.




The Data Warehouse home page displays within your Web browser.




Virginia Department of Social Services                                         Published November 2006
Division of Information Systems                                                                      5
Data Warehouse User’s Guide




Virginia Department of Social Services   Published November 2006
Division of Information Systems                                6
Data Warehouse User’s Guide


2.2 VDSS Data Warehouse Home Page




The Home Page (or Public Folders tab) is your portal to the DSS business areas whose data is accessible
through the Data Warehouse interface. The data marts links are preceded by yellow folders . Business
needs determine the data marts a user will have access to. Home pages are unique and will differ by user.

Note: Business needs determine the data that is available in the business areas data mart. For example, a
system such as ADAPT has thousands of data elements associated with the Applications, Cases and Clients
receiving Food Stamps, TANF and Medicaid. Not all data elements are included in the ADAPT data marts.

The Data Warehouse will always open to the Public Folders tab. Entries in Public Folders are shared and
utilized by many users.

My Folders is an individual online filing cabinet used to store (save) favorite or frequently used reports.
Entries in My Folders can not be shared with other data warehouse users.

Log Off will terminate your Data Warehouse connection.

Note: The Reference and Instructional Material folder contains a copy of the ADAPT Data Dictionary. As
they are developed, data dictionaries for all Data Warehouse data marts will be added to the folder. A copy
of the data marts data dictionary will also be available within the data marts folder.



Virginia Department of Social Services                                                     Published November 2006
Division of Information Systems                                                                                  7
Data Warehouse User’s Guide


The Public Folders tool bar, detail view below, is located across the top right hand side of the screen.
Scrolling your cursor over the icons will provide a pop-up description. The privileges associated with your
User ID will determine the tools you have access to. For example, a local or state DSS staff member will not
be able to delete or create a New Folder      in Public Folders. However, they will be able to create new
folders and delete existing folders in My Folders.


                    Search for a
                    report in the
                    data warehouse                                                       Public
                                                                                         Folders
                                                                                         Tool Bar



                                                                                           Display
                                                                                           additional
                                                                                           menu entries




The Search field locates menu entries such as reports, analysis cubes and reference materials whose
name matches the string entered into the search criteria. Search from the highest level folder you wish to
include in your search results. Entries for which you have access permissions are included in the results.

The Screen Advance icon                  moves the screen forwards or backwards allowing additional menu
entries to display.

Tools contains a link to a Cognos Quick Tour of the new Data Warehouse interface and its functionality.
Users may find this tour helpful.




Virginia Department of Social Services                                                    Published November 2006
Division of Information Systems                                                                                 8
Data Warehouse User’s Guide


On line Cognos Help is also available. Users should note that both Tools and Help are Cognos products and
not DSS specific.




2.2.1 Exercise: Accessing Data Marts from the Public Folders Tab

This exercise will demo accessing the ADAPT Data Mart and the Medicaid Data Mart and navigating
between screens in the Data Warehouse.

     •    From the Public Folders tab, Click on the ADAPT folder link.

               A sub menu displays with links to the ADAPT data marts.
                        ADAPT Applications Data
                        ADAPT Cases Data
                        ADAPT Client Data

                    Note: There is a link to the ADAPT Data Mart Dictionary on the menu.

     •    Click on the link for ADAPT Client Data.




                                   CLICK
                                   here


Virginia Department of Social Services                                                     Published November 2006
Division of Information Systems                                                                                  9
Data Warehouse User’s Guide


The ADAPT Client Standard Report links are outlined in red. Note that the ADAPT Client data mart
contains an Analysis Cube. The cube is titled ADAPT Client Demographics Analysis and is located above
the standard reports. If your security profile allows access to the Analysis Cube an Analysis Studio User
Guide is available.




     Tip: Click on the links in the screen navigation bar to return to
previously accessed screens.

     •    Return to the Home page by Clicking on the link in the screen navigation bar.




Virginia Department of Social Services                                                    Published November 2006
Division of Information Systems                                                                                10
Data Warehouse User’s Guide


     •    Click on the Medicaid folder link to access the Medicaid data mart.




The Medicaid Management Standard Report links are outlined in red. Note that the Medicaid data mart
contains an Analysis Cube. The cube is titled MMIS Enrollee Demographics Analysis and is located above
the reports.




Virginia Department of Social Services                                              Published November 2006
Division of Information Systems                                                                          11
Data Warehouse User’s Guide




3. Pre-authored Standard Reports
The pre-authored standard reports are predefined or canned reports based on business specifications. Most
data marts have standard pre-authored reports. These reports require the user to respond to a few simple
prompts, such as Report Month and FIPS, prior to generating the report. The reports may be converted to
MS Excel, PDF, CSV or XML. The reports provide either summary or detail level data.

Standard Report links are always located within a data marts folder. Reports are accessed by clicking on the
link.
Standard Report links will be preceded by this icon     .

3.1 The Report Prompt Page

When the report link is first accessed a report prompt page may display. The prompt page allows the user to
refine the data request by selecting report parameters.

Examples of report parameters include:

          Report Month
          Locality
          Region
          Case Worker
          Program

Virginia Department of Social Services                                                   Published November 2006
Division of Information Systems                                                                               12
Data Warehouse User’s Guide


Some prompts allow the user to select more than one value from a report prompt parameter. For example,
the prompt may require the user to select a Program from a drop down list. The user may select one
Program from the list by clicking on it or a combination of Programs from the list by holding down the
control key and clicking on the desired Programs or all Programs from the list by clicking on the Select All
link




Note: Some prompt pages display a calendar for users to enter a report date range others display a drop
down list of available report months.

3.1.1 Exercise: Accessing, Selecting Prompt Values and Running a Pre-authored Standard Report

This exercise uses the Medicaid Data Mart.

     •    From the Public Folders tab (DW Home Page), Click on the Medicaid Reporting folder.




Virginia Department of Social Services                                                   Published November 2006
Division of Information Systems                                                                               13
Data Warehouse User’s Guide


     •    From the Medicaid Reporting folder, Click on the link for RS-O-145 Enrollee Eligibility Birth Date
          Reviews FIPS Summary.




     Tip: If the report name is in black and not underlined, you do not
have access to the report.




Virginia Department of Social Services                                                   Published November 2006
Division of Information Systems                                                                               14
Data Warehouse User’s Guide


The prompt page displays. The user defines the report parameters by selecting a FIPS and a Report Month.

     •    Click on 005-Alleghany for the FIPS.
     •    Click on 200607 for the Report Month. This represents July of 2006.
     •    Click on Finish to run the report. Note: Cancel will return you to the previous menu screen.




Virginia Department of Social Services                                                     Published November 2006
Division of Information Systems                                                                                 15
Data Warehouse User’s Guide


The report displays in HTML. This is the DW default report format.




3.2 The Standard Report Tool Bar/Home/Return Icons




The Standard Report tool bar is located above the report on the right hand side of the screen. The tool bar
icons allow the report creator to perform several different functions such as converting the report to another
display format or saving the report. By hovering over the icon, a literal will display. Grayed out icons are
not available for use in the standard reports environment.


1.           Save as Report View

          User may save a copy of the report to My Folders. Users cannot save reports to Public Folders.


Virginia Department of Social Services                                                     Published November 2006
Division of Information Systems                                                                                 16
Data Warehouse User’s Guide



2.          Email Report

          User may email a copy of the report to other interested parties.


3.           Run

          User may re-run the report with different prompt values. The icon will return the user to the prompt
          page.

          Note: This is a short cut since it allows the user to re-run the current report with different prompt
          values with out having to return to the report menu.


4.         Drill Down

          Not available.


5.          Drill Up

          Not available.


6.            Go To

          Not available.


7.          View in HTML Format

          Converts report to HTML format.

          Note: HTML is the Data Warehouse default format.


8.          View in PDF Format

          Converts report to PDF format.


9.             View in EXCEL Options

          Converts report to EXCEL (three versions) or CSV format. Clicking the small arrow on the ride side
          of the icon invokes a short menu where the exact version is selected.

                    CSV: The comma-separated values (CSV) file format is a tabular data format that separates
                    fields with the comma character. This format is often used to import data into spreadsheet
                    software.
Virginia Department of Social Services                                                         Published November 2006
Division of Information Systems                                                                                     17
Data Warehouse User’s Guide



10.           View in XML Format

          Converts report to XML format.

11.         Home

          Returns user to the Home Page (Public Folders).

12.         Return

          Returns user to the previous screen or menu.


3.2.1 Exercise: Convert a Pre-authored Standard Report to EXCEL

If needed, see exercise 3.1.1 for guidance on accessing a data mart.

      •   From the Medicaid Reporting data mart, Click on RS-O- 254 Locality Master Alpha Case Listing.
              Note: Use the scroll bars located above report menu to view additional reports.
      •   From the prompt page, Select the report parameters and Click on Finish.
              For this report: FIPS and Report Month.

The Medicaid Locality Master Alpha Case Listing report displays in HTML format.




      •   Click on the View in EXCEL Options icon.
                 Drop down list displays options.
      •   Click on the version of MS EXCEL supported by your agency.
                 Note: Contact your agency technical support person (wizard) or from the MS Excel Help
                 menu select “About Microsoft MS Excel” to determine the version your system is running.
Virginia Department of Social Services                                                 Published November 2006
Division of Information Systems                                                                             18
    Data Warehouse User’s Guide


    The report displays in a new window in MS EXCEL format.




Use File/Save As in the
MS Office Tool Bar to
save a copy of the report
to your hard drive.




    Virginia Department of Social Services                    Published November 2006
    Division of Information Systems                                                19
Data Warehouse User’s Guide


3.3 Saving Reports
EXCEL and CSV Format

When a report is converted to an EXCEL or CSV it opens in a new window. The report view is no longer in
the Data Warehouse reporting environment; therefore the report can not be saved to My Folders. The report
can be saved to a location outside of the Data Warehouse by using the File/Save as option on the MS Office
Tool Bar.

Note: The file type must be changed Microsoft Office Excel Workbook to save properly.




Virginia Department of Social Services                                                  Published November 2006
Division of Information Systems                                                                              20
Data Warehouse User’s Guide


HTML, PDF and XML Format

Reports converted to HTML (default view), PDF and XML remain in the Data Warehouse environment.
These reports may be saved to My Folders or saved to a file location outside of the Data Warehouse using
the File/Save As option on the MS Office Tool Bar.

3.3.1 Exercise: Saving a Report to My Folders

     •    Run a pre-authored standard report from any data mart.
                 The report will initially display in HTML.
     •    Click on the PDF icon in the Standard Report Tool Bar.
                 The report is converted to PDF format.
     •    Click on the Save as Report View icon.

                    The report may also be saved to a file location outside of the Data Warehouse by using the
                    MS Office Tool Bar options.




Virginia Department of Social Services                                                       Published November 2006
Division of Information Systems                                                                                   21
Data Warehouse User’s Guide


The Save as report view screen displays.




     •    Change or modify report name.
     •    Click on the Select My Folders link if the Location: field is not pre-filled with My Folders.
                 Note that the default location to save the report is Home or Public Folders. Users can only
                 save reports to My Folders.

The Location: field changes to My Folders.




     •    Click OK to save a copy of the report to My Folders and return to the Data Warehouse report view.


3.4 Printing Tips for Pre-authored Standard Reports
                    Print Excel reports using the MS Office Tool Bar. Select File/Print.

                    Convert report to PDF prior to printing. Printing from the HTML report format will only
                    print the screen view of the report.

                    For PDF reports, use the Printer icon on the tool bar located above the report. Do not print
                    from the MS Office Tool Bar.




Virginia Department of Social Services                                                         Published November 2006
Division of Information Systems                                                                                     22
Data Warehouse User’s Guide


PDF Report




Virginia Department of Social Services   Published November 2006
Division of Information Systems                               23
Data Warehouse User’s Guide


3.5 Email a Report
All reports can be emailed directly from the Data Warehouse. It is important to remember that many email
systems have limits on the amount of data that can be received in an email.

3.5.1 Exercise: Email a Report

Run a standard report from any data mart.

     •    Click on the Email Report icon in the Standard Reports Tool Bar.




Virginia Department of Social Services                                                Published November 2006
Division of Information Systems                                                                            24
Data Warehouse User’s Guide


Set Email Options screen displays.




     •    Enter email addresses in the To: and/or Cc: fields.
     •    Complete the Subject: and report Body: fields, if appropriate.
     •    Indicate whether a report link and the actual report should be included in the email by checking the
          appropriate boxes.
     •    Click OK to email the report.

Note: When the link is emailed to a recipient they will be directed to log on to the Data Warehouse prior to
running the report. The recipient must have the required privileges to access both the Data Warehouse and
the Data Mart the report belongs to.




Virginia Department of Social Services                                                      Published November 2006
Division of Information Systems                                                                                  25