Press "Enter" to skip to content

Day: April 14, 2023

Styling Excel Tables in R

Published 2023-04-14 by Kevin Feasel

Steven Sanderson wants to spice things up:

The styledtable package in R, which allows users to create styled tables in R Markdown documents. The package can help to create tables with various formatting options such as bold text, colored cells, and borders. It also has functionality on how to port these to Excel itself.

The package offers a simple syntax that allows users to specify formatting options using HTML and CSS. The resulting table can be customized by changing the CSS file or by using the ‘styler’ function to apply custom styles to individual cells or rows.

Read on for more information on what the package does and a few examples of how it works.

Comments closed

Comparing Data Visualization in Excel and R

Published 2023-04-14 by Kevin Feasel

Amieroh Abrahams builds some graphs:

In Excel it is challenging to eye-ball which changes have been made to a graph, especially if these were minor changes. With R (and some easy to use version control systems), you can see exactly which files were changed. Also, in Excel, a user would usually draw a graph on a single Excel document, and if the same graph is required on a different data set, it is common to copy-and-paste a bunch of manipulations and configurations to another document. Such repeated human interaction is prone to introducing errors, as well as consuming a large amount of time. With R we can avoid this by creating functions, which can be used to run the same code on different data sets simply by changing the input, thereby producing reliable outputs and saving us a lot of time.

Click through for the article. One big thing in Excel’s defense that I did not see here was that it’s a lot easier to perform specific story-telling in Excel visuals. For example, highlight just these two data points, or annotate this segment of the visual. You can do those things in ggplot2 but it’s considerably more difficult than “right-click the data point and format.”

Comments closed

Monitoring Client Timeouts via Extended Event

Published 2023-04-14 by Kevin Feasel

Erik Darling wait until the phone stops ringing and checks caller ID to see who bothered him:

Most applications have a grace period that they’ll let queries run for before they time out. One thing that I notice people really hate is when that happens, because sometimes the effects are pretty rough.

You might have to roll back some long running modification.

Even if you have Accelerated Database Recovery enabled so that the back roll is instant, you may have have 10-30 seconds of blocking.

Or just like, unhappy users because they can’t get access to the information they want.

Monitoring for those timeouts is pretty straight forward with Extended Events.

Click through for a query Erik uses for the task.

Comments closed

Protecting a Website from Cross-Site Request Forgery Attacks

Published 2023-04-14 by Kevin Feasel

Aneesh Lal Gopalakrishnan stops an attack:

ASP.NET MVC and ASP.NET Core are traditionally some of the most used platforms to build financial web applications, such as banks and hedge funds. From a statistical standpoint, these platforms are trusted more than their counterparts, such as Express or NodeJS, for financial web applications. In addition, it is easier to fix CSRF issues in ASP.NET Core than in ASP.NET MVC because of the better tools and support available. We will investigate techniques to fix CSRF issues in ASP.NET MVC.

About 10-15 years ago, CSRF was one of the top threats (in 2007 and 2010, it was #5; in 2013, it was #8), but then it dropped off the list. The reason is that, basically every platform in existence put in anti-CSRF tokens automatically, so you rarely see it work anymore except for really old websites.

Comments closed

Key Components to Power BI

Published 2023-04-14 by Kevin Feasel

Reza Rad provides an overview of what Power BI includes:

Power BI is a cloud-based technology from Microsoft for reporting and data analysis. This reporting technology is built in a way that is not only useful for developers to create reports but also for power users and business analysts. Power BI created a simple, easy-to-use, and user-friendly environment for creating reports. And on the other hand, it is based on several powerful components which help create reports and data analysis for complex scenarios.

Every component of Power BI is responsible for a specific part of the technology. There are components for building reports, connecting to data sources, doing analytics calculations, sharing reports, etc. The following sections explain what each component is. Some of these components are explained in detail in other articles.

Click through for a long list and a series of high-level summaries.

Comments closed

A Thought on Query Granularity

Published 2023-04-14 by Kevin Feasel

Chris Johnson shares some thoughts:

This query is pretty simple, we’re wanting to return a set of data about the orders placed including the total cost of each order. However the orders in the database are split into an Orders table that holds the order level information, and an OrderLines table that holds information on each product ordered in each order, and we need that OrderLines table to get the cost.

So we write our query, and we join to the OrderLines table, and we sum the cost. But then, because we have an aggregate, we need to group by every other column we’re returning. And to me that just doesn’t look right, and it doesn’t convey the intention of the query properly.

In Chris’s simple example, I’m not sure I’d push it very much, but Chris does have a good point in terms of explaining query intent. Also, depending on how many order lines there are relative to orders (the next step in the chain for that query), aggregation in a common table expression could be faster than waiting until after the join to aggregate on all of the columns. In reality, that’s the most likely reason I’d make this change, assuming that it made a big enough performance difference. But if you take a much more complicated query of this sort, then I’d be more amenable to the argument.

Comments closed

Getting Last-Used Report Parameters for SSRS

Published 2023-04-14 by Kevin Feasel

Brian Bønk shares some history:

A couple of years back I needed to find the latest used parameters from a reporting services – SSRS (yes, it is old!) report. The functionality was to find the latest used parameters for all parameters dynamically and use them as the default values for each parameter in the report, when tjhe user opened the report. If the uesr had not used the reprot before, it should ask for the parameters.

This was a alot of fun for me to do, as I needed to query the internal tables from the reporting services database. The tables ExecutionLogStorage and the Catalog was on play. This due to the fact that I needed the active user’s latest used parameter values from each specific report and this in runtime when the report was opened.

Click through to see how Brian did it.

Comments closed