How Thermo Fisher Scientific integrated Microsoft Power Apps with data lake resources on AWS to enable automated self-service platform

About Thermo Fisher Scientific

Thermo Fisher Scientific Inc. is the world leader in serving science, with annual revenue of approximately $40 billion. Our Mission is to enable our customers to make the world healthier, cleaner and safer. Whether our customers are accelerating life sciences research, solving complex analytical challenges, increasing productivity in their laboratories, improving patient health through diagnostics or the development and manufacture of life-changing therapies, we are here to support them. Our global team delivers an unrivaled combination of innovative technologies, purchasing convenience and pharmaceutical services through our industry-leading brands, including Thermo Scientific, Applied Biosystems, Invitrogen, Fisher Scientific, Unity Lab Services, Patheon and PPD.

Ever-growing needs for Actionable Insights

Thermo Fisher has grown through acquisitions and has a complex ecosystem. Thermo Fisher has a data-driven culture and a business drive to ingest new data and derive actionable business insights. Thermo Fisher’s Enterprise Technology Organization (ETO) has built a data lake, namely Enterprise Data Platform (EDP), that supports different business group’s Analytics/DW/BI needs. The ETO team has used Amazon Simple Storage Service (Amazon S3) as the data lake solution because of its high availability, scalability, 11 9s of durability, and cost efficiency. Business users, on the other hand, are familiar with Microsoft Office 365 tools and use Excel, SharePoint, and Power Apps for their day-to-day requirements. When they need data lake resources, they prefer to raise the request within the Microsoft platform itself.

In this blog post, we explain how the ETO Team integrated Microsoft Power Apps with data lake resources provisioned on Amazon S3. We will also walk through the workflow devised to automate and enable the self-service platform for business users familiar with the Microsoft platform.

The ETO team works closely with business stakeholders to support their ever-growing needs. As of early 2022, the ETO team serves eight business groups and 14 corporate functions (HR, Finance, IT etc.). When the ETO team developed EDP, their goal was to have business users self-serve to get their resources created with the least amount of lead time.

Creation and management of data lake S3 buckets was a manual multi-step process. Business users had to put in the request, which went through an approval cycle. Each of the S3 buckets must follow certain standards including encryption, lifecycle policy, S3 storage class analysis configuration, tagging, and access control to be compliant with Thermo Fisher’s policies for ensuring data security. There are hundreds of TBs of data maintained in the EDP data lake. As of early 2022, the EDP has hundreds of buckets per environment (Dev, QA, Prod) and dozens of buckets being created per week. The ETO team has planned to automate the end-to-end process that would provide a seamless, faster experience to business stakeholders.

Existing Manual Process

Thermo Fisher’s business users are familiar with Office 365 tools and Microsoft Power Apps. The business users used to put the S3 bucket provisioning requests in the Power Apps platform and notify the ETO admin team. The ETO admin team would then collect the requirements from the Power Apps platform, update the AWS CloudFormation template manually with the bucket configuration, and provision the bucket by running the updated CloudFormation template. AWS CloudFormation is an infrastructure as code service that gives developers and businesses an easy way to create a collection of related AWS and third-party resources, and provision and manage them in an orderly and predictable fashion.

However, the manual process of gathering business requirements and updating the CloudFormation templates takes time, especially with the scale of the enterprise. Hence, introducing an innovative approach to link the Microsoft ecosystem and AWS together in one workflow became important. When Microsoft introduced REST API capability in the Power Apps platform, it helped the ETO team build the integration between Microsoft ecosystem and AWS.

Enabling self-service by integrating Microsoft Power Apps with AWS

The ETO team at Thermo Fisher designed the architecture (as shown in Figure 1) to remove the manual work required to update the CloudFormation templates. This serverless architecture is performant, cost efficient, and reduces the operational overhead.

Figure 1: High-Level Architecture of self-service platform

Let us dive deep into how this architecture enables a self-service platform through the integrated workflow, as illustrated in Figure 2:

Figure 2: Integrated workflow

The following numbers correspond to the steps shown in Figure 2:

1. Business user submits a S3 bucket creation request in the PowerApps interface. The app requires all mandatory tags and justification for security and compliance requirements. There are links to documentation on the app to guide the user.
2. Power Automate forwards the request to the admin team for review and approval. Once approved, the system processes the next step.
3. Power Apps uses REST API Action to call Amazon API Gateway (API Gateway), a fully managed service that makes it easy for developers to create, publish, maintain, monitor, and secure APIs at any scale. APIs act as the “front door” for applications to access data, business logic, or functionality from backend services. Power Apps passes the S3 bucket configuration details to the API Gateway. The ETO team has used a combination of Lambda authorizers and IP restrictions to enforce security.
4. API Gateway triggers an AWS Lambda function to create the S3 bucket. AWS Lambda is a serverless, event-driven compute service that lets you run code for virtually any type of application or backend service without provisioning or managing servers. The REST API call carries all the S3 bucket configuration details.
5. AWS Lambda creates the S3 bucket and uses an AWS Identity and Access Management (IAM) execution role that only allows the access to create buckets. IAM provides fine-grained access control across all of AWS. With IAM, one can specify who can access which services and resources, and under which conditions. The ETO team uses the AWS SDK for Python (Boto3) package to create the buckets. If there are any errors, AWS Lambda returns the error code, which is passed to the Power Apps via an API Gateway response. Power Apps sends an email to the admin team via the Power Automate Outlook activity. AWS Lambda logs success or error messages in Amazon CloudWatch, a monitoring and observability service that provides customers with data and actionable insights to monitor the applications.
6. Upon success, Power Automate sends an email to the user and the ETO admin team. The email is sent by an Office 365 Service Account.

Conclusion

With this automated self-service platform, the ETO team reduced the data lake resource provisioning turnaround time from days to hours. Also, the integration between Microsoft Power Apps with AWS services allowed the ETO team to decouple business user experience from the backend technology. This helped the ETO team choose the right technology and cloud provider for their data lake requirements, without disrupting the experience of the business users, who can continue with the tool sets that they are comfortable with.

This solution is the first step in building a bridge between the Microsoft Office ecosystem and AWS in the Thermo Fisher environment. Continuing to build on the power of the Amazon API Gateway as the bridge, the ETO team plans to expand and scale up the solution to pair and match other AWS services and Microsoft Office Services. As a next step, the team plans to address the following automation needs:

• Request for AWS Management Console access, or AWS IAM access key creation
• Request to add service access or security policies to an existing IAM role
• Request access to data lake so that users can query data using their preferred analytics platform

The ETO team believes that this will activate and inspire endless possibilities, where resource auto-creation is merely a start. This will foster innovation, drive more automation, and process improvements within Thermo Fisher.

AWS can help you assess how your company can get the most out of cloud. Join the millions of AWS customers that trust us to migrate and modernize their most important applications in the cloud. To learn more on modernizing Windows Server or SQL Server, visit Windows on AWS. Contact us to start your modernization journey today.