When I get home, I’ll be jumping straight into a car with my family and driving for 7 or 8 hours. Why, you ask? To get to Birmingham, Alabama for SQLSaturday #81 on 7/30. I’m giving two sessions, Do More (ETL) with Less (Effort) – Automating SSIS and Handling Advanced Data Warehouse Scenarios in SSIS.

The following weekend, 8/6, I’ll be in Baton Rouge, LA for SQLSaturday #64, delivering the same sessions. If you happen to be attending either one, please look me up.

Pre-cons like this are some of the most cost effective training you can get – plenty of time to both cover a topic from end to end, and to dive into the real implementation details that are often missing from shorter presentations because of the time constraints. I hope to see you there!

Data Warehousing with SSIS Deep Dive

Want to learn more about implementing data warehouse ETL with SQL Server Integration Services? Attend this full day seminar, and we’ll cover using SSIS for data warehousing in-depth. You’ll learn everything you need to know to populate your data warehouse with data. We’ll cover how to develop a common framework for your packages, automate the creation of rote packages for staging data, implement common patterns for handling various types of dimensions and fact tables, and how to instrument your packages to identify and recover from failures when loading data. We’ll be using the AdventureWorks databases for the examples, so bring along a laptop configured with SQL Server 2005 or later, and the AdventureWorks sample databases installed. We’ll also cover how the upcoming Denali release of SQL Server affects what we discuss in this seminar.

• Laying out a framework for your ETL
  • Logging
  • Restartability and Recoverability
  • Auditing
• Handling Dimensions
  • SCD Type 1
  • SCD Type 2
  • Advanced Dimension Types
• Handling Facts
  • Transactional
  • Periodic Snapshot
  • Accumulating Snapshot
  • Advanced Fact Patterns
• Errors
  • Handling Processing Errors
  • Handling Data Errors
  • Recovering from Errors
• Best Practices for Managing Your ETL

If you’ve seen my posts about using Biml to generate SSIS packages, and you’d like to learn more, then please check out “Do More (ETL) with Less (Effort) – Automating SSIS”. I’ll talk about the Biml support in BIDS Helper, as well as other approaches for creating SSIS packages without all the manual effort. In my other presentation, “Tuning Analysis Services Processing Performance”, we’ll look at some of the common performance problems people encounter with Analysis Services, and how to resolve them. We’ll also cover a process for doing the tuning.

I really enjoy presenting at SQLSaturday events, and I’m looking forward to this one. I hope to see you there.

Do More (ETL) with Less (Effort) – Automating SSIS

SSIS is a great tool for transferring data from one data source to another, and for implementing complex ETL processes. However, for simple, straightforward data transfer tasks or packages that adhere to a pattern, creating SSIS packages by hand can be time-consuming and repetitious. By attending this session, you’ll learn how to automate package creation in SSIS, including the dynamic generation of data flows. We’ll cover some of the free and open source tools available for this, and discuss “roll your own” options.

Tuning Analysis Services Processing Performance

You’ve got your Analysis Services cube created, and deployed in production. However, you notice that every night, the cube is taking longer and longer to process, and users are starting to complain about their data not being ready when they arrive in the morning. If you’ve found yourself in this situation, or want to avoid being in it in the first place, come to this session. We’ll cover how to benchmark processing performance, track down bottlenecks, and how to tune things to get the best performance for processing your cube.

This year, I’ve submitted several sessions.

Do More (ETL) with Less (Effort) – Automating SSIS

SSIS is a great tool for transferring data from one data source to another, and for implementing complex ETL processes. However, for simple, straightforward data transfer tasks or packages that adhere to a pattern, creating SSIS packages by hand can be time-consuming and repetitious. By attending this session, you’ll learn how to automate package creation in SSIS, including the dynamic generation of data flows. We’ll cover some of the free and open source tools available for this, and discuss “roll your own” options.

Do You Know the Data Flow?

The Data Flow task is one of the most powerful and most complex tools available in SSIS. Whether you are brand new to SSIS, or you’ve been using it for a while, it’s likely you’ve had some questions about the Data Flow. Why are some components so much slower than others? Why can’t I store a value (like a row count) in one component, and use it in another component later in the Data Flow? And why does it always seem to be the part of my package that fails when I run it against real data? Well, you’re not alone. During this session, we’ll answer these questions (and many others) by learning how the Data Flow operates internally. We’ll cover the Data Flow from the basic (what’s a component?) to the advanced (how can I determine how many threads my Data Flow is using?). After attending this session, you’ll know a lot more about getting the most out of Data Flows in SSIS.

Handling Advanced Data Warehouse Scenarios in SSIS

So you’ve used SSIS to populate a simple star schema data mart, and everybody’s happy. But now you have new requirements that require more advanced data warehouse approaches, like late arriving dimensions, bridge tables, parent child dimensions, and Type 3 or Type 6 slowly changing dimensions (SCD). How do you handle those in a scalable, efficient way in SSIS? This session will present some common patterns for handling these scenarios. You’ll learn when to use each advanced approach and the pros and cons associated with each pattern. You will learn how to implement these patterns in SSIS, and how to tune them for high performance.

Tuning Analysis Services Processing Performance

You’ve got your Analysis Services cube created, and deployed in production. However, you notice that every night, the cube is taking longer and longer to process, and users are starting to complain about their data not being ready when they arrive in the morning. If you’ve found yourself in this situation, or want to avoid being in it in the first place, come to this session. We’ll cover how to benchmark processing performance, track down bottlenecks, and how to tune things to get the best performance for processing your cube.

Other Sessions I’d Like to See At the Summit

This is not an exhaustive list, by any means – so if you aren’t on the list, please don’t take it personally. These are the ones that happened to catch my eye as I looked through the list. Some because they had an interesting technical focus, some because I know the speaker will bring an interesting perspective on the topic, and some because…, well, just because I can.

Dynamic Business Rules Processing Using SSIS – Tim Mitchell

Techniques for Automating T-SQL Unit Tests and User Acceptance Testing – Scott Currie

Advanced Analysis Services Development – David Darden

Advanced SSAS Security – Chris Webb

Analysis Services Power Tools – Darren Gosbell

DAX Deep Dive – Marco Russo, Alberto Ferrari

Do More (ETL) with Less (Effort) – Automating SSIS
SSIS is a great tool for transferring data from one data source to another, and for implementing complex ETL processes. However, for simple, straightforward data transfer tasks or packages that adhere to a pattern, creating SSIS packages by hand can be time-consuming and repetitious. By attending this session, you’ll learn how to automate package creation in SSIS, including the dynamic generation of data flows. We’ll cover some of the free and open source tools available for this, and discuss “roll your own” options.

Do You Know the Data Flow?
The Data Flow task is one of the most powerful and most complex tools available in SSIS. Whether you are brand new to SSIS, or you’ve been using it for a while, it’s likely you’ve had some questions about the Data Flow. Why are some components so much slower than others? Why can’t I store a value (like a row count) in one component, and use it in another component later in the Data Flow? And why does it always seem to be the part of my package that fails when I run it against real data? Well, you’re not alone. During this session, we’ll answer these questions (and many others) by learning how the Data Flow operates internally. After attending this session, you’ll know a lot more about getting the most out of Data Flows in SSIS.

The abstract for my presentation is below. If you happen to be attending the event, please stop by.

Do More (ETL) With Less (Effort) – Automating SSIS

SSIS is a great tool for transferring data from one data source to another, and for implementing complex ETL processes. However, for simple, straightforward data transfer tasks, creating SSIS packages by hand can be time-consuming and repetitious. By attending this session, you’ll learn how to automate package creation in SSIS, including the dynamic generation of data flows. We’ll cover some of the free and open source tools available for this, and discuss “roll your own” options.

The presentation covers the loading of Late Arriving Dimensions, Parent-Child dimensions, Type 3 dimensions, and Type 6 dimensions. Over the next couple of weeks, I’ll be producing a series of blog posts detailing the patterns from the presentation in more detail. I’ll also cover a couple of bridge table patterns which I couldn’t squeeze into the presentation.

Varigence will have a table at the event. If you are working in Microsoft BI, I encourage you to stop by and take a look at what we are doing – it’s very cool.

Do More (ETL) with Less (Effort) – Automating SSIS

SSIS is a great tool for transferring data from one data source to another, and for implementing complex ETL processes. However, for simple, straightforward data transfer tasks, creating SSIS packages by hand can be time-consuming and repetitious. By attending this session, you’ll learn how to automate package creation in SSIS, including the dynamic generation of data flows. We’ll cover some of the free and open source tools available for this, and discuss “roll your own” options.

***If all goes well, you may even get a sneak preview of some exciting new BIDS Helper functionality at this session ***

Handling Advanced Data Warehouse Scenarios in SSIS

So you’ve used SSIS to populate a simple star schema data mart, and everybody’s happy. But now you have new requirements that require more advanced data warehouse approaches, like late arriving dimensions, bridge tables, parent child dimensions, and Type 3 or Type 6 slowly changing dimensions (SCD). How do you handle those in a scalable, efficient way in SSIS? This session will present some common patterns for handling these scenarios. You’ll learn when to use each advanced approach and the pros and cons associated with each pattern. You will learn how to implement these patterns in SSIS, and how to tune them for high performance.

This was my first public presentation of “Do You Know the Data Flow?”, which is always a little interesting. It’s a subject area I’m pretty comfortable with, though, and the presentation went smoothly. I got some great feedback on it (thanks, everyone), with several people telling me that they’d learned something new. For me, as a presenter, that’s great to hear, because I want people to feel like it was well worth their time to attend. Based on the feedback for this one, I’ll be putting it in my regular roster of presentations.

The slides from the presentation are located here.