Spreadsheets vs. Sigma: Root Cause Analysis
Sr. Content Marketing Manager, Sigma
Imagine you’re a logistics manager at a semiconductor manufacturing organization. It’s the end of the quarter and you’re reviewing a dashboard that your BI team prepared on product distribution.
To your surprise, you notice a dramatic decrease in supplier throughput compared to last quarter. You need to find out what went wrong and how to prevent it from happening again, so you try to dig into the data to run a root cause analysis.
But you don’t know SQL, so you can’t dig into data directly without going back to your BI team for help. To make matters worse, your presentation to leadership is in a few hours, so you don’t have time to wait for BI to get to your request.
You decide to take matters into your own hands and ask for data to be extracted from your BI team’s cloud data platform to a spreadsheet extract. But you soon discover that much of the data has been aggregated to fit within Excel’s scale limitations. It’s also missing data from a crucial source. Not to mention, it was already out-of-date the second you downloaded it.
Defeated and frustrated, you have no choice but to tell leadership that they’ll have to wait at least a week for your BI team to help you complete your analysis. By the time you get your answer, the business is already feeling the effects of this supplier issue. Sound familiar?
Traditional spreadsheets are by far the most well-known and beloved analytics tools: 61% of people use Excel and 29% use Google Sheets for analysis. But spreadsheets simply weren’t designed for the volume and variety of today’s data or the velocity of modern business — until now.
This post covers how the spreadsheet is evolving to help business leaders reimagine their analytics workflow and break free from the traditional cycle of high-level dashboards, BI request queues, extensive data modeling, and siloed data extracts. You’ll learn:
- Ways modern business teams use root cause analyses to remediate issues and drive business outcomes ad hoc and in real time
- Why traditional spreadsheets don’t stack up to the demands of modern analytics and are holding you back
- How Sigma transforms the traditional business intelligence workflow and allows non-technical users to freely explore data on their own terms using their existing spreadsheet skills
Cause and Effect: Getting to the Root of Business Problems with Systematic Analysis
A root cause analysis is a method of problem solving used to identify the origin of business issues and find appropriate solutions to prevent them from occurring in the future.
There are multiple methodologies and techniques to perform a root cause analysis to find the source of a problem or trend, but overall, root cause analyses help organizations:
- Improve their agility by speeding up problem resolution by identifying the origin of issues
- Identify and mitigate blockers to company growth
- Identify unnecessary costs to improve efficiency and profitability
Root cause analysis example: manufacturing operations
Manufacturing operations use root cause analysis to identify the origins of defects and design flaws which cause missed SLAs and the inability to take on more business. Instead of treating the symptoms of the issues, they go deeper to identify and remedy the root cause creating the errors in the first place.
In highly complex manufacturing processes, root cause analysis can require combining and correlating massive amounts of historical measurement and manufacturing line data, then filtering and sorting down to the most granular details to identify patterns.
This may help them find, for example, that machines that are recalibrated more frequently produce fewer errors. Other benefits of this type of root cause analysis are improved measurements, optimized performance, better technical decisions, and increased revenue.
Root Cause Analysis: Traditional Spreadsheets vs. Sigma
In the traditional analytics workflow, business users are expected to derive “insights” from dashboards their BI team builds for them. But while a high-level dashboard may surface an effect, it certainly won’t reveal its cause. Finding the origin requires iterative and investigative analysis by someone with the right domain expertise and business context.
But digging into the data underpinning these dashboards is generally impossible unless you know code, such as SQL, Python or LookML. Even “dynamic dashboards” are modeled by BI teams with a specific set of predetermined questions and drill-down paths in mind.
Trying to explain your questions to the BI team and waiting on the next revision of the dashboard is one option. Or, you can ask them to send you a “dump” of the most relevant data from your cloud data platform in a spreadsheet so you can put your Excel skills to work.
Why traditional spreadsheets fall short for root cause analyses
Traditional spreadsheets are unable to scale to support the volume and variety of data generated today. Excel crashes or slows down with data sets of 1 million rows or less, but modern root cause analysis requires teams to analyze up to 100s of billions of rows. As a result, data must be summarized or aggregated before extraction, which prevents teams from drilling in and seeing the level of granularity they need to explore and identify core issues.
As teams ask consecutive questions and make new discoveries in the data, it’s also common to identify additional data sources that need to be joined into the analysis. However, doing this in a spreadsheet is a highly manual and messy process — especially if this data is in a non-standard format, like JSON, which requires additional parsing and preparation by a data expert before it can be used in a traditional spreadsheet.
Last but not least, spreadsheets are static, siloed snapshots of data, which means they’re instantly outdated as soon as your BI team extracts them from their cloud data platform. Sharing them with colleagues creates governance, security and version control problems, and any future updates must be built from scratch using new (and instantly stale) data.
How Sigma re-imagines root cause analyses
Most BI teams invest in solutions like Snowflake’s Data Cloud and cloud data platforms (sometimes called cloud data warehouses) like Amazon Redshift. The purpose of these tools is to seamlessly manage massive volumes of data from across hundreds of sources in a centralized, secure, and fully governed manner.
Unlike traditional spreadsheets, Sigma was built to sit on top of these platforms and give teams direct, governed access to all of the live data inside. This allows Sigma to tap into their near-unlimited speed, scale, and power, which means users can perform root cause analyses on hundreds of billions of rows of data down to granular-level detail — all in a few seconds.
And unlike traditional business intelligence tools, Sigma doesn’t require coding skills to investigate data ad hoc or do complex, iterative analysis. Instead, it looks and feels like the spreadsheets we all know and love, and automatically translates familiar functions into SQL on the back-end. Users can join data sets, parse semi-structured data, do what-if analysis, create visualizations, and more in just a few clicks — without having to wait on BI assistance.
Because Sigma is directly connected to your company’s cloud data platform, data is always fresh, and analyses are automatically kept up to date. They can be easily edited over time without having to start from scratch. Sigma also gives teams a better way to reuse analyses and work collaboratively by combining spreadsheets, charts, text, and images that can easily be built upon and shared with one another.
SRoot Cause Analysis Success Story: Global Logistics Company
Global logistics for consumer products companies generate mountains of data like individual customer direct shipments, bulk shipments to suppliers, and inventory levels in distributed warehouses. And for all of these channels, they collect data on the locations and movement of thousands of product SKUs across the globe and more.
While standard KPIs can be tracked in regular summary-level dashboards, business leaders need to go deeper into their billions of data points to identify the root causes of changes that are not immediately apparent and may be hidden in the details of individual records. Traditional spreadsheets cannot process data on this scale without crashing or forcing users to work with subsets of data.
With Sigma, a global consumer products company used root cause analysis on such a data set, and quickly discovered why they were losing money on shipments: Their existing system was shipping single toothpaste boxes in containers that could hold several cases. Remedying this single operational issue resulted in millions of dollars of savings and helped them meet their corporate goal of reducing the environmental impact of their business.
How to perform root cause analysis in Excel
- Try to identify the required data for analysis – and hope nothing is missing!
- Request an aggregated extract from the BI/data team that is small enough to work in a traditional spreadsheet.
Perform a manual analysis on the local data copy in the spreadsheet:
a. Manipulate or format the data using a combination of filtering, calculations, pivot tables, sorting, or other forms of conditional formatting.
b. Make a graphic or chart to visualize your findings. Did you find an answer to your problem?
- If not you did not find an answer to your question or have a new question based on the results of the initial analysis, go back to your BI team. Together you’ll work to identify and add any additional data needed to complete the analysis, which they will provide to you as a brand new extract.
- Repeat this loop until the answer is eventually found.
- Want to share this analysis with a colleague? Open up your email and send them a copy of the spreadsheet. Now there are two versions of the same analysis floating around.
- If you decide you would like to update this analysis in the future, you will need to go back to step 1 and start over.
How to perform a root cause analysis in Sigma
- A business user opens a Sigma Workbook, selects the data or data sets to use, and merges them for analysis in a few simple clicks.
- Use the sandbox-like environment to freely investigate and filter, pivot, calculate, visualize, and sort through all your organizations’ data. You can zoom in and out of detail and ask as many as you want using spreadsheet-like formulas and functions.
- Share, collaborate, and present the analysis as needed. Your teammates can easily reuse and repurpose your analysis without duplicating or messing up your work.
- Since the data is always up-to-date, new root cause analyses can be done leveraging the original model instead of manually recreating the analysis from scratch.
Get to the Root of Your Business Problems with Sigma
The traditional analytics workflow and static, siloed spreadsheets of the past don’t cut it for our new normal of continuous change and disruption. Today’s highly demanding and rapidly evolving markets require on-demand, data-driven decision making at all levels and across all departments.
Fortunately, a new breed of cloud-native solutions has been developed to help individuals and teams navigate today’s challenges and take full advantage of tomorrow’s opportunities.
Sigma’s modern BI solution was purpose-built for the cloud and empowers everyone to ask questions and get answers from their data through a powerful yet familiar spreadsheet. Across industries, leading companies are using Sigma to reimagine what’s possible and modernize their data analytics workflows.
Ready to Transform Your Analytics Workflow?
Sign up for a free trial today/HOL or direct to sales (jw since this is MoFu)