-
Databricks JDBC driver for read and write workloads
Aperture Data Studio supports both the JDBC v2 (Simba / legacy line) and JDBC v3 (Databricks OSS / current and future line) drivers. These are "third-party" drivers which must be downloaded, but which will have some configuration support in the user interface. Databricks (via the JDBC driver) is also a supported system for…
-
Using ❄️ Snowflake with Aperture Data Studio
Snowflake is a popular cloud-based data warehouse that is used by many organizations to store, process, and analyze large volumes of diverse data. Connecting Snowflake to Aperture Data Studio Aperture Data Studio can import data and metadata from file stores, CRMs and databases, including Snowflake. To connect to Snowflake…
-
Unlock the Power of Markdown in Aperture Data Studio
Did you know Aperture Data Studio supports Markdown? This lesser-known feature can transform how you present information, making your dashboards, workflows, and catalog entries more engaging and easier to read. Markdown enables a wide range of formatting options, including: Text formatting (headers, subheaders, bold,…
-
Understanding Cloud Licensing Security Setup
If your account manager or consultant has recommended upgrading your Aperture Data Studio deployment to a cloud license, it’s because Aperture Data Studio v3.0 will only be available through cloud licensing. Cloud licensing brings many benefits (for more details, please refer to this write-up), but might require some…
-
Customizing your Dashboard with the Custom content widget
Data Studio Dashboards can be configured by adding, ordering and resizing different widgets including Views and Charts. One of these widgets is the Custom content widget: https://docs.experianaperture.io/data-quality/hosted-aperture-data-studio/data-studio-objects/dashboards/#custom-content~customizing-dashboards Firstly…
-
Connecting to an Amazon RDS MySQL Database
A user recently asked about connecting Data Studio to their AWS RDS MySQL Database. The first thing to note is that MySQL on RDS uses the MySQL community edition, which is not supported by Data Studio's native JDBC driver. When attempting to connect, you'll receive this error: "Connections to MySQL Community Server are not…
-
Licensing for Aperture Data Studio
This article provides an overview of all things cloud licensing including pre-requisites, activation and management guide and links to helpful documentation. Should you wish to switch to cloud licensing, please speak to your account manager or your local support team. Advantages of cloud licensing Faster setup and time to…
-
How to Implement Uniqueness & Referential Integrity Checks in Aperture Data Studio
Hi everyone, Happy New Year! 🎉 I wanted to share a quick tip that came up in a recent discussion around BCBS 239 compliance and data quality checks in Aperture Data Studio. Specifically, how to handle Uniqueness and Referential Integrity validations. Context When working with BCBS 239 or similar regulatory frameworks,…
-
Map list: Transform every item in a list
What it does: Applies a transformation function to each item in the list and returns a new list with the transformed values. Where it works: Comma-separated list JSON list Business Use Case & Example: Adding new prefix for employee ids before loading into a master database: This is perfect for standardizing formats,…
-
Map path: Targeted transformation in JSON documents
If you’re working with structured JSON data, Map path is your go-to Higher Order Function for applying transformations to specific fields. What it does: MapPath uses a JSONPath expression to locate fields in a JSON document and applies a function to each matched item. Where it works: JSON list JSON record Business Use…
-
Filter list: Keep only what matters
What it does: Returns a new list containing only the items that meet the condition defined by the operator function. Where it works: Comma-separated list JSON list Business Use Cases & Examples: Comma-separated list: Filtering emails with valid format from a list JSON List: Extract customers that matches a certain interest…
-
Any match: Find just one match in a list
What it does: Checks whether at least one item in a list satisfies a condition defined by a Boolean-returning function. Where it works: Comma-separated list JSON list Business Use Cases & Examples: Comma-separated list Flag if any items in the list are a UK postcode: JSON list Identify if the shopping cart contains a…
-
All match: Validate every item in a list
In this post, we will take a closer look at All match, one of the higher order functions now available in Aperture Data studio v3.2. What it does: All match checks whether every item in a list satisfies a condition defined by a Boolean-returning function. Where it works: Comma-separated lists JSON lists Business Use Cases…
-
Unlock powerful list and record logic with the higher order functions (part of List transformation)
In this series, we will explore the new set of Higher Order Functions (introduced in Aperture Data Studio v3.2) that bring more flexibility and control to how you work with lists and records in Aperture Data Studio. These functions allow you to pass another function as a parameter, making it possible to filter, match, and…
-
Gen AI in Aperture Data Studio
Aperture Data Studio has functionality that uses several different machine learning and artificial intelligence techniques, including clustering, cosine similarity, deep learning, and more recently GenAI. Gen AI Actions [in v3.1] When exploring data in the grid you are able to describe what you want to calculate or how…
-
A solution design for validating multiple mixed file schemas
We've recently been working on a solution to the following ask: A user has hundreds of data files with different schemas (different column names, datatypes, numbers of columns etc) Each file has an accompanying metadata file, defining expected attributes (columns, datatypes, primary keys, lengths) We want to be able to…
-
Make Your Data Pop: Visual Styling in Aperture Data Studio
Allow your data to speak for itself - Aperture Data Studio offers powerful ways to make your results more visual and intuitive, perfect for quick reviews and better decision-making. 1. Set Cell Style for RAG Colour Coding The Set Cell Style function lets you apply a Red-Amber-Green (RAG) overlay to cells based on business…
-
â„ą How to track DQ issues with Issue lists
Issue lists allow you to capture problematic records, assign them to stakeholders and collaborate on a resolution. Records that have been fixed will automatically be resolved. Setting up your Issues list Go to Issue lists in left nav bar and create a new list to track related issues Create a Workflow to write issues to the…
-
Suggested approaches for manipulating JSON values
My data includes a column containing JSON values - I've made a dummy example below: Example JSON value: { "magenta": "#f0f", "yellow": "#ff0", "black": "#000", "usageList": [ "giraffe", "lion", "axolotl" ], "properties": { "cost": 99, "difficulty": { "part_a": "hard", "part_b": "easy" } } } I have a couple of requirements:…
-
Word Frequency
Every now and then a scenario crops up where it'd handy to know how often a given word occurs within a given dataset. For example you're profiling a reasonably standardised list of values (e.g. job titles) and you want to identify unusually common terms (like 'manager' 'executive' etc) or infrequent ones (like 'test123').…
-
Bringing Data Together
Knowing the best path for working with multiple datasets can be confusing sometimes, understanding which approach is appropriate for what your wanting to achieve is key to getting the outcome you want. Here's a straight forward breakdown of 4 out-of-the-box Aperture Data Studio operations which will allow you to bring your…
-
 ℹ️  Importing your Datasets into Excel or Tableau using OData
You can easily get your data from Data Studio into Excel or Tableau using OData. OData (odata.org) is “an open protocol that allows the consumption of data via a simple and standard RESTful API”. If you choose to turn on OData in Data Studio (v2.2.3), you will be able to link your datasets (including snapshots) to any BI…
-
Enabling hard delete using the Salesforce connector
When deleting data from a Salesforce object you can tell the API to hard delete the data so that it does not go in to the recycle bin and therefore can't be recovered. It's also possible to do this with DELETEs in the Export step using the native Salesforce JDBC driver. Here are the steps: In your Salesforce External…
-
Suggest Validation rules - Validate step
Aperture Data Studio has a Validate workflow step that allows you to define rules of how data in specific columns should be populated. This information can then be used to be alerted to any data quality problems or to track data quality improvements over time. These rules can be simple: should not be null should be a…
-
Best practices designing complex Workflows
A Workflow is a sequence of steps that defines a process to transform and manipulate your data. When a single Workflow tries to tackle too many actions it can become difficult to read, understand and manage: If you are designing a Workflow alone this might be fine, but if you are collaborating or planning to have others…
-
â„ą How to use Functions in Workflows
Workflows Workflows is the area in Data Studio where designer users tend to spend most of their time. For anyone new to it, imagine a data pipeline that manipulates one or more data sources through a number of stages and then does something with the results. Whilst a Workflow is easy to build by connecting different steps…
-
How to best keep several environments in sync
I'd like to share a question that was recently raised by a user as I think it could be interesting for our community members. Question: We have two physical environments - Dev and Prod VMs and we have promoted all the objects from Dev to Prod environment (Export and Synchronised). Over time, Dev has gone through several…
-
Connecting to the ServiceNow REST API using OAuth2
The Autonomous REST Connector JDBC driver allows you to load (and refresh) the results returned from REST API calls as Data Studio Datasets, by translating SQL statements to REST API requests. In this article I'll show how to call ServiceNow's REST API (using OAuth2 authentication) to bring response data into Data Studio.…
-
Assign latest changed id to all linked records
Hi, I have a scenario where input file is providing changed address ids in from-address and to_address columns. Need to identify the links between the records using these 2 fields and assign latest id to all the linked records. Input: event_date,from_address_id,to_address_id 20210223,120,160 20210402,120,160…
-
🛢️ Loading from OData using the Autonomous REST Connector
The Autonomous REST Connector JDBC driver packaged with Aperture allows you to load (and refresh) the results returned from REST API calls as Datasets. OData is a standardized REST interface, which means that any data that can be queried via OData API can be loaded into Aperture. This article will demonstrate how to use…
-
Connecting to Salesforce with OAuth2
Data Studio uses a JDBC driver to connect to Salesforce, allowing data to be extracted, transformed, validated, and then optionally pushed back to source. As well as standard username / password authentication, the driver supports OAuth 2.0 authentication when establishing a connection. The following guide is a brief…
-
Business Validation and Enrichment
Aperture is a powerful tool when validating business information you already have. We can leverage trusted address sources, like the Royal Mail’s Postal Address File, for the UK. This will ensure that the addresses and business name information you hold is accurate and deliverable as per that trusted source. That’s all…
-
Use of If-Then-Else logic in Aperture Data Studio
Imagine you are asked to use Aperture Data Studio to generate a new field for your sales data. You have the input fields: Discount Code, Quantity and Price. The ask is to generate a new field “Offer Price” using the following logic: How can you do this in Aperture Data Studio? The answer is to build a custom transformation…
-
Documenting what data is available in Data Studio (to users who don't have access)
A question that cropped up earlier today was: 'how can we avoid different users connecting to the same data in different spaces?' Depending on how you've setup Data Studio and the processes around how your users work with it, this can be a bit of a challenge to tackle at the moment. However there's a solution, which is…
-
đź“‘ API paging with the Autonomous REST Connector
In this article I want to share a few approaches to configuring paging when accessing REST APIs using Aperture Data Studio's Autonomous REST (AutoREST) connector. What is the AutoREST connector? If you're reading this, you're probably already familiar with the AutoREST connector, but in case you're new to it, check out:…
-
How I got started with Data Studio
Assuming Data Studio is set up and your user account has been created, “Your space” is your personal work area. Any changes you make in this Space will not impact any other users. To understand some Aperture Data Studio specific terms (highlighted in bold below) the documentation site is a useful resource. Other resources…
-
Connecting to hosted Dynamics 365 with OAuth2
Data Studio’s Dynamics 365 connector can be used to read or write data from Microsoft Dynamics 365 apps. Click for a full list of supported Dynamics 365 ERP and CRM apps by the driver Click for a the driver's user documentation Click for basic config and troubleshooting steps in the Data Studio documentation The driver…
-
🎞️ A short video demonstation on using the Quick Actions bar, shortcuts and more
Since Aperture Data Studio 2.8.0 users have been able to use the Quick Actions bar to access frequent actions and quickly navigate the product. This short video gives a demonstration of how this can be used and also highlights other product shortcuts you may not have known. The main areas covered include: Quick Actions bar…
-
Quick Actions menu
Since v2.8 Aperture Data Studio has had a command palette that makes shortcuts and access to frequent actions more discoverable and accessible. Simply press CTRL + SHIFT + P from anywhere in the application to open the Quick Actions menu (and use the same shortcut or Esc to close the menu). The list of actions shown is…
-
Find Duplicates Training Data
Attached is data to be used with Find Duplicates Training.
-
Dashboard Custom content widget
The Dashboard Custom content widget allows you to add information such as text and links that might be useful to a customer such as details of their project integration and links to project docs. Example: This CAIS implementation uses version 4.0.0. See the release notes here:…
-
Partition Values (Dynamic SQL Partition Row Number)
A question that's cropped up for a couple of clients recently is how do I segment my data and split/partition it? Sample Data (Starting Point) In the above example I have records which I'd like to split or partition based on a value in a specific field (this could be a category, source, date etc). Before I go through the…
