Press "Enter" to skip to content

Day: October 22, 2019

Using Schemas with DBI and SQL Server

Published 2019-10-22 by Kevin Feasel

Thomas Roh takes us through an oddity in R’s DBI library:

I ran into an issue the other day where I was tring to write a new table to a SQL Server Database with a non-default schema. I did end up spending a bit of time debugging and researching so I wanted to share for anyone else that runs into the issue. Using the DBI::Id function, allows you to specify the schema when you are trying to write a table to a SQL Server database.

Click through for the end result. I will say that the more I work with DBI, the more I’m tempted to keep using rodbc, at least when working with SQL Server. H/T R-Bloggers.

Comments closed

Spark Transformations and Actions

Published 2019-10-22 by Kevin Feasel

Divyansh Jain differentiates the key sets of functions in Spark:

Now there is a point to be noted here and that is when you apply the transformation on any RDD it will not perform the operation immediately. It will create a DAG(Directed Acyclic Graph) using the applied operation, source RDD and function used for transformation. And it will keep on building this graph using the references till you apply any action operation on the last lined up RDD. That is why the transformation in Spark are lazy.

Read on for more details.

Comments closed

Making SQL Agent Jobs AG-Aware

Published 2019-10-22 by Kevin Feasel

Stuart Moore shows how you can use dbatools to make SQL Agent jobs Availability Group-aware:

What do I mean by Availability Group aware? When running on an Availability Group, one SQL Server instance ‘owns’ the database at any point in time, but the SQL Agent jobs have to be replicated across all of the instances in the cluster. So you want to make sure that your SQL Server Agent jobs only do work on the instance that currently owns the Availability Group.

Doing this is pretty simple. Below is a piece of T-SQL that checks if the current SQL Server Instance is the primary instance in the AG. If it isn’t then we exit with an error.

Read on to see how, and how you can use dbatools to automate this work.

Comments closed

When Extended Event Loss Occurs

Published 2019-10-22 by Kevin Feasel

Jonathan Kehayias explains when Extended Events will discard an event:

There are three specific session options that determine how large of an event an event session can actually collect, and one that controls how events are dropped when the buffer memory for the event session is full or under pressure. All four of these matter when we are talking about collecting events that could generate a large event payload and we want to minimize the chance that we could potentially drop an event.

Read on to see these settings in action.

Comments closed

PowerApps Security

Published 2019-10-22 by Kevin Feasel

Jason Bonello gives us some tips on PowerApps security:

Depending on how the backend is set up, the tables having these sensitive data might be in the same database. For example, ERP solutions can have Company Accounts data, Customer related data and Inventory related data all in the same database, maybe under different schemas – but still part of the same database.

Now let’s say we are about to create a PowerApps solution to maintain Customer information. However, as part of the organization policy, this information should not be shared across other departments apart from the intended users.

Read on for some ideas of how to limit the risk of data exposure.

Comments closed

Azure Data Factory Switch Activity

Published 2019-10-22 by Kevin Feasel

Rayis Imayev explains what the Switch activity does in Azure Data Factory:

Developing conditional logic of your Azure Data Factory control flow has been simplified with introducing of the Switch activity – https://docs.microsoft.com/en-us/azure/data-factory/control-flow-switch-activity. Official documentation resource states, this new data factory activity “provides the same functionality that a switch statement provides in programming languages“. I would also add a more simplified definition of the Switch activity in Azure Data Factory: it is a container (or wrapper) for multiple IF conditions.

Click through for an example.

Comments closed

Accelerated Database Recovery

Published 2019-10-22 by Kevin Feasel

Andy Mallon explains the concept of Accelerated Database Recovery:

Accelerated Database Recovery(ADR) is a new feature intended to speed up the recovery process, which could be very slow, particularly when there are long-running, large transactions. ADR is not just for recovery after a crash, but also helps in other scenarios where the transaction log needs to be recovered–including Availability Group secondary redo and Failover Cluster Instance failovers.

This is one of the most interesting new features in SQL Server 2019.

Comments closed

Using Power BI Cards to Display Notes

Published 2019-10-22 by Kevin Feasel

Prathy Kamasani takes us through one use of the card visual in Power BI:

This is a long-overdue blog post. A couple of months ago, I worked with a client in Amsterdam; one of the use cases was to show key metrics, flags that need attention. The user also wanted to click on warning symbol to confirm, where the issues were, however, the user didn’t want a drill through, it has to be a left-click.

As of yet, except for button/action we can not do left clicks in Power BI. As the user didn’t want the report to open in another browser tab etc., so was thinking about other options and at the end decided to go for tooltips and symbols to show flags like below:

Click through for an example and an explanation of how it works.

Comments closed