In this short video, you'll learn how to implement an SSIS framework to detect failures and achieve compliance for regulations like Sarbanes-Oxley.  To download BI xPress mentioned in the video please visit the following link: http://www.pragmaticworks.com/Products/BI-xPress.aspx.

In SQL Server 2012, you now have the choice between doing a cube in MOLAP or Tabular mode. This Whiteboard Wednesday shows you the benefit and cons to each of these modes.

 Navigating through the array of Microsoft visualization tools can be quite difficult. In this video, Devin Knight and Brian Knight show the pros and cons of each of the tools and the appropriate time to use each. There is of course lots of grey area in your selection.

We also refer to a decision matrix Excel spreadsheet in the video. You can find that attached here: Reporting Decision Matrix

Until next time,
Brian Knight

 Each and every Wednesday Pragmatic Works is excited to bring you Whiteboard Wednesday, a discussion on patterns, best practices or rants about our industry. In this first video, you'll see some of the top visualization mistakes that people make in building reports. At a glance those are:

1. Inappropriate use of colors - don't forget the color blind and black and white printers
2. Graph selection - Generally bar charts will communicate a message easier and faster.
3. 3D Bling is almost always a bad choice - 3D will distract your users from the data and give you bad perspective in judging one bar from another.
4. Chart Junk - If an item isn't enhancing your visualization of the data, it likely doesn't belong. Think USA Today infographs. While entertaining, you can't identify the numbers fast.
5. 5 Second Rule - If you can't communicate your message in 5 seconds or less when you hand a user a report, reconsider how you visualize. 

These are of course a subset of many of the issues. Let us know if you can think of others in the comments section below. This topic is part of the our Master's SSRS class, given monthly.  

Until next time!
Brian Knight

This week, the new quarterly update was release of BI xPress was released. The release had over a hundred improvements in it but I wanted to highlight 3 of the neater ones. There are also a lot of down payments in this release that will show themselves in a spring release. If you already have BI xPress, the release is a free update if you’re under maintenance. These are just a small fraction of the BI xPress overall features. For more information, see the entire product feature list here.

Free Trial Download Compare Editions

Update 1 – New Monitoring Console

Every report in the monitoring console has been redeveloped in WPF, giving it better performance and interactivity. View packages running live and view errors while they occur in a visual method. You can also stack packages side-by-side to see the all run at the same time.

Update 2 – SSIS Catalog Import

The SSIS catalog import tool will enable users to import native performance and execution data from a 2012 SSIS catalog to the BI xPress auditing database. This will make it possible for users to view execution data for packages that do not have the auditing framework. It allows you to combine multiple server’s auditing information into a single view.

Update 3 – New Personalization Features

While BI xPress has add-ins into the Visual Studio environment, you can now also use the Pragmatic Workbench to launch all the components. You can now customize the look and feel of the workbench’s metro interface.

While we do offer in-person training around the country, we also offer live virtual instruction from our top consultants in the field. So, when does it make sense to go virtual versus in-person?

1. Price tag - is 1/3 the price for the same technical training you receive in a class room
2. Virtual mentoring – we do include videos to help you months after you take the class but if you need more personalized one-on-one help, you receive two hours of personal time with our top consultants with your training class.
3. Personal interaction – class sizes are kept small and they are interactive with a live instructor
4. Still work – since classes are only a half a day each day, you can still work the other part of the day. Yay!
5. Travel – I travel quite a bit personally and my family has made enough compromises where one more week of travel is not in the cards for my family. Take a class from the privacy of your home or office and don’t worry about going home to a hotel.

See our Upcoming Virtual Training Classes. This January, you will receive an additional $200 off any training class that you book in 2013.

There are draw-backs of course. If your co-workers can’t leave you alone if you’re in the office, it’s best to be in-person training instead of virtual but be aware of the differences in cost. Our in-person training is offered in bootcamp-style or workshop style around the country (schedule here: http://www.pragmaticworks.com/LearningCenter/Bootcamps.aspx).

When you first unbox a fresh SQL Server environment or place a new application on a SQL Server, there are a number of hurdles that you may have to watch out for. These common pitfalls usually include:

1. Indexing – Not having enough indexes or too many can dramatically hurt performance
2. Disk Performance – A great band-aid to performance gains is moving hot areas of your databases onto better disks
3. CPU – Not having enough parallelism can cause query performance issues
4. Memory – The cheapest way to gain SQL Server performance is to increase the amount of memory to your SQL Server
5. Complex Design – Many developers and DBAs build too complex of a design to solve a simple problem. Boiling the ocean in a design can really hurt performance.

This video below features Jason Strate. He’s one of our Sr DBAs who also does assessments. If you’d like to see more about assessments in your environment, check out our offerings here.

As part of our assessment  we will review:

· Database design and index usage

· Query performance

· Potential system bottlenecks

· SQL Server’s IO responsiveness

· Microsoft Best Practices for environment and system configuration

· And more!

Until next time,
Brian Knight

Extended events in SQL Server allow you to trap certain events like errors or informational messages and log them so you can react to them. They’re especially critical to learn them now since they are the replacement for important tools like SQL Profiler. In this video, Jason Strate walks you through some of the cases when you’d use them.

This video below features Jason Strate. He’s one of our Sr DBAs who also does assessments. If you’d like to see more about assessments in your environment, check out our offerings here.

Until next time,
Brian Knight

The last thing you want to do is truncate and load your data when your synchronizing two tables. In this video Bradley Schacht and Brian Knight white board some of the common patterns and approaches to incremental loads in SQL Server.

1. Stages and left-outer join – Takes much longer to implement and potentially run. This can be done through T-SQL or through an SSIS similar technique without the staging of data.
2. Control table – Creation of a control table that allows you to check the last load date or ID and only retrieve the rows after that date.
3. Change Data Capture (CDC) – A mechanism built into SSIS (SQL Server 2012) and SQL Server 2008 Enterprise Edition to detect the rows that have changed if you don’t have a LastModifiedDate type column.

Now that you see the outline, get the full details in the below On the Board video. If your company blocks YouTube, you can also access the video through a corporate-friendly method here: http://permalink.fliqz.com/aspx/permalink.aspx?at=6294476941e6498f8bdaa9ef7130d0cc&a=e4af6c7e40c243b29b2cf4546fe57056

Upcoming SSIS Masters Workshops:

• Cape Town, South Africa – SSIS Essentials Workshop
• Johannesburg, South Africa – SSIS Essentials Workshop
• St Louis, MO (12/5/2012- 12/6/2012) – Masters SSIS Workshop
• Tampa, FL (1/15/2013-1/16/2013) – Masters SSIS Workshop
• Los Angeles, CA (1/29/2013 – 1/30/2013) – Masters SSRS Workshop
• Boston, MA (2/12/2013 – 2/13/2013) – Masters SSIS Workshop
• Pittsburgh, PA (3/12/2013 – 3/13/2013) – Masters SSIS Workshop
• Reston, VA (4/16/2013 – 4/17/2013) – Masters SSIS Workshop

We’re adding a few new workshops every month to our learning center section on the site here. Don’t want to travel? No problem, we also offer many more classes online like MDX, DBA, SharePoint and others.

Let us know which method you prefer to do in the comments section.

Thanks so much for the people all that came to the data cleansing session we did at PASS. We covered data cleansing using Data Quality Services (DQS), Master Data Services (MDS), scripting and deduplication with Fuzzy Grouping. The code downloads are now available for the project we used here:

http://dc613.4shared.com/download/iuKMIPJT/Data_Cleansing_WIth_SSIS.zip

Other free webinars that might interest you are:

Data Cleansing With SSIS 2012 (2012 focused without fuzzy logic)

Integrating DQS, MDS and Your Data Warehouse (Chris Price’s session on DQS and MDS)

Data Quality Services 2012 (1 hour on DQS with Chris Price)

Data cleansing in SSIS (focuses on fuzzy logic)

Thanks again,
John Welch, Chris Price, Brian Knight