Tips and Tricks for SQL-BI

Dynamic Color Coding in SSRS Charts

noreply@blogger.com (Hari Sharma) — Sat, 10 Nov 2012 08:00:00 +0000

In this article, I will explain how to change the color of a chart dynamically. In other words, how to provide custom color palette feature at run time.

Lets start off with an example including step by step operations.

Step1. Open or create new Report Server Project. I am creating a new Report Server Project using SQL Server 2012 and saved it with LearnSSRS2012 name.

Step2. Add new Shared Data Source by right click on "Shared Data Sources" folder.

Now give any name to Share Data Source (Local, in my case) and click on Edit button to define Server Name and Database Name. In this example, I will use static data sources so I am using "." (you can also use "(local)") as ServerName. I am not defining any Database name so I will keep it blank (default will be msater). You can define any database, if you want. Now click on OK.

Step3. Now create a new report by clicking on Reports --> Add --> New Item --> Report --> Add. I don't like default report name like Report1, Report2, Report3 so I have renamed it to ChartColorCode.rdl.

Step4. Create Data Source for ChartColorCode report. To create a new Data Source, right click on "Data Sources" followed by "Add Data Sources" under Report Data as shown below:

We will use Shared Data Source we created above. So click on "Use shared data source reference" and select Local data source created at step 2. Give any name to this report level data source. I will assign "Local" again. Click OK to proceed.

Step5. Now lets create couple of datasets. To create a new dataset, right click on "Datastes" followed by "Add Datastes" under Report Data as shown below:

I will use two data sources in this example, one for Color Palette and another for Chart.

To create new Dataset for ColorPalette, Right click on DataSets --> Add Dataset... and type "ColorPalette" in Name textbox. Select "Use a dataset embedded in my report" option and select Local as Data Source.

Select Text option in "Query Type" and type following query in Query window:

SELECT 'Red' AS ColorName UNION
SELECT 'DarkOrange' AS ColorName UNION
SELECT 'Green' AS ColorName UNION
SELECT 'Blue' AS ColorName UNION
SELECT 'Olive' AS ColorName UNION
SELECT 'SeaGreen' AS ColorName UNION
SELECT 'Brown' AS ColorName UNION
SELECT 'Gray' AS ColorName UNION
SELECT 'Tomato' AS ColorName

Click OK to proceed.

Create one more dataset using following query and name it Chart.ds:

SELECT 2010 AS [Year], 12000 Amount UNION
SELECT 2011 AS [Year], 15000 Amount UNION
SELECT 2012 AS [Year], 13000 Amount

Step6. Drag and drop "3D Cylinder" bar chart from toolbox. Drag and drop Amount field from Chart.ds to value field (Y axis) and Year field to Category Groups as shown below.

To make the chart more meaningful and dainty, do following formatting:
1. Replace Chart Title with "Year vs Amount".
2. Replace Y Axis Title with Amount.
3. Repalce X Axis Title with Year.
4. Remove the Chart Legend.
5. Change the font size and color as you wish.
6. Right click on the bar and select "Show Data Labels". Select Times New Roman in data labels font because numbers are better visible in this font.

After incorporating all the above changes, click on Preview tab to view the report. My report looks like one shown below:

Step7. Now I will explain how to change the chart color at run-time.
First of all, I will create a report parameter for dynamic color. Click on Parameters folders in Report Data and then click on Add Parameter... Define pColor as report parameter Name and Color as Prompt. Select parameter visibility Hidden. You can keep it as Visible if you wish to do so.

Click on Available Values under Report Parameter Properties window. Select "Get values from a query" option under "Select from one of the following options". Select ColorPalette in Dataset box, ColorName in "Value field", and ColorName in "Label field".

Click on Default Values under Report Parameter Properties window. Select "Get values from a query" option under "Select from one of the following options". Select ColorPalette in Dataset box, and ColorName in "Value field". Click OK to proceed.

Step8. Drag and drop Table control from toolbox.
Do set following properties for table control.
a) Table control will have three default columns. Delete one column.
b) Merge Table Header and write "Color Palette" in the textbox.
c) Write following expression in the second column of Table Details.
=IIF(Parameters!pColor.Value=Fields!ColorName.Value, "◄","")
and write following expression in background property of same text box:
=Fields!ColorName.Value

d) Select Table Details and then right click --> select "Row Group" --> select "Group properties"

e) In "Group Properties" window, click on expression button under "Group on" box and write following expression =Fields!ColorName.Value.

f) Select first text box of Table Details and then right click to select "Test Box Properties" as highlighted below:

g) in "Text Box Properties" dialogue box, click on Action and select "Go to report" under "Enable as an action" and "ChartColorCode" in "Specify a report" box. Also select report parameter pColor in Name and [ColorName] in Value as shown below:

Step9. Now we are at the final step where we need to change the properties of bar chart to change the color at run time. Right click on bar chart and select "Series Properties".

In "Series Properties" window, click on Fill. and then click on expression button to open Expression window. Double click on pColor under parameter or write following expression manually as shown below:
=Parameters!pColor.Value

Step10. Its time to run the report and see the results.

First Preview:

Second Preview: Following report will be displayed once we click on Red color under:

Third Preview: Following report will be displayed once we click on DarkOrange color under:

I hope you like this article!

SSIS Logging

noreply@blogger.com (Hari Sharma) — Sun, 28 Oct 2012 05:00:00 +0000

In this article I'm writting about Logging features in SQL Server Integration Services (SSIS).

I'hv been using SSIS since last 6-7 years and I have had to implement Logging Mechanism in almost all the projects to track/debug the execution of each and every task/event used in the package. Some of you may say its pretty easy to implement - still I guess we need to take care of logging because some of the things are not so easy.

Integration Services includes logging features that write log entries whenever run-time events occur but the good thing is that it can also write custom messages. Also SSIS supports a diverse set of log providers that can write log entries to: Text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files.

My favourite log is SQL Sevrer because using sql table I can write my own queries to find logging info related to specific task/event or any failure easily.

Basically SSIS logs are associated with packages and are configured at the package level. The task(s)/container(s) in a package can be enabled for logging even if the package itself is not e.g enable logging on an Execute SQL task without enabling logging on the parent package. Also package/container/task can write to multiple logs. You can enable logging on the package only, however you can choose to enable logging on any individual task/container.

You can select a level of logging as per your requirement by specifying the events to log and the information to log for each event, however some events provide more useful information than others.

I'll explain Logging using SQL Server 2012.
Note: SSIS 2005 uses table sysDtsLog90 for logging and SSIS 2008 & SSIS 2012 use sysSSISLog table for logging.

Below is the description of each element in the log schema:

Element	Description
Computer	The name of the computer on which the log event occurred.
Operator	The identity of the user who executed the package.
Source	The name of the container or task in which the log event occurred.
SourceID	The unique identifier of the package; the For Loop, Foreach Loop, or Sequence container; or the task in which the log event occurred.
ExecutionID	The GUID of the package execution instance.
StartTime	The time at which the container or task starts to run.
EndTime	The time at which the container or task stops running.
DataCode	An integer value from the DTSExecResult enumeration that indicates the result of running task: 0 - Success 1 - Failure 2 - Completed 3 - Canceled
DataBytes	A byte array specific to the log entry. The meaning of this field varies by log entry.
Message	A message associated with the log entry.

The following table describes the predefined events that can be enabled to write log entries when run-time events occur:

Events	Description
OnError	Writes a log entry when an error occurs.
OnExecStatusChanged	Writes a log entry when the execution status of the executable changes.
OnInformation	Writes a log entry during the validation and execution of an executable to report information.
OnPostExecute	Writes a log entry immediately after the executable has finished running.
OnPostValidate	Writes a log entry when the validation of the executable finishes.
OnPreExecute	Writes a log entry immediately before the executable runs.
OnPreValidate	Writes a log entry when the validation of the executable starts.
OnProgress	Writes a log entry when measurable progress is made by the executable.
OnQueryCancel	Writes a log entry at any juncture in the task processing where it is feasible to cancel execution.
OnTaskFailed	Writes a log entry when a task fails.
OnVariableValueChanged	Writes a log entry when the value of a variable changes.
OnWarning	Writes a log entry when a warning occurs.
PipelineComponentTime	For each data flow component, writes a log entry for each phase of validation and execution. The log entry specifies the processing time for each phase.
Diagnostic	Writes a log entry that provides diagnostic information e.g. you can log a message before and after every call to an external data provider.

Implementing Logging in SSIS 2012

To implement the SSIS logging, follow these steps:

Step1. Create a Connection Manager for logging database. Right click on Connection Managers area and click on New OLE DB Connections... as shown below:

Step2. In the "Configure OLE DB Connection Manager" click on Create button. Now select Server Name and database name from Connect a database. I am using (Local) server and Test database. Click on Test Connection button to verify the connection.

Finally click on OK button to save the connection manager. This will create a new connection manage with ServerName.DatabaseName name. In my example, it is (local).Test. You can rename it to appropriate and meaningful name. For instance cmLogging.

Step3. Right click anywhere on the package surface area in the Control Flow to open Configure SSIS Log: Package wizard. Check the Package folder checkbox (left side) to enable the logging. Under the "Providers and Log" tab, select "SSIS log provider for SQL Server" in Provider type and click on Add button t add this logging for the package. Finally, click on Configuration to select the logging connection manager. Select cmLogging as connection manager.

Step4. Select logging events.
To prevent log files from using large amounts of disk space, which could degrade performance, you can limit logging by selecting specific events and information items to log. For example, you can configure a log to capture only the date and the computer name along with error message. It is not a good idea to log all the events. Under "Details" tab, select required events for logging:

Step5. Click on Advance button to set advance properties of the logging events.

Finally click on the OK button to save logging configuration.

Step6. Now you can validate your logging information by executing the SSIS package. To get more information about hoe to execute SSIS package, click Different ways to Execute SSIS packages. My package looks like the picture shown below:

You can see the log in sysSSISLog table after executing the package.

SELECT * FROM Test.dbo.sysssislog

How to find missing Identity/Sequence numbers using T-SQL?

noreply@blogger.com (Hari Sharma) — Tue, 02 Oct 2012 05:00:00 +0000

SQL Developers, did you ever come across a situation where you need to find missing identity or sequence number for a given table? For instance, someone deleted few records from a table which has an IDENTITY column. Have you wondered how to find those missing rows? In this blogpost, I am going to explain different ways to find missing identity or sequence numbers.

First I will create some sample data for demonstration purpose.

-- Prepare Test data --
SET NOCOUNT ON
IF OBJECT_ID('dbo.TestData') IS NOT NULL DROP TABLE dbo.TestData

CREATE TABLE dbo.TestData (

    ID INT IDENTITY(1, 1) NOT NULL
)

DECLARE @Counter INT = 1
WHILE @Counter <= 15
BEGIN
    INSERT dbo.TestData DEFAULT VALUES
    SET @Counter += 1
END

SELECT ID FROM dbo.TestData

Now lets delete some rows manualy:
-- Now delete some records (IDs)
DELETE dbo.TestData WHERE ID IN (3,7,8,10,12,13)

--Verifiy the data
SELECT ID FROM dbo.TestData

Below are three different ways to identity missing values.

Find missing sequence numbers using Ranking Function:

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

-- Option 1: Using Ranking function

-------------------------------------------
DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

SELECT SeqID AS MissingSeqID

FROM (SELECT ROW_NUMBER() OVER (ORDER BY column_id) SeqID from sys.columns) LkUp
LEFT JOIN dbo.TestData t ON t.ID = LkUp.SeqID

WHERE t.ID is null and SeqID < @MaxID

Here is the output:

-- If there are less records in sys.columns and

-- you need need larger result then use CROSS JOIN

SELECT SeqID AS MissingSeqID

FROM (SELECT ROW_NUMBER() OVER (ORDER BY c1.column_id) SeqID

FROM sys.columns c1

CROSS JOIN sys.columns c2) LkUp

LEFT JOIN dbo.TestData t ON t.ID = LkUp.SeqID

WHERE t.ID is null and SeqID < @MaxID

Find missing sequence numbers using CTE:

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

-- Option 2: Using Common Table Expression

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

DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

;WITH CTE (MissingSeqID, MaxID)

AS (

SELECT 1 AS MissingSeqID, @MaxID

UNION ALL

SELECT MissingSeqID + 1, MaxID FROM CTE WHERE MissingSeqID < MaxID

)

SELECT MissingSeqID FROM CTE

LEFT JOIN dbo.TestData t on t.ID = CTE.MissingSeqID

WHERE t.ID is NULL

Find missing sequence numbers using Tally Table:
This is the most prefered way out of all the above options.

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

-- Option 3: Using Tally Table

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

DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

SELECT t.ID MissingSeqID FROM dbo.Tally t

LEFT JOIN dbo.TestData td

ON td.ID = t.ID

WHERE td.ID IS NULL

AND t.ID < @MaxID

Different ways to Execute SSIS Packages

noreply@blogger.com (Hari Sharma) — Mon, 17 Sep 2012 02:56:00 +0000

We have the following ways to execute SSIS packages:

DTExec Command Line Utility
DTExecUI Windows Application
SQL Server Agent

DTExec Command Line Utility
SQL Server provides the command line tool DTExec.exe which can be used to execute an SSIS package. DTExec can be run from a Command Prompt or from a batch (.BAT) file.
To begin, open a Command Prompt and navigate to the project folder as shown below (I am taking an example of SalesForcastInput package from local directory "D:\SSIS\Hari\Sample\SSIS-Sample1\SalesForcastInput.dtsx"):

Now type the following command to execute the SalesForecastInput.dtsx package:

DTEXEC /FILE SalesForecastInput.dtsx

To see the complete list of command line options for DTEXEC, type following:
DTEXEC /?

It is not necessary to navigate command prompt to package directory before executing DTExec command; You can give the full path of your package as shown below:
DTExec /f "D:\SSIS\Hari\Sample\SSIS-Sample1\SalesForcastInput.dtsx" /conf "D:\SSIS\Hari\Sample\SSIS-Sample1\SalesForcastInput.dtsConfig" /M -1

Here, /f (/File) parameter used to load the package that is saved in the file system in your system. Likewise, /conf (/ConfigFile) parameter used to load the configuration file that is saved in the file system in your system. /M (/MaxConcurrent) specifies the number of executable files that the package can run concurrently.

Click here for more information about DTExec utility and it's parameters.

DTExecUI Windows Application

SQL Server includes the Windows application DTExecUI.exe which can be used to execute an SSIS package. DTExecUI provides a graphical user interface that can be used to specify the various options to be set when executing an SSIS package. You can launch DTEXECUI by double-clicking on an SSIS package file (.dtsx).
To begin, navigate to the your project folder. Double-click on the package (For instance, SalesForecastInput.dtsx in my example) and you will see the following multi-page dialog displayed:

As you can see there are many settings available when you use this utility. As a general rule you can simply click the Execute button to run your package. You can also fine tune your execution by clicking through the various screens and entering your own settings. After changing the settings click on Command Line which will show you the DTExec command line based on the settings you have chosen.
Note: If you have a configuration file which is not configured at package level, then do not forget to add the configuration file through Configurations setting.

Scheduling SSIS Package through SQL Server Agent

SQL Server Agent includes the SQL Server Integration Services Package job step type which allows you to execute an SSIS package in a SQL Server Agent job step. This can be especially handy as it allows you to schedule the execution of SSIS package so that it runs without any user interaction.
To begin, open SQL Server Management Studio (SSMS), connect to the Database Engine, and drill down to the SQL Server Agent node in the Object Explorer. Right click on the Jobs node and select New Job from the popup menu. Go to the Steps page, click New, and fill in the dialog as shown below:

In the above example, the SSIS package to be executed is deployed to SQL Server (i.e. the MSDB database). You can also execute packages deployed to the file system or the SSIS package store. Note that the Run as setting is the SQL Agent Service Account. This is the default setting although from a security standpoint it may not be what you want. You can setup a Proxy that allows you to give a particular credential permission to execute an SSIS package from a SQL Server Agent job step.

noreply@blogger.com (Hari Sharma) — Sat, 01 Sep 2012 08:00:00 +0000

Columnstore Index

Columnstore index was introduced in the SQL Server 2012 to significantly speed-up the processing time of common data warehousing queries.

Generally data warehousing workloads involve summarizing large volume of data. As you might know, we use different techniques in data-warehousing (DW) and decision support systems (DSS) to improve performance like pre-computed summary tables, indexed views, OLAP cubes etc. Although, these techniques greatly speed up query the data processing but they have some side effects. These techniques can be inflexible, hard to maintain, and must be designed specifically for specific query problem.

Data warehousing processing overhead can be significantly reduced by using SQL Server Columnstore index. Not only that, columnstore indexes reduces the overhead of the other solutions. Columnstore indexes also enable queries to compute the results quickly so that pre-computation is not required.

Characterisitcs of SQL Server Columnstore Indexes:

· Columnar data format – unlike the traditional row based organization of data (called rowstore format), columnstore indexes group and store data in one column at a time. SQL Server query processing can take advantage of this new data layout and significantly improve query execution time.

· Faster query results – Columnstore indexes can produce faster results for the following reasons:

o Only the columns needed must be read. Therefore, less data is read from disk to memory and then from memory to processor cache.

o Columns are heavily compressed. This reduces the number of bytes that must be read and moved and hence less I/O operations.

o Most queries do not touch all columns of the table. Therefore, many columns will never be brought into memory. This, combined with excellent compression, improves buffer pool usage, which reduces total I/O.

o Advanced query execution technology processes chunks of columns called batches in a streamlined manner, reducing CPU usage.

· Key columns – There is no concept of key columns in a columnstore index.

· Clustered index key – all the columns of clustered index must be present in the nonclustered columnstore index. If a column in the clustering key is not listed in the create index statement, it will be added to the columnstore index automatically.

· Partitioning – Columnstore indexes works with table partitioning. No change to the table partitioning syntax is required. Albeit, nonclustered columnstore index can only be created on a partitioned table if the partitioning column is part of the columnstore index.

· Record Size – There is no index key record size limitation on columnstore indexes.

· Query processing – Along with the columnstore index, SQL Server introduces batch processing to take advantage of the columnar orientation of the data. The columnstore structure and batch processing both contribute to performance gains.

· Table cannot be updated – For SQL Server 2012, a table with a columnstore index cannot be updated.

How to create Columnstore Index:

Columnstore index can be create using SQL Server Management Studio as well as using T-SQL.

Creating Columnstore index by using SQL Server Management Studio (SSMS):

1. Open Management Studio and use Object Explorer to connect to the SQL Server Database Engine.

2. In Object Explorer, expand the instance of SQL Server, expand Databases, expand a database, expand a table, right-click on required table, point to New Index, and then click Non-Clustered Columnstore Index.

3. In the Index name dialog box, under the General tab, type a name for the new index, and then click Add.

4. In the Select Columns dialog box, select the columns to participate in the columnstore index, and then click OK two times to create the index.

Creating Columnstore index by using T-SQL:

Create columnstore index on Employee table, name it CSI_Employee, and include all the columns!

-- Check whether index CSI_Employee exists on Employee

-- and delete if exists.

IF EXISTS( SELECT name FROM sys.indexes

WHERE object_id = OBJECT_ID('dbo.Employee')

AND name = 'CSI_Employee')

DROP INDEX CSI_Employee ON dbo.Employee

-- Create nonclustered columnstore index CSI_Employee on Employee.

CREATE NONCLUSTERED COLUMNSTORE INDEX CSI_Employee

ON dbo.Employee

(

EmployeeID

,FirstName

,MiddleName

,LastName

,DOB

,DepartmentID

)

Data Types

Columnstore index support some data-types and doesn't support others. You need to be aware of this while creating columnstore index to avoid any run time errors.

The following data types can be included in a columnstore index:

· char and varchar

· nchar and nvarchar (except varchar(max) and nvarchar(max))

· decimal (and numeric) (Except with precision greater than 18 digits.)

· int, bigint, smallint, and tinyint

· float (and real)

· bit

· money and smallmoney

· All date and time data types (except datetimeoffset with scale greater than 2)

The following data types cannot be included in a columnstore index:

· binary and varbinary

· ntext, text, and image

· varchar(max) and nvarchar(max)

· uniqueidentifier

· timestamp

· sql_variant

· decimal and numeric with precision greater than 18 digits

· datetimeoffset with scale greater than 2

· CLR types (hierarchyid and spatial types)

· xml

Columnstore Index Restrictions and Limitations

There are certain restrictions and limitations on columnstore indexes which are listed below:

· Columnstor index cannot have more than 1024 columns.

· Columnstor index annot be clustered or unique. Only nonclustered columnstore indexes are available.

· Columnstor index cannot be created on a view or indexed view.

· Cannot include columns of restricted data-types as mentioned above.

· Cannot act as a primary key or a foreign key.

· Cannot be changed using the ALTER INDEX statement. Drop and re-create the columnstore index instead.

· Cannot be created with the INCLUDE keyword.

· Cannot include the ASC or DESC keywords for sorting the index. Columnstore indexes are ordered according to the compression algorithms.

· Does not use or keep statistics in the manner of a traditional index.

Note: You can compare the performance optimization be executing heavy volume queries before and after creating columnstore index. You might get 50% to 90% performance gain!

noreply@blogger.com (Hari Sharma) — Wed, 22 Aug 2012 02:00:00 +0000

Introducing IIF() To SQL Server Family

IIF() is a brand new logical function introduced with SQL Server 2012 that allows you to perform IF...THEN...ELSE conditional statements within a single function. Behavior of this function is similar to any other programming language IIF() function!

In earlier versions of SQL Server, we have used IF...ELSE and CASE WHEN...THEN...ELSE...END to perform logical conditional operations.

IIF() can be handy for writing conditional CASE statements in a single function. It evaluates the expression passed in the first parameter and returns either TRUE or FALSE.

--Example1: Repalcement of simple IF...ELSE statement

DECLARE @GradeCHAR(1) = 'A'

SELECT IIF(@Grade = 'F', 'Failed', 'Passed') AS Result

GO

Here is the output of above T-SQL code:

--Example2: Nested IIF()

DECLARE @PercentDECIMAL(5, 2) = '50'

SELECT IIF(@Percent > 80, 'A',

IIF(@Percent > 60, 'B', 'C'))

GO;

Here is the output of above T-SQL code:

----

(1 row(s) affected)

-- Example3: In this example, we will evaluate the marks obtained
-- by Smith and Hari and identify who got higer marks between them.

DECLARE @Smith_Marks INT= 72

,@Hari_Marks INT = 86

SELECT IIF(@Smith_Marks > @Hari_Marks

,CONCAT('Smith got ',@Smith_Marks-@Hari_Marks,' marks higher than Hari')

,CONCAT('Hari got ' ,@Hari_Marks-@Smith_Marks,' marks higher than Smith')

)

Here is the output:

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

Hari got 14 marks higher than Smith

(1 row(s) affected)

noreply@blogger.com (Hari Sharma) — Sun, 05 Aug 2012 06:00:00 +0000

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.

noreply@blogger.com (Hari Sharma) — Sat, 28 Jul 2012 06:41:00 +0000

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.

Creating Degenerated dimension

noreply@blogger.com (Hari Sharma) — Sun, 11 Dec 2011 04:07:00 +0000

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

Key Performance Indicators (KPIs) using Analysis Services

noreply@blogger.com (Hari Sharma) — Wed, 02 Nov 2011 05:23:00 +0000

In business terminology, a KPI is a quantifiable measurement for gauging business success. In Analysis Services, a KPI is a collection of calculations that are associated with a measure group in a cube that are used to evaluate business success. Typically, these calculations are a combination of Multidimensional Expressions (MDX) expressions or calculated members. KPIs also have additional metadata that provides information about how client applications should display the results of the KPI's calculations

Common KPI Terms:

Goal - An MDX numeric expression or a calculation that returns the target value of the KPI.
Value - An MDX numeric expression that returns the actual value of the KPI.
Status - An MDX expression that represents the state of the KPI at a specified point in time. The status MDX expression should return a normalized value between -1 and 1. Values equal to or less than -1 will be interpreted as "bad" or "low." A value of zero (0) is interpreted as "acceptable" or "medium." Values equal to or greater than 1 will be interpreted as "good" or "high."
Trend - An MDX expression that evaluates the value of the KPI over time. The trend can be any time-based criterion that is useful in a specific business context. The trend MDX expression enables a business user to determine whether the KPI is improving over time or degrading over time.
Status indicator - A visual element that provides a quick indication of the status for a KPI.
Trend indicator - A visual element that provides a quick indication of the trend for a KPI.
Display folder - The folder in which the KPI will appear when a user is browsing the cube.
Parent KPI - A reference to an existing KPI that uses the value of the child KPI as part of computation of the parent KPI. Sometimes, a single KPI will be a computation that consists of the values for other KPIs.
Weight - An MDX numeric expression that assigns a relative importance to a KPI. If the KPI is assigned to a parent KPI, the weight is used to proportionally adjust the results of the child KPI value when calculating the value of the parent KPI.

Creating KPIs using Business Intelligence Development Studio (BIDS)

Click on the KPIs tab
We shall first add a KPI named ProfitPercentage to the cube:

Click on the New KPI button or right-click on the KPI Organizer window and select New KPI.
In the KPI Designer which opens up, type in the name of the KPI as ProfitPercentage.

Add the measure to the Sales Fact measure group by selecting from the Associated measure group drop down.
Now let's add an expression to evaluate the value property of the KPI:

Type the following expression in the Value Expression box. You could also drag the measures from the Metadata tab in the Calculation tools window.

[Measures].[Profit]/[Measures].[Product Cost]

OR

([Measures].[Sales Amount]/[Measures].[Product Cost]-1

We have built the expression above using the logic that ProfitPercentage is calculated as the difference in Sales and Cost, divided by the Cost.

Here [Measures].[Profit] is Calculated Member using below calculation:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Profit]

AS SUM

(

[Date].[Calendar Year - Qtr - Month].currentmember,

[Measures].[Sales Amount]-[Measures].[Product Cost]

),

FORMAT_STRING = "#,#",

VISIBLE = 1;

Now, let us set the goal of the KPI to a constant. Enter 0.25 in the Goal Expression box, which means the goal expects 25% more in sales than the cost.
Next we are on to the all important status. Here we shall write an expression to compare the value with the goal:

Before examining the actual expression let us take a look at a pseudo-expression:
CASE
   WHEN value/goal >= 0.9 THEN 1
   WHEN value/goal < 0.8 THEN -1
   ELSE 0
END

Here, a ratio of .9 would be considered good in this instance. This means that if the value makes up at least 90% of the goal then it would be considered as successful, hence the value 1 to indicate it. If value/goal equals to less than 0.8 then the status will be considered as a failure (-1). And all those values do not fall into the above ranges will be considered neutral (0).

Now, enter the following MDX expression into the Status expression box:
CASE
   WHEN KPIValue("ProfitPercentage")/KPIGoal("ProfitPercentage")>=.9 THEN 1
   WHEN KPIValue("ProfitPercentage")/KPIGoal("ProfitPercentage")< .8 THEN -1
   ELSE 0
END

Being the realization of the previous "pseudo-expression", the above expression replaces value with KPIValue("ProfitPercentage") and goal with KPIGoal("ProfitPercentage"). The KPIValue function returns the value of the KPI's value property, while the KPIGoal function returns the value of the KPI's goal property. Both functions take the name of the KPI through a character string parameter.

Select an appropriate image type from the Status indicator drop down to provide for the visuals of the KPI status.
The next property to be configured is the trend. Once again, let us first look at a "pseudo-expression":
CASE
   WHEN IsEmpty(x) THEN 0
   WHEN v - vX >= 0.25 THEN 1
   WHEN v - vX < -0.1 THEN -1
   ELSE 0
END

Here, x is the current time period of the previous year. For instance if the current selected time period is 20-May-2008, x will be 20-May-2007. v is the value of the KPI, which by default would return the value for the current time period. vx is the value of the KPI for the same time period of the previous year. The expression first checks if x is empty, thereby resulting in 0 (or no change in trend). Then, it checks if the current period's value is an increase of at least 25%, thereby indicating 1 or a positive trend. If there's a decrease of more than 10% from last year it indicates a negative trend or -1. All other results will indicate no change in the trend (or 0).

Enter the following MDX expression in the Trend expression textbox:
CASE
WHEN IsEmpty(ParallelPeriod([Date].[Calendar Year - Qtr – Month].[Calendar Year], 1,[Date].[Calendar Year - Qtr – Month].CurrentMember)) THEN 0
WHEN KPIValue("ProfitPercentage")-(KPIValue("ProfitPercentage"), ParallelPeriod([Date].[Calendar Year - Qtr - Month].[Calendar Year],1,[Date].[Calendar Year - Qtr - Month].CurrentMember)) >= 0.25
THEN 1
WHEN KPIValue("ProfitPercentage") - KPIValue("ProfitPercentage"),
ParallelPeriod([Date].[Calendar Year - Qtr - Month].[Calendar
Year],1,[Date].[Calendar Year - Qtr - Month].CurrentMember)) < -0.1 THEN -1
ELSE 0
END

In the MDX expression above, x is replaced by ParallelPeriod([Date].[Calendar Year - Qtr - Month].[Calendar Year], 1,[Date].[Calendar Year - Qtr - Month].CurrentMember) ParallelPeriod is a function which returns a member from a prior period in the same relative position as a specified member. The variable v is replaced by KPIValue("ProfitPercentage") and vx is replaced by (KPIValue("ProfitPercentage"), ParallelPeriod([Date].[Calendar Year - Qtr - Month].[Calendar Year], 1,[Date].[Calendar Year - Qtr - Month].CurrentMember)).
Select an appropriate image type from the Trend indicator drop down to provide for the visual of the KPI trend. Here's a sample of what the KPI designer should look like:
The KPIs is done! Next, process the cube. You will be able to view the KPIs using the built-in KPI Browser under the KPIs tab in BIDS. You could also use Office 2007 Excel.

Sort Dimension Members in DESCending order

noreply@blogger.com (Hari Sharma) — Sat, 01 Oct 2011 23:11:00 +0000

Sometimes we need to display Date dimension members in descending order. For example, Client ask to show Calendar Year in DESC order.

But Analysis Services doesn't provide a way to sort dimension members in Descending order. SSAS provides only Ascending order.

However, we can achieve this sort functionality by using a simple trick. I will take an example to display Calendar Year member in DESC order. Below are the steps to achieve our goal:

Step1: Go to Data Source View (DSV) and right click on Date dimension in DSV diagram. Click on New Named Calculation... to create a new member.

Step2: Now enter CalendarYearSort in Column name and 0-CalendarYear in Expression textbox. This additional column will be used to sort calendar year in descending order. Click OK to proceed. Here I considered that CalendarYear is existing column in the dimension.

Step3: Go to Dimension Structure of Date dimension and drag and drop new member "CalendarYearSort" in attributes list.

Step4: Click on Attributes Relationship tab. Right click on Calendar Year member and select New Attribute Relationship... It will open Create Attribute Relationship wizard.

Step5: In Create Attribute Relationship, select Related Attribute as Calendar Year Sort and Relationship type as Rigid. Click OK to proceed.

Step6: Again go back to Dimension Structure tab and click on Calendar Year attribute. Go to properties and set following things as shown below:

OrderBy: AttributeKey and OrderByAttribute: CalendarYearSort

Step7: Now Calendar Year is set in decending order. Just save and process the cube.

Note: Before processing the cube, you can set AttributeHierarchyVisible = FALSE because we should not show this attribute in cube and AttributeHierarchyEnabled = False to reduce cube space and increase the performance.

Incremental Load in SSIS

noreply@blogger.com (Hari Sharma) — Sun, 04 Sep 2011 03:40:00 +0000

Problem Description: Perform Incremental Load using SSIS package. There is one Source table with ID (may be Primary Key), CreatedDate and ModifiedDate along with other columns. The requirement is to load the destination table with new records and update the existing records (if any updated records are available).

Soultion:
You can use Lookup Transformation where you compare source and destination data based on some id/code and get the new and updated records, and then use Conditoional Split to select the new and updated rows before loading the table.

However, I don't recommend this approach, specially when destination table is very huge and volume of delta is very high.

You can do it in simple steps:

Find the Maximum ID & Last ModifiedDate from destination and store in package variables.
Pull the new and updated records from source and load to a staging table using above variables.
Insert and Update the records using Execute SQL Task

Here is the an step-by-step example to do this:

STEP1:
Create a new Package IncrementalLoad.dtsx and add following package variables:

VariableName	Description	Examle
DestinationConnStr	Connection string for destination server/db	Data Source=(local); Initial Catalog=TestHN; Provider=SQLNCLI10.1; Integrated Security=SSPI; Auto Translate=False;
MaxID	Max ID from destination table	100
MaxModifiedDate	Max Date from destination table	2010:11:10 11:50:20.003
SourceConnStr	Connection string for source server/db	Data Source=(local); Initial Catalog=TestHN; Provider=SQLNCLI10.1; Integrated Security=SSPI; Auto Translate=False;

STEP2:
Create Source, Destination, and staging tables using below code:

-- Source Table (create in source DB)
IF OBJECT_ID('dbo.TestSource','U') IS NOT NULL
DROP TABLE dbo.TestSource
GO
CREATE Table dbo.TestSource
(
[ID] [int] IDENTITY(1,1)
,[Code] [varchar](10)
,[Description] [varchar](100)
,[CreatedDate] [datetime] NOT NULL default GETDATE()
,[ModifiedDate] [datetime] NOT NULL default GETDATE()
)
GO

-- Destination Table (create in destination DB)
IF OBJECT_ID('dbo.TestDestination','U') IS NOT NULL
DROP TABLE dbo.TestDestination
GO
CREATE Table dbo.TestDestination
(
[ID] [int]
,[Code] [varchar](10)
,[Description] [varchar](100)
,[CreatedDate] [datetime] NOT NULL
,[ModifiedDate] [datetime] NOT NULL
)
GO

-- Staging Table (create in destination DB)
IF OBJECT_ID('dbo.TestDestinationSTG','U') IS NOT NULL
DROP TABLE dbo.TestDestinationSTG
GO
CREATE Table dbo.TestDestinationSTG
(
[ID] [int]
,[Code] [varchar](10)
,[Description] [varchar](100)
,[CreatedDate] [datetime] NOT NULL
,[ModifiedDate] [datetime] NOT NULL
)
GO

STEP3:
Create two OLE DB Connection Manager, one for Source Server and another for Destination Server.

In the connection manager properties, set the expression ConnectionString with respective variables as shown below:

STEP4:
Drag and drop Execute SQL Task and name it -
Execute SQL Task - Get Max ID and Last ModifiedDate.
Double click on EST and set following properties:
ResultSet = Single row
Connection = Destination Server
SQLStatement =
SELECT
ISNULL(MAX(ID) ,0) MaxID,
ISNULL(MAX(ModifiedDate),'2000-01-01') MaxModifiedDate
FROM TestDestination (NOLOCK)

Drag and drop another Execute SQL Task to Truncate staging table. Rename the task - Execute SQL Task - Truncate staging Table and set following properties:
ResultSet = None
Connection = Destination Server
SQLStatement = Truncate Table dbo.TestDestinationStg

Drag and drop Data Flow Task and name it Data Flow Task - Pull New and Updated rows.
Double click on DFT or click on Data Flow tab.
Now drag and drop OLE DB Source and select Source Server in OLE DB connection manager, select SQL command in Data access mode, and write following T-SQL code in SQL command text:
SELECT [ID],[Code],[Description],
[CreatedDate],[ModifiedDate]
FROM TestSource
WHERE [ID] > ? OR [ModifiedDate] >= ?

Click on Columns to generate metadata and set Parameters for above query:

Now drag and drop OLE DB Destination task and select Destination Server in OLE DB Connection manager, Table or view - fast load in Data access mode, and dbo.TestDestinationSTG in Name of the table or view.
Now click on Mapping to map the metadat with source columns.

Drag and drop one more Execute SQL Task and rename it to Execute SQL Task - Insert and Updated new rows and set following properties:
ResultSet = None
Connection = Destination Server
SQLStatement =
-- INSERT New Records
INSERT INTO TestDestination
([ID],[Code],[Description],
[CreatedDate],[ModifiedDate])
SELECT
   [ID],[Code],[Description],
   [CreatedDate],[ModifiedDate]
FROM TestDestinationStg
WHERE ID > ?

--UPDATE modified records
UPDATE D
SET D.[ID] = S.[ID]
,D.[Code] = S.[Code]
,D.[Description] = S.[Description]
,D.[CreatedDate] = S.[CreatedDate]
,D.[ModifiedDate] = S.[ModifiedDate]
FROM TestDestination D
JOIN TestDestinationStg S
   ON S.ID = D.ID
WHERE
   S.ID <= ? AND
   S.ModifiedDate > D.ModifiedDate

Click on Parameter Mapping and set the parameters as shown below:

Finally package will look like below snapshot:

STEP5:
We are done with package development. Its time to test the package. I will test this package in three steps:
1. Insert few dummy records in Source table while keeping Destination table empty.
--CASE 1: First Time Execution
INSERT INTO dbo.TestSource ([Code],[Description])
VALUES ('AAA','American Automobile Association')
WAITFOR DELAY '00:00:01.100' -- delay between two rows
INSERT INTO dbo.TestSource ([Code],[Description])
VALUES ('ABC','Associated Builders & Contractors')
WAITFOR DELAY '00:00:01.150'
INSERT INTO dbo.TestSource ([Code],[Description])
VALUES ('RR','Road Runner')
GO

Now execute the package and check the destination data. You can add Data viewers in Data Flow to see the result at run time as shown below:

2. Insert few more records in Source table to check whether new records are inserted into Destination table.
--CASE 2: Only New Records
INSERT INTO dbo.TestSource ([Code],[Description])
VALUES ('TSL','Trina Solar Limited')
WAITFOR DELAY '00:00:01' -- delay between two rows
INSERT INTO dbo.TestSource ([Code],[Description])
VALUES ('FSLR','First Solar, Inc.')

Now execute the package and check for new records:

3. Insert few more records and update few existing records in Source table and check whether both table are in sync.
--CASE 3 -- New & Updated Records
INSERT INTO dbo.TestSource ([Code],[Description])
VALUES ('LDK','LDK Solar Co., Ltd')

UPDATE dbo.TestSource
SET [Description] = 'Associated Builders and Contractors',
ModifiedDate = GETDATE()
WHERE [Code] = 'ABC'

Finaly check source and destination tables for match. Since I have both Source & Destination tables in one server, I can use EXCEPT command (for this example) which retunrs no records i.e. both the tables are in sync.

Cheers!!!

Pulling SharePoint List using SSIS 2008

noreply@blogger.com (Hari Sharma) — Mon, 01 Aug 2011 23:18:00 +0000

Do you want to pull SharePoint List data into SQL database using SSIS? If your answer is Yes then you can go for SharePoint List Source. You can download the setup from Microsoft SQL Server Community Sample: Integration Services: here is the link: SharePointListAdaptersSetup

This component has two controls - SharePoint List Source and SharePoint List Destination. The Specific Features of the included SSIS Components are:

SharePoint Source Component

Auto-detects all of the public fields from a SharePoint List or view of the list.
Supports removal of fields from the output flow, which optimizes the query against SharePoint for increased performance.
Supports restricting SharePoint data by a custom CAML query, which can be provided through a variable expression for fine tuned filtering over large lists.
Provides a tuning feature for the # of rows to return at a time. Often with SharePoint, large lists need to be brought over in smaller batches than wide (many field) lists, or else the Webservice will die.

SharePoint Destination Component

Auto-detects all of the editable fields from a SharePoint List
Only fields bound to columns from the input flow will be ‘updated’, for performance.
Supports deleting rows by ID

System Requirements

.Net 3.5 Framework
SQL Server Integration Services 2008

How to Use

Download the sample from SharePointListAdaptersSetup
Run SharePointListAdaptersSetup.msi to install the SSIS component. This installer places pre-built binaries into the Global Assembly Cache (GAC) and into the C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents directory (if present).
A good way to explore the sample is to try using it, understand its behavior, and then begin reviewing the source code for the component. If you do not see the SharePoint adapters in your toolbox automatically, then follow these instructions:

Open Business Intelligence Development Studio, open a package, and then click Choose Toolbox Items on the Tools menu.
In the Choose Toolbox Items dialog box, click the SSIS Data Flow Items tab, and then check SharePoint Destination and SharePoint Source.
Click Ok.

The SharePoint source and destination components should now appear in the toolbox for the data flow task. You can add the source and destination components to the data flow of the package.

Table Partition

noreply@blogger.com (Hari Sharma) — Wed, 06 Jul 2011 04:02:00 +0000

Table partitioning is a data organization scheme in which table data is divided across multiple data partitions or ranges according to values in a table column.

Benefits of table partitioning

There are numerous benefits of table partitioning:

To improve the scalability and manageability of large tables and tables in Database and Data Warehouse
Database and Data Warehouse that would benefit from easier roll-in and roll-out of table data
Furthermore, if a large table exists on a system with multiple CPUs, partitioning the table can lead to better performance through parallel operations.
A table with varying access patterns might be a concern for performance and availability when different sets of rows within the table have different usage patterns.

The steps for creating a partitioned table include the following:

Create a partition function to specify how a table or index that uses the function can be partitioned.
Create a partition scheme to specify the placement of the partitions of a partition function on filegroups.
Create a table or index using the partition scheme.

Below are the steps to creation Horizontal Table Partition

-- Create the partition function

CREATE PARTITION FUNCTION PartitionFunctionMonthly (int)
AS RANGE RIGHT
FOR VALUES (20100101, 20100201, 20100301)
GO

-- Add the partition scheme
CREATE PARTITION SCHEME PartitionSchemaMonthly
AS PARTITION PartitionFunctionMonthly
ALL TO ( [PRIMARY] )
GO

-- Create a simple table
CREATE TABLE PartitionTable (
   DateKey int NOT NULL,
   CustomerKey int NOT NULL,
   SalesAmt money,
CONSTRAINT PKPartitionTable PRIMARY KEY NONCLUSTERED
   (DateKey, CustomerKey)
)
ON PartitionSchemaMonthly(DateKey)
GO

------------------------------------
-- Unit Testing of Partitions
------------------------------------
-- Add some rows
INSERT INTO PartitionTable (DateKey, CustomerKey, SalesAmt)
SELECT 20091201, 1, 5000 UNION ALL
SELECT 20100101, 2, 3000 UNION ALL
SELECT 20100215, 7, 6000 UNION ALL
SELECT 20100331, 5, 3000 UNION ALL
SELECT 20100415, 8, 6000
GO

-- A query accesses the entire table, exactly as you'd expect.
SELECT * FROM PartitionTable
GO

--Query partition contents
SELECT
$partition.PartitionFunctionMonthly(DateKey) AS [Partition#],
COUNT(*) AS RowCount,
Min(DateKey) AS MinDate,
Max(DateKey) AS MaxDate
FROM PartitionTable
GROUP BY $partition.PartitionFunctionMonthly(DateKey)
ORDER BY [Partition#]
GO

Moving Database Files From One Drive to Another

noreply@blogger.com (Hari Sharma) — Sun, 05 Jun 2011 03:40:00 +0000

In practice, database files grows everyday. Sometimes it occupy the complete disk and we may end up in unsufficeint mamroy - leading to unexpected results.

In this blog, I'll explain how to transfer database files from one drive to another. To explain, I'll create a test database TestDB. Here is the command:

USE [master]
GO

IF DB_ID(N'TestDB') IS NULL
BEGIN
CREATE DATABASE [TestDB] ON PRIMARY
(
   NAME = N'TestDB'
   ,FILENAME = N'C:\MSSQL\Data\TestDB.mdf'
   ,SIZE = 3MB
   ,MAXSIZE = 2048GB
   ,FILEGROWTH = 1024KB
)
LOG ON
(
   NAME = N'TestDB_log'
   ,FILENAME = N'C:\MSSQL\Data\TestDB.ldf'
   ,SIZE = 3MB
   ,MAXSIZE = 2048GB
   ,FILEGROWTH = 10%
)
END
GO

Now check existing files location:

USE TestDB
GO
SELECT name, physical_name, state_desc
FROM TestDB.sys.master_files
WHERE database_id = DB_ID(N'TestDB')

Now the database is created, you can create some tables and enter some data into the database, if you wish, Otherwise proceed like this:

Step1: Make the database OFFLINE.

USE [master]
GO
ALTER DATABASE TestDB SET OFFLINE

Step2: Move file Physically
Now you need to move that file physically from existing folder to the new the location. Open the parent folder (Here 'C:\MSSQL\DATA') , You can see both mdf and ldf files', make sure that you cut the appropriate file, in this example it is the Log file. Cut that "TestDB_log.LDF" file and paste it on "D:\MSSQL\Data"

Step3: Update the system reference
Once you move the file physically , you need to update the system reference using the ALTER DATABASE .. MODIFY FILE Command as shown below:

ALTER DATABASE TestDB
MODIFY FILE (
NAME ='TestDB_log'
,FILENAME = 'D:\MSSQL\Data\TestDB.LDF'
)

Step5 : Make database ONLINE
Last step is to make database online as shown below:

ALTER DATABASE TestDB SET ONLINE
GO

We are done. Now check existing files location using query mentioned above.

Sequence Container in SSIS - Different ways of using Sequence Containers

noreply@blogger.com (Hari Sharma) — Fri, 06 May 2011 00:32:00 +0000

Integration Services includes the Sequence container, which makes it simple to divide the control flow in a package into groups of tasks and containers that you can manage as a unit.
Using Sequence containers lets you handle the control flow in a package in more detail, without having to manage individual tasks and containers.
If a package has many tasks, it can be helpful to group the tasks in Sequence containers.

I'll explain different scenarios where Sequence Container paly an important role to implement business rules.

Scenario 1:

Sequence Task SC1 must execute first. SC1 may have many task with or without precedence constraints. The group of these task must execute prior to any other tasks in the package.
Sequence Task SC2 must start after completion of SC1. SC2 must execute irrespective of Failure/Success of SC1.
Sequence Task SC3 contains Maintenence Plan Tasks and should be followed by SC2. SC3 must execute only after successfully execution of SC2.

Scenario 2:

Sequence Task SC1 must execute first.
Sequence Task SC2 must start based on logical condition. This condition can be based on package variables.
Sequence Task SC3 contains Maintenence Plan Tasks and should be followed by both SC1 and SC2. SC3 must execute after successfully execution of SC1. SC3 should execute regardless of SC2 but if SC2 executes, SC3 must execute after SC2.

Scenario 3: This is real scenario in many BI applications.

SC1 must execute prior to other task. This Sequence Container may contain many tasks to pull data from hetrogenious data sources.
SC2 should be followed by SC1. This can be used to Insert/Update dimension tables and fact tables along with intermediate tables (if any). This container may further contain Sub - Sequence Containers e.g. SC2(a) to handle dimension Insert/Update and SC2(b) to update Intermediate and Fact tables.
SC3 should be followed by SC2. This may contains SSAS Tasks to Build/Prcoess Cubes. This container may also have Sub - Sequence Containers e.g. SC3(a) to check whether Current Month partition exists or not. If Yes than Do Nothing Else Create Current Month Partitions using Script task. SC3(b) can be used to Process Dimensions and Current Month Partitions.
SC4 can be used for Maintenence Plan tasks. If SC3 successfully completed (Evolution operation - Expression and Constraint) than take Backup of Cube, Shrink Datamart and Take Backup of Datamart.
SC5 can be used for Recovery Paln. If SC3 fails, Restore Cube from previos & Latest Backup available..

However, there are many more ways of utilizing Sequence Containers. Further, things depends on complexity of business requirement rules defined.

Foreach Loop Enumerators

noreply@blogger.com (Hari Sharma) — Wed, 16 Mar 2011 04:42:00 +0000

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages but in a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

SSIS provides the following enumerator types:

Foreach File Enumerator to enumerate files in a folder. The enumerator can traverse subfolders. For example, you can read all the files that have the *.csv file name extension in the folder and its subfolders.
Foreach Item Enumerator to enumerate items that are collections. For example, you can enumerate the names of executables and working directories that an Execute Process task uses.
Foreach ADO Enumerator to enumerate rows in tables. For example, you can get the rows in an ADO recordset.
Foreach ADO.NET Schema Rowset Enumerator to enumerate the schema information about a data source. For example, you can enumerate and get a list of the tables of your SQL Server database.
Foreach From Variable Enumerator to enumerate the object that a specified variable contains. The enumerable object can be an array, an ADO.NET DataTable, an Integration Services enumerator, and so on. For example, you can enumerate the values of an array that contains the name of Servers.
Foreach Nodelist Enumerator to enumerate the result set of an XML Path Language (XPath) expression. For example, this expression enumerates and gets a list of all the products of Electronics type: /products/product[@type='Electronics'].
Foreach SMO Enumerator to enumerate SQL Server Management Objects (SMO) objects. For example, you can enumerate and get a list of the views in a SQL Server database.

Adding Custom Color in SSRS Chart Reports

noreply@blogger.com (Hari Sharma) — Sun, 02 Jan 2011 07:19:00 +0000

Do you ever come across a requirement where you need to use user defined colors instead of default colors in a chart (for example Pie Chart).

In this article, I'll explain how to create a Custom Palette and how to use that palette in a Pie Chart.

In the above screen-shot, I am using color Blue, Red, Teal and Gold for the first four values of the Period column and same colors are used in Pie Chart as well.

Here are the steps to achieve this task:

STEP1:
Create your custome color palette in the report using Custom Code in your report. To do so, click Report => Report Properties => Code and copy below code:

Private colorPalette As String() = { "Blue", "Red", "Teal", "Gold", "Green","#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}

Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()

Public Function GetColor(ByVal groupingValue As String) As String
   If mapping.ContainsKey(groupingValue) Then
     Return mapping(groupingValue)
   End If
   Dim c As String = colorPalette(count Mod colorPalette.Length)
   count = count + 1
   mapping.Add(groupingValue, c)
   Return c
End Function

You can define your own colors in the above function.

STEP2:
In the Pie Chart, select Series Properties and select the Fill option from left side as shown below:

Now write following expression in the Color expression:

=code.GetColor(Fields!Year.Value)

Here Fields!Year.Value is a field of dataset which is used as Chart Category fields.

STEP3:
In the left most column of the tabular report, insert character █ and in the color property of the textbox write following expression:

=code.GetColor(Fields!Year.Value)

Here I am considing that tabular report is grouped on Fields!Year.Value field.

Thats it. Click on the preview and enjoy your custom colors in charting.

Cheers!!!

Dynamic Cube Partitioning in SSAS 2008

noreply@blogger.com (Hari Sharma) — Sun, 12 Dec 2010 22:05:00 +0000

In this article I am going to explain the Implementaion of Dynamic Cube Partition in SSAS 2008. I would want to split this article in two parts as mentioned below:

Partition benefits
Implementing Dynamic Partitions

Partition Benefits:

Better Query Performance: Cube partition is a powerful mechanism for improving query performance. Queries that summarize data over 10 years could take considerably longer than those that only search through the current year data. If we have proper partitions then SSAS only has to scan a small subset of data to return query results hence dramatic performance improvements compared to queries running against a cube with a single partition.
Minimize downtime: Cube partitioning supports reducing downtime associated with cube processing. In almost all the cases, a portion of data warehouse is volatile and needs to be processed often. However other portions are relatively static. For example, in a sales cube, we need to change the current year's data nightly, but sales from previous years might change only occasionally - in case if account for merchandise returns and exchanges. If your warehouse tracks last 10 years salesthen processing only the current partition may be 10 times quicker than processing the entire cube.
Aggregations benefits: The partition queried frequently could benefit from additional aggregations, which in turn could improve performance. Partition(s) that are used less can be processed less frequently with considerably fewer aggregations.
Customized storage and processing settings: Frequently accessed partitions might benefit from proactive caching and ROLAP storage. On the other hand, other forms of storage and processing might be better for less frequently queried partitions.
Distributed query and processing load: SSAS allows you to create remote partitions - a remote partition resides on a server different from its parent cube. This way the queries that affect the remote partition are processed on a server separate from its parent cube, allowing you to take advantage of additional processing power.
Parallel Partitions Processing: SSAS allows processing multiple partitions in parallel on a server that has multiple processors. This can further reduce the total cube processing time.

Implementing Dynamic Partitions

In this section I will explain step by step operations to create Dynamic Cube Partitions.

STEP1:
Preapare test data for Sales cube. I will use three dimensions (DimDate, DimProduct, and DimCustomer) and one fact table for Sales cube. Click here to download CubePartitionData.sql file which contains T-SQL code to generate these tables along with sample data.

STEP2:
Create new Analysis Services Project using BIDS and save it as Sales.

STEP3:
Create Data Source, and then Data Souce View using above three dimension tables.

If you are newbie, click Creating First Cube in SSAS 2008 for more information.

STEP4:
Create Sales cube along with required dimensions. Set all required properties of dimensions and create any hierarchies if required. My Solution looks like this:

STEP5:
Double click on the cube and navigate to Partition tab. You will see a default partition as shown below:

Delete this default partition and Click on new partition... to click new partition. In Partition Wizard, Select FactSales as available table in Specify Source Information page and click next. Select Specify a query to restict row in Restrict Rows page and write WHERE condition to restrict partition rows.

Click Next twice and enter Partition name Sales_20100101-20100131 in the Completing the Wizard page and click Finish.
Finally Process the cube. Once cube is processed successfully, you can see a new database in Analysis Services:

You can browse the data for this partition:

STEP6:
We are done with one manual partition. Now its time to create the partitions dynamically and apply a logic to process the delta data every time.

First off all, right click on the Partition Sales_20100101-20100131 (highlighted above) and then select Script Partition as ==> Create To ==> New Query Editor Window. It will create XMLA scripts for this partition. Save this file in the project with CreatePartitionSales_20100101-20100131.xmla name.

In similar fashion, right click on the Partition Sales_20100101-20100131 and then select Process. This will open Process Partition wizard, click Script to generate XMLA script and save this file in the project with CreatePartitionSales_20100101-20100131.xmla name.

STEP7:

We are done with SSAS development! Now we have to create an SSIS package to implement dynamic creation of cube partition and processing of that partition.

Click on File => Add => New Project in menu bar in the same solution. Select Integration Services Project and enter the name of the SSIS Project.
Rename the SSIS package1.dtsx by DynamicSalesPartition.dtsx.

STEP8:
Add two connection Managers - one OLE DB connection manager for database and another Analysis Services connection manager for SSAS database.

STEP9:
Add following Package variables:

Here is the brief description of each variable:

Directory - To store package path
FromDateKey - To Store current partition start date key e.g. 20101201.
ToDateKey - To Store current partition end date e.g. 20101231
PartitionName - Current partition name e.g. SalesPartition_20101201-20101231
IsPartitionExists - To check whether partition alreday exists or not
CreatePartitionXMLA - To store XMLA script to create current partition
ProcessPartitionXMLA- To store XMLA script to process current partition

Note: You can add more variables as per need and business requirement.

STEP10

Now you have to develop SSIS package as shown below:

First Execute SQL Task is used to initialize FromDateKey, ToDateKey, and PartitionName for the current partition. Use below query in this Task:
DECLARE
   @FromDateKey varchar(8), @ToDateKey varchar(8),
   @CalendarYear int, @CalendarMonth int

SELECT
   @FromDateKey = MAX(FromDateKey),
   @ToDateKey = MAX(ToDateKey)
FROM PartitionLog (NOLOCK)

SELECT
   @CalendarYear = MIN(CalendarYear),
   @CalendarMonth = MIN(CalendarMonth)
FROM DimDate WHERE DateKey > @ToDateKey + 1

--SET FromDateKey and ToDateKey for current Partition
IF NOT CONVERT(varchar(8),GETDATE(),112) BETWEEN @FromDateKey AND @ToDateKey
SELECT @FromDateKey = MIN(DateKey), @ToDateKey = MAX(DateKey)
FROM DimDate (NOLOCK)
WHERE CalendarYear = @CalendarYear AND CalendarMonth = @CalendarMonth

SELECT @FromDateKey AS FromDateKey, @ToDateKey AS ToDateKey,
'Sales_' + @FromDateKey + '-' + @ToDateKey AS PartitionName
GO

Second Task in the package is Script Task. Here we need to read the contents of files
CreatePartitionSales_20100101-20100131.xmla and
ProcessPartitionSales_20100101-20100131.xmla and store in the package variables CreatePartitionXMLA & ProcessPartitionXMLA.

I am using User::Directory as ReadOnlyVariable and User::CreatePartitionXMLA,User::ProcessPartitionXMLA as ReadWriteVariables.

Click here to download the code for Script Task - Read XMLA Files and store in variables.

To know about reading file contents using Script Task, click Script Task to Read File.

Third Task is a sequence container which contains three task - Script Task, Analysis Services Execute DDL Task, and Execute SQL Task.

Script Task is used for two reasons -
1. To check whether current partition exists or not.
2. To update XMLA content for current Partition.

This is the most tricky and interseting part here. We need to replace Partition ID, Partition Name, and Source QueryDefinition for current Partition which are highlighted below:

In Script Task, select User::FromDateKey,User::PartitionName,User::ToDateKey as ReadOnlyVariables and User::CreatePartitionXMLA,User::ProcessPartitionXMLA,User::IsPartitionExists as ReadWriteVariables.

Click here to download the code for Script Task - Generate XMLA to create and Process new Partition.

Click SSIS Script Task to Find and Replace File contents to know more about find and replace mechanism using script task.

Second task in the sequence Container is Analysis Services Execute DDL Task. This task is used to create new Partition. In Analysis Services Execute DDL Task Editor, select SSAS.localhost as Connection, Variable as SourceType, and User::CreatePartitionXMLA as Source.

Also don't forget to set precedence constraint. You should execute this task only when partition does not exists. Open Precedence Constraint Editor and Select Expression and Constraint in Evolution operation, Success in Value, and @IsPartitionExists == 0 in Expression as shown below:

Third Task in the Sequence Container is Execute SQL Task - this is used to store newly created Partition information in the PartitionLog table. In Execute SQL Task, select ResultSet None, ConnectionType OLE DB, Connection (local).Test, SQLSourceType Direct input and SQLStatement as:

INSERT INTO dbo.PartitionLog
([PartitionName],[FromDateKey],[ToDateKey],[CreatedDate])
VALUES (?,?,?,GETDATE())

Click on Parameter Mapping and map the parameters as shown below:

STEP11
Finally we need to process the current partition. This is independent of previous sequence container where we are creating new partition if it is not exists.
However, we need to process the current partition every time we execute the package. In most of the cases, new partition will be created on the first day of month but partition should be processed everyday to include delta data.

This sequence container also contains three tasks.

First task is Analysis Services Processing Task - to process the dimensions data. Select SSAS.localhost as connection manager and all the dimensions in Object Name - which are not static in data. Select Process Update as Process Option.

Second Task is againg Analysis Serives Processing Task - to process Current Partition. Select SSAS.localhost as connection manager and Sales_20100101-20100131 in Object Name - which is default partition (or first partition). Select Process Full as Process Option.

Now click on Expression and select @[User::ProcessPartitionXMLA] in ProcessingCommands.

Finally store the Last Processed Date and Processed Count (number of time this partition has processed) in the log table using Execute SQL task. Select ResultSet None, ConnectionType OLE DB, Connection (local).Test, SQLSourceType Direct input and SQLStatement as:

UPDATE PartitionLog
SET LastProcessDate = GETDATE()
,ProcessedCount = ProcessedCount + 1
WHERE PartitionName = ?

Click on Parameter Mapping and map the parameters and map the parameter 0 with User::PartitionName.

STEP12
We are done with package development. Now its time to execute the package and check the dynamic behaviour of Partition creation and processing. Before execution the package, there is only partition in the SSAS Sale database.
Now Right Click on the package and execute it. Once package is execute successfully, you will see one more partition in the Sales database and one more entry in PartitionLog table.

Now Execute the package or schedule it to execute automatically. Once you are done with 10 more executions, you will reach to current month partition Sales_20101201-20101231. and you can see 12 partitions - one partition for each month as shown below:

Since you are in current month partition, if you execute the package every day in current month - it will not create any new partition till beginning of next month. It will only process Current Partition to include the data in the cube as shown below:

Now you can browse the data for all the partitions:

We are done with Dynamic Package creation.
I hope you will like this article and if your answer is yes then don't forget to click Like :-)

Many people have had asked me to share the solution. I have stored that in my SkyDrive so you can download it from here: Dynamic Cube Partition

Cheers!!!

Convert Decimal to ROMAN using T-SQL

noreply@blogger.com (Hari Sharma) — Mon, 01 Nov 2010 17:03:00 +0000

Do you want to convert a number into Roman Equivalent using T-SQL code or function? If yes then here you go:

/**************************************************
CREATED BY : Hari Sharma
PURPOSE    : Convert Decimal to ROMAN Equivalent
HOW TO USE : SELECT dbo.GetRomanNo(18)
             --OUTPUT: XVIII
**************************************************/
CREATE Function GetRomanNo(@N as varchar(20))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @s varchar(100), @r varchar(100),
          @i bigint, @p int, @d bigint
SET @s = ''
SET @r = 'IVXLCDM' -- Roman Symbols

  /* There is no roman symbol for 0, but I don't
   want to return an empty string */
IF @n=0
     SET @s = '0'
  ELSE
BEGIN
    SELECT @p = 1, @i = ABS(@n)
     WHILE(@p<=5)
     BEGIN
       SET @d = @i % 10
       SET @i = @i / 10
       SELECT @s = CASE
         WHEN @d IN (0,1,2,3) THEN
           Replicate(SubString(@r,@p,1),@d) + @s
         WHEN @d IN (4) THEN
           SubString(@r,@p,2) + @s
         WHEN @d IN (5,6,7,8) THEN
           SubString(@r,@p+1,1) +
           Replicate(SubString(@r,@p,1),@d-5) + @s
         WHEN @d IN (9) THEN
           SubString(@r,@p,1) + SubString(@r,@p+2,1) + @s
         END
       SET @p = @p + 2
     END
     SET @s = Replicate('M',@i) + @s
     IF @n < 0
     SET @s = '-' + @s
   END

   RETURN @s
END
GO

ExecutionValue and ExecValueVariable in SSIS

noreply@blogger.com (Hari Sharma) — Thu, 28 Oct 2010 07:26:00 +0000

Have you ever used ExecutionValue and ExecValueVariable in SSIS package?

The ExecutionValue property can be defined on the object Task and all tasks have this property. Its up to the developer to do something useful with this. The purpose of this property is to return something useful and interesting about what it has performed along with standard success/failure result.

The best example perhaps is the Execute SQL Task which uses the ExecutionValue property to return the number of rows affected by the SQL statement(s). This could be a useful feature which you may often want to capture into a variable and using the result to do something else. We cann't read the value of a task property at runtime from SSIS but we can use ExecValueVariable to get it.

The ExecValueVariable property exposed through the task which lets us select a package variable. When the task sets the ExecutionValue, the actual value is copied into the variable we set on the ExecValueVariable property and a variable is something we can access and do something with. So if you are interested in ExecutionValue property then make sure you create a package variable and set the name as the ExecValueVariable.
Below are the steps to implement this:

STEP1:
Create a new package and add below variable.

STEP2:
Drad and drop Execute SQL Task and set the properties as per your requirement. I am using below query in SQLStatement to update Employee table:
UPDATE [TestHN].dbo.Employee
SET [Basic] = [Basic]*2
WHERE [Basic] < 5000

This query updates 4 records.

STEP3:
Set the ExecValueVariable with User::ExecutionValue variable as shown below:

STEP4:
Drag and drop Script Task to display the result of ExecValue variable. Now Execute the package.

Here is the list of few tasks that return something useful via the ExecutionValue and ExecValueVariable:

Task	Description of ExecutionValue
Execute SQL Task	Returns the number of rows affected by the SQL statement(s).
File System Task	Returns the number of successful operations performed.
File Watcher Task	Returns the full path of the file found.
Transfer Jobs Task	Returns the number of jobs transferred
Transfer Error Messages Task	Returns the number of error messages transferred.
Transfer SQL Server Objects Task	Returns the number of objects transferred.

Track SQL Database Growth

noreply@blogger.com (Hari Sharma) — Mon, 25 Oct 2010 07:01:00 +0000

In this article I am sharing a simple T-SQL code to track database growth for specific database. This could be a very simple query for SMEs but it can really help newbies:

/*************************************************
Purpose : Track Database Growth for a specific DB
Create By : Hari Sharma
**************************************************/
SELECT
   BackupDate =
   CONVERT(VARCHAR(10),backup_start_date, 111)
   ,SizeInMBs=FLOOR(backup_size/1024000)
FROM msdb..backupset
WHERE
   database_name = DB_NAME() --Specify DB Name
   AND type = 'd'
ORDER BY
   backup_start_date desc

Displaying Dynamic Columns in SSRS Report

noreply@blogger.com (Hari Sharma) — Sun, 17 Oct 2010 09:10:00 +0000

Problem: How to display selected columns dynamically in SSRS reports.
Example: A report contains more than 30 fields. Some users want to see only 5 fields, some users 10 fields, and other may want to see 20 fields.
Solution: Add a Report Parameter having the values as the name of all the fields of dataset. Now set hidden expression for each column of the tabular report.

Here is the solution with an example:

STEP1:
Create a report with required dataset. Drag and drop table control and select dataset fields.
In my example, I have following fields in the dataset: Year, Quarter, Month, Date, Product Name, Customer Name, Sales Region, Sales Country, Order Number, Sales Amount.

STEP2:
Create a dataset dsColumns using below query:
SELECT 1 ID, 'Year' AS ColumnName UNION
SELECT 2 ID, 'Quarter' AS ColumnName UNION
SELECT 3 ID, 'Month' AS ColumnName UNION
SELECT 4 ID, 'Date' AS ColumnName UNION
SELECT 5 ID, 'Product Name' AS ColumnName UNION
SELECT 6 ID, 'Customer Name' AS ColumnName UNION
SELECT 7 ID, 'Sales Region' AS ColumnName UNION
SELECT 8 ID, 'Sales Country' AS ColumnName UNION
SELECT 9 ID, 'Order Number' AS ColumnName UNION
SELECT 10 ID,'Sales Amount' AS ColumnName

STEP3:
Create a new parameter with name pDisplayFields and Promt Display Columns as shown below:

In Available Values of Report Parameter Properties wizard, select Get values from a query, select dsColumns in Dataset, ColumName in value field and label field.

In Default Values of Report Parameter Properties wizard, select Get values from a query, select dsColumns in Dataset, ColumName in value field.

STEP4:
Now you have to set the expression to display the colummns which are selected in the pDisplayColumn parameter. Right click on First Column (Year in my example) and click Column Visibility...

Write following expression in Show or hide based on an expression of Column Visibility wizard:
=IIF(InStr(JOIN(Parameters!pDisplayFields.Value,","),"Year")>0,False,True)

Now repeat this expression for all the columns by modify the expression for the respective column name accordingly.

Thats all. Now preview the report. You will see all the columns by default.

Now select required columns in Display Column parameter to modify the report layout at run time.

Cheers!!!

Creating SSIS Template Package

noreply@blogger.com (Hari Sharma) — Wed, 13 Oct 2010 06:14:00 +0000

One of the most desired feature in SSIS packages development is re-usability. Being a developer, you may need standard packages that can be re-used during different ETL development. In SSIS, this can be easily achieved using template features. SSIS template packages are the re-usable packages that one can use in any SSIS project any number of times.

You can reuse these items when you use a package template to create a new package. You may want to reuse the following items in a package template:

Connection Managers and Log Providers: Log Provider is common thing in almost all the packages. You can create a package that includes a connection manager and a log provider. You can also use that package as a template for other packages.
Common Variables and Configurations: In most of the packages you may use common variables and same configurations.
Event Handlers: You may need to use same event handlers or error handling across ETL packages in a project.
Send Mail tasks: You can create a package that contains an SMTP connection manager, a Send Mail task, and a property expression to build the Subject line. Use this package as a template to create other packages that notify you by e-mail when the package runs successfully or generates an error.
Common Task: I have seen many projects where many tasks are common across 70% packages e.g. Execute SQL Task, Data Flow elements, and Control Flow elements. It may differ project to project but its always good idea to encapsulate the common task in a tamplate and use it wherever required.

How to create SSIS Template Package
Below are the steps to create a new package template in SQL Server Business Intelligence Development Studio:
1. Start SQL Server Business Intelligence Development Studio. Click File, point to New, and then click Project.
2. In the New Project window, click Business Intelligence Projects, and then click Integration Services Project under Visual Studio installed templates, type a name for the project, and then click OK.

3. Add the items that you want from the Toolbox to the Package.dtsx file. Once you are done with pachake template development, Click File, and then click Save Selected Items.
Note: Give a package name that describes the functionality of the package.

4. Click File, and then click Save Copy of PackageName As. Here filename is the name of package.
In the Save Copy of Package dialog box, click File System in the Package location box, type the following path in the Package path box, and then click OK. In this path, drive is the hard disk where Microsoft Visual Studio is installed:
C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

Note: Type the path of the Visual Studio 2005 folder in the Package path box in case you didn't use the default location to install Visual Studio.

How to use the Package template in other Project or Solution
1. Start SQL Server Business Intelligence Development Studio. Click File, point to New, and then click Project.
2. In the New Project window, click Business Intelligence Projects, and then click Integration Services Project under Visual Studio installed templates, type a name for the project, and then click OK.

3. In Soultion Explorer, right click on Project name, click on Add and then New Item... Under Visual Studio installed templates, click the template that you want, type a name for the template, and then click Add. In the below screen-shot, MyPkgTemplate is a template I saved in my system.

Note: The default name for the template in the Name box is the name of the template plus an incremented counter. For example, if the template name is MyPkgTemplate.dtsx, the default name is MyPkgTemplate1.dtsx.

Dimension Design Best Practices

noreply@blogger.com (Hari Sharma) — Wed, 06 Oct 2010 21:17:00 +0000

Good Dimension design is the most important aspect of a well designed OLAP database. Although the wizards in SSAS do much of the work to get you started, it's important to review the design that is created by the wizard and ensure that the attributes, relationships, and hierarchies correctly reflect the data and match the needs of end-users.

Here are the Dimension Design Best Practices:

Create attribute relationships wherever possible
Attribute relationships are an important part of dimension design. They help the server optimize storage of data, define referential integrity rules within the dimension, control the presence of member properties, and determine how MDX restrictions on one hierarchy affect the values in another hierarchy. For these reasons, it is important to spend some time defining attribute relationships that accurately reflect relationships in the data.

Avoid creating unnecessary attributes

Attributes add to the complexity and storage requirements of a dimension, and the number of attributes in a dimension can significantly affect performance. This is especially of attributes which have AttributeHierachyEnabled set to True. Although SSAS can support many attributes in a dimension but having more attributes decreases performance unnecessarily and can make the end-user experience difficult.

Use numeric keys for attributes that contain many members (>1 million)
Using a numeric key column instead of a string key column / composite key will improve the performance of attributes that contain many members. This best practice is based on the same concept as using surrogate keys in relational tables for more efficient indexing. You can specify the numeric surrogate column as the key column and still use a string column as the name column so that the attribute members appear the same to end-users. As a guideline, if the attribute has more than one million members, you should consider using a numeric key.

Don't create hierarchies where an attribute of a lower level contains fewer members than an attribute of the level above
A hierarchy such as this is frequently an indication that your levels are in the incorrect order: for example, [State] above [Country]. It might also indicate that the key columns of the lower level are missing a column: for example, [Year] above [Quarter] instead of [Year] above [Year-Quarter]. Either of these situations will lead to confusion for end-users trying to use and understand the cube.

Don't include multiple non-aggregatable attributes per dimension
Because there is no All member, each non-aggregatable attribute will always have some non-all member selected, even if not specified in a query. Therefore, if you include multiple non-aggregatable attributes in a dimension, the selected attributes will conflict and produce unexpected numbers.

Use key columns that completely define the uniqueness of the members in an attribute
Usually a single key column is sufficient, but sometimes multiple key columns are necessary to uniquely identify members of an attribute. For example, it is common in time dimensions to have a [Month] attribute include both [Year] and [Month] as key columns. This is known as a composite key and identifies January of 2009 as being a different member than January of 2010. When you use [Month] in a time hierarchy that also contains [Year] so this distinction between January of 2009 and January of 2010 is important.

Do perform Process Index after doing a Process Update if the dimension contains flexible AttributeRelationships or a parent-child hierarchy
An aggregation is considered flexible if any attribute included in the aggregation is related, either directly or indirectly, to the key of its dimension through an AttributeRelationship with RelationshipType set to Flexible. Aggregations that include parent-child hierarchies are also considered flexible.

When a dimension is processed by using the Process Update option, any flexible aggregations that the dimension participates in might be dropped, depending on the contents of the new dimension data. These aggregations are not rebuilt by default, so Process Index must then be explicitly performed to rebuild them.

Don't create redundant attribute relationships
Do not create attribute relationships that are transitively implied by other attribute relationships. The alternative paths created by these redundant attribute relationships can cause problems for the server and are of no benefit to the dimension. For example, if the relationships A->B, B->C, and A->C have been created, A->C is redundant and should be removed.

Do include the key columns of snowflake tables joined to nullable foreign keys as attributes that have NullProcessing set to UnknownMember
If tables that are used in a dimension are joined on a foreign key column that might contain nulls, it is important that you include in your design an attribute whose key column is the corresponding key in the lookup table. Without such an attribute, the OLAP server would have to issue a query to join the two tables during dimension processing. This makes processing slower; moreover, the default join that is created by the OLAP server would exclude any rows that contain nulls in the foreign key column. It is important to set the NullProcessing option on the key column of this attribute to UnknownMember. The reason is that, by default, nulls are converted to zeros or blanks when the engine processes attributes. This can be dangerous when you are processing a nullable foreign key. Conversion of a null to zero at best produces an error; in the worst case, the zero may be a legitimate value in the lookup table, thereby producing incorrect results.

To handle nullable foreign keys correctly, you must also set UnknownMember to Visible on the dimension. The Cube Wizard and Dimension Wizard currently set this property automatically; however, the Dimension Wizard lets you manually de-select the key attribute of snowflake tables. You must not deselect the key column if the corresponding foreign key is nullable.

If you do not want to browse the attribute that contains the lookup table key column, you can set AttributeHierarchyVisible to False. However, AttributeHierarchyEnabled must be set to True because it is necessary that all other attributes in the lookup table be directly or indirectly related to the lookup key attribute in order to avoid the automatic creation of new joins during dimension processing.

Note: You can also create a new record in look table (e.g. -1 as surrogate key) for NULL references.

Set RelationshipType property appropriately on AttributeRelationships based on whether the relationships between individual members change over time
The relationships between members of some attributes, such as dates in a given month or the gender of a customer, are not expected to change. Other relationships, such as SalesPeople in a given region or the Marital Status of a customer, are more prone to change over time. You should set RelationshipType to Flexible for those relationships that are expected to change and set RelationshipType to Rigid for relationships that are not expected to change. When you set RelationshipType appropriately, the server can optimize the processing of changes and re-building of aggregations.

Avoid using ErrorConfigurations with KeyDuplicate set to IgnoreError on dimensions
When KeyDuplicate is set to IgnoreError, it can be difficult to detect problems with incorrect key columns, incorrectly defined AttributeRelationships, and data consistency issues. Instead of using the IgnoreError option, in most cases it is better to correct your design and clean the data. The IgnoreError option may be useful in prototypes where correctness is less of a concern.

Consider creating user-defined hierarchies whenever you have a chain of related attributes in a dimension
Chains of related attributes usually represent an interesting navigation path for end-users, and defining hierarchies for these will also provide performance benefits.
Avoid creating user-defined hierarchies that do not have attribute relationships relating each level to the level above
Having attribute relationships between every level in a hierarchy makes the hierarchy strong and enables significant server optimizations.

Avoid creating diamond-shaped attribute relationships
A Diamond-shaped relationship refers to a chain of attribute relationships that splits and rejoins but contains no redundant relationships. For example, Day->Month->Year and Day->Quarter->Year have the same start and end points, but do not have any common relationships. The presence of multiple paths can create some ambiguity on the server. If preserving the multiple paths is important, it is strongly recommended that you resolve the ambiguity by creating user hierarchies that contain all the paths.

Consider setting AttributeHierarchyEnabled to False on attributes that have cardinality that closely matches the key attribute
When an attribute contains roughly one value for each distinct value of the key attribute, it usually means that the attribute contains only alternative identification information or secondary details. Such attributes are usually not interesting to pivot or group by. For example, the Social Security number or telephone number may be interesting properties to view, but there is very little value in being able to pivot and group based on SSN or telephone. Setting AttributeHierarchyEnabled to False on such attributes will reduce the complexity of the dimension for end-users and improve its performance.

Consider setting AttributeHierarchyVisible to False on the key attribute of parent-child dimensions
Because the members of the key attribute are also contained in the parent-child hierarchy in a more organized manner, it is usually unnecessary and confusing to the end-user to expose the flat list of members contained in the key attribute.

Avoid setting UnknownMember=Hidden
When you suppress unknown members, the effect is to hide relational integrity issues; moreover, because hidden members might contain data, results might appear not to add up. Therefore, we recommend that you avoid use of this setting except in prototype applications.

Use MOLAP storage mode for dimensions with outline calculations
Dimensions that contain custom rollups, semi-additive measures, and unary operators will perform significantly better using MOLAP storage. The following dimension types will also benefit from using MOLAP storage: an Account dimension in a measure group that contains measures aggregated using ByAccount; the first time dimension in a measure group that contains other semi-additive measures.

Use a 64 bit server if you have dimensions with more than 10 million members.
If a dimension contains more than 10 million members, using an x64 or an IA-64-based server is recommended for better performance.

Set the OrderBy property for time attributes and other attributes whose natural ordering is not alphabetical
By default, the server orders attribute members alphabetically, by name. This ordering is especially undesirable for time attributes. To obtain the desired ordering, use the OrderBy and OrderByAttributes properties and explicitly specify how you want the members ordered. For time-based attributes, there is frequently a date or numeric key column that can be used to obtain the correct chronological ordering.

Expose a DateTime MemberValue for date attributes
Some clients, such as Excel, will take advantage of the MemberValue property of date members and use the DateTime value that is exposed. When Excel recognizes the value as DateTime, Excel can treat the value as a date type and apply date functions to the value, as well as provide better formatting and filtering. If the key column is a single DateTime column and the name column has not been set, this MemberValue is automatically derived from the key column and no action is necessary.

Avoid setting IsAggregatable to False on any attribute other than the parent attribute in a parent-child dimension
Non-aggregatable attributes have non-all default members. These default members affect the result of queries whenever the attributes are not explicitly included. Because parent-child hierarchies generally represent the most interesting exploration path in dimensions that contain them, it is best to avoid having non-aggregatable attributes other than the parent attribute.

Do not combine unrelated business entities into a single dimension

Combining attributes of independent business entities, such as customer and product or warehouse and time, into a single dimension will not only create a confusing model, but also reduce query performance because auto-exist will be applied across attributes within the dimension.

Another way to state this rule is that the values of the key attribute of a dimension should uniquely identify a single business entity and not a combination of entities. Generally this means having a single column key for the key attribute.

Consider setting AttributeHierarchyVisible to False for attributes included in user-defined hierarchies

It is usually not necessary to expose an attribute in its own single level hierarchy when that attribute is included in a user-defined hierarchy. This duplication only complicates the end-user experience without providing additional value.

One common case in which it is appropriate to present two views of an attribute is in time dimensions. The ability to browse by [Month] and the ability to browse by [Month-Quarter-Year] are both very valuable. However, these two month attributes are actually separate attributes. The first contains only the month value such as “January” while the second contains the month and the year such as “January 2010”.

Avoid making an attribute non-aggregatable unless it is at the end of the longest chain of attribute relationships in the dimension

Non-aggregatable attributes have non-all default members that affect the result of queries in which values for those attributes are not explicitly specified. Therefore, you should avoid making an attribute non-aggregatable unless that attribute is regularly used. Because the longest chain of attributes generally represents the most interesting exploration path for users, it is best to avoid having non-aggregatable attributes in other, less interesting chains.

Consider creating at least one user-defined hierarchy in each dimension that does not contain a parent-child hierarchy

Most (but not all) dimensions contain some hierarchical structure to the data which is worth exposing in the cube. Frequently the Cube Wizard or Dimension Wizard will not detect this hierarchy. In these cases, you should define a hierarchy manually.

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)