Chris Webb shows us a case in which Power BI Desktop’s DAX calculation might not be what you expect:
The difference here is that the Month Number and Month Name fields are both present – they have to be since the query has to sort by Month Number. In MDX the order of members on a hierarchy can be set inside the model; in a DAX query you can only sort using an ORDER BY clause and for that to work, the field you’re ordering by must be present in the query.
This kind of feels like an edge case, but if you run into it, it’s good to know that it’s not a bug.
Revolution Analytics has a series of tutorials using SQL Server R Services:
You may have heard that R and the big-data RevoScaleR package have been integrated with with SQL Server 2016 as SQL Server R Services. If you’ve been wanting to try out R with SQL Server but haven’t been sure where to start, a new MSDN tutorial will take you through all the steps of creating a predictive model: from obtaining data for analysis, to building a statistical model, to creating a stored prodedure to make predictions from the model. To work through the tutorial, you’ll need a suitable Windows server on which to install the SQL Server 2016 Community Technology Preview, and make sure you have SQL Server R Services installed. You’ll also need a separate Windows machine (say a desktop or laptop) where you’ll install Revolution R Open and Revolution R Enterprise. Most of the computations will be happening in SQL Server, though, so this “data science client machine” doesn’t need to be as powerful.
The tutorial is made up of five lessons, which together should take you about 90 minutes to run though. If you run into problems, each lesson includes troubleshooting tips at the end.
SQL Server R Services has the potential to be a great tool. The standard V1 warning obviously applies, but I’m excited.
Andy Mallon on auto-statistics:
I’m not going to say whether I think Auto Update Statistics should be on or off. Instead, I’m going to argue that there are definitely scenarios when you want to have this on AND there are scenarios where you want it turned off. Can you really have this both ways? Absolutely.
I’m going to say yes to auto-update unless you know the answer is no for an object. But it’s nice to know that the fine-grained option is available.
Rob Collie shows how to use QQube to help with Quickbooks data analysis:
Our financials are the logical first place to start. And our financials are in the hands of our accounting firm. Specifically, they are stored in Quickbooks.
This, of course, poses a problem. Because like ALL accounting and ERP systems, Quickbooks is primarily focused on being a great accounting system. A system that collects, stores, organizes, and routes data. Quickbooks is NOT an analytics tool.
And being an analytics (or BI or reporting, whatever you call it) tool is a full-time job. ANY system whose job it is to collect/organize/route data will NEVER be sufficient for reporting and analysis. NEVER. I’m not kidding. We should never expect different, and that’s not a “knock” on these vendors. It’s just too many missions for any one company to execute.
This is a nice walkthrough of how you can apply visualization and analytics concepts, especially in a small business scenario.
Chris Bell uses Powershell to alert us when the SQL Agent service goes down:
Now that we have our PS1 file that will send the email alert, but we need to have the job run when the service fails. To set this open up services and right click on the SQLServerAgent service you wish to add the failure alert to and select properties. Navigate to the Recovery tab and set one of the failure actions to “Run a Program” I tend to choose it as the first failure action as I like to look at why my service failed before I just restart it. You can attempt to restart first and then run the program on if it fails again by setting the second failure to run a program. The choice is yours!
Very cool.
Richie Lee has a long blog post on log shipping:
Despite the development of AlwaysOn in recent releases of SQL Server, log shipping is still a great way to set up a copy of databases to be used for reporting. One of the main reasons it is great is because, unlike AlwaysOn, it is available in less expensive editions like Standard and Web from SQL Server 2008 onwards. Sure, in 2016 AlwaysOn will be available in Standard, but in a greatly deprecated form, and you cannot read from the secondary. So it will be good for DR, but not for reporting (as an aside it still might be easier to set up log shipping for DR than AlwaysOn Basic because you need to setup a failover cluster. Read through the “how to set up Standard Edition Availability Groups” here.) However you do need to be careful though when setting up log shipping across different editions of SQL Server: whilst you can log ship between Enterprise to Standard/Web, if the database uses any Enterprise features then you’ll need to log ship to an Enterprise edition of SQL Server. And because you’ll be using the database for reporting, you’ll need to get it licensed.
Log shipping is a venerable disaster recovery technique and it behooves database administrators to know of its existence.
Ginger Grant shows a couple of techniques for sampling from tables:
The random sample that TABLESAMPLE provides is based on the number of data pages, not the number of records. If you want the number of rows to be specifically limited you will need to use Top(n) as well. I’ve written all my samples based upon AdventureWorksDW so you can run them for yourself later. I’ve listed the variety of ways to call TABLESAMPLE and shown the number of records returned.
TABLESAMPLE is useful for spelunking, but is somewhat limited otherwise.
Corey Beck on data compression:
Before we jump right into enabling either row or page compression, we can actually estimate the savings of each to determine which will provide us with the most savings on storage. Since page compression includes row compression, we will start with row compression and the estimated savings.
EXEC sp_estimate_data_compression_savings
‘Person’,’Person’,null,null,’row’
In practice, data compression is extremely valuable and in most circumstances, the benefits outweigh the costs. In certain workloads, you might even see CPU usage go down.
Gail Shaw finishes her outstanding series on transactions:
The error was thrown by the ROLLBACK statement. As such, the transaction is still open, the locks are held and the transaction log space can’t be reused. Unless the application that called this was checking for open transactions, that transaction could potentially be left open for quite some amount of time, causing blocking and/or the transaction log to grow.
It’s not just that someone in the future might call the code from another stored proc within a transaction, it’s also that it might be that the code is called from an application which started a transaction. Or called from SSIS which started a transaction. It’s very hard to ensure that code is never called from within an existing transaction
Read the whole thing.
Ed Elliott has another update to SSDTDevPak:
How do I get it?
i have now pushed it to the visual studio gallery and will use this for all future updates so feel free to grab it from:
https://visualstudiogallery.msdn.microsoft.com/435e7238-0e64-4667-8980-5b8a05dc7906.
Also check out T-SQL Clippy.
Kevin Feasel
2015-12-18
DAX