Big Data

Microsoft Fabric extends the promise of Azure Synapse integration to all analytics workloads from the data engineer to the business knowledge worker. It brings together Power BI, Data Factory, and the Data Lake, on a new generation of the Synapse data infrastructure. Delivered as a unified SaaS offering, it aims to reduce cost and time to value, while enabling new "citizen data science" capabilities. Check out all the resources from the endjin team collated in this post.

OneLake is the core of Microsoft Fabric - it is essentially the storage account for all of your data utilized within Fabric, be that within Azure or from another cloud (through Shortcuts). As the name suggests, it is a single, logical data lake backing more or less all of your Fabric workloads. For fans of analogies: you'll have probably heard Microsoft use the tag-line "OneLake is to data what OneDrive is to files/documents". Read this blog to dive deeper into OneLake and learn about why you should be excited.

Microsoft Fabric is a unified data & analytics platform that brings together, and improves upon, Microsoft's existing suite of data products. It can be thought of as the third generation of Microsoft data platforms, where first generation (e.g. HDInsight, SQL Data Warehouse) were somewhat isolated takes on traditional data products; second generation was Azure Synapse Analytics, which integrated platforms at a UX level but still felt a little disjointed at the data level; and now we have Microsoft Fabric which builds upon the Synapse "unification" vision, with a particular focus on enabling deep data-level interoperability. In this post we introduce its core offerings, as well as weigh up the pros and cons of this new product.

Learn about the thinking behind endjin's Power BI Maturity assessment by applying Wardley Doctrine, and asking more questions.

This is a summary of the sessions I attended at SQLbits 2023 in Newport Wales, which is Europe's largest expert led data conference.

Data validation is a vital step in any data-oriented workstream. This post investigates and compares two popular Python data validation packages - Pandera and Great Expectations

Great, so I've configured my Lake Database in Azure Synapse Analytics. But since I'm using parquet-backed files, my column names aren't very user-friendly. I also have these calculated columns incorporating business logic that I'd like to query on the fly rather than persist them to backing data. I also want to give specific end-users access to this database and provide them with pre-defined reporting queries to get them up and running as quickly as possible. How can I do this? Enter Custom Objects in Lake Databases - now you can create VIEWs, Stored Procedures, USERs (amongst other objects) in what used to be a read-only database. This article explores the customization options and how it can help you organize your reporting data in Azure Synapse Analytics

In this second blog in the series, we put the newly released Azure Synapse Analytics Database Templates into action by exploring the different methods that are available to create a semantic model.

Lake Databases in Azure Synapse Analytics are just great. If you're starting on a new Synapse Analytics project, chances are you can benefit from Lake Databases. Whether you need to analyze business data from Dataverse, share your Spark tables of data with SQL Serverless, or use Database Templates to visually design and define your standardized/semantic analytical data model, there's a use-case for you. In this blog we'll explore what Lake Databases are, why they're useful, and what you need to watch-out for when using them.

Many data projects fail to deliver the impact they should for a simple reason – they focus on the data. This series of posts explains a different way of thinking that will set up your data & analytics projects for success. Using an iterative, action-oriented, insight discovery process, it demonstrates tools and techniques that will help you to identify, define and prioritize requirements in your own projects so that they deliver maximum value. It also explores the synergy with modern cloud analytics platforms like Azure Synapse, explaining how the process and the architecture actively support each other for fast, impactful delivery.

In this blog series we explore the newly released Azure Synapse Analytics Database Templates. We put them into action to understand how they can be leveraged as part of a modern data pipeline.

Many data projects fail to deliver the impact they should for a simple reason – they focus on the data. This series of posts explains a different way of thinking that will set up your data & analytics projects for success. Using an iterative, action-oriented, insight discovery process, it demonstrates tools and techniques that will help you to identify, define and prioritize requirements in your own projects so that they deliver maximum value. It also explores the synergy with modern cloud analytics platforms like Azure Synapse, explaining how the process and the architecture actively support each other for fast, impactful delivery.

Many data projects fail to deliver the impact they should for a simple reason – they focus on the data. This series of posts explains a different way of thinking that will set up your data & analytics projects for success. Using an iterative, action-oriented, insight discovery process, it demonstrates tools and techniques that will help you to identify, define and prioritize requirements in your own projects so that they deliver maximum value. It also explores the synergy with modern cloud analytics platforms like Azure Synapse, explaining how the process and the architecture actively support each other for fast, impactful delivery.

Many data projects fail to deliver the impact they should for a simple reason – they focus on the data. This series of posts explains a different way of thinking that will set up your data & analytics projects for success. Using an iterative, action-oriented, insight discovery process, it demonstrates tools and techniques that will help you to identify, define and prioritize requirements in your own projects so that they deliver maximum value. It also explores the synergy with modern cloud analytics platforms like Azure Synapse, explaining how the process and the architecture actively support each other for fast, impactful delivery.

Many data projects fail to deliver the impact they should for a simple reason – they focus on the data. This series of posts explains a different way of thinking that will set up your data & analytics projects for success. Using an iterative, action-oriented, insight discovery process, it demonstrates tools and techniques that will help you to identify, define and prioritize requirements in your own projects so that they deliver maximum value. It also explores the synergy with modern cloud analytics platforms like Azure Synapse, explaining how the process and the architecture actively support each other for fast, impactful delivery.

Many data projects fail to deliver the impact they should for a simple reason – they focus on the data. This series of posts explains a different way of thinking that will set up your data & analytics projects for success. Using an iterative, action-oriented, insight discovery process, it demonstrates tools and techniques that will help you to identify, define and prioritize requirements in your own projects so that they deliver maximum value. It also explores the synergy with modern cloud analytics platforms like Azure Synapse, explaining how the process and the architecture actively support each other for fast, impactful delivery.

In this blog we demonstrate how the Gherkin specification can be adapted to enable BDD to be applied to data engineering use cases.

The "Shared Metadata Model" is a powerful feature within Synapse Analytics that synchronizes Spark database objects with SQL Serverless. This article describes how to give non-admin users access to these synchronized objects in a least-privileged manner.

A lesser known feature of Azure Synapse is the "Shared Metadata Model". Synapse has the capability to automatically synchronize tables created via Synapse Spark with objects you can query via the usual SQL Serverless endpoint, without any additional configuration. This article brings attention to this capability, highlighting the benefits and tradeoffs vs rolling your own SQL Serverless VIEWs.

The world runs on Excel, and misuse has caused some infamous data loss incidents. This post explores what happens when identifiers fall foul of Excel's numeric precision rules.

This is a summary of the sessions I attended at SQLbits 2022 in London, which is Europe's largest expert led data conference.

Spending more time planning then doing? Struggling to get stakeholders engaged in making tough decisions about prioritisation? This simple, light touch approach to visual prioritisation could help.

Despite being inherently difficult to test, the need to validate data modelling, business rules and security boundaries in Power BI reports is important, as well as the need for ensuring that quality doesn't regress over time as the insights evolve. This post explains that, by executing DAX queries over underlying tabular model, it is possible to execute scenario-based specifications to add quality gates and build confidence in Power BI reports, just as any other software project.

The new Power BI ExecuteQueries REST API presents a number of new opportunities for Power BI developers in terms of tooling, process and integrations. This post highlights some of the key advantages of this new capability.

SQL Serverless in Azure Synapse provides a structured way to query your data on-demand directly from your data lake. This consumption-based, flexible approach to data warehousing provides a compelling alternative to the traditional star-schema or RDBMS, but comes with it's own set of new challenges. This post explores some of the considerations around managing schemas in a serverless world, including an approach for ensuring that your data and schema remain in sync.

Thinking of data as the new soil is useful in highlighting the key elements that enable a successful data and analytics initiative.

Interactive Spark notebooks are an incredibly powerful tool for data exploration and experimentation. And in Azure Synapse, the time to (business) value is significantly decreased due to tight integration with Pipelines and monitoring tooling. But as with any software process, the need to validate business rules is important, as is ensuring that quality doesn't regress over time - especially so in such a collaborative and productive environment. This post looks at some simple ways to add testing to your Synapse Notebooks.

Some of my thoughts inspired whilst helping out on the farm over the weekend. What is the future of work given the increasing presence of machines in our day to day lives? In which situations can AI deliver greatest value? How can we ease the stress of digital transformation on people who are impacted by it?

Did you know that there's a null-safe operator in Azure Data Factory's expression syntax? No? Well, now you do. Here's how you can safely reference an activity output that might not always exist.

Summary of key themes from the Doing Data Together conference hosted virtually by The Scotsman newspaper and Edinburgh University in November 2020. The conference agenda was pivoted to focus on the use of data to help tackle the Covid-19 pandemic. It provided a fascinating insight into the lessons learned.

Modern analytics requires a multi-faceted approach, which can cause integration headaches. Azure Synapse's Swiss army knife approach can remove a lot of friction.

Data insights are useless, and even dangerous (as we've seen recently at Public Health England) if they can't be trusted. So, the need to validate business rules and security boundaries within a data solution is critical. This post argues that if you're doing anything serious with data, then you should be taking this seriously.

Data Engineers/Developers want to get access to Azure Synapse Analytics as quickly as possible to start designing and creating their data solutions. Being denied access to Synapse Studio can be frustrating and slows matters down. This article will address the "You need permission to access workspace..." error, discuss what causes it, and describe how to fix it.

Azure Synapse Analytics developers need to be assigned a role within Synapse Studio in order to access the GUI. The Azure CLI provides one way of programmatically achieving this, which can be done by any Owner or Contributor of the Azure Synapse Analytics resource.

Despite the subsequent media reporting, the loss of 16,000 Covid-19 test results at Public Health England wasn't caused by Excel. This post argues that a lack of an appropriate risk and mitigation analysis left the process exposed to human error, which ultimately led to the loss of data and inaccurate reporting. It describes a simple process that could have been applied to prevent the error, and how it will help if you're worried about ensuring quality or reducing risk in your own business, technology or data programmes.

Azure Synapse Link for Cosmos DB is a game-changing piece in the Synapse suite of services - extending the support for SQL on Demand to enable querying over the Cosmos DB Analytical Store. This post explores whether the term 'full stack serverless' should now be extended to cover No-ETL and pay-as-you-query analytics, alongside serverless application architectures.

Wishing Azure Synapse Analytics had support for SQL notebooks? Fear not, it's easy to take advantage rich interactive notebooks for SQL Pools and SQL on Demand.

Tracking down unexpected allocations in a high-performance .NET parsing library.

Azure Synapse Analytics is Microsoft's new unified cloud analytics platform, which will surely be playing a big part in many organizations' technology stacks in the near future. For many organizations, Azure Resource Manager (ARM) templates are the infrastructure deployment method of choice. This blog explains how to deploy an Azure Synapse Analytics workspace using an ARM template.

Serverless data architectures enable leaner data insights and operations. How do you reap the rewards while avoiding the potential pitfalls?

I was recently invited on to Microsoft Mechanics to talk about the new on-demand SQL Serverless offering within Azure Synapse. If you have been following along with my previous blog posts you will know that we've been hard at work applying Azure Synapse against real customer workloads. In the video I take you through the service by solving a real-world IoT problem for one of our telco customers.

There is a new service in town that promises to transform the way you query the contents of your data lake. Azure Synapse Analytics comes with a new offering called SQL Serverless allowing you to query your data on-demand with no need for pre-provisioned resources.When we heard about the new service we were keen to get involved, so for the last 10 months we've been working with the SQL Serverless product group to provide feedback on the service and to help ensure it meets our customers needs. During this time we've put it through it's paces by implementing a range of real-world use cases. We were particularly interested to see how it stacked up as a replacement for Data Lake Analytics, where to date there has been no clear and easy migration path.

Have you or are you about to invest in Azure Databricks? If so, the new Spark offering in Azure Synapse Analytics is likely to have grabbed your attention and rightly so. Why is Microsoft putting yet another Spark offering on the table and what does it mean for you?

For years we have been building modern cloud data solutions on Azure and helping our customers transform their use of data to drive outcomes. Here are 5 reasons why Azure Synapse Analytics might just be the service that we have been crying out for.

Whilst "read/write XMLA endpoint" might seem like a technical mouthful, its addition to Power BI is a significant milestone in the strategy of bringing Power BI and Analysis Services closer together. As well as closing the gap between IT-managed workloads and self-service BI, it presents a number of new opportunities for Power BI developers in terms of tooling, process and integrations. This post highlights some of the key advantages of this new capability and what they mean for the Power BI developer.

Despite being inherently difficult to test, the need to validate data modelling, business rules and security boundaries in Power BI reports is important, as well as the need for ensuring that quality doesn't regress over time as the insights evolve. This post explains that, by connecting to the underlying tabular model, it is possible to execute scenario-based specifications to add quality gates and build confidence in Power BI reports, just as any other software project.

Jess and Carmel recently gave a talk at Azure Oxford on Combatting illegal fishing with Machine Learning and Azure - for less than £10 / month. The recording of that talk is now available for viewing!The talk focuses on the recent work we completed with OceanMind. They run through how to construct a cloud-first architecture based on serverless and data analytics technologies and explore the important principles and challenges in designing this kind of solution. Finally, we see how the architecture we designed through this process not only provides all the benefits of the cloud (reliability, scalability, security), but because of the pay-as-you-go compute model, has a compute cost that we could barely believe!

Whilst testing Power BI Dataflows isn't something that many people think about, it's critical that business rules and associated data preparation steps are validated to ensure the right insights are available to the right people across the organisation. Data insights are useless, even dangerous, if they can't be trusted, so despite the lack of "official support" or recommended approaches from Microsoft, endjin treat Power BI solutions just as any other software project with respect to testing - building automated quality gates into the end to end development process. This post outlines an approach that endjin has used to test Power BI Dataflows to add quality gates and build confidence in large and complex Power BI solutions.

Azure Analysis Services provides an enterprise-grade analytical platform with massive scale and flexibility. But, as one of the more expensive services in the Azure platform, consideration should be given to cost management, especially in multi-environment ALM scenarios. This post explains how to massively reduce running costs through automation using Powershell and orchestration tools like Azure DevOps.

At endjin, our approach focuses on using scientific experimental method to support the creation of fully proved and tested decision making, and the use of scientific research to support our work. This post runs through how we applied that process to creation a pipeline to detect vessel proximity.This is an example which is based around the project we recently worked on with OceanMind. In this project we helped them to build a serverless architecture which could detect vessel proximity in close to real time. The vessel proximity events we detected were then fed into machine learning algorithms in order to detect illegal fishing! Carmel also runs through some of the actual calculations we used to detect proximity, how we used data projections to efficiently process large quantities of incoming data, and the use of Durable Functions to orchestrate the processing.

This post explains how to update Azure Analysis Services model schemas from inside custom .NET applications. Whilst not a common scenario for most, it shows that this is easy to do using the AMO SDK. So, there's nothing stopping you from developing complex and rich end-user functionality over the top of your data analysis solutions - providing run-time, user-driven schema changes like "what if" analysis.

In our recent project with OceanMind we used Azure Functions to process marine vessel telemetry from around the world. This involved processing huge quantities of data in close to real time. We optimised our processing for a serverless environment, the outcome of which being that the compute would cost less than £10 / month!This post summarises some of the techniques we used, including some concrete examples of optimisations we made.

Integrating Azure Analysis Services into custom applications doesn't just mean read-only data querying. But if your application changes the underlying model, it will need to be re-processed before the changes take effect. This post describes how to use the REST API for Azure Analysis Services inside a custom .NET application to perform asynchronous model refreshes, meaning your applications can reliably and efficiently deal with model updates.

As part of our work with OceanMind, endjin wrote a high performance .NET AIS parser. AIS (Automatic Identification System) is how commercial ships report location information. This blog describes the parser, and the performance techniques it uses.

Being able to construct DAX queries dynamically in C# means the possibilities are endless in terms of integrating Azure Analysis Services queries into your custom applications, and with the code samples in this post, you have everything you need to get started.

The theme of this year's British Science Week (6 - 15 March 2020) is "Our Diverse Planet". We'll be getting involved by speaking to school children about the work we've been doing with Oxfordshire-based OceanMind (part of the Microsoft AI for Good programme) to help them combat illegal fishing, hopefully inspiring some of the next generation of data scientists!

Integrating Azure Analysis Services into custom applications means more than just querying the data. By surfacing the metadata in your models, you can build dynamic and customisable UIs and APIs, tailored to the needs of the client application. This post explains how easy it is to query model metadata from .NET, so you can create deeper integrations between your data insights and your custom applications.

One of the first steps in integrating Azure Analysis Services into your applications is creating and opening a connection to the server - just like any other database technology. This post explains the ins and outs of creating Azure Analysis Services connections, including code samples for each of the key scenarios.

With a variety of integration support through client SDKs, PowerShell cmdlets and REST APIs, it can be hard to know where to start with integrating Azure Analysis Services into your custom applications. This posts walks through the options, and lays out a simple guide to choosing the right framework.

We've done a lot of work at endjin with Azure Analysis Services over the last couple of years - but none of it has been what you'd call "traditional BI". We've pulled, twisted and bent it in all sorts of directions, using it's raw analytical processing power to underpin bespoke analysis products and processes. This post explains some of the common (and not-so-common) reasons why you might want to do similar things, and how Azure Analysis Services might be the key to unlocking your data insights.

Towards the end of last year, Microsoft invited endjin along to a hackathon session they hosted at the IET in London as part of their AI for Good initiative. I've been thinking about the event and the broader work Microsoft is doing here a lot lately, because it gets to the heart of what I love about working in this industry: computers can magnify our power to do to good.

In this blog we discuss building a secure data solution using Azure Data Lake. Data Lake has many features which enable fine grained security and data separation. It is also built on Azure Storage which enables us to take advantage of all of those features and means that ADLS is still a cost effective storage option!This post runs through some of the great features of ADLS and runs through an example of how we build our solutions using this technology!

In January 2020, Carmel is speaking about creating high performance geospatial algorithms in C# which can detect suspicious vessel activity, which is used to help alert law enforcement to illegal fishing. The input data is fed from Azure Data Lake Storage Gen 2, and converted into data projections optimised for high-performance computation. This code is then hosted in Azure Functions for cheap, consumption based processing.

The addition of ref struct types, most notably Span<T>, opened C# to a range of high performance scenarios that were impractical to tackle with earlier versions of the language. However, they introduce some challenges. For example, they do not mix very well with async methods. This article shows some techniques for mitigating this.

We worked on a project recently which required us to build a highly performant system for processing vast quantities of messages in real time. We had made the decision to run this processing using Azure Functions with C#. This post runs through some of the techniques we used for writing highly performant, low allocation code, including data streaming, list preallocation and the relatively new C# feature: Span&lt;T&gt;.

Sometimes it's necessary to import and export notebooks from a Databricks workspace. This might be because you have some generic notebooks that can be useful across numerous workspaces, or it could be that you're having to delete your current workspace for some reason and therefore need to transfer content over to a new workspace. Importing and exporting can be doing either manually or programmatically. In this blog, we outline a way to recursively export/import a directory and its files from/to a Databricks workspace.

Machine learning often seems like a black box. This post walks through what's actually happening under the covers, in an attempt to de-mystify the process!Neural networks are built up of neurons. In a shallow neural network we have an input layer, a "hidden" layer of neurons, and an output layer. For deep learning, there is simply more hidden layers which allows for combining neuron's inputs and outputs to build up a more detailed picture.If you have an interest in Machine Learning and what is really happening, definitely give this a read (WARNING: Some algebra ahead...)!

Quite often it's beneficial to work with pre-built CLIs/SDKs to interact with your favourite tools, instead of making requests to the underlying REST API. Much of the complexity around constructing requests has been abstracted, and authentication is often easier. The Databricks CLI makes it easier to interact with your Databricks instance, but sometimes you can run into strange errors when constructing the values passed in as arguments. In this blog, we take a look at a JsonDecodeError that can occur when speaking to the Clusters CLI, and look at a way we can avoid this error.

Here at endjin we've done a lot of work around data analysis and ETL. As part of this we have done some work with Databricks Notebooks on Microsoft Azure. Notebooks can be used for complex and powerful data analysis using Spark. Spark is a "unified analytics engine for big data and machine learning". It allows you to run data analysis workloads, and can be accessed via many APIs. This means that you can build up data processes and models using a language you feel comfortable with. They can also be run as an activity in a ADF pipeline, and combined with Mapping Data Flows to build up a complex ETL process which can be run via ADF.

Snowflake is a cloud native data warehouse platform, that enabled data engineering, data science, data lakes, data sharing and data warehousing. Endjin are very excited to announce our partnership.

Mapping Data Flows are a relatively new feature of ADF. They allow you to visually build up complex data transformation sequences. This can aid in the streamlining of data manipulation and ETL processes, without the need to write any code! This post gives a brief introduction to the technology, and what this could enable!

When he joined endjin, Technical Fellow Ian sat down with founder Howard for a Q&A session. This was originally published on LinkedIn in 5 parts, but is republished here, in full. Ian talks about his path into computing, some highlights of his career, the evolution of the .NET ecosystem, AI, and the software engineering life.

If you're using the Gremlin API for Cosmos DB, you can now see how much each operation costs in Request Units.

This is the first blog in a series about dataflow. The series focuses on TPL dataflow, but this post gives an overview of dataflow as a whole.The crucial thing to understand when using dataflow is that the data is in control. In most conventional programming languages, the programmer determines how and when the code will run. In dataflow, it is the data that drives how the program executes. The movement of data controls the flow of the program.

Do you have a bunch of data in SQL Server that you're using ODBC/JDBC to pull data to work with in Python? Using SQL Server's Python integration, you can connect to a SQL Server instance within your preferred IDE and perform the computations on the SQL Server Machine. No more clunky data transferring. Operationalizing a Python model/script is as easy as calling a stored procedure. Any application that can speak to SQL Server can invoke the Python code and retrieve the results. Easy! This blog will provide a few, simple examples which make use of this capability to carry out some simple Python commands, so you can get up and running as quickly as possible.

Learn what types of things an apprentice gets up to at endjin a few months after joining. You could be learning about Neural Networks: algorithms which mimic the way biological systems process information. You could be attending Microsoft's Future Decoded conference, learning about Bots, CosmosDB, IoT and much more. Hopefully, you wouldn't be in hospital after a ruptured appendix!

We've been helping customers adopt Microsoft Azure since 2010, we have produced a lot of thought leadership to help people think about the steps required, the risk involved and how to plan a successful adoption.

Sometimes you want to build a Power BI dashboard that pulls in data from two different data sources. In this blog post Alice Waddicor demonstrates how you can use DirectQuery and multiple databased via ElasticQuery.

Alice reflects on year 2, being given more responsibility, diving deeper into all aspects of software delivery, and the good habits she's been building.

What do machine learning and data science actually mean? This post digs into the detail behind the endjin approach to structured experimentation, arguing that the "science" is really all about following the process, allowing you to iterate to insights quickly when there are no guarantees of success.

We have produced an insightful booklet called "Embracing Disruption - Financial Services and the Microsoft Cloud" which examines the challenges and opportunities for the Financial Service Industry in the UK, through the lens of Microsoft Azure, Security, Privacy & Data Sovereignty, Data Ingestion, Transformation & Enrichment, Big Compute, Big Data, Insights & Visualisation, Infrastructure, Ops & Support, and the API Economy.

This post looks at what machine learning really is (and isn't), dispelling some of the myths and hype that have emerged as the interest in data science, predictive analytics and machine learning has grown. Without any hard guarantees of success, it argues that machine learning as a discipline is simply trial and error at scale – proving or disproving statistical scenarios through structured experimentation.