Reduced / Selective Test Data Execution to SAVE Time & Effort - Introduction

Context: Data Warehouse / Business Intelligence (ETL Execution)

Background:

If you have ever involved in DW/BI Testing, waiting for ETL execution to complete won't be an alien subject to you. Its not ONLY frustating to discover ETL fails at Step X after running for say 8 long hours but its ALSO a show stopper for you to continue with your functional testing and directly impacts your efficiency and effectiveness.

If there are performance lags (like low configuration h/w, network bandwidth etc), the ETL job is only going to take longer than expected and often times this adds to higher WAIT Time (a.k.a NON Productive time).

Root Cause:

If dev / test team wants to run the ETL with Production data, which can be really huge (in GBs or sometimes even TBs), due to the volume its obviously takes a long time.
Trade Off is REAL vs SIMULATED data. Testing with REAL data comes with a cost.

Is testing team the only victim?

Answer is NO. Even Dev team has to wait for entire ETL to run to see the changes made in the code while doing integration testing.


Why don't we create Smaller test data ALWAYS?

-> Due to the data integration and transformational complexities, the team has to have very deep domain knowledge of the business rules to create test data which satisfy the ETL conditions (often times lack of this results in ETL failure or ZERO o/p after the run)

-> If the complexity is high (consider data coming from multiple disparate sources) then creating data while maintainig logical relationship can turn out to be quite tedious and time consuming.

Why dont we SELECT REDUCED / SUBSET of Test data from Production?

This is the heart of this blog and we are going to emphasize on this in detail. Though the above sounds simple and the obvious solution but it has been observed that teams dont find it very popular time because of following important reasons:

1. Random sampling of data doesnt work. Many times it will result in ORPHAN RECORDS (where you might end up pulling child records without parents records from the source)

2. ETL might break if the keys are not found.

3. ETL might succeed but can result in ZERO output records as none of the business conditions got satisfied for the the sample data.

4. Business / Domain knowledge is required

What do we recommend to select SUBSET test data with success?

1. Select a pattern:

a) Master Data Driven

b) Transaction Data Driven:

2. Identify Business Conditions

3. Select Sampling Method

4. Copy the SELECTED test data to a test db

5. Modify Test Data to cover Boundary Value conditions

6. Run the ETL

What are the advantages?

1. Save Time

2. Save Effort

3. Save Hardware Cost (Less Data, Less Space and computational resources required)

4. Better Test Coverage (High Requirement Coverage)

5. Better Code Coverage

The subsequent post will focus on the above methods in detail....Please share your feedback and suggestions.

SSAS Cube Testing

1.1 BVT / Smoke Testing Scenarios:

1. Ensure only the user with the desired permission is able to connect to the cube

2. Validate Data Source Connection string for cube

E.g. Provider=SQLOLEDB.1;Data Source=<Test ETL Server>.redmond.corp.microsoft.com;Integrated Security=SSPI;Initial Catalog=dbCPRMart

3. Validate your are able to successfully process the cube

4. Validate your are able to browse the cube

5. Configure and validating Analysis Services Query Logging

1.2 Data Validation Scenarios:

Approach 1: Browse the cube using BIDS or SQL Server Management Studio and compare the output against Mart using SQL query

If it’s a new development of a Cube, Browse the Cube, drag and drop measures on the Page and Dimension on the Y-axis. You need to write T-SQL that does similar aggregation that brings similar output.

Eg:

OWC Output:

                                 SALES        PROFIT

AP

      CHINA                 200                 60

      INDIA                   250                 50

US

      CHICAGO              500               65

       WASHINGTON      550               65

Equivalent T-SQL:

SELECT REGION, CITY, SUM(SALES ), SUM(PROFIT)  FROM GEOG G JOIN SALES  S FACT ON G.ID=S.ID

GROUP BY REGION, CITY ORDER BY 2

Approach 2: MDX query to compare data with SQL Query executed against Mart

2.1 For simpler Cube & Dimensional Models

Write your MDX query and compose SQL query for the same conditions and results should match

2.2 Automatically generate MDX using SQL Profiler & Reporting tools and compare it against SQL

SQL Profiler & Reporting Tools like PPS, ProClarity provide the capability of generating MDX on Cube conditions. Use these MDX against the SQL queries formed by you based on the relational / dimensional model on Mart.

Approach 3: Black box testing using sample test data

Insert / Update / Delete test data in your backend and calculate the outcome value based on the desired functionality / requirement without going to cube and ensuring that your expected output value matches the cube output in the final reports.

Approach 4: AMOMD object Compare

Create Automatic Verification Mechanism between OLAP and SQL objects

http://msdn.microsoft.com/en-us/library/cc281460.aspx

http://msdn.microsoft.com/en-us/library/cc280975.aspx

Figure: shows a high-level view of the AMO object model for OLAP objects

Reference:

http://microsoft.apress.com/feature/74/introduction-to-analysis-management-object-amo-programming

1.3 Cube Design Scenarios:

1. Validate all measures

• Open Visual Studio analysis services DB
• Browse and Open Cube DB
• Double Cube and browse to "Measure" pane
• Select measure <measure name>
• Go to Properties, and check "source" field

     Expected Result:

• Measure source should be set to correct table as source table and correct column as source column in Mart
• Ensure measure has all the required fields / columns as present in the mart

2. Validate all dimensions & dimension hierarchies

• Open Visual Studio analysis services DB
• Browse and Open Cube DB
• Double click Cube and open data source views and Open <Dimension name> Dimension under "Dimensions"

   Expected Result:

• Make sure Table columns in Mart are mapped to Dimension correctly
• Make sure Dimension key is correctly mapped to Dimension key column of the dimension Table in Mart
• Make sure all the required columns / fields are present in dimension as present in mart
• Ensure Hierarchy is correctly defined
• Fiscal Year -> Fiscal Month -> Fiscal Week -> Calendar date

3. Validate all calculated measures

• Open Visual Studio analysis services DB
• Browse and Open Cube and go to "Calculated" tab
• Check expression for "Calculated measure name"

    Expected Result

    The MDX expression defined should be accurate as per your requirements.

     Eg. [Measures].[ChangePoint Total Backlog] + [Measures].[Siebel High Pipe] + [Measures].[Siebel Low Pipe]

4. Validate “Data Source Views” of your cube against your design

 

1.4 Security Testing Scenarios:

1. Ensure each user belonging to a cube role has appropriate access

2. User with read permission should only have access to browse the cube

Read Definition checkbox should be selected

3. User with Process permission should be able to process the cube as well.

Process Database checkbox should be selected

4. User with Admin permission should be able to browse, process, make changes to the cube as well.

Full control (Administrator) checkbox should be selected

5. Cube roles should be mapped to correct users and group

6. Cube roles should be having restricted access or unlimited access to dimension data based on the design and project needs

1.5 Miscellaneous Scenarios:

1. Backup and Restore:

Take the backup of the cube and try restoring. The functionality should remain working as earlier.

1.6 Performance Scenarios:

1. Optimize Cube Aggregations

Before running your stress tests, you’ll want to ensure that you OLAP design has optimized aggregations. To optimize your aggregations, you’ll need to first turn on SQL Server Analysis Services query logging, then run the cube optimization wizard.

2. Using load simulator

Reduce number of invalid defects -> Improve Test Productivity and Efficiency & Let your Developers be happy

Just the other day we were discussing that how can we (testing team) reduce number of invalid defects. I was in deep thinking that why is it really important to reduce  the number of invalid defects.

• Isn't it a tester's fundamental job to log every potential defect and let it go through normal defect life cycle and let business and management take call if it is a valid defect or not?
• Isn't it right that tester shouldn't assume that it is not a bug and then regret later because of  a false assumption made?
• Isn't a tester taught to think negative and always be suspicious and uncover what is not seen by someone like dev?

 

The point here is what is a big fuss if testing team raises 'invalid' defects unknowingly. At least they don't leave anything to assumptions which is far more dangerous. There primary job is to find defects, whether that is a 'valid' defect or not is a secondary question.

One of the tester I am mentoring complained that his testing team had found 108 defects out of which 104  were valid and only 4 were invalid but his management didn't seem to appreciate them for the the number of valid defects found as they were expecting number of invalid defects to be zero.

My take on this:

Yes, it is important to reduce number of invalid defects.

Why?

-> Test Metrics gets screwed (Test Effectiveness or lets say Test Productivity goes down with no. of invalid defects)

            Test Effectiveness = No .of Valid defect  / (No. of Valid Defects + No of invalid Defects) X 100 %

            Example for above:  104  / (104 + 4) X 100 % = ~96.6 %

-> Time lost in tracking and logging invalid bug

When you raise a bug in your reporting tool like Test Director, it has to go through complete bug lifecycle. Say you raised a bug spending effort in recording it and then it turned out to be invalid, you developer rejects it.  Finally you have to close it.

-> Management don't like invalid bugs

You bet me that "invalid" bugs doesn't please any manager. It is a human behavior to criticize something that is not right.  It sets them off.

-> Developers stop taking you seriously

When they observe that you raise many invalid bugs, they start expecting that every time. They stop paying due attention to valid bugs considering them to be invalid. Quality over Quantity concept.

-> Time lost in triage meeting to discuss invalid bugs

When you log invalid bug, it not only your time getting lost, developers waste their time reading them, then testers and developers waste time arguing on that as it has been officially logged, and most importantly business waste their time in triage meeting to take call.

-> Spoiling terms with development team

Developers are under pressure to reduce number of defect found in their code by the test team. Now if you log it, they go defensive and try their best to prove your bug an invalid bug so that it doesn't spoil their commitments.

 

Now that we have a problem. Let me propose something which we successfully implemented

 

 

Now with this process, every bugs gets verified online by the Development team even before we officially log it. They update the sheet saying that they are okay with so and so bugs and we log only those bugs in the Bug Management tool  and hence all "VALID" bugs.

Bugs which they update as "INVALID" or "REJECTED", we update the SHARED SHEET with more information like repro steps etc and they change the status in the sheet accordingly. Now if it was our fault and it was actually INVALID defect, we update the SHEET and close it there itself rather than logging it in bug management tool and going through the entire process.

Now our metrics always say  100 % valid bugs. We don't miss any bugs because we record it anyway in the Shared Sheet and triage it with development team online. Development team feels good as they get a chance to to repro bug and confirm before it is actually logged against them.  We don't have to waste time in the triage meetings discussing whether its a bug or not. Now business only take call on functional bugs which are more important to end user.

Butterfly Effect in Software Testing (its happening all the time, did you ever notice)

"Small variation in the initial condition of a dynamic system may produce large variation in the long term behavior of the system"

Example: The phrase refers to the idea that a butterfly's wings might create tiny changes in the atmosphere that may ultimately alter the path of a tornado or delay, accelerate or even prevent the occurrence of a tornado in a certain location.

and for we guys "Small variation in the initial  requirement of a dynamic application / product / system may produce large defects in the long term usage of the system"

Example: A tiny misinterpretation or misrepresentation of a customer expectation in the form of requirement can ultimately impact the analysis, design and code, testing in such a way that it might result in a product failure or in worst case business failure sometime

here tiny change in requirement can be compared with butterfly's flapping of wings and because this change was not detected in the requirement stage and it keeps on passing through analysis, design and coding phase and finally gets so much amplified that the impact becomes a huge loss to the business.

 

One of my friend had been asked in an interview "what is Domino effect in software testing?"  He didn't know and hence couldn't answer.

It is again related to Butterfly effect and Chaos theory that a small change anywhere in the system (requirement, analysis, design, code, testing) can cause a similar change nearby and that will another similar change and so on in a linear sequence.

Example: A wrong change in requirement -> similar change in analysis to accommodate that -> similar change in design for that analysis-> similar change in code for that analysis -> similar change in test cases for that requirement and so on.

 

Interesting fact: The term is best known as a mechanical effect, and is used as an analogy to a falling row of dominoes.

Resurrecting the Prodigal Son – Data Quality (Presented at Test2008)

Saga of Unsung Heroes (A tribute to all testers in this world)