Today is New Year’s Day observed, so instead of linking to blog posts, there will be a couple links to academic papers coming up.
Comments closedAuthor: Kevin Feasel
Chicago’s Department of Public Health used the R language to build and deploy the model, and made the code available as an open source project on GitHub. The reasons given are twofold:
An open source approach helps build a foundation for other models attempting to forecast violations at food establishments. The analytic code is written in R, an open source, widely-known programming language for statisticians. There is no need for expensive software licenses to view and run this code.
Read on for more details and check out their GitHub repo.
Comments closedDikesh Jariwala provides sample R code for seven common visualizations:
In your day-to-day activities, you’ll come across the below listed 7 charts most of the time.
- Scatter Plot
- Histogram
- Bar & Stack Bar Chart
- Box Plot
- Area Chart
- Heat Map
- Correlogram
We’ll use ‘Big Mart data’ example as shown below to understand how to create visualizations in R. You can download the full dataset from here.
That’s a nice set of visuals, covering a broad swath of potential visualization scenarios.
Comments closedDev Nambi has a new open-source project:
Databases store text, and the best way to manipulate text is to use a regular expression (‘regex’). Using regular expressions in SQL queries has been possible in many database engines for decades.
Now you can use regular expressions in SQL Server queries, too. I’ve created an open-source project, sql-server-regex, that lets you run regular expressions in T-SQL queries using scalar and table-valued functions.
This is a set of CLR functions which use the built-in .NET regular expressions functionality. That makes it pretty easy to see how the code works.
Comments closedMike Walsh recommends ensuring your servers are not using any form of power saving mode:
Balanced power mode has a major impact on SQL Server performance. Simply put, you’ve told Windows Server (through Control Panel) or your server hardware (through BIOS settings) to sacrifice a few performance-minded things for the sake of using a little less power. In fact, in plenty of studies and blog posts by folks in the community (including this post by Glenn Berry), you can see the effect of CPU power saving, especially. In essence, the CPUs will run at a lower clock multiplier when demand isn’t deemed high enough, and that clock speed will only increase when demand is high enough for long enough. This results in a slower CPU speed during normal operations. That setting actually works well on my laptop or tablet when I want to conserve battery life and don’t have a workload that is sensitive to CPU speed.
For a SQL Server, though? That is horrible for performance. Windows balanced mode also can cause other components to run slower or behave differently than when the server is running in High Performance mode. For instance, USB ports can be underpowered and network interfaces can be under-powered or even go to sleep. Frankly, for a SQL Serve, nothing good comes out of these modes.
Read on for a few methods for checking whether your servers are affected.
Comments closedOne of the features that took me by surprise is the ability to view data directly from a shared dataset. This feature is called Data Preview, and is available to anyone who has permission to view the dataset and the security at the data source works out. I’m not sure how often shared datasets have been used in previous versions of SSRS. They were not actually needed in many cases, and I generally recommended them for datasets that would be frequently reused such as common parameter lists. This advice will have to change with 2016, because shared datasets are required for the new KPI reports and Mobile Reports. Stored credentials will be used in the data sources in many cases, because Kerberos delegation is not supported yet with Mobile Reports.
This is a potential data leakage scenario, so if you have potentially sensitive data sets, you’ll want to read this post.
Comments closedI have a post which uses Polybase to insert into Azure Blob Storage:
One additional question I have involves whether the process for loading data is round-robin on a row-by-row basis. My conjecture is that it is not (particularly given that our first example had 4 files with zero records in them!), but I figured I’d create a new table and test. In this case, I’m using three fixed-width data types and loading 10 million identical records. I chose to use identical record values to make sure that the text length of the columns in this line were exactly the same; the reason is that we’re taking data out of SQL Server (where an int is stored in a 4-byte block) and converting that int to a string (where each numeric value in the int is stored as a one-byte character). I chose 10 million because I now that’s well above the cutoff point for data to go into each of the eight files, so if there’s special logic to handle tiny row counts, I’d get past it.
Read on for the exciting(?) conclusion.
Comments closedThis subscription is going to use an Azure SQL Database.
Go to the AG primary replica. (In this demo, this is SQL2014AG2.)
Expand Replication. Expand Local Publications. Right-click the publication and select New Subscription.
It turns out that this is a basic push subscription. Jes’s post is full of screenshots, making it even easier to follow.
Comments closedKendra Little exposes a gotcha with non-clustered columnstore indexes and computed columns:
Looking at the execution plan, SQL Server decided to scan the non-clustered columnstore index, even though it doesn’t contain the computed column BirthYear! This surprised me, because I have a plain old non-clustered index on BirthYear which covers the query as well. I guess the optimizer is really excited about that nonclustered columnstore.
Kendra links to a Connect item from Niko Neugebauer to add persisted computed columns to columnstore indexes.
Comments closedDevin Knight shows off the horizontal funnel Power BI custom visual:
In this module you will learn how to use the Horizontal Funnel Power BI Custom Visual. The Horizontal Funnel functions somewhat similar to the traditional funnel but it allows you to display a secondary measure and has a few more customizations than you would normally get. You’ll find that the Horizontal Funnel is great for displaying a flow of data.
One of the better non-sales uses of funnels I’ve seen is tracking completion rates on multi-page forms or multi-step processes. If you see a huge drop-off at one step in the process, it might indicate a bug in the form or some incongruity with the end user’s expectation.
Comments closed