MIP Ad Hoc Reporting System (MARS) Training Overview of MIP Ad Hoc Reporting System - Purpose - Provide selected MIP users with the ability to create ad hoc reports on demand - Pilot-Deployed Rapid Query Tool (RQT) in June 2007 - Post-pilot objectives - Increase functionality with a more flexible data structure - Mitigate risk to MIP by querying a data warehouse built from MIP data instead of the MIP itself - Use Business Objects tool as user interface - Increase number of user IDs – Regions, RMCs, FEMA HQ MARS Users . MIP Champions and Black Belts . Other selected users in the Regions and RMCs . Selected FEMA Headquarters users . NSP * Mapping partners will not have access to MARS MARS Data Warehouse - Data for reporting is not pulled directly from the MIP, but from a separate data warehouse - Accessing data through the data warehouse minimizes interaction and impact to the MIP - Extract, transform and load data into the data warehouse - Data is refreshed to the data warehouse every 24 hours - Reports now have an overnight delay in data; reports not in real time - MARS will be unavailable every day between 5 AM and 7:30 AM (Eastern) while the data is refreshed Potential Future Capabilities - Support external data sources -Receive data from external data sources (e.g., levees inventory, spreadsheets, etc.) into the data warehouse - Provide data to external systems – Send data from the data warehouse to other systems (i.e., MSC, NCFMIS, NFDA, etc.) - Requests will be evaluated individually and implementation will be based on available resources Accessing MARS - Access MARS through the MIP - No separate link, user name or password - MARS is compatible with Internet Explorer 7 and requires Java - Security – only authenticated users with MARS role - MARS is ‘domain based’ meaning you can limit a user to individual universes (Studies, Revisions and Amendments) - At this time, all users receiving access to MARS will receive access to all universes - Studies, Revisions and/or Amendments data available MARS Landing Page - Description of MARS and link . Link to list of canned reports to access the site and descriptions - Link to list of data elements - MARS support email addresses and descriptions MARS Features - Canned reports and capability to create, save and schedule new reports - Studies data elements - Status information for Revisions and Amendments Key Terms Business Objects – MARS software Universe – logical groups of data elements - Studies - Revisions - Amendments Data element – specific/individual unit of information - Actual MIP terms and fields Web Intelligence Document – query template Query – included data elements and filters needed to create/modify a report Report – the result of a query Dimension, Detail and Measure Objects - Dimension object (blue box) – base elements for creating a query - Detail object (green pyramid) –provides additional information for adimension object, but does not formthe basis of a query - Measure object (pink circle) – quantityassociated with the dimension object(i.e., number of panels, cost, streammiles) - Cannot run a query with just a measureobject, must also have a dimension object - Elements marked with an * indicates a rolled up value Public Folders vs. My Folders Public Folders contain canned reports - All reports in Public Folders refresh on opening - 23 canned reports - 14 Studies and Earned Value (EV) - 5 Revisions - 4 Amendments My Folders includes personal reports - Favorites – individually developed, modified and/orRegion specific reports - Inbox – Report(s) scheduled from another user and sentwithin the tool to you - This is not your email inbox, but a folder in MARS that displays reports scheduled for you Links in My Folders History – shows status of scheduled reports; ability toreschedule reports Schedule – link used to schedule a report to bedistributed (when, destination, format, etc.) Modify My Folders – allows the user to edit the query and/or report Public Folders -allows the user to only edit the report (change column titles, rearrange columns, sort, etc.) Properties – link which displays basic reportinformation (title, description, date created, etc.) Tips and Tricks – Canned Reports There are two pages of Studies canned reports Certain canned reports have many columns and are specifically sized to fit on 11x17 paper, but some column widths are scrunched. You cannot adjust the column lengths in the canned report. - Fix this by saving the report to My Folders and modify the report as needed (e.g., remove unnecessary columns) Tips and Tricks -Creating Reports Always choose Web Intelligence Document to create a new report Any report that includes Project Area Detail information should also include CID to indicate whether it is a county-wide or community project Select logical data elements when creating the report - Selection of random data elements may lead to illogical reports Reports that return a Partial Results message should be further filtered Tips and Tricks – Modify/Format Cannot modify or move canned reports from their original folder - Must save a copy of the report to My Favorites to schedule (email) or make any modifications Different format options will appear in the Properties tab depending on whether you select a column, row or the entire table What to do if you see #MULTIVALUE value in a cell: - Highlight the report by clicking on the outline of the report - In the Properties tab, check Yes for Avoid Duplicate Row Aggregation under the Display section Tips and Tricks – Saving Use ‘Save as’ to save a modified report to My Favorites Use ‘Save to my computer as’ to export and save a reportto your local computer NOTE: Depending on your security settings, a pop up blocker may appear when you try to download a file to your computer. To avoid a pop up blocker appearing on your screen, hold down the Ctrl key whenyou click on the dropdown box. Continue to hold down the Ctrl key untilthe Save As screen. If you see this pop up, it means you have made changesto the report and if you click OK, your changes will not besaved. Make sure to click Cancel on the pop up and click the Save icon. More Tips and Tricks -Scheduling When scheduling an email make sure there is at least one mapmodteam email listed in both the To and From fields Use the History link to check the status or reschedule scheduled reports Caching Options, Server Group, and Events are not options we will use in scheduling reports Prompts will display when the report has a prompt set up. Open the Prompts link to set the prompt to send to the user. Support Questions and General Support: StudiesReports@mapmodteam.com RevisionsReports@mapmodteam.com AmendmentsReports@mapmodteam.com Adding new MARS users - Champs or Black Belts should contact MIP Help to request new user access; same process as adding a new MIP user - Mapping partners cannot have access to MARS Process to Promote a Personal Report to Public Folders - Send the report to the appropriate SME (Studies, Revisions, Amendments) - Suggest sending it to the SME’s MARS inbox and following up with an email to let them know you sent it there - SME evaluates the report and works with requester to modify, if necessary - If “promotable” SME works with MARS Admin (at NSP) to upload it to the appropriate folder in Public Folders - MIP Acceptance team communicates new report to MARS users IMPORTANT REMINDER MARS will be unavailable every day from 5 AM until 7:30 AM while the data is being refreshed to the data warehouse 20