Prerequisites

Before you connect, ensure you have Power BI Desktop installed.

GIS SQL Server Connection Steps

1. Open Power BI Desktop
2. On the Home tab, select SQL Server as the data source 
3. Enter details in the SQL Server database dialog box:
   1. Enter svm-gissql as the server name.
   2. Enter the database name. This will vary by user’s permissions and goals. It may be one of the following, although there are others. Contact IT GIS if you need help finding the name of your department’s database(s).
      1. ComDev
      2. PublicSpace
      3. greeleybasedata
      4. RegionalBaseData
      5. WaterResources
      6. WaterSewerData
   3. Under Data Connectivity mode, select either:
      1. Import: Data is copied into Power BI's cache. This offers faster performance but requires scheduled refreshes to stay current.
      2. DirectQuery: Power BI queries the data directly from the SQL Server in near real-time. This is suitable for large datasets or scenarios where data freshness is critical, but it can negatively affect performance.
   4. (Optional) Expand Advanced options to specify additional settings. Then click OK.
4. When prompted for credentials, select the Windows authentication type, using your current credentials, and then click Connect. 
5. An encryption support warning window may pop up. Click OK.
6. Next, the Navigator window will populate in which you can explore the data within the specified database available for import. The data is not organized in its datasets, as you see it within ArcGIS Pro. Instead, everything is alphabetized. Items ending with _evw are versioned views and are the data you should default to using.
   1. Scroll to the data you seek or search by name using the search bar in the top left corner of the window.
   2. Click on the name of a feature class or table to see a truncated preview of its contents in the center of the window.
   3. To select an item for import, click the checkbox to the left of its name. A green checkmark will appear, as will its data preview. You can bring in multiple items at the same time, but only the most recently selected item will be the one whose preview is shown. 
7. Click either Load or Transform Data. Load will load the data directly for use in Power BI reporting tool; Transform Data to open the Power Query editor for further data refinement. Because the selected data may contain sensitive information, it is recommended you select Transform Data which will open a window where you can examine tabular data and remove sensitive columns from use in the project.

Other settings and FYIs:

When you return to a Power BI project, if you are prompted with a “This preview may be up to [number] days old,” click the Refresh button.

If you wish to remove previous data source connection settings, you can manage these within a Power BI project by clicking File > Options and settings > Data source settings. In the Data source settings window, select the data source setting you wish to alter, click Clear Permissions and then Close.    

You can easily bring in additional data from previously connected SQL Server data sources in Report view, Model view, or within the PowerQuery (Transform data) window by clicking the Home tab, then Recent Sources.