Attachment_A_-_Vendor_Response_Workbook_20A0400.xlsx
XLSX spreadsheet 57 KB Posted
- Solicitation number
- 20A0400
- Issued by
- California
About this file
This is a Vendor Response Workbook for a Request for Information (RFI) issued by the California Department of Transportation (Caltrans) regarding an Ad Hoc Reporting Solution. The RFI is being used for information-gathering purposes to understand available marketplace solutions and does not constitute a solicitation for bids or a guaranteed contract award. Caltrans, which operates with a budget exceeding $17 billion and maintains over 22,000 positions, currently manages financial data through CGI Info Advantage and is transitioning to FI$Cal with a scheduled go-live date of June 30, 2028. The requested solution must support ad hoc reporting capabilities for financial data that will be housed in an Oracle Autonomous Data Warehouse within the Oracle Public Cloud via a Financial Data Model. Currently, Caltrans has 1,740 active users accessing financial data, with an average daily concurrent user load of 57 and a peak concurrent user count of 229.
The workbook requires vendors to provide comprehensive responses across seven sections: vendor information, proposed solution details, implementation methodology, support and maintenance operations, training approach, requirements mapping, and cost estimates. The solution must meet 81 specific technical requirements spanning functionality areas such as intuitive parameter input interfaces, SQL query building capabilities, role-based access control with three user tiers (Typical User, Super User, and Administrator), integration with Caltrans Single Sign-On, data visualization with multiple chart types, scheduling capabilities at various intervals, version control, and audit logging. Vendors must provide detailed cost breakdowns for one-time implementation costs (hardware, licenses, implementation, training, and maintenance/support) and continuing annual costs, with the workbook accommodating flexible cost presentation formats. The RFI emphasizes that Caltrans is not seeking traditional marketing materials but rather concise, detailed responses with supporting documentation where necessary.
View the file
Other files
| File | Type | Posted |
|---|---|---|
| E._Solicitation_Package_20A0400.pdf | ||
| C._Addendum_1_20A0400.pdf | ||
| C._Addendum_2_20A0400.pdf |
On GovTribe
Work with this file
- Download the original file
- Contacts named in this file
- Similar government files
- Ask GovTribe AI about this file
Text version
Instructions
| REQUEST FOR INFORMATION |
| AD HOC REPORTING SOLUTION |
| California Department of Transportation (Caltrans) |
| The Caltrans Ad Hoc Reporting Solution RFI Vendor Response Workbook consists of the following tabs, each focusing on key aspects of the solution: |
• Tab 1: Vendor Information
• Tab 2: Proposed Solution
• Tab 3: Implmentation
• Tab 4: Support - M&O
• Tab 5: Training
• Tab 6: Requirements
• Tab 7: Cost Estimate
• Each tab contains a list of questions to be completed by vendors in the designated Response column.
• If a capability is not offered or not applicable to your solution, please enter "N/A" and provide a brief explanation,
• Provide concise and relevant responses while ensuring enough detail to allow for proper evaluation. Caltrans is not looking for traditional "Marketing Material".
• If additional context is needed, respondents may provide supporting attachments that are clearly referenced in the respective response fields.
• Respondents can provide any additional documentation via email to the Caltrans contact listed in the RFI.
• Cost estimates. Vendors may complete the provided table or submit their cost details as a separate attachment in their preferred format, as long it addresses the requested components.
| Please Provide the Following Information: |
| Vendor Name: |
| Vendor Contact: |
| Phone Number: |
| Email Address: |
| Can we contact you for additional information? |
| Caltrans DPAC Contact: |
| Name: |
| Email Address: |
| Phone Number: |
&"Century Gothic,Regular"&14&K000000&A &"Century Gothic,Regular"RFI Adhoc Reporting Solution
1. Vendor Information
| Purpose: Provide infomration about the Respondent | ||
| # | General Information | Response |
| 1 | Name of Company | |
| 2 | Company website URL | |
| 3 | Company location | |
| 4 | Do you have the ability to provide on-site services in Sacramento? | |
| 5 | Number of years supporting and selling the proposed solution | |
| 6 | What other California state departments use your solution? | |
| 7 | What is the typical timeframe for an implementation of your solution? (This does not include considering Caltrans needs/requirements) | |
| 8 | What California state contract vehicles is your solution and implementation services available on? | |
| 9 | Do you have experience implementing similar size solutions compared to Caltrans needs? | |
| 10 | - If yes, how many of these implementations have you completed | |
| 11 | - List the names of the organizations | |
| 12 | ||
| 13 | ||
| 14 | ||
| 15 | ||
| 16 |
&"Century Gothic,Regular"&14&A &"Century Gothic,Regular"RFI Ad Hoc Reporting Solution
2. Proposed Solution
| Purpose: Provide details about the proposed solution | ||
| # | General Information | Response |
| 1 | Proposed Solution Name. | |
| 2 | Is your solution COTS or custom development? | |
| 3 | Is your solution on-prem, cloud, or other? | |
| 4 | What other state departments use your solution? | |
| 5 | List out the components needed/included in your solution. | |
| 6 | - Hardware (If applicable): | |
| 7 | (List out the details and add lines as needed) | |
| 8 | - Software | |
| 9 | (List out the details and add lines as needed) | |
| 10 | What model is used to purchase the software? | |
| 11 | - Subscription | |
| 12 | - Perpetual | |
| 13 | - User-based | |
| 14 | Other (list and provide details) | |
| 15 | What else is included with your solution that may be of benefit/interest to Caltrans? | |
| 16 | Are there any additional costs or options that Caltrans needs to be aware of? | |
| 17 | ||
| 18 | ||
| 19 | ||
| 20 |
&"Century Gothic,Regular"&14&A &"Century Gothic,Regular"RFI Ad Hoc Reporting Solution
3. Implementation
| Purpose: Provide informaiton related to implementiing the proposed solution | ||
| # | General Information | Response |
| 1 | List out the services/phases that are provided as part of implementation. | |
| 2 | What is the timeline of a typical implementation of this size and complexity? | |
| 3 | What are some of the lessons learned from other implementations that Caltrans should be aware of? | |
| 4 | What are the risks/issues and how have they been addressed in the past? | |
| 5 | ||
| 6 | ||
| 7 | ||
| 8 | ||
| 9 | ||
| 10 | ||
| 11 | ||
| 12 | ||
| 13 | ||
| 14 | ||
| 15 | ||
| 16 |
&"Century Gothic,Regular"&14&A &"Century Gothic,Regular"RFI Ad Hoc Reporting Solution
4. Support - M&O
| Purpose: Provide information about the support services and/or M&O provded with the implementation | ||
| # | General Information | Response |
| 1 | What is included with your system support (Maintenance and Operations)? (Define the services, amount of time, etc) | |
| 2 | - If needed, do you offer additional support. (Define additional support/services) | |
| 3 | What is included with your User Support? | |
| 4 | What levels of support do you offer? (Post go-live) | |
| 5 | - 24/7, business hours, etc. | |
| 6 | - Please define. | |
| 7 | - What is the cost of support that you offer? | |
| 8 | - If needed, do you offer additional support. (extended hours, etc) | |
| 9 | What are your Standard SLAs? | |
| 10 | - Can they be modified/customized, if needed? | |
| 11 | ||
| 12 | ||
| 13 | ||
| 14 | ||
| 15 | ||
| 16 |
&"Century Gothic,Regular"&14&A &"Century Gothic,Regular"RFI Ad Hoc Reporting Solution
5. Training
| Purpose: Provde infomraiton about training that is provided/offered | ||
| # | General Information | Response |
| 1 | What is your approach to end-user training and knowledge transfer? | |
| 2 | What kind of training is provided with a standard implementation? (Provide details) | |
| 3 | How is training provided? (e.g., in-person, virtual, on-line, self study, etc.) | |
| 4 | What other training services are provided? | |
| 5 | ||
| 6 | ||
| 7 | ||
| 8 | ||
| 9 | ||
| 10 | ||
| 11 | ||
| 12 | ||
| 13 | ||
| 14 | ||
| 15 | ||
| 16 |
&"Century Gothic,Regular"&14&A &"Century Gothic,Regular"RFI Ad Hoc Reporting Solution
6. Requirements
| Purpose: Provide information as to how the proposed solution meets the requirements | ||||||
| Requirement can be met: | ||||||
| Req # | Requirement (Description) | Out-of-the-Box | Configuration | Customization | Requirement ID | Comments |
| 1 | The solution shall provide a clear and intuitive interface for users to input or select multiple parameter values (e.g., text boxes, date pickers, dropdown lists) to run queries based on the parameter's data type and defined available values. | 1 | ||||
| 2 | The solution shall have a mechanism to validate user input against defined data types and constraints, providing error messages for invalid entries. | 2 | ||||
| 3 | The solution shall allow users the ability to drag and drop tables and visualization objects to easily create reports. | 3 | ||||
| 4 | The solution shall provide a graphical user interface (GUI) with drag-and-drop functionality that replicates SQL operations to allow users to build queries and run reports. | 4 | ||||
| 5 | The solution shall generate and display the corresponding SQL code in a dedicated read-only panel within the report. | 5 | ||||
| 6 | The solution shall support standard SQL operations that enable execution of custom queries of variable complexity. The list of standard operations must include: |
Selection: SELECT, DISTINCT, FROM, AS Filtering: WHERE, HAVING, IN, BETWEEN, LIKE Joining: JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, UNION Grouping: GROUP BY, COUNT(), SUM(), AVG(), VAR(), STDDEV() Sorting: ORDER BY, LIMIT, OFFSET Functions: String, Date, Math, Conditional functions Comparison: =, <, >, <=, >=, !=, etc Conditional Logic: CASE, COALESCE, NULLIF, IS NULL
| Subqueries/CTEs: WITH, (SELECT...) | 6 | |||
| 7 | The solution shall allow report designers the ability to assign field properties as "Required", "Optional", or "Dependent" to data entry field parameters. | 7 | ||
| 8 | The solution shall utilize iconography to distinguish between dimension attributes, calculated measures, and detail attributes. | 8 | ||
| 9 | The solution shall have the following pre-defined functions installed for use within queries: | |||
| • | State Fiscal Year | |||
| • | Calendar Year | |||
| • | Month/Year | |||
| • | Quarter/Year | |||
| • | Year-to-Date | |||
| • | Transaction Year/Month (i.e., year/month of posting) | |||
| • | Fiscal Month | |||
| • | Accounting Period | |||
| 9 | ||||
| 10 | The solution shall allow users to define parameters for use within SQL queries. | 10 | ||
| 11 | The solution shall provide users the ability to format report headers and footers, add query summaries displaying query properties and definitions, create sections, and create cross tabs. | 11 | ||
| 12 | The solution shall provide users with the ability to define calculated measures using custom formulas, enabling users to derive new parameters from the combination of existing parameters across different dimensions. | 12 | ||
| 13 | The solution shall not truncate or round dollar amounts on detail or summary records. (optional to use calculated field) | 13 | ||
| 14 | The solution shall allow the user to include explanatory remarks or comments on a report and to save and print the comments with the report. | 14 | ||
| 15 | The solution shall provide the option to display results of a query in tabular, graph, and report format. | 15 | ||
| 16 | The solution shall allow modifications to existing queries or reports. | 16 | ||
| 17 | The solution shall include an 'Undo' feature. | 17 | ||
| 18 | The solution shall use a pre-defined threshold to identify complex queries or reports. (i.e. Time to execute and # of records returned (for example, if a query is expected to return data for 1 month, the execution time should not be too long. But if it exceeds 30 minutes, it's most likely something is wrong with the logic.) | 18 | ||
| 19 | The solution shall display a warning dialog when a complex query or report is detected and offer two choices: "Proceed" or "Cancel". (i.e. System gives us an estimated time to run. User will have the ability to proceed or cancel.) | 19 | ||
| 20 | The solution shall provide a reason when a user query has been cancelled. If the cancellation was forced by someone other than the creator (IT or DMS), the solution shall provide the email address of the Data Management Section on the message for user to contact if needed. It shall be for both scheduled and run on the fly query. | 20 | ||
| 21 | The solution shall provide a preview of the query output before execution. | 21 | ||
| 22 | The solution shall support parallel query capabilities for designated users, allowing them to submit multiple queries for simultaneous processing. | 22 | ||
| 23 | The solution shall provide the ability to filter values from drop-down data entry fields using a search box functionality. | 23 | ||
| 24 | The solution shall provide support for single character and multi character wild card syntax. | 24 | ||
| 25 | The solution shall provide end users with the ability to interactively drill down to record level detail from within a graph, table, or query result output. | 25 | ||
| 26 | The solution shall allow previously scheduled reports to be run on demand. | 26 | ||
| 27 | The system shall generate custom reports based on user defined criteria. | 27 | ||
| 28 | The solution shall provide designated users with the ability to modify the schedule of recurring reports, which includes editing the time, triggers, frequency, and receipients. | 28 | ||
| 29 | The solution shall provide users the ability to name their queries/reports and provide a description. | 29 | ||
| 30 | The solution shall be capable of scheduling queries and running reports at annually, quarterly, monthly, bi-weekly, weekly, and daily intervals. Users shall be able to schedule queries and run reports at times specified by the end user. | 30 | ||
| 31 | The solution shall implement a version control system for tracking changes to queries and reports. | 31 | ||
| 32 | The solution shall present the following metadata for each report within the properties window: 'ID', 'Title', 'Description', 'Created By', 'Last Modified By'. | 32 | ||
| 33 | The solution shall allow end users the ability to set and modify properties through a query properties panel including 'Name', 'Data Retrieval Limits', and 'Location'. The solution shall also include toggle-able options including 'Refresh Behavior', 'Duplicate Rows', 'Empty Rows', and 'Locked'. | 33 | ||
| 34 | The solution shall clearly display the date of the latest successful load within the solution's UI. | 34 | ||
| 35 | The solution shall be capable of previewing and printing queries and reports. | 35 | ||
| 36 | The solution shall support the ability to export query output to the following formats: .txt, .html, .csv, .xlsx, and .pdf. | 36 | ||
| 37 | The solution shall provide end users with multi-colored data visualization charts, having the ability to visualize up to four variables. The list of data visualization charts must include at minimum: |
- Bar Charts
- Line Charts
- Pie Charts
- Area Charts
| - Column Charts | 37 | |||
| 38 | The solution shall provide end users the ability to customize graph axis scales and point of origin. | 38 | ||
| 39 | The solution shall provide end users the ability to label all data sets and subsets displayed on graphs. | 39 | ||
| 40 | The solution shall provide the option to customize the colors and fill patterns for each data series on a graph. | 40 | ||
| 41 | The solution shall allow users to ability to delete their own reports/queries/folders. | 41 | ||
| 42 | The solution shall include the ability to roll back changes to a previously saved version. | 42 | ||
| 43 | The solution shall ensure that a program-wide report may only be modified by its creator. Users who wish to make changes may only do so by saving reports as a personal copy to their personal folder. | 43 | ||
| 44 | The solution shall provide a user friendly interface for managing files and folders | 44 | ||
| 45 | The solution shall have program-wide and personal folders within the file management system | 45 | ||
| 46 | The solution shall list all program-wide and personal queries that are accessible to the user based on their user role or what has been shared to the user. | 46 | ||
| 47 | The solution shall provide a "Save"/"Save As" action for a query or report that saves the file to a destination folder within the solution's file system. | 47 | ||
| 48 | The solution shall provide the option to make queries program-wide or personal. | 48 | ||
| 49 | The solution shall have the ability to share reports/queries with other users. | 49 | ||
| 50 | The solution shall have the ability to copy one or more selected queries or reports from a source folder and paste them into a destination folder within the solution's file system. | 50 | ||
| 51 | The solution shall implement role-based access control that extends to schema, table, row, and column levels. | 51 | ||
| 52 | The solution shall define a "Typical User" role with the following baseline user permissions: |
- Execute one query at a time
- Save queries and reports to a 'Personal' folder
- Cancel own running reports
| - Create simple queries | 52 | ||
| 53 | The solution shall define a "Super User" role with the following baseline user permissions: |
- Execute multiple queries at a time
- Save queries and reports to 'Program-Wide' or 'Personal' folder
- Cancel own running reports
- Create complex queries
| - Schedule own queries | 53 | ||
| 54 | The solution shall define an "Administrator" role with the following baseline user permissions: |
- Execute multiple queries at a time
- Save queries and reports to 'Program-Wide' or 'Personal' folder
- Cancel running reports universally
- Create complex queries
- Access confidential or sensitive information
| - Fully control query schedules universally | 54 | |||
| 55 | The solution shall integrate with Caltrans Single Sign-On (SSO) solution. | 55 | ||
| 56 | The solution shall ensure that, in the event of a data assurance failure (e.g., quality assurance failure), users retain access to the most recent successful data load. | 56 | ||
| 57 | The solution shall continue to operate and an automated alert shall be triggered to notify appropriate personnel of the data assurance failure. | 57 | ||
| 58 | The solution shall maintain a log of all ETL jobs from the source system and document, the start, end and status (success/failure) of each. Error details must be recorded if an ETL job fails. | 58 | ||
| 59 | The solution shall automatically alert system administrators via email if an ETL job fails. The notification shall include all the necessary information for the support team to be able to remediate the error. | 59 | ||
| 60 | The solution shall notify end users if their query has failed and provide a brief explanation of the error. Error messages must describe the situation, including the cause of the error and action necessary to correct the error, as well as provide contextual information to allow for correction without additional research. | 60 | ||
| 61 | The solution shall provide clear and comprehensive error messages at both the individual field and global screen levels. Error messages must describe the situation, including the cause of the error and action necessary to correct the error, as well as provide contextual information to allow for correction without additional research. | 61 | ||
| 62 | The solution shall be architected for scalability, supporting both horizontal scaling (adding more machines) and vertical scaling (increasing components like CPU power, RAM, or disk space) to accommodate increases in data volume, user load, and reporting complexity. | 62 | ||
| 63 | The solution shall ETL data from the Financial Data Model (FDM) within the EDW following the completion of the nightly incremental ETL from identified source systems of record. | 63 | ||
| 64 | The solution shall make the latest and most current data available to end users following the completion of the nightly incremental ETL processes by 6 AM on a daily basis. | 64 | ||
| 65 | The solution shall ensure that the data supporting ad-hoc reporting is read-only. | 65 | ||
| 66 | The solution shall utilize the EDW's technical architecture for the purposes of data modeling, ETL, and reporting. Furthermore, the solution shall leverage the FDM to achieve the desired outcomes. | 66 | ||
| 67 | The solution shall alert admins and users of any unplanned system outages. Once the solution is restored, an email notification will be distributed to system users. | 67 | ||
| 68 | The solution shall notify users via email of the following system events: |
- Planned Maintenance
- Security Incidents
- Feature Updates
| - Third-Party Service Disruptions | 68 | |||
| 69 | The solution shall notify end users of the successful completion of scheduled queries. | 69 | ||
| 70 | The solution shall ensure the vendor technical support is provided within the core business hours of 6AM to 6PM PST. | 70 | ||
| 71 | The solution shall include help and technical documentation online and available to Caltrans users. | 71 | ||
| 72 | The solution shall provide a Windows Help Engine, or other industry standard help engine, of equal or greater functionality. | 72 | ||
| 73 | The solution shall provide context-sensitive help buttons for all modules, functions, screens, and field levels. | 73 | ||
| 74 | The solution shall provide on-line help features, including |
-Step-by-step instructions for performing the task -How-to examples, such as for making changes and deletions -Hints on translating old coding structure to the new coding structure -A description of the codes, their values to the new coding structure -A description of the coding structure -A comprehensive, searchable index.
-A comprehensive glossary -Basic system documentation
| -Key command instructions | 74 | |||
| 75 | The solution shall end the user session if no user input is provided after 2 hours. | 75 | ||
| 76 | The solution shall implement a continuous monitoring mechanism to track usage metrics for all user-defined queries, reports and scheduled jobs within the solution. | 76 | ||
| 77 | The solution shall provide the system administrator with the ability to archive queries, reports and scheduled jobs. Archived assets shall remain in a read-only repository for 14 days before permanent deletion. | 77 | ||
| 78 | The solution shall be responsible for system validation and system testing. Caltrans with be responsible for UAT. | 78 | ||
| 79 | The soultion shall provide User statistics. (e.g. Total number of users, average number of concurrent users, peak week of and number of concurrent users, number of users that have not logged in since XX, etc). | 79 | ||
| 80 | The solution shall provide definitions of data fields and the corresponding source field name and table name(s) in a data glossary format. | 80 | ||
| 81 | The solution shall have a Search feature by report name, employee S# or partner P#. | 81 |
&"Century Gothic,Regular"Caltrans &"Century Gothic,Regular"&14&A &"Century Gothic,Regular"RFI Ad Hoc Reporting
&"Century Gothic,Regular"&P of &N
7. Cost Estimate
| Purpose: Provide a cost estimate that meets the requirements in this RFI. Includes the details/components in each line. | ||||
| # | General Information | Comments/Assumptions | Estimated Cost | |
| Year 1 or Implementation | Estimated Cost |
Year 2 or Future Years
| 1 | One-Time Costs (Implementation) |
| 2 | Hardware |
| 3 | Licenses |
| 4 | Implementation |
| 5 | Training |
| 6 | Maintenance/Support |
| 7 | Other (list and provide details) |
| 8 | |
| 9 | (Add lines as needed) |
| 10 | |
| 11 | Continuing costs: |
| 12 | Hardware (e.g., Lease / Maintenance) |
| 13 | Software Licenses / Maintenance |
| 14 | Licenses |
| 15 | Maintenance/Support |
| 16 | |
| 17 | Other (list and provide details) |
| 18 | |
| 19 | (Add lines as needed) |
| 20 | |
| 21 | |
| 22 | Additional/Other Costs |
| 23 | Hardware |
| 24 | Software Licenses / Maintenance |
| 25 | Licenses |
| 26 | Training |
| 27 | User Support |
| 28 | Other Services |
| 29 | Other (list and provide details) |
| 30 | |
| 31 | |
| 32 | |
| 33 |
&"Century Gothic,Regular"&14&A &"Century Gothic,Regular"RFI Ad Hoc Reporting Solution
File details come from the government source that posted it. Updated .