Breaking into BI: Where to begin?

April 15, 2014 2 comments

This morning, I received some interesting news. A good friend of mine, who has been in IT for years mostly in the operations and technical documentation side, had signed up for the first test along the Microsoft BI MCSE path. How proud I was, you probably can never imagine. It also got me thinking: where do you begin nowadays?

8(ish) years ago, when I was just learning the Microsoft BI Stack, the starting point was pretty well defined: start with learning Kimball style star schema, then ETL with SSIS, OLAP with SSAS, and finally reporting with SSRS, Excel and maybe Performance Point M&A.

Now, where to begin? There are so many more options now, getting ramped up makes my head spin. Power BI, Cloud vs. On-Premise, Big Data, In-Memory. There are so many more way to “do BI” in the Microsoft world now than there were even just 10 years it’s staggering.

I think, for someone just starting out, building that understanding of Kimball Methodology is still really important. As much as we want to believe that PowerPivot and free-form designed models are going to rule the BI world, the fact of the matter is that the EDW is here to stay; It’s not dead.

What’s changed, however, is that someone starting out probably doesn’t need IMMERSION into the EDW world to break to Analytics like before. Knowing the difference between a Fact Table and Dimension Table will be a pretty good start to being able to develop a beginning Power Pivot model. However, Power BI simplifies the model development, deployment, and reporting path.

The above being said, I can’t imagine being able to really get a handle of the BI world without understanding star-schema design, so I think at some point within the first few months of train, immersion into star-schema design WILL be necessary.

So where to begin? I’ve been thinking about this for only a few hours at this point, and I want to open this post up to discussion. Here’s my thought of a plan to begin learning:

• Introduction to Analytics Section
o Intro to Kimball based Star Schema
o Basic Data Modeling with Power Pivot
o Analytic reporting with Excel and Power View
• Intermediate Analytic and Reporting
o Designing a full Kimball Data Mart
o Intro to data integration with SSIS
o Modeling star schema with SSAS Tabular
o Deployment of Analytics through Power BI and SharePoint 2013
• Expert Analytics
o Advanced Dimensional modeling topics (SCD, inferred members, etc)
o Analytics models with SSAS Multidimensional
o Designing an enterprise reporting environment (Excel, SSRS, PPS)

Of course, there are lots of other topics to consider in this list as well (that maybe didn’t exist before): Big Data (Hadoop, HDInsight), Mobile BI, Self-service model creation, cloud deployment, data mining, predictive analytics, etc…I think those topics are very important, however, I think if the goal is to become a Microsoft BI Expert, a foundation in the SQL Server BI Stack is key before jumping over to more advanced techniques.

Right now is an amazing time to be involved with Microsoft BI – it can seem like a pretty daunting task to jump in a start learning from square one, but the rewards are pretty great.

2014 Upcoming Presentations and a Discount Code

February 5, 2014 Leave a comment

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!

Categories: Uncategorized

The Worst Bar Chart of 2014? I think not.

February 4, 2014 2 comments

Recently Karen Lopez (b | t) offered up a candidate for “Worst Bar Chart of 2014”. It’s really bad, but I don’t think is the worst, here’s why.

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.

Happy charting,


Categories: Theory, Visualization

SQL Saturday Kalamazoo – Pre conference announcement

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.

Register with Allen here

A Deep Dive Into Waits-Based Performance Tuning with Eddie Wuerchimage

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!

Register with Eddie here

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!

Categories: Uncategorized

Guidelines for troubleshooting Kerberos Delegation – SharePoint and MSBI

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.


What Kerberos often seems like

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.

Common Causes

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.

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.

Common Causes:

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.

24 Hours of PASS is Coming: Will YOU be There?

24HOP_LogoThe latest edition of 24 Hours of PASS (#24HOP) is almost here. This time it’s a Summit  Preview!  I’m extremely excited about it for many reasons, one of those being I’m Speaking!!

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!

PASS Business Analytics Conference – I’m Speaking!

January 23, 2013 1 comment


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.

I will be joined by MSBI Specialist and friend,
Julie Koesmarno (b | t).  Join Julie and I as we take you on a tour of Narrative Reporting techniques in Microsoft Excel 2013.

For full session details see here.

Register for the PASS Business Analytics Conference here.

See you there!

Categories: Uncategorized Tags: , ,

Get every new post delivered to your Inbox.

Join 858 other followers