We all know that in uncertain times, a forecast underlies a company’s success or failure. Forecasts keep prices low by optimizing business operations—including cash flow, production, staff, and financial management—while increasing knowledge of the market. Business forecasting gives you an essential tool for adapting to change and fostering competitive advantage.
But relevant forecasting isn’t easy. It requires the right set of data in a usable format. It has to be accessible in existing tools, it needs to be fresh, and everybody on the team has to be able to analyze it quickly enough to support decision making.
With the right dataset and the right analysis tools, organizations can not only conduct initial analysis that helps them move quickly from confusion to clarity, they can drill down, asking second and third questions for deeper insight, greater refinement, and accelerated business value. But let’s face facts: We’re all struggling with business forecasts, and most of the challenges are tied to the size of the dataset being analyzed. Everyone knows that datasets are growing exponentially and often consist of billions of data points. The size of datasets, and their growth rates, put pressure on the existing forecasting tools we all used in the past to get the job done.
If you or your analysts are losing the ability to conduct full-fledged analysis quickly enough to support business decision making, you’re not alone, it’s become a significant problem. The conventional tools we use aren’t agile enough or scalable enough to handle emerging data volumes and complexities.
Excel Challenges
Let’s consider an example—Microsoft Excel—which many of us use for business forecasting. Excel is a powerful tool for analysis, but has a fundamental obstacle—it’s limited to a million rows. Most organizations address this problem through data extracts—extracting a subset or sample of data that fits within Excel’s row limit. That’s a quick solution, and it lets you move forward, but using extracts for forecasting comes with its own set of problems, which are that:
- Getting an extract isn’t automatic. It’s a manual process, and process complexities and delays can get in your way.
- Selecting an extract is an art, rather than a science. The sample isn’t random, so forecasts could be skewed or incomplete.
- Different extracts can result in KPI inconsistencies from analyst to analyst and over time.
Excel has other problems. With Excel, refreshing data as you go isn’t easy because often it isn’t responsive enough to answer questions as quickly as they are asked. It’s also hard to do deep analysis in the short time frames most of our businesses demand. It’s not unusual for a forecast to take days of work instead of the desired minutes. By the time analysis is done, the opportunity is may be lost.
Avoiding Excel Weaknesses
So what can an organization do? Excel can let us move past these challenges. If you use Excel’s MDX Connector and SQL with an OLAP data modeling tool that provides a defined model of the data, you can sidestep the weaknesses of Excel, reducing complexity and accelerating time to value. The right OLAP data modeling tool offers a path past the million-row limit, the need for extracts, consistency problems, update handling, and slowdowns in data analysis.
What happens when we use Excel with the right OLAP data modeling tool?
- You don’t need extracts. By connecting Excel “live” via its built-in OLAP interface, you or your analysts can continue using Excel to do an analysis based on full access to the data warehouse. Data access happens in minutes instead of days, and all the challenges of data extracts go away.
- By using Excel’s GETPIVOTDATA and CUBE functions with a live data connection, analysts have a new, robust, way to not only add data sets to their analysis, but also to automatically update and refresh all of the data in their analyses.
- You can rely on a “virtual cube” that creates a semantic layer for data’s business definition?enforcing consistency and data governance so that one analysis doesn’t differ from another no matter what BI tool is used.
So to recap what you need to achieve better business forecasting, you start with a live OLAP connection between Excel and your data warehouse so that whatever data warehouse you have, Excel can talk to it without the need for extracts. You need to use powerful Excel functions on your live data to add, update, and refresh all your data, and you need a semantic layer that can be used for every analysis, creating a “virtual cube” that helps you to maintain consistency across different analyses.
These approaches are the right choice, and help analysts overcome fundamental limits with Excel. Businesses such as yours and mine that use these techniques make smarter decisions faster, which enables unprecedented opportunities to take advantage of evolving economic conditions, dodge pitfalls, and come out at the end of year with added success.