MySQL TinyInt treated as Boolean

When working with a MySQL database as a source you may experience some unexpected results in your data extracts. Most of the issues are related with handling of certain data types such as “TinyInt”.

A particular issue I recently came upon was the fact that TinyInt(1) is treated by default in SSIS as a “Boolean”. You can see this setting in the ADO.Net provider configuration under the All section as shown in the image below.

Columns with data type of Boolean are expected to have values of either 1 (True) or 0 (False) or Null. But, TinyInt(1) data type allows values other than 1 or 0. At this particular client, columns defined as TinyInt(1) had values ranging from 1 to 9. Because of the default setting in the ADO.Net driver, only values of 1 and 0 were coming through as all values that were not 1 were turned into 0.

As a recommendation when working with MySQL, check for data types like these that could be interpreted differently depending on your driver. A good practice is too refer to an existing data dictionary or profile the data source before you start coding away. Also, check the documentation online, in particular for MySQL you can find it at http://dev.mysql.com/doc

As a side note, also check the values allowed for each data type. TinyInt allows different range of values depending if it is signed and unsigned. For example, in MySQL signed TinyInt allows values ranging from -128 to 127 while unsigned allows values from 0 to 255. SQL Server only allows values of 0 to 255 for TinyInt. In other words, SQL Server only supports unsigned TinyInt. You will need to use SmallInt at a minimum in SQL Server to support signed Tinyint data types.

There is an additional type of integer in MySQL called “MediumInt” that allows values greater then “SmallInt” but smaller than “Int”. In this case you will need to use Int data type in SQL Server at a minimum.

MySQL Integer (Exact Value) and its variations data type documentation can be found at:
https://dev.mysql.com/doc/refman/5.0/en/integer-types.html

SQL Server Integer and its variations data type documentation can be found at:
https://msdn.microsoft.com/en-us/library/ms187745.aspx

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!

IT Pro Camp Tampa 2013

This year I was honored to sponsor and speak at IT Pro Camp Tampa 2013. Like always, it is a great pleasure to give back to the community and get to know people from different technical backgrounds. The event was very well-organized and the venue was great.

This year the entire Agile Bay team spoke at IT Pro Camp delivering 4 sessions in total for Track 2: Business Intelligence & Big Data (SQL Server, Hadoop, etc.). You can download the PowerPoint presentations by clicking on the session links below:

1. Wes Springob | Introduction To Reporting Services
2. Jose Chinchilla & Jon Bloom | Introduction to Big Data
3. Jonathan Bloom | Introduction to T-SQL
4. Jose Chinchilla |  Introduction to Microsoft BI
   

Wes Springob deliverin Introduction to SSRS

IT Pro Camp Tampa 2013 Attendees

Configuring BIDS 2008 / 2008 R2 and Visual Studio 2008 to work with Database & Business Intelligence Projects and Team Foundation Server (TFS)

Working with the SSIS, SSAS, and SSRS Business Intelligence projects along with Database projects in the same Visual Studio 2008 development environment can be challenging as there is no clear documentation how to enable both set of projects.  Database projects are supported by Visual Studio 2008 Developer, Professional and Team System Database Editions. The  SSIS, SSAS, and SSRS Business Intelligence projects are supported by the Visual Studio 2008 shell that comes with SQL Server 2008 / 2008 R2.

If you have BIDS 2008 / 2008 R2 installed by itlself and you try to open a database project you get an error saying that this type of project is not supported. You get the same error if you have Visual Studio 2008 installed by itself and try to open one of the Business Intelligence projects. The reason behind these errors is that BIDS 2008 / 2008 R2 does not come with the database project template and Visual Studio 2008 does not come with the SSIS, SSAS, and SSRS Business Intelligence project templates.

So how do you get both set of templates in a single development environment?

In order to get both set of templates in the same development environment, you need to install both Visual Studio 2008 and BIDS in the right order as described in the following steps:

1. Uninstall all of these if you already have them installed in your environment:
   – Visual Studio 2008
   – BIDS 2008 / 2008 R2
   – Team Explorer 2008
2. Install Visual Studio 2008
3. Install Visual Studio 2008 Service Pack 1
   http://www.microsoft.com/en-us/download/details.aspx?id=10986
4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media

At this point should have a working Visual Studio environment with both set of projects.  A quick way to verify that the installation was successful, is to take a look at the Visual Studio version in the Help|About page. It will show as Version 9.0.30729.4462 QFE as shown in the picture below. QFE stands for Quick Fix Engineering.

When you launch Visual Studio 2008 or BIDS 2008 / 2008 R2 you will be prompted to select the default environment settings. Since I work with the Business Intelligence projects most of the time, I select the Business Intelligence environment. This is an option presented only the first time you open Visual Studio. To change this setting, use the Import and Export Settings wizard, which is available on the Tools menu. For more information on choosing and changing the environment settings go to http://msdn.microsoft.com/en-us/library/6k364a7k(v=vs.90).aspx.

Connecting to TFS 2005 and 2008

To connect to TFS 2005 and 2008 you will need to download and install Team Explorer 2008 from http://www.microsoft.com/en-us/download/details.aspx?id=16338.

If your end goal is to be able to work with database projects, business intelligence projects and connect to TFS 2005 or TFS 20008 the complete steps are as follows:

1. Uninstall all of these if you already have them installed in your environment:
   – Visual Studio 2008
   – BIDS 2008 / 2008 R2
   – Team Explorer 2008
2. Install Visual Studio 2008
3. Install Visual Studio 2008 Service Pack 1
   http://www.microsoft.com/en-us/download/details.aspx?id=10986
4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media
5.  Install Team Explorer 2008
   http://www.microsoft.com/en-us/download/details.aspx?id=16338.

Connecting to TFS 2010

In some cases, the solutions & projects are 2008 / 2008 R2 projects but the repository is TFS 2010 or TFS 2012. Even if you installed Team Explorer 2008 you will get an error when trying to connect to a TFS 2010 or TFS 2012 server. The reason for this is due to the fact that Team Explorer 2008 does not support full URL paths (i.e. https://myservername/mytfs/mycollection) in the TFS server name section.

To fix this issue you will need to download and install the Forward Compatibility Update Team Explorer 2008 SP1 for Team Foundation Server 2010 (http://www.microsoft.com/en-us/download/details.aspx?id=10834).

Notice that this update can only be applied to Team Explorer 2008 SP1 (Service Pack 1). The curve ball here is that there is no Team Explorer 2008 SP1 available  as a download. In order to turn Team Explorer 2008 into Team Explorer 2008 SP1 is to apply the Visual Studio 2008 SP1 to it. This means that if you followed steps 1 to 4 above, you will need to repeat Step 3 (Re-install Visual Studio 2008 SP1).

If your end goal is to be able to work with database projects, business intelligence projects and connect to TFS 2010 the complete steps are as follows:

1. Uninstall all of these if you already have them installed in your environment:
   – Visual Studio 2008
   – BIDS 2008 / 2008 R2
   – Team Explorer 2008
2. Install Visual Studio 2008
3. Install Visual Studio 2008 Service Pack 1
   http://www.microsoft.com/en-us/download/details.aspx?id=10986
4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media
5. Install Team Explorer 2008
   http://www.microsoft.com/en-us/download/details.aspx?id=16338
6. Re-install Visual Studio 2008 SP1
7. Install the forward compatibility update for Team Explorer 2008 SP1 for Team Foundation Server 2010
   http://www.microsoft.com/en-us/download/details.aspx?id=10834

Connecting to TFS 2012

To connect to TFS 2012 the following update is required :

Visual Studio 2008 SP1 Compatibility GDR for Visual Studio 2012 Team Foundation Server and Team Foundation Service Preview (http://www.microsoft.com/en-us/download/details.aspx?id=29983).

If your end goal is to be able to work with database projects, business intelligence projects and connect to TFS 2012 the complete steps are as follows:

1. Uninstall all of these if you already have them installed in your environment:
   – Visual Studio 2008
   – BIDS 2008 / 2008 R2
   – Team Explorer 2008
2. Install Visual Studio 2008
3. Install Visual Studio 2008 Service Pack 1
   http://www.microsoft.com/en-us/download/details.aspx?id=10986
4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media
5. Install Team Explorer 2008
   http://www.microsoft.com/en-us/download/details.aspx?id=16338
6. Re-install Visual Studio 2008 SP1
7. Install Visual Studio 2008 SP1 Compatibility GDR for Visual Studio 2012 Team Foundation Server and Team Foundation Service Preview
   (http://www.microsoft.com/en-us/download/details.aspx?id=29983

Hopefully this post helps you get your environment all squared away.

Connecting to the Hortonworks Sandbox v1.3 through IP 192.168.56.101

I recently downloaded the Hortonworks Sandbox v1.3 Hyper-V VM and had a hard time connecting to the included web interface that comes with it. I downloaded and followed the instructions to configure the VM in my Hyper-V server. As of this writing, the instructions point you to take note of the IP address that shows up in the console screen. The default IP address is 192.168.56.101 as shown in the next figure.

As part of the instructions you are directed to create a dedicated Virtual Switch in Hyper-V that is then given shared internet connectivity with your physical network adapter (wireless or Ethernet). The issue is that the vSandox virtual switch gets assigned an IP address from a random subnet  in the 192.168.xxx.xxx range that is not in the same subnet as the Hortonworks Sandbox default IP address (192.168.56.101).

In my case, my virtual switch vSandbox got an IP address of 192.168.137.100. The solution is to change the vSandbox’s IP address to an IP address within the same subnet range as Hortonworks Sandbox default IP address, for example: 192.168.56.100 with the default Subnet Mask 255.255.255.0 and no Default Gateway or DNS as shown in the next figure.

Once you apply the new IP address for vSandbox virtual switch, you should be able to reach the Hortonworks Sandbox web interface by typing http://192.168.56.101 on your browser as show in the next figure.

Happy Hadooping!

Presenting 2 sessions at SQLPASS

I’m truly honored to be chosen to speak at PASS Summit 2013. The honor is compounded as I have been chosen to deliver two sessions:

• Fast Performing SSAS Hierarchies: Tips and Tricks (3 hours)
  Track: BI Platform Architecture, Development & Administration
  Session Abstract: Depending on how they’re implemented, SQL Server Analysis Services hierarchies can be the source for a very high performing OLAP cube or the culprits of long running analysis. Are you implementing SSAS hierarchies correctly?This half-day session will walk you through how to implement SSAS hierarchies the right way and show you some tips and tricks to make them perform faster. You’ll also learn how to query and navigate SSAS hierarchies using common MDX functions.

• PowerPivot + Power View = Self-Service BI Revolution(75 minutes)
  Track: BI Information Delivery
  Session Abstract: The status quo has changed. IT is no longer required for business intelligence. End users can do their own BI within the comforts of Excel and SharePoint. Sound threatening? Maybe freeing? In this session, get to know what your role is and could be in this new self-service delivery model. You’ll also learn how PowerPivot and Power View work, how to leverage them, and how to make them fit into your enterprise data warehouse.

I feel very fortunate to be chosen as a speaker and to be able to give back to the community at a much bigger scale. I hope to instill my passion for the community and to be a relevant source of learning at PASS Summit 2013 to all who attend my sessions.

I encourage you to join me and attend PASS Summit 2013 in Charlotte, N.C. from Oct. 15th to 18th. There are more than 190 sessions to attend. We will also host  12 full-day pre-conference sessions on Oct. 14th and 15th with top SQL Server gurus.

For more information visit: http://www.sqlpass.org/summit/2013/

In addition to speaking at PASS Summit 2013, I am planning to speak and attend the SQL Saturday #237 Charlotte, N.C. BI Edition which will be hosted Saturday Oct. 19th following PASS Summit 2013 week.

For more information about SQL Saturday #237 Charlotte, N.C. BI visit: http://www.sqlsaturday.com/237/eventhome.aspx

I hope to see you there!

.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

I have been appointed as PASS Regional Mentor for the Greater Southeast

I have been appointed as PASS Regional Mentor for the Greater Southeast by PASS (http://www.sqlpass.org). I am truly honored to volunteer for this position to help improve our PASS community. These are BIG shoes to fill.

I will be joining my good friend Tim Radney (Twitter | Blog) oversee 28 chapters across 6 US states (AL, FL, GA, MS, NC, and SC) as well as the Netherlands Antilles. The complete list of chapters that make up the Greater Southeast can be found at: http://www.sqlpass.org/PASSChapters/USGreaterSoutheast.aspx.

What is a Regional Mentor? What do Regional Mentors do?

In Karla Landrum’s (Twitter | Blog) own words:

In general, a PASS Regional Mentor can be defined as the following:

Passionate community volunteer

• Dedicated to PASS and the SQL Server community
• Understands the value of giving his/her time and talents to helping others increase their knowledge and skills and improving the overall community

PASS ambassador

• Understands PASS and its mission and represents the best of PASS to Chapters in his/her area
• Works to keep PASS accountable, on track, and meeting the needs of its members

Primary point of contact for Chapters

• RM facilitates 2-way communications: resources, benefits, and news from PASS to Chapters and Chapter needs and feedback back to PASS
• RM knows who to contact at PASS, at the local/regional Microsoft office, area sponsors, etc. if Chapters need something or have questions

The full blog post of Karla Landrum on “What is a Regional Mentor” can be found at: http://karlalandrum.wordpress.com/2010/10/26/what-is-a-pass-regional-mentor/

What does being a Regional Mentor means to me?

My personal view is that  a Regional Mentor is a facilitator and nothing else. This position is not a bureaucratic layer on top of PASS Chapters. I’m not a Regional Mentor to supervise, police, decide, or interfere with day-to-day activities of PASS Chapters. It is meant to be the first level of contact, but it does not mean Chapters can’t talk directly to PASS staff and leadership.

Regional Mentors can help PASS Chapters acquire swag, books and even speakers for their Chapter meetings. Regional Mentors can also open doors from personal contacts, contacts established by PASS or even contacts that other Chapters have already established.

Regional Mentors can also help by providing best practices and guidelines on how to run a succesful Chapter or event from past personal experiences as well as experiences from other Chapter leaders and PASS management.

Regional Mentors can also help Chapters leverage as a group on common challenges and issues, for example: incorporating as a non-for profit, learning how to deal with taxes, managing finances, structuring the chapter leadership, obtaining legal representation, obtaining special discounts, sharing of event materials and equipment, coordinating events and speakers, etc. These are just examples of some of the ideas and initiatives for the Southeast Chapters, so be in the lookout for some discussions around them.

What do I have to offer?

As President for the past 3+ years of the Tampa Bay Business Intelligence PASS Chapter (http://tampabaybi.sqlpass.org), organizer/co-organizer of  3 SQL Saturdays, and speaker at more than 35 events like SQLSaturdays,  I understand the challenges and time committment of PASS Chapter leaders. 

Throughout my community involvement, I have created a good network of SQL Server and Business Intelligence professionals, Chapter leaders and community rockstars. In addition, I have established great relationships with “blue badges” (Microsoft employees), PASS leadership and staff and have direct points of contact at many companies that offer products and services related to SQL Server and other Microsoft and non-Microsoft technologies.

It is my desire to help Chapters that need from my past experience, network, ideas and initiatives, and role as a facilitator . It is my intention to let Chapter leaders continue to keep doing their invaluable jobs and not interfere or serve as a barrier to their activities.

I’m here to facilitate!

Windows 8 Activation Error: DNS name does not exist Error Code: 0x8007232B

The Windows 8 Activation may fail while using the online activation process. You may receive an error screen similar to this:

This issue was documented in this Microsoft Support article http://support.microsoft.com/kb/929826 for previous versions of Windows but also applies to Windows 8. The cause according to this article is:

As the support article explains, the issue has to do with the default behavior of the activation wizard in certain installation media. Depending on where the installation media was obtained from (for example MSDN or TechNet subscription download) the activation wizard  by default will look for a Key Management Service (KMS) host on your internal network. It will not try to activate using the internet to reach Microsoft Activation servers.

In my case, I downloaded the Windows 8 Pro ISO file from my MSDN subscription. The installation binaries from the MSDN ISO file uses a temporary / default key to install as I was not prompted for an installation key. This installation key is apparently a temporary key that tells the Windows 8 activation wizard to look for a KMS host on the local network only and not try Microsoft’s activation servers online.

The solution is to manually change the product key to a Multiple Activation Key (MAK) most likely provided with your MSDN or TechNet subscription. To change the product key, use the slmgr.vbs script included with your Windows installation. The steps to manually change the product key are as follows:

1. Open an elevated Command Prompt (CMD) in Windows 8. Elevated means, Command Prompt needs to be run as an administrator. To do this, go to Windows 8 Start (click on the Windows key), locate the Command Prompt shortcut on your tiles (or search for it on your Apps) and righ-click on it.

2. The option to Run as Administrator appears on the bottom toolbar as shown in the following screenshot:

3. Once the elevated Command Prompt opens type the following command and press ENTER:slmgr.vbs /ipk “Your 5×5 Product Key”

For example:

4. After you press ENTER you will receive the following confirmation message within 10 seconds:

5. Once you receive this confirmation message you may proceed to activate Windows 8 using Microsoft’s activation servers online.

Top 3 Reasons Why Evernote is worth $1 billion to me

Everyone heard about Facebook’s recent purchase of Instagram for $1 billion dollars. No one can imagine how they came with such price tag for a bunch (a big bunch) of pictures with special filters added. I never used Instagram and I don’t think I will ever. I’m happy with my regular’ol Flickr, Twitpic and Picasa.

One free application that is really worth $1 billion is Evernote. Why? Well, it’s way more than just another online organizer to me. And it’s even better than the Trapper Keeper I carry around.

Reason #1: No more emailing back and forth
One of the best reasons why Evernote is so valuable is the fact that I can write notes or take pictures of a whiteboard with my smartphone and they will auto-magically synchronize with my laptop and iPad or vice-versa. No more emailing back and forth so that I can download them. The notes and pictures go right into my user-defined notebooks I saved the notes under and show up wherever I have Evernote configured.

Reason #2: No more wallet full of receipts.
As a consultant I travel a lot and taming my receipts monster for my T&E reports is not an easy task. At the end of a week-long trip my wallet looks somewhat like a juicy double-decker burger. With Evernote, I can take a picture of the receipt with my smartphone, tag it and upload it to my expenses notebook. I can do it right there at the restaurant when the waiter or waitress brings the check and add notes related to the expense.

Reason #3: Share & Grab
Another reason why Evernote is so valuable is the fact that I can share my notebooks and notes with other people. Imagine how cool it is to brainstorm and whiteboard and be able to share those notes, pictures, videos, links, etc with your teammates. Awesome, right! Well, it doesn’t stop there, you can even use third-party extensions and applications like ifttt.com to save content to an Evernote notebook from Tweeter, Facebook, email and other channels based on the criteria that you define. Make sure to check-out the Evernote Trunk section for a complete list of apps.

Just wanted to share this as I find it very useful and believe that applications such as Evernote should be the next billion dollar company. Please comment with other ways you use Evernote for: Recipes? CRM? Reminder? Your own private Facebook? sa password vault?