A Fine Slice Of SQL Server
David Eldersveld shows how you can use orthographic projection in Power BI:
The projection from three coordinates to a 2D plane is achieved by adding the following two measures. Be sure to adjust the column references and what-if parameter names at the top to correspond to your own data.
Here’s my “Ortho x” measure. The initial six bold values are what you’d need to adjust to your own data and parameter names.
David lays out a face, which is pretty neat.
Comments closedMala Mahadevan continues a series on graph tables in SQL Server:
I have highlighted in red what SQL Server adds to the table – the two system columns – graph id, which is bigint, and node id, which is nvarchar and stores json, and the unique index to help with queries.
We can also see from constraint type that this table is similar to other relational tables – it can be enabled for replication and can have related delete or update actions defined on it if need be.
This post gives a bit more insight into how graph tables work in SQL Server under the covers.
Comments closedPaul White digs into batch mode normalization and its consequences for performance:
I mentioned in the introduction that not all eight-byte data types can fit in 64 bits. This fact is important because many columnstore and batch mode performance optimizations only work with data 64 bits in size. Aggregate pushdown is one of those things. There are many more performance features (not all documented) that work best (or at all) only when the data fits in 64 bits.
In our specific example, aggregate pushdown is disabled for a columnstore segment when it contains even one data value that does not fit in 64 bits. SQL Server can determine this from the minimum and maximum value metadata associated with each segment without checking all the data. Each segment is evaluated separately.
Paul goes deep into the concept, making this well worth your while.
Comments closedLet’s assume that you have inherited a database which stores passwords in cleartext in a column called
Password. This is not a good scenario. For one thing, it is a temptation to access information we wouldn’t normally have access to, by using that person’s credentials. For another, due to a human trait called “using a computer,” people reuse passwords all the time. I do it myself and I’m writing a post about security (out of 1,089 items in my 1Password vault, 82 items contain reused passwords).
Randolph has a few ideas, but there’s one which I put into place a long time ago: automatic migration. A system was MD5 (it was a while ago) and I changed it to a better password-hashing algorithm. If the existing user record had a binary the length of an MD5 hash, I’d perform the lookup (to ensure that this was the correct user) and then re-hash using my new algorithm, which had a longer binary length. Over time, people migrated to the new system without any issue. Eventually, when the set of remaining users was small enough, we took more active measures. It wasn’t quite as good as the ideal of forcing resets on everyone, but that was deemed as going overboard for our user base.
Comments closedStefan Franczuk explains the different categories of machine learning algorithms available in Talend:
Clustering is the task of grouping together a set of objects in such a way, that objects in the same group are more similar to each other than to those in other groups. Clustering is really useful for identify separate groups and therefore is used to solve use cases such as “who are my premium customers?”.
Understanding when to use which algorithm is important. You don’t want to build out the world’s best regression if your benefactors are asking for a classifier.
Comments closedMapping Data Flows
One of the many data flows from Microsoft these days providing, for the first time, data transformation capabilities within Data Factory. This is not a U-SQL script or Databricks notebook that is orchestrated from Data Factory, but a tool integrated. This means that you can reuse (many of) the datasets you have defined in Data Factory, while in Databricks you don’t.Mapping Data Flows runs on top of Databricks, but the cluster is handled for you and you don’t have to write any of that Scala code yourself.
Read on for the full comparison.
Comments closedJenny Jiang announces a new capability in Visual Studio Code:
With the Visual Studio Code extension, you can enjoy native Python programming experiences such as linting, debugging support, language service, and so on. You can run current line, run selected lines of code, or run all for your PY file. You can import and export a .ipynb notebook and perform a notebook like query including Run Cell, Run Above, or Run Below. You can also enjoy a notebook like interactive experience that includes your source code and markdown comments along with the running results and output. You can remove the unneeded sections, enter comments, or type additional code in the interactive results window. Moreover, you can visualize your results in a graphic format through a matplotlib like Jupyter Notebook. The integration with SQL Server 2019 Big Data Clusters empowers you to quickly submit a PySpark batch job to the big data cluster and monitor job progress.
This is rather useful for developers, though I greatly prefer the Azure Data Studio notebook interface.
Comments closedHere’s a little tidbit I wanted to share regarding the Perform Volume Maintenance Tasks security setting. In the SQL Server world, this is often referred to as IFI. On more recent versions of SQL (SQL 2012 SP4 or later, I believe), you can verify if IFI is enabled or not for the database engine logon account by checking the error log.
That’s one, but click through for the technique you can easily script out.
Comments closedAllan Hirt makes a disappointing discovery:
This one is going to be a short post. One of the big capabilities of SQL Server 2019 – the ability to deploy AGs using containers – is not shipping when SQL Server 2019 is released.
Read the whole thing, which isn’t much longer than my snippet. But Allan does promise an update for when it does hit.
Comments closedChrissy LeMaire has a Powershell ad blocker:
I really abhor the new ad in the PowerShell 5.1 console and it seems there’s no hope of Microsoft making it go away.
After a long, involved Twitter conversation with the community and the PowerShell team that confirmed it’s impossible for the advertisement (?!) to be easily removed, it looks like the only solution is to bypass it. Przemysław Kłys has a great suggestion to emulate the old prompt that totally works!
Click through for that solution.
Comments closed