A Fine Slice Of SQL Server
Senthil Thyagarajan gives us an example of uisng the htmltools package to change the way tables render in Shiny:
In order to build the html table I have used a function
table_framewhich can be used as a container inDT::renderdatatable. This function basically useshtmltools. For more references on the basics of html tables please refer here
In addition to changing the colors, Senthil also shows how to add a couple of buttons which call Javascript functions. H/T R-bloggers
Comments closedWhat is Results to Grid and what can it do for you? Results to Grid are Query Results options in SQL Server Management Studio (SSMS) that can help users customize their query results in a variety of ways that can help make users more efficient. Some of these might be little changes, but when used often throughout the day, they can make a big difference. Once you change the setting, you will need to open a new query window for the change to go into effect
The two I wish were on by default are column headers when copying or saving results, and retaining CR/LF on copy or save.
Comments closedDavid Eldersveld shows off how you can customize maps in the Power BI Report Builder Map Gallery:
One of the defining features of Power BI’s [paginated] Report Builder vs the current geospatial offerings in Power BI Desktop is the native support for ESRI Shapefiles. If you have worked with maps in Report Builder over the years, you may have used the Map Gallery. The Map Gallery offers a collection of built-in geographies, but you can also customize and enhance what’s available out of the box.
David takes us through an example of simplifying one map, but the same technique can help in other ways.
Comments closedHugo Kornelis has a pattern matching problem to solve:
The actual use case and the list of patterns that I had to remove are considered a confidential competitive advantage by my client, so I will just make up a list here to illustrate the problem. In this fake requirement, the following patterns must all be removed if anywhere in the string: “-@@%”, “@@%”, “-@%”, “@%”, “No.X#X”, and “^^^”. Also, “@X” needs to be removed when at the end of the string. In these patterns, @ is a placeholder for a numeric symbol, X for an alphabetic symbol, and # for an alphabetic or numeric symbol. All other symbols are to be taken literally. (So “%” is the percent mark, not the
LIKEpattern for any character!).
This is a problem for regular expressions, but without built-in regular expressions (and I’d guess no desire to use the CLR), Hugo gives us a workable solution.
Comments closedJohn McCormack shows two ways to connect to an Availablity Group listener with read-only intent:
SQLCMD
The -Kreadonly switch is your key to success here but remember to also specify the database using -d. When not set (and with an initial catalog of master for my login), I found I always got the primary instance back during my check. This simple omission cost me hours of troubleshooting work, because I was convinced my listener wasn’t working correctly. In fact, I just wasn’t testing it correctly.
There’s some good information in here for sqlcmd and for SQL Server Management Studio.
Comments closedDave Bland shows off the Azure SQL Database query editor built into the Azure portal:
We have all been using SQL Server Management Studio to query and manipulate data, even for an Azure SQL database. There is also an option to do this same thing built into the SQL Azure database interface in the Azure portal. Although there have been a number of posts related to this topic dating back a few years, this feature is still marked as “preview” in the Azure portal.
Click through to see how it works, what you can do with it, and some of its limitations.
Comments closedAndy Leonard shares one technique for reusing a data set in SSIS:
A work table is a table defined in a nearby data location; either a schema in the source or target database or in a database on the same instance. I take a constraint-driven approach to work table location selection. Closer – a schema in the same database – is often better for performance.
I write this knowing some folks will frown at the suggestion of polluting a data source or target database with additional schemas and tables. Best practices exist for a reason. It’s helpful to maintain a list of best practices and to include in this list the reasons each practice exists. This could be a case where violating one or more best practices is justified.
Andy throws out a few ideas as alternatives but states his preference for using work tables to solve this problem.
Comments closedI continue my series on forensic accounting techniques with cohort analysis:
In the last post, we focused on high-level aggregates to gain a basic understanding of our data. We saw some suspicious results but couldn’t say much more than “This looks weird” due to our level of aggregation. In this post, I want to dig into data at a lower level of detail. My working conception is the cohort, a broad-based comparison of data sliced by some business-relevant or analysis-relevant component.
Those familiar with Kimball-style data warehousing already understand where I’m going with this. In the basic analysis, we essentially look at fact data with a little bit of disaggregation, such as looking at data by year. In this analysis, we introduce dimensions (sort of) and slice our data by dimensions.
Click through for some fraud-finding fun.
Comments closedJamie Wick shows us how we can back up database directly to Azure Blob Storage:
Azure storage, as a backup destination for SQL backups, is a great option for organizations that are contemplating replacing older on-prem NAS appliances or improve their Disaster Recovery functionality. The tiered storage pricing, along with local and global redundancy options, can be much more cost-effective than many traditional backup options.
In this post, we’re going to look at some of the key concepts and restrictions, along with how to back up an SQL database to an Azure storage location.
Click through for the demo.
Comments closedStephanie Evergreen explains what denodgrams are and when they can be useful visuals:
Dendrograms are not THE most common qualitative visual because they require a data generated through a hierarchical cluster analysis. Cluster analysis can be a useful tool in analyzing qualitative data. By clustering groups of participants with similar qualitative codes, you can better understand your findings. According to Henry & team, this analysis can help “reveal things like participant motive and the reasons behind counterintuitive findings.”
Check out Henry’s article to learn more about the analysis. Here, let’s just focus on describing a dendrogram that could display those hierarchical cluster analysis findings. They can be a little confusing at first, especially since the x-axis has 100% closest to the y-axis when we aren’t used to seeing it that way. Walk through this example with us.
Click through for an example. If it’s confusing at first, read to the end, as I think the concrete example helps everything click.
Comments closed