A Fine Slice Of SQL Server
Categorical data works well with Decision Trees, while continuous data work well with Logistic Regression.
If your data is categorical, then Logistic Regression cannot handle pure categorical data (string format). Rather, you need to convert it into numerical data.
Each algorithm has its own uses and assumptions.
Comments closedAndy Kirk has a few tips to help you design for people who have deuteronopia or protanopia:
Many visualisations use colours to represent data values, either to show quantitative scales or categorical classifications. One of the most common colour metaphors used in visual displays involves the use of a red-green colour scheme, sometimes known as “RAG” or “traffic light” colours. These colours are used to convey notions of green = ‘good’ or ‘above average’ and red = ‘bad’ or ‘below average’ in some cultures, and the reverse in others. Such colour connotations are long-established and widely used, especially in financial or corporate contexts, but whilst they provide a certain immediacy in their meaning for many viewers, around 4.5% of the population are colour-blind (8% of men) with the red-green colour deficiency “Deuteranopia” being the most common form. This means a significant proportion of viewers may not be able to perceive important such visual encodings.
I’m not the biggest fan of some of them, but there are some really good ideas in here.
Comments closedJohn McCormack wants to store a single space in a CHAR(1) column:
I was asked by a colleague why his where clause wasn’t being selective when filtering on a space value. The column was a char(1) data type. To understand the curious case of the space in char(1), we need to understand how the char data type works and also a bit more about the need for it in this scenario.
The ANSI standard makes sense, but it is something you have to keep in mind in cases like this.
Comments closedErik Darling looks at how available indexes may contribute to parameter sniffing problems:
When you’re troubleshooting parameter sniffing, the plans might not be totally different.
Sometimes a subtle change of index usage can really throw gas on things.
It’s also a good example of how Key Lookups aren’t always a huge problem.
Both plans had them, just in different places.
The plans had a small change, but that made a big difference.
Comments closedPamela Mooney has a script to validate that transactional replication is up to date:
You may sometimes have reports or other processes that are dependent on transactional replication being current. If that is the case, you will probably need a mechanism to check and see if, in fact, replication is caught up. Here is my solution to that, without having to resort to Replication Monitor all the time. The bonus? This could be inserted into conditional workflows to help streamline processes (i.e., validate publications before moving on to Step 2 of process).
To do this, I chose to make three stored procedures. The first one to just check all publications on a server, one to check just one publication on a server, and one central sproc to rule them all. You simply execute the master stored procedure, and based on the parameters you feed, it decides which of the other two to execute.
Read on for those scripts.
Comments closedChris Webb shows us how to speed up a query to get the maximum value in a column:
In part 1 of this series – which I strongly recommend you read before reading this post – I showed how removing columns from a table can make a dramatic improvement to the performance of certain transformations in Power Query. In this post I’ll show some tricks taught to me by Curt Hagenlocher of the dev team that can improve performance even more.
Click through for the trick and an explanation of when it works and when it doesn’t.
Comments closedTim Weigel covers the basics of change tracking in SQL Server:
There aren’t a lot of parameters here. You can set change tracking on or off, you can specify your retention period, and you can specify whether to enable auto-cleanup or not.
For the retention period, you have the choice of DAYS, HOURS, or MINUTES. If you don’t specify retention, SQL Server will configure change tracking with a retention period of 2 days. If you provide a number but don’t specify the interval, SQL Server will default to days. The minimum retention period is 1 minute.
This post specifically covers enabling change tracking at the database level.
Comments closedNick Craver has a long post on how Stack Overflow does application caching:
For everyone who hates caching, this is the section for you! Yes, I’m totally playing both sides.
Given the above and how drastic the wins are, why wouldn’t we cache something? Well, because every single decision has trade-offs. Every. Single. One. It could be as simple as time spent or opportunity cost, but there’s still a trade-off.
This is a long but very useful post.
Comments closedHasan Savran shows how to query Cosmos DB documents with SQL:
CosmosDB’s SQL API uses T-SQL like language to query the database. It looks like T-SQL language. You still need to use SELECT, FROM, WHERE clause to retrieve the data you are looking for. It looks similar but it works different because the data structure is different. CosmosDB is not a relational database that means you cannot join other containers.
Let’s start with SELECT. You can use * to select all properties but just like T-SQL you shouldn’t, pick only what you need. Charge of a Request Units depends on how much data your queries retrieves from containers. ORMs like to select everything and that’s one of the reasons DBAs don’t like them. I know couple of ORM supports CosmosDB. Think twice if you want to use an ORM with CosmosDB. Don’t let an ORM to generate a query for CosmosDB. You will literally pay for it!
Feasel’s Law in force, with a bonus of “ORMs are expensive.”
Comments closedMarc Lelijveld continues a series on storytelling with Power BI:
Another thing to think about before building visuals, is the click path. In order to make sure we’re really telling the story, we need to get our click path in a logical order. This is most important for both, declarative and exploratory storytelling.
Maybe you want to provide all the information in your visuals right away. Sometimes I see things like this happening when people add a lot of visuals on only one page. In my experience it is better to make your report interactive and work with multiple pages. Spread the story you want to tell out over multiple pages. This is what Will Thompson also has done as well in this report for the Gartner Bakeoff in 2018.
This sort of planning helps you out in the long run.
Comments closed