Excel is undoubtedly one of the most common business tools in the world. It has been used to collect and analyse data for over 30 years. Along with corporate and global digital transformation development Microsoft has enhanced Excel functionality with additional modules such as Power Query, Power Pivot, Power Map, Virtual Basic for Application (VBA) or Power View. The latter one was soon transformed into a separate project currently known as Power BI. Can all those tools replace professional CPM system? What are they used for? What problems may occur while integrating Excel and Power BI? This article aims to answer these questions?
Data analysis — what exactly is Power BI and what is it used for?
Power View module was one of the innovative Excel add-ons. However, it was separated due to lack of extensibility options within Excel technology. Knowledge of Excel is of great advantage while using Power BI, which — just like Power View — enables analysing data and visualizing it in dashboards. Effective enterprise management is much easier when all key indicators are updated in real time. Power BI is currently available in a free-of-charge desktop or cloud version as well as in an extra paid Pro edition. One of many advantages of this solution is the possibility to load data models drawn up in Excel. This is particularly important from the perspective of enterprise digitalization as it makes tool integration much easier. The option of smooth switching from Excel to Power BI solves a number of problems with compliant historical data formatting.
However, it should be remembered that data analysis in Excel or Power BI does not suffice to maintain comprehensive and reliable analytics. Although Excel boasts broad functionality its clarity and way of presenting data is problematic. On the other hand Power BI, which enables decent visualization, lacks the functionality to analyse big, variable and heterogeneous data sets and thus it will not prove as big data tool. Analysts also complain on lack of live mode and on limited data updates, which make it harder to work in a multinational environment. Therefore business management on operational, strategic or tactical level requires additional CPM software.
Enterprise management in the SMB segment
Effective business management is one of the greats challenges for every entrepreneur. Currently small and medium enterprises from the SMB segment very often use ERP system. The possibility to adapt modules to specificity of the industry is by far the greatest advantage of dedicated software. However, in the long run it is not possible to manage business based entirely on software provider. An excellent alternative for ERP which will work well with small, medium and big enterprises is a state-of-the-art CPM software. The role of such systems is mainly to boost financial performance of an enterprise. It is also important that they extend beyond transactional layers as opposed to ERP systems. That is why digitalization of an enterprise can be fast and smooth. An excellent example could be companies which decided to implement generic ERP system to cut costs. Such scenario is potentially most expensive and problematic because implementation of any modifications is extremely time-consuming.
Collecting data — what is Power Query needed for?
Digitalization of an enterprise in most cases starts with implementing electronic flow of documents. Centralizing data in one system (e.g. CPM) is critical not only for security but also for their reliability. Collecting data from multiple business entities is much easier when all of them are capable of delivering data in one format or in supported formats.
Data from multiple sources are very often unified and integrated using Excel, which can process CSV files of size of even dozen of GB thanks to Power Query module. It also offers the possibility to automate recurring steps by means of single macros, which can be extended using simple M language as necessary. Knowledge of Power Query in the first place enables you to organize and collect data from multiple sources (e.g. local files, clouds or on-line platforms). Before doing any analysis or presentation they need to be organized in a suitable format and purged from potential errors and artifacts. The data needs to be validated against false negative and false positive entries since such results deteriorate reliability of forecasts. The drawback of such solution is lack of automatic update and complicated reporting process. Typical business user with no specific technical competence will not be able to carry out necessary reports or dashboards. To make business processes easier and facilitate access to analytics for a broader group of employees one should take into account easy to use CPM system.
Why reporting data using Power BI entails high risk of errors?
Power BI is one of the tools which is able to automate data reporting using dashboards updated in real time. The problem usually consists in compatibility of the format and proper definition of relations between particular databases. Obviously the key to success is to skilfully configure software and maintain suitable data structure. Unfortunately, senior employees in medium and big enterprises (e.g. Chief Financial Officer, CFO) often wrongly believe that Power BI is the best possible solution for planning, analysing and reporting. In practice this is only a basic tool whose functions can be successfully replaced by every professional CPM system. High rate of errors in Power BI dashboards is mainly caused by unprofessional data processing and wrongly configured relations between sources.