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!

.Net Code Camp Orlando 2013

This past Saturday March 16th, I had the opportunity to speak once again the .Net Orlando Code Camp.  I presented a session under the SQL Server track titled “Change Data Capture, a developer’s best unknown friend”.  I had better than expected turn-out with about 20 attendees and overall very good participation.

I was glad to hear from more than one attendee that they were planning to use Change Data Capture in their environment and best of all to get rid of triggers!

I had the opportunity to catch-up with some of my friends and fellow SQL-ers from PASS. Also, met some new folks from the App/Dev community. These type of events are always great to expand your network, share your knowledge and learn something new!

Thanks to the organizers, sponsors, volunteers and attendees to make this event a success and for having me once more.

You can download my PowerPoint presentation and demo files at: http://sdrv.ms/15UUX64

The affordable self-service BI revolution has started.

I am officially announcing that I have now become an independent consultant and business owner. Becoming independent has always been my dream and I have now decided to follow that dream. I believe it is the right time to do it for my family and myself. It’s time to soar!

Why did I leave Pragmatic Works?

Simple. I wanted to follow my career goals and carve my own path. It was a hard decision to leave Pragmatic Works as I really enjoyed the work environment and had great relationship with the top brass Brian Knight, Adam Jorgensen, and Sam Washburn as well as my co-workers. The folks at Pragmatic Works gave me a lot of opportunities to learn and grow for which I am forever grateful. It is a great company to work for and highly recommend working for them. I would return to work for them in a heartbeat.

What are my plans?

First of all, I have created a corporation, called Agile Bay, Inc. (http://www.agilebay.com) through which I will be providing Business Intelligence consulting services, Software Development, Training and Staffing services. I will also be doing contract work and hiring BI and Software developers to take on some of these jobs.

My plan is to focus on self-service Business Intelligence solutions using the Microsoft toolset, including:

• SQL Server Database Services
• Analysis Services
• Reporting Services
• Master Data Services
• Data Quality Services
• SharePoint
• Performance Point
• PowerPivot
• PowerView
• & more…

I will continue leading, volunteering and sharing my knowledge in the SQL Server and Business Intelligence community and in PASS.

Enter the affordable self-service BI revolution

The new release of SQL Server 2012 will bring a lot to offer in the BI space. I truly believe it will be a game changer and many companies will embark in new Data Warehouse, Business Intelligence and Master Data Management projects. Some of these projects may have been put on hold due to licensing and development costs. This is where SQL Server 2012 will shine as it introduces lower BI licensing costs with the new SQL Server Business Intelligence Edition. Organizations will no longer require Enterprise Edition to do all the cool things Microsoft has to offer with their BI suite.

Additionally, the shift to the BI Semantic Model and the focus on self-service BI will open a lot of doors to consulting firms with lower overhead like mine. Enter the affordable self-service BI revolution!

Hitting the ground running

I am very grateful to the fact that the word has spread out among my immediate professional network and have already been booked for projects and contract work several months ahead.

I have tried not to market myself or my company too much during this initial phase as I want to manage my growth more organically. I am in the process of hiring BI developers at all levels, so if you are interested feel free to contact me via http://www.agilebay.com/#!contact.

 Mission

My mission is to empower individuals and organizations through the Microsoft Business Intelligence Toolset.

Vision

My vision is to be the catalyst and leader of the affordable self-service Business Intelligence revolution.

Value proposition

There are many alternatives available when chosing a consulting firm to help you achieve a succesful Business Intelligence, Data Warehouse and Master Data Management implementation.  My value proposition is to achieve this same success at an affordable budget and by providing you with the knowledge transfer and mentoring needed to continue your own development efforts.

The sales pitch

 You need an experienced and expert professional to help you with your design and development efforts.  I have experience and expertise. Let’s talk.

You may contact me through my company’s website at http://www.agilebay.com/#!contact or through email: info@agilebay.com. 

I’m also available for any quick help through email at jchinchilla@sqljoe.com. Make sure to check out my blog at http://www.sqljoe.com as well.

Afterthoughts: IT Pro Camp Orlando 2012

This past Saturday January 21, 2012 I had the opportunity to speak at my first IT Pro Camp in Orlando, FL. This event was put together by Blain Barton ( Blog | LinkedIn | Twitter ), Microsoft Senior IT Pro Evangelist and Chad Miller ( Blog | LinkedIn | Twitter ), Microsfot MVP and Senior Manager of Database Administration at Raymond James Financial.

Event

The event went very smooth and had a good turnaout. Food was amazing. They reallly outdid themselves with good old southern BBQ.

Presentation

I presented a session titled Introduction to Microsoft Business Intelligence (Slides), focusing on the basic concepts of BI and Datawarehousing, the Microsoft toolset and my Top 5 Rules of Wisdom for succesful BI. I had a great turnaout, about 25 attendees. All of the attendees were very engaged and asked excellent questions. I enjoyed presenting to the Orlando crowd.

Picture credits: Russel Faustino. See more pictures of the event here.

Other IT Pro Camps are being added. Check the IT Pro Camp website here to keep up to date.

Click on the picture below to download my Intorduction to Business Intelligence powerpoint presentation.

 

 

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.

SQL Server Codename “Denali” CTP3 Resources

SQL Server codename “Denali” CTP3 has been released to the public after much anticipation. You may ask yourself, “What happened to CTP2?” The answer is simple. It was a private build for MVPs and some Microsoft partners. By private, I mean it was not released to the general public. The good news is that you did not miss much in CTP2. A lot of areas were incomplete or not working. But that is to be expected as it is a work in progress.

This blog post will serve as a means to gather resources such as links and blog posts regarding SQL Server “Denali” CTP3. Check back soon as I will be adding new resources. If you have a blog post about SQL Server Denali CTP3 please pingback or email me to add it to the list.

Downloads:

SQL Server DENALI CTP3 Demo VHD
http://www.microsoft.com/download/en/details.aspx?id=27253

A HyperV image of SQL Server Denali CTP3 in action, including fully configured services and integration with SharePoint 2010 and Office 2010
The following software is configured on the virtual machine:

• SQL Server “Denali” CTP3
• SharePoint 2010
• Office 2010

SQL Server codename “Denali” Community Technology Preview 3 CTP3
https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/default.aspx

SQL Server code name “Denali” Express Core Community Technology Preview 3 (CTP 3)
http://www.microsoft.com/download/en/details.aspx?id=26784

SQL Server code name ‘Denali’ Community Technology Preview 3 (CTP 3) Feature Pack
http://www.microsoft.com/download/en/details.aspx?id=26726

The SQL Server  code name “Denali” CTP 3 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft® SQL Server® code name ‘Denali’ CTP 3. It includes the latest versions of  tool and components an add-on providers.

Includes:

• SQL Servercode name “Denali” Master Data Service Add-in for Excel CTP 3
• SQL Servercode name “Denali” Semantic Language Statistics CTP 3
• SQL ServerReport Builder for SQL Servercode name “Denali” CTP 3
• SQL Servercode name “Denali” PowerPivot for  Excel CTP 3
• SQL Servercode name “Denali” Reporting Services Add-in for  SharePoin Technologies
• SQL Servercode name “Denali” Data-Tier Application Framework CTP 3
• SQL Servercode name “Denali” Transact-SQL Language Service CTP 3
• SQL Servercode name “Denali” Transact-SQL ScriptDom CTP 3
• SQL Servercode name “Denali” Transact-SQL Compiler Service CTP 3
• SQL ServerCompact 4.0
• SQL ServerCompact 4.0 Books On-line
• SQL ServerJDBC Driver 4.0 Community Technology 2 (CTP 2)
• Connector 1.1 for SAP BW for SQL Server code name “Denali” CTP 3
• System CLR Types for SQL Server code name “Denali” CTP 3
• SQL Servercode name “Denali” Remote Blob Store CTP 3
• SQL Servercode name “Denali” Books On-line CTP 3
• SQL Servercode name “Denali” Upgrade Advisor CTP 3
• SQL Servercode name “Denali” Native Client CTP 3
• OLEDB Provider for DB2 v4.0 for SQL Server code name “Denali” CTP 3
• SQL Servercode name “Denali” Command Line Utilities CTP 3
• SQL ServerService Broker External Activator for SQL Server code name “Denali” CTP 3
• Windows PowerShell Extensions for SQL Server code name “Denali” CTP 3
• SQL Servercode name “Denali” Shared Management Objects CTP 3
• SQL Servercode name “Denali” ADOMD.NET CTP 3
• Analysis Services OLE DB Provider for SQL Servercode name “Denali” CTP 3
• SQL Servercode name “Denali” Analysis Management Objects CTP 3
• SQL ServerDriver for PHP 2.0
• SQL ServerMigration Assistant

1. Microsoft SQL Server Migration Assistant for Access
2. Microsoft SQL Server Migration Assistant for MySQL
3. Microsoft SQL Server Migration Assistant for Oracle
4. Microsoft SQL Server Migration Assistant for Sybase
5.  Microsoft SQL Server Migration Assistant 2008 for Sybase PowerBuilder Applications

• SQL ServerStreamInsight v1.2

Adventure Works sample databases for SQL Server codename Denali CTP3
http://msftdbprodsamples.codeplex.com/releases/view/55330

Denali CTP3 Adventure Works Sample Databases Readme
http://social.technet.microsoft.com/wiki/contents/articles/sql-server-samples-readme.aspx

Includes:

• AdventureWorks2008R2 Data File
• AdventureWorksDWDenali Data File
• SSAS Multidimensional Model Projects Denali CTP3
• SSAS Tabular Model Projects Denali CTP3
• SSAS AMO2Tabular Denali CTP3

Don’t forget to read the sample databases readme file:
http://social.technet.microsoft.com/wiki/contents/articles/sql-server-samples-readme.aspx

Tutorials

Tutorials for SQL Server “Denali”
http://msdn.microsoft.com/en-us/library/hh231699(v=sql.110).aspx

Includes:

• Multidimensional Modeling (Adventure Works Tutorial)
• Tabular Modeling (Adventure Works Tutorial)
• Tutorial for Project Crescent

Blogs / Wikis:

Microsoft SQL Server Code-Named “Denali” CTP3 Release Notes
http://social.technet.microsoft.com/wiki/contents/articles/3711.aspx

SQL Server Team: SQL Server Code Name “Denali” CTP3 and SQL Server 2008 R2 SP1 are HERE!
http://blogs.technet.com/b/dataplatforminsider/archive/2011/07/11/sql-server-code-name-denali-ctp3-is-here.aspx

SSIS Team Blog: Matt Mason – What’s new in SQL Server Denali?
http://blogs.msdn.com/b/mattm/archive/2011/07/12/ssis-what-s-new-in-sql-server-denali.aspx

SQL Server Reporting Services Team Blog: Thierry Dhers – SQL Server codename “Denali” CTP3, including Project “Crescent” is now publically available
http://blogs.msdn.com/b/sqlrsteamblog/archive/2011/07/12/sql-server-codename-quot-denali-quot-ctp3-including-project-quot-crescent-quot-is-now-publically-available.aspx

What’s New in Master Data Services (MDS) in Denali CTP3
http://social.technet.microsoft.com/wiki/contents/articles/3714.aspx

Marco Russo: Installing Analysis Services ssas #Denali CTP3 and PowerPivot Denali CTP3
http://sqlblog.com/blogs/marco_russo/archive/2011/07/13/installing-analysis-services-ssas-denali-ctp3-and-powerpivot-denali-ctp3.aspx

Brent Ozar: CTP3 is Here! Five Things to Know About the Next Version of SQL Server
http://www.brentozar.com/archive/2011/07/five-things-sql-server-denali-ctp3/

Jamie Thompson: SSIS enhancements in Denali CTP3
http://www2.sqlblog.com/blogs/jamie_thomson/archive/2011/07/12/ssis-enhancements-in-denali-ctp3.aspx

MSDN SQL Server (Pre-release) forum threads
http://social.msdn.microsoft.com/Forums/en-US/category/sqlserverprerelease

SQL Server “Deanali” Books Online (BOL)
http://msdn.microsoft.com/en-us/library/ms130214(SQL.110).aspx

Technet Wiki: Project Crescent Overview
http://social.technet.microsoft.com/wiki/contents/articles/project-crescent-overview.aspx

Update Log:
7/26/2011 Added Denali CTP3 Adventure Works Sample Databases Readme link

9/25/2011 Added SQL Server DENALI CTP3 Demo VHD download link and description and Technet Wiki Project Crescent overview.

SSIS Foreach File Enumerator returns more files than expected by appending a wildcard (*) to the file mask

While working on a recent project for a customer that involved importing both Excel 97-2003 and Excel 2007/2010 files, I was a little surprised to discover that the Foreach Loop File Enumerator will return both *.xls and *.xlsx files even if you only specify to return *.xls files.

I tested this behaviour with similar results with other file formats as well. During my testing, I created three files:

1. File_txt.txt
2. File_txts.txts
3. File_txtsy.txtsy

See the screenshot of the three test files created below:

Next, I created an SSIS Package with a Foreach Loop Container with Foreach File Enumerator as the enumerator type and  specified *.txt as the file mask. The file names read by the Foreach Loop Container are being assigned to a user variable called varFileName. Then, inside the Foreach Loop Container I added a simple VB Script Task that returns the value of the user variable varFileName inside a message box.

See the Foreach Loop Containter configurations below:

See the VB script inside the Script Task below: 

You would expect only file_txt.txt to be returned, but as it turns out all three files were returned as can bee seen on the screen captures for the message boxes below:

It looks like the Foreach File Enumerator appends an asterisk (*) at the end of the extension portion of the file mask you specify. This means, that specifying *.txt or *.xls is the same as specifying *.txt* or *.xls*. As an additional test, I executed the <dir> command in D.O.S. to see the results of both masks. The two commands executed are shown below along with the results:

Interestingly, we get the same result in D.O.S. if we specify *.txt or *.txt*. I assume then, that the Foreach Loop file enumeration behaviour may be bound to the D.O.S. output of the Operating System. The current version of D.O.S. on my Windows 7 machine is 6.1.7600. With the introduction of support for more than three letter extensions, this little issue might have been overlooked in D.O.S. for the <dir> command.

The only reference I found regarding this issue with SSIS and the For Each Loop Container was by Douglas Laudenschlager (Blog), technical writer for Microsoft on the SQL Server Integration Services documentation team. The blog post where he mentions this as a gotcha can be found here: http://dougbert.com/blogs/dougbert/archive/2008/06/16/excel-in-integration-services-part-1-of-3-connections-and-components.aspx

Douglas correctly expresses,

 There appears to be no way to specify, “Give me .xls but not .xlsx”.

So, if you require to only limit your control flow execution for files with a specific extension, as in Douglas’ example .xls but not .xlsx, then your alternative will be to assign to a separate variable the extension portion of the file name contained in the varFileName variable of my SSIS package above. Once you capture the file extension in a variable, you can use a precedence constraint to restrict further control fow task execution for files of a specific file extension.

In my case, I declared a variable called varFileExtension and added the following line to the existing Script Task in my example:



I can now use a precedence constraint to check if the value contained in my varFileExtension is equal to “txt” as follows:

 By specifying a precedence constraint that evaluates for the exact file extension I’m looking to work with, I can limit the rest of the execution for only these files. Furthermore, if I wanted to have separate control flow logics for files of type “.xls” and “.xlsx” for example, I can split my control flow using two precedence constraints. In such case I could change the code inside my script to include the last 4 characters instead of just three. My two precedence constraint expressions would look something like this then:

1. For XLS files -> @[User::varFileExtension]==”.xls” (period included since we are assigning the last 4 characters now)
2. For XLSX files -> @[User::varFileExtension]==”xlsx”

The SSIS package would look something like this now:

 

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.

500th Twitter follower spotlight: Joshua Luedeman

It is hard sometimes to take the time to acknowledge our Twitter followers and spend time getting to know them. I decided to change that a little bit and go beyond the 140 character limit and do a video conference spotlight on whomever became follower number 500th.

Recently, I reached my 500th follower on Twitter. The lucky tweep was Joshua Luedeman aka @BigDadyLueda, a DBA/Business Intelligence Developer from Tallahassee, FL. I asked Joshua to do a recorded video conference via Skype and answer a couple of questions to get to know him better and introduce him to the rest of the community.

It turned out Joshua is a very smart guy from upstate New York who recently moved to Florida with his family. Joshua and I share several things in common:

• Proud father of two girls
• Husband in love with his beautiful wife
• Passionate about SQL Server and Business Intelligence
• Recently moved to Florida
• Recently re-focused his career in Business Intelligence
• Looks up to @SQLChicken (Jorge Segarra) and his SQL University initiative
• Reads SQL Server Books on Line and related blog posts
• Desires to become more active in the SQL Server, Business Intelligence, SQL PASS  community by blogging and speaking

It was truly a pleasure and honor to get to know him in more than 140 characters. Here is the recording of our video conference:

 

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