Friday, November 05, 2021

SQL Server linked server to .xlsx Excel files

A question came up recently concerning whether it was still possible to connect to .xlsx Excel files as  SQL Server linked servers.

Short answer: Yes, it's possible as recently as SQL 2019 using ACE.OLEDB.12.0 provider. 

Medium answer: Yes, but linked servers from SQL Server to Access and Excel are only supported when using the deprecated 32-bit Microsoft.JET.OLEDB.4.0 OLE DB provider

Long answer: Yes, but this shouldn't be part of any automated ETL process, please please engineer around using Excel file interchange in any automated data ingestion processes. That said... 

Thursday, September 16, 2021

Ethics in Modern Data at Music City Tech 2021

 

On Sept 16, Christine and I presented our joint presentation on Ethics in Modern Data at the Music City Tech 2021 Virtual Conference. Thanks for joining our presentation!

This session explores a variety of considerations that modern data scientists and data practitioners must account for when gathering and presenting data, including topics on bias, construct analysis, and machine learning. We'll discuss examples from history and current headlines.

This is an important topic that lives at the crossroads of our careers, my wife's career in organizational psychology and human resources, my career in data, our work in civic non-profits, and our joint passion for history and civil rights. It's important to understand that when dealing with bias: outcomes matter, intentions don't. 

Our slidedeck and all citations and references will be made available for download here.





Monday, August 02, 2021

Ethics in Modern Data this Saturday with the South Florida Data Geeks

This Saturday Aug 7, Christine and I will present our joint presentation on Ethics in Modern Data. 

This session will explore a variety of considerations that modern data scientists and data practitioners must account for when gathering and presenting data, including topics on bias, construct analysis, and machine learning. We'll discuss examples from history and headlines.

This is an important topic that lives at the crossroads of our careers, my wife's career in organizational psychology and human resources, my career in data, our work in civic non-profits, and our joint passion for history and civil rights. It's important to understand that when dealing with bias: outcomes matter, intentions don't. While many of our examples come from the historical context of the United States, not all, and we have added additional context for international audiences. 

Our slidedeck and all citations and references will be made available for download here.

Register for the virtual event here, and we'll see you Saturday afternoon at 2pmET.




Tuesday, July 27, 2021

Ethics in Modern Data at the Baton Rouge Analytics and Intelligence Network (BRAIN)


On July 27, Christine and I will present our joint presentation on Ethics in Modern Data. Looking forward to speaking at yet another technical user community looking to jumpstart after a COVID-induced hiatus. 

This session will explore a variety of considerations that modern data scientists and data practitioners must account for when gathering and presenting data, including topics on bias, construct analysis, and machine learning. We'll discuss examples from history and headlines.

This is an important topic that lives at the crossroads of our careers, my wife's career in organizational psychology and human resources, my career in data, our work in civic non-profits, and our joint passion for history and civil rights. It's important to understand that when dealing with bias: outcomes matter, intentions don't. While many of our examples come from the historical context of the United States, not all, and we have added additional context for international audiences. 

Our slidedeck and all citations and references will be made available for download here.

Register for the virtual event at 5pmCT here.

Thursday, June 24, 2021

Speaking at Data ANZ 2021

Looking forward to speaking at Data ANZ 2021 this weekend. Data ANZ "is the data community event for the whole of Australia and New Zealand (and the world)"! 

I'll be presenting both my "How to Think Like a Certification Exam" and co-presenting "Ethics in Modern Data" along with Christine. Their schedule is published here

Both slidedecks are available for download here on Github.

Sunday, May 23, 2021

SQL Server Admin Best Practices with DMVs at GroupBy Americas 2021

On Tuesday I'm looking forward to presenting again at GroupBy Americas on a topic that was voted on by the attending public. This presentation "SQL Server Admin Best Practices with DMVs" is a talk that I've been giving for more than a decade and been tweaking and tuning ever since. 

This talk covers basic roll-your-own maintenance strategies using data inside SQL Server including wait stats, query stats and the Query Store, fragmentation and index maint, index DMVs for new/existing indexes, and a ton more helpful DMVs introduced in SQL 2017 and 2019. 

You can register for this talk here: groupby.org.

You can download my presentation here, and the toolbox of sample scripts referenced throughout can be found in here.


Friday, May 14, 2021

This Saturday: #DataWeekender, DataSaturday Southwest

Geeked to be speaking 2x on Saturday May 14, my second time speaking at #DataWeekender and at the very first Data Saturday Southwest 2021.

My Certification Exams Inside Out is one of my favorite presentations to give. Its fun, its positive and career-oriented, it's useful for folks at any point in their career. Between my guided walkthrough based on actual experience and fun sample test questions, I hope help you understand both how a question gets written, and how best to go about answering.

Slides for my presentations are available for download on Github: #DataWeekender and Data Saturday Southwest 2021.






Monday, May 03, 2021

"Ethics in Modern Data" at Minnesota Developers Conference 2021

Looking forward to speaking to a brand new conference for us, the Minnesota Developers Conference 2021 on May 4 at 2pmCT. Christine and I will be presenting a talk that we're both passionate about. We bring both our career focuses to the topic of modern data analytics, construct analysis, historical and current bias, and modern machine learning algorithms. We'll talk about ethics of bias in data both historical and issues ripped from the headlines. 

See you at 2pmCT virtually from Minnesota!

Our slidedeck for the presentation is available here.

Thursday, April 08, 2021

"Think Like a Certification Exam" at Certification Saturday 2021

I'll be presenting at Certification Saturday 2021 this weekend! I'll be contributing my talk on How to "Think Like a Certification Exam" at 3pmGMT/8amPT.

Microsoft is offering any one certification exam for USD15 to anyone who has become unemployed or furloughed due to the pandemic this year. Schedule an exam before December 30, 2021. If you're interested in learning more about how to prep cert exams, how they're written, and how the test questions are constructed, I'm presenting on this topic.

Slidedeck available for download here. Video recording available on YouTube here.

Wednesday, April 07, 2021

"Ethics in Modern Data" at the Inland Northwest Data Professionals Association

Looking forward to speaking to one of our new home turf's data organizations, the Inland Northwest Data Professionals Association. My spouse Christine and I will be presenting a talk we're both passionate about and bring our career focuses to the topic of data, construct analysis, historical and current bias, and modern machine learning algorithms. We'll talk about ethics of bias in data both historic and ripped from the headlines.

Join us at NOON PT on April 8 here: https://www.meetup.com/inland-northwest-data-professionals-association/events/277102053/

Slidedeck available for download here.


Become a Contributor to Microsoft Docs

Starting this month, I'm leading a talk series with my teammates from the SQL Docs team at Microsoft. In this presentation we lay out just how easy it is, an how exactly, to contribute to Microsoft's Docs as Code in Github. We're looking forward to increasing visibility and ease of access to community contributions to the open source Docs that millions of professionals use every day.

If you'd like the SQL Docs team to deliver this talk to your local user group or community conference, reach out to us on Twitter or message me on LinkedIn or comment on this blog post, we'd be happy to get on your schedule. I've list upcoming engagements for this talk my Speaking Engagements page on this blog. 

Abstract: Join the SQL-Docs content team for a discussion on modern Microsoft documentation (regardless of technology). Learn about the content publishing engines behind Docs, and the best ways to add your content and fixes to Docs that are read by the entire customer community. There are hundreds of thousands of GitHub issues and PR’s submitted to Docs every month, how can you be an effective part of that process and get your face on the top of an article?

Presenter: MSFT SQL-Docs team (panelist headcount TBD)

Bio: Members of the Microsoft SQL-Docs team will present this thorough walkthrough on how you can contribute and be recognized as a SQL Docs contributor. 

Friday, March 26, 2021

Moving Into Consulting 101 - Part 5 - The Tensions of a Consultancy

This is part five in a five part series this week, Moving into Consulting 101

In the business consulting system, the clients are served by two separate yet equally important groups. The sales who pursue opportunities, and the consultants who prosecute the projects. Dun dun... 

Today's topics help you navigate the classic internal consultancy tension between people who sell consulting services and people who deliver consulting services.  

from Pixabay



The Tensions of a Consultancy


Sales vs Ops


The people who sell consulting services will have various buzzy names, like program/business development/client/excellence/vice president/partner/manager/executive. The people who operate/deliver services are the practitioners of whatever is being sold. That's probably you. 

Obviously, what is being sold and what can be delivered — and when — need to line up. This is an eternal conflict. Sales and delivery are co-equally important. We cannot stay in business if we don't sell, and we cannot stay in business if we don't deliver. 

As a consultant, do what you can to make sure that sales and delivery are communicating effectively. This tension always exists and communication is really the only treatment. When both sides are on the same page, everyone wins.

Aligning the sales pipeline (and projects starts) with the resource availability pipeline (and project endings) is a difficult task that requires coordination and collaboration by managers. This becomes even more difficult when sales unwisely promises specific, named consultant(s) for a project. Good consulting companies maintain a staff of consultant options for a project type, not a set of hyper-specialized unicorns, and do not sell specific people, but specific services. 

Running too lean as a consultancy will lead to obvious staffing constraints, as Ops pushes project start dates back or loses projects because they cannot be staffed. Staffing too many consultants will cause the consultancy to carry too much burden as those consultants sit on the bench, unbillable. 

In the end, sometimes Sales and Ops try to resolve staffing conflicts by asking consultants to work more than 40 hours or double-bill two different clients at the same time. This is untenable, unethical, and potentially self-destructive. 


"Land and expand"


Sales: "Look, if we do a really good job on this project, they'll give us a bigger one."

PM: "Look, we've got a lot of work coming down the road if this goes well."

Client: "Look, we want to see some investment on your part to get this project done, then we'll work together more in the future."

Without formal Request For Proposal (RFP) processes, many project contracts are signed by individual decision-makers. A consultancy's sales staff will work directly with them, and many of the projects I worked on for companies large and small had some "promise of future work" implicit to the deal. It's not a scam, it's not a ruse, but more often than not those promises prove ephemeral. Consultancies will face pressure by customers to risk razor-thin margins and below-cost resources with the promise of bigger work in the future.

It is nevertheless part of a consultancy's strategy to win projects that lead to bigger ones. The slang lingo is "land and expand." If you're not already familiar with this term, you will be inside of 15 minutes in your first sales-delivery meeting. Listen up when a salesperson tries to sell a prospective deal as more important than its face-value dollar amount would indicate.

In consulting, you may also hear jargon similar to "everyone's job is to sell," even those who are consultants in the field. That's because a key to "land and expand" is identification of opportunities from the inside. A project on x exposes a need for y, which creates demand for z. It's true, on your way to delivering a project, keep an eye out for other opportunities as they arise: 
  • Perhaps a health check has exposed a significant gap in the client's disaster recovery strategy. 
  • Perhaps a performance tuning exercise has revealed flaws in custom applications. 
  • Perhaps an incident has exposed a policy vs practice gap that should be addressed.
Keep looking for these type of opportunities on your consultant radar. Without being disingenuous, your honest assessments, tactfully delivered, of client environments should be appreciated. Your business development colleagues love to talk about them, and your clients might be willing to use a now-trusted consulting resource to solve them. You may even be compensated for identifying follow-up sales leads.


Become familiar with the sales process 


We talked earlier about becoming familiar with the project management process. As you progress in consulting, you'll become more involved in the process of selling rather than delivering, because your expertise is valued and an asset in client conversations.

You should understand the basics of the project life cycle, from scoping, sales, execution, and follow-ups. If this isn't transparent to you, ask. Become familiar with your company's project sales process, sales assets and tools. You should have or ask for access to your project's original Statement of Work, which lays out the client project's deliverables and schedule. You should know who to talk to if the client requests work outside of the scope, and you should know to stop working when they do. You should introduce yourself to your project's project managers and sales people. 

If you have a project manager, keep them up to date and use them as defense against out-of-scope requests. Don't say "no, that's out of scope" to the client. Instead: "Well, that may be out of scope, but no worries, we can get the change order process going while we talk about it." In fact, a good consultant will never turn down out-of-scope requests, but rather turn them into more signed Change Orders or Statements of Work. 

It's just like improv comedy, say "Yes, and - let's get the project manager going on that."

If you're new to consulting, go out of your way to ask to see this type of documentation. Understanding how your consulting services are scoped, structured, and billed is important to understanding your work. It will help your advancement and efficiency. 


from Pixabay


Vocabulary


Common project sales concepts


Statement of Work (SOW) = The project management document that is signed by both parties to execute the work. It's a binding contract and should include details and limitations about deliverables, explicitly including what is out of scope. It is preceded by a Level of Effort (LOE) which the client agrees to. It operates under the framework of the Master Services Agreement (MSA). You should have access to the SOW and know the deliverables we've promised.

Level of Effort (LOE) = An estimate only, to make sure that your anticipated costs are in line with the client's budget. It's usually prepared by senior staff who have experience estimating similar projects. It is not a binding contract, just used to set the stage for an SOW. It follows a discovery project or at least some form of pre-sales discovery that allows for proper estimation.

Discovery =  Here we want to use our standardized tools and checklists to perform a thorough evaluation. This is beneficial to both sides. The consultancy gets to explore the turf and find opportunities. The client gets a thorough evaluation and some explanation for what could be. Often, clients come to consultants thinking they know exactly what they need. A discovery session will determine what they actually need. You may sign a small SOW with the client just to do discovery as a fixed-fee project. Or, discovery may just be the first part of a project. I prefer to have the Discovery project be a small, separate precursor project. The deliverables of a Discovery project are usually an LOE for a larger, detailed, well-scoped project.

Request For Proposal (RFP) = Large organizations and governmental agencies will likely follow an RFP (or RFQ, request for quote) process when putting projects out to bid. Usually there is a minimum threshold of cost for a project to require a RFP process, but cost is only part of a final formula that awards a winner. RFP's are usually quite large and detailed, usually have a public Q&A period, and may require consultancies to submit an LOE and SOW based on only that public information. Competition for RFP's from consulting companies far and wide is usually difficult, and it may be time-consuming to submit a qualified bid, often including the resumes of resources.

Master Services Agreement (MSA) = A precursor legal contract between the client and the consultancy, allowing for the signing of further contracts for individual projects, billing, terms of invoices, contacts. It also likely contains prohibitions of the client hiring away consultants, or in the event of a hire, terms of damages to pay the consultancy. Such "non-compete" clauses of MSA contracts have dubious success for enforcement, but it could be a non-starter for some employers who don't want the entanglement of dealing with a non-compete. Non-compete clauses may also be enforced in your hiring agreement from the consultancy.


This concludes the final of a five-part series on Moving into Consulting 101

Previous topics in this series:

Got comments, questions, or guide for your fellow consultants? Comments are welcome below.

Thursday, March 25, 2021

Moving Into Consulting 101 - Part 4 - The Process of a Consultant

This is part four in a five part series this week, Moving into Consulting 101

Today's topics give you a numerical advantage in consulting. All have a common theme: don't wing it. Try to formalize processes that deal with how you account for and plan your time. 

Maturity in terms of project estimation, project execution, and time accounting are required for a successful consulting career.

from Pixabay

The Process of a Consultant


Make estimation a science


Contracts should not be signed based on estimation of "ballpark figures" or WAGs (wild-ass guesses). Agile methodology involves regular sprint planning meetings, where tasks are devolved into small components, usually no larger than 8 hours of work. Sometimes the hours for any given task must be a power of 2 (1,2, 4, 8), sometimes they must be primes (1,2,3,5,7) but the idea is that we are forcing projects to be broken down into many small tasks, which are easier to estimate. But you don't have to practice Agile or Scrum or similar methodologies to practice this pre-project deliverable. 

Regardless of your field or expertise, strive to make your estimation as detailed as possible, breaking down tasks into individual activities that fit inside of a day. In other words, nothing should take "about a week". 

Don't forget to include other tasks aside from your effort in the project's estimate. Does this project need a Project Manager? Quality Assurance or testing? Meetings with Subject Matter Experts? Are you accounting for internal standup meetings and client-demo meetings? Once the work is done, are you accounting for migration time, post-migration support, knowledge transfer meetings and documentation? 

Finally, as painful as it sounds, you should review your pre-project estimation against your post-project timeline. In any project post-mortem, we should review how accurate our estimates were and why they were off. They will be off. It's okay. Highly experiences senior resources will consistently miss on duration estimates because project estimation is hard. It's really very hard, we can get better at it only through practice, through planning small atomic blocks of time, and through honest post-mortems. So save your pre-sales estimation spreadsheets for careful examination post-project, and learn from them. 


Practice rapid prototyping


This isn't a blog post extolling the virtues or buzzwords of Agile methodology, there are many others to do that. This is something you can use whether or not you practice Agile or scrum, or organize your team's planning meetings and daily standups around that culture.

There is one important key deliverable of Agile that clients and end-users become keenly aware of: rapid prototyping. You should develop a habit of going back to the client regularly to show progress. This allows the project to make micro-corrections instead of major changes. These course adjustments will only start when the client sees whatever it is you're working on. You want that to happen sooner rather than later.

In consulting terms, rapid prototyping also allows for ample opportunities for project scope conversations, which a good consultant turns for new features and hopefully change orders for additional hours. This isn't underhanded or conniving. Clients don't know beforehand the full extent of what would make a their project successful. Delivering a more full-featured product will be judged more positively in the end. 

I'm not just talking about custom app development. You can plan for rapid prototyping in business intelligence, process documentation, training/development content, runbooks, personnel evaluations, even the scoping and estimates for the project itself. 

In short, don't take the initial work specs, work on it for a few weeks, then show a demo. You should be demoing your work to the client every other week, if not more often. This isn't as cynical as it sounds, but no customer has ever accepted a deliverable matching only the initial description.

There are always project changes from the original plan, some predictable and some thanks to the whimsy of the client. "Scope creep" is definitely a thing that happens, but this is a manageable problem when you have good project controls in place. In fact, scope creep can be advantageous for a consultancy if it results in change orders to add scope and duration to the project.

Whether you accomplish rapid prototyping via Agile or scrum or neither, just make sure you are keeping your project on track with frequent check-ins with the customer.


Learn the skills of a project manager


Don't assume all your projects will have a PM doing that "project management work" for you. You need to make sure PM-type tasks happen regardless. 

Across the many projects over 12 years, I worked project scopes large and small, some with a PM and some without. 
  • On many projects, I was doing the status communication myself anyway, so naturally the client came to me for status communication and follow-up. Clients might prefer to hear technical status communication straight from the resource, without the PM's filter. Regardless, keep your PM in the loop at all times, if you have one.
  • When my project had a PM resource, more client communication ran through them, especially for documentation. (In some of those cases, the PM was absent or uninvolved, and the task of managing the project fell to me anyway.)
  • Many smaller or shorter duration projects had no PM at all. Perhaps the client knew me so well that I was entrusted with keeping the project on track. Perhaps clients would refuse to be charged for my consultancy's PM, and would instead provide their own PM. (In many of those cases, the client-provided PM was absent or uninvolved, and the task of managing the project fell to me anyway.)
  • Sometimes, a PM would be assigned but have a only fraction of the total hours for their services, maybe 2-4 hours a week. This isn't enough time to do much more than gather progress notes, sit in on a meeting, and provide a status update. (Many of the tasks of managing the project fell to me anyway.)
Having a well-invested Project Manager on the project was certainly helpful and relieved some of my workload, but as you can see, it didn't always work out that way.

That's when I had to be my own project manager. That's when it became paramount that I had a good understanding of what had been agreed to and on what timeline, so that I could:
  • Track progress versus the deliverables we're contractually 
  • Account for hours/dollars spent versus the cap
  • Hold cadence meetings with the client
  • Wrap up the project in style, reviewing all deliverables
  • Hold a wrap-up meeting to get signoff
  • Ensure SLAs are met
  • Track bugs, change requests, QA feedback
  • Go to Sales with opportunities for follow-up projects
  • Create knowledge transfer documentation detailing everything delivered
Later we'll discuss the Statement of Work document, which is important for you to have access to and review. A SOW is a contract that contains everything that the consultancy has promised, when, and for how much. It may fall to you to make sure your consultancy has delivered everything in that document. Pay special attention to end-of-project documentation requirements.


from Pixabay


Vocabulary


Common project billing concepts


T-Shirt Size = An estimation tactic that forces all tasks to fit a size of hours. For example: XS (.5 hour), S (1), M (2), L (4), XL (8). The Key: anything larger than XL needs to be broken down more granularly. This forces you to analyze deliverables and anticipate complications sooner, and a project to contain a long laundry list of tasks. This is a good thing. Your consultancy may use different values or strategies for this same general idea. 

Cadence = An overused buzzword for sure. The idea here is that you have regular status meetings (every week perhaps), and that the meetings occur in a standardized way. In a cadence meeting, you'll probably see questions answered around progress, metrics towards deliverables, budget vs actual spent. 

Change Order = This may be called different things in different places, but it's an amendment to the original contract, or Statement of Work. If the client asked for ABC and now wants D and E, a change order contains the details, deliverables, schedule, and additional budget to deliver D and E. Change Orders often represent new money to be spent, so clients may have hesitancy or long processes for approval. That doesn't mean work should stop, but we cannot deliver on D and E until we have a Change Order.

Delivery or Operations = The boots on the ground. The people clicking the buttons, writing the code, creating the product, providing the solution. Deliverables are delivered by these consultants who have subject matter expertise: recommendations documents, code, processes, improvements, training, service, etc. 

Sales = The opposite of delivery. We'll talk about this in the next post in this series.


This is part four of a five-part series on Moving into Consulting 101

Got comments, questions, or guide for your fellow consultants? Comments are welcome below.

Wednesday, March 24, 2021

Moving Into Consulting 101 - Part 3 - The Hours of a Consultant

This is part three in a five part series this week, Moving into Consulting 101 

Today's topics talk about why you were hired. How is your work likely to be quantified, weighed, and forecasted? You'll soon become intimately familiar with the daily process of time entry. The hourly rate your company bills to your clients, based on your time entries, funds the whole operation. Or perhaps, your services are provided in exchange for monthly flat-rate contract, or fixed-fee projects. You should be aware of your project's setup for the client.

In tomorrow's topic, we'll dive further into projects, what else you should know and what roles you should be prepared to play.


from Pixabay


The Hours of a Consultant


Utilization is not a badge of honor


Utilization = The king of all consultancy buzzwords. The number of hours you billed for divided by number of hours out of the week (generally 40). 

Most consultancies will expect you to bill 70-95% of your 40 hours per week, depending upon the industry and your level of seniority. You should probably know what this goal is so that you can balance out non-billable tasks you still need to accomplish. Most consultants won't bill 40 hours a week every week, especially as you advance. Non-billable time will include supporting sales efforts, team management, time entry, training, internal meetings, project estimation, and creating project proposals.

You will be judged, evaluated, perhaps even compensated based on your utilization goals. While utilization is the factor you'll be reminded of the most, it should not define your success as a consultant or your career progress. If you sense that a consultancy only awards/advances people who work the most hours, perhaps this isn't the place for you. While profitable, squeezing maximum billable hours with no regard to work-life balance is not ethical or empathetic. Maximizing utilization has a negative impact on diversity. Jokes about slacking off if you only billed 40 hours should be a red flag.

A consultancy will be happy to see you bill 55 hours a week, but everyone should know this is not sustainable or empathetic. It's sometimes a requirement, but should not be a pattern. A responsible consultant manager, with empathy for work-life balance, should trade excess billable hours in a week for time off the next week. This is not guaranteed or common. Consultancies employing FTEs like to play fast and loose with the legal definition of "full time employee" and overtime compensation regulations. 

Billing 3000 hours in a calendar year is not a badge of honor, it means your consultancy mangled its staffing plan, you helped cover that mistake, and you almost certainly were not compensated fairly in return. There will be pressure and gamification of your utilization, so be prepared for the internal push to bill all the hours you (honestly) can. 

One uncomfortable question you'll want to ask right away: "That mandatory half-hour departmental meeting... should I still be expected to bill 40?" I've seen a range of answers to this question: "just work 40" to "bill it to your next client today" to "work late and bill your forty" to "hey man, look, just be cool about it". If the answer isn't presented obviously, ask the question, judge your manager's ethics/empathy and prepare accordingly. If I had to guess, the answer from most consultancies is going to be that you should bill 40 and record 40.5 hours that week's time sheet. Just be prepared for that. 

A mature consultancy will be familiar with burn-out and will value their employees' time and work-life balance. While long work weeks may come in bursts, and that's expected, look for signs that your consultancy is paying attention and rewarding overwork.


Track time like a consultant


Every consultancy is going to ask you to track your time, likely in a way and level of detail that is more inquisitive than your in-house job. 

Time entry comments may be exposed to clients on invoices. Different time entry systems allow for public vs private comments, for example. Be sure to understand that:

1) Time entry is required for Time and Materials (T&M) type work, so it's just a part of the business. Having it entered timely and accurately is also important for billing purposes. You don't want to be "that employee" who consistently draws negative attention upstream for entering your time late.

2) Know what time sheet detail/comments the clients will see. You don't want to be that consultant whose rude complaints about a client showed up on the client's desk in an invoice.

3) Know what project/task/bucket you should be billing time to for every project. If you need a bucket, ask a project manager or your boss promptly - don't wait. Before I was in a position to create my own buckets, I had probably sent "I need a bucket for..." emails a thousand times.

Don't be surprised if your manager asks you to account for every 15 minutes of your day, even though you're a full-time employee (FTE). You get used to it. It's really not that bad. Oftentimes, I was able to fill in most of my time sheet by virtue of reviewing my calendar, sent items, chat history, and ticketing system history. 


How consultancies should bucketize non-billable hours


Time entry systems usually have a bucket for "admin time" or "non-billable" time. In many consulting companies, this single bucket exists but is discouraged and scrutinized, as if it is a trap. I've worked in many different time entry systems and regimes, and I'll strongly suggest to anyone that listens that this type of bucket is a bad idea. 

Instead, there should be a wide, customized array of time buckets for things like "training", "sales support", "mandatory internal", "internal systems", "internal reporting", or maybe even one for "time entry". These more specific buckets are self-evident, and over a year's look more understandable.

Consider also "sales support" buckets inside existing projects, for time spent scoping the next project, as you land-and-expand in the client. This way, all that non-billable time doesn't add up so inexplicably on a monthly time report. This makes everyone happy - those who enter the time don't feel so needled, and those who review the time don't get frustrated by big blocks of non-billable hours.

Also, don't be surprised to see your weekly time sheet change dramatically from week to week, project to project, client to client. Your time sheet may be 8x5 for a single client one week and then 12 clients in a week. Especially if you enter a more senior consulting role where you are engaging with technical sales support and scoping, your timesheet will probably look more like a chess board. 

As you engage on more and more sales support or pre-sales work, your utilization target should drop accordingly. Early in my consulting career my utilization goal was 85-90%. By the end of my last consulting position as a principal consultant and team lead, I averaged around 50-60% utilization per week, including time spent on T&M and flat rate projects.


from Pixabay


Vocabulary


Common consulting sales concepts


Time and Materials (T&M) = A project billed hourly as needed, likely with a cap. If we finish early, we stop billing early. If we run over, we'll need a change order.

Fixed fee = A single price for a single set of deliverables. If we finish early, we get more profit. If we finish late, we can't charge extra.

Change order = Okay, let's get the customer to agree to be charged extra.

Flat rate fee = Often a single price, agreed monthly. Many Managed Service Providers (MSPs) use flat rate pricing in multi-year contracts to lock-in agreements for ongoing support of clients. It's mailbox money for the consultancy, and it's predictable billing and guaranteed services for the client. Flat rate fee contracts almost certainly include a detailed SLA. 

Service Level Agreement (SLA) = This details the level of service — the response time, for example — a client should expect from a consultancy in a flat rate fee or "Managed" agreement. T&M and Fixed Fee projects rarely include any SLAs, but a flat rate fee project for access to consultant services must include an SLA. The SLA determines, for example, how long it should take the consultants working on a Managed Service contract to respond to a client request. If a production server is down, the SLA may be 15 minutes. If a non-critical process has stopped working, the SLA may be 3 business days. Other types of requests may have SLAs as well, and different levels of severity need different SLAs.

Sales Support = In short, sales people without expertise need non-sales people with expertise to seal the deal. Senior resources with relevant expertise join pre-sales conversations on a non-billable basis to talk about architecture options, possibilities, approaches to solve problems. As you progress inside a consultancy, you'll be part of more Sales Support conversations, and trusted to talk about the latest trends and technology. You'll also be tasked with estimation and drafting proposal language. 


Common internal consulting concepts


Pipeline = As sales opportunities move from status to status inside a company's customer relationship management platform (CRM), a percentage of their estimated value at signing is increased. For example, an opportunity after one conversation with a client might only have 20% of its contract value allocated to a sales person's pipeline. A deal expected to close tomorrow would have 80% of its contract value reflected in the pipeline. Similar systems are in place around the industry help with capacity planning, justify hiring decisions, and prioritize pre-sales assets.

Burden Rate = While consulting fees might be relatively flat between different consultants in your company, their burden rates will vary. Burden is the calculated cost/hour of each consultant accounting or salary and benefits. Burden is subtracted from project revenues to determine profit margin. Burden is part of the cost of goods sold, an accounting concept, which is what it sounds like.

Your Plate = Your current and anticipated workload. If your plate is full, you have enough billable work to keep you busy for the next week or two. If your plate is full, you will struggle to take on more work. I had a recurring reminder for my consulting DBA team to send me their "plate," a weekly status report of what they were working on and what was coming. This aided us with forecasting and project assignment, and making the justification for hires. 

Bench = Consultants are "on the bench" when they don't have any billable work to do. Bench work makes everyone nervous. Larger consultancies can absorb more bench time than smaller consultancies. The typical conversation that happens with management is that consultants on the bench should pursue training and certifications, work on internal projects, or (more or less involuntarily) burn their personal time off hours. You can't convince an experienced consultant that bench time isn't something to worry about, but sometimes it is inevitable. If you experience bench time, make the most of it by using the time on personal improvement, like technical blogging, developing a presentation for a conference, or developing labs for training. 

"Lab it out" = My employees knew this expression well: if you have a question about how something works, try it out yourself. If they came to me with a question about how abc works, we'd test abc out in code, right then and there. I mastered many SQL Server features not by reading about them, but by creating them in my personal SQL Server environment, bending and breaking them. Your technical "lab" might be on personal hardware, your work laptop, or in the cloud. Your best learning will come as you get hands-on with technologies, and clients don't always give you a perfect environment to learn by trial and error. 

This is part three of a five-part series on Moving into Consulting 101

Got comments, questions, or guide for your fellow consultants? Comments are welcome below.

Tuesday, March 23, 2021

Moving Into Consulting 101 - Part 2 - The Tact of a Consultant

This is part two in a five part series this week, Moving into Consulting 101

Today's consulting topic is friendly advice on how to handle yourself in front of clients, as a consultant. Not always are we welcomed with open arms by clients as an expert-level savior. Oftentimes we are met with hesitancy, withheld facts, skepticism, or outright defensiveness.

We need to win over clients' trust if the engagement is going to be pleasant, much less successful. We also need to avoid landmines by tactfully operating as guests in the clients' workplace, or in a pandemic, in their headspace.

from Pixabay


The Tact of a Consultant


Use first person plural


Avoid aligning yourself inside the client's environment. Whether it's Devs vs DBAs, or Sales vs Ops, or Management vs Employees, don't pick sides. By all means avoid Consultants vs Clients. When talking about the client's problem, say "we" a lot - it immediately puts you on "their team." 

You need to get past any "us vs them" or "big scary consultant" mentality quickly. So use language that puts you on their side right away: "we need to get those backups running again" or "we need to take a look at xyz" or even retrospectively, "what were our requirements for this program?"

Try to win clients over with your subject matter expertise, but avoid presenting it in a way that makes anyone look bad. You'll win more clients over, especially at the end of a long day of troubleshooting or health checking, if you've not made any of them look bad to their coworkers. 


Don't blame-storm on their behalf


Beware of landmines in your discovery (see previous), and when you find one, try to disarm it without creating an enemy inside the client organization. Never ask "who did this?" or "who set this up wrong?" In fact, avoid trying to identify "who did what" as much as possible. Just focus on the what. 

If someone admits their mistake: "I set this up this way because I thought..." turn the lesson to be learned to the whole group. Don't single out people for correction, don't make someone the culprit or the martyr. Always use that first person plural: "So what we need to do is..." 

Should a client need to know who is to blame, they'll figure it out well enough without your help. If they insist you point a finger, do so in your documentation, not in person, and do so by referring to a job role, not a name. 

Show me a client employer whose server admin wasn't doing database index maintenance, and I'll show you an IT Manager that wasn't staffing tasks to skills, or properly investing in training for their employees. 

Unless you've been brought in explicitly to be the executioner, a consulting engagement is not the time to put yourself in the middle of that existing conflict.
  • I've made fast friends refusing to publicly execute the party at fault. 
  • I've won over developers with a positive, helpful explanation instead of a punitive lecture. 
  • I've made allies and new business partners out of sysadmins that I could have publicly shamed.
  • I've had clients thank me for not blame-storming and finger-pointing, because nothing good really comes of that. 
Root cause analysis doesn't always have to name names for innocent mistakes and oversights. Again, the client will do all the executions needed with or without your help.


Stay uncontroversial at client sites


I was onsite at a client when one of their employees in a group asked me what I thought about guns. (Insert your own hot-button political topic here.) I casually turned away and said something like, "Nahhh, hey, let's get back to this database." In hindsight I was glad. Their beliefs got shared aloud anyway (of course), and their beliefs were extreme. 

If you wouldn't bring it up in polite company at the dinner table, don't bring it up with clients, and don't take their bait. I had no chance of emerging from that conversation without creating some distracting animus. Staying out of that conversation entirely was the only way. 

This occurred many times to me, actually. It was much better that I maintained that professional distance and a productive client relationship in each case. 

Don't engage in political or religious conversation with clients. Don't talk about how to raise kids, what to do with your money, or the outcomes of elections. Don't screen-share your browser bookmark toolbar or personal email inbox. Don't mention what news sources you follow. Today's world is extremely polarized, that's not your fault, but you must be aware. We have less and less in common with our political opposites. Don't let that invade perfectly good projects and business opportunities. 

I've watched conference calls devolve when the right kind of arrogance catches wind of a political polar opposite, it's not pretty. I've had perfectly fine business relationships and project success with folks who I later learned had politics incompatible with my values. That's ok. I'm not asking you to compromise your morals or ethics — I never had to — but you can prioritize the project over stumbling into a minefield.

I'm also not asking you to cover. Bring your whole self to work. Covering — hiding parts of your identity that don't fit into a mainstream in order to avoid negative bias — is a completely different topic. What I am suggesting is that for this short engagement, stick to business while being proud of yourself and being memorable. Let's be frank - consultants need to be as business-focused as possible. There is a time and place for clients to get to know you better, if they deserve to.

I'm not telling you to "read the room" and blend in with the attitudes and tempo of the clients. If you can, great. But "reading the room" and "dressing the part" too often means "be a straight white male or an unusually intelligent oddity, please." I did most of my consulting in the American deep south, and this is what most rooms looked like. 

Certainly, you should try to find a consultancy that aligns with your personal and career goals, and that allows you to bring your whole person to work. When collaborating internally and not on client sites, I hope you find executive leadership and management that holds high standards of ethical and moral behavior, as well as inclusive policies and practices. Perhaps your consultancy builds trust by matching donations to your favorite non-profits and charities, or partnering with and hosting your community involvement. You can't always ask the same of your clients.

I know this topic doesn't seem like breaking news in today's divisive politics-as-creed society. But now, as a consultant, more than it was when you were an in-house employee, your restraint against delving into personal conversations is required.
 

from Pixabay


Vocabulary


Common client-facing concepts in consulting:


Covering =  Hiding parts of your identity that don't fit into a mainstream in order to avoid negative bias. Everyone has likely experienced covering at some point in their careers, for a variety of reasons.  I have lost multiple great colleagues in consulting because they were tired of feeling like a phony trying to fit in. Consulting should not be one uncomfortable trial after another of putting on the masks of someone else. If you don't feel comfortable in the work environments where your consulting work brings you, talk to your manager, start a dialogue, and see if this consultancy is the right fit for you. For more resources and awareness, see: Harvard Business Review: Help Your Employees Be Themselves at Work and Deloitte Analysis: Inclusion survey: Uncovering talent

One team = If you've ever worked in a restaurant, you know that the wait staff blaming a chef or a chef blaming the manager looks bad for the whole restaurant. Similarly, blaming other colleagues or teams in your consultancy, blaming your PM, or blaming sales won't buy any credibility. It might feel good to absolve yourself from personal accountability, but it's not a win. To clients, you represent a company with a contract, not a set of individuals. Your consultancy will probably succeed or fail on this project (and the next contract) as a team. You'll need to practice accountability and acting in the client's best interest while keeping some conversations internal. There may be key moments that will test your resolve when deciding who to include on an email or chat message. As we discussed, be solution-focused and 86 the blamestorming.

Self-deprecation = Humor has its place. Self-deprecating humor might be the exact opposite of what the client is looking for. The sales exec or project manager can joke all they want about not knowing anything technical, but not you, the consultant. For the most part, being congenial and winning over a room with charisma, asking questions, and good story telling are great skills to have. Being comfortable addressing a room of clients and making them smile is a valuable comfort level to have. But spare them the humor about how you "can't even balance your checkbook". They're paying money for your services, don't given them pause. 

This is part two of a five-part series on Moving into Consulting 101
Got comments, questions, or guide for your fellow consultants? Comments are welcome below.

Monday, March 22, 2021

Moving Into Consulting 101

Introduction to this blog series

After being laid off by a Great Recession-era mortgage company in 2007, I entered into consulting. I made that switch from in-house DBA to consulting DBA at age 26 out of necessity, and as a professional and SQL consultant, I grew up in consulting. 

I've prepared this blog series as an intro to what it might be like going from an in-house position to a consulting position.

I made the transition myself. I've hired institutional DBAs to work as consulting DBAs. I've observed and coached others making the transition. I worked for more 12 years as a consultant with nationwide clients at an IT consultancy and MSP of ~250 people. Consulting as a database administrator was enjoyable and successful work that constantly sharpened my skills, and constantly exposed me to new challenges and the latest technology.

I enjoyed consulting work and I recommend it, especially for folks motivated to learn and see a lot of projects in a short amount of time. I think it's great for your first or second job out of college, just because of the career velocity you're likely to see. 

That said, everyone's experience is different. I have seen folks get a raw deal in consulting: stuck on old project support, difficult clients, or worst, SharePoint development. But over time, consulting should provide a variety of learning experiences, both interpersonal and for whatever your technical skillset is. This blog series isn't just for SQL data professionals, it's for anyone entering consulting or thinking about it.

After 13+ years in consulting I took a position at Microsoft, a completely different type of in-house spot, and have spent some time reflecting on, appreciating, and unraveling some consulting work habits. I hope you or perhaps someone in your network will benefit from this blog series.

A caveat - I have not done independent or solo consulting as my primary employment. Instead, this blog series is focused on the millions of people working for consultancies both big and small, in any industry or field of expertise. If you're making the transition from in-house whatever to consulting whatever, I might have some wisdom here for you, even if you're not a DBA.

With all that said, let's get started.

 

from Pixabay

Part 1 - The Patience of a Consultant


My first summer job was at a local independent computer shop when I was 16.

I was asked to count a large stack of printer cartridges. Simple math, I thought, I multiplied height times width times depth, easy. I was told my count was wrong. I stood there and insisted my math was right. (This is not a pleasant memory.) I was told I was wrong again, too low. It was only after walking around the stack that I saw it was not a cube, but an L-shape.  My teenage math was fine, it was my teenage premature assumptions that were flawed. 

Thus our first topic of the series:


Don't jump to conclusions


Take the time to walk around the client environment before doing math. Reserve your recommendations, and hold your tongue until after you've done proper discovery.

If you're brought into a client who is having an issue, definitely listen to everything they're telling you, but don't start based on only that information. I've seen consultants, even Microsoft support techs, too eager to suggest solutions and quickly start grand doomed fishing expeditions. It was frustrating for everyone involved.

Remember, they probably called you in because they haven't figured it out themselves, but they have tried. Don't assume your clients have no knowledge in this field, or haven't searched the internet for standard solutions. 

Similarly, don't assume they've told you everything you need to know up front, the client has almost certainly not given you a complete picture of the environment. In the next blog post in this series, we'll discuss the tact needed to unravel facts provided by clients, avoid blame-storming, and do root cause analysis without making enemies.


Put on your toolbelt


As a consultant, have your own questions and tools. Try to set about your discovery in a way that is methodical and isn't likely to miss anything. It's easy to be distracted by your client's initial diagnosis, or to be distracted by symptoms rather than causes. Before you mention solutions, do some digging yourself.

Also keep in mind that the upfront info you get from your client contact may not be complete. They may be hiding something that is their fault, something they know they should have done, and are just wanting you to get in and fix it. 

Listen before judging. I've heard a benchmark that newly hired managers should do nothing but listen and observe for the first few business cycles. Consultants should absorb as much info as possible before making recommendations. 

The Health Check Findings or Discovery Report documents (you should create these if they don't already exist) are deliverables where you to lay out what you found and what action is recommended to address the client's requests. You should provide your clients your findings in both simple executive terms and detailed technical terms. These deliverables are the place for your conclusions and recommendations - not day one on a new client.

A toolbox/checklist helps you avoid recreating the wheel each time you do a similar engagement. In some industries that have life-critical safety concerns, checklists are crucial. Do the the checklist every time and don't forget anything, or someone could get hurt. Give the same attention to creating repeatable content for your engagements.


Standardize your discovery


Like I mentioned, build a checklist useful in discovery. Assemble tools and stash reusable assets in your utility belt, next to your batarang and batgrapple. Organize and curate it and constantly update and improve it. 

The checklist should provide structure about both process and technical means of delivery:
  • Process: what questions to ask in what groups, what conversations to have, and what information to send and receive.
  • Technical: how to find answers in a standard way, reference for FAQs, talking points for common training opportunities, scripts and/or diagrams for important conversations.
This structure helps you in multiple ways:
  1. It gives you a marketable asset for your partners in sales (or, yourself). Your time and process are valuable assets. 
  2. It gives you a structure to fall back on when doing discovery or health checks. You won't miss anything, and your reporting will be thorough and useful to your future engagements.
  3. It gives you institutional knowledge for your junior team members and new hires to ramp up to your team's process, so that client deliverables are produced consistently.
If you're joining a mature consultancy, they might already have these assets. They exist for a reason. Absorb them quickly, and contribute to them in the institutionally-provided ways. 

Bring your past experiences with you, sure, but make sure to leverage the wisdom of a well-developed toolkit if it exists. If it doesn't exist? Get to work on it.

from Pixabay


Vocabulary


Common structured findings concepts


Low-Hanging Fruit = Easily achieved-wins. Oftentimes a Discovery Report or Heath Check Findings will include a variety of tasks of various difficulty. It is always good to identify the easy wins are low effort, high reward. These help both your consulting engagement get positive results, and the clients to realize a return on their investment that looks good to their superiors. Always stress the importance of more difficult tasks while stressing the fast payoff of easy tasks — you want to get paid to do all the work, in the end.

Remediation = You've performed your health check/assessment/discovery/evaluation, and now you've got recommendations to make. Be sure to include not just the problem scenario, but also how you recommend solving it. Don't present the client problems with no solution, always recommend a path to remediating the problem, even if your consultancy doesn't offer those services.  


Sales Collateral - Visual that looks good in a sales pitch meeting. Believe it or not, sales people aren't great at making this stuff. Oftentimes, senior technical folks provide good info and the marketing or content people turn it into go "battle cards" or "solutions pitches" for sales people to take into conversations. Senior consultants should try generating some sales collateral for the projects you want to do, and working with marketing/content folks to refine it. A good first bit of sales collateral kit? "XYZ Health Check" or "ABC Success Checklist".



This is part one of a five-part series on Moving into Consulting 101


Got comments, questions, or guide for your fellow consultants? Comments are welcome below.

Friday, February 26, 2021

How to: exec sp_help on temp tables?

Say you have a temp table and you want to see the columns names.

For example, I was trying to convert a query from using a #temp table to a CTE instead, and wanted to see the column list and resulting data types of the #temp table. 

Sp_help is a helpful SQL Server system sproc to return schema of objects. It's that magic that happens when you press Alt+F1 in SSMS. (Side note: showing someone the Alt+F1 shortcut in SSMS for the first time and seeing their life change for the better is really rewarding.

But Alt+F1 doesn't work on #temp tables because it tries to execute this:

exec sp_help #temp;
  

Which fails with the error:

Msg 15009, Level 16, State 1, Procedure sp_help, Line 79 [Batch Start Line 21]
The object '#temp' does not exist in database 'myuserdb' or is invalid for this operation.

That's because as far as sys.objects is concerned, temp tables don't exist in the user database you're working in, but always in the TempDB.

So you change your database context to TempDB:

use tempdb;
exec sp_help #temp;

 
And it works! You get back the expected sp_help output. 

But what if you don't want to change your database context, or you are working in Azure SQL Database, where you youths can't use USE? Use this, instead:

exec tempdb.sys.sp_help #temp;

You can also shorten this to:
    exec tempdb..sp_help #temp;

Why? The ".." syntax or "dot dot" syntax shortens the schema by assuming the default schema. The default schema is users is dbo. This still works for sp_help and other system objects are in the sys schema but are addressable through the dbo schema. 

Helpfully, both of these work:
    exec tempdb.sys.sp_help #temp;
    exec tempdb.dbo.sp_help #temp;

As for Alt+F1, resign yourself to not using it for temp tables. You could map another shortcut key in SSMS or Azure Data Studio to exec tempdb..sp_help instead of exec sp_help if you wanted to.

Sunday, February 21, 2021

Virtual User Group Presentations, Feb 23-24

I'll be presenting at two virtual SQL data platform communities this week on a couple well-rehearsed topics, each with fresh updates.

Tuesday, February 23, 2021
12:00 PM to 1:30 PM EST
DBA Fundamentals Virtual User Group

Database Professionals Virtual Meetup Group
Event link: Security Principals 101 | Slidedeck and Samples

Looking forward to speaking to both these audiences this week, interacting and answering questions! It will be my first time speaking to a public user group community since joining Microsoft in November and I'm eager to get rolling again.