Consolidate Data needed for Reporting, Planning & Dashboards

The Data Warehouse (DW) is Solver’s powerful yet easy to configure module for consolidating the data needed for reporting, planning and dashboards.

The author of this page: Deirdre Lysaght
Deirdre Lysaght, Marketing Executive Apr 08, 2021

The Data Warehouse database is fully integrated with Solver’s Web Portal and its Reporting and Planning options. This enables immediate access to advanced financial and operational reporting capabilities and fully customizable budgeting, forecasting, and data entry features. The Data Warehouse is a key component of the Solver Web Portal where administrators can configure and manage the Data Warehouse, and using the native Solver integration tool, data can be loaded without the need for much technical skills or developers.

The Data Warehouse is a Microsoft SQL Azure database residing in Solver’s multitenant cloud, located in Microsoft’s global data centers.

Data Warehouse Interface

The Data Warehouse interface has been designed to be managed by business users for rapid setup and easy ongoing management of data, dimensions, trees and integrations.

Data Warehouse Components

Integrations

The Solver Data Warehouse comes with a powerful integration tool. It includes a generic connector to load data from 'CSV files as well as numerous pre-built connectors to populate data from different sources across the organisation. In addition to connectors to cloud data sources, Solver Cloud can also load data from on-premise data sources using the Solver Gateway to access databases on a local or privately hosted network.

The Solver Connectors automatically create Data Warehouse modules and dimensions the first time you run integration, or you can set these up manually through the Solver Web Portal user interface.

Modules

The Solver Data Warehouse always comes with two pre-defined financial modules, General Ledger and General Ledger Detail. Users can effortlessly turn on additional modules (up to 100) and dimensions, referred to as user-defined modules, for all other types of data, including sub-ledger and operational data. To dramatically reduce implementation time, each module contains pre-defined fields specific to that module/area. The modules can be renamed using the Data Warehouse interface. Without any need for customisation, an unlimited number of user-defined fields can be added using the Data Warehouse menu in the Solver Web Portal to further extend the transaction tables' content.

Dimensions

The Solver Data Warehouse contains pre-defined dimensions specific to each module but can be expanded up to 100 dimensions which can be shared across modules. Using the Data Warehouse interface, the dimensions can be mapped to each module and labelled as needed. Each dimension contains many attributes specific to the dimension. Each of the custom dimensions also contains attributes to customize the dimensions to match a customer’s specific data model.

Dimension Management

Dimensions can be managed within the Data Warehouse interface. There are several ways to load dimensions into the Data Warehouse:

  • Automatic import from the source database via the Solver Web Portal integration tool.

  • Manual entry: Manual entry is specifically useful for budgeting and forecasting. Since the Data Warehouse has a friendly user interface, accounts and other dimensions that do not exist in the source system(s) can be created and maintained directly within the Data Warehouse environment.

Module and Dimension Mapping

Pre-configured modules in Solver Data Warehouse come pre-built with financial and time logic. Both General Ledger and General Ledger Detail modules require that Account, Entity, Scenario and Period dimensions are populated with data for the transactions being loaded.

Data Warehouse user-defined modules do not have these constraints, allowing customers to load in any data, even data that has no financial content.

Each module can contain unique dimensions as well as dimensions that can be shared across all modules. Each module also contains standard dimensions, e.g. Period, Entity, Currency, Scenario, Category, etc. Additionally, each module contains system-defined attributes, including three amount fields and one text comment field.

Currency

The Data Warehouse contains advanced currency features which calculate foreign currency exchange. The tables accommodate exchange rates that can be directly entered into the Data Warehouse interface or imported. By combining several advanced backend features, the end-user experience is very simple yet advanced.

The currency conversion process has several elements.

  • Rate Types: Rate types can be manually entered, directly imported, or imported via an integration. Rate types such as Average, Closing, Historical, and Budget are typical.

  • Monthly Rates: Rates can be loaded or manually entered.

  • Rate Configuration: The configuration ties together the Account type, such as Balance Sheet, the Scenario such as Actual and the Rate Type such as Average.

Data Explorer

The Data Explorer is used to filter, group and display data for each Data Warehouse module so that an administrator, at any time, can see and validate what is stored in the Data Warehouse without the need to perform SQL queries or running reports. The data can be viewed within the Data Warehouse interface or exported to Excel.

Processes

Processes are SQL procedure-based rules which enable data transformation and movement within the Data Warehouse. Because rules are created as stored procedures within the Data Warehouse, the rules can be loaded into the warehouse via an XML file. The XML file contains information about the parameters used to execute the rule as well as the SQL for the data manipulation.

Practical application of rules include:

  • Currency conversion

  • Data aggregation

  • Entity eliminations

  • Allocations

  • Data movement

  • Other transformations

Typical Implementation Process

A typical implementation process of the Solver Data Warehouse would encompass the following:

  • Receive access to Solver cloud tenant.

  • Determine source systems and data to be integrated with each system and map to Solver Data Warehouse modules and dimensions.

  • Configure Solver Data Warehouse periods and tailor module and dimension names as needed (or leave default). Note: Custom labels will also be visible in Reporting, Planning & Dashboards.

  • Configure currency (as needed). Determine if exchange rates will be loaded from an external system or monthly average/closing, etc. rates will be loaded directly into the Data Warehouse currency table. If it set up as an automated process, then configure the integration to update rates in the Data Warehouse.

  • Determine import method (Solver integration tool or manual input into the exchange rate screen).

  • Configure integration tool and map to Data Warehouse module & dimension mappings.

  • Business Rules: If business rules, such as currency conversion, are to be used, ensure step #5 is completed. No additional steps required other than the running rule.

  • Document integration decisions.

  • Complete training.

To learn more about how your business could benefit from Solver CPM, join our webinar 'Streamline Month End Reporting Processes' on Thursday 29th April at 11 am via Microsoft Teams.

Contact our Solution Experts

Get in touch to find out how Storm Technology can help you grow your business with scalable, future-ready solutions.