Introduction
Solver, Inc. is the leading provider of cloud corporate performance management (CPM) solutions. The Solver Suite provides users with web driven reporting, planning, dashboarding and many more features, all sitting on top of an enterprise level data warehouse for financial and operational CPM purposes.
The Power BI certified connector is a connector built into Power BI Desktop. The connector utilizes the Solver API to access data from the Solver Data Warehouse. The Solver API handles the data preparation for the Power BI connector.
Power BI version:
This guide is built using Power BI Version: Version: 2.68.5432.661 64-bit (April 2019). The April or newer version of Power BI desktop is required. Learn about Power BI Desktop here: https://docs.microsoft.com/en-us/power-bi/desktop-what-is-desktop
Getting Started
This section will detail enabling the Solver External API connector and connecting to Power BI.
- Download and install Power BI desktop. Start here: https://powerbi.microsoft.com
- Once Power BI desktop is installed, navigate to your Solver tenant.
- From the Solver main menu, click Data Warehouse→ Configuration→ API
Note: this is a licensed feature. To purchase access to the API connector, please contact Solver.
- Click the Disabled button to set it to Enabled.
- Select the modules that you would like to begin pulling data from and save by clicking the
icon in the top right corner of the page. This will generate the access to the selected modules and related dimensions.
Connecting to Solver and Loading Data
- From the Power BI ribbon:
- Click on Get Data
- Type in “Solver” to find the Solver data connector.
- Select Solver – Budgeting & Financial Reporting (beta)
- A prompt with a Preview connector message will appear to let the user know that this product is still in beta. Click Continue.
- Navigate to the API tab within Solver cloud, click the actions gear icon
and select the “URL”. This will copy the API URL for that tenant to the machine’s clipboard.
- Within Power BI, paste the url copied in the previous step into the text box and click “OK”.
- The next prompt is for the API token from Solver. To do this:
- On the Solver API screen, click the Actions icon again and select Access Token. This will automatically copy the token to the clipboard.
- Navigate back to the Solver window and paste in the copied token.
- Once authentication completed, the Power BI Navigator window will appear. The Navigator window will list all modules selected on the External API page within Solver, as well as all dimensions associated with the selected modules.
- Select the modules and related dimensions to import into Power BI and click the Load button.
Module & Dimension Relationship
Selecting to expose a Module within the Solver API, will automatically map and expose the related dimensions. To review the dimensions mapped to each module, navigate to the Solver Overview page. The Overview page displays every module and related dimensions.
- Data will begin loading for each module and dimension selected. This may take several minutes, depending on the size of the data. Please note that size limitations do exist, depending on the version of Power BI desktop used.
Managing Power BI Desktop Connector
Updating the Solver API
If changes are made to the modules or dimensions, for instance if a new attribute is added, deleted or edited, the API will need to be updated to reflect the changes.
Note: Changes to the Data Warehouse structure may break existing Power BI dashboards. For example, if a dimension or attribute name used in a dashboard is changed or deleted, the dashboard will fail.
- Open the Solver Portal and navigate to the API screen, under the Data Warehouse -> Configuration menu.
- Unselect the Module(s) which contain the changes and click the Save icon
- Reselect the Module(s) which contain the changes and click the Save icon once again. This will update all related dimensions and views to include the latest changes. This includes related dimensions added, removed or edited as well as dimension and module attributes added, removed or edited.
Refreshing Data in Power BI Desktop
To refresh Power BI desktop with the latest data within Solver, click the Refresh button on the Power BI ribbon. This will refresh all data as well as any changes to the Dimensions and Modules selected.
To refresh an individual dimension or module, on the far-right FIELDS section, click the … and select Refresh data.
Publish Dashboard to Power BI Cloud
Once a Dashboard or other visualization is created in Power BI Desktop, the results can be published to the Power BI web application.
- Save the current desktop Dashboard.
- Click on the Power BI Home ribbon and select Publish to share the dashboard.
- The prompt will display available destinations based on your Microsoft Power BI user.
- Power BI Desktop will publish the report and related data set to Power BI.
Refreshing Data in Power BI Web Application
Once the report is published to the Power BI web application, the data can be refreshed manually or scheduled directly from the Power BI web application.
Note: To refresh the data directly from the Power BI web application, you will need your Solver API Access Token. The token is found on the API page within Solver.
To manually refresh or schedule a refresh, follow these steps:
- Log into the Power BI web application https://app.powerbi.com
- Expand the My Workspace section to reveal the DATASETS you have published
- To refresh a dataset, click on the … to the right of the data set.
- Click the REFRESH NOW option
Note: You will receive an error the first time you click refresh, follow these steps to configure the permissions
- Click Close to continue to add the correct permission. You will be redirected to the Dataset management page. Click the Edit credentials link.
- The Account Key is required. To get the Account key, navigate to your Solver tenant -> Data Warehouse -> Configuration -> API page and click the gear icon in the upper right to copy the Access Token.
- Select the privacy level for the dataset. Learn more about privacy levels here: https://docs.microsoft.com/en-us/power-bi/desktop-privacy-levels
- Click Sign in to connect to Solver Cloud.
- You can now manually refresh or schedule a refresh from within Power BI.
- To learn more about scheduling a refresh, follow this link: https://docs.microsoft.com/en-us/power-bi/refresh-scheduled-refresh
- You can also view the Refresh history, including refreshes in progress from the Datasets menu.
Appendix
Additional Resources
- Getting Started with Power BI: https://docs.microsoft.com/en-us/power-bi/desktop-getting-started
Troubleshooting
- Solver Connector does not show in Power BI Desktop.
Try the following:
- Ensure that you are running the latest version of Power BI Desktop
Common Questions
- How do I post feedback?
Within Solver, click the Feedback button on the upper part of the screen. Note that the feedback is for Power BI and/or API and enter comment. - What is the cost of the Solver – Power BI connector?
The Power BI connector is the same price as other Data Connectors found on the Solver order form. - Is the data filtered based on the users data access rights configured within Solver?
Not at this time. It will be considered for a future release. Data access limitations can be managed within Power BI. - Is the API key by tenant or by user?
The Authentication is stored by user account. This key should be kept private as it is the authentication to the Solver API. - Do I need to select the dimensions associated with the module?
In order to import the related dimensions and attributes, they must be selected in the Navigator. However, the modules can be imported without selecting the related dimensions, if desired. The benefits of selecting the dimension(s) is that the visualizations can be built based on the dimension attributes. For example, if the chart should be based on the Entity description, not just an Entity code, the dimension must be selected in the Navigator. - Are the Dimensions automatically joined to the modules?
Yes. The connector includes logic to map each related dimension to the selected module using the “dimension code” field. However, the dimensions must be selected to be included in the import on the Power BI navigator screen. - Can I use the API to access Solver data from other applications besides Power BI?
Direct access to the API (other than Power BI) is not currently supported. It is on the roadmap and expected to be made available near or soon after the general availability of the Power BI connector. - Does the Solver API would for other Data Visualization applications like Tableau?
The Solver product team is reviewing connectors from other popular visualization applications.
Appendix
Solver Support
The Solver Support Center (support.solverglobal.com) is the central location for users to learn more about the Solver Suite. From opening and managing your support tickets to reading knowledgebase articles about the product, the Solver Support Center has everything a user will need.
Users may contact Solver Support if they have questions about the Solver Suite. One of our technical support consultants will gladly assist you.
Solver Academy
The Solver Academy is an eLearning subscription based learning platform for Solver users to access. Users can find detailed video material on many Solver Suite features along with training material and certifications. To begin, visit academy.solvertraining.com.