“Excel is one of those applications that the business world cannot live without.”
So says one of 27 Excel experts commenting on Microsoft’s Power BI Suite and its effect on the great wide world of spreadsheet users.
As Excel lovers, we couldn’t agree more!
There’s a wealth of wisdom in the observations made by these 27 pros. Along with the main topic of what’s new in Power BI Suite, there’s excellent insight on the use of Excel and BI generally. (For a good post about what one expert calls a “running joke in BI communities—‘What is the most used feature in any business intelligence solution?’”, see But, Does It Export to Excel?, from PARIS Technologies.)
About what’s new with Power BI Suite, the experts agree that it delivers significantly powerful new capabilities: from connecting to enterprise data (Power Query) to aggregating differently sourced data (Power Pivot) to creating exciting visualizations (Power View and Power Map—watch out, “Visual Analytics” products!), this suite of tools signifies a whole new era of “self-service BI” for Excel users.
Smart guys and gals that they are, the experts point up some caveats. How will these new capabilities affect a company’s “B.I. workflow[s]”? And, mightn’t Power BI, by empowering Excel user(s), propagate more—and more complex—silos of data among disconnected groups of users? Will IT lose total control, if users feel it’s their Excel-given birthright to reach back to underlying data sets for BI solutions? And as to collaborative work—will SharePoint be the answer, finally?
All the caveats and concerns are certainly worth considering … That said, from our standpoint: we begin with the premise that user-empowering technologies—Excel or otherwise—are a good thing! As for the caveats, won’t someone please invent a technology that addresses concerns about security and collaboration? And one that provides for other application needs, like planning, budgeting and forecasting? Because that’s what people do in Excel! And while they’re at it, a solution that is inclusive of other non-Excel users? (Hmmm, maybe someone has – see here for a description of PARIS’s Olation).
“The future looks very bright for Excel and BI,” says another expert. Here’s to a future that’s bright for all BI users—and no caveats required!
Read the original article, from InvestInTech.com here: 27 Microsoft Excel Experts Predict the Future of Excel in Business Intelligence
If you think OLAP is Obsolete,
A couple things could be possible:
You don’t know what OLAP is.
You haven’t seen a recent OLAP application.
You never tried to do the knitty-gritty of business planning.
You’ve resigned yourself to doing manual labor in Excel spreadsheets and pushing to a dashboard.
OK, so I’ll give nay-sayers of OLAP a little bit of credit, OLAP (historically) can be frustrating. Especially when some implementations are hardly what we consider to be “online”. Most of what people are calling OLAP technology is not really connected in a live way to the data source. There is a batch process to update the data from relational source to a Proprietary OLAP cube.
Now that is where you lose most of the IT people. Ugh OLAP, really? IT Teams need a proprietary system to maintain like a hole in the head, because they are already underwater just trying to make sure all the delicate connections between systems are running. They just want to know that processes are able to finish running, and that their end-users are eventually served with the data they need.
The new batch of more evolved OLAP systems will address all those painful processes and more. And how dare anyone call that obsolete?! That is the opposite of obsolete—it is what you need—a platform which allows you to:
1.) Connect the relational source to the “cube” or multidimensional modeling space
2.) Consolidate several data sources
3.) Easy access to consolidated source data in ANY front-end, and
4.) Live, real-time updates in a collaborative environment.
Never heard of an OLAP product that can do all that? Check out the Olation eBook for a quick intro. The most exciting things in OLAP have yet to come!
OLAP technology has continued to develop, a good indicator of its broad applicability in the software solution market. And though newer doesn’t always mean improved, our opinion is that the most recent OLAP technologies are faster and (generally) better than their predecessors. These recent OLAP advances include aspects of in-memory OLAP combined with hybrid systems that couple the benefits of multidimensional modeling with the steadfastness of a relational database.
Which OLAP is best for your business?
When a company commits to purchasing an OLAP-based BI system, it’s essential that the system meets present and potential future needs. With the wide variety of OLAP technologies available, it has become critical to know the differences between the main types, MOLAP, ROLAP, HOLAP–and a new entrant, HTAP. While there are other versions of OLAP, with this post we have tried to help make the decision-making process a bit easier, by providing descriptions of each type, along with their advantages and drawbacks.
MOLAP: Multi-dimensional OLAP
Data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats (one example is PowerOLAP’s .olp file). MOLAP products can be compatible with Excel, which can make interacting with the data very easy to learn.
- Excellent performance: MOLAP cubes are built for fast data retrieval, and are optimal for slicing and dicing operations.
- Can perform complex calculations quickly: often calculation logic can be handled by users (meaning, no relational database programming skills needed), and the main reason for MOLAP is precisely to speed up calculations in a multidimensional environment optimized for fast data calculation.
- Sometimes limited in the amount of data it can handle: because all calculations are performed when the cube is built, it might not be possible to include a large amount of data in the cube itself. This is not to say that the data in the cube cannot be derived from a large amount of data. Indeed, this is possible, but only summary-level information will be included in the cube itself.
- MOLAP products are typically proprietary systems.
- Relevant data must be transferred from relational tables, which can be cumbersome and, by definition, redundant.
ROLAP: Relational OLAP
ROLAP products access a relational database by using SQL (structured query language), which is the standard language that is used to define and manipulate data in an RDBMS. Subsequent processing may occur in the RDBMS or within a mid-tier server, which accepts requests from clients, translates them into SQL statements, and passes them on to the RDBMS.
- No data limitation, can handle large amounts of data
- Can access use functionality of inherited relational databases
- Performance can be slow because of large size of data sets
- Can be limited to SQL functions, which can be inflexible
- Data may need to be reformatted for end-users
HOLAP: Hybrid OLAP
The merger of the best features of MOLAP and ROLAP allowing for fast calculations from RDBMS by using pre-calculated cubes. (New HTAP systems–see further below–may be considered HOLAP products, though they function differently from previous products that people may recognize as HOLAP.)
- Has the best features of both MOLAP and ROLAP: scalability, flexibility, and speed
- Uses RDBMS SQL functionality
- Can “drill-down” from a cube to a relational table
- Fast to use because of pre-calculated cubes
- Has the limitations of both MOLAP and ROLAP: as it is fast, it may not be as fast as pure MOLAP, and as it is scalable, it may not be as scalable as pure ROLAP.
What’s New in the Market?
Hybrid Transaction / Analytical Processing (HTAP)
Gartner coined the term HTAP in a paper in early 2014 to describe new in-memory data systems that do both online transaction processing (OLTP) and online analytical processing (OLAP). HTAP represents a new way to tie data together in a way that hasn’t been possible before. Combining analytical engine capabilities with relational data tables is a the root of HTAP, and we at OLAP.com think it is the way data will be managed in the future.
- The technology is sited in the relational database
- Powerful, often distributed, processing–which means it is fast
- No more data replication
- New transactional information becomes part of an analytical model as fast as technologically possible
- Unites the relational data tables with the models that are used for decision making by business leaders
- Change in existing architectures can be disruptive
- New technologies and accompanying skills may have to be learned
For an example of an HTAP product, check out Olation® from PARIS Tech, the sponsor of OLAP.com. Olation can be categorized as an HTAP product — even the name Olation implies the combination of “OLAP” and “relational” technologies.
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.
Some vendors have run from the four letters OLAP…and yet, articles continue to be published, and assertions made, that “analytical processing” is entering a new phase. There’s a new acronym in the market, HTAP, which stands for Hybrid Transactional/Analytical Processing. Given the close similarity (two out of four words ain’t bad—and it’s three if we consider the once-upon-a-time category HOLAP), is there a difference? This post from one of our favorite bloggers, Timo Elliott, may help you decide…
What is HTAP?
Read more about PARIS’ take on the HTAP technology on the PARIS Tech Blog
You may not think that business analytics as a concept applies to academics, but access to meaningful and timely information is critical to the proper function of a dean’s office. Anyone who has been a dean or worked in the dean’s office knows that. And, like in any other business, certain kinds of data seem to percolate to the top of the needs list, but that data is all too frequently not easily accessible.
Take, for example, the need to project student enrollment for the ensuing year. As new students arrive on campus, there must be an adequate number of required courses as well as qualified faculty to teach those courses. If enrollment is low because of a glitch or error in the Admissions Office, or there is an unexpected fluctuation in applications, the number of courses can be adversely impacted.
Similarly, enrollment projections are required several years forward, suggesting the need for a strong analytics tool, and one that allows for “what if” eventualities. The dean’s office needs to be able to monitor the number of applications, the number of offers tendered, the number of offers accepted, the number of paid acceptances, the cumulative amount of financial aid and the average aid per applicant.
These are just a few of the desired “data intelligence” items to have available at one’s fingertips; each one of these belongs to its own “dimension” for measuring performance. These enrollment statistics and other metrics drive the university, and the university president regularly seeks to be updated, just like any other CEO who needs “business analytics” to run a company. Of course, a graph showing how the current numbers compare to past numbers is necessary.
The challenge is delivering up this intelligence, as by nature it is “multidimensional,” a word one encounters in typical definitions of OLAP technology.
For example, one obvious dimension is “school” or “college,” since typically colleges are subdivided into various schools while universities are subdivided into colleges. Thus, university X might have a College of Medicine, College of Business, and the like. And so all the metrics above might be required by each school or college as well.
Other dimensions might concern staff [faculty] allocation to courses as well as course availability for student selection. Tracking the number of students progressing through the colleges from year to year, especially at the undergraduate level, is desirable so that sufficient resources can be properly allocated in a timely fashion. When faculty are absent from the university for the purpose of a sabbatical (another dimension), their classes and student advisees must be accommodated either through existing faculty movement or the hiring of adjunct faculty. If adjunct faculty are to be hired, the problem of recruiting an academically or professionally qualified individual can be a formidable challenge, not to mention the impact on a budget that might need to be crafted two or three years in advance. This means that access to appropriate historical and current data must be available.
To consider another factor impacting faculty allocation: students, especially undergraduates, change majors at least once and it is not altogether unusual for a student to change majors several times. As students move from one major to another, frequently within the same college, demand for certain courses might unexpectedly increase or drop. Having as much advance data intelligence as possible for the potential impact on staff teaching needs in turn would lessen any adverse effect on financial budget plans.
Of course, the need for financial information is forever present in higher education as it is in business. The usual data such as major budget categories and current progress should be easily available along with a comparison with past budgets. Of equal importance is the projection to end-of-year. At a different level the projected liability of the university to support the scholarships granted is something the financial vice president would want to know. And it is not uncommon for faculty to approach their dean with requests for additional travel funds so they can take advantage of some unforeseen opportunity. As in the business world, in the face of unavailable or incomplete data, the answer is typically in the negative!
The list of information needs continues with the number and kind of publication each faculty member writes, the total yield for each faculty member over the number of years necessary to meet accreditation requirements, the percentage of publications in high quality journals, and perhaps some ranking of faculty by academic production.
I have indicated these information needs not only to suggest that in the academic environment they can be vast and various, but also to suggest that it behooves us to look beyond products with limited single information source availability. A really productive solution would also provide modeling with 1) robust “dimensionality”, 2) “what if” capabilities, and 3) the ability to compare Current v. Historical v. various Planned scenarios. These are hallmarks of products featuring OLAP technology, which, though more widely adopted in the business community, are clearly well-suited and necessary to provide an application-oriented academic “business analytics” program as well.
(Professor Bill Tastle is a regular contributor to OLAP.com. At Ithaca College he teaches a course in Business Analytics, and uses PowerOLAP from PARIS Technologies for in-class exercises.)