We added a new customer from the healthcare industry to our list over the past month. As with most customers, we are initially engaged to install Sherlock® and then perform an initial assessment. The assessment is really the fun part as we get to tailor it based upon the specific use case envisioned by the customer. This allows us to get the customer the information that they need immediately while also taking some time to show them other things that Sherlock® can do to augment their information and deployment monitoring requirements.
With this specific customer, they were headed towards the first compliance milestone for the new ICD-10 coding standard. If you’re not familiar with ICD, or the International Classification of Diseases, then perhaps a quick review of the World Health Organization’s website would be useful. At a basic level, it is a standardized system for documenting health problems diagnosed by medical professionals. It involves a series of codes that medical professionals use to document the specific diagnosis for a specific patient at a specific time. Among other things, it allows governing bodies and research institutions within the healthcare industry to determine how prevalent certain diseases and conditions are worldwide.
Sherlock® was useful in this scenario as our report inspector engine is able to index not only the structure of a report, but also details about the data source to which the report is connecting. This allowed us to quickly determine the reports that were connecting to the database tables that contained the ICD-9 codes so that they could then have a short list of reports to modify and point to the new tables containing the ICD-10 codes. This helped to reduced the number of reports that they would need “touch” from about 12,000 down to fewer than 500.
The sample list of reports below shows just a few of the almost 500 reports that were identified as using the ICD-9 tables. As you can tell, in addition to getting the report name, we also returned the full folder path where the report is located, the user who created the report, and the last time that an action was taken against that report. In this case, the actions considered for the query were either a view or a refresh. This data came from our new Sherlock® KPI component released in 2.3.0.
In this particular scenario, there were also a few reports using command objects in the Crystal Reports content rather than just querying basic tables. These command objects allow a report developer to build custom SQL where the results are then presented as a table inside Crystal Reports. The data from these command objects can then be pulled into the report just like any other table dataset.
We were able to identify all reports using command objects due to the fact that, when a command object is used, we capture that as a Table name of ‘Command’ with our report inspector. By filtering the list of reports down to those that use a table name of ‘Command’, we could then pull back the list of field names that were being used inside the reports from those command objects. We then filtered further to see which reports were using specific fields from the command objects for fields that were known to contain ICD-9 code data.
The sample data below shows some data for a single report. You can see that in the object long name we capture the table name as ‘Command’ and then the field name after (e.g., ‘ADT_PAT_CLASS_C’). You could also just pull in the object name rather than the object long name to get the field names.
In the screenshot below, you can see a sample list of reports that are using a command object with a field name of ‘ICD9_CODE’. Again, we returned the report name, full folder path, and the owner. The last activity date wasn’t required in this case as the list of reports was fewer than 10.
The objective for this post was to highlight one scenario where Sherlock® was used to assist an organization with a compliance related activity. In this scenario, it was related to the ICD standard for a healthcare provider; however, the same activities can be applied to any number of governance, risk, and compliance scenarios within any organization. Of course, we could also use the same methodology to search for tables behind Web Intelligence and Desktop Intelligence content. It just wasn’t necessary for this particular engagement.
If you have any scenarios where this type of analysis would be helpful, it would be great to hear about it.
Thanks for reading.