|
If you believe most demonstrations of query and reporting tools, getting valuable answers out of complex data is as simple as clicking on a couple of buttons and within seconds the magic numbers appear out of nowhere.
Experienced data analysts know that that is almost never the case in real life!
In the real world, questions are often poorly defined, the data may be complex, possibly a little dirty and certainly not just sitting there in the required format. It takes some effort to understand the data, refine the question, produce and test preliminary results, gain insight, overcome obstacles and ultimately to move to final solid results.
In our experience the very best Data Analysts start by investigating the broad data to see what is available that may help to answer the question. They actually look at the data via both samples of rows and aggregates on important columns. They check for missing or unknown codes, outliers and default values. Where data is joined between tables they ensure that rows don't 'drop out' due to incorrect relationships.
Everything needs to be subjected to common sense tests and cross validation wherever possible.
Then the first cut results cause a change in the problem definition or lead to an alternative line of reasoning.
This is challenging for the analyst. It is the ability to grapple with problems at this level that defines the true analyst. It is also why specialised information systems and tools are needed to provide true Business Intelligence. The final answer is important but it is the process, the creativity and the rigour that gets you there.
QiD is designed to help deal with the real world complexity that is both the bane and the joy of the Data Analyst's working life. Our features in the area of Project Structure and Managament give the analyst unparalled abilities to create, maintain and re-use queries and processes to answer questions from the simple to the complex.
Some of the main features of QiD in this area:
- Queries can be written and executed either individually or in sequence in what is known as a 'Sheet'. A single sheet can be up to several thousand lines in size if that is what you want - however we recommend breaking processes into smaller chunks for easier understanding and management.
- Multiple sheets for a related piece of work can be grouped into a 'Project'. As many projects as desired can be stored for as long as desired. They can be copied, renamed, searched, deleted etc.
- Each project and each sheet can given a meaningful name and description. Also the appropriate database connection to be used can be set at the project but overridden at the sheet level if necessary
- Multiple projects can be open at once within QiD. Choosing a sheet to work on is as easy and quick as clicking on it in the project tree. You can copy and re-arrange sheets within and between projects.
- Projects can be exported and given to another QiD user. Importing a project brings all of the structure, text, variables and settings that are part of the project.
|
The project tree in QiD showing four open projects:
-
The 'Demo on MySQL' project has been modified but not yet saved
-
The 'Work' project has the sheet '0100 - Select' highlighted because it is currently selected (and available in the SQL Code Editor)
-
The 'SQL Server - Test' sheet within the 'Work' project has a different connection set as indicated by the icon. The specified connection will be used instead of the project setting for that sheet only
|
|
The Project Management dialog showing available projects:
-
Large numbers of projects can be created and managed to retain your analysis work for future use
-
The projects are shown in a normal data grid. All of the usual QiD facilities are available to assist with finding projects within the list - sort, filter, group etc
-
New projects can be created through this dialog or through the main menu or toolbar
|
By contrast, other database query products make it surprisingly difficult to organise and retain the various queries that together are required to solve a problem. Here are some of the approaches that we have seen in other products that we believe are vastly inferior to the capabilities of QiD:
- The equivalent of sheets have to be laboriously saved and recalled using File / Save and File / Open
- Only one sheet / file can be open at a time. To look at or execute another query you have to close the current one and open the other
- Due to this approach you end up creating single files that contain the whole script from beginning to end and that can be scores of statements over thousands of lines. Scrolling up and down such files and choosing bits and pieces to then run one step at a time is a nightmare
- You are not warned if a query has been modified and not saved before you close it or exit the product !
- There isn't really a project structure but you can bookmark a query as a 'Favourite'. Fine but what about the 16 other related queries that we might want from time to time in the future just for this one bit of work? And what about the hundreds of separate analysis pieces that an analyst will perform over a period of months and years?
- You rely on a query history. Unfortunately it keeps only the last 300 queries that you ran (or those in the last 90 days or whatever). So individual queries from different pieces of work are jumbled together according to whatever order they have been run in. Too bad that the complicated but infrequently used problem investigation query from last year has dropped off the history!
The facilities provided by QiD are more like how we use spreadsheets compared to the Windows Calculator accessory. The latter assumes that the working calculations can be discarded once the answer has been produced. The former understands that we need to keep our workings and refine them over time. There will be variations and supporting analysis to store in different worksheets of the same workbook. We might want to modify and re-use processes over months and years.
|
