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.

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!

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.

 

 

Master Data Services Error: Resolving from a long-running stucked Staging Batch with Status “Queued to Clear” or “Not Running”

In some ocassions,  you may run into a runaway, long-running or stucked staging batch with Master Data Services in SQL Server 2008 R2. The issue happens most frequently when a fairly large batch of rows are being processed for update or deletion.

The issue has to do with Service Broker, either due to a timeout or notifications not being received or received incomplete.  You can confirm the issue when you query the queue [mdm].[microsoft/mdm/queue/stagingbatch]. You will see one or more messages in this queue. After searching through a couple of forum threads I found the solution to be fairly simple: rollback transactions an re-enable Service Broker. The steps are as follows:

1. Set the MDS database in single user mode and rollback uncommitted transactions.

   ALTER DATABASE [MDS] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;

2. Re-enable Service Broker on the MDS database.

   ALTER DATABASE [MDS] SET  ENABLE_BROKER WITH NO_WAIT;

3. Set the MDS database back in to “regular” multi-user mode.

   ALTER DATABASE [MDS] SET  MULTI_USER WITH ROLLBACK IMMEDIATE;

Once the MDS database is brought back online, all queued messages should be cleared out and MDS database will be able to communicate normally with Service Broker.

You can check Books on Line for more information on the difference between “NO WAIT” and “WITH ROLLBACK IMMEDIATE” options when issuing the ALTER DATABASE command here. Pinal Dave has a good post about the difference between these two options as well here.

Hope this post has helped you. Your feedback is greatly appreciated.

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 2012 Master Data Services Error: The required .svc handler mappings are not installed in IIS.

Microsoft SQL Server 2012 Release Candidate 0 (RC 0) has a confirmed issue with Master Data Services configuration. The following error is raised by the Master Data Services Configuration Manager when launched:

Before creating an MDS web application, you must correct the following errors:
Internet Information Services (IIS) is not configured on this server.
The required .svc handler mappings are not installed in IIS. For more information, see http://go.microsoft.com/fwlink/?LinkId=226284.
For web application requirements, see http://go.microsoft.com/fwlink/?LinkId=215355.

This issue has been communicated to Microsoft via Connect ID: 701993 and is documented in the Technet Article “Troubleshoot Installation and Configuration Issues (Master Data Services in SQL Server 2012)”

The Workaround

After several days trying to figure out the issue, today I received notice from Reagan Templin (LinkedIn | Twitter), technical writer for Microsoft, that the MDS Team has identified the issue. This issue will NOT be fixed in the current RCO but will be fixed in the RTM version. The error can be replicated under the following conditions:

1. Configuring Master Data Services on a server that is not joined to a Windows domain
2. Running Master Data Service Configuration Manager with a local account (non-domain user) on a server joined to a Windows domain.

The workaround is simply to join the server to a Windows domain and launch Master Data Services Configuration Manager with a domain account that has administrator privileges. In my case,  after I joined the server to a domain I was able to complete by MDS configuration.

Many thanks to Reagan Templin for her above and beyond customer service. She followed up on this issue directly with the MDS Team after I posted a message on Twitter about the issue I was having and kept me posted with updates towards resolving the issue.

SQL Saturday 86 BI Edition Tampa 2011: Afterthoughts

Wow! Just Wow! That’s all I can say to describe how great SQL Saturday 86 was on November 5th in Tampa, FL . I got so much positive feedback from every attendee, speaker and sponsor. Food was great, sessions were amazing, speakers were first class!  I have to say thanks to everyone for attending, speaking, sponsoring and helping out with these great event put together by the Tampa Bay Business Intelligence User Group officers and volunteers.

Organizing Committee & Volunteers

I cannot take personal credit for this event. It was truly a team effort. Somewhere I read that in order to be successful you need to surround yourself with talented & driven individuals that are committed to your same goals. Maybe I’m paraphrasing or simply making that up, but this has proven to work with running our local Tampa Bay Business Intelligence User Group and the SQL Saturday #86 BI Edition event.

The organizing committee was made up by:

• Jose Chinchilla, President (Twitter | LinkedIn | Blog)
• Andrew Brittain, Vice-President (Twitter | LinkedIn | Blog)
• David Liebman, Treasurer (Twitter | LinkedIn | Blog)
• Sanjeev Jha, Project Manager (Twitter | LinkedIn | Blog)
• Stephen Murphy, Event Coordinator
• Jeff Steffel, Event Coordinator (Twitter | LinkedIn)

The volunteers that participated in our event included:

• Randy Borys
• Gloria Salcedo
• Chris Richardson
• William Sanders
• Pam Shaw
• Richard Pyra
• Wes Helton
• Tom Totten
• Denis Desault
• David Greim
• Lena Pavlyuk
• Lyn Taylor

Speakers

Our speakers were world class speakers to say the least. The speaker roster included Microsoft MVPs, field engineers and evangelists and other professionals that committed their time and paid for their own travel to support our event. We had just a few speakers that cancelled for personal, health or work-related conflicts. We still want to express our appreciation to them for willing to support our event as well. Thankfully, we managed to secure alternate speakers who did a great job even though it was last minute. Thank your SQL Saturday #86 Speakers!

Sponsors

Our sponsors made our event a quality and epic event by sponsoring with cash, items and swag to giveaway. Thanks to them we were able to sponsor great Cuban food from LATAM restaurant, coffee and donuts throughout the day, swag and raffle prizes and provide our speakers with a thank you dinner.

• KForce provided their facilities for our event.
• Convergence Consulting Group (Twitter | Website) did an amazing job with our speaker and event organizers’ shirts.
• Quest Software (Twitter | Website) raffled an iPad and a $100 Amazon gift card.
• Redgate Software (Twitter | Website) offered a great variety of books to give away.
• Pragmatic Works offered a free training voucher for any of their online classes.
• LaSalle Computer Learning School offered a training voucher for any of their in-person SQL Server certification classes.
• Fusion-IO raffled an iPad.
• Idera raffled one their SQL Serve monitoring software suites and gift cards.
• Soaring Eagle, ElSavier and Wrox each provided SQL Server and Business Intelligence books to raffle.

To all our sponsors thank you for supporting our SQL Server and Business Intelligence Community!

Panel of Experts

In this event I decided to put together a panel of experts to discuss several topics relating to SQL Server and Business Intelligence as well as a discussion of what’s new in the upcoming release of SQL Server 2012. The Panel of Experts took place at LATAM restaurant right after lunch. Our panel of experts included:

• Adam Jorgensen, Pragmatic Works (Twitter | LinkedIn | Blog)
• Brian Mitchell, Microsoft (Twitter | LinkedIn | Blog)
• Sanjay Soni, Microsoft (Twitter | LinkedIn| Blog)
• Michael Antonovich, Orange County School District (Twitter | LinkedIn | Blog)
• Mark Tabladillo, SolidQ (Twitter | LinkedIn | Blog)

We invited our sponsors to briefly talk about their products and services and prizes they were giving away during the event. Following our sponsors I decided to recognize Andy Warren, MVP and PASS (Website) Board of Directors, for his contributions to the SQL Server community, SQL Saturday creation and 100 SQL Saturday milestone.

BI BootCamp pre-conference

Along with our free SQL Saturday #86 event we hosted a paid pre-conference titled “BI BootCamp” presented by SQL Server MVP and SSAS Maestro Instructor Adam Jorgensen. We exceeded our goal with 33 attendees paid attendees, all of which gave very positive feedback of Adam’s presentation. I did a star appearance, helping Adam present a section on SSRS Reports using OLAP cubes as a source.

Thanks to Mike Wells for (Twitter | LinkedIn | Blog) for taking pictures throughout the event. You can see more event pictures at:

https://picasaweb.google.com/116314811362928917852/SQLSat86