ETL Testing

ETL - Extract Transform and Load
Extract - Raw data is copied or exported from source locations to a staging area during data extraction data mangement.

Transform - In this stage, raw data undergoes data processing. In this step, the data is processed and consolidated for its intended analytical use case.

Load - The converted data is moved from the staging area into the target data warehouse in this final stage.

An ETL pipeline (or data pipeline) is the system that allows ETL activities to take place. Data pipelines are a collection of tools and actions used to transfer data from one system with ites own manner of data storage and processing to another system where it might be kept and managed differently.

OLAP - Online Analytical Processing- is an approach to answer multi-dimensional analytical queries. Operates on Data Warehouse.

OLTP - Online Transaction Processing - facilitates and manages transaction oriented applications. Operates on Database.
CRUD - Create, Read, Update and Delete Operations are performed on the Database
OLTP Architecture - Database Server, Application Server, User Interface

OLTP (Online Transacti onal Processing)	OLAP (Online Analytical Processing)
Current data	Historical data
Day-to-day Transactional operations	Data analysis and decision making
Normalised data structures	Star schema or Snow flake Schema
Simple Queries	Complex Queries
Requires Fast Response	Can have Longer Response Times
Used by Front Line Employees, Managers	Analyst, Executives and Decision Makers
Data in OLTP is processed	Data in OLAP is processed is periodically refreshed
Used by Front Line Employees	Analyst, Executives and Decision Makers
Oracle, MySQL, SQL Server, DB2	Tableau, Power BI and SAP

Star Schema
A star schema is a type of data modeling technique used in data warehousing to represent data in a structured way.
The fact table in a star schema contains the measures or metric
The dimension table in a star schema contain the descriptive
Snowflake Schema

API Testing

API - Application Programming Interface.
They are basically collection of procedures and functions which allow us to communicate between two applications or libraries.
REST API - It is REpresentational State Transfer API.

Excel Functions

XMATCH
XMATCH function searches for a specified item in an array or range of cells, and then returns the item's relative position.
The XMATCH function returns the relative position of an item in an array or range of cells. =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

PROPER
PROPER function is a Text function, and its purpose is to capitalize the first letter in each word of a text value that will remain in lowercase or convert to lowercase.

XLOOKUP function: Use =XLOOKUP("last", range, return_array) to get the value of the last non-empty cell1.

LOOKUP function: Use =LOOKUP("last", range, return_array) to find the last non-blank cell in a row2.

COUNTA and OFFSET functions: Use =COUNTA(range) to count the number of non-empty cells, and =OFFSET(A1, COUNTA(A:A)-1, 0) to get the last non-blank cell34.

ADDRESS and LOOKUP functions: Use =ADDRESS(LOOKUP(2, 1/(M:M<>""), ROW(M:M)), 13) to return the address of the last non-blank cell in column M

Basic Functions & Operations		SUM & COUNT
A1+B1	Addition	SUM(A1: A10)	Sum of a range
A1-B1	Subtraction	AVERAGE(A1:A10)	Average of a range
A1*B1	Multiplication	COUNT(A1:A10)	Count of numeric entries
A1/B1	Division	COUNTIF(A1:A10, ">10")	Count cells that meet a condition
A1*2	Exponentation	COUNTIFS	Counts the number of cells in a range where one or more conditionsa are met
MOD(A1, B1)	Remainder of division	SUMIF(A1: A10, ">10", B1: B10)	Sum based on a condition
Logical Functions		MIN(A1:A10)	Calculates the smallest number in a range
IF(A1> 10, "Yes", "No")	Conditional Statement	MEDIAN	Finds the median value in a group of values
AND(A1> 0, B1 < 5)	Logical AND	MAX	Calculates the highest number a range
OR(A1 > 10, B1 < 5)	Logical OR	Text Functions
NOT(A1 = 10)	Logical NOT	CONCATENATE(A1, "", B1)	Join text strings
IFERROR(A1/B1, "Error")	Handle errors gracefully	TEXT(A1, "0.00")	Format number as text
Date & Time		LEFT(A1, 3)	Extract Left Most characters
Today()	Current date	RIGHT(A1, 3)	Extract Right Most characters
NOW()	Currnet date and time	MID(A1, 2, 3)	Extract chracters from the middle
Lookup Functions		LEN(A1)	Length of a Text String
VLOOKUP	Vertifically searches for a value in a table	FIND("text", A1)	Find position of text
INDEX	A lookup function that searches a table both vertically and horizontally	SUBSTITUTE(A1, "old", "new")	Replace text in a string
XLOOKUP	An updated version of VLOOKUP available in newer Excel versions	UPPER	Converts a string of text to all uppercase
HLOOKUP	Horizontally searches for a value in a table	LOWER	Converts a string of text to all lowercase
MATCH	Returns the location of a value in a sequence	VALUE	Converts a text to a value
OFFSET	Moves the cell reference by the number of given rows and/or columns

Java Annotations:
@Entity
@Author
@Override - annotation informs the compiler that the element is meant to override an element declared in a superclass.
@SuppressWarnings - annotation tells the compiler to suppress specific warnings that it would otherwise generate.
@Deprecated - annotation indicates that the marked element is deprecated and should no longer be used.


Lambda Expressions: Lambda can be used with certain type of interfaces.
parameter -> expression
(parameter1, parameter2) -> expression
(parameter1, parameter2) -> {expression}


Any interface with a SAM(Single Abstract Method) is a functional interface, and its implementation may be treated as lambda expressions. When a method inside an interface does not have implementation, it is called abstract method. When an interface has one abstract method, the interface is called Functional Interface. Abstract method in the interface uses the lambda expressions. Interface is preceded with Functional Interface Tag
Example Interface:
@Functional Interface
public Interface printable {
void print();
}
For Loop using Lambda Expressions on Array List:
List names = Arrays.asList("John", "Freddy", "Samuel");
names.forEach(name -> System.out.println("Hello, " + name));
For Loop using Lambda Expressions on Hash Map:
Map ages = new HashMap<>();
ages.put("John", 25);
ages.put("Freddy", 24);
ages.put("Samuel", 30);
ages.forEach((name, age) -> System.out.println(name + " is " + age + " years old"));
Filter list using the Stream API and keep only the names that start with the letter “A”.
List names = Arrays.asList("Angela", "Aaron", "Bob", "Claire", "David");
List namesWithA = names.stream()
.filter(name -> name.startsWith("A"))
.collect(Collectors.toList());
System.out.println(namesWithA.get(0).toLowerCase());
System.out.println(namesWithA.get(1).toLowerCase());
Replace All in List: List names = Arrays.asList("bob", "josh", "megan");
names.replaceAll(name -> name.toUpperCase());
names.forEach(name -> System.out.println("Hello, " + name));
Replace All in HashMap: Map salaries = new HashMap<>();
salaries.put("John", 40000);
salaries.put("Freddy", 30000);
salaries.put("Samuel", 50000);
salaries.replaceAll((name, oldValue) ->
name.equals("Freddy") ? oldValue : oldValue + 10000);
salaries.forEach((name, value) -> System.out.println(name + " is " + value + " years old"));
Calculator Operations using Lambda Expressions:
package org.example;
public class Main {
@FunctionalInterface
interface Operation {
int calculate(int x);
}
public static void main(String[] args) {
int a = 5;
Operation square = (int x) -> x * x;
Operation add = (int x) -> x + x;
Operation subtract = (int x) -> x - x;
int ans = square.calculate(a);
int addedvalue = add.calculate(a);
System.out.println(ans);
System.out.println(addedvalue);
System.out.println(subtract.calculate(6));
}
}

Working with Batch Files

Synchronise List of files from Linux Server to Window using Batch file

@echo off
"C:\Users\mmendu\AppData\Local\Programs\WinSCP\WinSCP.com" ^
/ini=nul ^
/command ^
"open sftp://a_MMendu:P "synchronize -filemask=S_EDI_202202*.TXT local C:\Users\mmendu\Documents\UFT /app/retek/rtkstst/transport/extract" ^
set WINSCP_RESULT=%ERRORLEVEL%
if %WINSCP_RESULT% equ 0 (
echo Success
) else (
echo Error
)

Get the files by Masking in synchorize command

synchronize -filemask=RMS_EDI_202202*.TXT local "+FilesFolderpath+" /app/retek/rtkstst/transport/extract"

VBScript - Coding Standards

Constant Names

Constant Names 

Names of constants should be meaningful noun or noun phrases with all letters capitalized. Multiple words were separated using the underscore (_) character. 


Example: MY_VARIABLE

Function Names

Function Names (or) Module names should be meaningful words or phrases that describe the abstraction of the module.  Use nouns for object abstractions and verbs for functional abstractions.  Module names should be without spaces, in mixed case, with the first letter uppercase, and the first letter of each subsequent word capitalized.  

Boolean Function Names

Functions that return the results of a test of a Boolean condition T should be named IsT, HasT, or CanT.

Variable Names

• Append computation qualifiers (Avg, Sum, Min, Max, Index) to the end of a variable name where appropriate.

• Boolean variable names should contain Is which implies Yes/No or True/False values, such as fileIsFound.

• Do not use literal numbers or literal strings, such as For i = 1 To 7. Instead, use named constants, such as For i = 1 To NUM_DAYS_IN_WEEK for ease of maintenance and understanding.

Scripting Techniques

There are 5 types of scripting techniques.
1. Linear
2. Structured
3. Shared
4. Data Driven
5. Keyword Driven