Saturday, August 4, 2012

SQL Server 2012 Analytical Functions: Lead and Lag

SQL Server 2012 supports many T-SQL analytic functions which are listed below:
  • LEAD: Accesses data from a subsequent row in the same result set without the use of a self-join.
  • LAG: Accesses data from a previous row in the same result set without the use of a self-join.
  • CUME_DIST: Calculates the cumulative distribution of a value in a group of values. In other words, CUME_DIST computes the relative position of a specified value in a group of values
  • FIRST_VALUE: Returns the first value in an ordered set of values.
  • LAST_VALUE: Returns the last value in an ordered set of values.
  • PERCENTILE_CONT: Calculates a percentile based on a continuous distribution of the column.
  • PERCENTILE_DISC: Computes a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset.
  • PERCENT_RANK: Calculates the relative rank of a row within a group of rows.
Analytic functions compute an aggregate value based on a group of rows. However, unlike aggregate functions, they can return multiple rows for each group. You can use analytic functions to compute moving averages, running totals, percentages or top-N results within a group.
In this article, we are going to focus only on Lead and Lag functions.
Lead function accesses data from a subsequent row and Lag function accesses from previous row in the same result set without the use of a self-join.
Syntax:
LEAD | Lag ( scalar_expression [ ,offset ] , [ default ] )
    OVER ( [ partition_by_clause ] order_by_clause )
scalar_expression is the value to be returned based on the specified offset. Offset specify the number of rows forward/backward from the current row from which to obtain a value. Default is the value to return when scalar_expression at offset is NULL.
Now lets explorer Lead function with an example:
Problem: Compare values between current year, previors year, and next year.



---------------------
-- Prepare Test Data
---------------------


DECLARE @TestData TABLE (Period DATE, Amount INT
INSERT @TestData
SELECT '2006-12-31' Period, 1000 Amount UNION
SELECT '2007-12-31' Period, 3000 Amount UNION
SELECT '2008-12-31' Period, 5000 Amount UNION
SELECT '2009-12-31' Period, 4000 Amount UNION
SELECT '2010-12-31' Period, 7000 Amount UNION
SELECT '2011-12-31' Period, 6000 Amount UNION
SELECT '2012-12-31' Period, 8000 Amount
 
----------------------------------------------------------
-- Fetch current year, previous year, and next year data
----------------------------------------------------------

SELECT Period
,Amount AS CurrentAmount
,LAG(Amount, 1, 0) OVER (ORDER BY Period) AS PreviousAmount
,LEAD(Amount, 1, 0) OVER (ORDER BY Period) AS NextAmount
FROM @TestData


 
 
 


 
 
 

Note that in the above we have set the default vaule 0 for Lead and Lag functions so we are getting zero (0) instead of NULL. Also we have set the offset to 1 for lead and lag function so we are getting only subsequent  and previous values. Offset can be set as per requirement but in most of the cases we use 1.

For more information on Analytical Functions, please click here.

Friday, July 27, 2012

CONCAT() IN SQL SERVER 2012

SQL Server 2012 introduces a new string function CONCAT() which allows you to concatenate up to 255 string or variable values in to one single string. It requires a minimum of two input values. Good thing about the function is that it takes care of implicitly converting the data values to a string by following the data type conversion rules of SQL Server 2012. This function would help eliminate the need of explicit data conversions when concatenating two values.

 Note: NULL values are implicitly converted to an empty string. If all the variables passed as parameters in CONCAT function are NULL, an empty string of type VARCHAR(1) is returned.


SYNTAX:

SELECT CONCAT (String_Value1, String_Value2, String_Value3 [, String_ValueN])
-- Between 2 and 254 values can be passed.

EXAMPLE:

DECLARE @FirstName NVARCHAR(10) = N'हरि'
       ,@LastNameVARCHAR(10) = 'Sharma'
       ,@Cell VARCHAR(20) = '+1 (425) XXX-YYYY'
       ,@Age INT = 30
       ,@Delimiter VARCHAR(2) = SPACE(2)
SELECT CONCAT(@FirstName, @Delimiter, @LastName, @Delimiter, @Cell, @Delimiter, @Age)


OUTPUT:
 


You might have observed that there’s no data conversion being performed in the above example.

The data returned by CONCAT function depends on the type of valued passed. Below are some of the mapping input and output types:
 


Input Value / Type
Output Value / Type
SQL-CLR (TYPES & UDT)
NVARCHAR(MAX)
NVARCHAR(MAX)
NVARCHAR(MAX)
NVARCHAR(<=4000)
NVARCHAR(<=4000)
VARBINARY(MAX)
NVARCHAR(MAX)
ALL OTHER INPUT TYPES
VARCHAR(<=8000) *Unless one of the parameters is an NVARCHAR of any length and in that case, the output value would be in NVARCHAR(MAX)

NOTE: CONCAT function only works with SQL Server 2012 and later versions.


Saturday, December 10, 2011

Creating Degenerated dimension

Degenerate Dimensions
In a data warehouse, a degenerate dimension is a dimension which is derived from the fact table and doesn't have its own dimension table. Degenerate dimensions are often used when a fact table's grain represents transactional level data and one wishes to maintain system specific identifiers such as order numbers, invoice numbers and the like without forcing their inclusion in their own dimension. The decision to use degenerate dimensions is often based on the desire to provide a direct reference back to a transactional system without the overhead of maintaining a separate dimension table.

Once a cube is created, we can add degenerate dimension to the cube.
Below are the steps to create Degenerate Dimension:

1. Click on Add Cube Dimension icon in Dimension Usage tab as shown below. It will open Add Cube Dimension dialogue box.
2. Click on New dimension… button in Add Cube Dimension dialogue box.















3. It will open Dimension Wizard. Click on Next to continue…
4. Select required data source view and click on Next to continue…

















5. Select Standard dimension option and click on Next to continue…
6. Select Main Dimension table and Key columns. Click on Next to continue…  
7. Select Dimension Attributes. Click on Next to continue…  
















8. Click on Next to continue…  
















9. Now we are at Completing the Wizard. Give proper dimension name click Finish to complete.

 















10. Newly created dimension is now available in Dimension Usage tab. At the intersection of Fact Sales measure group and the Sales Order Number dimension, click the ellipsis button (…) in the Item Description cell to review the fact relationship properties.   












The Define Relationship dialog box opens. Notice that you cannot configure any of the properties. The Selected relationship type should be Fact.

The following image shows the fact relationship properties in the Define Relationship dialog box