Kevin S. Goff: Stages of Data

COVID-19 Reporting Project and CODE Magazine article(s), using Microsoft Power BI (updated for week ending 7/18)

Summary:

I have an article in the July/August 2020 issue of CODE Magazine, on creating a public dashboard to show COVID-19 data (using Microsoft Power BI and SQL Server data). Some personal comments at the end on why I'm writing about this. 

First, here's a link to the CODE Magazine article (this is part 1 of what will likely be a 3-part series): 
https://bit.ly/2C0PxB6

Second, here is a public dashboard page that I'll be updating weekly on Sunday nights.
https://bit.ly/2A6lk3a

I'm using county-level data from the NYT:
https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-counties.csv

Third, on Tuesday night, July 21st, I'll be doing a remote user group presentation for the Philadelphia BI User Group.  
https://www.eventbrite.com/e/july-2020-philly-bi-user-group-virtual-meeting-tickets-113049989562?aff=ebdssbonlinesearch

Click here to download the slide deck for the 7/21 Meeting.

You can email me either at [email protected], or [email protected]

KG

================================================================================

Some Personal notes on this:

Personal comments: This is an extremely sad and tragic topic. I realize it seems morbid to write about using database technology to report on numbers, because these numbers represent lives with families and friends.   To date we have over 129,000 people in the United States that have died.  An analyst can break out these numbers in many ways, but here are 3 breakouts that are as sobering as they are compelling:

• Almost 25% of the deaths have occurred in the New York City and neighboring northern New Jersey areas.  
• At least 43% of the deaths have occurred in long-term care facilities.   Many view this number as conservative, because not all states provide complete reporting, either due to privacy restrictions or just general data collection challenges. 
• Roughly 80% of U.S. deaths have been age 65 and up, and about 60% of U.S. deaths have been ages 75 and up. 

Worldwide, COVID-19 has taken the lives of over 475,000 people.  We are seeing history, and unfortunately a very devastating history. So I can understand why someone would wonder about why I'd write about this topic.  

There are several reasons, one of which is this: people read books about the 1918 flu pandemic, books that contain charts and statistics that someone put together. Obviously, we live in the "age of information" but the concept still holds true.   But I've tried to approach this topic with great sensitivity.  

Also, anyone who has created video content knows it can take hours to produce 15 minutes of  something worthwhile.  As I already have a regular career /day job and family and have put in quite a bit of personal time on this project, I can't really devote hours to make perfect videos.  I'm doing these videos pretty much extemporaneously, with limited practice and a short script, and I'm not starting over if I have a few slip-ups halfway through.  :)  

Also, as I work with data for a living as a software developer/analyst,  most developers like to have some kind of public portfolio of their work.  For years I've faced the challenge of coming up with good data examples for conferences, SQL Saturday and User Group events, etc.   Actual work examples from different industries have all the great context and nuance, but then you have to mask all the data.  Given that all of this information is public, it provides a good source for a public example. 

Finally, for anyone who is interested in prior .NET/C#/SQL Server articles of mine, here's a link to all of them, dating back to 2004.
https://codemag.com/People/Bio/Kevin.Goff

I'm disabling comments on my blog. However, I'm definitely interested in communicating with people on this topic.

You can email me either at [email protected], or [email protected]

More to come...folks, take care of yourselves, stay safe, and play it smart!  Even if the numbers overwhelmingly point out the risk to older individuals, I implore everyone to do the right things. This virus doesn't just cause serious respiratory illness, it also can cause horrible vascular damage, especially in those with pre-existing conditions.  Please - play it smart!!!

03/30/2019 - New Name: Stages of Data

Exit stage left, Baker's Dozen

Enter, Stages of Data

After over a decade of using Baker's Dozen Productivity Series as my "alias", I decided to make a change.   

I have some recent content that I'll be posting here soon.

My download page should be working now.  Email me at [email protected] if you have any issues.  Thanks. 

KG

10/16/2017 - Follow-up to my talks at NYC Code Camp yesterday

Thanks to all who attended my 2 presentations at NYC Code Camp yesterday!

Here are the links to my slides from the 2 sessions:

Data Warehouse Patterns

In-Memory Technologies in SQL Server 2016

 

 

 

12/2/2016: An introduction to Columnstore indexes and SQL Query Store in SQL Server 2016

Most people are familiar with the phrase, "this will kill two birds with one stone". If you're not, the phase refers to an approach that addresses two objectives in one action. (Unfortunately, the expression itself is rather unpleasant, as most of us don't want to throw stones at innocent animals!)

Today I'm going to cover some basics on two great features in SQL Server: the Columnstore index (available only in SQL Server Enterprise) and the SQL Query Store. Microsoft actually implemented the Columnstore index in SQL 2012 Enterprise, though they've enhanced it in the last two releases of SQL Server. Microsoft introduced the Query Store in SQL Server 2016. So, what are these features and why are they important? Well, I have a demo that will introduce both features and show how they can help us.

Before I go any further, I also cover this (and other SQL 2016 features) in my CODE Magazine article on new features SQL 2016.

As a basic introduction, the Columnstore index can help speed up queries that scan/aggregate over large amounts of data, and the Query Store tracks query executions, execution plans, and runtime statistics that you'd normally need to collect manually. Trust me when I say, these are great features.

For this demo, I'll be using the Microsoft Contoso Retail Data Warehouse demo database. Loosely speaking, Contoso DW is like "a really big AdventureWorks", with tables containing millions of rows. (The largest AdventureWorks table contains roughly 100,000 rows at most). You can download the Contoso DW database here: https://www.microsoft.com/en-us/download/details.aspx?id=18279. Contoso DW works very well when you want to test performance on queries against larger tables.

Contoso DW contains a standard data warehouse Fact table called FactOnLineSales, with 12.6 million rows. That's certainly not the largest data warehouse table in the world, but it's not child's play either.

Suppose I want to summarize product sales amount for 2009, and rank the products. I might query the fact table and join to the Product Dimension table and use a RANK function, like so:

 

Here's a partial result set of the top 10 rows, by Total Sales.

 

On my laptop (i7, 16 GB of RAM), the query takes anywhere from 3-4 seconds to run. That might not seem like the end of the world, but some users might expect near-instant results (the way you might see near-instant results when using Excel against an OLAP cube).

The only index I currently have on this table is a clustered index on a sales key. If I look at the execution plan, SQL Server makes a suggestion to add a covering index to the table:

 

 

Now, just because SQL Server suggests an index, doesn't mean you should blindly create indexes on every "missing index" message. However, in this instance, SQL Server detects that we are filtering based on year, and using the Product Key and Sales Amount. So, SQL Server suggests a covering index, with the DateKey as the index key field. The reason we call this a "covering" index is because SQL Server will "bring along the non-key fields" we used in the query, "for the ride". That way, SQL Server doesn't need to use the table or the clustered index at all – the database engine can simply use the covering index for the query. Covering indexes are popular in certain data warehousing and reporting database scenarios, though they do come at a cost of the database engine maintaining them.

Note: Covering indexes have been around for a long time, so I haven't yet covered the Columnstore index and the Query Store.

So, I will add the covering index:

If I re-execute the same query I ran a moment ago (the one that aggregated the sales amount for each product), the query sometimes seems to run about a second faster, and I get a different execution plan, one that uses an Index Seek instead of an Index Scan (using the date key on the covering index to retrieve sales for 2009).

So, prior to the Columnstore Index, this could be one way to optimize this query in much older versions of SQL Server. It runs a little faster than the first one, and I get an execution plan with an Index Seek instead of an Index Scan.

However, there are some issues:

1. The two execution operators "Index Seek" and "Hash Match (Aggregate)" both essentially operate "row by row". Imagine this in a table with hundreds of millions of rows.
2. Related, think about the contents of a fact table: in this case, a single date key value and/or a single product key value might be repeated across hundreds of thousands of rows (remember, the fact table also has keys for geography, promotion, salesman, etc.) So, when the "Index Seek" and "Hash Match" work row by row, they are doing so over values that might be repeated across many other rows.

This is normally where I'd segue to the SQL Server Columnstore index, which offers a scenario to improve the performance of this query in amazing ways. But before I do that, let's go back in time. Let's go back to the year 2010, when Microsoft introduced an add-in for Excel known as PowerPivot. Many people probably remember seeing demos of PowerPivot for Excel, where a user could read millions of rows from an outside data source into Excel. PowerPivot would compress the data, and provide an engine to create Pivot Tables and Pivot Charts that performed at amazing speeds against the compressed data. PowerPivot used an in-memory technology that Microsoft termed "VertiPaq". This in-memory technology in PowerPivot would basically take duplicate business key/foreign key values and compress them down to a single vector. The in-memory technology would also scan/aggregate these values in parallel, in blocks of several hundred at a time. The bottom line is that Microsoft baked a large amount of performance enhancements into the VertiPaq in-memory feature for us to use, right out of the proverbial box.

Why am I taking this little stroll down memory lane? Because in SQL Server 2012, Microsoft implemented one of the most important features in the history of their database engine: the Columnstore index. The index is really an index in name only: it is a way to take a SQL Server table and create a compressed, in-memory columnstore that compresses duplicate foreign key values down to single vector values. Microsoft also created a new buffer pool to read these compressed vector values in parallel, creating the potential for huge performance gains.

So, I'm going to create a columnstore index on the table, and I'll see how much better (and more efficiently) the query runs, versus the query that runs against the covering index. So, I'll create a duplicate copy of FactOnlineSales (I'll call it FactOnlineSalesDetail_NCCS), and I'll create a columnstore index on the duplicated table – that way I won't interfere with the original table and the covering index in any way.

 

Next, I'll create a columnstore index on the new table:

Note several things:

• I've specified several foreign key columns, as well as the Sales Amount. Remember that a columnstore index is not like a traditional row-store index. There is no "key". We are simply indicating which columns SQL Server should compress and place in an in-memory columnstore.
• To use the analogy of PowerPivot for Excel – when we create a columnstore index, we're telling SQL Server to essentially do the same thing that PowerPivot did when we imported 20 million rows into Excel using PowerPivot

So, I'll re-run the query, this time using the duplicated FactOnlineSalesDetail_NCCS table that contains the columnstore index.

This query runs instantly – in less than a second. And I can also say that even if the table had hundreds of millions of rows, it would still run at the proverbial "bat of an eyelash". We could look at the execution plan (and in a few moments, we will), but now it's time to cover the Query Store feature.

Imagine for a moment, that we ran both queries overnight: the query that used the regular FactOnlineSales table (with the covering index) and then the query that used the duplicated table with the Columnstore index. When we log in the following morning, we'd like to see the execution plan for both queries as they took place, as well as the execution statistics. In other words, we'd like to see the same statistics that we'd be able to see if we ran both queries interactively in SQL Management Studio, turned in TIME and IO Statistics, and viewed the execution plan right after executing the query.

Well, that's what the Query Store allows us to do – we can turn on (enable) Query Store for a database, which will trigger SQL Server to store query execution and plan statistics so that we can view them later.

So, I'm going to enable the Query Store on the Contoso database with the following command (and I'll also clear out any caching):

Then I'll run the two queries (and "pretend" that I ran them hours ago):

 

Now let's pretend they ran hours ago. According to what I said, the Query Store will capture the execution statistics. So how do I view them? Fortunately, that's quite easy. If I expand the Contoso DW database, I'll see a Query Store folder.

The Query Store has tremendous functionality and I'll try to cover much of it in subsequent blog posts. But for right now, I want to view execution statistics on the two queries, and specifically examine the execution operators for the columnstore index.

So I'll right-click on the Top Resource Consuming Queries and run that option. That gives me a chart like the one below, where I can see execution duration time (in milliseconds) for all queries that have been executed. In this instance, Query 1 was the query against the original table with the covering index, and Query 2 was against the table with the columnstore index. The numbers don't lie – the columnstore index outperformed the original table/covering index by a factor of almost 7 to 1!!!

 

I can change the metric to look at memory consumption instead. In this case, note that query #2 (the columnstore index query) used far more memory. This demonstrates clearly why the columnstore index represents "in-memory" technology – SQL Server loads the entire columnstore index in memory, and uses a completely different buffer pool with enhanced execution operators to process the index.

 

OK, so we have some graphs to view execution statistics – can we see the execution plan (and execution operators) associated with each execution? Yes, we can! If you click on the vertical bar for the query that used the columnstore index, you'll see the execution plan below.

The first thing we see is that SQL Server performed a columnstore index scan, and that represented nearly 100% of the cost of the query. You might be saying, "Wait a minute, the first query used a covering index and performed an index seek – so how can a columnstore index scan be faster?"

 

 

That's a legitimate question, and fortunately there's an answer. Even when the first query performed an index seek, it still executed "row by row". If I put the mouse over the columnstore index scan operator, I see a tooltip (like the one below), with one important setting: the Execution Mode is BATCH (as opposed to ROW, which is what we had with the first query using the covering index). That BATCH mode tells us that SQL Server is processing the compressed vectors (for any foreign key values that are duplicated, such as the product key and date key) in batches of almost 1,000, in parallel. So SQL Server is still able to process the columnstore index much more efficiently.

 

 

Additionally, if I place the mouse over the Hash Match (Aggregate) task, I also see that SQL Server is aggregating the columnstore index using Batch mode (although the operator itself represents such a tiny percent of the cost of the query)

 

Finally, you might be asking, "OK, so SQL Server compresses the values in the data, treats the values as vectors, and read them in blocks of almost a thousand values in parallel – but my query only wanted data for 2009. So is SQL Server scanning over the entire set of data?" Again, a good question. The answer is, "Not really". Fortunately for us, the new columnstore index buffer pool performs another function called "segment elimination". Basically, SQL Server will examine the vector values for the date key column in the columnstore index, and eliminate segments that are outside the scope of the year 2009.

I'll stop here. In subsequent blog posts I'll cover both the columnstore index and Query Store in more detail. Essentially, what we've seen here today is that the Columnstore index can significantly speed up queries that scan/aggregate over large amounts of data, and the Query Store will capture query executions and allow us to examine execution and performance statistics later.

09/28/2015 – Dynamic PIVOT and Multiple Groupings

Recently someone asked the following, where we can use (and almost "need" to use) dynamic SQL to accomplish a dynamic PIVOT with multiple groupings. The original example had some very specific data requirements, so I'm recreating the example to simplify the data – while still keeping to the overall requirements.

As I'll point out throughout this blog post, this is another case where identifying the pattern is part of the battle.

Before I go any further, I have 3 download files. The script to generate the data, the script for the query model we'll want to generate, and then the final query that will use dynamic SQL to dynamically generate the desired result.

Suppose we have this data: a table called dbo.SalesData (on the left), with Sales Amounts by Client on specific days….and a table called dbo.ReturnData, with Sales Returns by Data, Client, and a Return Reason. I realize most production systems would have a more elaborate table structure. I'm simply using the core necessary elements for this example.

Sales	Returns w/multiple Return Types

In the end, we'd like to produce a result set that shows the following. Notice three things:

1. The columns essentially pivot all of the possible Return Reasons, after showing the sales amount
2. The result set contains subtotals by the week ending (Sunday) date across all clients (where the Client is NULL)
3. The result set contains a grand total row (where the Client and Date are both NULL)

First, before I get into the SQL end – we could use the dynamic pivot/matrix capability in SSRS. We would simply need to combine the two result sets by one column and then we could feed the results to the SSRS matrix control, which will spread the return reasons across the columns axis of the report. However, not everyone uses SSRS (though most people should!). But even then, sometimes developers need to consume result sets in something other than a reporting tool. So for this example, let's assume we want to generate the result set for a web grid page – and possibly the developer wants to "strip out" the subtotal rows (where I have a ResultSetNum value of 2 and 3) and place them in a summary grid. So bottom line, we need to generate the output above directly from a stored procedure.

And – as an added twist – next week there could be Return Reason X and Y and Z. So we don't know how many return reasons there could be. We simple want the query to pivot on the possible distinct values for Return Reason. Here is where the T-SQL PIVOT has a restriction – we need to provide it the possible values. Since we won't know that until run-time, we need to generate the query string dynamically using the dynamic SQL pattern. The dynamic SQL pattern involves generating the syntax, piece by piece, storing it in a string, and then executing the string at the end. Dynamic SQL can be tricky, as we have to embed syntax inside a string. But in this case, it our only true option if we want to handle a variable number of return reasons.

I've always found that the best way to create a dynamic SQL solution is by figuring out what the "ideal" generated-query would be at the end (in this case, given the Return reasons we know about)….and then reverse-engineering it by piecing it together one part at a time.

And so, here is the SQL we need – if we knew those Return Reasons (A through D) were static and would not change. The query does the following:

1. Combines the data from SalesData with the data from ReturnData, where we "hard-wire" the word Sales as an Action Type form the Sales Table, and then use the Return Reason from the Return Data into the same ActionType column. That will give us a clean ActionType column on which to pivot. We are combining the two SELECT statements into a common table expression (CTE), which is basically a derived table subquery that we subsequently use in the next statement (to PIVOT)
2. A PIVOT statement against the CTE, that sums the dollars for the Action Type being in one of the possible Action Type values. Note that this isn't the final result set. We are placing this into a CTE that reads from the first CTE. The reason for this is because we want to do multiple groupings at the end.
3. The final SELECT statement, that reads from the PIVOTCTE, and combines it with a subsequent query against the same PIVOTCTE, but where we also implement two groupings in the GROUPING SETS feature in SQL 2008:
   1. GROUPING by the Week End Date (dbo.WeekEndingDate)
   2. GROUPING for all rows ()

So if we knew with certainty that we'd never have more return reason codes, then that would be the solution.

However, we need to account for other reason codes. So we need to generate that entire query above as one big string – where we construct the possible return reasons as one comma separated list.

I'm going to show the entire T-SQL code to generate (and execute) the desired query. And then I'll break it out into parts and explain each step. So first, here's the entire code to dynamically generate what I've got above.

There are basically five steps we need to cover.

Step 1, we know that somewhere in the mix, we need to generate a string for this in the query:

[SalesAmount],[Reason A],[Reason B],[Reason C],[Reason D]    

What we can do is built a temporary common table expression that combines the hard wired "Sales Amount" column with the unique list of possible reason codes. Once we have that in a CTE, we can use the nice little trick of FOR XML PATH('') to collapse those rows into a single string, put a comma in front of each row that the query reads, and then use STUFF to replace the first instance of a comma with an empty space. This is a trick that you can find in hundreds of SQL blogs. So this first part builds a string called @ActionString that we can use further down.

Step 2: we also know that we'll want to SUM the generated/pivoted reason columns, along with the standard sales column. So we'll need a separate string for that, which I'll call SUMSTRING. I'll simply use the original @ActionString, and then REPLACE the outer brackets with SUM syntax, plus the original brackets.

Step 3: Now the real work begins. Using that original query as a model, we want to generate the original query (starting with the UNION of the two tables), but replacing any references to pivoted columns with the strings we dynamically generated above. Also, while not absolutely required, I've also created a variable to simply any carriage return/line feed combinations that we want to embed into the generated query (for readability). So we'll construct the entire query into a variable called SQLPivotQuery.

Step 4: We continue constructing the query – again, concatenating the syntax we can "hard-wire" with the @ActionSelectString (that we generated dynamically to hold all the possible return reason values)

Step 5: Finally, we'll generate the final part of the Pivot Query, that reads from the 2^nd common table expression (PIVOTCTE, from the model above) and generates the final SELECT to read from the PIVOTCTE and combine it with a 2^nd read against PIVOTCTE to implement the grouping sets.

Finally, we can "execute" the string using the SQL system stored proc sp_executeSQL

So hopefully you can see that the process to following for this type of effort is…

1. Determine what the final query would be, based on your current set of data and values (i.e. built a query model)
2. Write the necessary T-SQL code to generate that query model as a string. Arguably the most important part is determining the unique set of values on which you'll PIVOT, and then collapsing them into one string using the STUFF function and the FOR XML PATH('') trick

09-06-2015: A Baker’s Dozen of Reflections…

As part of my Baker's Dozen Productivity Series articles in CoDe Magazine, I've sometimes written editorial/opinion pieces. In true Baker's Dozen form, each opinion article contained 13 items. Sometimes they followed a theme, and sometimes they just represented 13 random opinions on different aspects of our industry.

Going forward, I'm going to post these opinion pieces here on my blog. If anyone wants to view prior "Baker's Dozen of Reflection" articles (which I'll refer to as BDR from now on), here are the links. Some of the items are a bit dated, but most of the opinions I've expressed still have some relevance today.

Article Link	Article Issue
http://www.codemag.com/Article/1109081	Sep/Oct 2011 issue (special editorial on leadership
http://www.codemag.com/Article/0807021	Jul/Aug 2008 issue
http://www.codemag.com/Article/0707021	Jul/Aug 2007
http://www.codemag.com/Article/0611021	Nov/Dec 2006

 

So what's on my mind today? Well, at least 13 items! Two summers ago, I wrote a draft BDR that focused (in part) on the role of education and the value of a good liberal arts background – not just for the software industry but even for other industries as well. One of the themes of this particular BDR emphasized a pivotal and enlightened viewpoint from renowned software architect Allen Holub regarding liberal arts.

I'll (faithfully) paraphrase his message: he highlighted the parallels between programming and studying history, by reminding everyone that history is reading and writing (and I'll add, identifying patterns), and software development is also reading and writing (and again, identifying patterns). And so I wrote an opinion piece that focused on this and other related topics.

But until today, I never got around to either publishing/posting it. Every so often I'd think of revising it, and I'd even sit down for a few minutes and make some adjustments to it. But then "life in general" would get in the way and I'd never finish it. So what changed?

A few weeks ago, fellow CoDe Magazine columnist and industry leader Ted Neward wrote a piece in his regular column, "Managed Coder", that caught my attention. The title of the article is On Liberal Arts, and I highly recommend that everyone read it.

Ted discusses the value of a liberal arts background, the false dichotomy between a liberal arts background and success in software development, and the need to write/communicate well. He talks about some of his own past encounters with HR personnel management regarding his educational background.

He also emphasizes the need to accept and adapt to changes in our industry, as well as the hallmarks of a successful software professional (being reliable, planning ahead, and learning to get past initial conflict with other team members).

So it's a great read, as are Ted's other CoDe articles and blog entries. It also got me back to thinking about my views on this (and other topics) as well, and finally motivated me to finish my own editorial. So, better late than never, here are my current Baker's Dozen of Reflections:

 

1. I have a saying: Water freezes at 32 degrees. If you're in a training/mentoring role, you might think you're doing everything in the world to help someone – when in fact, they're only feeling a temperature of 34 degrees and therefore things aren't solidifying for them. Sometimes it takes just a little bit more effort or another idea/chemical catalyst or a new perspective – which means those with prior education can draw on different sources. Water freezes at 32 degrees.
   
   
2. Some people can maintain high levels of concentration even with a room full of noisy people. I'm not one of them – occasionally I need some privacy to think through a critical issue. Some people describe this as "you gotta learn to walk away from it". Stated another way, it's a search for the rarefied air.
   
   This past week I spent hours in half-lit, quiet room with a whiteboard, until I fully understood a problem. It was only then that I could go talk with other developers about a solution. The message here isn't to preach how you should go about your business of solving problems – but rather for everyone to know their strengths and what works, and use them to your advantage as much as possible.
   
   
3. Some phrases are like fingernails on a chalkboard for me.   "Use it as a teaching moment" is one. (Why is it like fingernails on a chalkboard? Because if you're in a mentoring role, you should usually be in teaching moment mode anyway, however subtly).
   
   Here's another – "I can't really explain it in words, but I understand it".   This might sound a bit cold, but if a person truly can't explain something in words, maybe they don't understand.  Sure, a person can have a fuzzy sense of how something works – I can bluff my way through describing how a digital camera works – but the truth is that I don't really understand it all that well.  

   
   There is a field of study known as epistemology (the study of knowledge). One of the fundamental bases of understanding – whether it's a camera or a design pattern - is the ability to establish context, to identify the chain of related events, the attributes of any components along the way, etc.     Yes, understanding is sometimes very hard work, but diving into a topic and breaking it apart is worth the effort. Even those who eschew certification will acknowledge that the process of studying for certification tests will help to fill gaps in knowledge.    A database manager is more likely to hire a database developer who can speak extemporaneously (and effortlessly) about transaction isolation levels and triggers, as opposed to someone who "sort of" knows about it but struggles to describe their usage.
   
   There's another corollary here. Ted Neward recommends that developers take up public speaking, blogging, etc. I agree 100%. The process of public speaking and blogging will practically force you to start thinking about topics and breaking down definitions that you might have otherwise taken for granted. A few years ago I thought I understood the T-SQL MERGE statement pretty well. But only after writing about it, speaking about, fielding questions from others who had perspectives that never occurred to me – that my level of understanding increased exponentially.
   
   I know a story of a hiring manager who once interviewed an author/developer for a contract position. The hiring manager was contemptuous of publications in general, and barked at the applicant, "So, if you're going to work here, would you rather be writing books or writing code?" Yes, I'll grant that in any industry there will be a few pure academics. But what the hiring manager missed was the opportunities for strengthening and sharpening skill sets.
   
   

4. While cleaning out an old box of books, I came across a treasure from the 1980's: Programmers at Work, which contains interviews with a very young Bill Gates, Ray Ozzie, and other well-known names. Every interview and every insight is worth the price of the book. In my view, the most interesting interview was with Butler Lampson, who gave some powerful advice. "To hell with computer literacy. It's absolutely ridiculous. Study mathematics. Learn to think. Read. Write. These things are of more enduring value. Learn how to prove theorems: A lot of evidence has accumulated over the centuries that suggests this skill is transferable to many other things."
   
   Butler speaks the truth. I'll add to that point – learn how to play devil's advocate against yourself. The more you can reality-check your own processes and work, the better off you'll be.
   
   
5. The great computer scientist/author Allen Holub made the connection between software development and the liberal arts – specifically, the subject of history. Here was his point: what is history? Reading and writing. What is software development? Among other things, reading and writing. I used to give my students T-SQL essay questions as practice tests. One student joked that I acted more like a law professor. Well, just like Coach Donny Haskins said in the movie "Glory Road", my way is hard. I firmly believe in a strong intellectual foundation for any profession. Just like applications can benefit from frameworks, individuals and their thought processes can benefit from human frameworks as well. That's the fundamental basis of scholarship.
   
   There is a story that back in the 1970's, IBM expanded their recruiting efforts in the major universities by focusing on the best and brightest of liberal arts graduates. Even then they recognized that the best readers and writers might someday become strong programmer/systems analysts. (Feel free to use that story to any HR-type who insists that a candidate must have a computer science degree!)
   
   And speaking of history: if for no other reason, it's important to remember the history of product releases – if I'm doing work at a client site that's still using SQL Server 2008 or even (gasp) SQL Server 2005, I have to remember what features were implemented in the versions over time.

    

6. Ever have a favorite doctor whom you liked because he/she explained things in plain English, gave you the straight truth, and earned your trust to operate on you? Those are mad skills, and are the result of experience and HARD WORK that take years and even decades to cultivate. There are no guarantees of job success – focus on the facts, take a few calculated risks when you're sure you can see your way to the finish line, let the chips fall where they may, and never lose sight of being just like that "doctor" who earned your trust. Even though some days I fall short, I try to treat my client and their data as a doctor would treat patients. Even though a doctor makes more money!

    

7. There are many clichés I detest – but here's one I don't hate: "There is no such thing as a bad question". As a former instructor, one thing that drew my ire was hearing someone criticize another person for asking a supposedly, "stupid question". A question indicates a person acknowledges they have some gap in knowledge they're looking to fill. Yes, some questions are better worded than others, and some questions require additional "framing" before they can be answered. But the journey from forming a question to an answer is likely to generate an active mental process in others. There are all GOOD things. Many good and fruitful discussions originate with a "stupid question".

    

8. I work across the board in SSIS, SSAS, SSRS, MDX, PPS, SharePoint, Power BI, DAX – all the tools in the Microsoft BI stack. I still write some .NET code from time to time. But guess what I still spend so much time doing – writing T-SQL code to profile data as part of the "discovery" process. All application developers should have good T-SQL chops.

    

9. Ted Neward writes (correctly) about the need to adapt to technology changes. I'll add to that – the need to adapt to client/employer changes. Companies change business rules. Companies acquire other companies (or become the target of an acquisition). Companies make mistakes in communicating business requirements and specifications. Yes, we can sometimes play a role in helping to manage those changes – and sometimes we're "the fly, not the windshield".
   
   These sometimes cause great pain for everyone, especially the I.T. people. This is why the term "fact of life" exists – we have to deal with it. Just like no developer writes bug-free code every time, no I.T. person deals well with change every single time. One of the biggest struggles I've had in my 28 years in this industry is showing patience and restraint when changes are flying from many different directions. Here is where my prior suggestion about searching for the "rarified air" can help. If you can manage to assimilate changes into your thought process, and without feeling overwhelmed, odds are you'll be a significant asset.
   
   In the last 15 months I've had to deal with a huge amount of professional change. It's been very difficult at times, but I've resolved that "change will be the norm" and I've tried to tweak my own habits as best I can to cope with frequent (and uncertain) change. It's hard, very hard. But as coach Jimmy Duggan said in the movie A League of Their Own: "Of course it's hard. If it wasn't hard, everyone would do it. The hard, is what makes it great". A powerful message.

    

10. There's been talk in the industry over the last few years about conduct at professional conferences (and conduct in the industry as a whole). Many respected writers have written very good editorials on the topic. Here's my input, for what it's worth. It's a message to those individuals who have chosen to behave badly: "Dude, it shouldn't be that hard to behave like an adult".
    
    A few years ago, CoDe Magazine Chief Editor Rod Paddock made some great points in an editorial about Codes of Conduct at conferences. It's definitely unfortunate to have to remind people of what they should expect out of themselves.
    
    But the problems go deeper. A few years ago I sat on a five-person panel (3 women, 2 men) at a community event on Women in Technology. The other male stated that men succeed in this industry because the "Y" chromosome gives men an advantage in areas of performance. The individual who made these remarks is a highly respected technology expert, and not some bozo making dongle remarks at a conference or sponsoring a programming contest where first prize is a date with a bikini model.
    
    
11. Our world is becoming increasingly polarized (just watch the news for five minutes), sadly with emotion often winning over reason. Even in our industry, recently I heard someone in a position of responsibility bash software tool XYZ based on a ridiculous premise – and then give false praise to a competing tool.
    
    So many opinions, so many arguments, but here's the key: before taking a stand, do your homework and get the facts. Sometimes both sides are partly right…or wrong. There's only one way to determine: get the facts. As Robert Heinlein wrote, "Facts are your single clue – get the facts!" Of course, once you get the facts, the next step is to express them in a meaningful and even compelling way. There's nothing wrong with using some emotion in an intellectual debate – but it IS wrong to replace an intellectual debate with emotion and false agenda.
    
    A while back I faced resistance to SQL Server Analysis Services from someone who claimed the tool couldn't do feature "XYZ". The specifics of "XYZ" don't matter here. I spent about two hours that evening working up a demo to cogently demonstrate the original claim was false. In that example, it worked. I can't swear it will always work, but to me that's the only way.

     

12. I'm old enough to remember life at a teen in the 1970's. Back then, when a person lost his/her job, (often) it was because the person just wasn't cutting the mustard. Fast-forward to today: a sad fact of life is that even talented people are now losing their jobs because of the changing economic conditions.
    
    There's never a full-proof method for immunity, but now more than ever it's critical to provide a high level of what I call the "Three Vs" (value, versatility, and velocity) for your employer/clients. I might not always like working weekends or very late at night to do the proverbial "work of two people" – but then I remember there are folks out there who would give anything to be working at 1 AM at night to feed their families and pay their bills.

     

13. Always be yourself…your BEST self. Some people need inspiration from time to time. Here's mine: the great sports movie, Glory Road. If you've never watched it, and even if you're not a sports fan I can almost guarantee you'll be moved like never before.
    
    And I'll close with this. If you need some major motivation, I'll refer to a story from 2006. Jason McElwain, a high school student with autism, came off the bench to score twenty points in a high school basketball game in Rochester New York. Here's a great YouTube video.
    
    His mother said it all: "This is the first moment Jason has ever succeeded and is proud of himself. I look at autism as the Berlin Wall. He cracked it."

     

 

 

9/17/2014 – My slides for SQL PASS BI VC on Intro to Power BI

Thanks to everyone for attending my presentation this afternoon.

Click here to pull down my slides from today

If you have questions, feel free to email me at [email protected]

6/17/2014 – My presentation at Central PA .NET User Group on T-SQL Anti-Patterns

Thanks to those who attended my talk this evening at the Central PA .NET User Group.

Click here to download my slide deck and code samples.

KG

2/19/2014 – New Presentation later today on Power BI for Office 365

Later today/this evening I'll be posting a new presentation – Introduction to Power BI for Office 365

This is now generally available from Microsoft and has some interesting and valuable functionality, especially for those who are interested in publishing BI content to SharePoint but don't want to deal with all the complexities of SharePoint.

12/7/2013: Today’s SQL Saturday in Washington DC and my Session

To anyone who wanted to attend my session at today's SQL Saturday event in DC I apologize that the session had to be cancelled.

I hate to make excuses, but a combination of getting back late from Detroit (client trip), a car that's dead (blown head gasket), and some sudden health issues with my wife have made it impossible for me to attend.

Back in August, I did the same session (ColumnStore Index) for PASS as a webinar.

You can go to this link to access the video (it'll be streamed, as all PASS videos are streamed)

https://attendee.gotowebinar.com/recording/2247689092133300736

The link does require that you fill out your name and email address, but that's it. And then you can watch the video.

Feel free to contact me if you have questions, at [email protected]

11/10/2013: SQL Server Topic of the Week: Getting Started with Windows Azure SQL Databases

Getting started with Windows Azure and creating SQL Databases "in the cloud" can be a bit daunting, especially if you've never tried out any of Microsoft's cloud offerings.

Fortunately, I've created a webcast to help people get started. This is an absolute beginner's guide to creating SQL Databases under Windows Azure. It assumes zero prior knowledge of Azure.

You can go to the BDBI Webcasts of this website and check out my webcast (dated 11/10/2013).

Or you can just download the webcast videos right here: here is part 1 and here is part 2. You can also download the slide deck here.

KG