Consolidating Many Companies from Dynamics GP to Solver Cloud

Thoughtware Article Published: Aug 05, 2020
Tech Technology Technologies TW-2

Many organizations have multiple companies that need to be included in financial reports. There may be a need to consolidate the companies. But even without consolidations, the ability to report on those companies is required.

The Solver Corporate Performance Management solution is a reporting and budgeting solution. It is available as a cloud-based solution and an on-premise solution, called Private Host. With the latter option, companies can push data into the database using tools like SQL Server Integration Services. With the cloud solution, companies use the connectors available, such as the Gateway Connector. The on-premises gateway is a service that runs within the network and provides the Solver CPM solution with the ability to view SQL tables and views.

Normally, with the cloud solution, you set up integrations for each company. Below is an example of an integration that is going to integrate General Ledger Open Summary balances based on a SQL view in a company database.

SQL view

Solver provides many SQL view scripts for retrieving data. They have views for single company and multicompany. The multicompany scripts are stored in a new Solver database. They create scripts for every company. That’s not too bad when you have a handful of companies. If you have a lot of companies, you wind up with multiple SQL views for every single company. Imagine if you have 50 or 100 companies—that is a lot of SQL views.

Another Option

A few years ago, a client wanted a report that included every company—they had more than 100 companies. Creating a SQL view that included all the companies would be arduous, at best. BKD ended up creating a stored procedure that cycles through the companies listed in the Dynamics database, other than test companies. We can take the same approach here. A series of stored procedures can be created to gather the information into tables. These tables are referenced by the Solver integration. Rather than setting up integrations for every company, you can set up integrations based on only the type of data. If you are considering General Ledger, here are some data types:

  • Account segments
  • Full account string (necessary for GP)
  • Open and History Summary balances
  • Open and History Detail transactions

You might wind up with seven, eight, or more tables. Here are some suggested names, although you may want to get more descriptive when it comes to the individual segments. The fiscal periods are maintained, quite simply, in Solver.

  • SOLVER_FullAccountString
  • SOLVER_Segment1
  • SOLVER_Segment2
  • SOLVER_Segment3
  • SOLVER_GLOpenSummary

In this example there are seven or more tables; imagine having seven or eight for each company. That is a lot of tables. Having a stored procedure push data into the tables is a much more manageable solution. As you add companies, the stored procedure will automatically include them.

The process is:

  • Create the stored procedures.
  • Schedule a SQL job to run the stored procedures each night.
  • Schedule a Solver job to run the integrations each night.

These jobs are generally quick enough that they can be run during the day should it be necessary to run them.

One additional task you may need to do yourself is update reporting trees to put the new companies where they belong. For example, if you have a geographic tree, only you will know into which geographic business unit the new company should be placed.

Organizations with many companies need a method for integrating them into the Solver Data Warehouse. Using stored procedures to populate tables used for importing is one approach. They provide a dynamic method for including new companies and pushing data into the Data Warehouse. For more information, reach out to your BKD Trusted Advisor™ or use the Contact Us form below.

Related Thoughtware

Kate & Ben — How can we help you? Contact Us!

How can we help you?