Data Virtualization is a process that gathers and integrates data from multiple sources, locations, and formats to create a single stream of data without any overlap or redundancy.
Data Virtualization innovation is helpful, in our world of non-stop data transmission and high-speed information sharing, as a tool to aid in collecting, combining, and curating massive amounts of data.
With big data analytics, companies can locate revenue streams from existing data in storage, or they can find ways to reduce costs through efficiency. However, this is easier said than done. IT companies generally have multiple, dissimilar sources of information, so accessing that data can be time consuming and difficult. Data virtualization systems can help.
Companies that have implemented data virtualization software have better, quicker integration speeds and can improve and quicken their decision-making.
What is Data Virtualization
Data virtualization (DV) creates one “virtual” layer of data that distributes unified data services across multiple users and applications. This gives users quicker access to all data, cuts down on replication, reduces costs, and provides data flexible to change.
Though it performs like traditional data integration, DV uses modern technology to bring real-time data integration together for less money and more flexibility. DV has the ability to replace current forms of data integration and lessens the need for replicated data marts and data warehouses.
Data virtualization can seamlessly function between derived data resources and original data resources, whether from an onsite server farm or a cloud-based storage facility. This allows businesses to bring their data together quickly and cleanly.
How Virtualization Works
Most people who use IT are familiar with the concept of data virtualization. Let’s say you store photos on Facebook. When you upload a picture from your personal computer, you provide the upload tool with the photo’s file path.
After you upload to Facebook, however, you can get the photo back without knowing its new file path. Facebook has an abstraction layer of DV that secures technical information. This layer is what is meant by data virtualization.
When a company wants to build Virtual Data Services, there are three steps to follow:
- Connect & Virtualize Any Source: Quickly access disparate structured and unstructured data sources using connectors. Bring the metadata on board and create as normal source views in the DV layer.
- Combine & Integrate into Business Data Views: Integrate and transform source views into typical business views of data. This can be achieved in a GUI or scripted environment.
- Publish & Secure Data Services: Turn any virtual data views into SQL views or a dozen other data formats.
Once a DV environment is in place, users will be able to accomplish tasks using integrated information. A DV environment allows for the search and discovery of information from varied streams.
- Global Metadata: Global information search capability lets users access data through any format from anywhere in the world.
- Hybrid Query Optimization: Allows for the optimization of queries, even with “on-demand pull and scheduled batch push data requests.”
- Integrated Business Information: Data virtualization brings users integrated information while hiding the complexity of accessing varied data streams.
- Data Governance: DV layer serves as a unified layer to present business metadata to users. Simultaneously, it helps to understand the underlying data layers through data profiling, data lineage, change impact analysis and other tools and expose needs for data normalization / quality in underlying sources.
- Security and Service Level Policy: All integrated DV data views can be secured and authenticated to users, roles and groups. Additional security and access policies can manage service levels to avoid system overuse.
Data Virtualization Tools
The various capabilities that Data Virtualization delivers offers companies a newer, faster method of obtaining and integrating information from multiple sources. The top tools currently in use are as follows:
- Logical abstraction and decoupling
- Enhanced data federation
- Semantic integration of structured & unstructured data
- Agile data services provisioning
- Unified data governance & security
These capabilities cannot be found organized in any other integration middleware. While IT specialists can custom code them, that minimizes the agility and speed advantages DV offers.
Data Virtualization creates many benefits for the companies using it:
- Quickly combine multiple data sources as query-able services
- Improve productivity in IT and by business data users (50%-90%)
- Accelerate time-to-value
- Improve quality and eliminate latency of data
- Remove the costs associated with populating and maintaining a Data Warehouse
- Significantly reduce the need for multiple copies of any data
- Less hardware infrastructure
While this innovate new path to data collection and storage offers increased speed and agility, it is important to note what DV is not meant to be.
What Data Virtualization is Not
In the business world, particularly in IT, there are buzzwords flying about in marketing strategies and among industry analysts. It is therefore important to make note of what Data Virtualization is not:
- Data visualization: Though it seems similar, visualization is the physical display of data to users graphically. Data virtualization is middleware that streamlines the search and collection of data.
- A replicated data store: Data virtualization does not copy information to itself. It only stores metadata for virtual views and integration logic.
- A Logical Data Warehouse: Logical DWH is an architecture, not a platform. Data Virtualization is technology used in “creating a logical DWH by combining multiple data sources, data warehouses and big data stores.”
- Data federation: Data virtualization is a superset of capabilities that includes advanced data federation.
- Virtualized data storage: VDS is database and storage hardware; it does not offer real-time data integration or services across multiple platforms.
- Virtualization: When used alone, the term “virtualization” refers to hardware virtualization — servers, networks, storage disks, etc.
Myths and Inaccuracies
As with every new innovation in technology, there will always be myths and inaccuracies surrounding implementation.
We don’t need to virtualize our data – we already have a data warehouse.
The sources of unstructured data increase every day. You can still use your data warehouse, but virtualization allows you to tie in these new sources of data to produce better information and a competitive advantage for your business.
Implementing new data technology isn’t cost effective.
Data virtualization software costs are comparable to building a custom data center. DV also does not require as many IT specialists to use and maintain the system.
Querying virtual data can’t perform like physical data queries.
With the constant innovation and improvement of computing platforms, faster network connections, processor improvements, and new memory storage, virtualization software can process queries with multiple unconnected data sources at near real-time speeds.
Data virtualization is too complex.
When something is new in technology, humans have the tendency to question it based on their own lack of experience. Most virtualized software is easy enough to be used by geeks and laymen alike.
The purpose of data virtualization is to emulate a virtual data warehouse.
While DV can work this way, it is more valuable when data marts are connected to data warehouses to supplement them. “The flexibility of data virtualization allows you to customize a data structure that fits your business without completely disrupting your current data solution.”
Data virtualization and data federation are the same thing.
Data federation is just one piece of the full data virtualization picture. Data federation can standardize data stored on different servers, in various access languages, or with dissimilar APIs. This standardizing capability allows for the successful mining of data from multiple sources and the maximizing of data integration.
Data virtualization only provides limited data cleansing because of real-time conversion.
This is a claim that can be made about any number of data query software programs. It is best to clean up system data natively rather than burden query software with transformation of data.
Data virtualization requires shared storage.
Data virtualization is quite versatile. It allows you to build customized storage devices for your system needs.
Data virtualization can’t perform as fast as ETL.
Through data reduction, data virtualization performs more quickly than ETL. “Operations perform at higher speeds because the raw data is presented in a more concise method due to compression, algorithmic selection and redundancy elimination.”
Data virtualization can’t provide real-time data.
DV sources are updated live instead of providing snapshot data, which is often out of date. “It is closer to providing real-time data and faster than other data types that have to maintain persistent connections.”
Why Do We Need Virtualization?
Data is transferred among users in different speeds, formats, and methods. These variables make Data Virtualization a must have in the global business world. DV will help companies search, collect, and integrate information from various users, platforms, and storage hubs much more quickly. This will save the company time and money.
Data Virtualization is perfect when data demands change on the fly and when access to real-time data is critical to positive business outcomes. DV also provides you with access to any data storage system you are currently using. Despite the differences in storage platforms and systems, DV will allow you to integrate all the material in a single model.
Data Virtualization offers help in security challenges because the data is not transferred – it is left at the source as DV provides virtual access from anywhere. This is also cost-effective as you will not be duplicating any data.
As we move further into the technical age of global systems, the need for Data Virtualization becomes clear. Access to information across platforms, languages, and storage types will precipitate a faster and more useful transfer of data that everyone can use.
The future is here. The future is now.
Big data is a modern technology that captures huge amounts of data, which is then analyzed in order to reveal patterns and trends. Organizations need to transform what they have gathered into business insights that promote growth.
Across business in general, there are 5 sectors that are intelligently using big data to improve their operations and connect with customers better.
By using big data, fashion industry moguls can predict when there is a market for businesses to carry certain products. Big data gives them the opportunity to stop the manufacturing of some items, and focus on the products that sell well.
According to researchers, big data presents fashion companies with opportunities to engage businesses and markets through effective content on social media. The leading fashion brands use comments on social media and turn them into engaging conversations, thereby increasing their global presence and at the same time gathering necessary data for future analysis of patterns and trends. Fashion designers and magazine publishers are constantly producing innovative fashion content to attract customers. This allows them to collect the data received from the different content outlets and see how the fashion industry is changing.
If marketing-based businesses are driven by analytics, then market intelligence groups are the engines of it all. Big data and analytics are on a whole new level today, creating vast new opportunities for company leaders. The last few years have seen a huge increase in the quantity of data available from different sources including mobile phones, social media, and transactional data from people’s online behavior. Only businesses that are based on good research can minimize operational risks, as well as pursue profitable opportunities for growth. Fortunately, market intelligence groups now have the tools, approaches, and talent, in order to turn gathered data into a competitive advantage. Market intelligence can either make businesses realize their potential or advise them against unrealistic expectations.
Data is the basis of any customer intelligence group. Today’s businesses are generating huge amounts of data through several channels, which include online stores, kiosks, sales offices, and even call centers. In addition, organizations are now using customer and prospect data in order to generate information through reseller and advertising networks. This year, customer intelligence organizations can gain insights by integrating views of the aforementioned sources with different target markets. Customer intelligence groups are now developing strategies for accessing and integrating customer data into making big business decisions.
Read more about how Marketing Intelligence and Customer Intelligence provide powerful ways to strategize, ensure customer satisfaction, and continued growth
Bloomberg claims that the global financial industry has evolved enormously over the past decade in terms of investments and trading. The sheer volume of financial data is constantly growing, which has a direct impact on the value of different assets that can be traded online. These assets include currencies, cryptocurrencies, commodities, indices, stocks, and options. The information contained in big data can greatly improve the way investment and trade indicators are gathered and used.
Big data has been a crucial step for advancements in online trading. Without big data, online trading platforms are unable to create solutions that would ease the everyday activities of traders. Nadex states that people can now perform transactions on a regulated US-based exchange, as well as trade on different markets using just one account on a computing device. It’s thanks to big data that the financial industry can expand their solutions, as well as improve their offerings based on their customers’ needs. Big data is a huge step towards the development of investment and trade, and it will further propel the industry towards positive changes this year.
Big data integration is the combination of technology and business procedures used for synthesizing data from dissimilar sources. A complete data integration solution must deliver trusted data from different sources.
With a high volume of sensor and social data being generated every day, it is now critical for data scientists to make large amounts of information available for analytics and consumption. Data must not only be gathered, but its accuracy must also be assured. The powerful and far-reaching data integration of several digital frameworks will help this idea materialize in 2018.
Businesses are becoming more digital than ever which means data are being gathered by organizations in gigabytes. Utilizing data can provide leaders with unexpected information that lead to unconventional yet successful business strategies. While some industries are already taking advantage of the insights that big data provide, we can only expect more and more enterprises to join the data revolution as businesses become more competitive.
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.
As the fields of business intelligence and business analytics continue to develop and grow, organizations must be aware of the distinctions between the terms and understand their value. Adoption and usage of business intelligence and analytics tools show no sign of slowing. Understanding these concepts is vital to making the best business decisions, to maintaining a competitive edge across all industries, and to enabling companies to capture operational and strategic value.
To learn more, see the infographic below created by Pepperdine University’s Online MBA program.
Distinguishing Business Analytics and Business Intelligence – Resource from Pepperdine University
Differences Between Business Analytics and Business Intelligence
The goal of business analytics is to develop successful analysis models. It simulates scenarios to predict future conditions. It is a very technical approach to predict upcoming trends. This process helps find patterns after analyzing previous and current data. The analysis is used to devise future courses of action. Professionals working in this field use data mining, descriptive modeling, and simulations.
Business intelligence uses different types of software applications to analyze raw data. Professionals working in this field study business information. They closely consult with decision-making managers. They identify existing business problems and analyze past and present data to determine performance quality. They use KPIs and other metrics, and prepare easy-to-read reports. The reports give unique insights into the workings of the business and empower organizations to make optimum business decisions.
Business analytics experts help predict what is going to happen in the future. They use data to analyze what will happen under certain specific conditions. They can predict the next trends and outcomes.
Business intelligence experts, on the other hand, help track and monitor data and business metrics. They can correctly identify what happened and what is happening now. They can discover why something happened, how many times something happened, and when all such events took place.
Data-Focused Talent Shortage
Very few managers have high expertise in data fields because the use and analysis of big data has emerged only in the last few years. Even new managers and leaders do not have requisite skills to devise data-driven digital strategies. Most organizations need a new kind of talent base that is well versed in the data-driven business landscape. One McKinsey report estimates that by 2018, the US will face a shortage of 140,000–190,000 data science professionals. Even now, companies must pay very high salaries to employ data analysts. Only large companies can afford such professionals.
The Future of Big Data Analytics
While 78% companies agree that big data will impact their business, only 58% think their company is ready to take advantage of all the potential that big data offers. The reason for this is not difficult to ascertain. Companies must use various techniques to capture data, and the data collected must be realized in a specific format. Data analysts must use exacting methods and processes to analyze this data. Capturing and analyzing big data is a complex process and can be handled only by trained data analysts.
Benefits of Business Analytics
Engaging effective business analytics is necessary to make the right business decisions. Managers with proven analytics skills are better able to plan for future projects. The biggest advantage involves forecasting. Analysis of previous and current data helps predict future trends. This information is crucial to the success of a business. A company may have different types of products. It may keep promoting the fast-selling product while another product that is quickly gaining traction may remain under the radar. Only big data analysis can reveal the importance of the latter product. Business analytics is a forward-thinking way to improve operational efficiency. Decisions can be made faster, and it becomes easier to make sense of large volumes of data.
Benefits of Business Intelligence
Business intelligence proves useful in identifying new opportunities. A company can identify a new market that holds important business opportunities. Product pricing can be tweaked to market demands. Business productivity can be improved. Sales and marketing expenses can be optimized. Business intelligence helps predict customer behavior, which proves useful in improving customer service.
Usage and Adoption of Big Data
Even when the benefits are well known, very few companies are able to use big data analysis in a significant way. Almost 50% of businesses face difficulty in the field of business analytics. They are unable to ensure the quality of data. Without the right talent to manage and analyze data, they are at a disadvantage in the market. Many businesses rely on simple applications to analyze data. These tools are not very effective in analyzing big data. This type of data must be analyzed scientifically. It is a complex job that can be handled only by professionals who possess training and skills in data analytics.
Developing a Big Data Analytics Culture
All types of businesses are working continuously to take advantage of big data. They are using simple as well as complex solutions to work with such data. There is a consensus realization that a high level of data analytics is necessary to ensure business success in today’s market. Now, companies are incorporating data analytics into all their departments. They are using sophisticated tools and solutions to predict future trends. Almost 82% of business executives now take advantage of data-driven reports and dashboards.
Sources of Big Data
Big data is obtained from a wide range of sources. Sales records and financial transactions generate a great volume of useful data. They help devise pricing models for different types of products. The customer database is a key source of data. Large amounts of contact details and other data can be mined from emails, productivity and communication applications. In fact, every business process generates data. All such data must be collected and stored properly.
Businesses need the services of both business analytics and business intelligence experts. There are differences in their positions, but both groups play important roles in the success of a business. As more and more businesses rely on digital strategies, they have to analyze their big data properly and effectively. They need the support of trained and skilled data analysts to help achieve the best business success possible.
To identify threats and opportunities, analysts may look through thousands of data records manually, or define KPIs and make a discovery literally in a few clicks. Which approach will your business choose?
According to Richard Branson, a business magnate and investor, “business opportunities are like buses, there is always another one coming.” The idea seems convincing: however, there is hardly a person who did not feel disappointed when they missed their bus. Likewise, companies prefer not to miss their opportunities. But how to recognize them well in advance?
In fact, a company can identify threats and opportunities with the help of business intelligence. Here, we will not focus on the simplest, but highly inefficient approach of scrolling through thousands of data records. Instead, we will dwell on the approach of defining relevant KPIs, which BI consulting practitioners advise.
As the challenge described is not industry-specific, let’s consider a large product portfolio (100+) – an example relevant to several industries (for example, retail and manufacturing). Now, let’s take a closer look at how business intelligence and data analysis can help in defining KPI metrics and in finding opportunities and threats related to a particular product.
Prepare BI infrastructure
As a business has to deal with a big volume of data, usually taken from numerous sources, in order to reach the data, a company needs to implement BI infrastructure. This requires using a tool that is capable of connecting to multiple data sources from which data is combined to create OLAP data models for slicing and dicing. At this stage, to build a required BI infrastructure and ensure data quality, companies may reach out to business intelligence consulting experts.
Start with the right approach to developing KPIs
The next step is to define KPIs. At this stage, it’s crucial to have a clearly defined strategy and know how to translate it into right KPIs to create a hierarchy where lower levels support higher ones. Thanks to historical data analysis and forecasting, business intelligence allows companies to define metrics and set KPI targets, both long-term and short-term.
Track the dynamics
In a constantly changing environment, it is important to keep track of the dynamics. The following KPIs may be useful for this purpose.
1. Absolute figures
With absolute values, it’s possible to look quickly at best (or worst) results in a few clicks. A simple filtering will put the required information to the top. Having right dimensions and measures, a company will easily learn, for example, what product brought highest (or lowest) sales and margin.
2. Relative figures
Let’s imagine that one of the products from the portfolio shows -2% of sales. Undoubtedly, a decline in sales is not what a company is happy to see. But is this decline alarming? To understand that, you need to look at the portfolio in general:
Product 1: -2%
Product 2: -2.5%
Product 3: -3.2%
Product 4: -5.4%, etc.
When compared with others, Product 1 looks the best, while Product 4 looks problematic, as its sales decrease faster. Besides, there is an overall decline. Correspondingly, a company will focus on improving its overall performance.
3. Right time frame
Choosing the wrong period to measure performance may lead to distorted results. For instance, a company takes the period of last 2 weeks when the sales are growing. But if we look at last 10 weeks, we’ll see a decline followed by a slow recovery.
To avoid serious fluctuations that seasonality brings, it’s necessary to define a seasonal coefficient for each month (for example, Jan: 1.0, Feb: 0.98, Mar: 1.0, …, Jun: 2.5, Jul: 3.2, …) and apply it to the values (for instance, sales). This simple measure will help to get season-neutral values.
Compare Target vs. Fact
How can a company know if a 5-percent growth is enough? It depends on what they defined as good. For that, a company should set a target for each product, as some products cannot (or should not) grow while others are expected to do it. A larger company may need to set more sophisticated targets for every product and region combination. For example, Product 1 should grow fast in TX and CA, while product 34 in NY and PA.
To sum it up
To cope with the challenge of identifying threats and opportunities, a company needs KPIs oriented towards finding these valuable insights. Business intelligence can be a helpful tool for defining these KPIs, and an implemented BI solution will allow filtering, grouping or sorting in a few clicks, instead of scrolling through thousands of lines.
Business Analytics vs. Data Science
“Business analytics” and “data science” — are they basically interchangeable terms, or entirely separate professional pursuits? There’s certainly overlap on the topic of Big Data and using data to inform decisions. There is no dispute over the fact that both business analysts and data scientists use exponentially growing sources of data to do their work. [Check out PARIS Tech’s recent post on Big Data]
An article and featured infographic by Angela Guess for Dataversity.net argues that the terms business intelligence and data scientist are distinct, and not just because one pursuit applies to business, and the other to scientific results.
Click below to read the original article which accompanies the business intelligence vs. data scientist infographic.
Infographic: Business Analytics v. Data Science
What is Business Analytics?
Science and business continue to intersect, most recently on the topic of data analytics. Generally speaking, “data analytics” is the process of organizing and interpreting data to uncover valuable information. “Business [data] analytics” is the more specific application of data analytics to business purposes.
Some examples of data analytics might be: What segment of customers use desktop v. mobile? Or, which target audience found value in the most recent advertising campaign? Companies ranging from Target to Google find results from these kinds of questions so valuable that they pay data analysts over $100,000 per year. To learn more about the burgeoning data analytics industry, check out this educational resource, created by Villanova University’s Master of Science in Analytics program.
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.
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.