Do you have a data warehouse initiative in your current organization and looking for a way to learn how to properly build and support it? Would you like to learn how to do this straight from the Masters of the BIverse themselves? Well you’re in luck! Next month at SQLRally there’s going to be a great pre-conference session held by the following:
In this full-day workshop, you’ll learn from the author team of Mike Davis, Adam Jorgensen, Devin Knight, and Patrick LeBlanc how to build a data warehouse for your company and support it with the Microsoft business intelligence platform. We’ll start with how to design and data model a data warehouse including the system preparation. Then, we’ll jump into loading a data warehouse with SSIS. After SSIS, you’re ready to roll the data up and provide the slice and dice reporting with SSAS. The team will walk through cube development and data enrichment with things like key performance indicators, which are essential for your future dashboards. Lastly, we will cover how to report against the data warehouse with SSRS, including a primer in how to write MDX queries against the SSAS cube.
What you can expect to take away from this session:
Practical knowledge of building a Dimensional Model
Designing a simple ETL process using SSIS
Designing a Cube
Designing simple SSRS Reports
Building an integrated process that fully leverages the entire MS BI stack to load a Data Warehouse.
You can register here and pre-con fee is $199 (which includes lunch). This is a great deal so what are you waiting for? Sign up today as slots are filling up fast! See you at SQLRally!
This is just a quick post in regards to a conversation I just had via Twitter. If you don’t already use Twitter, the SQL Community has setup a great resource on there using the hashtag of #sqlhelp.
Today a conversation came up due to a forum question over at SQLServerCentral regarding applying policies to databases with tables that have heaps. If you’re not familiar with the term, a heap is a table that has no clustered index on it. This can be problematic from a performance stand point so it might benefit you to find a way to identify these potential problem children. Enter Policy-Based Management.
This is a simple policy that you can run against your servers and it will identify your tables that are heaps. Just to clarify this policy identifies if your table has a clustered index on it. If it doesn’t then it will fail policy check. I’ve provided two ways to get the policy.
EXEC msdb.dbo.sp_syspolicy_add_policy@name=N'Find Table Heaps',@condition_name=N'Find heaps',@policy_category=N'',@description=N'Heaps are tables without clustered indexes. Read the link below to learn more about heaps.',@help_text=N'Fragmentation (part 4):what are heaps? by Paul Randal',@help_link=N'http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/09/19/761437.aspx',@schedule_uid=N'00000000-0000-0000-0000-000000000000',@execution_mode=0,@is_enabled=False,@policy_id=@policy_id OUTPUT,@root_condition_name=N'',@object_set=N'Find Table Heaps_ObjectSet'
This week SQL University is on Spring Break but we’ve lined up some activities to help keep students busy (you know what they say about idle hands and whatnot!). In continuation of Women’s History Month, and properly coming off of the heels of the 24 Hours of PASS event, we’re proud to have our next talks in our Lecture Series this Friday at 1pm EST featuring the ladies of WIT Week here at SQL University.
Our Live Lecture will be happening over at SQLLunch, and as always it’s free, so make sure you go register for the event. If you enjoyed reading all of the fantastic posts from WIT week you’ll love this event. The session is going to be a round table discussion about what WIT means to them as well as discussing some of the issues they’ve faced and would like to address in the field. Audience participation is encouraged via Q&A in LiveMeeting so come join the panel. See you at the SQLLunch!
Yesterday was the first day of PASS’ 24 Hours of PASS event. For those not familiar, 24 Hours of PASS is an event that brings together 24 different presenters and they present on various topics on SQL Server ranging from performance tuning, internals to business intelligence and previews at vNext of SQL Server. This month’s event is quite special since March is Women’s History Month, PASS is celebrating it by having this event all delivered by women!
So far the event has been absolutely awesome and the awesomeness continues today with the last 12 hours of the event, starting at 8am EST. If you missed yesterday, don’t fret, all of the sessions are being recorded and will be available on the PASS website within a month. Yesterday’s sessions went well, we had some sessions that actually had over 750 attendees (or as Tom LaRock refers to it as, the Jetliner line)! There were a few surprises as well, such as in Isabel de la Barra’s session, where we were treated to a presentation in Spanish (and translated by moderator Jesus Gil). At first we thought it was going to be a big issue but it turns out that over 300 attendees stuck around for the session and feedback from the Twitter stream seemed positive.
Speaking of Twitter, if you wish to follow along with the event you can do so by following the event hash tag of #24HOP. We are also using #sqlpass as well as #passwit to help promote and discuss the event. Day one is in the book and day two is looking to be fantastic as well, see you in the sessions!
It’s March and this month sees a lot of celebration, amongst the parties are Mardi Gras and St. Patrick’s Day. This month we also celebrate women as it is Women’s History Month as well! If you’ve spent any time in the SQL Community you may have noticed that we have an especially strong support for Women in Technology and so this month there are some great things happening to help celebrate that fact.
This is the next post in my series about transitioning from a DBA to a BI consultant for Pragmatic Works. This post is a particularly sensitive one as it pertains to a lesson I had to learn the hard way. My hopes are that by writing and publishing this maybe you can spare yourself or someone else from making the same mistakes. This post is basically to teach one thing: Sometimes you’re a lighthouse, shining your light and showing people the way to safety. The lighthouse is steady and helps others through with a clear message and action. The other half is the shipwreck. Sometimes seeing the wrecks on the rocks gives others a warning about what NOT to do in a given situation. Throughout your life you will probably play both roles many times. For me, in this particular situation, I’m playing the role of shipwreck.
Before I begin let me quickly set the stage for my current position in life. For the last few years I’ve been a SQL Server DBA in shops where I was pretty much the only one. Due to this, along with very lenient bosses, I was allowed to leverage social networking on a daily basis. If you follow me on Twitter then you know I tend to tweet more than any human being should a lot. I’ve come to think of the network of fellow SQL professionals on Twitter as my extended DBA team. I would consume tons of knowledge via conversations, monitoring (and participating) in the #sqlhelp channel, reading blog posts and checking out all the various webcasts and events. This was before taking on the role of a consultant.
As a consultant you have to remember one thing: you’re no longer on YOUR time, you’re on your CLIENT’S time. When someone hires you the expectation is that you’re there to do a job and focus on that job. When you deviate from that, especially on a public platform like social networking sites, the perception is that you’re using up their time. And by using up their time, I mean wasting it. While I may be working hard on whatever client work I’m doing, yet tweeting throughout the day, the perception is that I’m not really working and my focus isn’t where it should be. Even if I scheduled every single tweet throughout the day the perception is still the same, and this is the key: perception is reality. That being the case, the “reality” I was broadcasting by tweeting all the time (as a consultant) is that I was not busy, not focused and to some extent not caring about my client. While none of these are true the fact is I should’ve been more cognizant of the perception I put out to the public, and for that I apologize to the community as a whole.
So now what do we do? Well, we move forward and learn! I now understand a little better what’s expected of me in my new role. The beauty of mistakes is it gives us a chance to learn from them. The important part of mistakes is that you DO learn from them and most importantly: MOVE ON! Mistakes happen. Not only do they happen, they happen to everyone. What matters is how you deal with it and move forward. A really great example of a shipwreck-turned-lighthouse would be a recent situation with Brent Ozar (Blog | Twitter) and his business partnership at SQLSkills. You can read the saga here, here and here. Brent’s public dealing with his situation also helped inspire this post. He took what could have percieved as a terrible situation and turned around and made it a fantastic learning opportunity for anyone looking to pursue a similar partnership in the future. He turned a shipwreck into a lighthouse!
Just remember if you make a mistake that it’s okay. Stuff happens. It’s how we deal with those mistakes that matters in the end. How about you? Have you had a shipwreck/lighthouse moment? Share your stories in the comments!
Much like the USA Network here in the States welcomes characters, I’d like to formally let the world know that BIxPress also welcomes folks, and this time it’s looking at you DBAs out there!
You may be thinking, “But Jorge, the product is called BIxpress, why as a DBA would I give a flip about it?!?” Glad you asked! I’ve recently made the transition from a DBA to a BI consultant and as part of my learning process for learning the BI stack I decided to take a crack at creating an SSIS package that would take a bunch of video files from a conference, compare the file names to the actual session titles (files came down named with their session codes, not names) and rename the files according to their formal session titles. If you’re interested in that, I’ll be posting another blog post soon detailing how I did it as well as you’ll be able to download the package yourself and try it out!
Well the new year is here and SQL University is back and better than ever! I just wanted to take a minute to bring everyone up to speed on what’s going on with SQLU.
First off the last semester we had (Spring 2010) started rather late, which pushed the rest of the schedule quite a bit. One of the unique facets of SQL University is having our coach Tom LaRock (Blog | Twitter) posting on EVERY topic, EVERY week which is quite the impressive feat! That being said, since the last semester ran a few weeks late it not only made a lot of work for him so I wanted to give him ample time off as that is a TON of writing he’s doing, which I think we can all agree, is pure awesomesauce. Also Tom has undergone a job transition, as well as I have, so it’s been a little hectic on that front as well. Due to the schedule shifts, job changes, moves and generally hectic life we decided to skip the Fall semester for 2010, hence you’ll find it missing from the overall SQLU main page.
Another reason we went quiet for awhile was we were busy putting together another major project: SQL University – The Book! No, sorry, no movie deals in the works but I think you guys will like this even better. What we’re doing is compiling all of this awesome material our professors have put together for you guys into an organized e-book companion! My hope is that we can it formatted properly for distribution via Amazon’s Kindle store but if that doesn’t work out we’ll probably just PDF it and let you guys go to town! As with the rest of this wonderful project, this is absolutely free to everyone and will be released as SQL University: Volume I, Freshman Year which includes the first two semester’s worth of blog content! This is taking a lot of time to put together so bear with us as we get that worked on.
Another big change you may have noticed, and one of the most exciting parts about this new year, is our re-branding! Our new logo comes courtesy of the wonderful folks at Revealed Design Inc. (Facebook| Twitter) and a big thanks to Aaron Nelson (Blog | Twitter) for hooking me up with them. This is a much cleaner design and look than my atrocious attempt at designing a blogger badge from before. When you visit each professor’s sites this time around you should see the new badges displayed.
Finally the other huge addition this year is our partnership with SQLLunch.com to bring you the live lecture series. We had our first one featuring Josef Richberg during SSIS week. We’ll be bringing you more this semester with some big names so stay tuned! The best way to keep up to date on all the latest news and additions to SQL University is to join our newsletter.
One More Thing…
If you’re enjoying SQL University and learning from all of these great folks in the SQL community you’ll get a chance to experience all of this in person! This Spring at SQLRally we’ll be hosting a Lightning Talk session featuring the professors of SQLU as well as some other special surprise guests. SQLRally runs from May 11-13 in Orlando, Florida and only costs $299 and $199 for pre-conference sessions (optional). Hope to see you there!
During my presentation at SQLSaturday 62 in Tampa I was asked by an attendee about having a policy to check the setting for ad-hoc optimization settings. At the time since I was in a bit of a time crunch (and I couldn’t remember the exact facet to look under) I couldn’t properly demo how to check for it. In this post I’ll show you how to check for that specific setting. In a future post I’ll show you how to check on many more settings.
Before we begin, I highly recommend you familiarize yourself with what exactly this setting changes and how it affects your SQL Server environment. Remember this setting affects the entire instance so all databases installed here will be affected by this change. Read this great post by Bob Pusateri (Blog | Twitter) to get an understanding of what Optimizing for Ad Hoc Workloads really does.
Creating the Policy/Condition
In SQL Server Management Studio browse down to and expand your management node, expand the Policy-Based Management node, right-click the Policies folder and select New Policy.
Name your new policy and then from the Check Conditions drop down menu select New Condition.
Give your new condition a name and from the Facet drop down menu select the Server Configuration facet.
In the Expression editor, click the area below the column title of field and you will be presented with a drop-down of all the properties available for this facet. Select @OptimizeAdhocWorkloads.
Under the heading of Value, you will have two options: True or False. When you create a policy you want to establish a condition you want so for the purposes of this demonstration we want our servers to have this setting set to off (which is default setting) so we’ll select the option for FALSE. Click OK to create your condition and return to the new policy window.
Next we’ll select our Evaluation Mode. This policy, based on the facets and properties we’ve selected offer us three options: On demand, on schedule and On Change: log only. The last option, if enabled, will allow this policy to be active and log any changes made to this particular setting. One cool thing you can do with this is you can create alerts to automatically email you if this particular condition is violated. Check out Ken Simmons (Blog | Twitter) article on Configuring Alerts for Policy-Based Management to learn more. Leave the Evaluation Mode to On Demand and click OK.
Now that we have our policy created simply right-click on it (located under your Policies folder) and select Evaluate to try it out!
GUI? We Don’t Need No Stinking GUI!
In this post I walked you through how to create this policy using the GUI but if you prefer to script this out, you can do that too! Here is the T-SQL script that you can run in lieu of walking through the SSMS screens, to create this particular policy:
Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N’Ad-hoc Workload Check_ObjectSet’, @type_skeleton=N’Server’, @type=N’SERVER’, @enabled=True, @target_set_id=@target_set_id OUTPUT
Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N’Ad-hoc Workload Check’, @condition_name=N’adhoc optimization check’, @policy_category=N”, @description=N’This policy checks the server setting to see if Optimize for Ad-Hoc Workload is enabled. The default setting is disabled.’, @help_text=N’To learn more about this policy check out Jorge Segarra”s blog post on this’, @help_link=N’http://sqlchicken.com/2011/01/policy-for-ad-hoc-workloads/’, @schedule_uid=N’00000000-0000-0000-0000-000000000000′, @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N”, @object_set=N’Ad-hoc Workload Check_ObjectSet’
Again, I can’t iterate enough NOT to blindly go changing settings on your servers without understanding the effects of your actions! Policy-Based Management is a very powerful and easy-to-use tool but be sure to use it wisely! In a later post I will show you how to modify even more server-level settings and let you customize policies to check exactly the settings you want audit.