Prof. Johnson teaches Data and Knowledge Management

Ad Hoc Queries

ad hoc

Wikipedia provides a short definition of ad-hoc queries:

Ad hoc querying is a term in information science.

Many application software systems have an underlying database which can be accessed by only a limited number of queries and reports. Typically these are available via some sort of menu, and will have been carefully designed, pre-programmed and optimized for performance by expert programmers.

By contrast, “ad hoc” reporting systems allow the users themselves to create specific, customized queries. Typically this would be via a user-friendly GUI-based system without the need for the in-depth knowledge of SQL, or database schema that a programmer would have.

Because such reporting has the potential to severely degrade the performance of a live system, it is usually provided over a data warehouse.

Ad hoc querying/reporting is a business intelligence subtopic, along with OLAP, data warehousing, data mining and other tools.

What is your experience with ad hoc queries? What limitations have you run into in when “asking” questions of the data held in systems that you interact with? What kinds of things do you think systems designers could do to make ad hoc access easier for users?

26 Responses to Ad Hoc Queries

  • Currently I work with adhoc queries every day. But due to the restrictions on my access, I only have access to Microsoft Access. I load multiple sets of data into Access and write queries off of many tables to produce reports for Directors and Planners within the Sales department.

    Also, previously when I worked in an Informatics department generating reports for medical claims, I also created adhoc queries to generate prototypes/sample of reports as well as for testing purposes. Here, I used Teradata as my querying interface and it was also the vendor for the data warehouse.

    Some issues I commonly come into is that complex queries take tens of minutes to run. Often, you don’t get all the information you intended to get (because you didn’t realize you needed another column). Then you have to edit your query and then re-run. This can be so time consuming.

    Adhoc’ing doesn’t have to be “raw” typing of the query (for example what Professor Johnson displayed in class –> select * from employees) there can be a user interface that exists on top of the datawarehouse that enables business users to easily generate reports on the fly using the data that they are interested in. SAP has a module that exists on top of their standard Business Intelligence package. It sits on top of Microsoft Excel and allows you to select fields, filtering criteria, sort order, etc. It is great and easy to use! This way, business users who aren’t so technical, aren’t relying on IT to provide them with sometimes critical information.

  • I used to work with Microsoft Access. I imported data from Excel into Access and created a database so that I could run queries to get needed information. I found that writing queries in Microsoft Access could be difficult sometimes as I have to remember the correct names of the fields I want to run on.
    I also worked with one software(could not recall the name of it), but it made my life so much simple. All I did was sitting there and clicking on field name, functions and how I wanted to run queries. As long as I have knowledge how to write an SQL, I only have to apply it to play around with queries. I personally think that the software could be a very useful tool for people with no IT background.

  • I worked with an ad hoc query tool in the mid-1980s before microcomputers and PCs were introduced in the marketplace. If you compare it to the tools that are available today, it was very archaic. The vendor provided a template which was in paper form. In fact, it was a tablet of templates and they were color coded based on the type of query you were writing. The vendor recommended that customers use this template to write the query because when you logged onto the system,the screen had very few prompts. The template helped you to organize your thought process and not miss critical components. The tool required you to use single and double quotes,parentheses and nested parentheses, and invoke certain logic if you wanted to access data across different segments of the data base.
    The tool was very limited in that,once you finished entering the query you had to store and catalog your ad hoc query. The system would update every two hours and tell you if the syntax was correct. If you had an error in your coding, you had to fix it, catalog again,and wait another two hours to determine if it was error free. Once it was correct, the actual query ran during the nightly update, you could not obtain the report in a “real-time” fashion.
    In the late 1980’s, the vendor updated the tool with more on-line prompts and eliminated the need to use a paper template. The syntax errors were immediately available and it provided the capability to run reports on demand. Additional tools were provided which allowed you to download and reformat data to import into Excel or Access gained popularity in the 1990s.
    In addition to the updated ad hoc query tool, another tool was introduced in the 1990’s and was referred to as Express Query. This tool allowed you to basically write a query in English with the aid of a lexicon and was designed for non-programming individuals. Today the vendor has developed a front-end for writing and executing SQL queries and often refer to this as a SQLesque tool because it has all the characteristics of SQL but provides prompts to make it user friendly.

  • I don’t know if it’s fortunate or unfortunate…. but I have not had the need to run my “own” ad hoc reports/queries… If I need a report from one of my departmental data base (like a report from the 3M HDM – coding system), I have staff members who are trained to run the reports for my needs. I inform my staff about the exact details I need on the report, and these reports are provided to me, at most times in an excel format so I can sort it to my needs. And if I am looking for any other reports that is outside of my department, I have the IT folks run the report for me.

    Even though I am unable to comment on my experience with running a ad hoc report, I can say that these reports are very important and has helped me in data analysis and process improvements…

  • As an analyst, my daily task revolves around creating/running reports based on standard and ad hoc queries about pharmacy and medical claims utilizations, which reports are requested by internal departments or external clients that my company manages. I utilize MS Access, SQL Server, Toad for Oracle, MySQL, and different interfaces to connect to data source. If I got the structure of the database, I can simply write my own simple or complex SQL statements to retrieve the information I need.

    The process of creating ad hoc queries (any kind of queries in general) can be simple or tedious depending on your database skills. If you are a person who is well-informed with the structure of relational databases and possesses some skills, you can easily work your ways around it. Otherwise, it can be complicated. As Patty stated it, the biggest problem I face most of the time is changes in requirements. For instance, after information is being diced and sliced and ready to go, requirements changes such as date ranges the query was running or adding additional column or formulas. This of course, changes everything. I mean everything from the way you even look the data itself. Thus, the process has to begin from the scratch again. Keep in mind that querying is not just a simple select from statements. There are other things involved during the process like sub-querying or impossible calculations that can be created by joining tables.

    Though I am not sure from system designer perspectives how possible this is,, one thing I need from them is “Speed”. When pulling large amount of data, it takes a long time to generate result or you could even run into timeout.

    But, one thing I would not suggest is enabling everyone to run “ad Hoc” queries since not everyone is capable and understands what data worth-a simple mistake can create a disaster.

  • Other than learning some basic ad hoc reports/queries in undergrad, I don’t have much experience with ad hoc queries. However, for my work, we do run reports from our cancer registry software but we use the “query wizard” and “report manager” tools for that, which is very user friendly and one doesn’t need to know any programming languages, so I’m not sure if that would be considered as ad hoc reports. You basically select what data you in your tables and it will generate a report, which is very similar to other MS reporting tools.
    When people run reports, the biggest problem is knowing which field to select to get the result you need—you have to very specific in your choices or you won’t get the result you want. I think the system designer should have a data dictionary that states exactly what each field means so there will be no definition confusion. Also, the field names shouldn’t be similar to each other because if one if doing ad hoc reports quickly mistakes are easily made.

  • I’m in the same boat with you Shiny! As HIM practitioners, its rarely we do much ad hoc report/queries, but hopefully after this program, we will get to do more hand on activities.

  • In my last role I would run or create ad hoc queries daily. To create/run these queries I would use access, IDX, or a tool called centricity business informatics. From my experience I do not think that you run into limitations regarding these systems, when asking for data, if the information is in the system then you can retrieve it if you know how. I think a better word for limitations in this case is user “know how.” Generally the limitations in the system come from the person writing the query forgetting to qualify on something or not knowing how to compose the query in the first place. Usually this can be overcome by proper training in whatever tool or system that you are using. The designers of these systems might be able to make it easier if they have a help tool that is detailed and easy to follow in case you become stuck something, it also might be beneficial if you are a software company/designer to provide a help line or some type of service that allows you to either talk to the user on the phone, over the web, or in person. I think that increasing the speed and performance of the system so the queries run at faster speeds is always good. Additionally making the editing of the query terms easy so that you can add and delete qualifiers without having to start over and write the query from the beginning again is certainly a time saver and extremely helpful.

  • I’m sorry, but I’m going to have to say that Ad Hoc query tools were never really that useful in my line of work. While I understand the good intentions behind them (they are meant for the less technically inclined customer), I have never used an ad hoc query tool that was ‘good enough’ for my business needs. When I was forced to use them because there weren’t any other options, I find them limiting, clunky and unintuitive and often wished for a good ODBC connection and a way to write straight SQL queries. Anytime I see a tool that’s named ‘EZQuery’, ‘EZSQL’ or something similar, I cringe. Sure, you can get the data out, but do you really want to jump through hoops using an ad hoc query tool? I guess you can perform heart surgery using a cleaver too, but I’d want a scalpel! However, the worst ad hoc reporting tool that I ever used has got to be a natural language ad hoc query tool that Jefferson was using for one of their databases. I still remember this monstrosity and how horrible it was. It was a website and you would input a natural language query (eg. I want the Discharge date, DRG, patient mrn and visit id for discharges from today-7 until today) into a text box and hit submit. Then you would hope that the interpreter would understand your query and not give you a error, which happened more than half the time. After tweaking your query endlessly so that it would understand (so much for understanding my language, eh?), you prayed that your query would give you search results that bore some sort of resemblance to what you originally wanted.

    As for what kinds of things that system designers can do to make ad hoc query access easier, I would include a comprehensive manual (most ad hoc tools comes with little documentation) and also include sample queries from which to emulate. Good examples are priceless and very useful. Also, every ad hoc tool should have an option for you to modify the underlying SQL directly. If I had that option, I would use it exclusively and just write my own SQL. Other than that, I don’t really see how we can improve ad hoc tools. They have a very specific niche and unfortunately, it’s not my niche.

  • My only exposure to ad hoc report writing is through an application software system. Fortunately, there are over 50 template reports with approximately 5 to 10 variables for possible selection within each and the templates truly meet 85+% of my needs. The system is a SoftMed/3M product called ClinTrac that the coding and abstracting is completed through. The system uses Crystal Reports as the reporting system the templates are created within. There are, however, times when I receive a data request that I cannot meet 100% because of my lack of knowledge of Crystal and my inability to modify existing reports extensively. The company had recommended someone receive Crystal training at the facility when they converted their reporting system to Crystal, but my organization made the decision to send someone from IT who ended up resigning and taking the knowledge I could have benefited from with him. The system does allow me to be a primary resource for diagnosis, procedure, physicians and DRG information.

  • I use to program in Visual Basic and use Access with some Crystal report experience. When I was programming I didnt run reports but I do use access at work to download data which is usual from a excel spreadsheet and create my own reports instead of using the vendors custom made reports. I do however generate my own reports from our financial system which has a unix feel to it, no GUI interface at all. The program is called Core/Eclypsis. The majority of the reports have been set up and I can modify/add criteria as needed. I only have access to a number of reports unless I copy someone elses report which has the permission to share and modify the report as well instead of creating a new report which I find helpful. The system is cumbersome to get through. I find Access easier because it builds report for you based on your selection and you can add your own qualifiers in the queries. I find the most difficult part of getting through the systems at work is selecting the right field to set up in the report which may be completely different in the other system but have the same name. I think system delevelopers can make reports easier by ensuring the interface is simple and the data elements used to create the reports are defined. For example I wanted to select all PNA pt who were admitted through the ED but there were several fields with “Admit”, source, type, ect. I had to look in another system to figure out which “Admit” field equaled the Point of Origin in the other system to generate the same outcome.

  • In the past, I ran ad hoc reports within software systems both for myself and for end users of the system who may not be familiar with how to run these reports. Or some end users may not have security access to these reports. These reports included data related to healthcare. I do think (and as already mentioned) that limitation in ad hoc reporting includes inadequate documentation on its use. Another limitation may include slowness of a system when reports with large data are run in the system. Providing detailed information on how to use ad hoc reporting is a way system designers could make ad hoc less complicated. The documentation should include descriptions of what the report columns are pulling.

  • My recent experience with ad hoc queries has been as a database administrator. As a dba, I address performance and system issues associated with users running ad hoc queries against transactional databases. Unfortunately, we do not have a data warehouse that contains data from the numerous applications that we have. As a result, we have end-users running ad hoc queries against the same databases where inserts, updates and deletes are being performed. This can cause serious performance degradation that affects the real live applications that customer service representatives use when dealing with incoming calls.

    Most of the limitations that I see regarding users running ad hoc queries are related to lack of knowledge about the data stored in the database. It’s very important for users who are running ad hoc queries to be familiar with the tables and fields and know what information they are trying to retrieve from the database. If they don’t know this, they could pull data from tables and perform analysis on the wrong subset of data.

    There are several things that can be done to make running ad hoc queries easier for users. The first thing is to educate the users about the structures in the tables and where data is being stored. It’s less overwhelming for someone to write queries if they are familiar with the data and know where it is stored. Another thing that can be done to make the user experience better is to educate them on how to write the most efficient sql. A large number of ad hoc queries have performance issues because the user is either selecting all columns instead of just the ones that they need or they have no where clause which results in a much larger dataset.

    In discussing ad hoc queries, I think the most important thing is to make sure that the end-user has access to the data; is familiar with the data and knows the most efficient way to retrieve that data while having the least amount of impact to the system and the other users.

  • Health Information Management departments recieve requests for reports on a consistent basis. Other departments including clinical staff within the hospital know that HIM is a good place to start if you are looking for organzed clinical data. I use Streamline Health Softmed Clintrac program to perform ad hoc queries to fufill requests for clinical data. If its coded data(ICD9CM of CPT4)it is easy to retrieve. There are can reports that generate informtion with just a few minor changes, and there are reports that you can create the query for. HIM historically maintained relational database that housed information used for reporting, research, financial and insurance purposes.

  • I’ve created and run ad hoc queries using MS Access, Crystal Reports and most recently here at Temple in the HR Department using an application called FOCUS to query mainframe data. With FOCUS the biggest limitation initially was learning the query language well enough to generate the reports requested. The system was in the process of being replaced, so there were no funds available for training. Thankfully, the system designers did a great job of documenting, providing examples and maintaining up-to-date data dictionaries. Another extremely helpful thing provided was a set of cubes or data subsets which consisted of the most used fields created each night, so that we could quickly run queries the following day on almost up-to-date data.

    Tonya mentions two good ways make ad hoc querying easier: by educating users about table structures and where data are stored. I also think systems designers can make ad hoc access easier for users by gaining a better understanding of how their organization operates. Having some knowledge of who is using the system, how the system is being used and the types of reports being generated may be helpful in the development of a system design that is more user friendly.

  • Experience with ad hoc queries:
    One example is a report we called the disease index which list all new patients diagnosed with malignant carcinomas within in the last month.
    When we actually update this report to remove or add new diagnosis as per regulatory guidelines, we have to be very clear / specific with the description in order to abstract the correct information, especially because the query has draw from several point of care and or clinical diagnostic areas to route the data back to us in the form of a monthly report. For example if the ICD-9 code with the proper number of digits is not entered, then we will not get the right case finding data we are attempting to obtain.
    Systems designers:
    Designers need to test the query and stay in touch with the person who is managing the knowledge so that accuracy can be ensured.

  • In my current position, there is no need for running or requesting ad hoc reports. However, when I was the coding manager, I ran ad hoc reports from Softmed Clin Trac for various reporting structures. Those reports were run from templates or customize Crystal reports that were in Clin Trac reporting module of the application. All I had to do was enter the time frame and select detail or summary report. A summary report only gave you total numbers, whereas a detailed report listed all items in the report request. On occasion, I would receive a request for a specified report that did not fit the templates’ criteria; therefore, I have to create a customized report. Moreover, reports for CMI, DRG, Coder’s productivity, and other HIM frequently requested reports were set up as templates.

  • I worked in Oracle databases with MySql as a tool to process the Ad hoc queries. I found the tool very powerful. MySQl can also be used to write triggers, procedures and functions to get some useful features as event handling etc.
    When I worked in the CRM application every mouse click on the front ends used to result in a generation of a query to fetch the information from the database. the queries are self built. when customizing reports I had to build some new queries to fetch data load data etc.
    In present days many GUI based applications have come and can be designed to just interactively select the columns( not necessarily the exact same columns in the database) but in end user understandable language easily without even knowing the Tables or columns involved, but there might exist or a Ad-hoc as the name itself suggests a new query requirement which needs to be processed at that time we need to know SQL to pull data from the back end and it does not happen quite often.
    till that any one can use and build their own ad hoc queries.

    One most be very careful while giving the access privileges will updating or deleting the content using the GUI. It might result in loss of data due to one unexpected operation.

  • What is your experience with ad hoc queries?
    In my current position, we utilize and create ad hoc queries to review, clean, and query clinical data. I create simple ad hoc queries using an interface via a database called Clintrial. In Clintrial, there is a specific module where we can pull tables and field names to create our queries. For example, creating a query to pull all subjects that have had an adverse event of “syncope” or creating a query to pull all subject hospitalizations after 2008. If I need a query that requires more intricate query language we would then request that from our programmers.

    What limitations have you run into in when “asking” questions of the data held in systems that you interact with?
    Some limitations that I have experienced would be extraneous fields and tables listed in our interface that users do not need or trying to remember certain naming conventions for fields and tables. Although we are provided with lists of tables and their respective fields sometimes it can be time consuming to find the right field from the right table.

    What kinds of things do you think systems designers could do to make ad hoc access easier for users?
    One thing system designers could do to make ad hoc access easier for users would be to purge tables and fields not needed. Also system designers could create an ad hoc query library that users can access for queries most often used. Users then could tweak those standard queries as they need them to pull and review data. In Clintrial, we have the capability to create query libraries but unfortunately this functionality has not been maximized.

  • On the other-side of the database – for me, as a database Administrator, ad-hoc queries are my nightmare. Whether a human written SQL or a generated query by a BI tool (such as Oracle’s Discoverer, SAP’s Business Objects, or many other tools) – such queries utilize scare resources of an OLTP database.

    I’m very interested in tools which generates SQL queries for non-IT users. There is a lot of IT and business work when planning and setting up some tools where the business rules are created and mapping is done between the actual tables in the database to business terms (customers, sales, orders). The BI tool can automatically generate the appropriate SQL based on what the end user wants to see.

  • Currently I run ad hoc reports in a database report writer called DI (Digital Innovation) Writer. Most times I have been able to configure the report to include what is requested. One issue I do tend to have is creating a customized report with a huge bulk of information. An example is trying to get years worth of patient’s name, MR#, dates of admissions, diagnosis and procedures & complications all into one report. I think that system designers could figure out what limitations can occur and figure out if it would hinder the user from abstracting the appropriate information.

  • I’ve experienced the same issue when trying to update our database with new guidelines. Sometimes the state systems are tested a few times before making changes. Other times glitches would occur which made our Report Writer system run slower or not allow for a report to be written due to changes.

  • Similar to Shiny and Ping, I do not need to run reports in my current posiiton. I did, however, taught myself how to use Access to collect results of two particular Behavior scales documented by nurses. I did not have to create new fields because I was just replicating what was already on the form. The reports are restricted to just the Psychology department.

    Debbbie mentions how a system can be slow when running reports with large data sets. I’ve encountered this with our system we use for therapy (TEAMS). It would take between 5-7 minutes to pull up individual patient schedules and the unit coordinator cannot print until they get the okay from the Team Leader or therapy manager (usually around 9am). This affects the workflow of patient care on the unit. The nurses need to know what therapy session each patient has beforehand in order to administer all the medications and make sure they have had breakfast.

    I agree with many others that educating users would be helpful. Also, the system designer should have a sense of how the organization operates.

  • Most of the systems at I have worked with at 3MHIS had either prepackaged reports or a GUI within the application where some basic reports could be created. As an analyst, it was generally necessary to use SQL directly to troubleshoot issues the client was encountering with the application or to run reports to data mine our client’s success with the application. The limitation I generally encountered was my lack of official training in SQL and or technical background. It was a huge barrier when troubleshooting because I was constantly reviewing documentation or consulting with development. When development did provide a GUI for a specific application, it usually lacked documentation. So I just had to learn it by trial and error and then teach the client. Although at times it was difficult to navigate a problem with limited knowledge and lack of documentation, it was a great learning experience. In conclusion, some of the things that system designers could do to make ad hoc access easier for users are to anticipate a user’s needs and create a GUI, provide documentation on the GUI, and possibly some training.

  • I have zero hands on experience with Ad Hoc Queries. I am looking forward to tonight to do some actual work.

  • I have the same experience with Microsoft Access. The user interface that essentially writes the code for you can really slow down the process with larger files. Also, the user interface will break down if you change some table/query names, which forces some users to start over or specifically go into the code and change the names. But, as all other tools, it serves its purposes as long as you understand the limitations.