October Free Training from Pragmatic Works

Speaker: John Welch

Topic:

 Migrating ETL Processes From Informatica to Integration Services

Date:  October 2, 2012, 11:00AM EDT

Informatica and SSIS offer similar functionality for moving data around your organization, but scratch below the surface and you will find some big differences in how these tools approach common ETL problems. If you are interested in migrating ETL processes to Integration Services, then this webinar will be highly beneficial. We will cover some common patterns found in Informatica, and explain how to get the same results from SSIS. For more advanced Informatica scenarios, we will also cover the use of PragmaticWorks’ Task Factory components to fill in some of the gaps in Integration Services.

Speaker: Jody Roberts

Topic: SQL Server Distributed Replay
Date: October 4, 2012, 11:00AM EDT

The Microsoft SQL Server Distributed Replay feature helps you assess the impact of future SQL Server upgrades. You can also use it to help assess the impact of hardware and operating system upgrades, and SQL Server tuning. Similar to SQL Server Profiler, you can use Distributed Replay to replay a captured trace against an upgraded test environment. Unlike SQL Server Profiler, Distributed Replay is not limited to replaying the workload from a single computer .Distributed Replay offers a more scalable solution than SQL Server Profiler. With Distributed Replay, you can replay a workload from multiple computers and better simulate a mission-critical workload. The Microsoft SQL Server Distributed Replay feature can use multiple computers to replay trace data and simulate a mission-critical workload. Use Distributed Replay for application compatibility testing, performance testing, or capacity planning. In this session we will cover the following: what is SQL Server Distributed Replay, Distributed Replay concepts , benefits of Distributed Replay, when to use Distributed Relay.

Speaker: Brad Schacht

Topic: SharePoint for Business Intelligence
Date:  October 9, 2012, 11:00AM EDT

Are you aware of the many features SharePoint has to offer the Business Intelligence world? In this session, you'll get an overview of SharePoint 2010 and how to integrate it with your current or new BI infrastructure. We'll discuss the general use of SharePoint for BI and basic setup and installation of the farm, then fly over a completed SharePoint site. You'll tour SharePoint Central Administration, learning how to allow your site to take advantage of many BI features such as PowerPivot, Excel Services, and Reporting Services. With the release of SQL Server 2012, you'll also get a sneak peak at some improvements available in SharePoint for those who want to be on the cutting edge of this growing technology. Forget the slides – this tour will be all demo!

Speaker: Sanil Mhatre

Topic: Understanding Parameter Sniffing & Making It Work For You
Date:  October 11, 2012, 11:00AM EDT

Parameter Sniffing is usually thought of as the bad guy, in association with a performance problem in your database. Contrary to the popular belief, Parameter Sniffing is usually the good guy, continuously working under the hood to help your database applications run faster. However, it can sometimes go wrong, causing severe performance degradation of your queries. In this session we will discuss the workings of Parameter Sniffing and demonstrate how it helps improve the performance of your database applications. We will also explore how Parameter Sniffing can go wrong and its impact. Several ways to fix bad Parameter Sniffing will be demonstrated to help make an appropriate choice for your scenario.

Speaker: Adam Jorgensen

Topic: Roping the Elephant - Big Data and BI Live
Date: October 16, 2012, 11:00AM EDT

Have you been wondering how you can sit down at work and use Hadoop to make your BI better? Have you thought about what Big Data could mean to your firm? Come watch Adam setup, populate, consume and integrate data from a Big Data Platform into a SQL BI solution Live!

Speaker: Mike Davis

Topic: Informatica vs. SSIS: Smack Down
Date: October 18, 2012, 11:00AM EDT

In this webinar you will see how to load a type 1 and type 2 dimension with SSIS and Informatica. Learn the pros and cons of each tool and see them in action. Learn how to use the data flow transforms in SSIS to perform delta load and see the equivalent in Informatica. You will also learn some of the best practices applied in both tools.

Speaker: Kathi Kellenberger

Topic: New T-SQL Features
Date: October 23, 2012, 11:00AM EDT

The SQL Server 2012 release has something for everyone: Column Store Index, AlwaysOn Availability Groups, Power View and more. I bet you haven’t heard about all the great new T-SQL features! Attend t

Free ebook: Introducing Microsoft SQL Server 2012

Introducing Microsoft SQL Server 2012 includes 10 chapters:

PART I   DATABASE ADMINISTRATION (by Ross Mistry)

1.   SQL Server 2012 Editions and Engine Enhancements

2.   High-Availability and Disaster-Recovery Enhancements

3.   Performance and Scalability

4.   Security Enhancements

5.   Programmability and Beyond-Relational Enhancements

PART II   BUSINESS INTELLIGENCE DEVELOPMENT (by Stacia Misner)

6.   Integration Services

7.   Data Quality Services

8.   Master Data Services

9.   Analysis Services and PowerPivot

10.   Reporting Services

Click here to download. 

Top 10 Features of SQL Server 2012

Microsoft has introduced SQL Server 2012 to the world and it's time for IT professionals to start to come to speed on what's new.

Microsoft has introduced SQL Server 2012 to the world and it's time for IT professionals to start to come to speed on what's new in this highly anticipated version of SQL Server.

1. AlwaysOn Availability Groups -- This feature takes database mirroring to a whole new level. With AlwaysOn, users will be able to fail over multiple databases in groups instead of individually. Also, secondary copies will be readable, and can be used for database backups. The big win is that your DR environment no longer needs to sit idle.

2. Windows Server Core Support -- If you don't know what Windows Server Core is, you may want to come up to speed before Windows 8 (MS is making a push back to the command line for server products). Core is the GUI-less version of Windows that uses DOS and PowerShell for user interaction. It has a much lower footprint (50% less memory and disk space utilization), requires fewer patches, and is more secure than the full install. Starting with SQL 2012, it is supported for SQL Server.

3. Columnstore Indexes -- This a cool new feature that is completely unique to SQL Server. They are special type of read-only index designed to be use with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries.

4. User-Defined Server Roles -- DBAs have always had the ability to create custom database role, but never server wide. For example, if the DBA wanted to give a development team read/write access to every database on a shared server, traditionally the only ways to do it were either manually, or using undocumented procedures. Neither of which were good solutions. Now, the DBA can create a role, which has read/write access on every DB on the server, or any other custom server wide role.

5. Enhanced Auditing Features -- Audit is now available in all editions of SQL Server. Additionally, users can define custom audit specifications to write custom events into the audit log. New filtering features give greater flexibility in choosing which events to write to the log.

6. BI Semantic Model -- This is replacing the Analysis Services Unified Dimensional Model (or cubes most people referred to them). It's a hybrid model that allows one data model will support all BI experiences in SQL Server. Additionally, this will allow for some really neat text infographics

7. Sequence Objects -- For those folks who have worked with Oracle, this has been a long requested feature. A sequence is just an object that is a counter -- a good example of it's use would be to increment values in a table, based a trigger. SQL has always had similar functionality with identity columns, but now this is a discrete object.

8. Enhanced PowerShell Support -- Windows and SQL Server admins should definitely start brushing up on their PowerShell scripting skills. Microsoft is driving a lot of development effort into instrumenting all of their server-based products with PowerShell. SQL 2008 gave DBAs some exposure to it, but there are many more in cmdlets in SQL 2012.

9. Distributed Replay -- Once again this is answer to a feature that Oracle released (Real Application Testing). However, and in my opinion where the real value proposition of SQL Server is, in Oracle it is a (very expensive) cost option to Enterprise Edition. With SQL, when you buy your licenses for Enterprise Edition, you get everything. Distributed replay allows you to capture a workload on a production server, and replay it on another machine. This way changes in underlying schemas, support packs, or hardware changes can be tested under production conditions.

10. PowerView -- You may have heard of this under the name "Project Crescent" it is a fairly powerful self-service BI toolkit that allows users to create mash ups of BI reports from all over the Enterprise.

SQL 2012 is a big step forward for Microsoft -- the company is positioning itself to be a leader in availability and in the growing area of big data. As a database professional, I look forward to using SQL 2012 to bring new solutions to my clients.

Twelve Days of SQL 2012 Webinar Series

Pragmatic works is coming with a series of 12 webinars from April to May.

Below are details of all the webinars:

SQL Server 2012 Overview-What's New

April 04, 2012 | 2:00 PM Eastern Daylight Time (UTC - 4 hours), 1 hour

AlwaysOn SQL Server Failover Cluster Instances and Availability Groups

April 05, 2012 | 2:00 PM Eastern Daylight Time (UTC - 4 hours), 1 hour

Power View (SSRS)

April 11, 2012 | 2:00 PM Eastern Daylight Time (UTC - 4 hours), 1 hour

Reporting Builder 3.0 and Reporting Services

April 12, 2012 | 2:00 PM Eastern Daylight Time (UTC - 4 hours), 1 hour

Data Quality Services

April 18, 2012 | 2:00 PM Eastern Daylight Time (UTC - 4 hours), 1 hour

Analysis Services - Tabular Modeling (SSAS)

April 19, 2012 | 2:00 PM Eastern Daylight Time (UTC - 4 hours), 1 hour

PowerPivot 2.0 for SharePoint 2010 (SSAS)

April 25, 2012 | 2:00 PM Eastern Daylight Time (UTC - 4 hours), 1 hour

What's New in Integration Services

April 26, 2012 | 2:00 PM Eastern Daylight Time (UTC - 4 hours), 1 hour

Master Data Services

May 02, 2012 | 2:00 PM Eastern Daylight Time (UTC - 4 hours), 1 hour

Data Mining

May 03, 2012 | 2:00 PM Eastern Daylight Time (UTC - 4 hours), 1 hour

What's New in SQL Server FastTrack 4.0

May 09, 2012 | 2:00 PM Eastern Daylight Time (UTC - 4 hours), 1 hour

Microsoft Data Warehousing — 5 TB to 1 Petabyte Solutions

May 10, 2012 | 2:00 PM Eastern Daylight Time (UTC - 4 hours), 1 hour

Click here to register.

Performance Tips and Tricks with SQL Server, real cases with developers

Date & Time :     3/22/2012 11:00:00 AM

Presenter: Nabeel Derhem

Application developers don't care much about how SQL Server executes their queries as long as they get what they want. This session introduces common real case scenarios where developers end up with troublesome queries and bad performance. Explanation is linked to a discussion of alternative effective ways to handle such scenarios.



Click Here to Register

Extending Reporting Services With Custom Code

SQL Server Reporting Services is capable of creating eye-popping visualizations that can be consumed by a variety of business users. The tool has built-in functionality for developers to produce reports that users will find not only functional, but also visually appealing. This whitepaper will focus on how to overcome the gaps that expressions leaves by using custom code. You will also learn many other advantages of using custom code like creating consistency across all reports developed. As you read this paper, you will walk through many examples of using custom code. These examples will emphasize the use of custom code, and will therefore skip topics like creating data sources, datasets and basic report toolbox items.

Click Here to Download

Defensive Database Programming By Alex Kuznetsov

Why read this book?

Resilient T-SQL code is code that is designed to last, and to be safely reused by others. The goal of defensive database programming, the goal of this book, is to help you to produce resilient T-SQL code that robustly and gracefully handles cases of unintended use, and is resilient to common changes to the database environment.

Too often as developers, we stop work as soon as our code passes a few basic tests to confirm that it produces the 'right result' in a given use case. We do not stop to consider what other possible ways in which the code might be used in the future, or how our code will respond to common changes to the database environment, such as a change in the database language setting, or a change to the nullability of a table column, and so on.

In the short-term, this approach is attractive; we get things done faster. However, if our code is designed to be used for more than just a few months, then it is very likely that such changes can and will occur, and the inevitable result is broken code or, even worse, code that silently starts to behave differently, or produce different results. When this happens, the integrity of our data is threatened, as is the validity of the reports on which critical business decisions are often based. At this point, months or years later, and long after the original developer has left, begins the painstaking process of troubleshooting and fixing the problem.

Would it not be easier to prevent all this troubleshooting from happening? Would it not be better to spend a little more time and effort during original development, to save considerably more time on troubleshooting, bug fixing, retesting, and redeploying?

This is what defensive programming is all about: we learn what can go wrong with our code, and we proactively apply this knowledge during development. This book is filled with practical, realistic examples of the sorts of problems that beset database programs, including:

• Changes in database objects, such as tables, constraints, columns, and stored procedures.
• Changes to concurrency and isolation levels.
• Upgrades to new versions of SQL Server.
• Changes in requirements.
• Code reuse.
• Problems causing loss of data integrity.
• Problems with error handling in T-SQL.

In each case, it demonstrates approaches that will help you understand and enforce (or eliminate) the assumptions on which your solution is based, and to improve its robustness. Ultimately, the book teaches you how to think and develop defensively, and how to proactively identify and eliminate potential vulnerabilities in T-SQL code.

Click here to Download

Inside SQL Server Optimizer Book

Here is a book from Redgate, Inside SQL Server optimizer by Benjamin Nevarez.

Here's a quick overview of what the book covers:

• Chapter 1, Execution Engine, Introduction to Query Optimization, starts with an overview on how the SQL Server Query Optimizer works, and introduces the concepts that will be covered in more detail in the rest of the book. A look into some of the challenges query optimizers still face today is covered next, along with a section on how to read and understand execution plans. The chapter closes with a discussion of join ordering, traditionally one of the most complex problems in query optimization.
• Chapter 2 talks about the Execution Engine, describing it as a collection of physical operators that perform the functions of the query processor. It emphasizes how these operations, implemented by the Execution Engine, define the choices available to the Query Optimizer when building execution plans. This chapter includes sections on data access operations, the concepts of sorting and hashing, aggregations, and joins, to conclude with a brief introduction to parallelism.
• Chapter 3, Statistics and Cost Estimation, shows how the quality of the execution plans generated by the Query Optimizer is directly related to the accuracy of its cardinality and cost estimations. The chapter describes Statistics objects in detail, and includes some sections on how statistics are created and maintained, as well as how they are used by the Query Optimizer. We'll also take a look at how to detect cardinality estimation errors, which may cause the Query Optimizer to choose inefficient plans, together with some recommendations on how to avoid and fix these problems. Just to round off the subject, the chapter ends with an introduction to cost estimation.
• Chapter 4, Index Selection, shows how SQL Server can speed up your queries and dramatically improve the performance of your applications, just by using the right indexes. The chapter shows how SQL Server selects indexes, how you can provide better indexes, and how you can verify your execution plans to make sure these indexes are correctly used. We'll talk about the Database Engine Tuning Advisor and the Missing Indexes feature, which will show how the Query Optimizer itself can provide you with index tuning recommendations.
• Chapter 5, The Optimization Process, goes right into the internals of the Query Optimizer and introduces the steps that it performs without you ever knowing. This covers everything, from the moment a query is submitted to SQL Server, until an execution plan is generated and ready to be executed, including steps like parsing, binding, simplification, trivial plan, and full optimization. Important components which are part of the Query Optimizer architecture, such as transformation rules and the memo structure, are also introduced.
• Chapter 6, Additional Topics, includes a variety of subjects, starting with the basics of update operations, and how they also need to be optimized just like any other query, so that they can be performed as quickly as possible. We'll have an introduction to Data Warehousing and how SQL Server optimizes star queries, before launching into a detailed explanation of parameter sniffing, along with some recommendations on how to avoid some problems presented by this behavior. Continuing with the topic of parameters, the chapter concludes by discussing auto-parameterization and forced parameterization.
• Chapter 7, hints, and warns that, although hints are a powerful tool which allow you to take explicit control over the execution plan of a query, they need to be used with caution, and only as a last resort when no other option is available. The chapter covers the most used hints, and ends with a couple of sections on plan guides and the USE PLAN query hint.

Its a very good book to understand a lot of features to enhance performance & also get a hands on what's happening in background. 

Click here to download this book.

SSIS Expression Cheat Sheet

SSIS Expression Cheat Sheet
Problems	Expression
Create a file name with today's date	Expression on the Flat File or File Connection Manager: "C:\\Project\\MyExtract" + (DT_WSTR, 30)(DT_DBDATE)GETDATE() + ".csv" Expression Output Example: C:\Project\MyExtract2009-03-20.csv
Use a 2 digit date (ex. "03" for March instead of "3")	RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()),2) Expression Output: 03 (if the month is March)
Multiple condition if statement	In this example, the statement determines that if the ColumnName is blank or NULL, it will be set to unknown. To make a Logical AND condition, use "&&" instead of the "||" operator. ISNULL(ColumnName)||TRIM(ColumnName)==""?"Unknown": ColumnName
Returns the first five characters from a zip code	Derived Column Transform in the Data Flow: SUBSTRING(ZipCodePlus4,1,5)
Remove a given character from a string (ex. Remove "-" from a social security number)	Derived Column Transform in the Data Flow: REPLACE(SocialSecurityNumber, "-","")
Uppercase data	Derived Column Transform in the Data Flow: UPPER(ColumnName)
Replace NULL with another value	Derived Column Transform in the Data Flow: ISNULL(ColumnName)?"New Value" : ColumnName
Replace blanks with NULL values	Derived Column Transform in the Data Flow: TRIM(ColumnName)=="" ? (DT_STR, 4, 1252)NULL(DT_STR, 4, 1252) : ColumnName
Remove any non-numeric data from a column	Script Transform in the Data Flow Task with the code as follows (VB 2008): Imports System.Text.RegularExpressions Public Overrides Sub Input()_ProcessInputRows(ByVal Row As Input()Buffer) If Row.ColumnName_IsNull = False Or Row.ColumnName = "" Then Dim pattern As String = String.Empty Dim r As Regex = Nothing pattern = "[^0-9]" r = New Regex(pattern, RegexOptions.Compiled) Row.ColumnName = Regex.Replace(Row.ColumnName, pattern, "") End If End Sub
Convert text to proper case (ex. 1st letter in each word is uppercase)	Script Transform with the line of partial code as follows: Row.OutputName = StrConv(Row.InputName, VBStrConv.ProperCase)
Build dynamic SQL statement	Expression on the SQLStatementSource property of Execute SQL Task: "SELECT Column From " + @[User::TableName] +WHERE DateFilterColumn = '" + (DT_WSTR,4)YEAR(@[User::DateTimeVar]) + RIGHT("0" + (DT_WSTR,2)MONTH(@[User::DateTimeVar]),2) + RIGHT("0" + (DT_WSTR,2)DAY(@[User::DateTimeVar]),2) + "'" Expression Output:SELECT Column FROM MyTable WHERE DateFilerColumn = '20060915'
Calculate beginning of the previous month	Expression on component or task: (DT_DATE)(DT_DBDATE)DATEADD("dd",-1*(DAY(GETDATE())-1),DATEADD("month", -1, GETDATE()))
Round to the nearest two decimal mark	Expression on Derived Column Transform: ROUND(YourNumber, 2) Expression Output Example: 1.2600000

SSRS Expression Cheat Sheet

SSRS Expression Cheat Sheet
Problems	Expression
Return first day of current Week (ex. Default Start Date parameter to return WTD)	Expression on the parameter default value: =DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today) Expression Output Example: 11/7/2010 12:00:00 AM
Return first day of current Month (ex. Default Start Date parameter to return MTD)	Expression on the parameter default value: =DateAdd("d",-(Day(today)-1), Today) Or =DateSerial( year(today()), month(today()), 1) Expression Output Example: 11/1/2010 12:00:00 AM
Return first day of current year (ex. Default Start Date parameter to return YTD)	Expression on the parameter default value: =DateAdd("d",-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today) Expression Output Example: 1/1/2010 12:00:00 AM
Return period over period (ex. Default date parameters to a rolling year)	Expression on the parameter default value: Week over Week =DateAdd("ww",-1, Today) Month over Month =DateAdd("m",-1,Today) Year over Year =DateAdd("yyyy",-1, Today) Expression Output Example: 10/9/2010 12:00:00 AM
Return current month name	Expression in Text Box: =MonthName(Month(Today())) Expression Output Example: November
Uppercase fields	Expression in Text Box: =UCASE(Fields!FieldName.Value) Expression Output Example: NOVEMBER
Convert text to proper case (ex. 1st letter in each word is uppercase)	Expression in Text Box: =StrConv(Fields!FieldName.Value, VbStrConv.ProperCase)
Replace NULL with another value	Expression in Text Box: =iif(Fields!FieldName.Value = nothing, "No Value",Fields! FieldName.Value)
Alternating row color (Banding effect)	BackgroundColor property on Text Box: =iif(RowNumber(Nothing) Mod 2 = 0, "Silver", "White")
Handling division by zero	Expression in Text Box: =iif(Fields!DenominatorField.Value = 0, 0, Fields!NumeratorField.Value/ iif(Fields!DenominatorField.Value = 0, 1, Fields! DenominatorField.Value))
Remove a given character from a string (ex. Remove “-“ from a social security number)	Expression in Text Box: =Replace(Fields!EmailAddress.Value,"-","")

SSIS Data flow or T-SQL

Advantages of using Stored Procedures

• A SQL statement will outperform a SSIS data-flow when the data transform is table-to-table on the same server
• No new learning curve as ETL developers will already know how to write SQL code. SSIS data-flows are a completely new technology.
• Utilise transactions within the database rather than use MSDTC
• Easier to generate stored procedures from known metadata than it is with a data-flow (at the time of writing it is anyway)

Advantages of using a SSIS data-flow

• Handle data from heterogenous sources in the same place
• Consume data from sources that can't be accessed using a SQL statement
• Data can be transformed without the need for an intermediate staging area
• If data does need to be persisted temporarily it can be to a raw file whereas T-SQL requires temporary tables for which there may be a management/security overhead
• Extra transformation functionality (e.g. Fuzzy logic, data mining, text mining, insert to Analysis Services)
• Visual representation of the "work"
• Bad data can be captured to a different data sink for examination later
• Exception Handling
• Use .Net Data providers as an alternative to OLE DB Providers (e.g. mySAP Business Suite .Net Data Provider)
• Data-flows are, to some extent, self-documenting
• User-controlled parallel execution of data-flows is possible where it isn't really in the inherent batch operated world of stored procedures
• "Heavy-lifting" of data can occur on a different to machine to that storing the data (thanks to John in the comments section)
• By default, events containing very pertinent information such as "component "" (5824)" wrote 2398156 rows" are raised