Press "Enter" to skip to content

Month: June 2022

Point-in-Time Recovery with Azure SQL DB and Managed Instances

Published 2022-06-14 by Kevin Feasel

Ahmed Mahmoud looks at point-in-time recovery and answers some frequently asked questions:

On some occasions, after the failover is initiated, the current Primary DR will start a new backup chain from that point and old backups are available on the current secondary DR. If we want to restore the backups which exists in Secondary it will not allow us to perform, apparently restore cannot be initiated on the Primary as the backup is not available.

Also, sometimes we observe in secondary DR for few databases, PitR restore point is available and for few databases it shows “no restore point available”    

Read on to understand why that happens and what you can do about it.

Comments closed

Consistency as the Best Coding Rule

Published 2022-06-14 by Kevin Feasel

Kevin Chant values consistency:

Over the years I have worked a lot with automating T-SQL updates across multiple databases. During that time, it has become very clear that both consistency and comments are essential to make the process smooth.

Naming conventions of objects created using T-SQL should be consistent. Along with comments in your code for colleagues or anybody else who picks up your code.

Especially if you are working in a team who want to work in a DevOps related way.

I value consistency a lot but I’ve found I’m too inconsistent to be consistent.

Comments closed

Error 13535: Data Modification Failed with Temporal Tables

Published 2022-06-14 by Kevin Feasel

Bob Dorr troubleshoots an issue:

When 2 or more workers are modifying the same row, it is possible to encounter 13535.  The time of the begin transaction and the modification are the defining properties.  When the transaction begin time is before the latest row modification, error 13535 is encountered.

Click through for an example of how you might trigger this error. This ultimately is the optimistic concurrency problem: how do you deal with multiple writers when using snapshot-based optimistic concurrency? Silently clobber or raise an error? Looks like temporal tables, like memory-optimized tables, raise an error instead of going quietly into the night.

Comments closed

Editing a Legend in Excel

Published 2022-06-13 by Kevin Feasel

Mike Cisneros provides tips on how to edit chart legends in Excel:

One essential element of our charts and graphs rarely gets the attention it deserves: the legend. 

Without a clear and thoughtfully-incorporated legend, viewers of our data communications will struggle to understand exactly what we’re presenting to them. Any additional effort an audience needs to devote to solving the mystery of “which data series is green?” or “what’s the difference between square data markers and circles?” is energy they won’t have to put towards grasping your visual’s important insights. A well-designed legend will remove that cognitive burden. 

Click through for eight such tips.

Comments closed

Building a Q&A Engine in R with httr and Shiny

Published 2022-06-13 by Kevin Feasel

Benjamin Smith builds an oracle but with R, not Delphi:

Knowing how to write API requests and handle their responses is a valuable skill that a developer, data
engineer or data analyst/scientist needs to know. In this short blog I share how its possible leverage DuckDuckGo’s instant answer API to create a oracle which can answer (some) of your questions using the httr package and Shiny.

Click through for a simple app which does the job.

Comments closed

Using SELECTEDVALUE with Field Parameters

Published 2022-06-13 by Kevin Feasel

Marco Russo diagnoses an error:

If you try to use SELECTEDVALUE on the visible column of the table generated by the Fields Parameters feature in Power BI, you get the following error:

Calculation error in measure ‘Sales'[Selection]: Column [Parameter] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.

Read on to understand what causes this error and what you can do to resolve it.

Comments closed

Guidance on When to Use Azure Data Explorer

Published 2022-06-13 by Kevin Feasel

Tzvia Gitlin Troyna has a flow chart for us:

Azure Data Explorer is a big data interactive analytics platform that empowers people to make data driven decisions in a highly agile environment. The factors listed below can help assess if Azure Data Explorer is a good fit for the workload at hand. These are the key questions to ask yourself.

The following flowchart table summarize the key questions to ask when you’re considering using Azure Data Explorer.

In addition to the flow chart, there is a table of three common patterns of interaction which ADE can do well.

Comments closed

Seven Principles for BI Skill Development

Published 2022-06-13 by Kevin Feasel

Brett Powell thinks about first principles:

Data and analytics languages should be prioritized far beyond graphical interface tools/software/services and should form a solid foundation of a skillset. Unlike software applications and various user interface controls which change frequently, the essential concepts and semantics of data languages such as SQL and DAX don’t change nearly as frequently and thus languages offer a much greater return on the time invested to learn them. For example, the fundamental PowerShell scripting knowledge I built up years ago using the Windows PowerShell ISE can still be applied today in many different tools, apps, and services that weren’t around back then such as Azure Function Apps and Visual Studio Code.

In almost every BI project I can remember, even projects that were explicitly intended to use low-code or no-code tools, it was the combination of different languages such as SQL, DAX, Kusto (KQL), Power Fx, and others that delivered the most value or which made the difference between project success and failure. Similarly, even in projects in which my role was intended to exclusively focus on the data model layer with DAX, I’ve almost always found myself also writing SQL, Power Query (M) and using other languages and code either in the data warehouse or on the reporting layer.

Brett has put a lot of thought into this and I think many of the principles apply outside of business intelligence work as well.

Comments closed