User Stories....

Recently I am getting used to term "User Stories". I found this link useful. A good user story should also have a good acceptance test case with it. It need to have sufficient information. The objective of user stories is to connect the dots and identify and develop required functionality for end user. It's not that easy as I write as one liner :).

If you would like to have a good read on Bad or Good Agile, Read the link. Very good excerpts from the link. Take away from this article is

• Take things like unit testing, design documents and code reviews more seriously
• Bad Agile focuses on dates in the worst possible way: short cycles, quick deliverables, frequent estimates and re-estimates
• Encourage Peer Reviews and be open to respect others views and learn from peers

Having Domain and Technical workshop at the early phase of project would always help stakeholders and IT team understand requirements/functionality clearly.

Scrum Checklist
Kanban Vs SCRUM
Scrum-ban

Happy Reading.....

Deep Dive into Reverse Logistics

I registered myself to http://www.reverselogisticstrends.com/. You can access free downloads sections for good articles. Articles Reverse Logistics = Service Logistics, Reverse Logistics Checklist are very good reads.

Reverse Logistics Checklist provides clear directions while dealing with Customers
I am summarizing the checklist below
Prereturn - Clear Packaging Instructions, Warranty lookup from site, Warranty Registration directly based on customer registering the product, Self Test - Providing Diagnostic software for customer to test at this place
Return Request - Self ServiceRequest Creation, Request Acknowledgement through email, Call centre having visibility of stock available for replacement
Return Processing - Status lookup phone/website, Status update emails, Customer Survey
Having Joint Metrics defined with partners and tracking them on a timely manner would also help to focus on right areas of improvement.

If you want to know features of a Reverse Logistics Software refer the brochure for
BacTracs-Reverse Logistics Management System. This would give you good insight on Reverse Logistics implementation. Warranty Life Cycle Management

How many times my query has been executed ?

Thanks to Balmukund for his help. I wanted to know how many times a query has been executed from its query plan. Here is quick steps for this.
Step 1 - Create necessary tables for demo
use tempdb
CREATE TABLE DBO.TESTTable
(
    Id INT Identity(10,10),
    Name VARCHAR(20)
)

INSERT INTO DBO.TESTTable(Name)
Values ('Testvalue')
GO 50
Step 2 - Run below query
SELECT * FROM TESTTable WHERE Id = 20

Step 3 - I would like to know the plan for this query - Below DMV query would help

SELECT *
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE TEXT LIKE '%TestTable%'

Step 4 - Fetch the Plan Handle from above step. Use the DMV sys.dm_exec_query_stats to find execution count of that plan based on plan handle
SELECT execution_count,* FROM sys.dm_exec_query_stats where plan_handle = 0x0600020078374D0040213585000000000000000000000000
 
This execution count would tell you how many times this plan has been executed. To check plan is cached  query cached_plans DMV can be used
SELECT * FROM sys.dm_exec_cached_plans where plan_handle = 0x0600020078374D0040213585000000000000000000000000
 
Hope it was useful. Have a Good Day.

Non-repeatable read and Phantom read

While trying to learn on concurrency problems I wanted to know difference between Non-repeatable read and Phantom read. The following post in MSDN is useful.

Nonrepeatable read: If somebody performed UPDATE or DELETE of any of the rows you read earlier.
Phantom: If anybody INSERTed a row within the range you had for an earlier query (i.e., you see new rows).

A Quick Experiment on Phantom Reads

Step 1 - Created a Test Table and Populated few records

USE TestDatabase
IF OBJECT_ID ('TestIsolation') IS NOT NULL DROP TABLE TestIsolation
CREATE TABLE TestIsolation
(Id INT Identity (1,1) PRIMARY KEY CLUSTERED ,
Value CHAR(20) )

DECLARE @I INT
SET @I = 0
WHILE 1=1
BEGIN
     INSERT INTO TestIsolation(Value)
     VALUES ('Value' + CONVERT(VARCHAR(20), @I))
     SET @I = @I+1
     IF @I > 100
     BREAK;
END

Step 2-Open a Session and Run Below Command

USE TestDatabase
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
     SELECT * FROM TestIsolation
    WAITFOR DELAY '00:00:05'
    SELECT * FROM TestIsolation
ROLLBACK

Step 3 - Parallelly Open another session and run below command

USE TestDatabase
GO
BEGIN TRAN
     INSERT INTO TESTIsolation(VALUE)
     VALUES ('New Entry')
COMMIT

Step 4 - Output would be as below for Step 2. As you see newly inserted row is reflected in second select query (103 rows affected)

Note: For Step2 if You change isolation level to Serializable, You will see same number of affected rows  for the above case. A serializable scan acquires a key range lock which prevents the insertion of any new rows anywhere within the range

Another Quick Experiment on Nonrepeatable Reads

Nonrepeatable Read - Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time

Step 1 - Run the below query in one session

USE TestDatabase

GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
     SELECT * FROM TestIsolation
     WAITFOR DELAY '00:00:05'
     SELECT * FROM TestIsolation
ROLLBACK

Step 2- When Step1 executes run Step2

USE TestDatabase
GO
BEGIN TRAN
    Update TESTIsolation
    SET VALUE = 'Modified Value'
    WHERE ID = 100
COMMIT

Step 3 - Output for Step 1

For Id 100 Data is modified as you can see above.

For Read-Committed Isolation level Advantages are

• Only committed changes are visible
• It acquires short lived share locks on a row by row basis
• The duration of these share locks is just long enough to read and process each row 

Disadvantages of Read-Committed Isolation Level

• Nonrepeatable Read - Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time
• Phantom reads occur when an insert action is performed against a row that belongs to a range of rows being read by a transaction.

Happy Learning!!!

SQL Tip for the Day

Tip 1 - Difference between Table Variables Vs Temporary Tables Vs Global Temporary Tables

Global Temporary Tables
1. Global temporary tables are available to all SQL Server connections.
2. All Sessions can access it
3. Same As Temp Tables Add ## before table name while declaring it

Table Variables
1. No Statistics associated with them
2. Do not participate in locking/transactions
3. Operations are not logged
4. Indexs cannot be created for table variables
5. They have scope associated only with current stored procedure it would be visible.
6. The only place where you can define columns to a table variable is in the declaration. ALTER TABLE not supported for Table Variable

Temp Tables
1. Statistics associated with them
2. Participate in locking/transactions
3. Operations are Logged
4. Add a single # before table name while declaring it
5. ALTER TABLE supported for Temp Tables
Reference - Link1, Link2, Link3, Link4, Good One - SangeethaShekar Blog
TempDB:: Table variable vs local temporary table


Tip 2 - When Clustered Index Columns are modified are Non-Clustured Indexes Re-built
Yes They are rebuilt. When Clustered Index column is re-ordered/arranged based on newly added/modified columns. Supporting no-clustered indexes would be as well rebuilt
Reference - Link1

Tip 3 - What resides in tempdb

• Inserted and deleted tables actually stored in tempdb
• User Created temp tables
• Online Index operation uses tempdb
• Worktables group by, order by (Ex- ORDER BY clause references columns not covered by any indexes, the relational engine may need to generate a worktable)
• MARS (Multiple Active Result Sets)
• In SQL 2005 Onwards When you use Row versioning data (Readcommitted, Snapshot Isolation level) is stored in tempdb

Happy Learning!!!

SQL Tip of the day

Tip 1
I need only Date part from date time. Different formats and examples

SELECT GETDATE()
SELECT CONVERT(char(20), GETDATE(), 101) --mm/dd/yyyy
SELECT CONVERT(char(20), GETDATE(), 1) --mm/dd/yy
SELECT CONVERT(char(20), GETDATE(), 0) --Month date yyyy hh:miAM (this is the default style)
SELECT CONVERT(char(20), GETDATE(), 1) --mm/dd/yy
SELECT CONVERT(char(20), GETDATE(), 112) --yyyymmdd
Tip 2
I want to group data based on month, year and day

SELECT GETDATE()
SELECT YEAR(GETDATE()),MONTH(GETDATE()), DAY(GETDATE())
Tip 3
I want to group data based on Hour, Minute and Second

SELECT GETDATE()
SELECT DATEPART(HOUR,GETDATE()), DATEPART(MINUTE,GETDATE()), DATEPART(SECOND,GETDATE())
Tip 4
COALESCE Function - Returns the first nonnull expression among its arguments

SELECT COALESCE(NULL+'Test',NULL,'Test1')--Test1
SELECT COALESCE('Test2'+'Test',NULL,'Test1')--Test2Test
SELECT COALESCE('Test2'+NULL,NULL,'Test1')--Test1
Tip 5
What is output for SELECT 'TEST'+ NULL
 
Cancatenation of anything to NULL will give only NULL output. Using ISNULL would fix it.
SELECT 'TEST'+ ISNULL(NULL,'')
Tip 6
Explore more on Datatype Date, Time, DateTime, SmallDateTime and Choose datatype based on need. This would help reduce space and appropriate use of datatypes.

IF OBJECT_ID ('TEST') IS NOT NULL DROP TABLE TEST
CREATE TABLE TEST
(
DateCol             Date,
TimeCol            Time,
DateTimeCol     DateTime,
SmallDTCol        SmallDateTime
)

INSERT INTO TEST (DateCol,TimeCol,DateTimeCol,SmallDTCol)
VALUES(GETDATE(), GETDATE(),GETDATE(),GETDATE())

SELECT * FROM TEST

What is Reverse Logistics.....

Reverse logistics has been defined as “... the term most often used to refer to the role of logistics in product returns, source reduction,recycling, materials substitution, reuse of materials,waste disposal, and refurbishing, repair and remanufacturing.” (Link)

A very good comparision on forward logistics vs reverse logistics presented below. Source Reverse Logistics Association.

Do find time to check Reverse Logistics Wiki
Reverse Logistics Framework as provided in Wiki. This kind of completely covers End-to-End Reverse Logistics Operations.

Very good white paper from UPS on ReverseLogistics
Key Learnings as Captured in paper as Summary

• Customer retention/satisfaction - Post Purchase Support for Repair is very important for better customer satisfaction
• Container reuse
• Recycling programs (Transport packaging)
• Damaged material returns
• Asset recovery/restock
• Downstream excess inventory (Seasonality)
• Hazardous material programs
• Obsolete equipment disposition
• Recalls

A good Example is also provided in the paper.
Possible options for reclaimed product

• Refurbish (Improve product beyond original specs)
• Recondition (Return product to original specs)
• Salvage (Separate components for reuse)
• Repair (Prepare for sale as a used product)
• Sell to 3rd Party
• Recycle
• Discard/Liquidation (Landfill)

The confluence of blockchain and IoT in Supply Chain can improve the efficiency of the operations, as per @EqualOcean's research. Link > https://t.co/5nWv9EIqNh @antgrasso via @LindaGrass0 #SupplyChain #blockchain #SmartContracts #IoT #Tech #DigitalTransformation pic.twitter.com/bDmPz7Njz0

— C-Suite Tech Point (@CsuiteTechPoint) May 25, 2020

Other Good Reads you may like
How to Develop A Reverse Logistics Strategy
Improve Your Business Applications
Advanced Exchange Service Model and the Secret of the ‘Black Hole’
Analysis of Reverse Logistics
Reverse Logistics Metrics (Customer Satisfaction, Financial Performance, Manufacturing (or Returns Processing and Refurbishment), Transportation and Warehousing)
Supply Chain Metrics

Biztalk Recipe No 4

Today we would look at publishing message to MessageBox and subscribing and transforming it.

1. Receive and Post Message M1 in MessageBox
2. Subscribe and Transform the message M1 into M2
3. Send the message M2 to another location

Step 1 - Create an Empty Biztalk Project
Step 2 - Create a New Message M1 Schema (ReceiveSchema)

Insert ChildFieldElement Id and Name











Step 3 - Create a New Message M2 Schema (Transform Schema)











Step 4 Add an Empty Biztalk Orchestration
STEP 5 Add Message1 Based on ReceiveSchema, Add Message2 Based on TransformSchema

Step 6 - Add a ReceiveShape, Set Activate-True, Message -> Message1

Step 7 - Specify Direct Binding for Port. Post Message in MessageBox

Step 8 - Add a Transform Shape, Under Properties of TransformShape ->Input Messages, SpecifySource Message_1 and Destination Message_2 and Click on OK

Step 9 - Biztalk Mapper would be launched. Map Id to Id & Name to Comments

Step 10 - Now go back to orchestration and add send shape and Assign Message_2 for Send Shape and bind port

Step 11 - Orchestration

Step 12 - Under Project->Biztalk Server Project -> Specify Application Name under Deployment Tab, Under Signing Tab sign the assembly

Step 13 Build and Deploy the application

Step 14 Start->Run-> btsmmc.msc
Step 15 - Goto Application. Create a Receive Location and a Send Port. Bind the orchestration and start it

Post a Message in Receive Location, It would be transformed and the message would be available in the SendPort Location Configured. Hope this article helps. Day by day would target to learn little by little.....Happy Learning!!!!

More Reads
BizTalk: Instance Subscription and Convoys: Details
BizTalk and SQL: Alternatives to the SQL receive adapter. Using Msmq to receive SQL data
BizTalk: Suspend shape and Convoy
BizTalk: Sample: Context routing and Throttling with orchestration

Learning on Blocking

One more learning added to our list while participating in a discussion.
Scenario -

• Read Committed Snapshot Isolation Enabled Database
• Transaction1 - Begin a Transaction for - Truncate Table, Do not Commit it
• Transaction2 - Begin a Transaction for - Select * from Table
• Transaction2 is blocked by Transaction1. We will try the example and see it why.

 Getting Ready with Required Steps and Data for it

• Enable ReadCommitted Snapshot Isolation
• Create a Table
• Insert Records

STEP 1
ALTER DATABASE TestDatabase
SET READ_COMMITTED_SNAPSHOT ON

STEP 2

Verify it is enabled by below query
select is_read_committed_snapshot_on, snapshot_isolation_state, snapshot_isolation_state_desc,
sys.databases.[name] from sys.databases

STEP 3
Create TestTable and Populate Data

CREATE TABLE TestTable

(
Number INT IDENTITY(1,1),
Name VARCHAR(20) NOT NULL
)
STEP 4
Populate Data in the tables

DECLARE @I INT

SET @I = 100
WHILE 1 = 1
BEGIN
    SET @I = @I + 1
    INSERT INTO TestTable(Name)
    VALUES (CONVERT(CHAR(6),@I)+'Test')
    IF @I=10000
    BREAK;
END

STEP 5 (Transaction 1)

Now we are set to experiment now. Open Transaction to trancate table TestTable
BEGIN TRAN
TRUNCATE TABLE TestTable

STEP 6 (Transaction 2)

Open Another Transaction, Transaction2
use TestDatabase
BEGIN TRAN
SELECT * FROM TestTable
 
STEP 7 (Here is Blocking, It has finally Arrived as per the need of this blog post). Detect Blocking with below Query
SELECT * FROM sys.sysprocesses WHERE blocked <> 0
Run Query as provided in link

STEP 8 - Find the locks associated with the transaction
Select * from sys.dm_tran_locks Where request_session_id = 52
Sch-M LOCK GRANT

Select * from sys.dm_tran_locks Where request_session_id = 53

Sch-S LOCK WAIT

For Session 52 It Already Holds Sch-M Lock.

STEP 9 - Reason Why and What is Learning Now for us

A Very Important Idea in MSDN Link

For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock.

Now that we learnt SCH-M Lock is the reason for blocking. Now How do i Fix it.
This is the learning of this Post. Replacing Delete with Truncate Solved the issue.

STEP 10 - Next Step
Transaction1
BEGIN TRAN
DELETE FROM TestTable

Transaction2
BEGIN TRAN
SELECT * FROM TestTable
Since, ReadCommitted Transaction is enabled you will see last committed data.

STEP 11 - Further Analysis

DBCC INPUTBUFFER(52)
--DELETE FROM TestTable
Select * from sys.dm_tran_locks Where request_session_id = 52
It has got X Lock

SELECT object_name(resource_associated_entity_id)

SELECT OBJECT_NAME(2105058535)
--TestTable
DBCC INPUTBUFFER(53)
--SELECT * FROM TestTable
Select * from sys.dm_tran_locks Where request_session_id = 53
It has got S Lock

STEP 12 - Below Query in Link is also good. Its worth to take a look at Lock Compatability Matrix
The schema stability (Sch-S) lock is compatible with all lock modes except the schema modification (Sch-M) lock mode. The Sch-M lock is incompatible with all lock modes. Lock Compatability


More Reads
Why do I get blocking when I use Read Uncommitted isolation level or use NOLOCK hint?


Happy Reading.......Happy Learning.....Thanks Roji and Vaibhav for the Discussion.

Third Biztalk Experiment

Third experiment is for correlation concept. Correlation is a process of associating an incoming message with the appropriate instance of an orchestration (link)

Understanding is – “Correlation is a process of relating an incoming message based on fields promoted and stored for its related messages, u can relate two messages based on some common property” .

There are three correlated messages exchange patterns:

• Traditional handshake
• Sequential convoy
• Parallel convoy

I am going to try simple Traditional handshake scenario.
Step 1. Created a New Biztalk Project
Step 2. Now Input XML Schema is Created

Step 3. Add a child Field Element as mentioned below

Step 4. Add element OfferId and Promote it. Once you promote it promoted schema would be created


Step 5. Create Output XML Schema and promote OfferId Child Field Element
 

Step 6. Create an Empty Biztalk Orchestration and Create two messages based on Input and Output Schema

Step 7. Create Message2 based on Output Schema

Step 8. The steps we will create in orchestration and our objective is listed below 

• ReceiveShape – Get Input based on input XML schema, Set Activate –True. (Message1)
• Send Shape – Send Input to a file share and enable correlation (Message1)
• ReceiveShape – Receive the output file and follow correlation (Message2)
• SendShape – Send the correlated message to destination file share (Message2)

This is all we are going to achieve in next listed steps. Created Receive and Send shape for Input Schema based message. Now we are going to Enable Correlation Type and Define Correlation Set

Step 9.  Create Correlation Type based on the promoted property offerid

Step 10. Create a Correlation Set based on Correlation Type

Step 11. Initialize Correlation Set for the Send Port

Step 12. Now add a Receive port and set follow correlation property

Step 13. Orchestration will look like below

Step 14. Build Application, Signin with Key, Deploy it

Step 15. Bind Ports and Assign Host. I have missed it in the picture

Step 16. Create sample files and drop in the source share with input xml.
Step 17. Create output file in the next receive share. Only matching offerid should reach the final share configured in the last send shape.

I was able to learn it with guidance from my team. Happy Reading....