dataMinds News Round up – September 2021

Azure Synapse

With Synapse workspaces, you can create tables in the managed spark clusters within the workspace. As a native feature of the product, these tables are synchronously replicated to the managed SQL serverless endpoint within the same workspace. This allows a familiar SQL interface and the ability to manage such SQL-like objects using the familiar SQL server vocabulary. However, in some cases you might not want to explicitly grant sysadmin permissions to query such shared databases or tables. Raunak Jhawar to the rescue.

The Microsoft Azure Well-Architected Framework is a set of 5 pillars which can be used to help an organisation improve the quality of their Azure workloads and infrastructure. The principles within these 5 pillars can help guide a person and/or organisation to deploy a safe, performant, and stable Azure Synapse Analytics environment. This framework is available within the Microsoft Azure Architecture Centre which also includes reference architectures to suit a variety of scenarios. Andy Cutler paves the path for you!

Azure Databricks

Behind the growth of every consumer-facing product is the acquisition and retention of an engaged user base. When it comes to customer acquisition, the goal is to attract high-quality users as cost effectively as possible. With marketing dollars dispersed across a wide array of different touchpoints — campaigns, channels, and creatives — measuring effectiveness is a challenge. In other words, it’s difficult to know how best to assign credit. Debu Sinha and Dan Morris shine their light on how multi-touch attribution can help you overcoming this hurdle.

Overall, studies show that the average out-of-stocks (OOS) rate is about 8%. That means that one out of 13 products is not purchasable at the exact moment the customer wants to get it in the store. OOS is one of the biggest problems in retail, but thankfully it can be solved with real-time data and analytics. In this write-up, Rich Williams, Morgan Seybert, Rob Saker and Bryan Smith reveal their approach with the Databricks Lakehouse Platform.

SQL

Azure SQL database is a fully managed Platform as a Service (PaaS) solution, that offers the ability to create, build and migrate native cloud applications at an affordable price. But what if there is a need to schedule a batch process, like using a SQL Server Agent Job for an on-premises instance? There are multiple options to achieve scheduling in the cloud, with each having pros and cons. In this tutorial, Eduardo Pivaral looks at the easiest way to achieve scheduling using a Basic Tier database.

Ever wanted to send streaming data from Azure IoT Central to an Azure SQL Database? Sucharita Das collected telemetry data from her mobile phone and will show you how to set up a continuous export through Azure Event Hubs and Stream Analytics.

Is there a way to alert on job steps that failed even when the job succeeded? Steve Jones show how you can set up a custom measure and provides you with a general approach on how to determine if a job step failed.

Power BI

Paginated Reports for Power BI offer pixel-perfect control over the format of each element of a report. They allow users to fine-tune each field of the report, such as text size, colors, spacing, and print layout, in a more precise way than using regular visuals in Power BI Desktop. Users can access Paginated Reports directly from workspaces in Power BI Service. Additionally, users can embed Paginated Reports directly onto a Power BI report page with the new visual option. Elayne Jones explains how to create a Paginated Report and how to configure the new Paginated Reports visual in Power BI Desktop.

The integration between Azure Log Analytics and Power BI opens up a lot of new possibilities for Power BI administrators. The Analysis Services events that are available,in public preview, give you pretty much everything you had in Profiler plus some new stuff too. Chris Webb shows you how you can use this data to work out which visual in a published report generated a particular DAX query.

The recent release of LAMBDA Helper functions in Excel unlocked some interesting opportunities when it comes down to Excel cube functions. They might be the least appreciated, least known and least used feature in the whole Microsoft BI stack. However, they can come in quite handy when you want to deviate from the standard pivot table in need of custom Excel reports such as balance sheets or P&L statements. Chris Webb got you covered in his triptych.

Phil Seamark reveals the secrets of Power BI User-Defined Aggregations feature. A well-explained walkthrough scratching the surface for those in need of a refresher or reminder or for those that are just keen to understand how to configure them.