Hello everyone and welcome to this month’s T-SQL Tuesday. This month’s topic is all about ! 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 so when you create and publish your report, embed it in your 桃子视频post! Here’s a couple of great examples of this feature in action:
How Do I Get Started?
If you’ve never used Power BI before it’s REALLY easy to get started. Simply head over to 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. 桃子视频), 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 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 (|) actually you can take advantage of. Want more great Power BI information? Make sure to check out Adam Saxton’s (| ) and subscribe for awesome bi-weekly, content.
Want some inspiration on what you can do? Check out the 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?
桃子视频is a monthly blog party hosted by a different blogger each month. This blog party was founded by Adam Machanic (|). 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 桃子视频logo from above and the image should link back to this blog post.
Don鈥檛 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 .
Here we go again with another round of 桃子视频fun this time brought to you by recent SQL Certified Master (MCM), SQL University professor, and my brother from another mother Robert Davis (| ).
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 , , , 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 . 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 , and .
How Do You Teach?
Ah : 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 “” in which聽Jonathan Medd (| )聽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鈥檚 been short to say the least. Without further ado, here鈥檚 this month鈥檚 #TSQL2sDAY roundup:
reports in on her love for all things Reporting Services.
fills in the gaps for us with a brilliant (as always) Intellisense post.
gives us some great insight on core database engine improvements. If you don鈥檛 think R2 brought anything to the core product stop and read this post.
merges several answers together with IntelliSense, Resource Governor and the MERGE statement! Also Andy鈥檚 new on my radar and has a so time to add another person to follow on Twitter/add to Reader!
packs a ton of great information in his post about compression.
talks about the new T-SQL and query writing improvements. This is awesome as I wasn鈥檛 aware of some of these new additions! Developers, this is a MUST read! Once again, another great blog added to my reader!
, one of the newest folks to attain the prestigious SQL Server Master certification, brings to our attention something you wouldn鈥檛 really think about: sysprep support! SWEET! Great walkthrough example using a virtual machine on Windows Server 2008 R2. Definitely an administrator MUST read.
takes us for a trip to the clouds with the SQL Azure team and SQL Server 2008 R2鈥檚 SQL Azure integration. If you鈥檙e even thinking about putting your database in the cloud check out this post and subscribe to the .
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!
hasn鈥檛 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.
, of fame, gives us some great insight on StreamInsight. Honestly this feature also excites me so this is a really nice intro to the feature.
is a man after my own heart by talking about . Also always great to discover another great blog from across the pond.
, who did a fantastic job last week with SQL University鈥檚 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!
, aka , could鈥檝e written a whole book on all the new BI features but instead focused on Reporting Services. Nice breakdown of new hotness in SSRS.
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 as well.
, aka , 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.
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!
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.
throws us for a loop as his favorite part about SQL 2008 R2 doesn鈥檛 even have to do with the features! Check it out.
is excited by what鈥檚 NOT there with his overview of filtered indexes in 2008 and how they helped him solve a specific issue on a homegrown solution.
It鈥檚 another month and so we have ourselves another 桃子视频event, this time hosted by Aaron Nelson ( | ) with this . If you don鈥檛 know what 桃子视频is here鈥檚 a quick blurb from Aaron鈥檚 blog:
You Don鈥檛 Know What 桃子视频Is Yet?
(Also on twitter as ) 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:
#001 Adam Machanic started this off with a topic of
#002 Adam then wanted to hear about your
#003 Rob Farley got romantic on us and asked about
#004 Mike Walsh wanted to glean the ins and outs of
So given that this month we (, and myself) are proud to be releasing I figured this month’s TSQL Tuesday could tie in nicely. So how can one use Reporting Services in conjunction with the power of Policy-Based Management (PBM)聽in SQL 2008? Well, you’re in luck!
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 that I thought was interesting and would share with you.
For聽 this month鈥檚 meme is hosted by Mike Walsh of (). For my post I don鈥檛 have a solution so much as a nugget of information to 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鈥檚 seen that the Oracle DBAs he鈥檚 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鈥ver and over again. What鈥檚 that recommendation you ask? Well in NetApp world they have what are called Aggregates which are聽 nothing more multiple RAID groups. Here鈥檚 the excerpt from Wikipedia about it:
NetApp supports either , , or disk drives, which it groups into (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.
Ok, so what鈥檚 so different about that? Well that鈥檚 not the part that鈥檚 interesting. What鈥檚 interesting is explicitly states:
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 [2].
Now think about that for a minute. As a SQL Server DBA you鈥檙e probably having a mental breakdown as I did when first slapped with this one as they鈥檙e 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鈥檚 algorithms function, and WHY this best practice exists and isn鈥檛 as bad as it appears at first glance. Apparently because of the way NetApp鈥檚 Aggregates work the more you expand your Aggregate (read also: add more disks) you鈥檙e actually helping improve performance as you鈥檙e 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, 鈥淚鈥檓 not a storage guy, I鈥檓 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鈥檛 make sense can and will come up in your career. Should you question them? Without a doubt! After all, it鈥檚 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鈥檚 and whys. In this post I talked about NetApp鈥檚 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鈥檙e just a quick 鈥�
Follow the hashtag on Twitter to check out everyone’s posts.
Well this is the second 桃子视频(my first as I missed the first one). is a great idea put forth by Adam Machanic ( on ). Here’s the summary of this month’s post challenge:
Theme
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, , , 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 ( on Twitter) also talking about this project. The other piece of our “development group” is another network analyst named Horace Knight. This story doesn鈥檛 contain any special programming tricks and I can鈥檛 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.
Preface
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鈥檙e 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鈥檚 this might not be a HUGE deal but we鈥檙e 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鈥ow Do We Do This?
So despite having GE tell us these things I鈥檝e covered, the whole process was still a little confusing (took months of back and forth to finally understand and hammer out what I鈥檝e 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鈥檚 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 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鈥檚 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鈥檛 anticipated or known and these were handled manually.
Enter the () Dragon
.NET: Yeah it's THAT badass
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 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鈥檓 hoping that Matt puts up a post about this project as the current iteration really is culmination of Horace鈥檚 original vision for this application along with a lot of additional ingenuity and hard work from Matt and myself.
Evolution
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鈥檛 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鈥檚 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鈥檚 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.
This is what what gets done before app even opens...
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鈥檛 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鈥檙e 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.
