Connection dialog

When you first create the connection to 1010data in the Get Data window in Power BI, the following 1010data connection dialog appears:

These fields are described below.

Data Source Name

Enter the DSN you specified when you configured the ODBC driver.

Advanced Connection Properties (optional)

This field allows you to specify additional properties for the connection. For example, PropertyA=Value1;PropertyB=Value2; etc. For a complete list of ODBC connection properties, see Connection String Options in the ODBC Driver Reference Manual. For a complete list of Power BI connection properties, see Connection String Options in the Power BI Connector Reference Manual.

Advanced Options (optional)

SQL statement - not supported with DirectQuery (optional)

This field allows you to provide a SQL statement that specifies what data to return. Expand Advanced Options if necessary, and then enter or paste the SQL statement.

You can use the following types of SQL statements:

• SELECT statements extract data from a database. For example:

  SELECT * FROM retaildemo.sales_detail WHERE store=728
  

• EXECUTE statements call procedures that are stored in a database. For example:

  EXECUTE my_proc @second = 2, @first = 1, @third = 3;
  

For an example of a more complex SQL query, see Add 1010data Macro Language to a query. For a SQL reference, see SQL Compliance in the ODBC Driver Reference Manual.

Data Connectivity Mode

Import

Use Import mode to save a copy of the data in your report. As you make changes to your report, Power BI executes the underlying queries to the local cache, independent of the connector. To synchronize your report with any changes in the remote data, click Refresh from the Home menu on the Power BI ribbon.

DirectQuery (recommended)

Use DirectQuery mode to work with the remote data in real time, rather than a local copy. As you define filters, aggregate fields, or join tables, the connector executes the underlying queries to 1010data. Since Power BI queries the underlying data source, you are always viewing the most recent data.

The next time you connect to an existing data source and load a table, you will see a simpler Connection settings dialog that just asks for the data connectivity mode: