OLAP |
Not too long ago businesses found it very difficult to query data out of their recently acquired relational databases. These queries were too slow to be processed by computer systems of the time as well as too inflexible to navigate the data. After trying different solutions offered by various big corporations in the market, OLAP came into being.
One of the primary goals that OLAP vendors try to achieve is to minimize the amount of on the fly processing needed when the user was simultaneously navigating the data. This was achieved by pre-processing and storing every possible combination of dimensions, measures, and hierarchies before the user started their analysis.
The earlier versions of the OLAP cube provided a snapshot of data at a specific point in time. OLAP pre-computes all the totals and sub-totals that need to be reported at a time the servers are usually idle. This allowed data to appear at the same time as it was being investigated by the user.
Because OLAP cubes pre-calculate all the resulting combinations between dimensions, you can do some amazing analysis. For instance, all at once, you can analyze sales by region, by product type, by period of time, by store, by sales representative, and by budget. Crazy, right? It gets so overwhelming that sometimes you may have to back up and try to figure out what kind of analysis you are trying to do. With practice, OLAP cubes speeds up the data modelling and analysis process by significantly minimizing manual operations.
Here are some interesting facts about the history of OLAP technology:
- Edgar Codd, the person who designed the OLAP cube and coined the term OLAP (Online Analytical Processing), he is a veteran in the field.
- SEQUEL (Structured English Query Language) was actually the first version of SQL (Structured Query Language).
- ‘Slice’ among OLAP technicians means dividing any cube shaped item into two.
- The OLAP cube is made up of dimensions, measures, and hierarchies.
- Arrangement of data into a cube is what allows large amounts of data to be analyzed as it is displayed instantaneously.
- The query language used to interact and perform tasks using an OLAP cube is called multidimensional expressions (MDX). It was first developed by Microsoft in 1990s and then got taken up by numerous other vendors in the market.
- OLAP cubes are designed for business users and therefore use business logic and understanding. However, business users can query OLAP cubes using Standard English.
Since its early beginning, OLAP technology has evolved together with advances in processing, connectivity, and cloud services. For instance, modern OLAP products are not limited by just calculation of aggregates. It can now calculate custom formulas and run driver-based computations which are useful for financial reporting, planning, forecasting, and predictive analytics.
Modern OLAP are also more dynamic. By adding connectivity to data sources, OLAP now provides an up-to-date view of a business – even computing on a real-time basis. It is important to note also that some OLAP are available on the cloud as a service, as more companies move toward a virtualized hosted environment.
Interested in trying an OLAP service in the cloud? Check this out.
OLAP technology falls under the umbrella term “Business Intelligence” which is composed of various tools such as data visualization, and more. BI capabilities are being packaged together with business solutions to provide an analytical perspective of the business.
For example, some eCommerce tools such as those offered by Magento development companies incorporate BI capabilities. You can hire Magento developers and see how the integration of these tools improves the outcomes of the business.
Junaid Ali Qureshi leads an expert magento development team and is an experienced digital marketing specialist dedicated to developing intuitive, well crafted, smart websites. His current ventures include magentodevelopers.online ,Elephantation, eLabelz, Smart Leads.ae, Progos Tech and eCig.
Application/Data Integration, Big Data, Data Analysis, Data Management, Data Warehousing, OLAP, Technology and Trends |
OLAP and Hadoop: A Great Pairing
OLAP continues to be a relevant and exciting technology, most recently in pairing OLAP and Hadoop. As we are OLAP.com, we have ALWAYS seen the value of OLAP technology. We admit OLAP has been a bit out of style the last few years. Some companies even run Google ads about how “OLAP is obsolete,” but nothing could be further from the truth. (Check out our blog on that one.)
We see this in the fashion industry all the time: what is old is new again! This is rare in the technology realm, but it seems to be the case with OLAP. As developers struggle to get value out of Hadoop data, they discovered they needed the speed and flexibility of OLAP. OLAP and Hadoop is a powerful combination for getting to the ultimate goal of extracting value from Big Data.
Bringing OLAP to scale for Big Data
In an article from ZDNet, Is this the age of Big OLAP? Andrew Brust writes about the new relationship between OLAP and Hadoop. He highlights that OLAP technology can be particularly beneficial when working with extremely large Big Data sets. Typically, OLAP has not been scalable enough for Big Data solutions. But OLAP technology continues to progress, we find this new application of OLAP exciting. Brust discusses a few strategies for bringing the two technologies together. He mentions a few OLAP vendors in detail and how they manage the issue of scalability for OLAP software.
If you want to try using OLAP with Hadoop, perhaps you want to give PowerOLAP, the mature OLAP product of OLAP.com, a try? There is a free version of PowerOLAP available. If you plan to test PowerOLAP with your Hadoop, contact PARIS Tech, and they will lift the member limit for you in the free version, as you will need to go beyond the member limit that ships with the free version.
In sum, OLAP.com is pleased to see OLAP rising in relevance once again and getting some of the recognition we felt it deserved all along. It is a testament to the power and value OLAP has as a technology.
Application/Data Integration, BI Solutions, Business Budgeting, Business Forecasting, Business Planning, Business Reporting, Data Analysis, Data Visualization, Data Warehousing, OLAP, 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.
BI Solutions, Big Data, Business Analytics, Business Budgeting, Business Forecasting, Business Planning, Data Analysis, Data Visualization, Data Warehousing, OLAP, Predictive Analytics, Spreadsheets |
“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.
BI Solutions, BI Strategy, Big Data, Business Analytics, Business Planning, Data Management, OLAP |
Human Resources is beginning to pull ahead in the Business Analytics world. One thing is certain, if you want to run a successful business, you need to hire the right people to help you get there. Who is in charge of recruiting and hiring your team members? “DING, DING, DING!” you guessed it, Human Resources. In a “Big Data” world, HR can use “People Data” to their advantage and help businesses develop strategy when it comes to hiring the best candidates. As David Klobucher writes in his article, “Data-driven confidence will help HR professionals identify behaviors and interview styles that attract better employees, as well as qualities that make effective workers – and lead to faster promotions.”
I agree with Klobucher, this is a great time to be in HR. There are big opportunities that may be presented to anyone working in HR. Executives within businesses are looking to their Human Resources department to help build the strategy to success. Of course this all depends on if HR professionals “welcome” the business analytics technology with warm arms. As stated in the article, many individuals working with Human Resources are not completely comfortable using data just yet. In today’s world, Big Data surrounds all of us, but for HR, this can lead to big success from analyzing data of past successes and past failures.
In some HR departments, to take on this scope of technology could be intimidating, however like one of my favorite sayings goes, “I never said it would be easy, I only said it would be worth it.”
Read original article here.
Originally posted on October 26, 2015.
BI Solutions, BI Strategy, BI System, OLAP and Excel, PARIS Tech blog, Spreadsheets, Technology and Trends |
“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
One of the primary goals that OLAP vendors try to achieve is to minimize the amount of on the fly processing needed when the user was simultaneously navigating the data. This was achieved by pre-processing and storing every possible combination of dimensions, measures, and hierarchies before the user started their analysis.
