Data Warehousing for Professional Services with Acumatica

This article covers data warehousing, both generally and more specifically for Professional Services organizations who are looking to expand their Acumatica analytics.

In the business culture of today, maybe particularly for Professional Services companies, data is king.  Since data drives decision-making, and Professional Services teams are working smartly to stay on track and on budget with clients, projects, vendors, and so forth, data warehousing can be really effective to support Business Intelligence (BI) analytical tasks.  When I first learned about a data warehouse as a solution, I had questions, so you might as well.  In this article, I’ll go about providing the answers you need about data warehousing with Acumatica for your Professional Services organization.

A definition.  Let’s begin by clarifying that data warehouses are multi-dimensional databases.  If that doesn’t make things clear enough for you, a data warehouse can also be described as a virtual storage space or a database staged on a server, shared or stand-alone.  For comparison, an external hard drive offers space where you can store diverse kinds of files and software while today’s data warehouse solutions provide a place to house multiple data sets, operational and transactional.  Another way to think about data warehousing: if you three-dimensionalized an Excel spreadsheet, you could organize your operational and transactional data in aggregated, streamlined, and effective ways, which is precisely what data warehouses do, eliminating errors in a business user friendly technology.  Today’s commercial data warehouse solution is maintained with a technical database management program, like Microsoft SQL Server Visual Studio or Solver’s own software for warehouse management, called the BI360 Data Warehouse Manager, with a business user friendly platform and part of the BI360 Suite.

Installation.  When it comes to implementing and automating, a commercial data warehouse usually comes “out of the box,” which translates to an easy installation; then, you’ll replicate your organizational information from Acumatica and other data sources.  A consultant who excels in the extracting, transforming and loading (ETL) of your data will deploy your data warehouse.  Next, they go about automating the ETL process from your database(s).  You’ll want to go with a data warehouse that offers a pre-built integration right to the Acumatica Cloud or on premise installation if you are going for a seamless solution.  Then, a consultant with expertise in simplifying and streamlining your financial reporting, budgeting, and dashboard analyses will educate your team on how to query data from your warehouse for financial statements, planning, and data visualizations you depend on to make more informed decisions about your Professional Services company’s future.  After data warehouse configuration and staff training are complete, business end users at all levels of the organization can manage the solution without involving the IT team.

How come?  Not every Professional Services organization will be seeking a data warehouse – out of want or need – and it’s not black and white regarding whether you need one.  If you consider how important utilization, or a function of hours billed per week divided by 40/hours a week, is to your business today – or even analyzing your bill rate, or what the average billing per dollar is that you charge to customers and how it is increasing, you know how important all of this data can be to staying competitive.  Typically, this data comes from separate sources, and many companies are typically pulling it together in a software like Excel, but a data warehouse can help you avoid errors, wasted time and money, as well as frustrating manual documentation.  Furthermore, if the Acumatica server is sluggish due to the significant and sometimes simultaneous data queries your team has to perform, data warehousing provides stability and high performance without slowing down the ERP system or operational database server.  In other words, the reason to get a data warehouse is because it allows you to produce richer reports, budgets, and dashboards due to inclusion of data from diverse systems like CRM, payroll, and sales order systems.

What about an OLAP cube?  In terms of housing multiple kinds of data, a number of BI tools recommend or require an online analytical processing (OLAP) cube, so you might wonder why we aren’t talking OLAP cubes.  An OLAP cube is not a transactional SQL server database, so managing the technology requires staff with OLAP experience and skills, as in MDX query language fluency, due to their technically complex nature.  Additionally, plenty of cubes are focused on analytical data instead of transactional information.  Data warehouses are ordered by subject, and you can replicate a variety of data types to the easy-to-use, dynamic interface for flexible data management and analytics with your BI tools.

Managing a Data Warehouse.  Business end users can run today’s commercial data warehouses.  Configuration and automation of your Professional Services data replication is easy, whether you’re pushing data once or as part of a more routine task.  You can also move your data by a simple click of the mouse.  Since plenty of data warehouses are Microsoft SQL Server relational databases, organized by topic, like general ledger data, clients, projects, and billing detail, IT can stay out of the picture.  Data warehouses organically offer cross module analytical and financial aggregation and drill-down functionality by storing multiple diverse data types from distinct sources.  Concurrency control mechanisms, transactional processing, and recovery are not usually requisite, with the exception of backing up your database since they stand on their own.  A data warehouse usually comes with dimension trees, attributes, and adjustment functionality, as in currency conversion, data cleansing, eliminations, and integration techniques for a more efficient process.  You can additionally use a data warehouse to make it faster and easier to migrate from an older accounting system to Acumatica.  You can store the historical data from the old ERP system in a data warehouse, perform a historical analysis there and only bring the most recent period’s closing balance over to Acumatica, just as an example.

Data is going to continue to grow exponentially in size and importance to your organizational decision-making, maybe particularly for Professional Services companies.  Thus, data warehousing will also continue to grow in popularity, specifically since you can aggregate your diverse data and the systems they come from into one space to expand your financial reporting, planning, and dashboard analyses.  Should you prefer to rely on just one technology to gather all of your information into one high performing place without having to involve IT in the management of the tool, data warehouses can provide you that solution.  Solver offers a fully built, configurable Microsoft SQL Server-based data warehouse stand-alone and as part of the comprehensive suite of BI modules and would be happy to answer questions and generally review BI360’s easy-to-use Data Warehouse solution that enables collaborative, streamlined decision-making capabilities for your Acumatica experience as a Professional Services organization.

Leave a Reply