Blog

Quickie: SSRS Multi-Column List/Grid

While there’s no native control like it, with a few simple tricks and visual slight-of-hand, we can fake it.

Advertisements

Today I present to you, a hopefully less confusing and more intuitive explanation of how to make a “multi-column list” control on your SSRS report.  And if the term is confusing by itself, I’ll illustrate in a second.

Three links that helped me get going in the right direction:

They all roughly boil down to the same concept: placing redundant or repetitive controls (like a set of 2 or 3 tablixes), and using a RowNumber() expression with some modulo arithmetic to set “visibility” properties of the dataset rows shown in each.

We don’t quite get to “dynamic # of columns and rows based on the total # of records” nirvana (e.g. make it a 3×5 grid if you have somewhere around 15 records, but only make it 2×4 if you have 8 or less records, etc.), but I’d estimate that this approach would suffice in 90% of use-cases.  And whoever at Microsoft decided to not build a control like this into the toolset, curse you!!

Yes, we’re not even past the intro section and I’ve already cursed someone at MSFT.  Doesn’t change my passion for the platform in general; it’s just a minor curse.  =)

Concepts

SSRS uses a dataset to fill one or more controls with data; controls such as the tablix. In my regular implementations, a dataset is usually a stored-proc. The tablix shows the results of the proc in tabular form. The problem with the tablix control is that you can’t turn it into a multi-column layout, as you might see with some more advanced UI controls. You might think that the matrix control would be suitable for this, but no; that is just a table-like structure that can support hierarchical column-groups and row-groups.

For example, let’s say you have a table of Users, with columns ID, Name, and Role. Like so:

ID | Name    | Role   
---|---------|--------
1  | Bob     | Admin
2  | Alice   | User
3  | Jack    | User

But with many more records. We want to display this on a report, which is typically viewed on a PC screen, i.e. landscape orientation (wider than tall). So wouldn’t it be nice if we could display it like so?

ID | Name    | Role   || ID | Name    | Role   || ID | Name    | Role
---|---------|--------||----|---------|--------||----|---------|-------
1  | Bob     | Admin  || 2  | Alice   | User   || 3  | Jack    | User

etc. Right? Right.

Implementation

The actual work is fairly simple. We add 3 tablixes to the report and place them side-by-side. We connect them all the same dataset, i.e. stored-proc — which will only be executed once, conveniently! On the data row (as opposed to the header row) of each tablix, right click to Row Visibility. Then use an expression like so:

=IIf(RowNumber(Nothing) Mod 2 = X, False, True) — where X is going to vary from 1 to (# of columns) - 1, and finally to 0 for the last (right-most) tablix.

Remember, the expression defines the row’s Hidden property, not “Visible”. I know, it seems backwards, but that’s how it is.

In plain English, we mean, “if the row is the 1st row, show it, else hide it”. Likewise, for the second tablix‘s row, we’d say “if the row is the 2nd row show it”. And so on.

Not bad, right?

Final Thoughts

It’s certainly odd that there’s no native way of building such a report. But with a few simple tricks and visual slight-of-hand, we can fake it.

ssrs-multiple-tablix
I highlighted the middle tablix so you can see that there are 3 separate ones, but we want to make sure they’re precisely adjacent to each other so the end-user can’t tell them apart.

T-SQL Tuesday 106: 5 Things Not to Do With Triggers

Here are a handful of anti-patterns that I’ve seen with triggers in my time…

This month’s invite comes once again from our benevolent overlord* community pillar Steve Jones, head of SQLServerCentral.com! If you haven’t been there, stop reading immediately and go check out the helpful forums and ‘Stairways’ articles. Some truly excellent content to be had.

No, don’t stop reading immediately… save it to your favorites/reading-list and look at it in 5 minutes when you’re done here.  =)

*Though I’ve not had the pleasure of meeting him in person, I’ve heard Steve is a phenomenally humble and down-to-earth guy, so my silly comment about him is even sillier in that light. ❤

Triggers – Love ’em or Hate ’em

Borrowing a bit of a mini-game from the CodingBlocks guys, the first “Google-feud” (auto-complete result) for “sql server triggers are ” is sql server are triggers bad.  Well, they can be, if used improperly / to excess.  Like most bits of tech, they have their place and their use-cases.

I’ve blogged about a few such use-cases here (a “who did what” audit-trail type of trigger) and here (as part of the Nested Set Model implementation), which you should definitely read. I didn’t condone click-baity titles back then. Alas…

click this right now omg
All we need now is a “blink” tag…

Here are handful of anti-patterns that I’ve seen with triggers in my time.

Thing 1: Using them as Queues

Repeat after me:

A trigger is not a queue.

Triggers are executed within the same transaction as the query that fires them. Meaning, they’re subject to all that ACID-y goodness of a transactional system. This is a double-edged sword. If all is successful, it guarantees that trigger will do its job when the calling query runs, which is great for audit-ability. On the other hand, if the trigger has a problem, anything and everything that triggers it will also fail.

The fundamental distinction between this and a queue, is that the success of the queued action is not immediately critical to the continued operation of the thing that called (queued) it.

So if your requirement matches the latter behavior, and not the former, do us all a favor and use a real queue. Heck, find one of the few people who know Service Broker. (Hint: one’s a Warewolf, another’s a Poolboy.)

Thing 2: Making them WAY TOO BIG

Mostly because of the transactional thing, the rule of thumb with triggers is K.I.S.S. “Keep it Small and Simple.” Even the audit-trail example is a bit much if the table being audited is under significant write load. Typically, if the business requirements are both high-throughput and high audit-ability, you’ll be implementing a much more complicated tech-stack than just plain ol’ SQL Server with triggers.

Some of the offenders I’ve seen include: A trigger that wanted to write to several other tables with IF conditions that branched based on what column was being updated. And a trigger that required near-SA level permissions to do some back-end maintenance-y stuff. Those are both recipes for problem pie.

pie that looks like a kraken attacking a boat
We’re gonna need a bigger… fork?

Thing 3: Doing Nothing Useful

Somewhat opposite of above, there’s no point in introducing the management and performance overhead of triggers if they’re not performing a transaction-critical operation. For instance, something better left to a queue.

Thing 4: Housing Business Logic

There’s always been a holy war about whether “business logic” belongs in the database or in the application code. And, since geeks love sub-classifying things to the Nth degree, there’s a sub-holy-war about what “business logic” actually means. But I won’t go into that here.

If you fall on the 1st side of the fence, and you feel the desperate desire to embed some logic in the data layer, it belongs in stored procedures, not in triggers. Reasons include maintaintability, discoverability, performance, documentability. Not to mention source-control on procs is a helluva lot easier than on triggers.

Thing 5: Too Many of Them

there's too many of them
Stay on target..

While multiple triggers can be defined for the same action on the same table, that’s not an invitation. You enforce trigger execution order to an extent (first and last), but any more than that and you’re asking for confusion. Falling back on the KISS principle, if you need more than one trigger on a table & action (insert, update, or delete), you probably need to rethink the underlying design.

Bonus

Using INSTEAD OF vs. AFTER: it’s fairly self-explanatory, but just be aware of what you’re doing, especially with the former. You’re literally replacing the desired action of, say, an update query with the contents of your instead of update trigger. If this is not obvious to all users of this table, you’re in for some really surprised faces and angry messages.

And that’s all I have for today folks! Enjoy triggering stuff. In moderation, as all things. =)

SQL Style Guide – a Rebuttal

Both the original article, and my responses, are guidelines, meant to help you start a conversation with your own team about how you can get better at writing code.

In what will surely be a controversial post, I give my take on some of the major points of this “SQL style guide” that made the rounds on Hacker News / Reddit recently a few years ago.  Hey, I never claimed to be a source of breaking news.

stamp of controversy
Feels so empty, without me…

Now remember kids, these are opinions — everyone’s entitled to theirs, and it doesn’t mean you’re right or wrong.  As the author repeatedly points out, consistency among a team / project / environment is more important than anything else.  Both the original article, and my responses, are guidelines, written by people with some experience (guided by others with more experience) to help you start a conversation with your own team about how you can get better at writing code.  Because that’s what we’re paid to do, among other things.

Basics

I agree with most of the points here:

  • Consistent descriptive names (I would add “concise” too — autocomplete/intellisense has come a long way, but we’re still typing stuff sometimes)
  • White space & indentation
  • Datetime literals – hail Saint Bertrand
  • Comments – you, dear reader, know my thoughts already
  • Some OOP principles should be avoided because they usually lead to poor performance
  • Hungarian notation is best left in the ’80s where it belongs

Something I don’t abide by:

  • Sticking to the ‘standard’ and void vendor-specific functions/features

Some brief justification for my rejection:

Database code portability is largely a myth — it sounds great, in theory, but most teams/projects never actually do it.  If your data persistence layer is ever slated for migration to a new tech-stack, it’s going to require a massive overhaul anyway, in which those vendor-specific functions/code-bits will the least of your worries.  More likely, you’ll be swapping-out pieces of the data layer in small chunks to move into other storage tech, like a NoSQL store or a DocumentDB or something; and eventually over time, the whole original data layer will have been moved, and the concern over SQL code portability will be moot.

Furthermore, database vendors give you these features/functions for a reason — they’ve found that it greatly enhances their product and the productivity of developers who work with it.  So why not take advantage?

Finally, if your application is ‘cutting-edge’ enough that ALL db access is done via ORM or some kind of repository layer in the code-base… guess what?  You don’t have this problem in the first place!  Because the database is a dumb state storage mechanism, containing little to no actual code whatsoever (storec procs, functions, etc.).  So, port away!

now that's what i call edgy
I can almost feel the edgy-ness…

Other basic issues:

  • CamelCase (actually TitleCase) is pretty standard in a lot of DB schemas, and I see nothing wrong with it.  Despite my love of underscores (snake_case) , it does make for more awkward typing.
  • Plural or singular entity names should match the convention preferred by your overlaying ORM, if you’re at the point of DB design; most of the time, though, you’re working with a DB that you’ve inherited and you have no control over entity naming anyway, so stop whining about it and get on with life.
  • Leading vs. trailing commas: I prefer leading, but the arguments against it can sound convincing (not just his, but in general in the tech community) — my experience leans toward being more likely to futz with the middle-to-end of a list than the beginning (1st item), thus making the leading commas more likely to help, but that’s just me. Also, thanks to an awesome member of the Coding Blocks Slack, a point in my favor is that source code comparison (diff) tools will only show the one changed line instead of two, if you’ve had to add to the end of the column list.

Naming Conventions

Yes, please avoid reserved keywords, replace spaces with underscores (or use TitleCase to avoid having spaces), and use concise yet meaningful table aliases when you’re writing queries.  I still remember, when I first started working at my current company, literally gasping and cursing under my breath when I found that some databases actually had a space in the name.

Beyond that, the article goes a bit too deep in the weeds for me, especially the whole “known suffixes” thing — because isn’t that just Hungarian notation on the other end?  How about names that make it intuitive, such as IsActive for a bit flag, or LineNumber or RecordSequence for a sequential integer that’s not auto-generated (not an identity value), or @NumMonths as a parameter for a stored-proc that indicates how many months of reporting to fetch?  Common sense should rule the day, not arcane prefix/suffix conventions that will never be documented or enforced.

White Space

This whole notion of a “river” feels strange and awkward.  It’s made worse by the fact that some clause’s keywords are “too large” for the “standard” river width (which is the width of the SELECT keyword, ish), such as group by and left join).  Plus, I’ve yet to see truly excellent tooling support for this kind of style (be it VSCode, Visual Studio, SSMS, SQL Prompt, or other styling tools / auto-formatters).  Given that I still largely write my code without continuous automatic re-style-on-the-fly styling assistance, I find this hard to digest.

Side-bar: big kudos to the author for pointing me at this typography article, which challenged my long-ingrained writing preference of double-spacing between sentences.  Even now, I do it while I write this post, knowing it’s wrong — I can’t help myself!

For similar reasons, JOINs and sub-queries don’t need to be “on the other side of the river” — since I’m actually saying “there is no river”, what I mean is, don’t indent those JOINs or sub-queries excessively.  In the FROM clause, the JOINed tables are just as important as the first one, so I don’t see the need to reduce their importance by putting them so far to the right.  And please for the love of all things holy, stop putting the JOIN predicates in-line (on the same line) after the joined table — put the ON conditions to their own line and indent it!

Sub-queries are a strange animal, and I won’t dive deep on it here.  Basically, I try to style them the same as I would normally, just indented to the context of their scope; and within Javascript-style parentheses — meaning, open-paren on the preceding line (or its own line), then the body, then close-paren on its own line to finish.

Special note about the “Preferred formalities” section

BETWEEN is mostly evil.  I’m not saying you should never use it; just be very clear about why you’re using it, and only use it with discrete valued types (DATE, INT), NOT with continuous (or conceptually/nearly-continuous) value types (DATETIME, REAL/NUMERIC/DECIMAL).

The UNION operator is often misused, usually because UNION ALL is preferred (and is what you really meant anyway), but a blanket statement to “avoid it” misses the point of why it exists in the first place.  Likewise, temporary tables (#temptables) are wonderful tools when used properly and in moderation, but flagrant overuse can lead to what I call #tempocalypse (which means you’re hammering your TempDB so hard that its underlying storage system screams for mercy).

cry for help cheeto puffs
Like Cheeto Puffs, your TempDB has become bloated and full of cheeze.

Misnamed section “Create syntax”

What he really does here is expound upon table and database design principles.  This does not belong in a “Style Guide”; it probably belongs in a “Design Guide”, but because the relational database as a technology is so mature (yes, that means ‘old’) by this point, most of this feels completely unnecessary and redundant.  And again, you’re working with inherited designs over 90% of the time, where you don’t get to make these decisions, because they were made for you by your predecessors.  If you are so lucky as to be designing a relational model from scratch, look for advice from the tried-and-true architects of the trade.

I do echo and reiterate his advice to look at other data storage tech for things that are not ideally served by an RDBMS, such as EAV models, document storage, key-value storage, text search, etc.  There is no shortage of NoSQL tech to meet those needs, and they will do a much better job of it than you or I could in designing a hacked-up SQL model that somewhat does the job but falls over flat when faced with scaling up to a larger size or heavier workload.

In Conclusion

As I said at the beginning, these are opinions.  I applaud the author’s effort, and the fact that he actually got something together, made it open-source on GitHub, asked for feedback, and actually got traction with several communities discussing it.  That’s more than I can say for myself at this point!

I hope that this spurs some discussion on your database development team, and perhaps helps you work toward making a consistent style guide for your own environment.  Even if it’s just water-cooler talk, it’s always good to remember why we write code: so that others can read it.  (Sure, it’s also to make stuff happen, i.e. “make the computer do things”, but that’s a side-effect — your main goal should always be readability and clarity.)

Do you have some comments, thoughts, disagree? Leave me a comment! I’d love to hear from you.  🙂

needs-moar-drama
Drama-Cat is bored…

Movie Wednesday #2 – The Meg

The only thing deep here is the water. But it’s good-old-fashioned shark-movie fun.

It won’t always be new movies, I promise.  I could go on for pages about such classics as The Gladiator, Independence Day, Jurassic Park, Scream, Pulp Fiction, Face/Off, Lord of the Rings, and more.  And I will, eventually.  I’m going to try out a hosted WP service with my own domain soon, and then I’ll have more freedom to draw lines in the sand between the tech stuff and the fun stuff.  But for now, you’ll have to settle for reviews of new or recent movies that we go enjoy semi-regularly at our local recliner-lined pizza-serving cineplex.

Without further ado…

Things Are What They Are

Let’s make something clear right off the bat.  This is a popcorn flick.  For those who aren’t up on the movie-goer lingo, that means it’s more or less mindless fun.  It’s CGI-infused, big-screen big-sound action, with a few recognizable faces, a few up-and-comers, and a whole gaggle of extras.  Throw in a Wilhelm Scream for good measure (fair warning: TVTropes link!).  And yes, a black guy dies first.

Statham is no stranger to this sort of film.  One might even say he’s built his entire career on them.  But most of those are more the dark, gritty, pure action flicks.  Yet he’s proven he can handle himself with the lighter, more humorous side as well.

The monster, the Megalodon, is sufficiently large and in-charge.  It menaces and mangles effectively, and even outsmarts a hapless human or two.  The premise around where and how they find this beast is mildly interesting, despite the occasional disregard for high pressure physics.

bro-do-you-even-science
Because Bill Nye will have your arse…

Who Dat?!

One of our favorite things to do with movies is to call out where we know the actors from.  Here we have Dwight from The Office, and Travis from Fear TWD — we’re supposed to believe he’s Statham’s brother, which is a pretty hard sell, unless one is adopted.  Oh, and that short-haired techie chick with the annoyingly hipster name? That’s the lead singer of ‘Evermoist’ from Pitch Perfect 3!

pitch-perfect-3-evermoist-wave
Pitch Perfect 3 – the ladies’ main rivals

Where It Hits and Misses

We get some cinematic moments of shock and awe, a few tearful goodbyes of self-sacrifice, and a handful of suspenseful close-calls.  And obviously, some big meaty carnage.

Unfortunately, the dialog falls a little flat sometimes, and the humor isn’t always snappy. Now, you’re not watching this for deep character development.  The only thing deep here is the water.  But there could have been a bit more emphasis on some key elements that would make the characters more memorable or relate-able.

Comparisons Are Fair Game

It’s quite natural to compare this to Jaws, and the many myriad of mimics that it spawned.  Obviously, this film has no such aspirations, nor does it feel the need to shove this fact down the audience’s throat. Unlike, say, Sharknado, which tries way too hard to be “so bad it’s good” that it ends up looping back around to terrible again.

Furthermore, the actors understand this, which means that, while they do take their roles somewhat seriously, they allow themselves to have fun with it.  Nobody’s trying to impress the Academy here.  And frankly, nobody’s expecting you to watch more than once. Your enjoy it for what it is, and then you walk away.

someone once said in a meeting let's make a film with a tornado full of sharks
No, let’s make SEVERAL!  Best idea EVARRR!!!1

The Verdict: one solid thumb up

Lets be honest.  You’re going to watch this because you enjoy big sharks, Jason Statham, and/or ocean-themed action-adventures / creature-features.  And you won’t be disappointed.  It’s just good-old-fashioned shark-movie fun.  Enjoy your popcorn, then go about your business.

Have fun!

Just Another Brick (in the) Wall

Over-engineering may be foolish, but under-engineering is just as perilous.

This month’s T-SQL Tuesday topic, thanks to Wayne Sheffield, is a doozy.  It’s difficult to admit failures, even if we eventually overcame them.  Mostly, it’s tough to fess up to those hours upon hours of what feels like wasted time preceding the hopefully inevitable breakthrough.

Let me tell you a tale of square pegs and round holes.

Get your mind out of the gutter.

And strap in.  This puppy went over 2k words.  I didn’t intend it that way, but, c’est la vie.

A Short History Lesson

I used to work for a software company that made K-12 educational assessment & reporting products.  A large part of K12ED is dealing with learning standards, these historically fraught named concepts that students are supposed to have “mastered” and learned during their time in the US public school system.  You ever hear a reference in pop-culture or media referring to “teaching to the test” or “state standardized testing” or similar?  That’s what they’re talking about.

In the late 90’s, a bunch of states, including California in 1997, finalized and centralized a “list” of sorts, almost a database (but not formally, because let’s face it, this was still the early days of what we know as modern IT), of said Standards, which came to be unofficially known as the “CA ’97 Standards”.  For over a decade, these learning goals dictated what was taught when in our schools.  Government funding incentives, based on state standardized test scores, drove instruction to the point that teachers had little if any time to dynamically mold their students’ minds into critically thinking, multi-faceted, creative individuals.

But this article isn’t about my bias.  This is about the broad, sweeping shift in the K12ED landscape called “Common Core”.  As the technology sector grew more mature and started permeating more “traditional” industries, a vocal minority of thought-leaders had what they deemed an epiphany.

Hey, what if we took the old-guard educational bureaucracy, and all those disparate state standards, and turned it into a unified, technology-driven learning ecosystem?

Sounds great in theory, right?  I mean, surely their intentions were good?  Well, you know what they say about the road to Hell and how it’s paved…

Gosh, there goes my bias again.  Sorry, let me just tuck that back in its pocket.

Anyway.  These new Core Standards introduced some new ways of thinking.  For example, that some learning concepts are inter-related and “cross-cutting” (a fancy way of saying that sometimes a Math concept requires a fundamental Reading-Literacy knowledge-point to fully grasp).  This had some very interesting impacts on the underlying technology systems which relied on, and housed, said Standards.  System which, I might add, had existed for over a decade at this point, in many cases.

Bringing it Back to Data

Our company’s system was one such.  Our key partner’s system, from which we quite literally inherited the traditional, relational database structure to house the CA ’97 Standards, was another.  You see, back before RESTful APIs ran the world, software systems relied heavily on what we call “local data stores”.  In order to show the teachers and administrators, who primarily used our system, the Standards, in which their students were performing well (or poorly), we had to relate those Standards to the test questions that said students were tested on month after month, year after year.  And, like so many other small businesses of the late 90’s / early 00’s, we had a trusty ol’ SQL Server lying around, just waiting to be loaded with all our precious data.

This was fine, for the most part.  The legacy Standards conformed reasonably well to a relational data model, even though we had to throw in a bit of hierarchy (using the good ol’ adjacency list scheme).  There wasn’t a complicated set of relationships from Standards in different subjects (Math, Science, History) to each other, and people didn’t care to do much in-depth analysis beyond “are my students getting well-prepared for the state tests?”.

Enter Common Core

You parents thought these things were complicated and convoluted — just Google “common core math problem” and you’ll find no shortage of critical satire.  Well, the underlying data structures required to store these new Standards were going to be significantly more complex as well.

One of my main jobs, for about a year or two, was to oversee the importation of said Core Standards into our SQL database system.  On the surface, it seemed reasonable — we had a hierarchy concept already, and we had a roll-up & drill-down mechanism for the handful of different “levels” of said hierarchy.  But it was all very static.  What that means, for us tech-heads, is that it was not easy to change or extend; not adaptable to new and expanded requirements.  The older Standards adhered to a fairly strict hierarchy, and each level of roll-up had a distinct name.  With Common Core, they broke out of that old mold, while simultaneously keeping some of the names only to change their meaning depending on context.

Think of it this way.  A group of cattle is called a herd.  A group of sheep is also called a herd.  And a group of seagulls is called a flock.

And I ra-a-an.. I ran so far a-wa-a-ay…

Sorry, where was I?  Right, group names.  So what if the government suddenly decided for us that a group of sheep will from now on be called a ‘gaggle’.  But only if they’re all female.  If the group contains any male sheep, it’s called a ‘herd’ still.  And groups of cattle will be still be called herds, unless it’s purely a group of bulls being raised for beef, in which case we call it a ‘meatsock’.

Have I lost you yet?  Of course I have!  This is pure nonsense, right?  Language does not work this way.  Moreover, hierarchies of groups of things do not work this way.

But I digress.  There was, despite my jest, a method to the madness of the new Common Core hierarchy groupings.  And I did learn it and understand it, for the most part.  The problem was that it threw our existing legacy data model to the wind.

Enter Academic Benchmarks

As usual with a legacy software system in a small company, the resources and buy-in for a data-layer overhaul were nil.  So it fell to us intrepid DB-Devs to shove that snowflake-shaped peg into the very square hole of the old relational model.  We sketched out plenty of ERDs, brainstormed how to tack-on to our existing structures, but nothing short of a miracle would make this new data conform to these old static norms.

Thankfully, the “geniuses” (and yes, that is used sarcastically) over at Academic Benchmarks, or AB for short (at least for the purposes of this post), had already done this.  And we paid them, thousands of dollars per year, for the convenience of using their GUIDs to identify Standards across different systems and vendors.  Never mind that they were just perpetuating the bad model of yesteryear; never mind that they provided zero support for data quality feedback loops.  We could happily take their Excel files or CSVs and load them nearly straight into our database.

Enter, and Exit, ASN

While I was searching for the words to express how insufficient our data model was, I came across this little gem from the Gates Foundation: Achievement Standards Network, or ASN.  (ASN stands for many other things, so as with all acronyms, it’s all about context; just fair warning for the Google-happy.)  The architects here had understood that learning standards needed a better and more flexible model, not only in terms of storage, but also in terms of data interchange format.  This new kid on the block called JSON had been making waves for a while, and was subsequently widely adopted by the tech industry in general, so it stood to reason that this would be the preferred format for publishing and serving the Standards from ASN.

Bonus: it was FREE.  Yes, really.  What a wonderful thought, I said to my team, to imagine never having to pay those crooks at AB another red cent!  Eventually.  After years of transition.  But alas, it was not to be.  See, AB had been around the industry for a long time.  They had their hooks in almost every learning content publisher and assessment vendor.  So as tempting as this shiny new source of academic truth may have been, sadly, it was not practical.

Enter the Contractor

Somewhere around the same time, we took on a promising new developer who, not only had a very strong background in Math and CS fundamentals, but who had also proven his worth with real world applications with actual “in the wild” deployments and users.  He was a bit arrogant, actually.  One could argue that he’d earned it, perhaps, but we didn’t appreciate always being told everything we were doing wrong, constantly, to the point that it was hard to hear the more important bits of wisdom and insight into how we could improve.

Despite that ego, one of his biggest contributions to the team was a fresh impetus to learn new things and branch out to new technologies.  To start looking at new Javascript frameworks.  To revisit OO fundamentals like Dependency Injection, and stop writing such procedural code.  To consider NoSQL data stores.  In particular, graph data stores.

Sadly, that last part came in too little, too late.

Side-note, on the crest of the micro-services wave, he diagrammed and proposed an entire system re-write for our core software product, using micro-services architecture and the concept of small purpose-dedicated data stores.  It looked real purty, but was ultimately and completely impractical for a company of our size.  If we were a “true startup” with millions in VC funding coming out the ears, and could afford to throw a brand new, young & hungry “2 pizza team” at the product, then sure.  But that was not reality.

The Brick Wall

No two bones about it: we had to support these new Standards.  So we soldiered on with our relational database tables.  And we tacked-on additional entities and relationships, logic and code, to make it “almost” do what the Common Core Standards wanted to do.  Effectively, what we were trying to do, was to shove that pretty round sparkly peg of graph data, into the dingy old square hole of a 15-year-old SQL table structure.

Somewhere along the way, AB caught up with the times and started offering other, less “flat” formats, for their Standards data.  So even though ASN was off the table, not all of our work toward JSON ingestion/conversion went to waste.  Consuming the data exports from the vendors wasn’t a problem — we’d already been doing this.  That was not the issue.

The issue, the brick wall against which we continually banged our heads, was the fact that we just plain couldn’t support the advanced & complex relationships and groupings (categorizations) of the new Standards.  Which turned out, in retrospect, not to be the end of the world, because honestly it would take years, if not decades, for the educational system’s old-guard mentality to even comprehend such relationships and categorizations, let alone how they could help shape classroom instruction toward better outcomes for their students.

Good lord, that sounded like a bunch of jargon.

What I mean, in plainer English, is that we spent a lot of time worrying and arguing about something that did not matter as much as we thought it did.  The consumers of our system, the teachers and principals and such, honestly didn’t care about all that.  They just wanted to know if their kids were on-track to be “Proficient” on the state tests so their funding would remain steady.  (I don’t give them enough credit, I know; teachers themselves also needed to know, on a regular basis, which kids needed special attention in what areas of learning, but that’s beyond the scope of most generalized reporting tools.)

Hindsight is Always 20/20

So what was the lesson here?  I don’t want to overlook the fact that we were still using the wrong data storage technology for the problem, fundamentally.  Or at least, the wrong data model.  But, we live in a real world where software needs to be delivered on-time, in-budget, with less than perfect teams who have less experience and expertise than they feel they should.  So instead of butting our heads against that brick wall, let’s try to remember to be pragmatic.  To be realistic about what’s feasible for who & when; and to adapt the parts and segments of our application infrastructure, appropriately and efficiently, to the business problems and use-cases at-hand.  Over-engineering may be foolish, but under-engineering is just as perilous.

Movie Review Wednesday

So I’ve Been Thinking…

Data isn’t literally everything.  I mean it is, technically, but it’s not all super happy fun times, so we need to take a break once in a while and do something less neuron-intensive.  Thus, my new segment: movie reviews!  Because, despite what you may have read, all work and no play make Nate a dull boy.  And yes, I promised you this blog would be professional.  Mostly.  I remember specifically using that word.  So don’t wag your naggy finger at me.  If you don’t like it, you can simply avoid the tags like #offtopic and #movies.

You, dear reader, may have guessed this already, but just in case you haven’t:  We watch a lot of movies.  Like, a lot.  That new red MoviePass card came out just around the same time that our local cineplex remodeled itself into a “luxury theater” with the reserved reclining seats, dining service, and all that jazz.  But besides that, when the day’s work is done and it’s time to chill, Netflix/Prime/Kodi are our M.O.’s.

Side-note, I’m running Kodi on LibreElec on a Dell micro-PC that I procured for $99 a few Black Friday’s ago.  It’s puny by PC standards, but beefy by media-stick/RPi standards, so it works pretty well.  We’re finding that our WiFi isn’t always quite up to the task of steadily streaming 1080p, but… meh.  It’s generally usable.  It serves up movies from my 3TB spinning-rusts on the circa-2014 gaming PC via Plex, which is a phenomenal app for the modern moviephile — if you haven’t checked it out, do it.

Let’s Get To It!

Back on topic.  Off topic, I mean.  We recently saw The Spy Who Dumped Me.  I was excited for this movie as soon as I saw the poster: Mila Kunis and Kate McKinnon.  Need I say more?  Not really, but I will, because that’s presumably why you’re reading.  I mean, I could watch these two actresses do just about anything for 2 hours.  Let alone a buddy-action-comedy about international intelligence intrigue gone awry.

I first loved McKinnon in the Ghostbusters reboot, where she, no disrespect to the rest of the phenomenal cast, stole the show.  That was a super fun movie too.  (I’m not an SNL guy, though her Hillary impersonation was superb.)  And Kunis, I’ve loved since she took over the voice of Meg in Family Guy.  It didn’t hurt when the wife said she had a girl crush on her too.

This is somewhat of a directorial debut from Susanna Fogel (she has some credits, but nothing blockbuster-worthy, as far as I can tell), and it hits a lot of good notes.  The acting is solid — especially, obviously, the two leads — the action believable, the punchlines not overly cheesy, the character development realistic, and the twists compelling, if a bit predictable.

The one notable gripe I have is the completely random and unnecessary bit of male nudity.  And it’s not that I’m a prude; nudity can have its place in movies, if it serves a purpose.  Look at Forgetting Sarah Marshall — very similar to this instance, technically, but its purpose was clearly to emphasize the point of the character’s vulnerability and shamelessness.  Contrasted to here, where it’s just some dude that Kate’s character hooked up with who turns out to be the first of dozens of agents to try to kill the duo in pursuit of the elusive “flash drive of doom” (which, surprisingly, is not yet a TvTropes trope, but I feel like it should be).  There was literally no point.

Anyway.

On the whole, the movie was excellent — a fun ride through Europe on the tails of two hapless yet confident, inexperienced yet tough, and sometimes supremely lucky ladies who go toe-to-toe with agents of CIA, MI6, and the big scary Russian Mafia.  We see these amazingly close best friends overcome frightening odds and tense situations by sticking together and encouraging each other, which is pretty awesome if you think about it.  There’s a lot of negativity thrown around these days, whether it’s in the name of politics, social movements, or otherwise.  Even your standard male buddy-cop movie usually hinges on the guys’ “banter” of tearing each other down (even in jest), and rarely does it truly bring out the best elements of their character.

Speaking of characters, the chemistry of these two women is top-notch.  They’re different, to be sure, but it works in their favor.  McKinnon is obviously more extroverted; “a little much” as someone tells her derogatorily, but that’s why we love her.  Kunis is of course more subtle; coy, yet never a damsel in distress.  One of the best bits of dialog in the movie, I think, comes from McKinnon’s positively bursting joy at seeing the head of MI6 is in fact a woman, who “hasn’t sacrificed one ounce of femininity!” (Gillian Anderson, of course, being very familiar with the role of an intelligence officer).

Now, I will normally try to avoid spoilers.  Today I will simply tease that yes, while Kunis ends up falling for a guy, he’s proven worthy, and there’s no reason to fault her for it.  If anything, it keeps us grounded in reality.  Of course that reality quickly turns fantasy again in the “post ending”, but that’s for you to enjoy.

The Verdict

Highly recommended, two thumbs up.

Happy Hump Day!  Now go watch some movies.  Preferably this one.  But hey, I ain’t the boss of you.  Do your thing.  And stay tuned for more!

Keeping Track of SQL Scripts with SVN

I’m talking about a locally stored, locally managed, only-ever-have-one-user, repository of SQL scripts.  All it really needs to do is track changes on a daily (ish) basis.

Inspired by a Slack conversation with friend & former colleague, and building off last week’s T-SQL Tuesday post:  How do I keep track of my SQL scripts?

The answer shouldn’t surprise anybody at this point: version control!

Side-note:  I really should try using SSMS Solutions/Projects like Kenneth Fisher advocates, but I just haven’t gotten a chance yet.  It would probably help with organization.

Well, perhaps you’re still in for a surprise.  Because, you see, as much as I love using GitHub and Gist for my blog code samples, much of that love is lost in the daily grind of fix-it-tickets, BI building blocks, report development, and performance troubleshooting that comprises my business as usual.  So forgive me if I haven’t made the leap to Git like all the cool kids are doing.  I’m talking about a locally stored (i.e. on my very own hard-drive), locally managed, only-ever-have-one-user, repository of SQL scripts.  All it really needs to do is track changes on a daily (ish) basis.

That’s what good ol’ Apache SVN is for!  Yes, boys & girls, it’s still around.  Consider it one of the many enduring foundational technologies that we tend to take for granted, or forget about, in the ever-present onslaught of the “new and shiny”.

But fortunately for us luddites, there are a couple GUI tools out there that make it feel less like the ’90s green-screen.  Enter VisualSVN Server (free edition) and TortoiseSVN (also free).

Eeww, yuck!  A non-distributed VCS??  What are you, some sort of wild animal!?  The impudence!

the audacity -the grinch
The unmitigated GALL!!

Okay, calm down there sparky.  Remember my requirements?  They’re only about 5 sentences up.  Go back and read them again.  Breathe.  We good?  Good.

Another reason it doesn’t need to be distributed or ‘cloudy’ or web-based is that these scripts often contain private company I.P., so data governance demands that they stay within company IT space.  And sure, there are private repos and ways to keep the stuff locked-down within a GitHub or similar, but again I ask, why?  The first and primary requirement of my VCS is that it stays the heck out of my way when I’m working.  I’m sure someone will counterpoint me, and I’d love to hear it.  But for now, we’re keepin’ it local.

Getting Set Up

The first step, if it’s not obvious, is to install VisualSVN Server — it already contains the binaries for Apache SVN, so you don’t need to worry about that.  It’ll ask you where to stick your repos and your backups; you can see an example of mine here:

visualSVN server config screen
Normally you’d point Repos at a network location, but since we’re setting this up as a local-only server, just pick a drive/directory that’s easy to remember.

Once it’s done, let it start up the manager GUI.  Next step is to create a new repo:

VisualSVN server repo create new
You can also use the “Create new repository…” link located a bit down the intro page.

I called mine “SQL_Scripts”.  Because, as my blog’s tagline says, “why yes, I do like underscores.”  When you go thru the steps, it’s easiest to leave everything at the default settings — that way you don’t need to futz with permissions or anything.

One ‘gotcha’ to note: in a corporate domain setting, you should set the server name to your machine’s FQDN, e.g. MYWORKSTATION.company.com.  This may be done for you when you install, but I’m not 100% sure, so it’s worth checking — you can right-click on the VisualSVN Server (local) node in the left pane and go to Properties, to the Network tab, and verify it.

VisualSVN server properties Network tab
Just to be sure!

Next, install Tortoise SVN, or your favorite SVN client.  I chose Tortoise because it’s easy to use, includes some very useful File Explorer options (right-click menu goodies), and supports standard command-line interaction just like vanilla SVN.  We’re going to use said CLI interaction in just a bit!

Import (or is it Export?)

I almost always have trouble remembering which option is for use with a non-empty folder of “here’s a bunch of files that I want to dump into the repo to start with”, vs. “here’s an empty folder where I want to pull down the contents of an existing repo”.  Fortunately, Tortoise yells at you if you try to do the latter — which is Export — into a non-empty folder.  So we want to Import.  Assuming you have a folder where all your SQL scripts live already, right-clicky and say “Tortoise SVN .. Import.”

TortoiseSVN import dialog
You can use the file:/// notation or the https:// address, either should work.

You can verify that it worked by switching back to your VisualSVN Server for a moment, refreshing it, and seeing that the repo’s contents are now.. your happy files!

But wait… the folder I imported into the repo doesn’t have the pretty little icon overlays showing me that it’s in SVN… Why?

Don’t panic.  We have one more step to go.

Right-clicky again!  On the same folder you imported into SVN.  You now want to “SVN Checkout…”, which will essentially mark all your files as “versioned”, because it will see that they all match exactly what’s already in the repo (because you just imported them a few moments ago).

There’s an important ‘gotcha’ here, if you named your repo something other than the folder name that’s serving as your repository root.  By default, it will try to pull the repo’s contents into a sub-folder of the same name as the repo.  In the example below, that’d be “Workspace\PersonalScripts”, instead of just “Workspace”, which is where I want it to go.  This has happened to me more than once.  Check it out:

SVN checkout with folder names
Notice that the repo name is different than my root (source/destination) folder name — that’s OK, as long as I set it correctly here in this dialog.

Pull the trigger, and you’ll see all your files getting ‘Versioned’, like so.

svn checkout finished
It’s really just comparing the repo contents with the folder contents and saying “OK, got it, we’re now tracking this file for version-control.”

Yay?  Yay.  Now for the cool part, where you can stop thinking about all of this and just let the machines do their work.

Automatic Nightly Check-In

Windows Task Scheduler, specifically.  Let’s be honest, if you’re reading this far, you’re not really a CLI jockey.  And you’re certainly not a Linux geek — you could have had all this set up and done with about 5 lines of bash, I’m sure.  Party on, Wayne.  Us Garth’s still need a bit of help.

I’m not going to do a walk-thru screen-shot montage of Task Scheduler; it’s pretty idiot-proof if you read & understand the dialogs.  Here’s the key bits, the commands that you’ll actually want to enter as the actions.  We have two actions: first “add all new files to the repo”, followed by “commit everything new & changed”.  Schedule it however often you’d like; mine happens nightly at 8pm, when I’m about 99.9% sure I won’t be touching work.  Oh, make sure to set it to “Run whether user is logged on or not”.

The actions are both “Start a program”, and said program will be the SVN executable, which should be (by default) located at C:\Program Files\TortoiseSVN\bin\svn.exe.  Here are the arguments, respectively (1st action, then 2nd), subject to personalization of course!

add --depth infinity --quiet "C:\Users\Documents\Your\Scripts\Folder" --force --username YourUsername

commit --depth infinity --message "daily" "C:\Users\Documents\Your\Scripts\Folder" --force --username YourUsername

Commence Laziness!

And that about does it!  Now, without any need to remember any command-line syntax, or to touch a right-click menu again, your SQL scripts are being silently versioned and committed every night to your very own local repository.  If you ever forget when or what you did last week or last month, or need to “blame” yourself (which is short-hand for “show me all the changes from this part of this file so I can see what led to its current state”)… now you can.

PS: If you’re using a fantastic 3rd party diff/compare tool like I am, be sure to plug it into the TortoiseSVN options for diff-viewing/comparison.  While their built-in interface ain’t too bad, BeyondCompare and other similar tools are even better.

Thanks for reading!