Skip to main content
SC Treatment of Wages Related to the Federal Employee Retention Credit

Using Solver as a Data Source for Power BI

Learn how using Solver as a data source for Power BI can help you gain efficiencies and reporting advantages. Follow this step-by-step guide.
banner background

The Solver Data Warehouse can be used as a data source for Power BI. By using the Solver Data Warehouse (DW) as a data source, you can gain these advantages:

  • Less reconciliation required between report types. Using a single data source helps keep data consistent and can reduce the reconciliation of reports between the systems.
  • A consistent data structure. A consistent data structure provides users with familiarity of the data, increasing the efficiency of writing reports. The data will look the same in both systems, so users won’t have to learn two different data structures.
  • Reduction in data modeling efforts. By using the Solver DW as a data source, the data structure will be consistent and users won’t have to recreate the wheel, so to speak, in Power BI.

Requirements for Using Solver as a Data Source

To utilize Solver as a data source, you need to have the Power BI connector from Solver. Once you have the connector license, you can set up Solver to be used as a data source. To do this:

  • Go to the Data Warehouse
  • Select Configuration
  • Select API
  • Toggle the Enabled switch
  • Select the modules to be available in Power BI – When you select a module, all dimensions used by the module become available, too

API Access

Getting Data in Power BI

Now that you’ve enabled Solver for use with Power BI, it’s time to switch to Power BI to get data.
Select Get data from the startup menu.

Get data from power BI desktop

Enter Solver in the search box.

Enter solver in the search box

Select Solver on the right.

Click the Connect button.

You’ll get a message requesting an API URL from the Solver Portal.

Paste API URL from the solver portal

Go back to the API page in Solver.

Select the gear icon at the top right.

Gear icon in API access

Copy the URL that appears.

Paste the URL into the field in Power BI.

If you have never used Solver with Power BI on your computer, you will get an authentication message.

Authenticate to Solver

Go back to Solver and select the gear icon a second time.

Copy the Access Token.

Paste the information into the field in Power BI.

Then, a list of tables will become available.

List of tables in navigator

You may select as many tables as you want.

Recommended best practice: Only select one module table per Power BI report.

For example, for General Ledger, you might select these tables:

  • Module General Ledger
  • Dimension Account
  • Dimension Category
  • Dimension Department
  • Dimension Entity
  • Dimension Period
  • Dimension Scenario

After you’ve selected your tables, click the Load button.

Note: Solver uses an import method of connecting. Therefore, the speed of loading data can vary based on how much data you have and how fast your connection is. In other words, don’t be in a hurry.

Varied loading speeds

Here is how the tables will appear:

Table appearance

If you select the Data Model icon on the left, you’ll see this data structure. Notice the dimensions are joined already to the module.

Data structure

Below is how the data will look in Report Designer.

The items with arrows are the dimensions. We didn’t select all dimensions available so some of the dimensions in Report Designer are not showing as tables in Power BI. However, you could certainly add more dimensions. Use the Get Data button at the top to get more tables.

General ledger

Moving Forward

Now that you have data, the next step is to create a report. The Business Technology Solutions Team at Forvis Mazars can assist. Our professionals have certified experience with Solver corporate performance management and Microsoft Power Platform software. Use the Contact Us form below to get in touch.

 

Related FORsights

Like what you see?
Subscribe to receive tailored insights directly to your inbox.