Many of the world’s leading companies run on SAP—and deploying it on Google Cloud extends the benefits of SAP even further. Migrating your current SAP S/4HANA deployment to Google Cloud—whether it resides on your company’s on-premises servers or another cloud service—provides your organization with a flexible virtualized architecture that lets you scale your environment to match your workloads, so you pay only for the compute and storage capacity you need at any given moment. Google Cloud includes built-in features, such as Compute Engine live migration and automatic restart, that minimize downtime for infrastructure maintenance. And it allows you to integrate your SAP data with multiple data sources and process it using Google Cloud technology such as BigQuery to drive data analytics.

SAP server-side architecture consists of two layers: the SAP HANA database, and the Netweaver application layer. In this blog post, we’ll look at the options and steps for moving the database layer to Google Cloud as a lift and shift or rehost, a straightforward approach that entails moving your current SAP environment unchanged onto Google Cloud.

Deploying an SAP HANA system on Google Cloud

Google Cloud offers SAP-certified virtual machines (VMs) optimized for SAP products, including SAP HANA and SAP HANA Enterprise Cloud, as well as dedicated servers for SAP HANA for environments greater than 12TB. (For a complete list of VM and hardware options, visit the Certified and Supported SAP HANA Hardware Directory.)

Before proceeding with a rehost migration to Google Cloud, your current (source) environment and Google Cloud (target) environments should meet these specifications:

Prerequisites:  

• The configuration of the Google Cloud environment (i.e., VM  resources, SSD storage capacity) should be identical to that of the source environment. If the underlying hardware is different, however, you must use Option 2 for your migration, detailed below.
• Both environments should be running the same operating system (SUSE or RHEL Linux).
• The HANA version, instance number, and system ID (SID) should be identical.

• Schema names must remain the same.
• Establishing the network connection between the on-premises environment and Google Cloud will be required in this phase to support rehost of the SAP application.you can use Cloud VPN or Dedicated Interconnect. Learn more about Dedicated Interconnect and Cloud VPN.

Note: Depending on your internet connection and bandwidth requirements, we recommend using a Dedicated Interconnect over Cloud VPN for production environments. 

We offer a number of automated processes to accelerate your cloud journey. To deploy the SAP HANA system on Google Cloud, you can use the Google Cloud Deployment manager or Terraform and Ansible scripts available on GitHub with configuration file templates to define your installation. For more details, see the Google Cloud SAP HANA Planning Guide.

Note: To deploy SAP HANA on Google Cloud machine types that are certified by SAP for production, please review the Certification for SAP HANA on Google Cloud page. 

Moving an SAP HANA Database to Google Cloud

There are two different options you can use to rehost your SAP HANA database to Google Cloud, and each has pros and cons that you should consider when deciding on your approach.

Option 1: Asynchronous replication uses SAP’s built-in replication tool to provide continuous data replication from the source system (also known as the primary system) to the destination or secondary system—in this case residing on Google Cloud. It’s best for mission-critical applications for which minimum downtime is a high priority, and for large databases. In addition, the high level of automation means that the process requires less manual intervention. Here’s where you can learn more on HANA Asynchronous Replication.

Option 2: Backup and restore relies on SAP’s backup utility to create an image of the database that is then transferred to Google Cloud, where it is restored in the new environment. Downtime for this method varies by database size, so large databases may require more downtime via this method vs. asynchronous replication. It also involves more manual tasks. However, it requires fewer resources to perform, making it an attractive option for less urgent use cases. Here’s where you can learn more on SAP HANA database Backup and restore.

How to migrate the SAP HANA database to Google Cloud using Asynchronous Replication

1. Create and configure Dedicated Interconnect or Cloud VPN between the current environment and Google Cloud.
2. Set up SAP HANA asynchronous replication. You can configure system replication using SAP HANA Cockpit, SAP HANA Studio, or hdbnsutil. See Setting Up SAP HANA System Replication in the SAP HANA Administration Guide.
3. Be sure to use the same instance number and HANA SID in the template as the primary instance.
4. Configure the Google Cloud instance as the secondary node for using HANA Asynchronous replication.
5. Perform data validation once full data replication is completed to the SAP HANA database in Google Cloud. To learn more: HANA System Replication overview.  
6. Perform an SAP HANA takeover on your standby database. This switches your active system from the current primary system onto the secondary system on Google Cloud. Once the takeover command runs, the system on Google Cloud becomes the new primary system.To learn more: HANA Takeover. 

How to migrate the SAP HANA database to Google Cloud using Backup and Restore

1. Create a full backup of your SAP HANA database in your current environment.
2. Create a new storage bucket in your Google Cloud environment. Visit Creating Storage Buckets in the Google Cloud Storage documentation. 
3. Download and install gsutil onto the source environment and run it to upload the HANA backup to the Google Cloud storage bucket. To install gsutil utility on any computer or server, visit Install gsutil in the Google Cloud Storage documentation.
   Note: You can run parallel multi thread/multi processing in gsutil to copy large files more quickly.
4. Recover the HANA database on Google Cloud using SAP’s RECOVER DATABASE statement. See RECOVER DATABASE Statement (Backup and Recovery) in the SAP HANA SQL Reference Guide for SAP HANA Platform.

Note: BackInt agent is an integrated SAP interface tool used for HANA database on Google Cloud.Backint agent for SAP HANA can be used to store and retrieve backups directly from Google Cloud Storage. It is supported and certified by SAP on Google Cloud. To learn more:  SAP HANA Backint Agent on Google Cloud. 

In summary, we recommend using Asynchronous Replication (Option 1) for mission-critical applications that require the lowest downtime window. For all other applications, we recommend Backup and Restore (Option 2), as this approach requires fewer resources. It’s also a great way to implement the backup and restore functionality on Google Cloud.

A rehost migration is the most straightforward path to getting your SAP on HANA system up and running on Google Cloud. And the sooner you migrate, the sooner you can take advantage of the many benefits Google Cloud brings to your SAP solution. For more information on the different migration options please review: SAP on Google Cloud: Migration strategies. 

Learn more about deploying SAP on Google Cloud. Technical resources can be found here.

Geospatial data is a critical component for a comprehensive analytics strategy. Whether you are trying to visualize data using geospatial parameters or do deeper analysis or modeling on customer distribution or proximity, most organizations have some type of geospatial data they would like to use – whether it be customer zipcodes, store locations, or shipping addresses. However, converting geographic data into the correct format for analysis and aggregation at different levels can be difficult. In this post, we’ll walk through some examples of how you can leverage the Google Cloud platform alongside Google Cloud Public Datasets to perform robust analytics on geographic data. The full queries can be accessed from this notebook here. 

Public US Geo Boundaries dataset

BigQuery hosts a slew of public datasets for you to access and integrate into your analytics. Google pays for the storage of these datasets and provides public access to the data via the bigquery-public-data project. You only pay for queries against the data. Plus, the first 1 TB per month is free! These public datasets are valuable on their own, but when joined against your own data they can unlock new analytics use cases and save the team a lot of time. 

Within the Google Cloud Public Datasets Program there are several geographic datasets. Here, we’ll work with the geo_us_boundaries dataset, which contains a set of tables that have the boundaries of different geospatial areas as polygons and coordinates based on the center point (GEOGRAPHY column type in BigQuery), published by the US Census Bureau.

Mapping geospatial points to hierarchical areas

Many times you will find yourself in situations where you have a string representing an address. However, most tools require lat/long coordinates to actually plot points. Using the Google Maps Geocoding API we can convert an address into a lat/long and then store the results in the BigQuery table. 

With a lat/long representation of our point, we can join our initial dataset back onto any of the tables here using the ST_WITHIN function. This allows us to check and see if a point is within the specified polygon. 

ST_WITHIN(geography_1, geography_2)

This can be helpful for ensuring standard nomenclature; for example, metropolitan areas that might be named differently. The query below maps each customers’ address to a given metropolitan area name.

  SELECT 
   cust.id as customer_id, 
   metro.name as metro_name 
FROM `looker-private-demo.retail.customers` as cust
,`bigquery-public-data.geo_us_boundaries.metropolitan_divisions` as metro
WHERE ST_WITHIN(ST_GEOGPOINT(cust.longitude, cust.latitude),metro.metdiv_geom)

It can also be useful for converting to designated market area (DMA), which is often used in creating targeted digital marketing campaigns.

  SELECT 
   cust.id as customer_id, 
   dma.dma_name 
FROM `looker-private-demo.retail.customers` as cust
,`bigquery-public-data.geo_us_boundaries.designated_market_area` as dma
WHERE ST_WITHIN(ST_GEOGPOINT(cust.longitude, cust.latitude),dma.dma_geom)

Or for filling in missing information; for example, some addresses may be missing zip code which results in incorrect calculations when aggregating up to the zipcode level. By joining onto the zip_codes table we can ensure all coordinates are mapped appropriately and aggregate up from there.

  SELECT 
   zip.zip_code, 
   count(distinct cust.id) as unique_customers
FROM `looker-private-demo.retail.customers` as cust
,`bigquery-public-data.geo_us_boundaries.zip_codes` as zip
WHERE ST_WITHIN(ST_GEOGPOINT(cust.longitude, cust.latitude),zip.zip_code_geom)
GROUP BY 1

Note that the zip code table isn’t a comprehensive list of all US zip codes, they are zip code tabulation areas (ZCTAs). Details about the differences can be found here. Additionally, the zip code table gives us hierarchical information, which allows us to perform more meaningful analytics. One example is leveraging hierarchical drilling in Looker. I can aggregate my total sales up to the country level, and then drill down to state, city and zipcode to identify where sales are highest. You can also use the BigQuery GeoViz tool to visualize geospatial data!

Aside from simply checking if a point is within an area, we can also use ST_DISTANCE to do something like find the closest city using the centerpoint for the metropolitan area table. 

  SELECT 
cust.id as customer_id, 
ARRAY_AGG(
  metro.name order by ST_DISTANCE(
   ST_GEOGPOINT(cust.longitude, cust.latitude),
  metro.internal_point_geom) asc limit 1)[offset(0)] as metro_name
FROM
`looker-private-demo.retail.customers` as cust
,`bigquery-public-data.geo_us_boundaries.metropolitan_divisions` as metro
GROUP BY cust.id

This concept doesn’t just hold true for points, we can also leverage other GIS functions to see if a geospatial area is contained within areas that are listed in the boundaries datasets. If your data comes into BigQuery as a GeoJSON string, we can convert it to a GEOGRAPHY type using the ST_GEOGFROMGEOJSON function. Once our data is in a GEOGRAPHY type we can do things like check to see what urban area the geo is within – using either ST_WITHIN or ST_INTERSECTS to account for partial coverage. Here, I am using the customer’s zip code to find all metropolitan divisions where the zip code polygon and the metropolitan polygon intersect. I am then selecting the metropolitan area that has the most overlap (or the intersection has the largest area) to be the customer’s metro that we use for reporting.

  SELECT 
   cust.id as customer_id, 
   ARRAY_AGG(
      metro.name order by ST_AREA(
        ST_INTERSECTION(zip.zip_code_geom,metro.metdiv_geom)
      ) desc limit 1)[offset(0)] as metro_name  
FROM
`looker-private-demo.retail.customers` as cust
JOIN `bigquery-public-data.geo_us_boundaries.zip_codes` as zip on      cust.zip=zip.zip_code
,`bigquery-public-data.geo_us_boundaries.metropolitan_divisions` as metro
WHERE ST_INTERSECTS(zip.zip_code_geom,metro.metdiv_geom)
GROUP BY cust.id

The same ideas can be applied to the other tables in the dataset including the county, urban areas and National Weather Service forecast regions (which can also be useful if you want to join your datasets onto weather data).

Correcting for data discrepancy

One problem that we may run into when working with geospatial data is that different data sources may have different representations of the same information. For example, you might have one system that records state as a two letter abbreviation and another using the full name. Here, we can use the state table to join the different datasets.

  SELECT 
   st.state_name, 
   sum(ab.sales+fn.sales) as total_sales 
FROM `bigquery-public-data.geo_us_boundaries.states` as st
LEFT JOIN abbreviated_table as ab on ab.state = st.state
LEFT JOIN fullname_table as fn on fn.state = st.state_name
WHERE COALESCE(ab.state, fn.state) IS NOT NULL
GROUP BY 1

Another example might be using the tables as a source of truth for fuzzy matching. If the address is a manually entered field somewhere in your application, there is a good chance that things will be misspelled. Different representations of the same name may prevent tables from joining with each other or lead to duplicate entries when performing aggregations. Here, I use a simple Soundex algorithm to generate a code for each county name, using helper functions from this blog post. We can see that even though some are misspelled they have the same Soundex code.

Next, we can join back onto our counties table so we make sure to use the correct spelling of the county name. Then, we can simply aggregate our data for more accurate reporting. 

  SELECT
 c.county_name,
 sum(sales) as total_sales
FROM
 table
 JOIN `bigquery-public-data.geo_us_boundaries.counties` as c
 on testing.dq_fm_Soundex(table.county) = testing.dq_fm_Soundex(c.county_name)
WHERE c.state_fips_code = cast(36 as string)
GROUP BY 1

Note that fuzzy matching definitely isn’t perfect and you might need to try different methods or apply certain filters for it to work best depending on the specifics of your data.

The US Geo Boundary datasets allow you to perform meaningful geographic analysis without needing to worry about extracting, transforming or loading additional datasets into BigQuery. These datasets, along with all the other Google Cloud Public Datasets, will be available in the Analytics Hub. Please sign up for the Analytics Hub preview, which is scheduled to be available in the third quarter of 2021, by going to g.co/cloud/analytics-hub.