The last few years have underscored the importance of speed in bringing new drugs and medical devices to market, while ensuring safety and efficacy. Over this time, healthcare and life sciences organizations have transformed the way they research, develop, and deliver patient care by embracing agility and innovation.

Now, the industry is set to reap the benefits of cloud technology and overcome the existing barriers to innovation.

What’s holding back innovation?

Costly clinical trials: The process of trialing and developing new drugs and devices is still long and costly, with more than 1 in 5 clinical trials failing due to a lack of funding.1 The high failure rate comes as no surprise when you consider the average clinical trial costs $19 million and takes 10-15 years (through all 3 phases) to be approved.2

Stringent security requirements: Pre-clinical R&D and clinical trials use large volumes of highly sensitive patient data – making the life sciences industry one of the top sectors targeted by hackers.3 On top of this, the FDA and other regulatory bodies have strict requirements for medical device cybersecurity.

Unpredictable supply chains: Global supply chains are becoming increasingly complex and unpredictable. This can be brought on by anything from supply shortages, to geo-political events, and even bad weather. Making things worse is the lack of visibility into medical shipment disruptions – so when disaster strikes you’re often caught off guard.

Google Cloud for life sciences

At Alphabet, we’ve made significant investments in healthcare and life sciences, helping to tackle the world’s biggest healthcare problems, from chronic disease management, to precision medicine, to protein folding.

Together with Google, you can transform your life sciences organization and deliver secure, data-driven innovation across the value chain.

• Accelerate clinical trials to deliver life-saving treatments faster and at less cost. Clinical trials require relevant and equitable patient cohorts that can produce clinically valid data. Solutions like DocAI can enable optimal patient matching for clinical trials, helping organizations optimize clinical trial selection and increase time to value. How that patient data is collected is also important. Collection in a physician’s office captures a snapshot of the participant’s data at one point in time and doesn’t necessarily account for daily lifestyle variables. Fitbit, used in more than 1,500 published studies–more than any other wearable device–can enrich clinical trial endpoints with new insights from longitudinal lifestyle data, which can help improve patient retention and compliance with study protocols. We have introduced Device Connect for Fitbit, which empowers healthcare and life sciences enterprises with accelerated analytics and insights to help people live healthier lives. We are able to empower organizations to improve clinical trials in key ways:

1. Enable clinical trial managers to quickly create and launch mobile and web RWE collection mechanism for patient reported outcomes
2. Enable privacy controls with Cloud Healthcare Consent API and, as needed, remove PHI using Cloud Healthcare De-identification API
3. Ingest RWE and data into BigQuery for analysis
4. Leverage Looker to enable quick visualization and powerful analysis of a study’s progress and results

• Ensure security and privacy for a safe, coordinated, and compliant approach to digital transformation. Google Cloud offers customers a comprehensive set of services including pioneering capabilities such as BeyondCorp Enterprise for Zero Trust and VirusTotal for malicious content and software vulnerabilities; Chronicle’s security analytics and automation coupled with services such as Security Command Center to help organizations detect and protect themselves from cyber threats; as well as expertise from Google Cloud’s Cybersecurity Action Team. Google Cloud also recently acquired Mandiant, a leader in dynamic cyber defense, threat intelligence and incident response services.

• Optimize supply chains and enhance your data to prepare for the unpredictable. With a digital supply chain platform, we can empower supply chain professionals to solve problems in real time including visibility and advanced analytics, alert-based event management, collaboration between teams and partners, and AI-driven optimization and simulation.

Ready to learn more? We’ll be taking a deep dive into each of the challenges outlined above in our life sciences video series. Stay tuned.

1. National Library of Medicine
2. How much does a clinical trial cost?
3. Life Sciences Industry Becomes Latest Arena in Hackers’ Digital Warfare

Google Cloud has a genuine passion for solving technology problems that make a difference for our customers. With the release of our BigQuery Connector for SAP, we’re taking a another big step towards solving a major challenge for SAP customers with a quick, easy, and inexpensive way to integrate SAP data with BigQuery, our serverless, highly scalable, and cost-effective multi cloud data warehouse designed for business agility.

Solving for simplified data integration

Like most businesses today, SAP customers are eager to unlock the immediate insights and opportunities within their ever-growing stores of business data. However, many are discovering just how hard it can be to take the first step in any modern, cloud-enabled data analytics strategy: combining SAP data with other cloud-native, and enterprise data sets in real-time and at scale. According to a 2020 SAPInsider study, more than half of SAP customers surveyed said data integration was their top analytics pain point. These companies urgently need a rapid, sustainable, cost-effective and scalable way to integrate SAP data with modern cloud data analytics solutions.

The BigQuery Connector for SAP gives our customers a solution: a fast, simple, cost-effective and massively scalable way to make SAP data fully accessible within BigQuery by leveraging customers’ existing SAP Landscape Transformation Replication Server (SLT) tooling and skill sets. It’s the first SAP SLT direct near real-time connector for BigQuery without the need to set up additional infrastructure or third-party middleware, and can be deployed using a variety of embedded or stand-alone deployment options. In fact, most customers can install the BigQuery Connector for SAP in less than an hour—a remarkably easy way to start working with our industry-leading analytics solution that delivers proven and quantifiable business advantages for customers. Additionally, the BigQuery Connector for SAP is not restricted to customers who have deployed their SAP applications on Google Cloud. Customer’s who are running their SAP applications on-premise, or on any cloud, can also deploy and realize the analytical benefits of the solution.

Designing a solution with customer requirements and investments in mind

When the Google Cloud team started work on an analytics data integration tool for our SAP customers, we began with a set of requirements designed to root out the usual sources of cost and complexity. These included: 

• The need for real-time performance with deltas replicated in milliseconds
• The ability to integrate data from almost any SAP Netweaver based application running today, regardless of its deployment location (on premises, any cloud, Google Cloud)
• Automatic BigQuery data type mapping with minimal transformation required
• Generation of target tables in BigQuery directly from source, if required 
• Application layer integration that avoids the issues of direct database access
• Leveraging customers’ existing SAP skillsets, change data capture, and infrastructure

An important step towards meeting these requirements came when Alphabet, Google’s parent company, decided to leverage SAP SLT as a foundation for developing direct data replication between SAP and BigQuery for its internal corporate landscape. SLT as part of SAP’s strategic Business Technology Platform, supports real-time replication of data from SAP or third-party systems to SAP HANA, however, one of its limitations was direct integration with targets like BigQuery. 

SAP SLT was a logical foundation for developing the connector for several reasons: 

• It’s widely adopted among SAP customers who likely already leverage SLT for SAP analytics data integration
• It works with almost every non-SaaS SAP application environment running today
• It supports real-time replication performance at massive scale

It was an obvious choice for the Alphabet engineering team who saw immediate value from integrating SAP with BigQuery. 

“The BigQuery Connector for SAP has enabled fast, low latency data replication for billions of records from 500+ tables of our most critical financial and supply chain data. Now in one cost-effective BigQuery data lake, this ERP data can be combined with other data sources for previously impossible real-time analytics and ML use cases. This allows us to drive much deeper strategic insights that support business and operational excellence, management and P&L reporting and more.”—Anil Nagalla, Sr. Engineering Director, Financial Systems, Google

SAP data integration with BigQuery enables new value

By leveraging SAP SLT, the BigQuery Connector for SAP can integrate real-time data streams from any SAP system—while also taking advantage of customers’ existing SAP investments and skillsets. 

At the same time, the BigQuery Connector for SAP does a lot of heavy lifting on its own. For example, it automatically handles the complex, multi-step process of transforming SAP data types for use in BigQuery—mapping data-type transitions between the SAP and BigQuery environments, creating a target table schema on BigQuery for the transformed data types, building the target BigQuery table, and even adapting as new data types appear in your SAP environment.

For teams that want to fine-tune the BigQuery Connector for SAP’s automated recommendations, the connector supports additional levels of customization and choice. But if you simply want to get the job done and give your data analytics team greater support for their high-value work, then you’ll love just how quickly and easily the BigQuery Connector for SAP turns the complicated work of data integration and performance to process large volumes of data into a done deal. By integrating enterprise data sets in real time, customers can drive differentiated value and unlock new insights and actions that drive a competitive advantage. 

The BigQuery Connector for SAP really shines as an enabling tool that transports and transforms your SAP data to power analytics solutions enabled by accelerators like the Google Cloud Cortex Framework: a comprehensive set of reference architectures, deployment accelerators, and integration services designed to give SAP customers a fast and seamless path to value with their data analytics investments. Simply put, the more SAP data you make available within Google Cloud, the easier it is to get meaningful—and often game-changing—insights from these solutions.

Learn more about the BigQuery Connector for SAP

Ready to get started with your own SAP data analytics strategy on Google Cloud? Install the Google Cloud BigQuery Connector for SAP, and discover a faster, simpler, more sustainable way to power your company’s data analytics strategy.

Over the last decade, financial service organizations have been adopting a cloud-first mindset. According to InformationWeek, lower costs and enhanced scalability were the biggest drivers for cloud adoption in financial services, and cloud-native applications allow access to the latest technology and talent, enabling adopters to rebuild transaction processing systems capable of supporting very high volumes and low latency.

Both Neo4j and Google Cloud have been using relationship-based data representations since the beginning, and we’re dedicated to using this technology to help financial services customers drive business transformation. We are excited about the prospects of financial services (FinServ) cloud systems and believe that graph data in the cloud can help solve significant challenges in the industry.

Data Challenge #1: Risk Management and Compliance

First among the top concerns for any CIO moving to the cloud is risk management and compliance. Disconnected, uncontextualized, or stale data create opportunities for fraud and financial crimes to occur. The fact is when it comes to FinServ, the question is not “if” but rather how often an attack will occur.  Unfortunately, incidents have been trending upward over the last decade, and COVID has only exacerbated this reality. Financial crimes affect the bottom line both in the remediation of these crimes and in intangibles like brand value.  

Add to this the complexity of international banking, which makes “compliance” a moving target. Penalties due to noncompliance are a constant concern to any FinServ organization.

The tabular representation of information with a fixed number of columns that never change prevents a description of an ever changing world with changing characteristics. Relational databases are great if the world you describe does not move fast but have limitations when data structures are highly interlinked and not homogeneous.

Neo4j Aura on Google Cloud provides a foundation for creating dynamic, futureproof, scalable applications that adhere to the security standards and protocols today’s financial services organizations require to meet the challenges of finding and preventing bad actors. This also includes enterprise scalability; reaching over 1 Billion nodes and relationships to streamline queries and provide solutions that meet regulatory and privacy compliance across geographies. Neo4j has helped some organizations save billions of USD in fraud in the first year of deployment alone.  

What makes graph technology the best choice for fraud detection use cases is that the relationships between the data-points are as important as the data-points themselves. Let’s take as an example, one John Smith approaches a multi-national banking institution to manage the primary account for his new holding corporation.  

While no one has any record of John R Smith Holdings LLC, the bank’s application built on graph technology understands that there are several well-known entities owned by John Smith Holdings. The application also identifies several well-known board members who bank with this institution. Due to this relationship-driven approach, the bank now understands John R Smith is not “John Smith,” who previously attempted to open an account for his holding corporation, which had no information associated with it prior to two months ago.

Data Challenge #2 Manual Processes and Inefficiencies 

The ubiquity of the cloud offers an opportunity to deploy automation at unprecedented levels to tackle the errors and inefficiencies that manual processing allows to creep into processes. When data comes from disparate, perhaps legacy systems – which may have become siloed and “untouchable” over the years – further complexity arises. As an example, if someone in sales types “John Smith” into a CRM system not knowing that John R Smith is the spelling in the customer data master, it may result in two separate and potentially conflicting records. Being able to join those records together in a mastered view helps to solve this problem. In addition, low data quality equates to an increase in risk, costs, and implementation times for new systems. 

Neo4j Aura on Google Cloud provides automation and artificial intelligence (AI) that reduces manual processes and the errors that accompany them. In this graph architecture each node, which can represent a person, will have labels, relationships, and properties associated with it. This allows for the use of AI which can easily understand that John Smith in the CRM is the same John R Smith in the customer master. The information contained in Neo4j can be connected bi-directionally to ensure consistency across applications and data sources. 

One of the benefits of this approach is that linking information allows organizations to keep the full value of the data, rather than forcing the data into predetermined tabular representations, with the risk of losing valuable information and insights.

Data Challenge #3: Customer Engagement and Insight

Another significant concern is the high expectations today’s customers have for every interaction. End users are accustomed to predictable experiences on their digital devices, and FinServ apps are no exception. Added to this, the “Covid economy” has driven digital adoption significantly across demographics; even among customers who might traditionally have used in-person services. This also equates to increased expectations for personalized, predictable experiences with every digital interaction. We know that latency has always been a key consideration for financial trading, but a recent ComputerWeekly study showed that every financial organization should ensure their visible latency is at 10 milliseconds or less. Customers no longer accept their broadband is at fault.

Finally, blind spots in the customer journey often result in dissatisfaction, which ultimately leads to increased churn. Without gaining actionable insights from your customers, there is no room to innovate and iterate on what they are looking for in your products and services. And this translates to losing market share and competitive advantage.

The NoSQL architecture, specifically the dynamic schema and structure of Neo4j Aura gives you the ability to take charge of your data and make changes according to your development cycles or newer data models. This equates to faster builds, more comprehensive releases and a wider, richer data-set that can be contextualized and understood instantly. Graph technology is the logical choice for building a Customer 360 application. Under this approach organizations not only get valuable insight into the individual client’s behavior and patterns, but also those of their family, friends and colleagues. This allows for stronger personalization, targeted campaigns and successful execution, resulting in increased customer satisfaction and retention levels.

Graph Technology on Google Cloud

Neo4j is a recognized leader in graph database technology and the only fully integrated graph solution on Google Cloud, helping to fill a common need for Google Cloud customers. Both Neo4j and Google Cloud are invested in continuing to grow our partnership and mutual product direction.  

You can find and deploy the Neo4j graph database straight from the Google Cloud marketplace, whether you want to download the software for an on-premises deployment, use the virtual machine image, or use the hosted solution, Aura on Google Cloud, the graph database-as-a-service. In any deployment, you get the same enterprise-grade scalability, reliability, and connectivity along with successful, repeatable use cases you can rely on to resolve your particular challenges and integrated billing. 

For a real-world example of how graph technology can optimize financial services, you can read our Case Study with fintech Current. Current, a leading U.S. financial technology platform with over three million members, used Neo4j Aura on Google Cloud to create a personalization engine based on client relationships. 

To learn more about Neo4j Aura on Google Cloud for FinServ organizations, register for our webinar on Thursday, December 16 with Jim Webber, Chief Scientist, CTO Field Ops at Neo4j and Antoine Larmanjat, Technical Director, Office of the CTO, Google Cloud. 

Click here to Register

Why do we need a BigQuery anti-pattern recognition tool?

BigQuery is a serverless and cost-effective enterprise data warehouse that works across cloud environments and scales with your data. As with any large scale data-intensive platform, following best practices and avoiding inefficient anti-patterns goes a long way in terms of performance and cost savings. 

Usually SQL optimization requires a significant time investment from engineers, who must read high-complexity queries, devise a variety of approaches to improve performance and efficiency, and test several optimization techniques. The best place to start is to fix anti-patterns, since this only requires easily applicable changes and provides significant performance improvements.

To facilitate the task of identifying and fixing said anti-pattern, Google Professional Services Organization (PSO) and Global Services Delivery (GSD) have developed a BigQuery anti-pattern recognition tool. This tool automates the process of scanning SQL queries, identifying antipatterns, and providing optimization recommendations.

What is the BigQuery anti-pattern recognition tool?

The BigQuery anti-pattern recognition tool let you easily identify performance impacting anti-patterns across a large number of SQL queries in a single go. 

It utilizes ZetaSQL to parse BigQuery SQL queries into abstract syntax trees (AST) and then traverses the tree nodes to detect the presence of anti-patterns. 

The tool takes a BigQuery SQL query as an input, such as:

SELECT
 t.dim1,
 t.metric1
FROM
 `dataset.table` t
WHERE
 t.id NOT IN (
 SELECT
   id
 FROM
   `dataset.table2`)

And produces the output as:

Subquery in filter without aggregation at line 8.

It examines potential optimizations, including:

• Selecting only the necessary columns
• Handling multiple WITH-clause references 
• Addressing subqueries in filters with aggregations
• Optimizing ORDER BY queries with LIMIT
• Enhancing string comparisons
• Improving JOIN patterns
• Avoiding subquery aggregation in the WHERE clause

The solution supports reading from various sources, such as:

• Command line 
• Local files
• Cloud Storage files
• Local folders
• Cloud Storage folders
• CSV (with one query per line)
• INFORMATION_SCHEMA

Additionally, the solution provides flexibility in writing output to different destinations, including:

• Printing to the terminal
• Exporting as CSV

Writing to a BigQuery table

Using the BigQuery anti-pattern recognition tool

The BigQuery anti-pattern recognition tool is hosted on GitHub. Below are the Quick Start steps on using the tool via command line for inline queries. You can also leverage Cloud Run to deploy it as a container on cloud. 

Prerequisites

• Linux OS
• JDK 11 or above is installed
• Maven
• Docker
• gcloud CLI

Quick start: – steps

1. Clone the repo into your local machine.

git clone git@github.com:GoogleCloudPlatform/bigquery-antipattern-recognition.git

2. Build the tool image inside the `bigquery-antipattern-recognition` folder.

mvn clean package jib:dockerBuild -DskipTests

3. Run the tool for a simple inline query.

docker run \
  -i bigquery-antipattern-recognition \
  --query "SELECT * FROM \`project.dataset.table1\`"

4. Below is the output result in the command-line interface:

Additionally, the above tool can read queries from Information Schema and load the output recommendations to a BigQuery table.

Below is an example of the BigQuery anti-pattern recognition tool results exported to a BigQuery table.

Getting started

Ready to start optimizing your BigQuery queries and cutting costs? Check out the tool here and contribute to the tool via GitHub.

Have questions or feedback?

We’re actively working on new features to make the tool as useful to our customers. Use it and tell us what you think! For product feedback/technical questions, reach out to us at bq-antipattern-eng@google.com. If you’re already a BigQuery customer and would like a briefing on the tool, please reach out, we’d be happy to talk.