AWS Business Intelligence Blog

Pinnacle Infotech’s pioneering business intelligence strategy takes off with Amazon QuickSight

This is a guest post authored by Naman Patwari and Akash Chaurasia at Pinnacle Infotech.

In this post, we share how Pinnacle used Amazon QuickSight to bring a new BI strategy to life. Pinnacle Infotech is the global leader in Building Information Modeling (BIM) services. BIM is the foundation of digital transformation in the architecture, engineering, and construction (AEC) industry. We use technology to bring certainty to construction projects’ coordination, collaboration, asset management, risk mitigation, logistics planning, and cost optimization. Our small business intelligence (BI) team of six members (three data analysts and three data engineers) were new to BI and the cloud. Our BI team started from three existing web services engineers who had no idea about data warehousing but later grew into a team of six members who forged ahead anyway, building a sophisticated BI team.

The decision to move to the cloud

At Pinnacle, we serve a diverse group of over 2,000 clients in the AEC industries, and we have over 3,000 engineers working across the globe. Engineering talent is expensive, and we invest heavily in automation tools to reduce manual work, increase efficiency, improve visibility, and enhance project quality.

Our research and development team needed to understand the adoption rates, usage patterns, and time saved by various tools to steer our investments. Moreover, we wanted to automate the reporting process, removing the need for Excel sheets and manual calculation from raw data.

Initially, our developers used web tools like .NET and JavaScript to build dashboards that directly interacted with our live production databases. We also used Excel sheets for reporting.

Neither solution got us the level of information we needed as fast as we needed it. The dashboards offered limited filtering options and interactivity. Additionally, the report webpages caused significant strain on our live databases, so they had to be disabled during peak website usage—even with batching. It was a poor user experience.

Moreover, building each dashboard took approximately 12 hours of developer time (6 hours on the front end and 6 hours on the backend). Any changes to filters or metrics involved additional developer time. Support and maintenance also ate up developer time, creating a significant opportunity cost.

We knew we needed more speed and flexibility from our dashboards, and a less hands-on process for building and maintaining them. Preparing for machine learning (ML) and artificial intelligence (AI) integrations would also be vital for advancing our operational effectiveness.

All these requirements pointed to one solution: moving to the cloud.

A skeleton team tackles the cloud

We weren’t just getting a new BI tool—we were implementing a whole new BI strategy. We needed to consider how to build and host a data warehouse, and then put a dashboard platform on top of it. Any solution would have to be well architected to easily sync data, and be cost-effective so we could scale our use.

We were looking for data warehousing solutions that could help us achieve our goals and came across Amazon Simple Storage Service (Amazon S3). Our current hosting infrastructure was already built on AWS; we were impressed with the AWS flexible pricing model, so we decided to have Amazon S3 as our cloud data warehousing platform, enabling ease of integration.

For BI tools, we considered several other competitors. Still, QuickSight was the clear winner—it integrated seamlessly with our AWS resources, and its data pipeline and data warehouse services like AWS Glue were an easy extension of our existing workflows.

QuickSight also offered a unique pay-per-session pricing model with monthly cost caps that allowed us to offer dashboards to different levels of consumers. We also create dashboards for our clients, and the pricing model allows us to easily pass the costs to them if we wish.

Amazon Q in QuickSight helped us simplify our complex dashboard by enabling our stakeholders to ask questions from the dashboard in natural language.

Finally, QuickSight also boasts strong security practices and simplifies sharing dashboards with external users with techniques like row-level security.

How we brought our business intelligence strategy to life

Our web service hosting environment uses the following data stores:

We also aggregate data from external data sources using Amazon AppFlow or custom pipelines:

  • Salesforce
  • Intuit QuickBooks
  • HROne
  • Dodge Construction Network Data & Analytics

All these data sources are cataloged in AWS Glue. An extract, transform, and load (ETL) pipeline aggregates this data in Parquet format on Amazon S3, where it’s moved through various stages to power feature stores and BI datasets along with the schema stored in the AWS Glue Data Catalog.

We use Amazon Athena as our query engine to quickly get data from our feature stores and host the final dashboards on QuickSight.

The following diagram illustrates the solution architecture.

Solution Architecture

To implement the solution, we completed the following steps:

  1. Connect an AWS Glue crawler to our existing databases in Amazon RDS for SQL Server and Amazon RDS for MySQL.
  2. Create a new dashboard for automation tools usage analysis.
  3. Experiment with multiple dashboards for different stakeholders to understand QuickSight capabilities.
  4. Experiment with AWS Glue to perform ETL jobs in cloud data.
  5. Set up an AWS Glue workflow to implement and deploy a data warehouse. We import our dataset in SPICE (Super-fast, Parallel, In-memory Calculation Engine) for faster loading time and quick access to data.
  6. Create feature stores to streamline ETL workflows.
  7. Recreate existing department dashboards in QuickSight.
  8. Create client-facing dashboards in QuickSight.
  9. Deploy multiple dashboards to an initial group of over 200 daily users.

Security

We use Amazon Cognito to allow users to access the dashboard in one click via single sign-on. We also use user groups to share specific dashboards with a set of people, such as departments only. All this contributes to row-level and column-level security, which lets us securely grant access to data to different stakeholders at the appropriate level.

Dashboard examples

The client desk dashboard helps us track and understand usage and interaction and generate insights about our client’s most-loved features.

Example of our client desk dashboard

PiVDC is a set of automation tools developed by the R&D department to optimize the modeling process and bring efficiency to the work. The PiVDC usage dashboard helps us see internal usage and client usage of PiVDC tools, such as who used them, when, and where. This helps us track product implementation and identify development priorities.

Example of our PiVDC usage dashboard

Our estimation team uses the revenue and projection dashboard for reporting. It helps us track company performance across different regions.

Example of our revenue and projection dashboard

Results and benefits

We’ve successfully embedded QuickSight dashboards across different internal portals and shared it with over 500 users. We have integrated data from a third-party platform with the Salesforce leads data to build an aggregated leads dashboard on QuickSight, which we have embedded in our Salesforce instance. Previously, our sales team had to visit multiple portals to extract data and perform data-cleaning activities to be able to use it. We automated this process with QuickSight and AWS Glue and created the combined dashboard in QuickSight.

We’re also offering dashboards to our clients with analytics reports that build trust and insight through transparency. For our service offerings, we are in the process of building client-facing project dashboards that enhance project visibility. We use per-user-session-based pricing for these external users to help with cost visibility, even though we often waive charges.

We also offer product usage dashboards to our clients to keep track of their usage. We’re planning to pass these costs on to our users in the near future.

Approximately 70 client users have been beta testing our QuickSight dashboards, and we estimate to onboard 1,000 external users by July 2024.

This solution resulted in the following key benefits:

  • Dashboard creation developer time reduced by 75%, from 12 to 3 hours
  • Report loading time decreased from 15 seconds to nearly instantaneous
  • Data source load shedding increased availability of dashboards without the need to scale live data sources
  • We improved data visualization and interactivity, leading to more informed decisions
  • Expensive reporting queries have been offloaded to Amazon S3, AWS Glue, and QuickSight

What’s next

We’re extending the scope of current dashboards and adding enhancements, such as pulling data from custom file formats used in construction like Autodesk Revit and AutoCAD Plant 3D. We’re also experimenting with automated reporting and integrating dashboards in our current sales collateral.

On our journey to tapping all the power of data within our organization, we plan to play around with more QuickSight features and use Q to enhance our BI insights to drive the business process.

To learn more about Pinnacle and how we are transforming the AEC industry with the help of technology, visit Pinnacle Infotech.

Get started with QuickSight

Integrating AWS and AWS Glue alongside QuickSight was a strategic move that addressed everything from data preparation and warehousing to gearing up for AI/ML integration—which is vital for staying ahead in business analytics. QuickSight has saved us significant developer time while giving us faster and more interactive dashboards that don’t tax our live databases. To learn more, visit Amazon QuickSight.

About the authors

Naman Patwari is Head of Automation at Pinnacle Infotech (Research & Development). Naman looks after the complete product development lifecycle and is fascinated by automation solutions in the architecture, engineering, construction, and operations (AECO) industry.

Akash Chaurasia is Product Manager at Pinnacle Infotech (Research & Development). Akash works as a product manager overseeing the Business Intelligence unit. He is responsible for managing all data things at Pinnacle and loves solving problems with the help of technology.