My PASS Summit 2013 Presentations

I had the honor to speak for the first time at this past PASS Summit 2013 in Charlotte, NC. It was a great opportunity for me as this is one of the most prestigious nationwide conferences on SQL Server. I delivered 3 different sessions on Business Intelligence related topics. Attendance to my sessions exceeded my expectations and my overall experience as a first-time speaker was very positive. I also need to add that the God’s of the live demos were on my side.

My session titled PowerPivot + Power View = Self-service BI Revolution was full and had about 5 to 8 people standing in the back. Everybody got excited when I did a demo of the nice interactivity in Power View and 3D geographical capabilities of PowerMap.

The Lightning Talk room was packed and people had to be turned away. This room should be bigger next time as these Lightning Talks are very popular. Everybody accepted and agreed with my postulation on the need for Conformed Stored Procedures in addition to Conformed Dimensions and Facts. After the Lightning Talks were over, several attendees came forward to discuss further how Conformed Stored Procedures should be implemented. One attendee in particular, mentioned that he had been thinking about proposing a similar approach to his team and this talk helped him get re-assurance of this approach.

My last session on SSAS Hierarhcies Tips & Tricks was a half-day session on the last day of the summit between 2:00pm and 5:00pm. It was half-full for the first half of the session, and ended up about 1/4 full.  This drop-off is expected towards the end of the Summit as many people fly back home on Friday and usually leave around 4:00pm.

I’m very pleased with the turnout and feedback received. The audience in all of my sessions was very engaged and had a lot of great questions. I definitely look forward to speak at PASS Summit 2014 in Seattle, WA and at PASS BA Conference 2014 in San Jose, CA.

To download my presentations slides click on the links below:

1. Regular Session (75 minutes): 4798 PowerPivot + Power View = Self-Service BI Revolution
2. Lightning Talk Session (10 minutes): Slides: 5511 Conformed Stored Procedures Script: ConformedStoredProcs_SQLScript
3. Half-Day Session (3 hours): 4803 Fast Performing SSAS Hierarchies Tips and Tricks

Thanks to all the attendees and PASS team for this great opportunity. I hope I met or exceeded everyone’s expectations.

If you attended one of my presentations, please leave your comments and feedback!

Codeplex: SSIS Community Tasks and Components

I recently bumped into this very exhaustive list of SSIS tasks, components and samples in CodePlex http://ssisctc.codeplex.com. The list is maintained by SQL Server MVP (Blog|Twitter) and  grouped in the following sections:

• Tools
• Connection Managers
•  Log Providers
• Tasks (for Control Flow)
• Foreach Enumerators
• Script Task
• Script Samples
• Components (for Data Flow)
• Script Component Samples
•  Sources
• Transforms
• Destinations

I hope this list keeps being updated and more people contribute. Very useful.

Todo lo que debes saber sobre SSIS en 1 hora!

Gracias a lo asistentes de mi charla Todo lo que debes saber sobre SSIS en 1 hora! en el evento 24HOP LATAM. La verdad me agrado mucho presentar en espanol. Aun cuando mi primera lengua es espanol, todas las presentaciones tecnicas en el pasado las he realizado en ingles.

Si deseas ver la presentacion PowerPoint puedes bajarla dando click en la imagen a continuacion.

Tambien puedes bajar el proyecto que utilize en mi presentacion dando click en la imagen a continuacion. Nota: Solo podras utilizar la base de datos y proyecto en SQL Server Denali, no servira en SQL Server 2008 o 2005.

Differences between Merge and Union All transformations in SSIS

 What are the differences between Merge and Union All transformations in SSIS ?

 The first and most obvious difference is that Merge can only accept two datasets while Union All can accept more than two datasets for input. The second difference is that Merge requires both datasets to be sorted while Union All does not require sorted datasets. Both transformations are considered to be partially-blocking.

As can be seen on the following image, Union All can accept more than two datasets as input while Merge is limited to only two input datasets. Also, notice that for the Merge transformation, the datasets need to be sorted before they can be accepted as valid input.

For simplification purposes,  a Sort transformation was used. Keep in mind that the Sort transformation is a full blocking operation because it needs to read all the rows in the data flow (buffer) in order to sort the data. No data will be sent downstream until all rows have been read first by the Sort transformation. A better approach would be to sort the datasets with an ORDER BY T-SQL clause at the data source component.

SSIS errors: Bulk Load failed. Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)

When working with the Bulk Insert Task in SSIS 2008 you may get the following error:

 The complete error message is:

[Bulk Insert Task] Error: An error occurred with the following error message: “Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)”.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.”

This error description can make you jump through hoops trying to figure out why is it detecting an error with the OLE DB provider or why does SSIS thinks you are trying to execute this operation on a linked server?

The real issue here has nothing to do with the first  sentence in the error description. Sentence 4 gives you the actual error:

Verify that the field terminator and row terminator are specified correctly.

You may be experiencing this error due to one or more of the following 3 reasons:

1) You may be specifying a wrong CommonDelimiter for your source file.
For example, You may be trying to do a Bulk Insert operation from a Comma Separated Value (CSV) file but did not change the CommonDelimiter property to Comma {,}. When you drag in the Bulk Insert Task the CommonDelimiter property default value is Tab.

2) You may be specifying a wrong RowDelimiter for your source file.
For example, you may be trying to do a Bulk Insert operation from a Comma Separated Value (CSV) file whose row delimiter character is different than the RowDelimiter property default value of {CR}{LF}. In some cases, you may receive a file with a very long stream of text with no Carriage Return (\r)  & Line Feed (\n) characters, commonly denoted as {CR}{LF} in between rows. These hidden {CR}{LF} row delimiter characters are placed on a text file each time you hit the ENTER key on your keyboard denoting the end of a row and beginning of the next row. You may read a little more about the Carriage Return and Line Feed characters in Pinal Dave’s blog: http://blog.sqlauthority.com/2009/07/01/sql-server-difference-between-line-feed-n-and-carriage-return-r-t-sql-new-line-char/

As seen on the image below, there are two properties,  CommonDelimiter and RowDelimiter, that you need to make sure  you specify the correct values for depending on your input or source file format:

.
3) You may be using a format file with an incorrect or invalid format defined.
Format files can be non-xml, commonly with an *.fmt extension or for SQL Server 2005 and later only you can also use xml format files. For more information about format files read MSDN Books on Line http://msdn.microsoft.com/en-us/library/ms191516.aspx

If you are using a format file make sure you are pointing to the right format file or that the format defined in your format file is correct. (Notice that the Format property value changes from Specify to Use File):

Phoenix User Group Presentation: 0 to SSIS

Thanks to the Phoenix User Group for hosting me tonight! The presentation went smooth and had good questions at the end. I will present anytime you need me and hopefully make it to Phoenix sometime soon.

Cheers my fellow Phoenix friends!

Phoenix User Group 2/9/2011 meeting attendees

Presenting at inaugural meeting for Wheeling, West Virginia User Group

I will be presenting my “Learning SSIS under 1 hour” session over live meeting for the Wheeling, West Virginia User Group on Thursday 1/20/2011. This meeting will kick-off the first meeting for the local user group put together by John Sterrett (Blog | Twitter). More details can be found at:

http://johnsterrett.com/2011/01/06/new-sql-user-group-in-wheeling-west-virginias-first-meeting-is-january-20th/

Business Intelligence: Decaffeinated Please!

For many Database Administrators, Data Analysts and other IT and Business Professionals, Business Intelligence (BI) and Data Warehousing (DW) may be a new and uncharted territory with no clear path towards the destination. Others, who have already jumped head-on into the Business Intelligence journey, may be facing challenges that can potentially put their efforts at the risk of failure. For this reason I put together a presentation titled Business Intelligence: Decaffeinated Please!

In this presentation I cover terms and acronyms associated with Business Intelligence and Data Warehousing. I make a differentiation of what Business Intelligence is and what it is not. I also go over my “10 Rules of Wisdom” towards BI success based on lessons learned from personal experience as well as from insight gained from leading authors and speakers in the BI universe. The purpose of my 10 Rules of  Wisdom is to serve as a guideline for anyone involved in a BI initiaive or in its planning stages.

My “10 Rules of  Wisdom” for Business Intelligence Success are listed below:

1. The Business Intelligence solution needs strong executive management support. Keyword: Sponsor
2. The Business Intelligence solution must add value to the organization and be trustworthy.
3. Always have a Business Intelligence roadmap in clear view.
4. Define iterative success criteria.
5. Choose the right technology based on your business and user needs.
6. The BI solution needs to be understandable and documented.
7. The BI solution needs to be accessible.
8. The BI solution must be able to grow according to the business needs.
9. The BI solution needs to adapt to new business analysis needs.
10. Partner with experts in the field to help you achieve your goals.

These and other topics can be found on my PowerPoint presentation below which I continuosly enhance for my presentations for SQL Saturdays and User Groups.

 (click on image to download)

Slides updated 3/24/2011

Tuning the Performance of Change Data Capture in SQL Server 2008

Great SQL Server Best Practices Article on performance considerations for Change Data Capture:

http://msdn.microsoft.com/en-us/library/dd266396.aspx

Change Data Capture as a tool for Business Intelligence, Disaster Recovery, Auditing, and more

This is a summary of the Change Data Capture presentation covered in my sessions at SQL Saturday #38 – Jacksonville and SQLSaturday #40 – South Florida.

Presentation Summary
Change Data Capture (CDC) is one of the new features available for SQL Server 2008 Enterprise and Developer Editions, which allows you to easily track and keep history of all DML (Data Manipulation Language) changes on user tables such as INSERTs, DELETEs, and UPDATEs with little or no overhead. Change Data Capture has been used primarily to track changes for incremental loads using SSIS for the ETL process in Data Warehousing and Business Intelligence because it allows you to identify new data and data that changed or was deleted since last incremental load.

Before Change Data Capture in SQL Server 2005 and prior versions, identifying new or changed data required some level of custom programming, triggers or third party tools. These solutions work well but introduce additional overhead to your production systems. This is the main reason why Change Data Capture was introduced in SQL Server 2008; to eliminate overhead while tracking new and changed data.

But Change Data Capture use is not limited as a tool for ETL Process. It can be used for many other purposes where overhead, simplicity and cost might impose a restriction.  Some of these scenarios include:

1) You are a developer adding or modifying existing code and you need to see the difference in the data output on a particular table.
2) You are a DBA and need to audit several tables in a database and need to report which data was changed, what were the new and previous values, who did the change and when was the change made.
3) You were tasked to delete several records on a highly transactional production database and unintentionally deleted records that you were not supposed to with no possibility of rolling back the changes (no explicit transaction) and no possibility of restoring a backup.
4) You are a DBA and need to know which tables and columns are being written to the most in your database.
5) You are a DBA and need to know how many new records are being entered into your database in a period of time and the percentage of change of existing records.

In most cases you would want to know what changed and which were the values before the change.

 One of the most important benefits of this feature is that it allows you to see the data before & after an UPDATE or DELETE statement, which in turn allows you to query and recover data overwritten or deleted quickly without the need of a database restore. It also captures each new row inserted.

CDC accomplishes this task by reading the committed operations from the log file and inserting the changed records in a tracking table that mirrors the source (tracked) table. The records inserted in this tracking table contain both the value before the change and the value after the change along with the metadata associated with the change. The metadata can be queried to identify the type of DML operation as follows:

1 = Delete
2 = Insert
3 = Update (record’s value before update)
4 = Update (record’s value after update)

More concepts are covered in my PowerPoint presentation which you can download here or by clicking slide below.