Hello everyone and welcome to this month’s T-SQL Tuesday. This month’s topic is all about Power BI! If you’re reading this and thinking “crap, I’m not a BI person!”, don’t you fret. My intention is to make folks who normally don’t use BI on a day to day basis try their hand at creating cool new visualizations and reports with Power BI and seeing how the other side lives. Maybe it can spark some ideas on how you can make use of it in your operational day-to-day. For the BI folks, let’s see your creative side! What cool things can you show us with Power BI?
Your challenge, if you choose to accept it, is to create and publish your very own Power BI report! Great news, one of the latest features of Power BI to come out is the ability to publish your report to the web so when you create and publish your report, embed it in your T-SQL Tuesday post! Here’s a couple of great examples of this feature in action:
If you’ve never used Power BI before it’s REALLY easy to get started. Simply head over to PowerBI.com and click on the ‘Get Started Free’ button. Download and install Power BI Desktop tool. It’s a free download and this is what you’ll use as your authoring tool. Click ‘Sign Up’ button. You’ll need to sign in with an organizational account (e.g. you can’t use personal email such as @gmail,com @live.com, @Hotmail.com, etc.). If you have a custom domain (e.g. SQLChicken.com), you can use your custom domain email address, otherwise you’ll have to use an organization/work email.
Once you’re up an running, check out the Power BI documentation page to help you get started. For this post, feel free to connect and use any data set you want that can be shared without issue. For example don’t publish your company’s data, customer data, etc. There’s a ton of data out there to use that is safe. In fact, the tutorial from the above link lets you download a sample CSV file if you can’t find a proper dataset. Also, Jen Underwood (Blog|@idigdata) actually posted a great list of public data sources you can take advantage of. Want more great Power BI information? Make sure to check out Adam Saxton’s (Blog | @guyinacube) Guy in a Cube YouTube channel and subscribe for awesome bi-weekly, content.
Want some inspiration on what you can do? Check out the Power BI Best Report contest that’s currently going on. Be sure to vote for your favorite report before voting closes on February 14th, 2016.
What is T-SQL Tuesday?
T-SQL Tuesday is a monthly blog party hosted by a different blogger each month. This blog party was founded by Adam Machanic (blog|@AdamMachanic). You can take part by posting your own participating post that fits the topic of the month and follows the requirements below. Additionally, if you are interested in hosting a future T-SQL Tuesday, contact Adam Machanic.
How to Participate
Your post must be published between 00:00 GMT Tuesday, February 16h, 2016, and 00:00 GMT Wednesday February 17th, 2016.
Your post must contain the T-SQL Tuesday logo from above and the image should link back to this blog post.
Don’t rely on trackbacks. Please link to your post in the comments section below so everyone can see your work.
Optional: Tweet about your post using the hash tag #TSQL2sDay.
Here we go again with another round of T-SQL Tuesday fun this time brought to you by recent SQL Certified Master (MCM), SQL University professor, and my brother from another mother Robert Davis (Blog | Twitter).
This month’s topic is actually a series of really good questions:
We return to our days of youth to take a fresh look at learning. How do you learn? How do you teach? What are you learning or teaching? Or the coup de grace post would be learning something new and telling us about it.
So given that I established and run SQL University this topic is right up my proverbial alley!
How do you Learn?
I love the old adage “Give a man a fish and he’ll eat for a day, teach a man to fish and he’ll eat for a lifetime”. My best learning tool is by doing, which I’m sure many of you can relate to. There’s only so much academic knowledge you can soak up before it starts spilling out. Honestly, if something isn’t relevent to me in terms of real-world application I have a much harder time grasping the concept. For instance a year or two ago when I first heard of PowerShell I looked at it and went “oh that looks cool…I guess, not sure what I’d do with it.” Fast-foward to a month ago and I finally get a chance to re-learn the basics and actually apply it before that lightbulb went off in my head that said “oh WOW, I GET it! I heart PowerShell!”. Without that opportunity to really apply the academic knowledge, the education itself goes to waste.
Thing is not every job you’re in will call for use of PowerShell, replication, mirroring, etc. so the next best step…do it yourself! The wonderful world of technology now allows us to build virtual machines on our laptops with relative ease. So if your shop doesn’t give you a proper sandbox to play around with advanced features then I highly recommend you install Virtual PC, VMware Workstation, VirtualBox, VMLite or whatever your preferred flavor of virtual platform and get to practicing! If installing and configuring virtual machine’s isn’t your cup of tea (but I highly recommend as you learn lots of lessons by installing/configuring machines from scratch) there’s a free sandbox environment you can experiment with a fully configured SQL Server 2008 virtual machine at http://sqlserverbeta.com . This is a free, no-strings-attached virtual machine complete with AdventureWorks databases installed ready for you to tinker with brought to you courtesy of PASS, Dell and MaximumASP.
How Do You Teach?
Ah the Golden Rule: we learn it young and it applies forever. When I learn I like to be entertained a bit as well as soak up good knowledge. I also like to interact as much as possible. This being the case when I do a presentation I try to keep it lively as sometimes straight technical content is…well…boring. I’m sure that sounds blasphemous to some of my fellow geeks out there but you know its true. You’ll be sitting in a session with content that can’t really be demoed (i.e. internals talk). The secret to teaching is making a topic engaging enough that you don’t lose your audience.
Although there’s a fine line between entertainment and being annoying. I’ve been guilty of this myself wherein in your attempt to keep it light you come off looking like a clown. Make sure you remember your root cause for being there and that’s to teach. Clowning has its place so remember to toe that line carefully…unless you’re Buck Woody then all bets are off!
What Am I Learning?
Anything and everything! I’m like a shark and learning is my water. If I’m not learning, I’m dying. SQL Server is a HUGE product and currently I’m starting to lean towards learning the BI stack. Another key thing that I keep learning and playing with is PowerShell. In all seriousness there’s ALWAYS something to learn and it doesn’t always have to be something as huge as learning an entire product line or feature. Start with baby steps. One cool thing I’ve seen recently is a series of blog posts called “One Cmdlet At a Time” in which Jonathan Medd (Blog | Twitter) learns PowerShell one cmdlet at a time. This is a great example of how you can tackle learning something one little bit at a time. In his case not only is he learning by doing so hands on but he’s teaching as well via his blog. It’s a win for everyone!
First of all I apologize for this roundup taking more time than usual to appear. This last week was my last week of work at the hospital and trying to get everything turned over as well as prepping for my move to Jacksonville my time’s been short to say the least. Without further ado, here’s this month’s #TSQL2sDAY roundup:
Stacia Misner reports in on her love for all things Reporting Services.
Brad Schultz fills in the gaps for us with a brilliant (as always) Intellisense post.
Glenn Berry gives us some great insight on core database engine improvements. If you don’t think R2 brought anything to the core product stop and read this post.
Andy Lohn merges several answers together with IntelliSense, Resource Governor and the MERGE statement! Also Andy’s new on my radar and has a witty Twitter name so time to add another person to follow on Twitter/add to Reader!
Jason Brimhall packs a ton of great information in his post about compression.
Seth Phelabaum talks about the new T-SQL and query writing improvements. This is awesome as I wasn’t aware of some of these new additions! Developers, this is a MUST read! Once again, another great blog added to my reader!
Robert Davis, one of the newest folks to attain the prestigious SQL Server Master certification, brings to our attention something you wouldn’t really think about: sysprep support! SWEET! Great walkthrough example using a virtual machine on Windows Server 2008 R2. Definitely an administrator MUST read.
Wayne Berry takes us for a trip to the clouds with the SQL Azure team and SQL Server 2008 R2’s SQL Azure integration. If you’re even thinking about putting your database in the cloud check out this post and subscribe to the SQL Azure team’s blog.
Kendra Little has plays the dating game in this post where she narrows down three great features (PowerPivot, Data Compression and The Data Collector) and finally comes up with her winning date. Read this post to find out who wins!
Bill Fellows hasn’t been able to play with R2 yet but gives a great and detailed walkthrough of how and why to use User Defined Table Types as Table-Valued Parameters. VERY cool! Added to reader.
Steve Jones, of Voice of the DBA podcast fame, gives us some great insight on StreamInsight. Honestly this feature also excites me so this is a really nice intro to the feature.
Ted Krueger, who did a fantastic job last week with SQL University’s HA/DR week, waxes poetic about all of the new changes in SSIS Data Flow Engine. If you care at all about SSIS performance check this out!
John Racer, aka @speedracer, could’ve written a whole book on all the new BI features but instead focused on Reporting Services. Nice breakdown of new hotness in SSRS.
Mark Blakey talks about PowerPivot and comparing it to a certain scene in Austin Powers (no, not the hot tub scene with Alotta). Also make sure to give him a follow on Twitter as well.
Nicholas Cain, aka @anonythemouse, despite claiming to cheat by referencing one of his old SQLServerCentral posts covers a real nice example of how Data Compression helps you save space.
Pinal Dave does a fantastic job of covering MERGE operations as well as showing us precisely how it improves performance over traditional methods. Very cool! Developers, definitely check this out!
Stef Bauer walks us through the Resource Governor feature and how it works. Pretty cool feature to help cull down those resource-hungry queries from ad-hoc queries.
Rob Farley throws us for a loop as his favorite part about SQL 2008 R2 doesn’t even have to do with the features! Check it out.
Bob Pusateri is excited by what’s NOT there with his overview of filtered indexes in 2008 and how they helped him solve a specific issue on a homegrown solution.
It’s another month and so we have ourselves another T-SQL Tuesday event, this time hosted by Aaron Nelson (Blog | Twitter) with this month’s theme being Reporting. If you don’t know what T-SQL Tuesday is here’s a quick blurb from Aaron’s blog:
You Don’t Know What T-SQL Tuesday Is Yet?
Adam Machanic (Also on twitter as @AdamMachanic) had a great idea 5 months back – Invite new and existing SQL Server bloggers to post about the same topic on the same day. The results have been excellent – diverse skill sets and data related job roles all posting from differing perspectives on the same issue.
Since this is still early in the game, a quick list of the post roundups from the first 4 topics:
This blog post is going to be relatively short and sweet as my expertise in the storage realm is limited. Yesterday I had the pleasure of learning some new and interesting things about NetApp’s SAN technology that I thought was interesting and would share with you.
For this month’s T-SQL Tuesday #004 meme is hosted by Mike Walsh of StraightPath Solutions (@Mike_walsh on Twitter). For my post I don’t have a solution so much as a nugget of information to pass along I found interesting. I was speaking to a consultant yesterday about a few things and the topic of his experiences with Oracle DBAs-vs-SQL Server DBAs in terms of his experience with them in regards to storage (SAN) consultations. Clearly this perked my ears up and I asked him to explain. He went on to tell me that in his experience he’s seen that the Oracle DBAs he’s come across come across as rather paranoid and never believe anything he tells them despite showing them whitepapers direct from storage vendor on the matter. On this particular matter we were talking about how NetApp has a best-practice recommendation that seems rather contradictory and (rightfully so) the DBAs were skeptical and would continue asking the same question over and over again despite having it already answered…over and over again. What’s that recommendation you ask? Well in NetApp world they have what are called Aggregates which are nothing more multiple RAID groups. Here’s the excerpt from Wikipedia about it:
NetApp supports either SATA, Fibre Channel, or SAS disk drives, which it groups into RAID (Redundant Array of Inexpensive Disks or Redundant Array of Independent Disks) groups of up to 28 (26 data disks plus 2 parity disks). Multiple RAID groups form an “aggregate”; and within aggregates Data ONTAP operating system sets up “flexible volumes” to actually store data that users can access. An alternative is “Traditional volumes” where one or more RAID groups form a single static volume. Flexible volumes offer the advantage that many of them can be created on a single aggregate and resized at any time. Smaller volumes can then share all of the spindles available to the underlying aggregate. Traditional volumes and aggregates can only be expanded, never contracted. However, Traditional volumes can (theoretically) handle slightly higher I/O throughput than flexible volumes (with the same number of spindles), as they do not have to go through an additional viritualisation layer to talk to the underlying disk.
For Oracle databases it is recommended that you pool all your disks into a single large aggregate and use FlexVol volumes for your database datafiles and logfiles as described below. This provides the benefit of much simpler administration, particularly for growing and reducing volume sizes without affecting performance. For more details on exact layout recommendations, refer to .
Now think about that for a minute. As a SQL Server DBA you’re probably having a mental breakdown as I did when first slapped with this one as they’re essentially telling you throw all your eggs in the same basket, its better for you. Well this is where our conversation got interesting as he started breaking down for me exactly how Aggregates worked, how NetApp’s algorithms function, and WHY this best practice exists and isn’t as bad as it appears at first glance. Apparently because of the way NetApp’s Aggregates work the more you expand your Aggregate (read also: add more disks) you’re actually helping improve performance as you’re adding more spindles to it and will help performance along. At this point of this post you storage guys are probably ready to tear me a new one as I may or may not be explaining this correctly/accurate to which I re-state, “I’m not a storage guy, I’m a DBA learning something new and attempting to relay this information as best as I understood it.”
Which brings me to the point of my post. As a DBA crazy things like a best practice recommendation that doesn’t make sense can and will come up in your career. Should you question them? Without a doubt! After all, it’s your bacon on the line after these guys are gone. The important part however is the learning. Ask questions, realize the differences between technologies and understand the how’s and whys. In this post I talked about NetApp’s solution but EMC works differently as well as has different terminology. It may not be your job to be a SAN admin but as a DBA I think its essential to understand all the technologies involved in your configuration and work with those responsible to come up with the best solution that works for you. There are plenty of resources out there to garner knowledge from, they’re just a quick Boogle search away…
Follow the hashtag #TSQL2sDay on Twitter to check out everyone’s posts.
Well this is the second T-SQL Tuesday (my first as I missed the first one). T-SQL Tuesday is a great idea put forth by Adam Machanic (@AdamMachanic on Twitter). Here’s the summary of this month’s post challenge:
Have you ever found yourself unable to figure out the intricacies of how some piece of code works? Ever been confused by the results you’ve gotten back from a query, only to find out that something totally unrelated was going on? Or have you ever been compelled to wile away your spare time working on a “challenge” posted by some blogger?
For this month’s T-SQL Tuesday, I’m asking participants to write a blog post on a “puzzling” topic, along the lines of some of the following ideas:
Describe a confusing situation you encountered, and explain how you debugged the problem and what the resolution was
Show a piece of code that doesn’t behave as most people might expect, and illustrate the reasoning behind the discrepancy
Create a challenge for your readers to solve
As always, even given the event’s name the posts are not limited to T-SQL! Any component of, or software product related to SQL Server, is fair game. MDX, SSIS, LINQ to SQL, Entity Data Model, NHibernate, and any other software product that deals with SQL Server data can be featured in your post. Be creative!
That being said I’d like to share a little project I’ve been working on with some co-workers here at work. If you follow me on Twitter you’ll probably have seen some of this discussion and I’ve mentioned one of the other guys I’m working with, Matt Schultz (@mmzplanet on Twitter) also talking about this project. The other piece of our “development group” is another network analyst named Horace Knight. This story doesn’t contain any special programming tricks and I can’t post specific code at this time but this particular project has been an absolute pleasure to work on and work with these guys coming up with a from-scratch solution that works.
Recently here at the hospital we have put in phase I for a new GE system for our electronic medical records system. The thing about this particular system is that the application requires an XML file to be located on the desktop. This XML file contains some pertinent information for the application to work such as pathway (production/test/dev), terminal ID (an ID needed for client to be able to connect) as well as some other GE-centric information. The key thing here is that terminal ID, which allows a workstation to authenticate to the tandem system that contains our medical information. Well the way this works is that GE provides us with a pool of terminal IDs to divvy up amongst our clients and we’re supposed to manage them however we choose so long as those clients have that XML file. Now had this only been for 20-30 PC’s this might not be a HUGE deal but we’re talking about 2,000+ machines that are going to need to access this system, have their terminal IDs properly assigned to their proper locations (multiple campuses at play here as well) and be properly managed (i.e. no duplicates, correct locations, proper allotment per location, etc.). What puts the pressure on here is that the these locations REALLY need to be correct since the locations dictate where forms are printed to, forms that contain patient registration information.
Soooo…How Do We Do This?
So despite having GE tell us these things I’ve covered, the whole process was still a little confusing (took months of back and forth to finally understand and hammer out what I’ve just explained in a paragraph!). Horace, being the awesome human being he is, stepped up to the plate and took the responsibility of coming up with a solution to handle the terminal ID assignments. Horace worked hard with the folks at GE as well as hospital employees to get a high-level grasp of what was needed and expected out of this solution (such as locations, printers, workstation identifiers, etc.). We also got a few curve balls thrown at us in that not only regular workstations would be running this application but we would also need this solution to work on kiosk machines (locked down workstations being logged in with dedicated accounts) as well as workstations on our regular domain. As time went on, and our deadline loomed closer, Horace’s job became increasingly difficult because every time he thought he had it figured out something else would change and it was back to the drawing board. He had a vision of an application that would eventually manage everything but he had no programming experience and he knew this solution would require a database as well so this is where I came in. He and I started meeting frequently and fleshed out a general workflow as well as a database structure that would handle the various information we needed to do this. On the client side Horace was developing a script in VBscript that handled the various things we needed such as writing the needed XML file out with the custom terminal ID that would be assigned from a table in the database. Due to the way we had architected it at that point we were also creating another XML which contained location information for that PC. That location XML would not only allow the script to identify where the workstation was but it would let any PC technicians know where the machine should be in case of issues. This script would be delivered to workstations via kixstart script kicked off upon logging into the workstation. As some of you have probably figured out there’s lots of holes in this solution but bear with us, we know not what we do! Well our deadline date came, the script and database did their thing, and for the most part it worked as it was supposed to. Like any system there were a few flaws and things were learned on rollout day that we hadn’t anticipated or known and these were handled manually.
Once our go-live date came and went things finally calmed down a bit in our department for the first time in months. Now that the system was up and running it was time to go back and figure out how to clean things up. Matt came into the project when he volunteered his services by offering to build a GUI that would prompt the user at login for a location and that would then write the location to the XML file as needed. Now, this was the initial thought pattern but as Matt learned more about .NET (sorry pundits, he went with VB.NET as he had previous experience with VB syntax so VB.NET was easier/faster to pick up) and working with me more on revamping queries to the database and converting poor, simple queries into re-usable and secured stored procedures. During this time Matt took it upon himself to not only put a GUI over the script but improve the functionality and then some! I’m hoping that Matt puts up a post about this project as the current iteration really is culmination of Horace’s original vision for this application along with a lot of additional ingenuity and hard work from Matt and myself.
The current version of the script now works like this: The application will be delivered via an as-yet-determined means. When the application first starts the user is prompted with a GUI that allows you to choose a campus as well as location (this process is to be done by PC techs on new PCs so location/campus will be correct before gets to users). If the PC already has a location XML (which the current ones in production have) the application reads the current location information and checks the database for an existing record for the PC and makes sure location matches up. Since the PC is already providing the location information the GUI won’t even show up since it has the information it needs. Now the application is running in the notification area of the taskbar. If you access the application it shows you a GUI read-only interface that shows you some pertinent information including PC name, location assignment, and assigned terminal ID per pathway (each pathway access requires individual terminal ID). Matt broke it up to show not only what value was in the database but what value was in the local XML document so that you could see at a glance if values were set properly or needed updating. In order to manage and change values there is a button you click to access the management interface of the application. This area is secured via login prompt and Active Directory groups. Only members of a certain AD group are allowed access to this portion of the application to make changes. Once this secured area is accessed you can make changes such as location and adding/removing pathway accesses. The pathway add/drop processes are handled by stored procedures which are programmed to not only add and modify records but it can dynamically pull additional terminal IDs if none are available for a given area (this was a concern as when we initially rolled out and told there were only 20 workstations in Area X and we come to find 5 more needed IDs assigned to that area it was a manual pain to add those 5 terminal IDs to the section’s pool). Another nice thing about having the database handle everything via stored procedures is that we managed to eliminate the need for that second location XML file on the workstation since all data needed was now stored in the database. Another addition from Matt’s hockey-filled brain was the use of a global settings table. This table would hold information that would be common to all clients such as registry path of the application, location of certain file that denoted whether the application was installed and client version level.
Now The Part You DBA Geeks Are Waiting For…
So besides all of the tables and stored procedures that are underneath this application, this project gave me the chance to try my hand at something else I haven’t had a chance to play with before: mirroring. Thanks to my fellow SQL tweeps and some quality time on Books Online I figured out how to modify our connection string to recognize a mirrored instance. It took some trial and error but I finally got my test database mirrored with a witness server so that I had a High Safety mode with automatic failover setup. Moment of truth came when we tested the application by opening it up, manually failing database over to its partner, then continuing to use the application. As expected there was a slight delay while the database failed over but the application did recognize the failover and continued working! NASA-style Apollo mission high-fives handed all around!
So now we’re at the point where this week is the final week of feature additions and testing and we plan to roll out to production in the coming weeks. We are very proud about how this whole thing has turned out especially considering Matt had never touched .NET as of 3 weeks ago and yet has managed to produce a very slick, full-featured application in this time. This project has really kickstarted my desire to learn more on the development side of the house hence those of you who follow me on Twitter have noticed me asking more and more about .NET, C# and all of those fun things you guys do.