I am heading down to Houston, TX to speak at the Houston Techfest this weekend. Being a Houston native makes this trip even more exciting. This is a free event that offers a diverse set of sessions. The sessions range from What's new in Java to Introduction to Window Azure. I attended the event last year and if my memory serves me correctly there were only two SQL Sessions. Fortunately, I was able to convince the event coordinator, Michael Steinberg, to include a full SQL Track at the event. Thi...
Read More
There has been a slight change to this weeks SQL Lunch meeting. Our scheduled speaker cannot present this week, therefore you dedicated host, me, will be presenting. I know you all were excited to learn about Performance Point, but you will have to wait until later this year. Here is the updated meeting information. Hope to see you all at Lunch. #39-SSIS: Taming your Configuration Files Speaker: Patrick LeBlanc Live Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=75Q6Q6&role=a...
I am very excited to announce the 1 year anniversary of the SQL Lunch. Last year around this time I was getting the site up and trying to find a couple of speakers to ensure that we had a good start. I was able to convince Tim Mitchell to speak and the rest is history. Thanks to everyone that has attended all of the lunch events and thanks to all of the speakers who gave their time to make the site a success. During the first year I gained a co-host (Adam Jorgensen) and several new friends along...
I have been working with a several clients that are leveraging Change Data Capture (CDC) as a tool for incrementally loading their Data Warehouse. In most cases the clients are using database restores from various environments to create test environments. When databases with CDC enabled is restored to the same server with the same database name, CDC remains enabled and all related meta data is persisted . However, when restoring the database to another server or to the same server, but with a ne...
So many people have been waiting for a SQL Saturday in Houston and now it’s finally here. As a native of Houston I was surprised that it wasn’t one of the first SQL Saturday venues. Well the wait is over. Thanks to Nancy Wilson, the local SQL Server User Group Chapter Leader, Jonathan Gardner and Malik Al-Aminthe, the first Houston SQL Saturday (#57) will be held on January 29, 2011. Several great sessions have already been submitted, but they are definitely accepting more. In a city the size of...
I am not quite sure how many of you have tried to set or change the value of a variable using an SSIS script task, but if you have tried I am sure that you may have ran into a few road blocks. Recently I tried to do this and I quickly realized that it is not as straightforward as I thought. To do this configure your package so that you have a source of some type on the data flow design surface. Drag a script task onto the data flow and connect it to the source. Typically, you would set the varia...
Last Night (9/14/2010) I had the opportunity to present to the Wisconsin SQL Server User Group. My topic was SSIS- Configuration Files, Deployment and a Little Performance Tuning. The presentation was well received and I hope to have the opportunity to speak to the group again on another occasion. Tonight(9/15/2010) I will be speaking in front of my local SQL Server User Group in Baton Rouge. The topic is Loading a Data Warehouse with SSIS. In addition, I will doing a short presentation (Lightni...
I was asked by someone the other day, where do you get your drive and motivation? For a second, I was stumped. After giving it a little thought I realized that my drive has several sources. The first is my family of course. My wife has this unbridled drive for success that cannot be measured at any level. Whenever anyone in the house says I can’t, I don’t know how to…, You can’t or some variation of those sentences she immediately corrects us. Can’t is not part of our vocabulary. Next it’s my ki...
Puzzle #7 had an interesting twist. You were required to add a column to the result set that dynamically displayed a currency type based on the existing currency code. As always, there are a couple of ways to solve this puzzle. So here is what I started with: This query satisfies most of the requirements, but one thing you should notice here is the tuple. I started out here with only the Country and State-Province, which I thought would work. Then I added the following calculated member: In the ...
Join me for Lunch - SQL Lunch #33 (Indicators, Sparklines and Databars) #33-SSRS 2008 R2 - Indicators, Sparklines and Databars Speaker: Patrick LeBlanc Add To Outlook: Add To Calendar Live Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=RZW5HJ&role=attend Date and Time: 9/1/2010 11:30:00 AM CST BIO: Patrick LeBlanc, SQL Server MVP, is currently a Business Intelligence Architect for Pragmatic Works. He has worked as a SQL Server DBA for the past 9 years. His experience includes...
I have presented you with a couple of easy puzzles in the last few posts. So, let's step it up a little with one that is a little more challenging. I have to be honest, this puzzle was sent to me by one of my co-workers at Pragmatic Works. Here are the requirements: Columns: Reseller Sales Amount and using the Source Currency Code add a column to the result that displays US if the currency code is USD and International for all other currency codes. Rows: Country and State-Province Filters: None ...
Puzzle #6 should not have presented too much of a challenge, it's intentions were to introduction you to the ORDER function that is available in MDX. Ok, so this is what I started with: This is a very simple statement that satisfies most of the puzzle requirements. The only one that is does not satisfy is the ordering of the results. To do this I used the ORDER function. The function accepts two arguments. The first is a valid expression that returns a set. In this example it is the set of Produ...
As we continue with the puzzles, I will continue to introduce new MDX functions. In this puzzle the requirements are as follows: Columns : Reseller Sales Amount Rows : Product Subcategories Filters : None Hints : This puzzle is too easy for a hint. ;) Ahhhh, but there is one additional requirement, the resulting rows should be returned in DESC order by Reseller Sales Amount. Remember, don't post your solution here. Save them for my solution post. I will post it along with the steps that was take...
After spending a week in Tampa, FL at a client site I've had some time to unwind and reflect back on SQL Saturday #28. I have received some really positive feedback from all individuals involved. While I can't say that every aspect of the event was excellent, I can say that compared to last years event this one far exceeded any of my expectations. I have read several blog posting about the event and I have to issue one correction, most of you are reporting our attendance as 350 attendees. That i...
So, I was recently asked if you could display a vertical bar on a line graph to denote the last day of the month. Initially, I had no clue how to solve this problem. I talked to a couple of people and searched the web, but came up empty. I decided to just dive into a report and solve the problem on my own. So I started with the following query that can be run in the AdventureWorks2008 database: SELECT st.CountryRegionCode, CAST (sod.OrderDate AS DATE ), SUM (sod.TotalDue) TotalDue, NULL Marker F...
Again I want to apologize for the slow posting of the solution to this puzzle, but I have been hard at work on SQL Saturday #28 . Fortunately, I carved out some time to write it up. The solution to this puzzle could have been accomplished a couple of ways. I chose to use the SUM and YTD MDX functions, but before I discuss these functions I will start with the basic query, which satisfies these requirements: 1. Internet Sales Amount as a Column 2. Delivery Date Calendar Month as a Row 3. Applies ...
In this three part series SQL Server MVP Andy Leonard will discuss SSIS Design Patterns . Speaker: Andy Leonard Add To Outlook: Add To Calendar Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=J8NSMR&role=attend Date and Time: 8/9/2010 11:30:00 AM CST Topic: #28-SSIS Design Patterns 1a: Introduction to SSIS Design Patterns This is part one of a three part series. A brief introduction to the concept and usage of design patterns and a demonstration of the Counts Acquisition in ET...
We are just about a week away from probably the biggest FREE training event to ever take place in Baton Rouge, LA. Heck this is probably the biggest FREE technology event in Louisiana. I really don't know. Nevertheless, since it is almost here I wanted to encourage any one that has not already signed up to do so now. We are well over 500 registered attendees and that number is increasing daily. If you are not sure whether to attend or not here are a few highlights: 53 sessions, including 4 early...
Do you have a challenging SQL Server or .Net issue that is causing you to have sleepless nights? Well, we have convinced 12 of our speakers (Experts) to allocate an additional hour of their day strictly for answering questions. These are some of the top SQL Server and .NET guys in the industry. So, if you have any questions bring them to SQL Saturday #28 Baton Rouge. Here is the schedule. Time Slot Speakers Topics 9:00 am – 10:00 am Chris Eargle and Chander Shekhar Dhall Software Development, Ap...
Writing YTD totals and Running totals using T-SQL can take a little effort. However, with MDX it's not too difficult. In this puzzle I will write an MDX statement that produces a monthly YTD or Running total. Here are the requirements: Columns : Monthly Running Total (Calculation), Internet Sales Amount Rows : Delivery Date Calendar Month Filters : Delivery Date from January 2006 to December 2006 Hints : You may need to create a scope calculation (WITH MEMBER), use the YTD and SUM functions Reme...
This puzzle may not have been much of a challenge, but it introduced two new MDX concepts. I have to admit, one of my colleagues and I chatted about this puzzle, and I realized that my requirements were a little unrealistic. As a result, I changed the requirements a little. Instead of returning data between the Calendar Years of 2006 and 2008, the data should be filtered by Ship Date Calendar Year. So, here is what I started with: SELECT NON EMPTY ( [Ship Date ].[Calendar Year ].Children,\ [Meas...
Now that we are into the third quarter of 2010 I decided to post an update of my 2nd quarter goals and post goals for the 3rd quarter. If you all have been following my blog or me on twitter (patrickdba) you know that I was selected as a SQL Server MVP for the first time this year and that the SQL Lunch is doing great. I am really excited about both and I can't wait for you all to see the great Lunches that are coming up in the next few months. So now onto my goals. In the second quarter I met a...
This puzzle is rather simple, but it does introduce a few new things. Using T-SQL you typically filter queries with a WHERE clause. You can also do the same with MDX, but just like T-SQL there are a few ways to accomplish this. In this puzzle you will see an example. Here are the requirements: Show: Internet Sales Amount Columns: Ship Date Calendar Year Rows: Product SubCategory Filter: Only return Product SubCategories that have sales greater than 1,000,000 and only return data between Calendar...
This past weekend I had the opportunity to hangout with some of the brightest upcoming SQL Server Professionals and a couple of old-timers. We were gathered in Orlando, FL by Andy Warren to work on the SQL Saturday Wiki . Essentially, the plans were to improve on the information that he had collected about organizing and running successful SQL Saturday events. We were locked in a room all day Saturday and our brains were drained of any information we had obtained from running our own events. Ove...
I recently had the opportunity to do two presentation on SQL Server R2 for the DBA. In the first session there were approximately 150 attendees and at the second there were approximately 400 attendees. The recording is available here and you can download the slide deck here . I would like to correct something I said about the Utility Control Point and Data-Tier applications. They are only available in SQL Server 2008 with Service Pack 2, SQL Azure and SQL Server 2008 R2. Service Pack 2 for SQL S...