OLAP and Excel

OLAP and Excel


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.

Introducing OLAP
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.

BIG Data Road Blocks to Tackle

Businessman jumping over hurdle

As I think most of us would agree, Big Data has made big leaps in providing the business world with a large advantage. Luc Burgelman does a great job of identifying the three hurdles that he believes are holding businesses back from reaping the most benefit from their Big Data, in his article.

The first hurdle Burgelman refers to is the ability to evolve. He brings up a great point, companies are looking for different things than they were just two years ago. They need to take technology further than before to accomplish what they need as a final result. Also, companies need to be able to engage different/more departments in the analytical process. It is no longer just about the IT team. Other departments have valuable assets to add to the equation of data anaylsis, and we have to be open to sharing the data across the departments and company, taking a more well-rounded approach to tackling large analytical processes. Which leads smoothly into Burgelman’s second point…

Not only is it important to be involving more of the company’s departments, but we need to make sure that the C-level Executives are equally “on-board.” Let’s face it, without their their final “blessing,” no data technology plan will hit the ground running and be successful. Executives need to be equally passionate about the technology and understand the great benefit and ROI of the analytics behind the data.

The third hurdle that Burgelman talks about is changing the mindset of not only the C-level executives but of all who work directly with the data such as the data users and data scientists. Big Data and the technology behind it is a game changer and offers greater benefits to customers, which returns in greater customer loyalty and greater sale margins. Companies need to be able to change and progress with the latest technologies and analysis software to be able to change the way people and businesses make their decisions and interact with their data. So what do you think, are these hurdles something that we can get over and allow businesses to run faster.

Want to read the full article? Click Here.

Advice for CFOs: Invest in New Technology

Top Technology Trends for Today’s CFO’s” is another insightful post from a blogger we frequently feature, Timo Elliott. In it he admits that the CFO relationship with the CEO and other business executives leaves something to be desired.  He recommends that CFOs invest in the latest technology, which will increase productivity with real-time updates and continuous forecasting.


{Image from Timo’s post, link to http://timoelliott.com/blog/2015/07/top-technology-trends-for-todays-cfos.html}

Elliott mentions a combination of new technology including: in-memory computing, big data, the cloud, and mobile.

He homes in on a key point—that finance staff at large companies are extremely bogged down with just the basics of maintaining their financial reports. As Elliott puts it, “Staff have to spend too much time on basic duties and have no time to improve their understanding of the operational measures that drive and impact financial measures.” This lack of insight or understanding of how the operational measures drive and impact financial measures is the root of the relationship problem between CFOs and other business executives.

Elliott suggests new in-memory computing technology because, “they reduce complexity by combining real-time actuals with budgeting and analysis in a single, integrated system. Financial data is stored just once, making almost every aspect of financial operations faster, simpler, cheaper, and more effective.” We couldn’t agree more, as developers of a new in-memory technology ourselves.

The result of improved systems, improved speed, and better data is ultimately a better working relationship between business executives, and a more productive, effective workplace.

Read Timo Elliott’s post here



Why Excel isn’t Enough for Businesses

Why Excel isn't enough

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.

Create Variable-Length, Dynamic Reports Linked to Excel Tables

When you change a criteria value in a cell, Excel can use formulas and conditional formatting to expand or contract your report automatically, without macros. Here’s how.

Charley Kyd is a Microsoft Excel MVP by Charley Kyd, MBA
Microsoft Excel MVP

Excel Tables are a powerful feature introduced in Excel 2007. Not only can you report from them directly, you can use them as a source of data for dynamic reports, including variable-length accordion reports.

The first task is quick and easy; the second is a lot more interesting. Let’s look at both.

Report Directly from Excel Tables

Suppose you have a table like this and you want to generate a call-assignments report for each sales person.

The easiest approach is to report directly from the Table. For example, If you wanted to report on Alyson, you could click on the Sales Person filter and choose Alyson from the list, giving you this result:

You could print her report, and then do the same for Peter.

Read Full Article: Create Variable-Length, Dynamic Reports Linked to Excel Tables

Source: ExcelUser

Four Ways to Leverage BI for Executive-Level Reporting

Business Intelligence Team Work

Businesses today have an increasing volume of sales performance data available from multiple sources.  While most companies understand the potential value of this data, many struggle to turn it into actionable insights–especially at the executive level. Why? Because few organizations even have the right mix of business intelligence tools and processes to uncover hidden insights from within their data. Although even when they do, most companies still face challenges delivering insights to their management teams.

The problem is that many executives still depend on manually produced spreadsheets for managing and tracking sales performance. But static spreadsheets can’t deliver the real-time, actionable intelligence management needs to improve sales and achieve revenue goals.

Full Article: Four Ways to Leverage BI for Executive-Level Reporting

Article Source: Information Management