Step 1: Verify what you have been told
A user has brought a data problem to your attention. Be sure to verify that everything your user has told you is in fact correct. Your user may not fully understand the data set or may be operating under false assumptions. Make sure you can see the problem data for yourself and that you understand the problem as presented. This will save you hours from investigating non-existent problems that were merely data misinterpretation.
Step 2: Reduce the data set
Focus like a laser beam on the smallest possible set of data that exhibits the data set problem. Ideally you’d like this to be less than 10 if possible, but anything fewer than 100 is easily manageable. This will allow you to speak to your user in concrete terms possibly about a single transaction, and will also allow you to rapidly run ETL or OLAP cubes quickly and repeatedly on this reduced data set.
If your data is in an OLAP cube, pulling a reduced data set can be simply a matter of navigating the hierarchies of a dimension for a small record set. Otherwise, your user may do this for you up front by providing a specific example. Ask for an example from your business user if necessary.
Step 3: Isolate the environment
Where is the problem? In the ETL code? In the OLAP cube delivery? In the report? In a report’s individual query? This will take some investigative work. Be sure to identify the root cause, not merely a symptom. The quicker you can isolate the environment of your problem the sooner you will be able to find the problem source.
Step 4: Find and resolve
The simplest explanation is usually the first place to check. Look for recent changes to an environment. In a report, check prompts, filters and calculations. In ETL, check logic against business rules and ensure results are as expected. In all cases, check query results closely. Individual queries, calculations and other derivatives are a common source of problems.
Step 5: Follow Through
Ensure that you follow through with the user. You may have resolved an issue, but it might not necessarily have been the problem that the user had in mind. Do not assume your user will magically know the problem is fixed. Confirm with the user that the issue is in fact resolved.
If multiple issues exist, this cycle may need to be repeated until all issues are addressed.
#1 by Garnie Bolling on June 9, 2010 - 11:35 pm
Hmmm… Trust BUT Verify…
I see that you put Verify First… which is always a prerequisite for almost every project / issue / process.
Here is a twist, what about the issue from an application to data source comes up, when most of the time there is a layer between Applications (and application developers) and the Database. Technologies like Hibernate and iBatis are abstraction layers that “rewrites” the SQL that connects to the Database, basically allowing the Application team write code without knowing SQL, or the Database, so the data set is a result of the abstraction layer interpets the original query… and leaving off the Application Session ID, and no identifiers or tracing capabilities to do root cause… I digress.
Here is the every growing need for collaboration and tools to enable collaboration between users (being applications, people and developers) to the data source (DBAs, databases, or warehouse systems).
I am glad that there is a growing and developing set of tools that allows insight to the process of a query… and that will support the Verify & Isolate activities
Thanks for sharing…