Upcoming presentations and a discount code
2014 has come in like a tidal wave! We’re already in February, and I’m just barely recovered from the Holiday vacation.
I’ve been a busy bee here in Michigan, having presented at SQL Saturday Nashville on 1/18/2014 – Great job again, Nashville Team!! – and I’ve got another event lined up.
This weekend, on Feb 8th, 2014 I’ll be speaking at SQL Saturday #241 in Cleveland, OH! My topic is Visual Analytics with HDInsight and Power View. This has been an area that I’ve been interested in, and playing around with it has proven to be very exciting. If you are a SQL Professional that is curious about Hadoop and Big Data this session will show how it all works, how to get HDInsight up and running quickly, and get started with Hive to begin transferring your SQL knowledge to a new technology. Finally, we’ll complete the circle with showing how to incorporate it with SSAS and use Power View to visualize the data. It will be an exciting and knowledge filled hour!
You can download my presentation materials ahead of time here. I hope to see you there!
Moving father into the future, I’ll be presenting again this year at the PASS Business Analytics Conference in San Jose, CA! This time, I’ll be showcasing Master Data Services (MDS) and Data Quality Services (DQS). I’ll be giving an overview of how the technology works, how it’s set up, and then show an example of how we’ve used it in the past to help clients manage Master Data efficiently.
If you’re considering attending this event, I’ve got a special discount code for you! Using my code BASB5V, you’ll be given a $150 discount to the conference! This code can’t be used with any other discount offers, and rates rise as we get closer to the event, so register now and use this code if you are ready to go!
I’m looking forward to both of these events. If you’re attending, I’d love to get a chance to chat with you. Happy analyzing!
First, it’s not a bar chart. It’s a waterfall chart. Now, beyond that, there are lots of things ‘wrong’ with the implementation of it that I would like to address here.
Before I begin dissecting, a little explanation of a waterfall chart. At first glance, a waterfall chart can easily be mistaken for a bar chart. It has bars, and is a chart, but that’s where the similarity ends. Typically a waterfall chart is used to tell a story. A story of how data is moved from Data Point A to Data Point B.
A classic example is inventory. I give you, Exhibit A. Ok, this is an extremely simple example I made with MSPaint – the graphics tool of all power users – still, it should help to prove my point.
This chart, remember it’s not a bar chart, tells the story of HOW July’s ending Inventory Total made it to August’s Inventory Total. Green bars are increases, Red bars are decreases – data labels for clarity. At a glance, you can see if the store is receiving equivalent amounts of inventory as they are selling, etc. Pretty neat visualization I think.
That’s a waterfall chart in a nutshell, so let’s move on.
Looking at the example that Karen provided, I see a few things (or more) wrong with it. Here are the big points (and those easily fixable with MSPaint).
First, the colors. The blue portion of the chart is supposed to show movement. It doesn’t. Which direction does blue go in? One section it’s moving left, another, right. It’s also a starting point and it’s an ending point. Additionally it’s used to show the deficit created by the 2018 supply vs. demand. So, I propose modification #1 – new colors.
In this new example, green signifies an increase, red a decrease. The ending point of the red ‘bar’ is how we arrive at the Projected 2018 Supply starting at the 2008 Employment. (156 + 161 – 32)
Second, the data labels. In this example, the data labels are not related to the movement of the data. All of the labels are oriented towards the right terminator of the bar, but the right terminator is not always the ending position of the bars movement. Some quick rearranging of the data labels will help to make it a bit clearer that where each bar starts, and where it ends.
Finally, the last ‘bar’ on the chart doesn’t follow the same format as the others. It’s not showing movement. It’s showing the portion of the total that is a deficit. Additionally, the data labels here DON’T EXPLAIN THE DEFICIT BUT RATHER THE TOTAL. This is against so many good rules of visualization it hurts my head just thinking about it. To fix this final bar we would want to modify colors, change the data label to a scale (showing 475 being the total) and then highlight the deficit with the correct number in question as a data label.
There are additional confusions created by alternating the row label color (why?), but again, not easily solved with MSPaint, so just imagine how great it looks when labels are all one color.
Here’s how I would have visualized a waterfall chart using this data. What do you think? More or less clear than the original version?
In specific rebuttal to Karen’s post, do I think this is the ‘Worst Bar Chart of 2014?’ No. Mostly because it’s not a bar chart. Still, it’s a pretty awful chart and while I’ve shown a few ways it can be better it took WAY to much explanation for it to be an effective visualization. If you have to explain your chart in words, then why visualize it at all?
To reiterate one of Karen’s most impactful closing points “If your chart leaves viewers thinking ‘I’m not sure’ more than once, it’s not effective.” If you are in the business of publishing visualizations to the public, please show you charts to someone who doesn’t know the data as well as yourself. Their response will be good litmus test of whether your chart will be effective out in the wild.
SQL Saturday Kalamazoo is less than a month away! We are busily preparing the event to ensure a fantastic day of learning and networking.
One part of the event we have been working on is securing the Pre-conference information. We are now happy to announce two pre-conference options for SQL Saturday attendees. This is our first year holding pre-conferences and we are very excited to bring in Allen White and Eddie Wuerch to spend a full day diving deep into their topics. Interested in attending? See below for session details, and registration links.
Automate and Manage SQL Server with PowerShell with Allen White
This soup-to-nuts all day workshop will first introduce you to PowerShell, after which you’ll learn the basic SMO object model, how to manipulate data with PowerShell and how to use SMO to manage objects. We’ll then move on to creating Policy-Based Management policies, work with the Central Management Server, manage your system inventory and gather performance data with PowerShell. We’ll wrap up with a look at the new PowerShell cmdlets introduced for SQL Server 2012 and how you can use PowerShell to manage SQL Server 2012 in server environments including Windows Server Core. After this one day, you’ll be ready to go to work and able to use PowerShell to make you truly effective.
Start with a simple proposition: a process is either working or waiting. You can tune the working part, but are you seeing the whole picture? There are many different resources on which your process could be waiting – a lock, memory, disk, CPU, and much more. When a process must wait, SQL Server will log it. There are hundreds of different wait types, and they are a gold mine of data for finding and solving performance problems – and proving the changes worked. Eddie Wuerch takes his extensive experience as a speaker, trainer, mentor, and DBA in one of the largest and busiest SQL Server environments in the world and distills it into a collection of performance tuning topics for DBAs and developers tuning databases of all sizes. After attending this seminar, you will be able to gather wait stats and use them to zero in on performance issues affecting your databases. Stop guessing, start knowing!
Please note that pre-conference registration IS separate from the SQL Saturday registration. Early bird pricing for Pre-conferences ends on 10/15/2013, so sign up now for the best pricing options! If you’re still looking to register for SQL Saturday to see Allen, Eddie, and 25 other great presenters, sign up today!
Whether running SharePoint 2010 or SharePoint 2013, Kerberos Delegation is an important part of delivering a fully functional BI solution. It’s also not a trivial task to configure correctly. There are many layers involved and the larger the SharePoint environment, the more complex it is.
The purpose of this post isn’t to explain how to configure delegation from the ground up, that topic has been covered very well with documents such as Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products. This document, by the way, is still largely relevant for SharePoint 2013, although some of the SharePoint setting have changed, the foundational elements and Active Directory settings are the same.
Rather, the purpose of this post is to help troubleshoot an existing environment where delegation is not working properly. This task can often feel like fighting the mythical monster namesake, a Kerberos.
Troubleshooting an established environment can be a mammoth task. I’ve worked through enough of them to know how to break it into manageable pieces, which most of the time, will lead to a configuration error that can be fixed and get the environment delegating identities as expected.
My goal is, by end of this post, to help explain how to identify the major pieces of configuration, determine which pieces are or are not working, and offer some common issues and solutions to get this up and running correctly. I’ve found that by following the three or four major steps below, the majority of environments with incorrect delegation configuration will be corrected.
Verify Delegation to Data Source – Example: SSAS
Using a domain connected client machine, create a new connection to the domain connected SSAS server in Excel. One the data base list is populated, log into the database server and check the Security Event Log for Event 4624. If Event does not specify Kerberos as Logon Process, delegation is not configured.
1. Incorrect Service Principal Name (SPN) registration
2. Service account not trusted for delegation
3. Computer account not trusted for delegation
1. Using setspn.exe, verify currently registered SPN entries and adjust as needed for data source
2. Using an account with Domain Admin rights, adjust the delegation trust level for the service account to “Trust this user for delegation to any service” or “Trust this user for delegation to specified services only”
3. Using an account with Domain Admin rights, adjust the delegation trust level for the computer accounts to “Trust this user for delegation to any service” or “Trust this user for delegation to specified services only.” Note: this should be done for any computer accounts that fall in the path from client to middle tier to data source
Verify Delegation to Web Front End
If delegation to the data source is working – Event 4264 lists Kerberos as the Logon Process – then the next step is to verify that delegation to the SharePoint Web Front End is working properly. Very similar to the data source, this test should be done on a domain connected client machine.
Open IE and browse to the root site, http://spdemo, for example. Once the page loads, log into the SharePoint WFE server and again check for Event 4264 in the Security log. Just as before, if the Logon Process does not mention Kerberos, then delegation is not configured correctly. See below for common causes.
1. Root site not in client machine INTRANET zone.
2. Client machine not configured to use Integrated Windows Auth
3. Service account not trusted for delegation
4. Computer account not trusted for delegation
5. SPN registration incorrect for site collection
6. Site collection authentication provider setting incorrect
7. IIS configuration incorrect
1. Verify root site is in INTRANET zone. Add if not.
2. Check Internet Options -> Advanced tab for “Use Integrated Windows Authentication”. Select if currently unselected (will require IE restart)
3. Using an account with Domain Admin rights, adjust the delegation trust level for the service account to “Trust this user for delegation to any service” or “Trust this user for delegation to specified services only”
4. Using an account with Domain Admin rights, adjust the delegation trust level for the computer accounts to “Trust this user for delegation to any service” or “Trust this user for delegation to specified services only.” Note: this should be done for any computer accounts that fall in the path from client to middle tier to data source
5. Use setspn.exe to verify registered SPN for AppPool service account. Correct as necessary.
6. In Central Administration verify the setting for the default Authentication Provider for the site collection. Ensure that the Integrated Windows Authentication option is set to “Negotiate (Kerberos)”
7. Ensure the Application Pool is configured correctly for Kerberos delegation. See Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products for detailed IIS configuration settings
Verify Delegation of Service Application – Example: PerformancePoint Services
The final step to test is the service application itself. For example, testing PerformancePoint Services would mean creating a new data source with “Per User Identity” enabled. Tracing the database while creating this should show whether or not Kerberos delegation is working.
1. Service account not trusted for delegation
2. Claims to Windows Token Service
1. If the application is running under a different service account that the WFE or databases, then it’s possible that it’s not being trusted for delegation like the others (set correctly above). However, the Delegation tab might not be visible in AD Users and Computers. In order to make it visible, create a bogus SPN. For example, the following SPN can be used for PerformancePoint Services
setspn.exe –s PPS/SPDEMO DEMO/svc_pps
setspn.exe –s PPS/SPDEMO.DEMO.LOCAL DEMO/svc_pps
Note: In this example, this SPN does not point to a real Service Principal, but it does enable the Delegation tab in AD Users and Computers to be able to check the Trust settings.
2. If the service account is already trusted, then there is one more big section to check IF Claims authentication is being used. Note that Claims authentication is available in both SP2012 and SP2013, but enabled by default in SP2013. The following steps do not apply if Basic Authentication is being used.
Verify Claims to Windows Token Service
When claims authentication is being used in SharePoint, it’s important that everything is configured with it correctly to allow the C2WTS to create the appropriate Kerberos tickets. If it’s not configured correctly, it won’t work, but there may not be many logs pointing to that fact. This makes the service rather tricky to troubleshoot. The best way I’ve found is just to verify all of the settings when it’s believed to not be working correctly.
The white paper Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products goes over this really well, but see below for a high level checklist.
- Ensure the service account for C2WTS is a local administrator on the WFE
- Make sure the following SECPOL settings have the service account included (Local Policies/User Rights Assessment)
- Logon as a service
- Act as part of the operating system
- Impersonate a client after authentication
- In Central Admin make sure the C2WTS service account is included in Managed Accounts
- In Central Admin make sure the C2WTS service is configured to use the above managed account
- In the Services snap-in make sure the Claims to Windows Token Service is dependent on Cryptography Services
Once all of those setting are verified, restart the Secure Token Service in Central Admin (Services on Server). If C2WTS had incorrect settings, and everything above in this document tested successfully, then Kerberos delegation should be working now.
What if it’s still not delegating?
If after all of the above troubleshooting has been performed and delegation is still not happening, then it time to go through the referenced White paper (Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products) step and step and make sure that EVERYTHING is 100% configured correctly.
In my experience with troubleshooting a number of different environments, spending an hour to two and verifying correcting any issues with the above three areas, Data Source, WFE, Application/C2WTS, will usually result in an environment with working delegation. If not, then more time will need to be taken to go through the steps in close detail.
The good news is that SharePoint has some of the best documentation in the Microsoft landscape. If you’ve taken the time to read it all, then you’ll be very well prepared to take on the task of troubleshooting delegation in an existing environment.
Not only was I honored to be selected as a Summit Speaker this year (with TWO sessions no less), but I was also asked to present at #24HOP.
I will be presenting a fun session titled “Delivering Analytics with Excel: 10 Secrets for Success” – due to the shortened session length for #24HOP I will probably only get through 8 secrets, but still, it’s a lot of good content that will benefit any report developer.
The reason I love to talk about Excel is that EVERYONE uses it. Sure SSRS is nice and flashy, and PerformancePoint is my second true love (see my Summit sessions), but Excel is where business is done. Talking about Excel and how to really make it work well as a reporting tool is one of my favorite career-related pastimes.
I’ve managed to pull the prestigious 3AM EST time slot for my #24HOP presentation, so if you are in the US, put on your jammies and come join me as I talk about rows and columns and pivots (oh my!). Everyone else, grab some coffee, or some dinner and join me as well. I can’t wait to see you all there. While you’re at it, take a look at the other great #24HOP sessions. Sign up for those as well!
I’m excited and honored to announce that I’m presenting a Community Session at the PASS Business Analytics Conference in Chicago, IL on April 10 – 12.here.
Register for the PASS Business Analytics Conference here. See you there!
See you there!
By the end of this post, I will have you convinced that you can work out and stay fit all day at work. Seriously.
If you’ve not been to a gym in a long time, this might seem like a lot of working out, but I promise, in no time, you’ll be so used to this schedule that you’ll start to incorporate fitness into other aspects of your life; like dinner time!
Read on for my story:
Hi. I’m husky (read: fat). And out of shape. Well, I was… I liked to blame lots of outside factors: Fast food, preservatives, salt, fruit (no, really), sitting at a desk all day…
Wait, what was that last one? Sitting all day? That probably has something to do with it, right? About a year ago I decided to really do something about it. I really didn’t want to go as drastic as a new desk with fancy heights for standin’; I like my desk and I like my chair. I don’t want to stand all day. But, I did want to get in shape.
Problem is, I work from home. I also live in the woods. There is no gym or fitness center close to me. And honestly, I typically go 4 or 5 days without leaving the house so there’s little chance that I would end up at the gym on a regular basis.
Since I tend to put in a lot of hours in between work and family, completing a long, drawn out weekly workout schedule isn’t going to cut it either. I need something that I could do during the day that wouldn’t impact my productivity.
It took me a few iterations (I’m agile!) and several months, but I finally ended up on something that works well for me and I’d like to share it.
Staying fit really is simple. Although having a Shake Weight is pretty awesome, you don’t need a bunch of fancy gadgets. To stay fit, you only need your body. Nothing more than that; well, maybe a floor, and some gravity. But really, that’s it.
So, how do I stay fit AND get lots of fantastic work done without changing into workout gear? Simple. Read the following three tips:
1.) I have an hourly reminder set (on my phone) that tells me to do 10 push-ups.
It’s like having a drill instructor, without all of the yelling. It’s easy, takes 10 seconds to complete, and through the day I’m routinely doing 80 – 100 pushups. Spreading them out over the day helps with the “But I can’t do 100 push-ups” mentality. Everyone can do 10 push-ups. You can use your knees to start with (see here). Over time, you’ll get stronger and be able to do standard push-ups.
As you advance you can get really crazy and start doing things like
- Military Style Push-ups
- Explosive Push-ups
- Diamonds of Death
- Under the Fence
There are lots of examples online. I’ll let you investigate further.
2.) I also have a reminder to tell me to do 10 sit-ups.
Again, you CAN do 100 sit-ups a day. Start simple; build complexity as you get stronger. Anyone can do this. Other example for hourly reminders can include:
- Tricep Dips – Use your desk chair for simplicity!
- Squats – weighted or no!
- Lunges – great for cardio strength!
- Jumping Jacks – 25 for plyometric strength and cardio in one!
I routinely change up my reminders throughout the week so I’m not sticking with the same exercise every day. Variety is key when it comes to easy fitness.
3.) Engage a friend!
This is probably the most important thing I did to take my fitness to the next level. I found a friend (YAY!) that was willing to play a little game with me. Have you heard of tabatas? If not, read this, then come back….
As I was saying, I found a friend that was willing to play a little game with me. At random intervals through the day, we IM each other – keeping mindful of Presence Indicators – and say “Tabata Time!” Ok, we really don’t say that — it sounds to TMNT-ish.
After describing what the tabata is (at our discretion), we complete said tabata. An example might be: 60 second wall-sit, 10 push-ups, 20 lunges, Repeat, GO. This does take a little more time than the 10 push-ups mentioned above, but we have a limit that it can’t take any more than 5 minutes and there is a 10 minute window for accepting and starting. Doing this craziness with a friend makes it fun, and also helps to PUSH me. That’s a big one. Having that accountability is key.
Generally we do this 3 or 4 times a day, although some days may be more or less depending on uncontrollable factors. Still, it’s fun, it’s a workout, and it helps to keep you in shape.
If Cardio is more your style; there are things you can do there too!
Some of my friends have had the pleasure of building a treadmill desk – which I understand as a treadmill with a shelf attached for a laptop. Setting the speed to something nice and slow – under 2 mph – is good enough for an hour or two long walk while you check email, or write queries, or build cubes! That’s a lot of caloric burn during that productive time.
So what did we learn?
In a normal day, I spend no more than 30 minutes completing all of this working out: 100 or so push-ups, sit-ups, and various tabatas. No workout gear, no special attire. Simplicity. That’s what I love about this kind of working lifestyle. Even if you work in an office you can do this. Sure, you may look a little foolish to those around you…but, I ask you, who will look more foolish in 6 months when you’ve toned up and others are spreading out?
The above points have worked well for me and my friends. What would you like to be able to incorporate into your day to help stay in just a little better shape?