SQL Quick View

Four Categories

DDL - Data Definition Language - Used to define format and type i.e. structure of objects.
Ex.CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME

DML - Data Manipulation Language - Used to manipulate data stored in tables.
Ex.SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE

DCL - Data Control Language - Used to define roles, permissions, referential integrities and other security features.
Ex.GRANT, REVOKE

TCL - Transactional Control Language - Used to manage transactions happening in database.
Ex.COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION
 
Objects >> General
Tables - Objects which will stay permanently in database. Contains columns and rows.
CREATE TABLE Emp1(ID int Primary Key, FirstName VARCHAR(255), LastName VARCHAR(255), Salary int) CREATE TABLE Emp2(ID int Primary Key, Area VARCHAR(255), Track VARCHAR(255))
Views - Same as tables, but resides dynamically not permanently. But database will have query to make the view permanently.
1.CREATE VIEW V1 2.AS 3.SELECT * FROM EMP1 4. 5.-- Access the View 6.SELECT * FROM V1

Software Testing Jobs

http://www.gcreddy.com/2012/10/software-testing-job-news.html

Kutools - Tool contains advanced Excel features

For my project. i am getting the scenario to combined the three parameters ( Account Name ,PublisherName and Date) into one URI. After that this value to be imported (  Account Name ,PublisherName,  Date and URI) into tables.

I am searching the tools to combine the no of column values into one column value.Finally i found the tool called KUTOOLS,it is addon tool for EXCEL. 

You can download the addon from the below link : 

http://www.extendoffice.com/product/kutools-for-excel.html. ( This addon contains  More Than 120 Advanced Functions and Tools for Microsoft Excel )

once it is installed, the two menus ( Kutools and Enterprise) added in the Excel.  I have used  "Combines Rows and Columns" feature to simply combined the more than 10 lakhs rows. It contains lot of utility to do the work faster. 

List of Features Below : 

List all open workbooks within pane

List all worksheet names of a workbook within pane

List all columns and titles of a worksheet within pane

List all range names of a workbook within pane

Minimize the ribbon with one click

Minimize the ribbon and hide the status bar with one click

Minimize the ribbon, hide the status bar and formula bar with one click

Minimize the ribbon, hide the status bar , formula bar and ribbon with one click

Show or hide grid lines

Show or hide zeros in cells

Show or hide vertical scrollbar

Show or hide horizontal scrollbar

Show or hide row / column headers

Show or hide page breaks

Show or hide formula view

Show or hide selection pane

Show or hide sheet tabs

Show or hide inner tabs

Show or hide status bar

Show or hide formula bar

Show or hide windows in taskbar

Show or hide comments and comment indicators

Show or hide objects

Easily set or limit a scrolling area in active worksheet

Interactively hide or unhide multiple workbooks and worksheets at one time

Easily very hidden sheets in Excel

Hide all inactive worksheets or sheets with one click

Hide all inactive Excel windows with one click

Unhide all hidden ranges in current workbook

Unhide all hidden worksheets or sheets in current workbook

Unhide all hidden Excel windows in Excel

Rearrange the selected cells in random order

Select a cell or multiple cells randomly

Insert a sequence of integers in random order

Insert a random strings with customize specifications

Insert a unique sequence number

Fix trailing negative signs

Change all negative values to positive

Change all positive values to negative

Reverse the sign of all values in Excel

Convert values store as text into number

Convert values store as number into text

Swap two rows or two same size ranges

Swap two columns or two same size ranges

Convert a row into multiple columns

Convert a column into multiple rows

Fill cells with displayed values

Replace accented characters

Flip a single selected row

Flip a single selected column

Transpose cross table to list

Transpose list to cross table

Fill blank cells based on values

Fill blank cells with linear values

Fill blank cells with fixed value

Insert a specific number of blank rows at a speicfied interval

Insert a specific number of blank columns at a specified interval

Change the case of letters

Add text before,after or at a specified position in cells

Remove characters by position

Remove leading, trailing, leading and trailing ,excess or all spaces in strings

Delete certain characters

Combine multiple cells contents

Combine multiple rows contents

Combine multiple columns contents

Do common math operations in cells, such as add, subtract a number in a range and so on

Do mathematics operations with a handy calculator

Quickly convert between various currencies

Quickly convert between unit measurements

Select the greatest value cell in selected range

Select the smallest value cell in selected range

Select cells which contains an error value (such as #N/A)

Select cells based on certain criterias

Select entire rows based on certain criterias

Select entire columns based on certain criterias

Select cells according to the cell formattings

Select multiple ranges without press Ctrl key in Excel

Deselect cells from the selected range in Excel

Reverse selection of the selected range in Excel

Compare two ranges and select only duplicate cells

Compare two ranges and select only unique cells

Compare two ranges and select entire rows of duplicate values

Compare two ranges and select entire rows of unique values

Select one or specified rows at a specified intervals

Select one or specified columns at a specified intervals

Delete all blank rows in selected range

Delete all blank rows in active sheet

Delete all blank rows in selected sheets

Delete all blank rows in all sheets

Delete all hyperlinks in selected range

Delete all hyperlinks in active sheets

Delete all hyperlinks in selected sheets

Delete all hyperlinks in all sheets

Delete all charts in active sheet, selected sheets or all sheets

Delete all pictures in active sheet, selected sheets or all sheets

Delete all smart arts in active sheet, selected sheets or all sheets

Delete all lines in active sheet, selected sheets or all sheets

Delete all auto shapes in active sheet, selected sheets or all sheets

Delete all text boxes in active sheet, selected sheets or all sheets

Delete all embedded OLE objects in active sheet, selected sheets or all sheets

Delete all linked OLE objects in active sheet, selected sheets or all sheets

Format a text as superscript in a cell

Format a text as subscript in a cell

Shade every other row in selected range

Shade every other column in selected range

Insert date with date format in a cell

Apply date formatting for a selected range

Copy formulas without changing cell references

Convert relative refrences to absolute references

Convert absolute refrences to relative references

Convert relative refrences to column absolute references

Convert relative refrences to row absolute references

Replace range names with cell references

Convert error messages to some readable messages, such 0, blank cell, a text and so on

Merge multiple workbooks into a single workbook

Merge specified worksheets into a single workbook

Merge all the Excel files from a folder into a single workbook

Split active workbook into individual Excel file

Open the folder which the current workbook stays in

Copy full path of the current workbook and paste it where you like

Take a snapshot of the current workbook with one click

Open the current open workbooks automatically next time

Select the same range of multiple worksheets in one workbook

Sort sheet tabs by alpha

Sort sheet tabs by alpha numeric

Sort sheet tabs by color

Insert the specified text in front of the sheet name

Insert the specified text after the sheet name

Replace the sheet names with the specified text

Replace the sheet names with the cell contents

Create list of sheet names with hyperlinks

Create list of sheet names with buttons and macros

Make multiple copies of current worksheet or selected worksheets

Create sequence worksheets with month names in cuttent workbook or new workbook

Create sequence worksheets with day names in cuttent workbook or new workbook

Create sequence worksheets with numerical sequence in cuttent workbook or new workbook

Create sequence worksheets with series text in cuttent workbook or new workbook

Create sequence worksheets with customed names in cuttent workbook or new workbook

Create sequence worksheets with data in a range in cuttent workbook or new workbook

Create a monthly or a yearly calendar

Delete all blank worksheets in a workbook

Delete all hidden worksheets in a workbook

Export a range of worksheet to Excel workbook

Export a range of worksheet to CSV

Export a range of worksheet to unicode text

Export a range of worksheet to simple HTML

Export a range of worksheet to complex HTML

Export a range of worksheet to GIF

Export a range of worksheet to JPEG

Export a range of worksheet to TIF

Export a range of worksheet to PNG

Insert a file at the current cursor position in a workbook

Create a list of filenames of specified directory in a new workbook

Print multiple workbooks within same directory

Print multiple workbooks in multiple directories

Print multiple non-continuous selections or ranges in a workbook

Copy page setup setting from one worksheet to specified or all worksheets

Remove all macros from a workbook

Show or hide comments and comment indicators in a workbook

Create comment list in a new workbook or a new worksheet

Change all comment formats to the specified comment format in a workbook

Find and replace text within comments

Change or remove comments' author name

Convert contents of cells into comments

Convert comments into contents of cells

Copy multiple non-continuous ranges in Excel

Set row height and column width in pound, centimeters, inches or pixels

Create folders based on a list of cell contents

Re-run the last Kutools for Excel utility

Solution for facing Load UI issues while installing

I have searched the open source tool to test the load of the web application for my project. Finally I have choose the Load UI tool. Downloading the trial version and installing the tool without internet connectivity.
I got the error message "Application not loading" while installing the LoadUI tool.

After some analysis, i have found the solution to solve the issue. Please find the below :


In a command line, enter these commands:

1. javaws -uninstall
2. javaws -clearcache
3. From the Control Panel, Click Programs and open Java.
4. In the Security tab, click Certificates.... Remove all Trusted Certificates. Then close this dialog.
5. Also go to the General tab, click Settings..., then Delete Files..., check all items and click OK.
6. Click Apply and then View....
7. Make sure that the list is empty for all views (use the dropdown in the upper-left corner to change views). If there are items left, remove them by selecting them and clicking the red X.
8. Reinstall loadUI.

Now it's successfully installed to test the load of the application..... 

Basic ETL Concepts

Recently, I assigned to Data warehousing project to test the ETL (Extract,Transform,Load).

Lot of testers interested to know about ETL testing. So that i would like to share the article.

Here we go..

I hope this will give you some ideas to improve your ETL Knowledge and what we do to test ETL process.

ETL testing (Extract, Transform, and Load) means Extract the data from different sources table and transform by using some business logic. Finally load the data into target table.

Now we can see the different type of category:

Data warehouse testing is categorized into four different types:

• New Data Warehouse Testing – New DW is built and verified from scratch. Getting input from customer and new data warehouse is build and verified with the help of ETL tools.
• Migration Testing – Project customer will have an existing DW and the ETL is performing the job. However they are looking for new ETL tool in order to improve efficiency.
• Change Request – In this type of project new data is added from different sources to an existing DW. However customer needs to change their existing business rule or they might integrate the new rule.
• Report Testing – Report is main key factor for any Data Warehouse project. Report must be tested by validating layout, data in the report and calculation.

Lot of testers, misunderstanding that the Database testing and Data Warehouse is similar while the fact is that both hold different direction.

Now we can see how Database testing differs from Data Ware house testing

• Database testing is done using smaller scale of data whereas Data ware house testing is done large volume of data.
• Database testing is normally using with OLTP (Online transaction processing) type of databases whereas data warehouse testing normally with OLAP (online analytical processing) databases.
• Database testing is consistently injected from uniform/Single sources whereas Data ware house testing inconsistently injected the data from different/Multiple sources.
• Database testing performs only CRUD (Create, read, update and delete) operation whereas Data Ware house testing performs only SELECT operation (Read Only).
• Database testing used Normalized databases whereas Data ware house testing used the DE Normalized databases.
• Database uses only .sql files (Based on the DB platform) whereas Data Ware house testing can use any type of file (.txt, .xlsx) to import data. 

List of ETL Testing Techniques that are treated when we testing the ETL package:

• Make sure that Data transformed correctly to Target table.
• Make sure data is loaded into the DW table without any data loss and truncation.
• Make sure that Rejects the invalid data if any and generates the reports/Logs for invalid data for reference.
• Make sure that data is loaded within expected time frames. (Performance)

List of points to be considered as essential when we testing the ETL package:

• Data transformation from source to destination works 
• Expected data is added in target system
• No of Records count should match.
• Error Log should create for rejected data
• NULL Value fields 
• Duplicate data is not loaded
• Focus is to ensure Data validation and integrity mainly.
• Verify that data getting loaded without truncation.

ETL Testing Process:

All testing types like unit testing, integration testing, System testing and UAT testing lies under verification and validation process as same like ETL testing also lies under verification and validation process.

Now we can see the process of Data Warehouse /ETL process.

•  Business and requirement understanding.
• Validating the requirement.
• Estimate for testing.
• Preparing test plan based on test estimation and Requirement.
• Preparing Use cases (Scenarios) and test case as per the inputs.
• Once all the test cases are freezed and approved, testing team move forward to execute the test cases.
• Execution is performed once the exit criteria are met.
• Finally, prepared the completed summary report and Closure process is done.

ETL Testing Challenges which faced in my project: 

ETL testing is quite different from other testing. There are many challenges we faced while performing data warehouse testing.
Here I have listed few ETL challenges which most of the testers faced on the project:

• Out of Memory exception occurs while executing ETL.
• Taking more memory while writing huge volume of Data into the table.
• Package goes to No response state when no memory to execute the package.e
• Incompatible and duplicate data.
• Loss of data during ETL process
• Unfreeze data format
• Don’t have access to run the ETL jobs.
• Volume of data is huge. 

Dareware House Testing Observation

I got chance to do the Dataware house testing  (BI) in my company.

In Data Integrity testing, I have used below approach : 

I have used VBExcel comparer (Macro) to validate the column values or data between source and destination.
It is useful for me to validate/compare large number of records in both source and destination.
 

Few observations (Issues) found while doing the Performance testing,

While executing the ETL with huge volume of date, Out of Memory exception occurred. For BI Testing, mainly focused to avoid the memory related issues...

 After some extent of analysis to provide the solution that,

1. Release the the memory object through garbage collector after inserting the large volume of data. So Memory level has maintained at the same level if inserting huge data to the database.
2. Avoid to use more connection to open and close. Since every connection takes memory to initialization. So that is also taking some memory. Reduce the connections.

Note : I have used SQL Monitor 3.2 to analysis the performance of the server. It has been used generating various reports like CPU Usage, Disk Read, Disk Write, Disk Transfer, SQL Server Total Memory...etc.