The Power of OLAP and Excel
Should Excel be a key component of your company’s Business Performance Management (BPM) system? There’s no doubt how most IT managers would answer this question. Name IT’s top ten requirements for a successful BPM system, and they’ll quickly explain how Excel violates dozens of them. Even the user community is concerned. Companies are larger and more complex now than in the past; they are too complex for Excel. Managers need information more quickly now; they can’t wait for another Excel report. Excel spreadsheets don’t scale well. They can’t be used by many different users. Excel reports have many errors. Excel security is a joke. Excel output is ugly. Excel consolidation occupies a large corner of Spreadsheet Hell. For these reasons, and many more, a growing number of companies of all sizes have concluded that it’s time to replace Excel. But before your company takes that leap of faith, perhaps you should take another look at Excel. Particularly when Excel can be enhanced by an Excel-friendly OLAP database.That technology eliminates the classic objections to using Excel for business performance management.
Excel-friendly OLAP products cure many of the problems that both users and IT managers have with Excel. But before I explain why this is so, I should explain what OLAP is, and how it can be Excel-friendly. Although OLAP technology has been available for years, it’s still quite obscure. One reason is that “OLAP” is an acronym for four words that are remarkably devoid of meaning: On-Line Analytical Processing. OLAP databases are more easily understood when they’re compared with relational databases. Both “OLAP” and “relational” are names for a type of database technology. Oversimplified, relational databases contain lists of stuff; OLAP databases contain cubes of stuff.
For example, you could keep your accounting general ledger data in a simple cube with three dimensions: Account, Division, and Month. At the intersection of any particular account, division, and month you would find one number. By convention, a positive number would be a debit and a negative number would be a credit. Most cubes have more than three dimensions. And they typically contain a wide variety of business data, not merely General Ledger data. OLAP cubes also could contain monthly headcounts, currency exchange rates, daily sales detail, budgets, forecasts, hourly production data, the quarterly financials of your publicly traded competitors, and so on.
You probably could find at least 50 OLAP products on the market. But most of them lack a key characteristic: spreadsheet functions.
Excel-friendly OLAP products offer a wide variety of spreadsheet functions that read data from cubes into Excel. Most such products also offer spreadsheet functions that can write to the OLAP database from Excel…with full security, of course.
Read-write security typically can be defined down to the cell level by user. Therefore, only certain analysts can write to a forecast cube. A department manager can read only the salaries of people who report to him. And the OLAP administrator must use a special password to update the General Ledger cube.
Other OLAP products push data into Excel; Excel-friendly OLAP pulls data into Excel. To an Excel user, the difference between push and pull is significant.
Using the push technology, users typically must interact with their OLAP product’s user interface to choose data and then write it as a block of numbers to Excel. If a report relies on five different views of data, users must do this five times. Worse, the data typically isn’t written where it’s needed within the body of the report. Instead, the data merely is parked in the spreadsheet for use somewhere else.
Using the pull technology, spreadsheet users can write formulas that pull the data from any number of cells in any number of cubes in the database. Even a single spreadsheet cell can contain a formula that pulls data from several cubes.
At first reading, it’s easy to overlook the significant difference between this method of serving data to Excel and most others. Spreadsheets linked to Excel-friendly OLAP databases don’t contain data; they contain only formulas linked to data on the server. In contrast, most other technologies write blocks of data to Excel. It really doesn’t matter whether the data is imported as a text file, copied and pasted, generated by a PivotTable, or pushed to a spreadsheet by some other OLAP. The other technologies turn Excel into a data store. But Excel-friendly OLAP eliminates that problem, by giving you real-time data for a successful BPM system.
To learn more about OLAP, click here.
“There’s nothing inherently wrong with spreadsheets; they’re excellent tools for many different jobs. But data visualization and data communication is not one of them.” – Bernard Marr
We couldn’t agree more with what Bernard is saying in his article, “Why You Must STOP Reporting Data in Excel!” Excel is everywhere and it has proven to be a valuable resource to every company across the globe. The problem is that many companies are using spreadsheets as their main line of communication internally. Excel is great at displaying all of the raw data you could possibly dream of, just ask any Data Analyst, who eats, sleeps and dreams of never-ending spreadsheets. Bernard gets right to the point and lays out the top 4 reasons that spreadsheets are not the right fit for visualizing data and communication within an organization.
Most people don’t like them.
Bernard makes a great point, unless you work with Excel frequently like a data analyst, it has the reputation of being intimidating. Employees will be reluctant to use it, let alone even think about analyzing data from it. If employees are not clerking in Excel all day, they are most likely going to give Excel the cold shoulder when it comes to communicating data.
Important data is hidden.
I think it is safe to agree with Bernard on this. Spreadsheets are not the best visualization tool out there. Most spreadsheets today are full of endless numbers. If users can’t look at the data and quickly decipher valuable vs. non-valuable, that is a problem. There are better visualization tools that paint a clearer picture and allow for effective communication.
Loss of historical data.
Users in Excel are constantly updating the facts and data as necessary. The downfall to that is it essentially erases all historical data. Without historical data there is no clear way to see the trends and patterns. It takes away the ability to make predictions for the future.
It’s difficult to share.
Spreadsheets are not ideal for collaborative data sharing because they allow the risk of having data deleted or changed. The way that data is shared today is by emailing updated spreadsheets. This data is considered stale or dead, it lacks the key component of remaining “live” or in real-time. This way of sharing is not only time consuming but eliminates the opportunity for users to collaborate while never losing connection to the most updated information available.
The great news is, there’s an easy answer to all of the common frustrations of spreadsheets…
PowerOLAP is an example of a product developed with a solution that addresses all of these problems. It allows for real-time collaboration between users, while always remaining “live”. It has the ability to store historical data which allows for accurate analytical predictions to be reported. Take a deeper look into PowerOLAP and see how it can take your organization to the next level.
To read the entire article by Bernard Marr, click here.
The article, The File-less Organization: Why Excel Isn’t Enough for Businesses, from Dataversity.net is quite astute in the way it identifies Excel as a problem–noting that each manager gathers his or her own version of the numbers to bring to a meeting. And so in the meeting everyone has a different version of the state of affairs, and things can easily devolve into an argument over whose numbers are the “most right”. Sound familiar?
I was so excited when I began reading because this article gets it exactly right in the beginning, but at the end, it seems to recommend dashboards as the solution to this problem. Except that dashboards are usually representations of those same error-prone, manually compiled spreadsheets. The ‘replace-Excel-with-dashboards’ scenario is more of a band-aid theory: once you get past the pretty graphics, you’ll discover that you traded one problem for another! Don’t get us wrong, we love dashboards, but we think they should be real-time, fed from the data source directly. This requires a sophisticated BI solution, which the article confuses a little bit with dashboards. Typically, dashboards have limited calculation capability. With the newest, advanced business solutions, like Olation, the relational data source is combined with a data calculation engine and modeling solution that, yes indeed, works with Excel, not against it.
The survival of any business depends on three basic principles—continuously increasing sales, operating efficiently by finding ways to cut costs, and increasing productivity. Achieving this ultimate business mission involves the cooperation of different operative units of an organization: marketing, accounting, HR, operations, among others. With each department having its own long- and short-term goals, it’s possible that certain crucial elements of a business will fall into the cracks.
An executive dashboard offers organizations the ability to get a holistic view of how the business is operating on a continuous basis, so that preventive rather than prescriptive measures can be implemented when needed. Generally, your dashboard is your business heartbeat! It is able to display areas where your business is performing well and areas that need improvement. Dashboards are no longer a “nice to have” business technology feature—rather, they could determine if a business can stay ahead of its competition, or whether it will be left behind, trying to keep up with industry evolutions.
What Are the Best Dashboard Practices?
There are many resources on best ways to measure performance, visualize data, and set up dashboards. However, most of the discussions fall under two main umbrellas, both with the aim of improving an organization’s productivity.
Plan, Strategize and Determine Your Key Performance Indicators (KPIs):
Determining what you want to track with your dashboard starts with a lot of preparation and planning. Don’t just start off by asking yourself, What do I want to track? Rather, aim for what you are trying to achieve. Once you have determined your business objectives you can add some context to it by analyzing your current situation. For instance, you may want to increase your Web leads by over 50%. This would be your starting point. This can help you put some goals in place, backed by an action plan that can help you bring those goals into fruition. Don’t just create a dashboard to track your KPIs without having goals, objectives and action plans in place.
Refine, Re-strategize, Improve, and Repeat.
The whole point of having an executive dashboard is to ensure that you are constantly making improvements to your existing business strategies. It’s a way to be certain that you keep up with what’s happening with your business, make changes, and even obtain predictions through trend and predictive analysis.
Business dashboards can help every business department stay on top of their game. Not monitoring the ever critical performance areas of your business could put your business heartbeat at risk.
Enjoyed this article? Get Free Email Updates From OLAP.com
Get The Latest on OLAP and Business Intelligence
According to recent research and a report by BI review company Software Advice in their Business Intelligence Software BuyerView for 2014, the image above depicts the most sought-after features in the BI market right now.
There are a number of things to learn here:
1 – Buyers Want Customizable Dashboards and Reporting
Buyers overwhelmingly want to see their data in smart visual representations that allow them to digest information and make decisions quickly. People have had it with dashboards that are difficult to create, difficult to use, difficult to get data into, and, worst of all, need to be re-created every time it is needed. They need quick, easy, beautiful reporting that changes to answer their questions as quickly as they can think to ask them. Mostly these days, people don’t have useful dashboards because they don’t have good data to feed them with. The visualization may be pretty, but there isn’t updated, aggregated, relevant substance behind it, and it doesn’t have enough flexibility to be easily and sufficiently customized into reports.
2- Buyers Want a User-Friendly Interface
And who can blame them? No one likes moving around in clunky interfaces. Some of today’s most common BI tools, like PowerPivot, can’t even manage to list the months properly! Who wants to spend time struggling with something as menial as getting months to display in their natural order as opposed to alphabetically? All this goes hand-in-hand with customizable dashboards and flexible reporting: a good BI solution should be able to display what the user needs to see, on demand as well as up-to-date.
3- Buyers Want Something that Integrates
Buyers want something that works with what they’ve already got. Integration is so important that it trumps considerations over on-premise vs. in the cloud. It seems that buyers don’t care so much about the deployment model, provided the solution empowers them in their existing framework. This makes sense because the state-of-play in most companies, and more so in enterprise-level companies, is that they’ve already spent big bucks on solutions that they thought were going to solve their business problem through IT. For exmaple: ERPs that cost a fortune, and that arguably have a place in the scheme of things, but that do not even begin to provide the kinds of smart, visual reporting that today’s workplace demands.
So it comes down to getting data together and through to beautiful informative visuals without the hassle. Is that too much to ask?
Image Source: Dashboard Insight
The pace of business is accelerating—and with that quickening in tempo comes a need for ever-faster insights into how the business is performing. And the contrast with the era of printed reports, produced weekly, is remarkable. These days, an executive dashboard provides real-time visibility into business performance, delivering powerful metrics in easy-to-read visual formats. The result? As with your car, all the information that’s important to you is gathered together in one place—your executive dashboard—helping you to progress speedily and efficiently towards your objectives. But the fact remains that many businesses don’t yet really ‘get’ executive dashboards. They look at images of an executive dashboard, and understand that they provide performance data in visually compelling forms such as charts and ‘gauges’, but then fail to see the transnational impact that this can offer. And it’s a blind spot that is holding back both businesses and individual executives from achieving a step change in performance.
Read Full Article: Is an Executive Dashboard the Blind Spot That’s Holding You Back?
Source: Dashboard Insight