This article was updated for use with GPM v11.9.0
As part of the Goliath Advanced Reporting and Analytics Module users are provided with a number of pre-built Power BI report templates to use as a quick starting point for the analysis of core metrics in your environment. This guide covers the steps necessary to connect the PowerBI reports to your database.
- Review the requirements for installing and running MS PowerBI.
- SQL Server 2014 or later is required.
- If using Goliath Performance Monitor v11.8 or earlier, review the compatibility and view requirements below.
When you first open one of the PowerBI report templates, to view data on your server, you’ll need to change the database connection, which initially is still connected to a Goliath test environment.
Please note that if you have an older version of Power BI Desktop, your ribbon may appear different from what you see in the images.
- In the report, navigate to the Home tab in the ribbon at the top of the page:
- On the Home tab, locate and click the small down arrow within the Transform data button (not the icon above the text):
- Select Data source settings:
- The Data source settings window opens.
- Click the Change Source at the bottom of the window:
- The SQL Server database window opens. Enter your server and database information. Depending on when you installed Goliath, your database name may be MonitorIT or GoliathDB.
- Click OK.
- On the Data source settings screen, click Close.
- Once the window closes, you’ll see a yellow bar at the top of the page – click Apply Changes.
- Next you're prompted to enter your credentials with access to the Goliath Database. Credentials with at least Read access to the Database are required.
- You may see the following Encryption Support dialog. If you do, click OK to continue.
Note: You may encounter an error similar to "An error happened while reading data from the provider:" In this case you will need to run PowerBI as an Administrator at least when you make the initial connection. Additional details can be found on Microsoft's support web site: Troubleshoot Opening PowerBI Desktop.
- From here, tables are refreshed from your data source. Note, in some cases establishing the connection to the Goliath database can take an extended period of time, for example, connecting over a VPN or other remote access can influence the time required to connect. Ensure the device you're using meets the requirements for running PowerBI (see above).
- While report is now connected to tables in your database, as some reports contain additional date and/or time dimension tables, it is recommended that you also click on the “Refresh” button in the ribbon to ensure that all tables are up to date:
Once the refresh completes, all report data is up to date, and you can begin to explore the report or create new visualizations.
If you encounter a scenario where the above methods result in an error (typically in cases where you have multiple data sources) you may need to change the database queries using Power Query.
To change database queries with Power Query
- From the PowerBI ribbon menu, select Transform data to access the Power Query interface:
- The Power Query editor opens. On the left side of the screen, in the Queries section, you’ll find the list of tables stored in the report.
- In the queries section, click on the table to be updated. Once the table is highlighted, on the right-hand side of the screen you’ll see a section labeled Applied Steps.
- In the “Applied Steps” section, click on the gear icon beside the “Source” step:
- The SQL Server database window opens. Enter your credentials and click OK.
- Complete this process for all tables that need to be connected to your database. Once complete, click Close & Apply button in the upper right corner.
- The Power Query Editor will close and all changes are applied automatically. Follow the steps above to complete connecting the report tables to your database.
GPM v11.8 and Earlier Compatibility and View Requirements
If using Goliath Performance Monitor v11.8 or earlier, you will need to set the appropriate compatibility settings for SQL Server and apply the ARAM Views:
- The compatibility level for your database must be 110 (SQL 2012) or 120 (SQL 2014).
- You can check the current compatibility level by running the following: EXEC sp_dbcmptlevel 'databasename'
- The compatibility level can be updated by running the following: ALTER DATABASE databasename SET COMPATIBILITY_LEVEL = [required level based on SQL Server version];
- Next apply the database views, run "ARAMViews##.#.#.#.sql" (# version number). The ARAMViews script is included with earlier versions of the PowerBI template package.