Connecting to a SQL Data Warehouse

Owned by Joni Girardi
Last updated: Jan 11, 2023
3 min read

IMPORTANT: Click here if your data warehouse is hosted in DataSelf Cloud Services.

Requirements

  • A Microsoft SQL Server user license

  • A URL or IP to your MS SQL data warehouse

  • The MS SQL database name(s) of your data warehouse (example: DwXyzcorp)

  • A tool to connect to the cloud MS SQL database such as DataSelf ETL+, MS Excel, Crystal Reports, Tableau, Power BI, Qlikview, SSMS (SQL Server Management Studio), SSRS (SQL Server Reporting Services), and SSAS. See below for further instructions.

DataSelf ETL+

Contact DataSelf to connect your ETL+ to your private data warehouse.

SSMS

  • In SQL Server Management Studio (SSMS), click Connect -> Database Engine.

  • Enter the URL to your MS SQL data warehouse

  • Select the authentication method and credentials if applicable.

  • If your data warehouse uses a SQL database with Contained type = Partial:

    • Click Options.

    • Enter MS SQL database name in Connect to database (example: DwXyzcorp_Erp).

  • Click Connect.

The following is only required for SQL databases with Contained type = Partial.

MS Excel

  • In MS Excel, go to the Data ribbon -> Get Data -> From Database -> From SQL Server Database

  • Server box: Enter the URL to your MS SQL data warehouse

  • Database box: Enter MS SQL database name).

  • Click Ok.

  • Select Database on the left pane.

  • Enter User name and Password.

  • Click Connect.

Tableau Desktop

  • In Tableau Desktop right panel, click Microsoft SQL Server.

  • Server box: Enter the URL to your MS SQL data warehouse

  • Database box: Enter MS SQL database name

  • Select Use a specific username and password.

  • Enter User name and Password.

  • Click Sign In.

Power BI Desktop

  • In Power BI Desktop, select Get Data from the top ribbon then SQL Server.

  • Server box: Enter the URL to your MQ SQL data warehouse.

  • Database box: Enter the MS SQL database name.

  • Typically you will be using the Import data connectivity mode.

  • Select OK to proceed.

  • Select Database then input User name and Password and select Connect.

IMPORTANT: Power BI Service scheduled dataset refreshes require your Data Warehouse to be an Azure SQL Database. All other on-premises or private cloud data sources (including a MS SQL Database on an Azure VM) require the use of a Power BI Gateway to connect and allow scheduled dataset refreshes. See https://dataself.atlassian.net/wiki/spaces/TeckKB/pages/1611071910 for more information.