1 / 73

Data Warehouse Reporting

Data Warehouse Reporting. “Creating Documents”. Definition and Benefits. A data warehouse is a copy of transaction data specifically structured for reporting. Benefits: Easy access to information Enhanced data privacy controls Single source for information User defines format and content

della
Download Presentation

Data Warehouse Reporting

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Warehouse Reporting “Creating Documents”

  2. Definition and Benefits A data warehouse is a copy of transaction data specifically structured for reporting. Benefits: • Easy access to information • Enhanced data privacy controls • Single source for information • User defines format and content • Timely access to information • See information behind the report • Single definition for data elements • Historical data - trends over time • Look at information several ways • Combine data with data from multiple sources

  3. Table Of Contents • Logging Into WebIntelligence • System Navigation • “Create Documents” Screens and Options • Structuring Your Report • Building Your Report • Sorting Data • Drill • Calculations • Condition Prompts • Selecting The Block Type • Exporting Data From WebIntelligence • Printing A Report • Saving A Report • Publishing A Report • Sending A Report • Deleting A Report • Logging Out Of WebIntelligence • Common User Problems • Web Panel Toolbar

  4. Logging Into WebIntelligence To log into the WebIntelligence system, you need the following from your supervisor. • A user name and password • The WebIntelligence URL {https://www.dw.dhhs.state.nc.us} Type the Web address on the Location line, as indicated above, and hit enter.

  5. Logging Into WebIntelligence When you hit <enter> the following screen will appear: Click the round Log in button, which will bring up the login dialog box.

  6. Logging Into WebIntelligence Type your user name andpassword in the entry boxes, and then click OK. Your WebIntelligence start page will load automatically and you can begin working in WebIntelligence.

  7. System Navigation The Infoview Welcome Page is the default start page that opens when you log into WebIntelligence. It is your main interface for viewing, managing, and distributing WebIntelligence documents. InfoView contains short descriptions of WebIntelligence and InfoView features, which are hyperlinked to provide you with immediate access.

  8. System Navigation There are two ways to maneuver around the WebIntelligence system: • Click on a selection from the Navigation Bar • Click on a hyperlinked selection within the Page Contents

  9. The Page Contents:The larger of the two frames in the InfoView browser window. It contains the body of the page. The Navigation Bar:The black bar located on the left side, which contains links. System Navigation

  10. System Navigation Depending on the access rights you have, the Navigation Bar also allows you access up to three different InfoView document catalogs: • Corporate Documents: The document catalog that contains documents stored in the Corporate Documents repository. As long as you have access to the Universe where they were created, you can edit the report for your needs. Changes will not be saved under this document but must be saved in your personal folder. • Personal Documents: The document catalog that contains documents that you have saved for your own use. You can send these documents to other users. • Inbox Documents: The document catalog that contains the documents that other users have sent you.

  11. “Create Documents” Screens and Options To create a report, move your cursor over “Create Documents” on the Navigation Bar and click the left mouse button:

  12. “Create Documents” Screen and Options After clicking “Create Documents” you will get a list of available universes you have access to. Click on the universe you wish to enter. In the picture on the right you can see that I have access only to the “Mental Health” universe.

  13. The Create Documents screen will appear, which includes the following elements: 1. Results -The tab where you selected the data elements you want in your report. 2. Conditions - The tab where you determine what criteria the results of your report need to meet, for example, which County do you want to see the results for. 3. Settings - The tab where you can determine how your report should look. 4. All Objects - The panel that houses data elements that are available for report writing. 5. Section - The panel that allows you to display information under a header. 6. Table - The panel on the result tab where you place the data elements you have selected for your report. 7. Run Query - The button you click to begin running your report. {Once you click this button it changes from “Run Query” to “Cancel Query”. Do not click the “Cancel Query” unless you wish to cancel the report.} “Create Documents” Screens and Options 5 4 6 1 2 3 7

  14. Structuring Your Report Should you add an object to the Section or Table panel? Table Format You can create new documents that display all of the requested results within a table. For example, since the following screen includes Area Institution Name as a table header, the area institution name appears repeatedly, on each line of the table.

  15. Structuring Your Report Section Format If a field is to remain constant (as in the preceding example, which displays data for only one Area Institution Name (Alamance-Caswell) you can display that data only one time in the Section Block instead of displaying it within the table itself. The example shows how Area Institution Name for the same report would appear if you place it in the Section Block instead of the Table Block.

  16. Building a Report

  17. In this report Area Pgm Client Number, Clinical Code and Diagnosis have been selected; therefore, when your report is processed and received, the report will only include information for these three data fields. Building A Report Select Create Documents from the Navigation Bar, which will bring you to the Results Tab. On the Results Tab, select from All Objects the data elements that you want to display in the report, all follows:

  18. Building A Report Once you have placed the data elements you selected for your report in the Results Tab, click on the Conditions Tab, where you will complete the detailed Query Conditions of your report. The criteria for this report includes Area Institution Name equal to Gaston-Lincoln, Ethnicity equal to H and Report Date equal to the month of July 2002. Data meeting all of these criteria are the only data that will be received in the report results and will be displayed as determined from the Results Tab.

  19. Building A ReportAdding Conditions to a ReportThe following table contains the available condition operators and specifies the type of operand each operator requires:

  20. Building a Report You can apply a condition to an object by doing either of the following: • Drag the object from the Classes and Objects panel into the Table panel. With the object still selected, click the Conditions Tab automatically. • Go to the Conditions Tab of the Web Panel. Drag the object to which you want to apply the condition into the Conditions Tab. Select the condition’s operator from the drop-down list

  21. Building a Report Specify any required operand by clicking on the far right side of the condition and depressing the mouse button until another list drops down. The operand options in the drop-down list are: • Enter a Constant: Displays an entry box in which you can type the operand value you want to be used in the condition. • Show List of Values: Queries the databaseandreturns a list of values from which you can select one or more values. • Prompt list of Values: Lets you create a user prompt for the selection of an operand from a list of pre-defined values. • Enter a Prompt: Lets you create a user prompt for the definition of a new operand value. When you click this option, an entry box appears containing the data objects name. This will be the operand prompt’s label. The default label is the recommended setting.

  22. Building A ReportSettings Once you have completed your Conditions information, place the cursor on the settings Tab and click the left mouse button. The Settings Tab will be displayed as indicated below: The databaseused to generate the document may contain records with duplicate values. To make sure your document won’t contain duplicate rows of data, check Avoid duplicate rows aggregation. The Settings Tab allows you to format the report that will be received from your query results. You can change the font size of the report, add a header, etc. The database used to generate the document may contain records with duplicate values. If you want your document to contain duplicate rows of data, check Fetch duplicate rows. The Results, Conditions and Settings Tab at the bottom of your screen remain constant. You can move back and forth to any of the Tabs at any time during the building of a report by clicking on the Tab with the mouse. Once you have completed all of the information for each of these tabs click the Run Query Button to process the report.

  23. Building A Report This illustration displays the results for this report. As requested, the report displays the information listed on the Results Tab. The Results meet the criteria from the Conditions Tab and is formatted as defined from the Settings Tab. However, we have determined that instead of displaying the “Diagnosis Axis Code” we also wanted to display the disability group code. To add a data element field, click on Edit by moving the cursor to the word edit and pressing the left mouse button. This will bring you back to the Results, Conditions and Settings Tab.

  24. Building A ReportSettings Select Diagnosis Disability Group Code from the MH Clinical table in the All Objects panel and move it to the Table Section. Then, click Run Query. Note: if this were a Settings change or formatting change only, you could click Apply Format to see the results of the change requested. Since you are asking for a new data element you will need to Run Query to update the results of your report.

  25. Building A ReportSettings Your report results will be updated with the added data field, “Diagnosis Disability Group Code.

  26. Sorting Data You can perform sorts on the data while building your report. To add a sort order to your report on the Results page, click on the AZ button at the top of the screen. Hold down the button on the mouse once you have clicked the mouse and drag the AZ box to the data element you want to sort by. When the AZ button is over the data element box, drop it by releasing the mouse button. You can sort in Ascending (AZ) or Descending order (ZA). This will create a sort as illustrated below, which in this instance will be sorted by Disability Group Code:

  27. Sorting Data Sorted by Diagnosis Disability Group Code alphabetically in ascending order. The sorted data will appear in the report as indicated below:

  28. Drill Drill gives you the ability to look at a subset of the report results. For example, the results returned 3 separate Disability Group Codes; however you want to look at the results for only Substance Abuse Codes. At the top of the screen, select Drill, as shown in the image below.

  29. Drill Your results will then be displayed with boxes and drop-down arrows around the data element field names. Click on the the drop-down arrow box beside of “Diagnosis Disability Group Code”. The following appears:

  30. Drill Select SA from the box by moving your cursor down the list of Diagnosis Disability Group Codes to “SA” and releasing the mouse button. The following illustration displays how “drill” affected the original results. You now only have information for SA diagnosis. To End the “drill” mode click the End Drill option at the top of the screen. Note: If you want your report to retain the results from the drill function, just End Drill with the results obtained. If you want your report to revert back to the original results prior to performing the Drill Down, click on the down arrow boxes and select the All Values option, once done, click End Drill. Your results will now reflect the original report results prior to the drill down.

  31. Calculations There are two methods for performing data calculations: (1) using predetermined data element fields or (2) applying a calculation to a data element. Data element calculation fields have already been applied to data elements that are know to be used in calculating totals, sums, averages, etc., within the Tables. Selecting any of these data fields will apply the calculation for that field. Fields having predetermined calculations are indicated by the name of the data element and a pink circle next to the name of the element. For example, CDW Consumer ID count returns a count of all the unique Consumer ID numbers. The pictures on the left demonstrate how this works:

  32. Calculations You can also place a calculation on any data element by clicking on the calculator button at the top of the screen as indicated below and dragging it to the data element you want to use in the calculation. Release the mouse button when the calculation box is on top of the data element field you wish to select.

  33. Calculations Double click on the calculation box that you dropped on the data element field and the following box will appear. Select the calculation function that applies to the results you would like to see and click OK. Sum has been selected for this example.

  34. Calculations Click on Apply Format to return the following results: There is a summary total for the Area Pgm Client Number established by the Data Element field. Note: Some of the numbers are duplicated so they will not be counted in the total.

  35. Condition Prompts Prompts provide a way to ask for your input when you run reports. Prompts can be the result of conditions that you define, predefined conditions, or the definition given to it by its universe’s designer. If you have a report you want to be able to run at any time but also need to make changes to it, you can add a condition to the document that limits the results. Instead of defining a list of specific options, you can define a prompt. In this case you are using the Prompt Values From List option, so the condition object looks like this:

  36. Condition Prompts When you click the Run Query button,the following dialog appears: Select the desired value (which must correspond to data in the database), then click OK.

  37. Condition Prompts You can also select the Enter a Prompt option, which will allow you to enter the condition at the time you run the report (as displayed in the following illustration):

  38. Conditions Prompt Once you have clicked Run Query, the following prompt box should appear. Type in the data required to run the report on the prompt line and click OK, to run the report. Note: When using the show list of values or prompt list of values, make sure to select the correct Condition Operator prior to performing the show list options. For example, if you select the condition Equal To when you display the list, you will only be able to select or enter one value. However, if you select the condition In List, you can select multiple values.

  39. Selecting the Block Type By default, the blocks of data in the documents you generate are displayed as simple tables. Changes to the format of the blocks in your document will vary, depending on whether you are using the Java or ActiveX Web Panel. You can change the block type of an existing document directly from the Document Results page by clicking the arrow on the far right side of the Document Results Screen as illustrated below: Click this arrow button to display the block types below the arrow button

  40. Selecting The Block Type • Block Type: Select the icon that represents Block Type and press the right mouse button. There are four types of Tables to select from, depending on how you would like to display the information. They are Table, Financial Table, Form and Crosstab. • Bar Chart: Select the icon that represents Bar Charts on the Web Panel by right clicking the mouse over the icon. Once selected, it will display five types of bar chart displays; Grouped Bar Chart, Line and Bar Chart, Stacked Bar Chart, Percent Bar Chart and 3D Bar Chart. • Lines Chart:Select the icon that represents Line Charts on the Web Panel by right clicking the mouse over the icon. Once selected, it will display five types of line charts; Mixed Line Chart, Stacked Line Chart, Percent Line Chart, 3D Line Chart, and 3D Surface Chart. • Area Chart: Select the icon that represents Area Charts on the Web Panel by right clicking the mouse over the icon. Once selected, it will display five area chart displays; Area Chart, Stacked Area Chart, Percent Area Chart, 3D Area Chart, and 3D Volume Chart. • Pie Chart: To produce a Pie Chart to represent the results of a report, select the icon that represents Pie Charts on the Web Panel by right clicking the mouse over the icon. Once selected, it will display two types of pie charts; a Pie Chart and a Doughnut Chart. • Radar, Polar, and Scatter Charts: Select the icon that represents Radar, Polar and Scatter Charts on the Web Panel by right clicking the mouse over the icon. Once selected, four radar, polar and scatter chart types will be displayed; Radar Line Chart, Stacked Radar Area Chart, Polar Chart, and the simple Scatter Chart.

  41. Exporting Data From WebIntelligence Two export methods are available in WebIntelligence. The first way to export to a spreadsheet can be performed from either the Corporate Documents, Personal Documents, or Inbox Documents page, as displayed below: From the results page select Download as displayed.

  42. Exporting Data From WebIntelligence OR... Select Load into Spreadsheet from the report you wish to export.

  43. Exporting Data From WebIntelligence After using either of these methods, the follow box will appear, which allows you to select whether to open the downloaded information or to save it to disk. If you would like to view the information immediately, select Open, then you can save the information once you have viewed it. If you select Save it to disk, the information will be saved as the file name you assign it in the location that you assign, where it can be opened at a later time. Open or Save it to disk Note: If you choose to save the file after loading it into Excel, save it as a .xls document.

  44. Exporting Data From WebIntelligence The following screen displays the exported report information.

  45. Send your results to your PC as an Excel Spreadsheet file and print via Excel. (To use this method see the instructions in the previous section, Exporting Data from WebIntelligence) Print your document directly from your Web browser. Printing A Report There are two different methods available to print your document results:

  46. Printing A Report To print directly from your Web browser, you need to be aware, first, that what is printed may not look exactly like what you see on your screen. Second, how your data is printed depends on the options you select before running your report and the quantity of data to be printed. Three (3) ways to print your report results are: • If your data fits on one screen, such as graph or data for a single individual you can print it by clicking the “Print” icon on the browser toolbar, or by clicking “File” then “Print” on the menu bar.

  47. Printing A Report If your data fits on one logical browser page, indicated by the absence of page controls on your browser display but shows a window scroll bar, you can print it by clicking the “Print” icon on the browser toolbar, or by clicking “File” then “Print” on the menu bar. This is a single web page, there are no page controls.

  48. Printing A Report To print your report if the results contain multiple logical web pages (the presence of the page controls indicate multiple web pages), click on the page forward button to the right of the number of pages and then the “Print” icon. To print only one logical page (what you see on the screen and by scrolling down) use the “Print” icon on your browser toolbar. Page Control shows page 1 of 4.

  49. Printing A Report You can print all of the data as one page by changing the size of a logical web page. To change the size of the web page, ‘edit’ the report and click the ‘Settings’ tab on the bottom left of your screen. Enter the ‘Maximum rows fetched’ value for ‘Row count per page’.

  50. Saving A Report Saving the document allows you to save the document in your Personal Documents folder. Only you have access to this folder. To save a Document (in the Personal Documents folder), click Save as indicated.

More Related