Tuesday, March 26, 2013

EXEC vs SP_EXECUTESQL

 

We all know Stored Procedure are better than having any dynamic SQL Statement,   There are times where dynamic SQL Statement is unavoidable.  In such cases we can make use of dynamic SQL, there are two options to run dynamic SQL.

EXEC SP_ExecuteSQL
Every time the Execution Plan is created for different parameters Reuses the execution plan
May introduce SQL injection Parameters are safe from SQL Injection
Type casting is required Type casting not required
slow as every time the execution plan needs to be created faster as execution plan is reused.
Non-parameterized Parameterized

 

Make use of SP_ExecuteSQL over EXEC.

Monday, March 25, 2013

Managing Fact & Dimension

Recently I got a question from our team member on how are we managing, delta data merging to master data and how do we do synch with source data periodically.  It is very big topic, but How do I put them all in 15 minute discussion…The following example I showed to him and he got the insight into it.

The following script, which talks about common scenario for most of the projects.  Hope any one who is get starting with Warehouse projects.

-------------------------------------
-- Assume scenario where you get source data as
-- single flat table, you need to extract the masters
-- create surrogate keys and handle INSERT / UPDATE for repeated runs.

-- Source Table: tblSource
-- Target Table: dimPlan, factTransaction

-- Temp tables: wrkSource, wrkPlan

-------------------------------------

----------------
-- SOURCE TABLE
----------------
IF OBJECT_ID('tblSource') IS NOT NULL DROP TABLE tblSource
CREATE TABLE tblSource
(
Id INT,
Name VARCHAR(100),
PlanCode CHAR(2),
PlanDesc VARCHAR(200),
)


----------------
-- TEMP TABLE
----------------
IF OBJECT_ID('wrkSource') IS NOT NULL DROP TABLE wrkSource
-- 1. Transaction
CREATE TABLE wrkSource
(
Id INT,
Name VARCHAR(100),
PlanKey INT
)

IF OBJECT_ID('wrkPlan') IS NOT NULL DROP TABLE wrkPlan
-- 2. Master
CREATE TABLE wrkPlan
(
PlanCode CHAR(2),
PlanDesc VARCHAR(200)
)


----------------
-- TARGET TABLE
----------------
IF OBJECT_ID('dimPlan') IS NOT NULL DROP TABLE dimPlan
-- 1. Master
CREATE TABLE dimPlan
(
Id INT IDENTITY(1,1),
PlanCode CHAR(2),
PlanDesc VARCHAR(200)
)

IF OBJECT_ID('factTransaction') IS NOT NULL DROP TABLE factTransaction
-- 2. Transaction
CREATE TABLE factTransaction
(
Id INT,
Name VARCHAR(100),
PlanKey INT
)


----------------
-- MERGE SP
----------------
DROP PROC MG
GO
CREATE PROC MG
AS
BEGIN
-- Extract Master
TRUNCATE TABLE wrkPlan
INSERT INTO wrkPlan(PlanCode, PlanDesc)
SELECT PlanCode, PlanDesc
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY PlanCode ORDER BY PlanDesc) RNO, PlanCode, PlanDesc
FROM tblSource) S
WHERE RNO =1

-- Merge master
;MERGE dimPlan AS T
USING wrkPlan AS S
ON T.PlanCode = S.PlanCode
WHEN MATCHED THEN UPDATE SET T.PlanDesc = S.PlanDesc
WHEN NOT MATCHED THEN INSERT (PlanCode, PlanDesc) VALUES(S.PlanCode, S.PlanDesc);


-- Merge master --- IF TYPE 2 ON INCLUDE DESC
--;MERGE dimPlan AS T
--USING wrkPlan AS S
--ON T.PlanCode = S.PlanCode
--WHEN MATCHED THEN UPDATE SET T.PlanDesc = S.PlanDesc
--WHEN NOT MATCHED THEN INSERT (PlanCode, PlanDesc) VALUES(S.PlanCode, S.PlanDesc);

-- Extract transaction
TRUNCATE TABLE wrkSource
INSERT INTO wrkSource (id,Name, PlanKey)
SELECT s.id, S.Name, P.Id
FROM tblSource S
JOIN dimPlan P ON S.PlanCode = P.PlanCode


-- Merge transaction
MERGE factTransaction AS T
USING wrkSource AS S
ON T.ID = S.ID
WHEN MATCHED THEN UPDATE SET id = s.id, Name = S.Name, PlanKey = S.PlanKey
WHEN NOT MATCHED THEN INSERT (id,NAME, plankey) VALUES (s.ID,S.NAME, s.plankey);
END
go



----------------
-- UNIT TEST
----------------

SELECT 'Unit test 1. New data' UNIT_TEST
INSERT INTO tblSource VALUES
(1,'RAM','aa','aaaaaaa'),
(2,'RAMA','aa','aaaaaaa'),
(3,'RAMAN','bb','bbbbbbb'),
(4,'RAMU','bb','bbbbbbb')

go

SELECT * FROM tblSource
EXEC MG
SELECT * FROM factTransaction
SELECT * FROM dimPlan

SELECT 'Unit test 2. Data change' UNIT_TEST
INSERT INTO tblSource VALUES (5,'RAddM','aa','aaaaaaa')
UPDATE tblSource SET NAME = 'UPDATED...', PlanCode = 'BB', PlanDesc = 'aaUPDATED TO BBBBB' WHERE ID =2

SELECT * FROM tblSource
EXEC MG
SELECT * FROM factTransaction
SELECT * FROM dimPlan

Teams PowerShell

 The PowerShell helps to get quick meta data around Teams. Install-Module -Name MicrosoftTeams Connect-MicrosoftTeams Get-TeamAllChannel...