Press "Enter" to skip to content

Day: March 23, 2023

Building a Shiny App to Show Star Maps

Published 2023-03-23 by Kevin Feasel

Benjamin Smith builds a UI:

Recently, I released a R package called starBliss that aimed to replicate the output of a e-commerce site called MapsForMoments – a site which lets users order custom prints of the night sky on the date of their choosing (usually a special occasion such as a birthday, first date, wedding etc.) and allows them to choose a style, and add some custom text. It was a great experience getting to build the package which replicated the MapsForMoments product and I was shocked to see how well it was received when I posted about it- with the Github receiving over 30 stars at the time of writing this blog!

I decided to take this to the next level by trying to build a similar UI in shiny which allows the user to create a custom star map and not need to use the R console. In this blog I share my experience constructing and showcase the “free alternative” to MapsForMoments – starBlissGUI!

Click through to see how you can run the app, as well as a sample output.

Comments closed

Estimating Simulation Variance when Running Stan Models in R

Published 2023-03-23 by Kevin Feasel

Sebastian Sauer takes a look at an interesting question:

stan_glm() allows for setting a seed value thereby eliminating the variance induced by random numbers. However, in case a seed is not used, how much variance is to be expected? This is the research question of this analysis.

Let’s choose n=100 repetitions in our simulation.

Click through for the demonstration, including a summary table and notes on installed packages for the sake of reproducibility.

Comments closed

Checking XML Validity

Published 2023-03-23 by Kevin Feasel

Kevin Wilkie doesn’t like misshapen XML data:

Sometimes you’ll find that you will have XML in your database. This could be for various reasons – from storing the XML after receiving an API response to keeping it in a table because a web developer couldn’t figure out another way to store their data. Sometimes – no matter how much you trust your source – you should question if the XML is well-formed. Let’s work out a few ways you can do that in a database.

Read on for a few tests. The more concerned you are about XML data quality, the more you’d want to push in the direction of having an XSLT defined as well.

Comments closed

Using a Trigger to Auto-Refresh View Metadata

Published 2023-03-23 by Kevin Feasel

Aaron Bertrand keeps metadata in sync:

As much as we tell people to use SCHEMABINDING and avoid SELECT *, there is still a wide range of reasons people do not. A well-documented problem with SELECT * in views, specifically, is that the system caches the metadata about the view from the time the view was created, not when the view is queried. If the underlying table later changes, the view doesn’t reflect the updated schema without refreshing, altering, or recreating the view. Wouldn’t it be great if you could stop worrying about that scenario and have the system automatically keep the metadata in sync?

It’s almost entirely not apropos, but the first thing I thought of when I read the title and Problem statement was Goethe’s line about Mephistopheles: “Oft evil will shall evil mar.” Make of that what you will.

Comments closed

Performing an Azure DevOps Release for a Database Project

Published 2023-03-23 by Kevin Feasel

Olivier Van Steenlandt pushes out the database:

In my previous blog post, Automate your Database Builds – Using Azure DevOps Pipelines, we successfully automated our Build process. As a next step, we will be automating our deployment process to Azure SQL.

In this blog post, we will go through the process step-by-step to set up our Release pipeline in Azure DevOps and execute our first release just by clicking a button.

Olivier has plenty of screenshots to take us through the process.

Comments closed

Azure Data Studio 1.42 Released

Published 2023-03-23 by Kevin Feasel

Erin Stellato shows off what’s new in Azure Data Studio:

I just finished writing up the release notes for Azure Data Studio 1.42, and I cannot get over the amount of work the engineering team has completed since January.  Go big or go home.

For regular users of Azure Data Studio, we hope you are starting to experience the improvements we have been making in the application.  This continues with the 1.42 release, though we also found time to introduce new functionality as well. 

Read on for some of the highlights.

Comments closed

Refreshing a Power BI Dataset via HTTPS URL

Published 2023-03-23 by Kevin Feasel

Gilbert Quevauvilliers presses the big red button:

I have found that sometimes there are other systems that are loading data, and once they are complete they then want to refresh the Power BI Dataset.

Another way to do this is to use Power Automate, in which a system or user can request a HTTPS URL and once called that will then refresh the Power BI dataset.

I explain how to do this in the steps below.

Click through to see how to set up that job.

Comments closed

Analyzing SQL Server Table Metadata

Published 2023-03-23 by Kevin Feasel

Barney Lawrence looks at details about a table:

For a while I’ve been building up a script that uses SQL Servers INFORMACTION_SCHEMA.COLUMNS table to create a query that applies aggregates to each column in a table and then formats the results to allow an at a glance summary of the shape of the data inside of it.

I showed some of the techniques used to build this in a session regarding metadata and data warehousing which I co-presented with Emma Dolling and Ruth Pearson at SQLBits. The one comment that I heard the most after this session was “I want that script”. I did promise to make it available more than a few times and so I’ve put together a github repository to hold it and other resources we built for the session.

Barney anticipated the most likely change I’d make while reviewing the script: APPROX_COUNT_DISTINCT() if you’re on SQL Server 2022 and dealing with a large table.

Comments closed