1010data Power BI Connector

(1)

(2)

1010data Power BI Connector | Contents | 2

Introduction... 3

Power BI Connector setup... 4

Install the connector... 4

Adjust security settings... 7

Connect to 1010data from Power BI... 8

Connection dialog... 11

Using Power BI with 1010data... 14

Visualize data... 14

Enable Tracing... 18

Troubleshooting and Support... 19

(3)

Introduction

The 1010data Power BI Connector is a utility that offers self-service integration with Microsoft Power BI. This guide will get you set up with the Power BI Connector and get you started with using the connector to query and visualize data.

Note: Before you can use the Power BI Connector, you must first install the latest ODBC Driver. Follow the instructions in the next section to install the ODBC Driver and then the Power BI Connector. For detailed documentation about the ODBC Driver, see the ODBC Driver Reference Manual.

(4)

1010data Power BI Connector | Power BI Connector setup | 4

Power BI Connector setup

To set up the Power BI Connector, install and configure the ODBC driver, and then install the 1010data connector.

Install the connector

Watch the video, or follow the instructions below to install the Power BI Connector. To install the connector:

1. Contact your account representative to enable SQL connection features on your account. 2. Ensure Power BI is installed and is up to date.

3. Find the Windows ODBC package and the Power BI Connector at this link.

4. Download both the ODBC executable and the Power BI Connector file. Both files are now in your downloads directory.

(5)

6. Follow the prompts in the ODBC Driver Setup wizard to install and configure the ODBC Driver. 7. When you get to the Installation completed successfully dialog, make sure Configure ODBC Data

Source is selected and click Finish.

The DSN Configuration screen appears.

8. In the DSN Configuration screen, enter the following information:

a) Data Source Name: Name your connection (you will need to remember this for later). A DSN is the name that applications use to request a connection to a data source.

You can also use the default data source name (DSN): CData C1010 Source. b) User: Enter your user name.

c) Password: Enter your password.

d) Login URL: Enter the URL of the 1010data environment you are accessing, including the version (such as /prime-16.40) as shown. The Power BI connector is compatible with the 1010data Insights Platform V15 and above.

Note: Any version of the 1010data Insights Platform after V15 will work. However, as a best practice, we recommend using prime-latest as the version.

(6)

9. Click Test Connection.

You should see the following dialog:

10.Click OK in this dialog and click OK again to exit the DSN Configuration screen.

Note: If you need to make further changes to your DSN Configuration screen in the future, run the program ConfigureODBC in the directory C:\Program Files\Cdata\CData ODBC

Driver for 1010data.

11.Move the .pqx file you downloaded to the folder C:\Users\[USERNAME]\Documents\Power BI

Desktop\Custom Connectors.

You can now connect to Power BI using the instructions in Connect to 1010data from Power BI on page 8.

(7)

Adjust security settings

You need to first adjust the security settings in Power BI in order to use the 1010data Power BI Connector. The 1010data Power BI Connector is not yet certified by the Microsoft Power BI Certified Connector program. Therefore, you will need to adjust the Power BI security settings in order to use it.

To adjust the security settings of Power BI:

1. Launch Power BI Desktop on your local machine.

You will receive the following warning message about uncertified connectors:

2. Click OK.

3. In Power BI Desktop, select File > Options and settings > Options. The Options dialog appears.

4. Select Security under Global options.

5. Under Data Extensions, select (Not Recommended) Allow any extension to load without validation or warning.

(8)

6. Click OK.

7. From the menu, select File > Exit.

8. Restart Power BI Desktop with the new settings.

Connect to 1010data from Power BI

Once you install the ODBC Driver and 1010data Power BI Connector and adjust the security settings, you are ready to connect to 1010data from Power BI.

To start Power BI and connect to 1010data: 1. Open Microsoft Power BI.

2. Select Get Data.

(9)

3. Select 1010data and click Connect.

You can also enter 1010data in the Search box to find it easily.

A Connecting to a third-party service warning appears the first time you connect. Click Continue. A 1010data dialog appears.

4. Enter your Data Source Name from your DSN Configuration screen.

5. Under Data Connectivity mode, select DirectQuery in order to run queries in the 1010data Insights Platform.

Import is generally not recommended.

(10)

6. Click OK.

A Navigator window appears, containing all the tables available to you in your 1010data environment.

Note: Power BI uses the path and table names from the Insights Platform (not the table titles). 7. (Optional) If you enter a Data Source Name different from the one you entered in the DSN

Configuration screen during the installation process, a credential dialog appears.

a) Enter your User name and Password for the specified Data Source Name. b) Click Connect.

A Navigator window appears, containing all the tables available to you in your 1010data environment.

8. Select your table(s) from the Navigator.

(11)

9. Click the check box and click Load to load the table into Power BI.

A Create connections dialog appears while Power BI loads the data. After the data is loaded, Power BI takes you to the Report ( ) view of the application.

You can now analyze your data and create visualizations in Power BI.

Connection dialog

The 1010data connection dialog is where you enter DSN information and advanced connection properties. When you first create the connection to 1010data in the Get Data window in Power BI, the following 1010data connection dialog appears:

(12)

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 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.

(13)

Query Limit connection property. The default limit is set at "10000". You can change the Direct Query Limit in Misc section of the Connection Properties in your DSN Configuration screen.

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:

(14)

1010data Power BI Connector | Using Power BI with 1010data | 14

Using Power BI with 1010data

After you connect to 1010data, you can use all the features of Power BI on your data.

Visualize data

After connecting to your data source, you can filter and create visualizations from the data.

Once you connect to a 1010data table, you can use the full capacity of Power BI to discover and visualize insights from the data. The following is a simple example using a demo table. Refer to the Microsoft Power BI documentation for the full set of Power BI features.

The following steps show how to create a simple bar graph showing total sales by day for a single store for the year 2019.

To create a simple data visualization: 1. Connect to 1010data from Power BI.

If you have already connected to 1010data, click Recent Sources from the Home ribbon. Then click 1010 as the data source.

2. In the Navigator, select retaildemo.sales_detail. You will see a sales_detail preview pane.

3. Click the check box next to sales_detail and click Load. 4. In the Connection settings dialog, select DirectQuery.

(15)

5. Click OK.

The Power BI Desktop Report ( ) view displays a blank canvas. The Fields pane to the right displays the fields in the sales_detail table.

6. The following steps limit the sales data to store 10 for the year 2019. a) Drag the date field to the Page level filters section of the Filters pane.

Note: You can also apply filters at the report level (for all pages in the report). b) Under Filter type, select Advanced filtering.

c) Under Show items when the value, select is on or after and enter 1/1/2019. d) Under And, select is on or before and enter 12/31/2019.

e) Drag the store field to the to the Page level filters section of the Filters pane. f) Under Filter type, select Basic filtering.

g) Select store 10. h) Click Apply filter.

(16)

7.

Click the Table ( ) visualization.

A table visualization appears on the Power BI canvas.

8. With the table visualization highlighted, select date and xsales from Fields. The table displays the total sales for store 10 for each day in 2019.

(17)

Note: Creating a table is a good way to check the accuracy of your data before you create a visualization.

9.

With the table highlighted, click Stacked column chart ( ) to see a visual representation of this data. 10.Change the chart formatting as desired.

(18)

Enable Tracing

Tracing is useful for troubleshooting connectivity issues with the 1010data Power BI Connector, such as finding out why a 1010data query was not supported in DirectQuery mode.

If you wish to enable tracing, you need to select this option each time you start Power BI. Note: You may want to move the contents of the Traces directory (C:\Users

\[USERNAME]\AppData\Local\Microsoft\Power BI Desktop\Traces) before enabling tracing in Power BI Desktop.

To enable tracing:

1. From the menu, select File > Options and settings > Options. The Options dialog appears.

2. In the Options dialog, select Diagnostics.

(19)

Traces usually appear in C:\Users\[USERNAME]\AppData\Local\Microsoft\Power BI

Desktop\Traces, with a file name beginning with PBIDesktop.

Troubleshooting and Support

If you are encountering issues with your ODBC Driver configuration, 1010data offers support.

In order to make sure you have captured all the information necessary for our support staff, make sure you take the following steps prior to contacting 1010data.

• Please confirm that the ODBC Driver is configured to access the Insights Platform V15 or above. If you get a 500 Internal Server Error, this might be the case. You can run the program ConfigureODBC in the directory C:\Program Files\Cdata\CData ODBC Driver for

(20)

Once in the ODBC Driver for 1010data - DSN Configuration screen, check the Login URL. It should reference version 15 or above.

• Please also make sure you have the most recent version of the ODBC Driver installed on your system before you contact support. You can find the version number you are using by running ConfigureODBC. See above for directions on accessing ConfigureODBC.

Once in the ODBC Driver for 1010data - DSN Configuration screen, click the License tab, and you will see the version number of the ODBC Driver.

You can find out what the latest version of the driver is by checking the CData change log. • If you want to change your credentials for a connection, you can run into problems with a cached

connection that you no longer want. To clear existing credententials bound to a DSN, do the following: • In Power BI Desktop, select File > Options and settings > Data source settings.

• In the Data source settings dialog, select a DSN and click Edit Permissions.

(21)

difficulties, you will need to create a log and sent it to 1010data support. To create a log and contact support:

1. Run the program ConfigureODBC in the directory C:\Program Files\Cdata\CData ODBC

Driver for 1010data.

The DSN Configuration screen appears.

2. In the Connection tab, under Logging, enter the name of your log file. The user using the DSN must have permission to write to this file. For this example, we will name the log file myLog.txt.

By default, the new log file is placed in the directory C:\Users\[USERNAME]\AppData\Roaming

\CData\C1010Data Provider\Schema.

Note: You can move an existing log file to a different directory. However, you must point to the new directory within the DSN Configuration screen. Click the ellipsis ( ) icon to the right of the log file name and select the new location of the log file.

3. Under Max Log File Count, enter the maximum number of log files to keep. When the limit is hit, a new log is created in the same folder with the date and time appended to the end of the file name. The oldest log will be deleted.

4. Under Max Log File Size, enter the maximum size the log file can be. When the limit is hit, a new log is created in the same folder with the date and time appended to the end of the file name. (An old log may be deleted, depending on the Max Log File Count value.)

5. Under Verbosity, enter the level of verbosity of the log file as follows:

• A verbosity of 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors.

• A verbosity of 2 will log everything included in verbosity 1, cache queries, and additional information about the request, if applicable, such as HTTP headers.

• A verbosity of 3 will additionally log the body of the request and the response.

• A verbosity of 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.

• A verbosity of 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.

Remember that the higher the verbosity, the slower the execution times.

(22)

6. Click OK.

7. Reproduce the issue you are experiencing. The driver will produce the file myLog.txt.

Consider deleting old logging information if you are appending to an existing log file. We only want the information pertaining to the issue you are experiencing.

8. Log into the 1010data Support Portal at https://support.1010data.com. 9. Click Create New Ticket.

(23)

10.Complete the following fields:

Requester This field lists the email address associated with your Insights Platform account. You can change the default email address.

Subject Enter the subject of your support request. Priority (optional) Select the priority of your support request.

Description Enter a detailed explanation of the question, problem, or request. 11.Click +Attach a file and include your log file.

12.Click Submit.

(24)

1010data Power BI Connector | External Reference Manuals | 24

External Reference Manuals

Refer to the CDATA reference manuals for technical specifications for the ODBC Driver and Power BI Connector.