Get documents about "Data Warehouse User's Guide
Document Sample
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
Related docs
