Overview

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 reports to your database in order to get up and running.

Getting Started

Step 1: Prepare your Goliath database

 We support SQL Server 2014 or later. 

 For SQL you need to set the appropriate compatibility settings. Here's what would need to be done:

1. The compatibility level needs to be 110 (SQL 2012) or 120 (SQL 2014).
2. You can check the current compatibility level by running the following: EXEC sp_dbcmptlevel 'databasename'
3. The compatibility level can be updated by running the following: ALTER DATABASE databasename SET COMPATIBILITY_LEVEL = [required level based on SQL Server version];

Next, run "ARAMViews##.#.#.#.sql" (# version number)

Getting Connected

When you first open one of the reports, in order to view data on your server, you’ll need to change the database connection, which initially is still connected to a Goliath development environment.

 To change the connection, you’ll need to perform the following steps. 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.

1. In the report, navigate to the Home tab in the ribbon at the top of the page:

2. On the Home tab, locate and click on the “Transform data” text (not the icon above the text):

3.  Click on “Data source settings”:

4. This will open the Data source settings window:

5. From this screen, click the “Change Source” button at the bottom of the window:

6. This will open a new “SQL Server database” window – from here enter your server and database information and click the “OK” button:

7. On the Data source settings screen, click the “Close” button:

8. Once the window closes, you’ll see a yellow bar at the top of the page – on the right-hand side of the bar, click “Apply Changes”:

9. After clicking the “Apply Changes” button, you should be prompted to enter your credentials. Once entered, click “Connect”:

10. Once you click “Connect”, you may see the following window. If you do, click “OK” to continue:

11. From here, your tables should be refreshed from your data source:

12. 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 Have Issues Connecting

While the steps above should work in most cases, occasionally you may encounter a scenario where the above method will not work (typically in cases where you have multiple data sources, but not always). In this case, you will need to change the database using Power Query.

 The following steps will cover this method:

1. Follow the same steps covered in steps 1 and 2 above, but once you’ve clicked on the “Transform data” text, rather than select “Data source settings”, select “Transform data” to access the Power Query interface:

2. This button opens the Power Query editor. On the left-hand side of the screen, in the “Queries” section, you’ll find the list of tables stored in the report:

3. 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”:

4. In the “Applied Steps” section, click on the gear icon beside the “Source” step:

5. This will open the “SQL Server database” window referenced in step 6 of the preview section. From here, enter your credentials and click “OK”:

6. Complete this process for all tables that need to be connected to your database. Once complete, click the “Close & Apply” button in the upper right corner:

7. From here, the Power Query Editor screen will close, and all changes will be applied automatically. Follow steps 9 through 12 from the above section to complete connecting the report tables to your database.