Nils R. | January 18, 2021
Top Data Warehouse Features in the Best CPM Software Apps
This article is part 5 of an 8-part series on evaluating the best CPM tools for your business. Part 5 focuses on data warehouse considerations to focus on as you are evaluating the best CPM software applications.
In the Digital Era, companies that make faster and better decisions than their counterparts are the most successful – yet accurate decisions require accurate information. Because all businesses have numerous data sources in addition to their ERP system, it takes a lot of time and effort to extract and compile information from all the internal systems. The fact that most of these systems are now based in the cloud does not make it easier. This fast decision-making requirement has led an increasing number of companies to now implement a cloud data warehouse in order to streamline data collection, reporting, and analysis. Some of these data warehouse platforms are included with Corporate Performance Management (CPM) solutions. Because CPM solutions already use databases to store financial data and budgets, these vendors sometimes offer an expandable CPM that can also serve as a data warehouse. More about this later. However, implementing a
corporate data warehouse can be risky and expensive without proper technology selection and planning. And, of course, the
return on investment (ROI) needs to be positive. When working through a software selection process to find the best data warehouse platform for your organization, there are some features that are more important than others.
Here are some of the top features to look for in a data warehouse app While most vendors can probably showcase more than 100 features in their product — something which can make software selection a virtual nightmare — there is a clear 80/20 rule that can be applied to zoom in on critical success factors. Here is a list of five major functionality areas to consider:
- Data warehouse platform
In today’s world, IT departments and business managers are eager to move their applications out of their server room and into the cloud. Therefore, most new data warehouses are now being deployed in the cloud. All the big database vendors offer their own data warehouse platforms including Microsoft, Oracle, Amazon (AWS), IBM, Snowflake, and others. Internal knowledge, cost, and other factors should be evaluated when choosing a solution. Any cloud CPM vendor that has a solution that expands into a full data warehouse generally also runs on one of these platforms, either with the cloud vendor’s database or a proprietary database.
- Homegrown versus commercial data warehouse
In the past, the majority of all businesses either managed to operate without a data warehouse or they purchased a database license like Microsoft SQL Server or Oracle and designed a homegrown system. For a mid-sized company, such a project could take upwards to nine months or more and often cost more than $100,000 in consulting fees (much more for a large enterprise). Today, however, there are a number of commercial cloud-based data warehouses with user-friendly interfaces that provide menu-driven configuration requiring few technical skills to run. As a result, most companies, especially smaller and mid-sized businesses, utilize commercial cloud data warehouses that are pre-designed and configurable through a web interface. Of course, it is critical to easily get data in and out of a data warehouse, so any good data warehouse must provide application programming interfaces (API) to allow integration tools and business intelligence (BI) solutions to connect to it. Here is an example of a commercial data warehouse interface:
- Separate data warehouse or combined with CPM solution?
If a selection process is driven by an IT department and the data warehouse solution will be managed by their team, they often prefer a stand-alone commercial data warehouse or may already have an enterprise data warehouse in use. A
CPM solution that includes data warehouse functionality becomes a one-stop reporting and planning database that can leverage the data stored in the enterprise data warehouse (where one already exists), as well in the ERP, payroll, and other systems. This combination approach may offer a lower cost and has the added advantage that CPM tools offer strong report writers, which make it easy to query the reporting and planning data warehouse directly without purchasing yet another third-party reporting tool.
- Data extraction, transformation, and loading (ETL)
ETL tends to be the most technical and tedious part of a data warehousing project. This is because it involves extracting data from various transaction sources, transforming it into the format required to store it in the data warehouse, and then setting up automated data loading. Many of the leading data warehouse and CPM solutions offer their own native ETL tools, often with pre-built connectors to popular data sources. In addition, there is a whole industry of third-party ETL vendors that can be “middleware” between your transaction sources and the data warehouse. Of course, these come with their own subscription cost. You should always request detailed information about the vendor’s integration to your systems, including the time and cost it takes to get them configured. A really good, pre-built integration should take
at the most an hour to configure while “toolboxes” can take days to set up and connect to each data source. Which one your vendor offers will therefore usually become clear when you see their estimates for the integration step in the implementation. Without good, automated integrations to your source systems, your users will end up spending a lot of wasted time on loading and possible “cleaning” of data. Rapid deployment tools will also help speed up an implementation while reducing risk and cost.
Here is an example of the advantages of a pre-built ERP integration to a cloud data warehouse and how it can enable users to get immediate, “day one” benefits from out-of-the-box budget and forecast input forms, reports and dashboards.
- Modules, dimensions, and trees
In general, data warehouses store transactions and dimensions and connect these in their schema. In commercial data warehouse user interfaces, an administrator can typically add business logic to dimensions (accounts, companies, etc.) by setting up hierarchies represented as graphical trees or attributes. These hierarchies are then used by report writers and dashboards to automate reporting and consolidations. To select the best data warehouse solution for your business needs, it is advisable to get a demonstration of the web portal. This gives you the ability to see how user friendly (or not) it is to manage the dimensions and data, and also to understand the effort it takes to set up user security as well as control access rights.
How much does a data warehouse solution cost? Cloud-based data warehouses are sold on a subscription basis and vendors have various pricing models that could include factors like:
- Number of users
- Number of data source connectors
- Number of transactions stored in the database
- Amount of data processing
Vendors that offer multi-tenant solutions versus stand-alone systems per customer have a cost advantage as upgrades are simpler and processing resources can be shared. Beyond features and the quoted price, here are some things to think about when you get prices from your vendor finalists:
- Does the annual subscription from each vendor contain the same user count and modules?
- If you are receiving a discount, how long until it resets to the list price?
- Does the vendor provide a written policy for annual price increases?
- Are the implementation estimates from each vendor for exactly the same work?
A good rule of thumb is to ask each vendor for the total subscription cost for the first 5 years. Make sure this includes any potential price increases. And, if the vendor is owned by a private equity firm, chances are that they will be sold while you are still a customer, so ensure that you receive a document stating their policy for price increases in the future, including stipulations if they are sold to another company.
Here is a tool to help you compare vendors and calculate return on investment (ROI).
Why not use the ERP system as a data warehouse? Some ERP vendors and implementation partners suggest that customers use the ERP system as a data warehouse. This could work if all or most of the data is from the ERP system itself. However, most data warehouse use cases do not work well inside an ERP database for these reasons:
- Major data loads and processing could slow down the ERP system and make daily work difficult for accountants.
- Licensing requirements for the ERP may become more costly than expected.
- Some ERP APIs may not be suitable for flexible and fast data import and export.
- ERP databases are generally not built to easily handle lots of detail from other data sources such as CRM data, helpdesk information, or other solutions.
- ERP systems typically don’t offer best-of-breed reporting, budgeting, and dashboards so chances are that you will end up exporting data out of the ERP “data warehouse” and into CPM and BI systems anyway.
Conclusion In summary, choosing a new data warehouse solution to centralize your important data in order to support front-end tools that drive better and faster decision making has increasingly become a strategic priority for organizations across all industries. As we discussed earlier, certain features are more important than others and can be key success factors – in addition to a well-executed implementation process. Here is a free vendor comparison and ROI tool to help you compare vendors across a number of different features. This tool also includes a simple return on investment (ROI) calculator that is part of the total vendor score.
Links to useful software research and evaluation assets
