DW - Full load & Incremental or Refresh load

Data is the business asset which is used to making the right business decision on the right time. It is very important to design our database correctly, up to whatever normal form we can bear. 
While OLAP cubes are still common across the BI technology marketplace, data warehousing has definitely seen growth as it is serving consumers in more modern and complete ways. In the current business world, every organization is spending lot of money on the BI technologies to quick access of the valuable data because they believe that data visualisation is the key of the success in making the right decisions at the right time.

So, it is become very important urgent or sometimes treated as do and die situation and need to apply very highly recommended approaches which should be based on the real cases.  We are trying to highlight the importance of using an incremental load and change data capture technique for loading data to the dashboards for the end users.
ETL tools such as SQL Server Warehouse Builder have the ability to set table loading to insert/update that will support both full load and incremental load with the use of the same ETL routines. The key to supporting this,  staging tables are created in the staging layer to join to source tables based upon update or create dates of the record.

What is Incremental Load?
As we know that Data is very important factor for an organisation for making the right business decision on the right time. So, Incremental load is the best option and it is an widely used method to load data in data warehouses from the respective source systems. This technique is employed to perform faster load in less time utilizing less system resources. To understand the incremental load functionality, we can take an example for the daily product hierarchy sales chain where we can put the staging tables between destination and source.
In this scenario, staging tables always refreshed in the beginning of the ETL process and then after load from the source files after proper data transformation. In the last section of the ETL process, targeted tables are filled from the staging tables based on the following conditions -

1. If data is matched with the target table then matching data will be updated.
2. If data is not matched with the target tables then data will be treated as new and append into the target tables.

  

Incremental data loading is the most common approach to capture ongoing changes and keeping historical data on a predefined schedule.  In the incremental approach, the key to supporting this is a created table in the staging layer to join to source tables based upon update or create dates of the record.



What is Full Load?
In this technique, there is no staging area and always truncate the target tables before load all the data from the source raw data files. This technique is very time consuming and easily lost the information but capable to provide the guaranteed information. Full data loading also known as the fully refresh data loading also.

To know more, click on 

1. Data Warehouse Architecture and Multidimensional Model. 
2. Data Warehouse - Dimension tables.
3. Data Warehouse - Fact tables.
4. Data Warehouse - Conceptual Modeling.
5. Data Warehouse - Star schema.
6. Data Warehouse - Snowflake schema.
7. Data Warehouse - Fact constellations. 
8. Collaboration of OLTP and OLAP systems. 
9. Major differences between OLTP and OLAP. 
10. Data Warehouse - Multidimensional Cube 

SQL – Recursive Date with CTE

In the daily analytical process, we need to verify the daily prices for the particular stack. It’s become very difficult if we need to get the last two or more year price performance because we never know that when price is missing. To get the price missing date, we need to create the mirror calendar date for that date range. With the help of this mirror date calendar, we can easily find out the price missing date. To generate the Calendar Date, CTE (Common Table Expression) would be very useful due to its recursive nature.

To get the recursive date, we need two date parameters such as start date to end date where start date will introduced the starting date of the calendar and end date will introduced the last date of the calendar as given below:

--- Input parameters --- Start date of the calendar DECLARE @inpStartDate DATE=DATEADD(YY,-2,GETDATE()), --- End Date of the calendar @inpEndDate DATE=GETDATE() SELECT StartDate=@inpStartDate, EndDate=@inpEndDate StartDate EndDate 11/29/2013 11/29/2015

In our example, we are going to get the calendar date having DayDate, NameOfDay, NameOfMonth, YearOf as given below:

--- Input parameters --- Start date of the calendar DECLARE @inpStartDate DATE=DATEADD(YY,-2,GETDATE()), --- End Date of the calendar @inpEndDate DATE=GETDATE() SELECT StartDate=@inpStartDate, EndDate=@inpEndDate ---- Common Table Expression ;WITH GetCalander (DayDate, NameOfDay, NameOfMonth, YearOf) AS ( ---- Block 1 ---- This block will create the base of table to generate the ---- First record of the calendar SELECT DayDate=@inpStartDate, NameOfDay=DateName(DW,@inpStartDate), NameOfMonth=DateName(M,@inpStartDate), YearOf=YEAR(@inpStartDate) UNION ALL ---- Block 2 ---- This block will use the first block and ---- will add the 1 day in the current date of the GetCalander ---- This will produce a resultset till the end of the @inpEndDate SELECT DayDate=DateADD(D,1,DayDate), NameOfDay=DateName(DW,DateADD(D,1,DayDate)), NameOfMonth=DateName(M,DateADD(D,1,DayDate)), YearOf=YEAR(DateADD(D,1,DayDate)) FROM GetCalander WHERE DayDate<=@inpEndDate ) ---- Pull the calendar data SELECT * FROM GetCalander ---- Set to get all data OPTION (MAXRECURSION 0) ---- Calendar should have the folloing data till the end of the end date parameter DayDate NameOfDay NameOfMonth YearOf 11/29/2013 Friday November 2013 11/30/2013 Saturday November 2013 12/1/2013 Sunday December 2013 12/2/2013 Monday December 2013 12/3/2013 Tuesday December 2013 12/4/2013 Wednesday December 2013 12/5/2013 Thursday December 2013 12/6/2013 Friday December 2013 12/7/2013 Saturday December 2013 12/8/2013 Sunday December 2013 12/9/2013 Monday December 2013 12/10/2013 Tuesday December 2013 12/11/2013 Wednesday December 2013 12/12/2013 Thursday December 2013 12/13/2013 Friday December 2013 12/14/2013 Saturday December 2013 12/15/2013 Sunday December 2013

So, CTE is the best way to get the this kind of the data based on the some condition where we need to recursive functionality.

SQL - Event Notifications vs. SQL Trace

Event Notifications in SQL

This is also a very beautiful feature of SQL Server which was introduced with the release of SQL Server 2005. Event Notifications mechanism is used for tracking all events or changes happening at the database or instance level. Event Notifications convey the very same data as DDL triggers and occur on the very same events, but they are asynchronous and loosely coupled as SQL Trace.

SQL Tracing
Microsoft SQL Server provides Transact-SQL system stored procedures to create traces on an instance of the SQL Server Database Engine. These system stored procedures can be used from within our own applications to create traces manually, instead of using SQL Server Profiler. This allows us to write custom applications specific to the needs of our enterprise.

SQL – LEAD function

LEAD function was introduced in SQL Server 2012 in the group of analytics functions which is very helpful to make analytics in T-SQL a possibility and would add some value from a BI perspective. LEAD function is used for accessing subsequent (or columns from the subsequent row) rows along with the current row which could make certain operations which done in a multi-step fashion be more efficient.

The basic fundamental of LEAD function, accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2012. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.

How to use LEAD function

Lead function supports non-deterministic nature. Non-deterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same. By using below syntax, we can use this function where want to use.

LEAD (scalar_expression [,offset] [,default])     OVER ( [ partition_by_clause ] order_by_clause ) Arguments scalar_expression Within this parameter, we can specify a scalar expression or column name whose value from the subsequent row is to be returned. Scalar_expression cannot be an analytic function. Offset, default Within this parameter, we can specify an offset to access not only the next immediate row but any row after the current row. Its default value of 1 accesses the next immediate row whereas a value of 3 accesses the third row from the current row. OVER ( [ partition_by_clause ] order_by_clause) Partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. Order_by_clause determines the order of the data before the function is applied. Return Types The data type of the specified scalar_expression. NULL is returned if scalar_expression is nullable or default is set to NULL.

LEAD functions are flexible to let us specify the number of rows to move forward from the current row with the offset parameter. To better understand this analytical function, we can take an example to calculation daily performance of any stock where we need to calculate the current value divided by Next day’s value as shown below:

Performance= (Current Day Value/Next Day’s Value)-1

Now, we need to get the Next day’s value and LEAD function is capable to fulfill this requirement but first of all we need to know about our data table and position of the actual data into the table before using LEAD Function.

USE TEMPDB GO ---- Declare Table variable for Daily Stock Values DECLARE @TableStock Table ( StockId int, StockName Varchar(20), StockDate Date, StockValue real ) ---- Insert Values in the stock Table variable INSERT INTO @TableStock (StockId, StockName, StockDate, StockValue) VALUES (101, 'StockName S1', '2015-10-23', '546.56'), (101, 'StockName S1', '2015-10-22', '544.22'), (101, 'StockName S1', '2015-10-21', '543.50'), (101, 'StockName S1', '2015-10-20', '544.75'), (101, 'StockName S1', '2015-10-19', '543.78') ---- Values in the Table Variable SELECT StockId, StockName, StockDate, StockValue FROM @TableStock StockId StockName StockDate StockValue 101 StockName S1 10/23/2015 546.56 101 StockName S1 10/22/2015 544.22 101 StockName S1 10/21/2015 543.50 101 StockName S1 10/20/2015 544.75 101 StockName S1 10/19/2015 543.78

Get previous day’s value with the help of LAG Function as given below:

---- USE CTE to get the Next Day's Stock Value ;WITH CTE AS ( SELECT StockId, StockName, StockDate, StockValue, ---- LEAD Function to get Next Day's Value NextDayValue=LEAD(StockValue,1,0) Over (ORDER BY StockDate) FROM @TableStock ) ----- Values in CTE table SELECT StockId, StockName, StockDate, CurrentValue=StockValue, NextDayValue FROM CTE ORDER BY StockDate DESC; StockId StockName StockDate CurrentValue NextDayValue 101 StockName S1 10/23/2015 546.56 0.00 101 StockName S1 10/22/2015 544.22 546.56 101 StockName S1 10/21/2015 543.50 544.22 101 StockName S1 10/20/2015 544.75 543.50 101 StockName S1 10/19/2015 543.78 544.75

Get the Daily performance now as shown below:

---- USE CTE to get the Next Day's Stock Value ;WITH CTE AS ( SELECT StockId, StockName, StockDate, StockValue, ---- LEAD Function to get Next Day's Value NextDayValue=LEAD(StockValue,1,0) Over (ORDER BY StockDate) FROM @TableStock ) ----- Performance Calculation in CTE table SELECT StockId, StockName, StockDate, CurrentValue=StockValue, NextDayValue, ---- If Next day value is 0 then set current value Performance=(StockValue/ (Case when NextDayValue=0 then StockValue else NextDayValue end) )-1 FROM CTE ORDER BY StockDate DESC; StockId StockName StockDate CurrentValue NextDayValue Performance 101 StockName S1 10/23/2015 546.56 0.00 0.000000 101 StockName S1 10/22/2015 544.22 546.56 -0.004281 101 StockName S1 10/21/2015 543.50 544.22 -0.001323 101 StockName S1 10/20/2015 544.75 543.50 0.002300 101 StockName S1 10/19/2015 543.78 544.75 -0.001781

With the help of the LEAD function, we can get any next values for day, month, year or anything which you want to use in you analytics calculations.

Query at a Glance

USE TEMPDB GO ---- Declare Table variable for Daily Stock Values DECLARE @TableStock Table ( StockId int, StockName Varchar(20), StockDate Date, StockValue real ) ---- Insert Values in the stock Table variable INSERT INTO @TableStock (StockId, StockName, StockDate, StockValue) VALUES (101, 'StockName S1', '2015-10-23', '546.56'), (101, 'StockName S1', '2015-10-22', '544.22'), (101, 'StockName S1', '2015-10-21', '543.50'), (101, 'StockName S1', '2015-10-20', '544.75'), (101, 'StockName S1', '2015-10-19', '543.78') ---- Values in the Table Variable SELECT StockId, StockName, StockDate, StockValue FROM @TableStock ---- USE CTE to get the Next Day's Stock Value ;WITH CTE AS ( SELECT StockId, StockName, StockDate, StockValue, ---- LEAD Function to get Next Day's Value NextDayValue=LEAD(StockValue,1,0) Over (ORDER BY StockDate) FROM @TableStock ) ----- Performance Calculation in CTE table SELECT StockId, StockName, StockDate, CurrentValue=StockValue, NextDayValue, ---- If Next day value is 0 then set current value Performance=(StockValue/ (Case when NextDayValue=0 then StockValue else NextDayValue end) )-1 FROM CTE ORDER BY StockDate DESC;

So, LEAD function can really help in looking at after records to compute records differences from a single T-SQL statement that is more readable and logical than previous methods for gathering this information. Learn more on another features of SQL as:

1. SQL - Stored Procedures
2. SQL - Create Stored Procedure
3. SQL - Execute Stored Procedure
4. SQL - Alter Stored Procedure
5. SQL - Delete Stored Procedure
6. SQL - Views
7. SQL - Stored Procedures Vs Ad-Hoc (In-Line) Queries
8. SQL - Stored Procedures Vs Functions
9. SQL - Stored Procedures Vs Triggers
10. SQL - Merge Statement
11. SQL - Functions
12. SQL - Triggers
13. SQL - Indexes
14. SQL - Cursors