Press "Enter" to skip to content

Curated SQL Posts

Building a Google Analytics Dashboard using RShiny

Published 2022-08-23 by Kevin Feasel

Pascal Schmidt builds a Shiny dashboard:

I participated in the R Shiny 2021 contest and published an application similar to the Google Analytics dashboard app. For that, I used the Google Analytics API and the Google Search Console API to pull my own data from my blog directly into the application.

The application uses the shinyauthr library because the dashboard can be adjusted for each user who has their own username and password. On the first page, there are some visualizations for page views, devices used, etc. On the second page, there is a time-series model that tries to predict my page views two months in advance.

In particular, this post covers a couple of the foundational pieces, with future posts getting into more detail on other components. H/T R-Bloggers.

Comments closed

Choosing between Neural Network Types

Published 2022-08-23 by Kevin Feasel

Jason Brownlee takes us through three common classes of neural network and explains when each is useful:

In this post, you will discover the suggested use for the three main classes of artificial neural networks.

After reading this post, you will know:

– Which types of neural networks to focus on when working on a predictive modeling problem.

– When to use, not use, and possible try using an MLP, CNN, and RNN on a project.

– To consider the use of hybrid models and to have a clear idea of your project goals before selecting a model.

Read the whole thing.

Comments closed

Azure Synapse Link for SQL Server 2022 and File Analysis

Published 2022-08-23 by Kevin Feasel

Kevin Chant digs into Azure Synapse Link for SQL Server 2022:

In this post I want to cover some file tests for Azure Synapse Link for SQL Server 2022 that I performed.

Because a while back I spotted something interesting whilst I was doing some initial tests for Azure Synapse Link for SQL Server 2022.

Which is when you add new data after the initial load that a new folder called ‘ChangeData’ appears in the storage account container. I noticed that the new file containing the insert was a comma separated value (csv) file. Whereas the table used for the initial load was a parquet file.

Is there a method to this madness? Click through to see Kevin’s tell-all story.

Comments closed

sp_prepare and Parameter Sensitive Plan Optimization

Published 2022-08-23 by Kevin Feasel

Erik Darling is a bit surprised:

I admit that sp_prepare is an odd bird, and thankfully one that isn’t used a ton. I still run into applications that are unfortunate enough to have been written by people who hate bloggers and continue to use it, though, so here goes.

When you use sp_prepare, parameterized queries behave differently from normal: the parameters don’t get histogram cardinality estimates, they get density vector cardinality estimates.

That part’s not the surprise. You’ll have to click through for that.

Comments closed

Data-Driven Report Subscriptions with Power Automate

Published 2022-08-23 by Kevin Feasel

Dan English puts together a workflow:

Being able to do a data driven report subscription with Power BI and Paginated reports is a common request we hear from many customers. Let’s say you want to send a PDF version of a report to each of your store or department managers using a set of parameter values specific to each person. In the Power BI service that is not an option, but using Power Automate you can do this.

In this post I will be using a Paginated report that is referencing data in an Azure Analysis Services database and I will be referencing an Excel file that I have in OneDrive for Business which includes the needed information for the data driven subscription with 2000 records. The Paginated report is in a workspace backed by a Power BI Embedded A-SKU (A4 – equivalent of a P1 SKU) for testing purposes and the AAS tier is an S1 (100 QPU).

Read on to see how.

Comments closed

DAX EvaluateAndLog()

Published 2022-08-23 by Kevin Feasel

Chris Webb is excited:

I think this is the most exciting thing to happen in DAX since the introduction of variables. Why? Well we all know that writing DAX can be hard, and part of why it’s hard is that it’s hard to debug.

Read on to see how EvaluateAndLog() makes life easier, as well as a couple links to Jeffrey Wang’s blog for further detail.

Comments closed

tapply() and Ragged Arrays in R

Published 2022-08-22 by Kevin Feasel

Benjamin Smith explains how tapply() works:

While I saw other programmers use this function, I found myself unsure how of how it worked or knew when I would need to use it. In this blog I attempt to change that and explain the cryptic description by showing some applications with my commentary and how it compares to using the “tidy” approach with tidyverse.

My inspiration for writing this blog was from seeing Dr. Norm Matloff’s blog where he mentions the use of tapply() and his thoughts on the tidyverse. For a more thorough treatment on his critique of the tidyverse and “tidy” methods, check out his formal essay here.

Read on to learn the benefit of learning and using tapply().

Comments closed

Querystrings and R Shiny

Published 2022-08-22 by Kevin Feasel

Thomas Williams passes along querystring data:

As background, a query string is part of a web page address. Query strings are used to pass information to web pages, in name/value pairs separated by an equals sign – for instance, user=Andrew or country=au. Name/value pairs are themselves separated by ampersands, so passing multiple values looks like user=Andrew&country=au.

Click through for an example of how it all works.

Comments closed

Case-Sensitive String Comparisons and Case-Insensitive Tables

Published 2022-08-22 by Kevin Feasel

Meagan Longoria reminds us that case sensitivity was a huge mistake:

Here’s the scenario: You are using Python, perhaps in Azure Databricks, to manipulate data before inserting it into a SQL Database. Your source data is a flattened data extract and you need to create a unique list of values for an entity found in the data. For example, you have a dataset containing sales for the last month and you want a list of the unique products that have been sold. Then you insert the unique product values into a SQL table with a unique constraint, but you encounter issues on the insert related to unique values.

Click through for an example and how to extricate yourself from this scenario. Python certainly is not the only language to do this, so it’s good to know even if you don’t plan on using or supporting Python.

Comments closed

OPENJSON Performance and Schemas

Published 2022-08-22 by Kevin Feasel

Dave Mason has a new blog theme and a post on OPENJSON performance:

Support for JSON data has been around in SQL Server for a while now, starting with SQL 2016. The OPENJSON rowset function is the built-in function that allows you to natively convert JSON text into a set of rows and columns. There are two options for using OPENJSON: with the default schema or with an explicit schema. There are performance implications for each, which I’ll review with some examples.

Dave has some nice tips for people working with JSON data in SQL Server.

Comments closed