Over the past few years, many organizations have experienced the benefits of migrating their SAP solutions to Google Cloud. But this migration can do more than reduce IT maintenance costs and make data more secure. By leveraging BigQuery, SAP customers can complement their SAP investments and gain fresh insights by consolidating enterprise data and easily extending it with powerful datasets and machine learning from Google. 

BigQuery is a leading cloud data warehouse, fully managed and serverless, and allows for massive scale, supporting petabyte-scale queries at super-fast speeds. It can easily combine SAP data with additional data sources, such as Google Analytics or Salesforce, and its built-in machine learning lets users operationalize machine learning models using standard SQL — all at a comparatively low cost. 

If your SAP-powered organization is looking to supercharge its analytics with the strength of BigQuery, read on for considerations and recommendations for modeling with SAP data. These guidelines are based on our real-world implementation experience with customers and can serve as a roadmap to the analytics capabilities your business needs.

Considerations for data replication 

Like most technology journeys, this one should start with a business objective. Keeping your intended business value and goals in mind is critical to making the right decisions in the early steps of the design process.

When it comes to replicating the data from an SAP system into BigQuery, there are multiple ways to do it successfully. Decide which method will work best for your organization by answering these questions:

• Does your business need real-time data? Will you need to time travel into past data?
• Which external datasets will you need to join with the replicated data?
• Are the source structures or business logic likely to change? Will you be migrating the SAP source systems any time soon? For instance, will you be moving from SAP ECC to SAP S/4HANA?

You’ll also need to determine whether replication should be done on a table-by-table basis or whether your team can source from pre-built logic. This decision, along with other considerations such as licensing, will influence which replication tool you should use.

Replicating on a table-by-table basis
Replicating tables, especially standard tables in their raw form, allows sources to be reused and ensures more stability of the source structure and functional output. For example, the SAP table for sales order headers (VBAK) is very unlikely to change its structure across different versions of SAP, and the logic that writes to it is also unlikely to change in a way that affects a replicated table. 

Something else to consider: Reconciliation between the source system and the landing table in BigQuery is linear when comparing raw tables, which helps avoid issues in consolidation exercises during critical business processes, such as period-end closing. Since replicated tables aren’t aggregated or subject to process-specific data transformation, the same replicated columns can be reused in different BigQuery views. You can, for instance, replicate the MARA table (the material master) once and use it in as many models as needed. 

Replicating pre-built logic
If you replicate pre-built models, such as those from SAP extractors or CDS views, you don’t need to build the logic in BigQuery, since you’re using existing logic. Some of these extraction objects have embedded delta mechanisms, which may complement a replication tool that can’t handle deltas. This will save initial development time, but it can also lead to challenges if you create new columns, or if customizations or upgrades change the logic behind the extraction. 

It’s also important to note that different extraction processes may transform and load the same source columns multiple times, which creates redundancy in BigQuery and can lead to higher maintenance needs and costs. However, replicating pre-built models may still be a good choice, since doing so can be especially useful for logic that tends to be immutable, such as flattening a hierarchy, or logic that is highly complex.

How you approach replication will also depend on your long-term plans and other key factors — for example, the availability (and curiosity) of your developers, and the time or effort they can put into applying their SQL knowledge to a new data warehouse. 

With either replication approach, bear in mind when designing your replication process that BigQuery is meant to be an append-always database — so post-processing of data and changes will be required in both cases. 

Processing data changes

The replication tool you choose will also determine how data changes are captured (known as CDC – change data capture). If the replication tool allows for it (for example as SAP SLT does) the same patterns described in the CDC with BigQuery documentation also apply to SAP data. 

Because some data, like transactions, are known to be less static than others (e.g., master data), you need to decide what should be scanned in real time, what will require immediate consistency, and what can be processed in batches to manage costs. This decision will be based on the reporting needs from the business.

Consider the SAP table BUT000, containing our example master data for business partners, where we have replicated changes from an SAP ERP system:

In an append-always replication in BigQuery, all updates are received as new records. For example, deleting a record in the source will be represented as a new record in BigQuery with a deletion flag. This applies to whether the records are coming from raw tables like BUT000 itself or pre-aggregated data, as from a BW extractor or a CDS view.

Let’s take a closer look at data coming particularly from the partners “LUCIA” and “RIZ”. The operation flag tells us whether the new record in BigQuery is an insert (I), update (U) or deletion (D), while the timestamps help us identify the latest version of our business partner.

If we want to find the latest updated record for the partners LUCIA and RIZ, this is what the query would look like:

  SELECT partner,
        ARRAY_AGG(i1 ORDER BY i1.recordstamp DESC LIMIT 1) AS row
FROM SAP_ECC.but000 i1 
WHERE partner in ('LUCIA','RIZ')
    GROUP BY partner

With the following result:

After identifying stale records for “LUCIA” and “RIZ” business partners, we can proceed to deleting all stale records for “LUCIA” if we do not want to retain the history. In this example, we are using a different table to which the same replication has been done, for the purpose of comparison and to check that all stale records have been deleted for the selection made and that we only kept last updated records. For example:

  DELETE SAP_HANA.but000 i1
WHERE
i1.recordstamp < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 HOUR) AND
i1.recordstamp < (SELECT MAX(recordstamp) FROM SAP_HANA.but000 i2
WHERE
i1.partner = i2.partner
and partner="LUCIA")

You can also use the following query to retrieve stale records for “LUCIA” partner before moving forward with deletion

  SELECT partner, operation_flag, recordstamp  FROM SAP_HANA.but000 i1
WHERE
i1.recordstamp < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 HOUR) 
AND
i1.recordstamp < (SELECT MAX(recordstamp) FROM SAP_HANA.but000 i2 
WHERE 
i1.partner = i2.partner
and partner="LUCIA")

Which produces all of the records, except the latest update:

Partitioning and clustering

To limit the number of records scanned in a query, save on cost and achieve the best performance possible, you’ll need to take two important steps: determine partitions and create clusters. 

Partitioning
A partitioned table is one that’s divided into segments, called partitions, which make it easier to manage and query your data. Dividing a large table into smaller partitions improves query performance and controls costs because it reduces the number of bytes read by a query.

You can partition BigQuery tables by:

• Time-unit column: Tables are partitioned based on a “timestamp,” “date,” or “datetime” column in the table.
• Ingestion time: Tables are partitioned based on the timestamp recorded when BigQuery ingested the data.
• Integer range: Tables are partitioned based on an integer column.

Partitions are enabled when the table is created, as in the example below.  A great tip is to always include the partition filter as shown on the left-hand side of the query.

Clustering
Clustering can be created on top of partitioned tables by applying the fields that are likely to be used for filtering. When you create a clustered table in BigQuery, the table data is automatically organized based on the contents of one or more of the columns in the table’s schema. The columns you specify are then used to colocate related data.

Clustering can improve the performance of certain query types — for example, queries that use filter clauses or that aggregate data. It makes a lot of sense to use them for large tables such as ACDOCA, the table for accounting documents in SAP S/4HANA. In this case, the timestamp could be used for partitioning, and common filtering fields such as the ledger, company code, and fiscal year could be used to define the clusters.

A great feature is that BigQuery will also periodically recluster the data automatically.

Materialized views

In BigQuery, materialized views are precomputed views that periodically cache the results of a query for better performance and efficiency. BigQuery uses precomputed results from materialized views and, whenever possible, reads only the delta changes from the base table to compute up-to-date results quickly. Materialized views can be queried directly or can be used by the BigQuery optimizer to process queries to the base table.

Queries that use materialized views are generally completed faster and consume fewer resources than queries that retrieve the same data only from the base table. If workload performance is an issue, materialized views can significantly improve the performance of workloads that have common and repeated queries. While materialized views currently only support single tables, they are very useful common and frequent aggregations like stock levels or order fulfillment.

Further tips on performance optimization while creating select statements can be found in the documentation for optimizing query computation.

Deployment pipeline and security

For most of the work you’ll do in BigQuery, you’ll normally have at least two delivery pipelines running — one for the actual objects in BigQuery and the other to keep the data staging, transforming, and updated as intended within the change-data-capture flows. Note that you can use most existing tools for your Continuous Integration / Continuous Deployment (CI/CD) pipeline — one of the benefits of using an open system like BigQuery. But, if your organization is new to CI/CD pipelines, this is a great opportunity to gradually gain experience. A good place to start is to read our guide for setting up a CI/CD pipeline for your data-processing workflow.  

When it comes to access and security, most end-users will only have access to the final version of the BigQuery views. While row and column-level security can be applied, as in the SAP source system, separation of concerns can be taken to the next level by splitting your data across different Google Cloud projects and BigQuery datasets. While it’s easy to replicate data and structures across your datasets, it’s a good idea to define the requirements and naming conventions early in the design process so you set it up properly from the start. 

Start driving faster and more insightful analytics

The best piece of advice we can give you is this: Try it yourself. Anyone with SQL knowledge can get started using the free BigQuery tier. New customers get $300 in free credits to spend on Google Cloud during the first 90 days. All customers get 10 GB storage and up to 1 TB queries/month, completely free of charge. In addition to discovering the massive processing capabilities, embedded machine learning, multiple integration tools, and cost benefits, you’ll soon discover how BigQuery can simplify your analytics tasks. 

If you need additional assistance, our Google Cloud Professional Services Organization (PSO) and Customer Engineers will be happy to help show you the best path forward for your organization. For anything else, contact us at cloud.google.com/contact.

Did you know that by 2025, enterprise IT spending on public cloud computing will overtake traditional IT spending? In fact, 51% of IT spend in application software, infrastructure software, business process services, and system infrastructure will transition to the public cloud, compared to 41% in 20221.. As enterprises continue to rapidly shift to the cloud, government agencies must prioritize and accelerate security and compliance implementation.

In May 2021, the White House issued an Executive Order requiring US Federal agencies to accelerate cloud adoption, embrace security best practices, develop plans to implement Zero Trust architectures, and map implementation frameworks to FedRAMP. The Administration’s focus on secure cloud adoption marks a critical shift to prioritizing cybersecurity at scale. Google Cloud’s Public Sector Professional Services Organization (PSO) has committed to helping customers meet security and compliance requirements in the cloud through specialized consulting engagements.

Accelerating Authority to Operate (ATO)

The Federal Risk and Authorization Management Program (FedRAMP) was established in 2011 as a government-wide program that promotes the adoption of secure cloud services across the federal government. FedRAMP provides a standardized approach to security and risk assessment for cloud technologies and federal agencies. US Federal agencies are required to utilize and implement FedRAMP cloud service offerings as part of the “Cloud First” federal cloud computing strategy.

While Google Cloud provides a FedRAMP-authorized cloud services platform and a robust catalog of FedRAMP-approved products and services (92 services and counting), customers are still tasked with achieving Agency ATO for the products and services they use, and Google Cloud provides many resources to assist customers with this journey. Google Cloud’s FedRAMP package can be accessed by completing the FedRAMP Package Access Request Form and submitting it to info@fedramp.gov. Additionally, customers can use Google’s NIST 800-53 ATO Accelerator as a starting point for documenting control implementation. Finally, Google Cloud’s Public Sector PSO offers the following strategic consulting engagements to help customers streamline the Agency ATO process.

• Cloud Discover: FedRAMP is a six-week interactive workshop to support customers that are just getting started with the ATO process on Google Cloud. Customers are educated on FedRAMP fundamentals, Google’s security and compliance posture, and how to approach ATO on Google Cloud. Through deep-dive interviews and design sessions, PSO helps customers craft an actionable ATO plan, assess FedRAMP readiness, and develop a conceptual ATO boundary. This engagement helps organizations establish a clear understanding and roadmap for FedRAMP ATO on Google Cloud.
• FedRAMP Security Review is a ten to twelve week engagement that aids customers in FedRAMP operational readiness. PSO consultants perform detailed FedRAMP architecture reviews to identify potential gaps in NIST 800-53 security control implementation and Google Cloud secure architecture best practices. Findings from the security reviews are shared with the customer along with configuration guidance and recommendations. This engagement helps organizations prepare for the third-party or independent security assessment that is required for FedRAMP ATO.
• Cloud Deploy: FedRAMP is a multi-month engagement designed to help customers document the details of their FedRAMP System Security Plan (SSP) and corresponding NIST 800-53 security controls, in preparation for Agency ATO on Google Cloud at FedRAMP Low, Moderate, or High. PSO collaborates with customers to develop a detailed technical infrastructure design document and security control matrix capturing evidence of the FedRAMP system architecture, security control implementation, data flows and system components. PSO can also partner with a third-party assessment organization (3PAO) or an independent assessor (IA) to support customer efforts for FedRAMP security assessment. This engagement helps customer system owners prepare for Agency ATO assessment and package submission.

Developing a Zero Trust Strategy

In addition to providing FedRAMP enablement, Public Sector PSO has partnered with the Google Cloud Chief Information Security Officer (CISO) team to assist organizations with developing a zero trust architecture and strategy.

Zero Trust Foundations is a seven-week engagement co-delivered by Google Cloud’s CISO and PSO teams. CISO and PSO educate customers on zero trust fundamentals, Google’s journey to zero trust through BeyondCorp, and defense in depth best practices. The CISO team walks customers through a Zero Trust Assessment (ZTA) to understand the organization’s current security posture and maturity. Insights from the ZTA enable the CISO team to work with the customer to identify an ideal first-mover workload for zero trust adoption. Following the CISO ZTA, PSO facilitates a deep-dive Zero Trust Workshop (ZTW), collaborating with key customer stakeholders to develop a NIST 800-207 aligned, cloud-agnostic zero trust architecture for the identified first-mover workload. The zero trust architecture is part of a comprehensive zero trust strategy deliverable that is based on focus areas called out in the Office of Management and Budget (OMB) Federal Zero Trust Strategy released January 2022.

Scaling Secure Cloud Adoption with PSO

Public Sector PSO enables customer success by sharing our technical expertise, providing cloud strategy, implementation guidance, training and enablement using our proven methodology. As enterprise IT, operations, and organizational models continue to evolve, our goal is to help government agencies accelerate their security and compliance journeys in the cloud. To learn more about the work we are doing with the federal government, visit cloud.google.com/solutions/federal-government.

1 Gartner Says More Than Half of Enterprise IT Spending in Key Market Segments Will Shift to the Cloud by 2025

Retail will forever be an industry that must constantly reinvent itself in response to, and anticipation of, ever-changing consumer demands.

Digital transformation is fueling these changes and we’ve previously spoken about how businesses including Ulta Beauty and Kohl’s are taking advantage of Google Cloud to put data at the center of what they do and deliver the best possible shopping experience and product offerings for their customers.

Leveraging Google Cloud machine learning platform, Sainsbury is able to develop predictive analytics models to spot trends and adjust inventory, providing shoppers with a better experience. 

Sainsbury’s, one of Britain’s best-known supermarkets, is another great example of a business transforming the way it engages with its customers with the cloud.

With over 150 years of service, Sainsbury’s vision is to be the most trusted retailer, where people love to work and shop. It makes customers’ lives easier, by offering great quality and service at fair prices. 

The food industry and the way that customers shop is rapidly changing. From foodie hashtags on Instagram, to the latest cooking fads, customers want to stay connected to the latest trends and Sainsbury’s is empowering them do that.

To help Sainsbury’s achieve this goal, its Commercial and Technology teams, in partnership with Accenture, are building cutting-edge machine learning solutions on Google Cloud Platform (GCP) to provide new insights on what customers want and the trends driving their eating habits.

With the help of Google Cloud Platform, we are generating new insights into how the world eats and lives, to help us stay ahead of market trends and provide an even better shopping experience for our customers.
–Phil Jordan, Group CIO, Sainsbury’s 

Sainsbury’s solution relies on data from multiple structured and unstructured sources. Using Google Cloud’s powerful cloud-based analytics tools to ingest, clean and classify that data, and a custom-built front-end interface for internal users to seamlessly navigate through a variety of filters and categories, Sainsbury’s is able to gain advanced insights in real time.

As a result, Sainsbury’s has been able to develop predictive analytics models to spot trends and adjust inventory, providing shoppers with a better experience. 

Phil Jordan, Group CIO of Sainsbury’s believes this project will have a big impact.

“The grocery market continues to change rapidly. We know our customers want high quality at great value and that finding innovative and distinctive products is increasingly important to them. With the help of Google Cloud Platform, we are generating new insights into how the world eats and lives, to help us stay ahead of market trends and provide an even better shopping experience for our customers.” 

This project is also a great example of the successes Google Cloud customers have when they work with the company’s partners.

“We’re delighted to partner with Google Cloud to help the Sainsbury’s Commercial team apply predictive analytics to the identification of new and emerging trends in grocery,” says Adrian Bertschinger, Managing Director for Retail, Accenture.

“The food sector is experiencing significant, rapid disruption, and this new, cloud-based insights platform will help Sainsbury’s identify trends much earlier and adapt their product assortment in a faster, more informed way—all for the benefit of customers.” 

Whatever the next food or shopping trend may be, Sainsbury’s is looking to the cloud to help them stay a step ahead.