In the previous article on Data Driven Framework using QTP Data Tables, you saw how you can add data in QTP Data Table and then write a script that can fetch data from this Data Table. This is the most basic method and also the easiest to implement. But the issue with this approach is that though it is easy to use and implement, it is not a suitable method when the number of test scripts is large.

So whenever you have a large number of test cases or you have large amount of data that needs to be validated, it is always advised that you use some external source (which would be Microsoft Excel in most of the cases) to store your data. But identifying a data source is just the beginning of the story. The most important part that you have to take care is how your data should be organized in the external data source.

There are many ways in which you can save or group your data in the excel sheets. Consider a scenario where you have, say 20 different test cases to be automated. Let us see what all different ways are there to save the data for all these test cases in excel sheets.

• 1) You can use only one excel sheet where you can save data for all the test cases.
• 2) You can have one excel workbook and 20 sheets in it, thus each test case will have a separate sheet for itself.
• 3) You can also have 20 different workbooks (each with one sheet) for the test cases, i.e. One workbook for each test case.
• 4) If you are dividing your test cases into multiple business flows, then you can have a separate sheet for each business flow.
• 5) Other than these, there can be many others ways that you can use to save your data in the excel sheets.

As you have seen above, there are many different ways you can use to save data in the excel sheets. The important question that arises here is – which of these methods should you use in your Data Driven Framework? Please note that this is one of the most important things that need to be done as part of a Data Driven Framework and this format should be identified before you start writing your test scripts.

This is because your entire code and framework flow will be decided based on the format or structure of the data sheets. And if at a later point (when you have scripted quite a number of test cases) you come to know that the data sheet format you have used is not that efficient, you might need to do considerable amount of rework in the scripts and the data sheets to make the necessary corrections.

To help you with this identification process, we will cover some of the most common data sheet formats that are used in data driven frameworks. We will also see the advantages and disadvantages of each of these formats and will also try to identify the scenarios where these data sheet formats/structures can be used.

Format 1: Adding all the test cases in a single excel sheet

Here, you will have only 1 excel sheet (or workbook) and the data for all the test cases will be saved in that single excel sheet only. Here each row in the data sheet will cater to 1 test case. So if you have 10 test cases, you would be using 10 different rows in the excel sheet (i.e. 1 row for 1 test case).

Where can this type of data sheet be used? You can use this data sheet format in the following scenarios –

• 1) In this type of format, all the test cases are saved in the same sheet, hence they will be sharing the same number of columns. There would certainly be cases where a Column used by one test case will not be used by some other test case. Hence this type of data sheet is more suited where the test cases have similar sort of flows so that most of the columns are used by most of the test cases.
• 2) Since there will always be many cells that will be left blank by one or the other test case, this data sheet format will be more effective and manageable when you have fewer number of test cases and lesser number of data for each of these test case. Both these points will ensure that the number of columns stays within manageable limits.

Sample Scenario where this data sheet format can be used. Refer the test cases in the below table and the data requirements for each of these test cases.

The above 3 test cases have –

• a) Somewhat similar kind of flow (because of Login component).
• b) Have few data requirements and hence fewer columns in the data sheet.

So these test cases can use shared data sheet approach. The below figure shows the sample data sheet for the above mentioned test cases.

Advantages

• 1) You can change all the test data from a single sheet only.
• 2) The coding portion, where you fetch data from the data sheet is relatively easier to write due to the single sheet structure.

Disadvantages

• 1) This type of data sheet format is efficient only when the number of test cases and the associated columns are less.
• 2) Since the data sheet contains columns for all the test cases, identifying which column is associated with which test case is somewhat difficult and this may sometimes result in errors while filling the data.

Format 2: Having separate Excel Sheets for each Test Case

In this format, you can use separate excel sheets for each test case. For example, if you have 10 different test cases, you will have 10 different excel sheets.

You can add all these excel sheets in the same workbook or you can have separate workbooks (each with one sheet) for each test cases.

TIP: One best practice that you can follow here is to keep the same name for the excel sheet and the test case. Example: If your test case name is TC_01, then you can have the data sheet for this test case named as TC_01. This is advantageous because of the following 2 reasons –

• 1) You can easily identify the data sheet for a particular test case using its name.
• 2) You can use Environment.Value(“TestName”) property to find the test case name and thus you can write a generic function to fetch data based on the test case name.

Where can this data sheet format be used? This is the most common data sheet format that you can use in the following scenarios –

• 1) You can use this data sheet format when you have test cases with entirely different flows (and hence different columns that would not be used much by other test cases).
• 2) This format is also suitable when you have large number of test cases or large number of data requirements (columns) for the test cases.
• 3) This data sheet format is also well suited for scenarios where you have to run each test case multiple number of times. In each excel sheet, you can add as many number of rows as the times your test case needs to run. And then you can use a for loop to loop through all the rows in the data sheet and run that particular test case that many number of times.

Sample Scenario Consider the below table where you have different test cases, their purpose, data requirements and the number of times each of these test cases need to be run.

For the above test cases, we can create the data sheets as shown in the below image.

Advantages

• 1) Since each test case is added in a separate sheet, it is very easy to scale up this framework to add more number of test cases. To add more test cases, all we have to do is add more sheets to the existing workbooks.
• 2) Since each test case has a separate data sheet, it becomes very easy to run any test case any number of times. For this we just need to add multiple rows in the same data sheet. Running 1 test case multiple times doesn’t affect the execution of other test cases in any way.

Disadvantages

• 1) This framework is not very efficient when you have similar sort of flows in different test cases. For example, let us consider that you have 10 test cases where you have to Login to GMail once and then do some other action. Now suppose that you have used the same UserID and Password for each of these test cases. Let’s say that you have changed the password for your user ID. Now in order to change the password for all the test cases, you would have to make the changes in all the 10 test data sheets.

Format 3: Creating separate excel sheets for each Business Flow

In this case, you first divide your test cases into multiple business flows (reusable and non-reusable) and then you create an excel sheet for each of these business flows. Check the below image which shows few test cases and the business flows for each of these test cases.

Now you can create the data sheets for each of the business flows. There would be some business flows that would not need any data (CountEmails and Logout in this case). For those business flows, you need not create any data sheets. You can keep the name of the data sheet same as the business flow. This would help you identify the data sheets without any issues. Let us see how the data sheets would look like for this scenario.

Where can this data sheet format be used? You can use this type of data sheet structure when you use pure modular framework or modular based hybrid framework. More the scope of reuse of business flows, the better it would be for this data sheet design.

Advantages

• 1) In case of reusable flows, all the test data for different test cases is available at a single place. So its easy to change the data in this case. For example, if you want to change the password for your test cases, you can easily do it as all the login details will be available in a single data sheet.

Disadvantages

• 1) Since the data sheets are created on the basis of business flows, if you want to change the complete data for a test case, you have have to do it in multiple data sheets because your test case will most probably be using multiple business flows.
• 2) Also, keeping a track of all the data sheets associated with a test case becomes bit difficult sometimes.

This was all about the common data sheet formats that you can use in Data Driven Framework. Please note that each of these formats has its own advantages and disadvantages and hence none of them is superior over the other. Based on your requirements and analysis of test cases, you should be choosing a data sheet structure that would help you get the maximum benefit.

Let us know what you think about this article. If you feel that there is any other data sheet format that can be included here, then please let us know about it using the comments section.

If you enjoyed this article, you can join our blog to get new articles delivered directly in your inbox.