The latest addition to my recent series of DAX based games is the classic Minesweeper game. This is the game where you are presented with a 9 x 9 matrix of squares. There are 10 hidden mines and you can either step on a square or place a flag where you think there might be a mine. If you are lucky enough not to step on a square that contains a mine, you will get clues that help you identify where the mines are.
Click here if you would like to see the final publish to web version.
Click here if you would like to download the PBIX version to go through the code.
Still better than the Windows 10 version.
In my recent post, Installing External Modules into SQL Server’s Python I had a look at just how simple it is to import external modules into Python so that they can be used within SQL Server.
In this post I’d like to show you a little something to demonstrate how we can integrate one of these modules into SQL Server and just how powerful this can be.
This is really just for fun and it may not really be something that you’d want to put out into production but when I happened to notice that there’s a Python module that interfaces with Google Translate, I wondered to myself if it’d be possible to write a procedure that could take a string and automatically translate it into our native language.
There are a couple of setup steps but once you get past them, it’s easy going.
While writing a sample random function in using T-SQL Server, I have remembered, why not write a job title generator for T-SQL domain only. You might have seen so called bulls**t job title generator and similar, but this one is T-SQL SQL server specific.
So, why not come up with random, yet funny T-SQL job titles. And making it, I have to tell you, it was fun. And I was simply hitting that F5 button in SSMS, to get new job title generated and laugh out loud.
It took me a few clicks, but I got “Qualitative R ggplot library Stackover subscriber,” which might be hitting the mark a little close.
Democratized artificial intelligence is one of five trends driving Gartner’s latest Hype Cycle for Emerging Technologies, which it updates this month, as it traditionally does during the month of August.
The analyst group took a slightly different approach with this year’s Hype Cycle for Emerging Tech and grouped the 35 individual technologies into major groups, which includes digitalized ecosystems, do-it-yourself biohacking, transparently immersive experiences, and ubiquitous infrastructure, along with democratized AI.
The five trends will “blur the lines between human and machine,” Gartner says. “CIOs and technology leaders should always be scanning the market along with assessing and piloting emerging technologies to identify new business opportunities with high impact potential and strategic relevance for their business,” writes Mike J. Walker, research vice president at Gartner.
It’s an interesting exercise and parlor game.
We could give it some smarts. For example, we know that in our game, we can only choose X or O, so we could put a data constraint on the
Playcolumn. And we know that in our game, you can’t play on the same space more than once, so we could put a unique constraint on the combination of
Position. You could even be really creative and enforce our game’s alternating player moves by putting a data constraint on the
Playercolumn such that it equaled 1 when
(Turn Modulo 2)equaled 1 and 2 otherwise. (Really it wouldn’t need to be a data column at that point, just a calculated column.)
But imposing those restrictions robs our data structure from its raison d’être. It’s no longer a general purpose game play storage system; it only works for our game.
Instead of saddling the data storage itself with all of those rules, we could enforce all of the game mechanics through our data interpretation and manipulation logic. When we saved a game move, we could make sure that an X or O was played and it could check to see whether the specified square was already used. When we analyzed a game to determine a win, all of the criteria could be housed in that consuming query. But this flexible design isn’t done inflicting its complexity on us.
Riley covers a number of T-SQL features in the process of this post.
It turns out there’s a concept called bitmasking which can work a lot like this cardboard cut-out process. (Props to Dylan Beattie for his quick visual demonstration at NDC Minnesota which drove this point home.) First, you represent your game state with a bunch of bits (“OXOOOXXXX” yields “0100011110” for our example above, remembering that we’re padding that last 0 just to make the powers 1-based instead of 0-based) and then you represent your winning state with a bunch of bits (“0000001110” for our example winning state here). Now you use the magic of “bitwise math” to compare the two.
For our use, we want to find out whether our mask exposes the winning three bits. We want to block everything else out. With bits, to check if both items are true, you use “AND” (0 and 0 is 0; 0 and 1 is 0; 1 and 1 is 1). If we apply that “AND” concept to each bit in our game, it will squash out any values which don’t match. If what we have left matches the mask (fills in all of the space we can see through), then we have a match and a win.
The twist in all of this is that the end result doesn’t quite work as expected, but it was interesting watching the process. That said, there’s a good reason why we don’t use T-SQL as a primary language for development…
Luckily, this syntax also happens to be SQL syntax, so we’re almost done. So, let’s try plotting this formula for the area of
x BETWEEN 0 AND 105and
y BETWEEN k AND k + 16, where k is just some random large number, let’s say96093937991895888497167296212785275471500433966012930665 15055192717028023952664246896428421743507181212671537827 70623355993237280874144307891325963941337723487857735749 82392662971551717371699516523289053822161240323885586618 40132355851360488286933379024914542292886670810961844960 91705183454067827731551705405381627380967602565625016981 48208341878316384911559022561000365235137034387446184837 87372381982248498634650331594100549747005931383392264972 49461751545728366702369745461014655997933798537483143786 841806593422227898388722980000748404719
Unfortunately, most SQL databases cannot handle such large numbers without any additional libraries, except for the awesome PostgreSQL, whose decimal / numeric types can handle up to 131072 digits before the decimal point and up to 16383 digits after the decimal point.
Yet again, unfortunately, even PostgreSQL by default can’t handle such precisions / scales, so we’re using a trick to expand the precision beyond what’s available by default.
Check it out.