Business Case
While several companies provide COTS-based analytical solutions, CMA takes our solutions a step further. CMA recognizes that these application software products are limited by the pre-programmed capabilities inherent in the software package. The NYS MDW provides a comprehensive foundation which enables a total solution by providing transformed, accurate, and current data that is available for high-speed data access.
NYS MDW end users needed the ability to upload large amounts of personal data for immediate availability in their analyses. While users have 80 to 90 percent of the data they need available in the NYS MDW, they never have all the data that they needed. They may have a subpopulation or a cohort; they may have something on their desktop that they’d like to inject into the warehouse and use in their ad hoc querying. So instead of bringing data down (and all the associated security risks that go along with that), users are able to push their data up and examine it in an environment where it is protected and readily usable.
Users also needed to efficiently extract data that is ad hoc in nature, but too large for extraction using query tools. Users required the ability to extract, in an ad-hoc fashion, tens of millions of claims and encounters for further manipulation and analyses using desktop tools or within other systems. This was a problem because it’s not efficient or secure to bring that volume of data down to a desktop computer.
They also needed to provide users with assistance in developing and writing their own personal queries, that they are required to run on a day to day basis.
The existing COTS analytical tools were not meeting all the end users’ needs.
The Solution
CMA developed Data Warehouse Assistant (DWA) for the NYS MDW End Users. Built-in industry standard technology and backed by open metadata, the DWA software is flexible and easy to integrate with other solutions.
DWA provides key functionality that is absent from any one commercial vendor and cannot be found on the market. It fills gaps in regards to ad-hoc querying, templates, and custom SQL. DWA consists of the following components: My Private Data, Templates, and Custom SQL.
Private Data allows users to upload their personal data. All the users, in addition to all the data that is available in the data warehouse, can upload and store their own private data. To them, it looks like it’s there, secure on the platform. However, no other user can see someone else’s private data.
Templates were another area where CMA saw gaps in the available software. There are many common patterns for query and analysis that go on. Data Warehouse Assistant enables users to create queries or templates from form driven processes and then store them in the data warehouse itself.
DWA’s configurable templates simplify the method of creating claims centric SQL queries by providing a form-driven process and access to most commonly queried data. It is capable of very large data sets and creates standard SQL code that can be utilized in SQL Developer or other third-party SQL tools. DWA templates do not require Power User credentials or technical expertise.
A third component of our Data Warehouse Assistant is the ability to register custom SQL. There are essentially three types of users in a warehouse.
- Data analysts and data scientists who can write their own SQL and use advanced analytic tools like SAS
- Business users that can develop and run reports and queries using BI tools
- Executive users that use pre-existing reports and dashboards
Some users want the flexibility of executing a custom SQL script, but they aren’t comfortable writing it themselves. So functionality was built into the product that allows users to register SQL generated from third-party BI tools or other sources. DWA parses it semantically and triggers a review by an experienced developer on the CMA Team. After undergoing review, and possibly modification, the user can then execute the SQL through Data Warehouse Assistant. In other words, if a business user has a report that requires supporting content, they can now “extend” the results with custom SQL queries without having to be a SQL programmer.
The Results
With DWA, NYS MDW users gained complimentary functionality of BI and Data Analytics tools and Direct SQL access including the ability to efficiently extract data that is ad hoc in nature but too large for extraction using query tools. In addition, they were provided with a user-friendly way of uploading their own private data for inclusion in their analyses, which avoids the security issues associated with downloading the data.
Satisfying the NYS MDW end-users and meeting their needs has improved DOH customer satisfaction and led to several contract amendments for the NYS MDW.