A Fine Slice Of SQL Server
In SQL Server 2016, you can now enable the very same optimizer hotfixes controlled by Trace Flag 4199 at the database scope by using ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON.
If you have the setting configured at the database level, it’s much easier to test what would happen if the setting was NOT enabled, because you can compile your query from a different database.
Interesting results. Check it out.
Comments closedThe SQL Server team announces graph extensions in SQL Server 2017:
Graph extensions are fully integrated in the SQL Server engine. Node and edge tables are just new types of tables in the database. The same storage engine, metadata, query processor, etc., is used to store and query graph data. All security and compliance features are also supported. Other cutting-edge technologies like columnstore, ML using R Services, HA, and more can also be combined with graph capabilities to achieve more. Since graphs are fully integrated in the engine, users can query across their relational and graph data in a single system.
This is interesting. One concern I have had with graph databases is that graphs are storing the same information as relations but in a manner which requires two distinct constructs (nodes and edges) versus one (relations). This seems to be a hybrid approach, where the data is stored as a single construct (relations) but additional syntax elements allow you to query the data in a more graph-friendly manner. I have to wonder how it will perform in a production scenario compared to Neo4j or Giraph.
Comments closedSteve Jones has a post on upgrading SQL Server on Linux:
I’m cutting off part of the path, since I think it’s probably NDA. No worries, apparently the old location for me hasn’t been updated with new packages, which makes sense.
I decided to check the MS docs and see how a new user would get SSoL running? At the new docs.microsoft site, I found the Install SQL Server on Ubuntu doc.
Following the instructions, I updated the GPG keys and registered the repository with curl:
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list
My expectation is that upgrading SQL Server on Linux is going to be a lot less painful than upgrading on Windows.
Comments closedShane O’Neill compares table columns with T-SQL and Powershell:
That is not really going to work out for us…
So I’m not liking the look of this, and going through the results, it seems to me that these results are just not useful. This isn’t the computers fault – it’s done exactly what I’ve told it to do – but a more useful result would be a list of columns and then either a simple ‘Yes’, or a ‘No’.There’s syntax for this…
PIVOT…
This is helpful for normalizing a bunch of wide, related tables into a subclass/superclass pattern.
Comments closedSteph Locke has a presentation on performing logistic regression using R:
Logistic regressions are a great tool for predicting outcomes that are categorical. They use a transformation function based on probability to perform a linear regression. This makes them easy to interpret and implement in other systems.
Logistic regressions can be used to perform a classification for things like determining whether someone needs to go for a biopsy. They can also be used for a more nuanced view by using the probabilities of an outcome for thinks like prioritising interventions based on likelihood to default on a loan.
It’s a good introduction to an important statistical method.
Comments closedI was trying to come up with a demo for something totally different. Don’t ask. Seriously. It’s top secret.
Okay, so it’s just embarrassing.
Anyway. I had these two queries. Which are actually the same query twice. The only difference is the table variable definition.
Click through for the demo and additional information.
Comments closedCallum Green notes the trade-offs between using a Header versus a Title in a Power BI slicer visual:
o Header can have an Outline, which includes the ability to underline text.
o Header is constrained to displaying the name of the attribute (“Product Category Name”), whereas a Title can be customised (“Select Category”). You can rename your source data attribute to get around this, however.
o Title enables you align the text, but this is not possible with a Header.
o Header contains the “Clear Selection” option.
It’s not usually great to have both, but there are definitely trade-offs.
Comments closedPatrick Keisler shows how to use Blob Auditing with Azure SQL Database to log database activity:
If you have multiple objects or actions to audit, then just separate them with a comma, just like the AuditActionGroups parameter. The one key piece to remember is you must specify all audit actions and action groups together with each execution of Set-AzureRmSqlDatabaseAuditingPolicy. There is no add or remove audit item. This means if you have 24 actions to audit and you need to add one more, then you have to specify all 25 in the same command.
Now let’s run a few queries to test the audit. First, we’ll run a simple select from the Salaries table.
Patrick shows off the UI (which is nice for one-off checking) and also the function sys.fn_get_audit_file(), which you’d probably want to use for automated audit checks.
Comments closedIn this video, Patrick answers your question about how to do this in Analysis Services Tabular and Multidimensional. Also, he adds a little bit of SQL to the mix.
Make sure to watch the previous dynamic filtering videos to understand the basics of how to do this.
To begin, you need to make sure to get the URL for your published report.
I completely agree with Patrick about doing as much as you can in the source, especially if there will be more than one potential consumer aside from Analysis Services.
Comments closed