Power BI Connector for 1010data 2020

Power BI Connector for 1010data 2020

Power BI Connector for 1010data 2020 - Build 19.0.7333

Overview

The Power BI Connector for 1010data 2020 offers self-service integration with Microsoft Power BI. The connector facilitates live access to 1010Data data in Power BI from the Get Data window. The connector also provides direct querying to visualize and analyze 1010Data data.

Getting Started

See Getting Started for a guide to create the 1010Data data source and connect from the Get Data window.

Using the Data Connector

See Using the Connector for a walk-through of creating reports that query 1010Data in real time.

SQL Compliance

See SQL Compliance for a syntax reference and code examples outlining the supported SQL.

Caching Data

The connector supports Power BI's ability to embed data in a report, but it also supports robust caching. See Caching Data to configure replication and caching for a range of scenarios common to remote data access. Configurations include:

  • Autocache: Automatically cache data to a lightweight database. Save data for later offline use or enable fast reporting from the cache.
  • Replication: Copy data to local and cloud data stores such as Oracle, SQL Server, Google Cloud SQL, and so on. The replication commands allow for intelligent incremental updates to cached data.
  • No caching: Work with remote data only. No local cache file is created.

Data Model

See Data Model for information on the available database objects and how to use SQL to work with 1010Data APIs. This section provides more detailed information on querying specific 1010Data entities.

Connection String Options

The Connection properties describe the various options that can be used to establish a connection.

Power BI Connector for 1010data 2020

Getting Started

The Power BI Connector for 1010data 2020 is built on top of an ODBC driver. This section discusses how to install the connector, create a DSN, and connect from Power BI.

Installing the Connector

Installing the Connector describes how to install the Power BI Connector for 1010data 2020.

Connecting from Power BI

Getting Data shows how to connect to the DSN and build a query to 1010Data from the Get Data window.

1010Data Version Support

The connector uses the 1010Data ODBC API to retrieve data.

Power BI Connector for 1010data 2020

Installing the Connector

The Power BI Connector for 1010data 2020 includes comprehensive high-performance data access, real-time integration, extensive metadata discovery, and robust SQL-92 support.

Install the Connector

Complete the following steps to install the connector:

  1. Contact your account representative to enable SQL connection features on your account
  2. Ensure PowerBI is installed and is up to date
  3. Download the most recent version of the ODBC package from this link
  4. Install the appropriate ODBC package to your local machine
  5. Use the prompt to Finish and configure the PowerBI Connector. If the prompt doesn't appear you can access it by finding the application "ConfigureODBC.exe" in the folder where you installed the application
    Configure
  6. In the configuration screen perform the following changes:
    • Under Authentication:
      • Data Source Name: Name your connection (you will need to remember this for later)
      • User: Enter your username
      • Password: Enter your password
      • Login Server: Enter the URL of the 1010data environment you are accessing including the version (such as /beta-latest) as shown. The PowerBI connector is only compatible for Edge V15 and above

    Authentication
  7. Download the connector.DB1010data.pqx file
  8. Place the .pqx file in the folder This PC > Documents > Power BI Desktop > Custom Connectors
  9. Open Microsoft PowerBI
    Open PowerBI
  10. Select GetData
  11. Select the 1010data connector from the list
    GetData
  12. Enter your DSN name from Step 5 and select the "DirectQuery" option from the radial in order to run queries in the Edgedb
    DirectQuery
  13. You will be prompted to enter your credentials
    Enter Credentials
  14. Finally, select your table(s) on the next modal window and you should be connected and able to analyze your data in PowerBI
    Select Table

The installation process creates a data source name (DSN) called CData Power BI C1010. A DSN is the name that applications use to request a connection to a data source.

Power BI Connector for 1010data 2020

Using the Connector

The Power BI Connector for 1010data 2020 hooks into Power BI's two modes for Querying Data:

  • DirectQuery: Visualizing data in real time by connecting directly to the data at the source.
  • Import: Embedding data in a report, which can be refreshed on demand. This is the most common way to get data. Importing data takes advantage of the Power BI query engine.

You select the data connectivity mode when Getting Data. The following sections describe the basics of Visualizing Data, which are defining filters, aggregating data, and joining tables when working with remote data.

Power BI Connector for 1010data 2020

Querying Data

Select a data connectivity mode when you create the connection to 1010Data in the Get Data window. The connector fully integrates 1010Data connectivity into the two data connectivity modes in Power BI: DirectQuery and data import.

Using DirectQuery

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.

Note: DirectQuery mode is limited by the DirectQueryLimit connection property.

Using Data Import

Use data 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 ribbon.

Advanced Connection Properties (optional)

This field allows you to specify properties for the connection. For example, PropertyA=Value1;PropertyB=Value2;

Advanced Options (optional)

This field allows you to provide a SQL statement that specifies what data to return. To configure this option, expand the Advanced Options area and then, in the SQL statement field, type or paste the SQL statement. Note that SQL statements are not supported in DirectQuery mode.

You can use the following types of SQL statements:

  • SELECT Statements extract data from a database. For example:
    SELECT * FROM Account
  • EXECUTE Statements call procedures that are stored in a database. For example:
    EXECUTE my_proc @second = 2, @first = 1, @third = 3;

Power BI Connector for 1010data 2020

Visualizing Data

After Getting Data, you can create data visualizations in the Report view by dragging fields from the Fields pane onto the canvas. This section describes how to use visualizations to display insights that have been discovered in the data.

Creating and Working with Data Visualizations

The following example shows how to create and work with data visualizations, using a pie chart as an example. See Data Model for more specifics on querying 1010Data tables.

  1. Select a pie chart icon in the Visualizations pane.
  2. Select a dimension in the Fields pane.
  3. Select a measure in the Fields pane.

You can change sort options by clicking the ellipsis (...) button for the chart. Options to select the sort column and change the sort order are displayed.

Highlighting and Filtering Data

Highlighting and filtering change the focus on the data. Filtering removes unfocused data from visualizations; highlighting does not remove data, but instead highlights a subset of the visible data; the unhighlighted data remains visible but dimmed.

Highlight fields by clicking them. You can apply filters at the page level or at the report level. To create a filter, drag fields onto the Filters pane. Select the filter type and filter options in the Filters pane.

Creating Real-Time Visualizations

If you selected DirectQuery data connectivity mode when you created the connection, the connector builds a new SELECT WHERE clause as you change the filter.

Power BI Connector for 1010data 2020

SQL Compliance

SELECT Statements

See SELECT Statements for a syntax reference and examples.

See Data Model for information on the capabilities of the 1010Data API.

CACHE Statements

CACHE statements allow granular control over the connector's caching functionality. For a syntax reference and examples, see CACHE Statements.

For more information on the caching feature, see Caching Data.

EXECUTE Statements

Use EXECUTE or EXEC statements to execute stored procedures. See EXECUTE Statements for a syntax reference and examples.

Names and Quoting

  • Table and column names are considered identifier names; as such, they are restricted to the following characters: [A-Z, a-z, 0-9, _:@].
  • To use a table or column name with characters not listed above, the name must be quoted using square brackets ([name]) in any SQL statement.
  • Parameter names can optionally start with the @ symbol (e.g., @p1 or @CustomerName) and cannot be quoted.
  • Strings must be quoted using single quotes (e.g., 'John Doe').

Power BI Connector for 1010data 2020

SELECT Statements

A SELECT statement can consist of the following basic clauses. This statement can be accessed using the Odbc.Query function in the M formula language.

  • SELECT
  • INTO
  • FROM
  • JOIN
  • WHERE
  • GROUP BY
  • HAVING
  • UNION
  • ORDER BY
  • LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the connector:

SELECT {
  [ TOP <numeric_literal> | DISTINCT ]
  { 
    * 
    | { 
        <expression> [ [ AS ] <column_reference> ] 
        | { <table_name> | <correlation_name> } .* 
      } [ , ... ] 
  }
  [ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
  { 
    FROM <table_reference> [ [ AS ] <identifier> ] 
  } [ , ... ]
  [ [  
      INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } 
    ] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ] 
  ] [ ... ] 
  [ WHERE <search_condition> ]
  [ GROUP BY <column_reference> [ , ... ]
  [ HAVING <search_condition> ]
  [ UNION [ ALL ] <select_statement> ]
  [ 
    ORDER BY 
    <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
  ]
  [ 
    LIMIT <expression>
    [ 
      { OFFSET | , }
      <expression> 
    ]
  ] 
}

<expression> ::=
  | <column_reference>
  | @ <parameter> 
  | ?
  | COUNT( * | { [ DISTINCT ] <expression> } )
  | { AVG | MAX | MIN | SUM | COUNT } ( <expression> ) 
  | NULLIF ( <expression> , <expression> ) 
  | COALESCE ( <expression> , ... ) 
  | CASE <expression>
      WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
    [ ELSE { <expression> | NULL } ]
    END 
  | <literal>
  | <sql_function> 

<search_condition> ::= 
  {
    <expression> { = | > | < | >= | <= | <> | != | LIKE | NOT_LIKE | IN | NOT_IN | IS_NULL | IS_NOT_NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
  } [ { AND | OR } ... ] 

Examples

  1. Return all columns:
    SELECT * FROM pub.demo.baseball.master
  2. Rename a column:
    SELECT [Firstname] AS MY_Firstname FROM pub.demo.baseball.master
  3. Cast a column's data as a different data type:
    SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM pub.demo.baseball.master
  4. Search data:
    SELECT * FROM pub.demo.baseball.master WHERE LastName = 'B%';
  5. The 1010Data APIs support the following operators in the WHERE clause: =, !=, <>, >=, <=, >, <, LIKE, NOT LIKE, IS NULL, IS NOT NULL, AND, OR, NOT, IN.
    SELECT * FROM pub.demo.baseball.master WHERE LastName = 'B%';
  6. Return the number of items matching the query criteria:
    SELECT COUNT(*) AS MyCount FROM pub.demo.baseball.master 
  7. Return the unique items matching the query criteria:
    SELECT DISTINCT Firstname FROM pub.demo.baseball.master 
  8. Summarize data:
    SELECT Firstname, MAX(AnnualRevenue) FROM pub.demo.baseball.master  GROUP BY Firstname
    See Aggregate Functions for details.
  9. Retrieve data from multiple tables.
    SELECT B.ab, B.h, B.year, B.firstname, P.era FROM "pub.demo.baseball.batting" AS B INNER JOIN "pub.demo.baseball.pitching" As P ON B.firstname = P.firstname
    See JOIN Queries for details.
  10. Sort a result set in ascending order:
    SELECT lahmanid, Firstname FROM pub.demo.baseball.master  ORDER BY Firstname ASC
  11. Restrict a result set to the specified number of rows:
    SELECT lahmanid, Firstname FROM pub.demo.baseball.master LIMIT 10 
  12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
    SELECT * FROM pub.demo.baseball.master WHERE LastName = @param
See Explicitly Caching Data for information on using the SELECT statement in offline mode.

Pseudo Columns

Some input-only fields are available in SELECT statements. These fields, called pseudo columns, do not appear as regular columns in the results, yet may be specified as part of the WHERE clause. You can use pseudo columns to access additional features from 1010Data.

    SELECT * FROM pub.demo.baseball.master WHERE LastName = 'B%'
    

Power BI Connector for 1010data 2020

Aggregate Functions

Examples of Aggregate Functions

Below are several examples of SQL aggregate functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a reporting tool.

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM pub.demo.baseball.master WHERE LastName = 'B%'

AVG

Returns the average of the column values.

SELECT Firstname, AVG(AnnualRevenue) FROM pub.demo.baseball.master WHERE LastName = 'B%' GROUP BY Firstname

MIN

Returns the minimum column value.

SELECT MIN(AnnualRevenue), Firstname FROM pub.demo.baseball.master WHERE LastName = 'B%' GROUP BY Firstname

MAX

Returns the maximum column value.

SELECT Firstname, MAX(AnnualRevenue) FROM pub.demo.baseball.master WHERE LastName = 'B%' GROUP BY Firstname

SUM

Returns the total sum of the column values.

SELECT SUM(AnnualRevenue) FROM pub.demo.baseball.master WHERE LastName = 'B%'

Power BI Connector for 1010data 2020

JOIN Queries

This section provides information about the features and restrictions that are specific to how the Power BI Connector for 1010data 2020 supports joins. The Power BI Connector for 1010data 2020 supports join queries between any tables in 1010Data. Tables in 1010Data do not contain relationship information. Instead, data can be joined between any two tables, but with some limitations. The only joins available are INNER and LEFT. All other join types will fail.

SELECT B.ab, B.h, B.year, B.firstname, P.era FROM "pub.demo.baseball.batting" AS B INNER JOIN "pub.demo.baseball.pitching" As P ON B.firstname = P.firstname

Power BI Connector for 1010data 2020

SELECT INTO Statements

You can use the SELECT INTO statement to export formatted data to a file. This statement can be accessed using the Odbc.Query function in the M formula language.

Data Export with an SQL Query

The following query exports data into a file formatted in comma-separated values (CSV):

SELECT lahmanid, Firstname INTO [csv://pub.demo.baseball.master.txt] FROM [pub.demo.baseball.master] WHERE LastName = 'B%'
You can specify other formats in the file URI. The possible delimiters are tab, semicolon, and comma with the default being a comma. The following example exports tab-separated values:
SELECT lahmanid, Firstname INTO [csv://pub.demo.baseball.master.txt;delimiter=tab] FROM [pub.demo.baseball.master] WHERE LastName = 'B%'

C#

String connectionString = "User=user@company.com;Password=password;";

using (C1010Connection connection = new C1010Connection(connectionString)) {
  C1010Command cmd = new C1010Command("SELECT lahmanid, Firstname INTO [csv://pub.demo.baseball.master.txt] FROM [pub.demo.baseball.master] WHERE LastName = 'B%' ", connection);
  int rowsAffected = cmd.ExecuteNonQuery();
} 

VB.NET

Dim connectionString As [String] = "User=user@company.com;Password=password;" 

Using connection As New C1010Connection(connectionString)
  Dim cmd As New C1010Command("SELECT lahmanid, Firstname INTO [csv://pub.demo.baseball.master.txt] FROM [pub.demo.baseball.master] WHERE LastName = 'B%'", connection)
  Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
End Using
You can specify other file formats in the URI. The following example exports tab-separated values:

C#

String connectionString = "User=user@company.com;Password=password;";

using (C1010Connection connection = new C1010Connection(connectionString)) {
  C1010Command cmd = new C1010Command("SELECT * INTO [pub.demo.baseball.master] IN [csv://filename=c:/pub.demo.baseball.master.csv;delimiter=tab] FROM [pub.demo.baseball.master] WHERE LastName = 'B%'", connection);
  int rowsAffected = cmd.ExecuteNonQuery();
} 

VB.NET

Dim connectionString As [String] = "User=user@company.com;Password=password;" 

Using connection As New C1010Connection(connectionString)
  Dim cmd As New C1010Command("SELECT * INTO [pub.demo.baseball.master] IN [csv://filename=c:/pub.demo.baseball.master.csv;delimiter=tab] FROM [pub.demo.baseball.master] WHERE LastName = 'B%'", connection)
  Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
End Using

Power BI Connector for 1010data 2020

CACHE Statements

When caching is enabled, CACHE statements provide complete control over the data that is cached and the table to which it is cached. The CACHE statement executes the SELECT statement specified and caches its results to a table with the same name in the cache database or to table specified in <cached_table_name>. The connector updates or inserts rows to the cache depending on whether or not they already exist in the cache, so the primary key, which is used to identify existing rows, must be included in the selected columns.

This statement can be accessed using the Odbc.Query function in the M formula language.

See Caching Data for more information on different caching strategies.

CACHE Statement Syntax

The cache statement may include the following options that alter its behavior:

CACHE [ <cached_table_name> ] [ WITH TRUNCATE | AUTOCOMMIT | SCHEMA ONLY | DROP EXISTING | ALTER SCHEMA ] <select_statement> 

WITH TRUNCATE

If this option is set, the connector removes existing rows in the cache table before adding the selected rows. Use this option if you want to refresh the entire cache table but keep its existing schema.

AUTOCOMMIT

If this option is set, the connector commits each row individually. Use this option if you want to ignore the rows that could not be cached due to some reason. By default, the entire result set is cached as a single transaction.

DROP EXISTING

If this option is set, the connector drops the existing cache table before caching the new results. Use this option if you want to refresh the entire cache table, including its schema.

SCHEMA ONLY

If this option is set, the connector creates the cache table based on the SELECT statement without executing the query.

ALTER SCHEMA

If this option is set, the connector alters the schema of the existing table in the cache if it does not match the schema of the SELECT statement. This option results in new columns or dropped columns, if the schema of the SELECT statement does not match the cached table.

Common Queries

Use the following cache statement to cache all rows of a table:

CACHE SELECT * FROM pub.demo.baseball.master

Use the following cache statement to cache all rows of a table into the cache table Cachedpub.demo.baseball.master:

CACHE Cachedpub.demo.baseball.master SELECT * FROM pub.demo.baseball.master

Use the following cache statement for incremental caching. The DateModified column may not exist in all tables. The cache statement shows how incremental caching would work if there were such a column. Also, notice that, in this case, the WITH TRUNCATE and DROP EXISTING options are specifically omitted, which would have deleted all existing rows.

CACHE Cachedpub.demo.baseball.master SELECT * FROM pub.demo.baseball.master WHERE DateModified > '2013-04-04'

Use the following cache statements to create a table with all available columns that will then cache only a few of them. The sequence of statements cache only lahmanid and Firstname even though the cache table Cachedpub.demo.baseball.master has all the columns in pub.demo.baseball.master.

CACHE Cachedpub.demo.baseball.master SCHEMA ONLY SELECT * FROM pub.demo.baseball.master
CACHE Cachedpub.demo.baseball.master SELECT lahmanid, Firstname FROM pub.demo.baseball.master

Power BI Connector for 1010data 2020

EXECUTE Statements

To execute stored procedures, you can use EXECUTE or EXEC statements. This statement can be accessed using the Odbc.Query function in the M formula language.

EXEC and EXECUTE assign stored procedure inputs, referenced by name, to values or parameter names.

Stored Procedure Syntax

To execute a stored procedure as an SQL statement, use the following syntax:

 
{ EXECUTE | EXEC } <stored_proc_name> 
{
  [ @ ] <input_name> = <expression>
} [ , ... ]

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

Example Statements

Reference stored procedure inputs by name:

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

Execute a parameterized stored procedure statement:

EXECUTE my_proc second = @p1, first = @p2, third = @p3; 

Power BI Connector for 1010data 2020

Caching Data

Caching Data

Caching data provides several benefits, including faster access to data and reducing the number of API calls, which improve performance. The connector supports a simple caching model where multiple connections can also share the cache over time. You can enable and configure caching features by setting the necessary connection properties.

Contents

The sections in this chapter detail the connector's caching functionality and link to the corresponding connection properties, as well as SQL statements.

Configuring the Cache Connection

Configuring the Cache Connection describes the properties that you can set when configuring the cache database.

Caching Metadata

Caching Metadata describes the CacheMetadata property. This property determines whether or not to cache the table metadata to a file store.

Automatically Caching Data

Automatically Caching Data describes how the connector automatically refreshes the cache when the AutoCache property is set.

Explicitly Caching Data

Explicitly Caching Data describes how you can decide what data is stored in the cache and when it is updated.

Data Type Mapping

Data Type Mapping shows the mappings between the data types configured in the schema and the data types in the database.

Power BI Connector for 1010data 2020

Configuring the Cache Connection

Configuring the Caching Database

This section describes the properties for caching data to the persistent store of your choice.

CacheLocation

The CacheLocation property species the path to a file-system-based database. When caching is enabled, a file-system-based database is used by default. If CacheLocation is not specified, this database is stored at the path in Location. If neither of these connection properties are specified, the connector uses a platform-dependent default location.

CacheConnection

The CacheConnection property specifies a database driver and the connection string to the caching database.

CacheProvider

The CacheProvider property specifies a database driver and the connection string to the caching database.

Power BI Connector for 1010data 2020

Automatically Caching Data

Automatically caching data is useful when you do not want to rebuild the cache for each query. When you query data for the first time, the connector automatically initializes and builds a cache in the background. When AutoCache = true, the connector uses the cache for subsequent query executions, resulting in faster response times.

Configuring Automatic Caching

Caching the pub.demo.baseball.master Table

The following example caches the pub.demo.baseball.master table in the file specified by the CacheLocation property of the connection string.

SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = 'B%'

Common Use Case

A common use for automatically caching data is to improve driver performance when making repeated requests to a live data source, such as building a report or creating a visualization. With auto caching enabled, repeated requests to the same data may be executed in a short period of time, but within an allowable tolerance (CacheTolerance) of what is considered "live" data.

Power BI Connector for 1010data 2020

Explicitly Caching Data

With explicit caching (AutoCache = false), you decide exactly what data is cached and when to query the cache instead of the live data. Explicit caching gives you full control over the cache contents by using CACHE Statements. This section describes some strategies to use the caching features offered by the connector.

Creating the Cache

To load data in the cache, issue the following statement.

CACHE SELECT * FROM tableName WHERE ...

Once the statement is issued, any matching data in tableName is loaded into the corresponding table.

Updating the Cache

This section describes two ways to update the cache.

Updating with the SELECT Statement

The following example shows a statement that can update modified rows and add missing rows in the cached table. However, this statement does not delete extra rows that are already in the cache. This statement only merges the new rows or updates the existing rows.

CACHE SELECT * FROM pub.demo.baseball.master WHERE LastName = 'B%'

Updating with the TRUNCATE Statement

The following example shows a statement that can update modified rows and add missing rows in the cached table. This statement can also delete rows in the cache table that are not present in the live data source.

  CACHE WITH TRUNCATE SELECT * FROM pub.demo.baseball.master WHERE LastName = 'B%'
  

Query the Data in Online or Offline Mode

This section describes how to query the data in online or offline mode.

Online: Select Cached Tables

You can use the tableName#CACHE syntax to explicitly execute queries to the cache while still online, as shown in the following example.

SELECT * FROM pub.demo.baseball.master#CACHE

Offline: Select Cached Tables

With Offline = true, SELECT statements always execute against the local cache database, regardless of whether you explicitly specify the cached table or not. Modification of the cache is disabled in Offline mode to prevent accidentally updating only the cached data. Executing a DELETE/UPDATE/INSERT statement while in Offline mode results in an exception.

The following example selects from the local cache but not the live data source because Offline = true.

SELECT * FROM pub.demo.baseball.master WHERE LastName='B%' ORDER BY Firstname ASC

Delete Data from the Cache

You can delete data from the cache by building a direct connection to the database. Note that the connector does not support manually deleting data from the cache.

Common Use Case

A common use for caching is to have an application always query the cached data and only update the cache at set intervals, such as once every day or every two hours. There are two ways in which this can be implemented:

  • AutoCache = false and Offline = false. All queries issued by the application explicitly reference the tableName#CACHE table. When the cache needs to be updated, the application executes a tableName#CACHE ... statement to bring the cached data up to date.
  • Offline = true. Caching is transparent to the application. All queries are executed against the table as normal, so most application code does not need to be aware that caching is done. To update the cached data, simply create a separate connection with Offline = false and execute a tableName#CACHE ... statement.

Power BI Connector for 1010data 2020

Data Type Mapping

The connector maps types from the data source to the corresponding data type available in the chosen cache database. The following table shows the mappings between the data types configured in the schema and the data types in the database.

Data Type Mapping

Note: String columns can map to different data types depending on their length.

Schema .NET JDBC SQL Server Derby MySQL Oracle SQLite Access
int Int32 int int INTEGER INT NUMBER integer LONG
double Double double float DOUBLE DOUBLE NUMBER double DOUBLE
date DateTime java.sql.Date date DATE DATE DATE date DATETIME
datetime DateTime java.sql.Date datetime TIMESTAMP DATETIME TIMESTAMP datetime DATETIME
time TimeSpan java.sql.Time time TIME TIME TIMESTAMP datetime DATETIME
string String java.lang.String If length > 4000: nvarchar(max), Otherwise: nvarchar(length)If length > 32672: LONG VARCHAR, Otherwise VARCHAR(length)If length > 255: LONGTEXT, Otherwise: VARCHAR(length)If length > 4000: CLOB, Otherwise: VARCHAR2(length)nvarchar(length)If length > 255: LONGTEXT, Otherwise: VARCHAR(length)
long Int64 long bigint BIGINT BIGINT NUMBER bigint LONG
boolean Boolean boolean tinyint SMALLINT BIT NUMBER tinyint BIT
decimal Decimal java.math.BigDecimal decimal DECIMAL DECIMAL DECIMAL decimal CURRENCY

Power BI Connector for 1010data 2020

Data Model

The Power BI Connector for 1010data 2020 models 1010Data entities as relational Tables, Views, and Stored Procedures.

Power BI Connector for 1010data 2020

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for 1010Data:

Data Source Tables

The following tables return information about how to connect to and query the data source:

  • sys_connection_props: Returns information on the available connection properties.
  • sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

  • sys_identity: Returns information about batch operations or single updates.

Power BI Connector for 1010data 2020

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

Name Type Description
CatalogName String The database name.

Power BI Connector for 1010data 2020

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas
          

Columns

Name Type Description
CatalogName String The database name.
SchemaName String The schema name.

Power BI Connector for 1010data 2020

sys_tables

Lists the available tables.

The following query retrieves the available tables:

          SELECT * FROM sys_tables
          

Columns

Name Type Description
CatalogName String The database containing the table.
SchemaName String The schema containing the table.
TableName String The name of the table.
TableType String The table type.
Description String A description of the table.

Power BI Connector for 1010data 2020

sys_tablecolumns

Describes the columns of the available tables.

The following query returns the columns and data types for the pub.demo.baseball.master table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='pub.demo.baseball.master'

Columns

Name Type Description
CatalogName String The name of the database containing the table.
SchemaName String The schema containing the table.
TableName String The name of the table containing the column.
ColumnName String The column name.
DataTypeName String The data type name.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The length in characters of the column or the numeric precision.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String A brief description of the column.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsReadOnly Boolean Whether the column is read-only.
IsKey Boolean Whether the column is a primary key.
IsHidden Boolean Whether the column is hidden.

Power BI Connector for 1010data 2020

sys_views

Lists the available views.

The following query retrieves the available views:

          SELECT TableName FROM sys_views 
          

Columns

Name Type Description
CatalogName String The name of the database containing the view.
SchemaName String The name of the schema containing the view.
TableName String The name of the view.
TableType String The type of the view.
Description String A description of the view.

Power BI Connector for 1010data 2020

sys_viewcolumns

Describes the columns of the available views.

The following query returns the columns and data types for a specified view:

SELECT ColumnName, DataTypeName FROM sys_viewcolumns WHERE TableName='MyView'

Columns

Name Type Description
CatalogName String The name of the database containing the view.
SchemaName String The name of the schema containing the view.
TableName String The name of the view.
ColumnName String The name of the column.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type of the column. This value is determined at run time based on the environment.
Length Int32 The length in characters of the column or the numeric precision.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String The column description.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsReadOnly Boolean Whether the column is read-only.
IsKey Boolean Whether the column is a primary key.
IsHidden Boolean Whether the column is hidden.

Power BI Connector for 1010data 2020

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures
          

Columns

Name Type Description
CatalogName String The database containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The name of the stored procedure.
Description String A description of the stored procedure.

Power BI Connector for 1010data 2020

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the NA stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='NA' AND Direction=1 OR Direction=2

Columns

Name Type Description
CatalogName String The name of the database containing the stored procedure.
SchemaName String The name of the schema containing the stored procedure.
ProcedureName String The name of the stored procedure containing the parameter.
ColumnName String The name of the stored procedure parameter.
Direction Int32 An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
NumbericScale Int32 The number of digits to the right of the decimal point in numeric data.
IsNullable Boolean Whether the parameter can contain null.
Description String The description of the parameter.
Ordinal Int32 The index of the parameter.

Power BI Connector for 1010data 2020

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the pub.demo.baseball.master table:

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='pub.demo.baseball.master'
          

Columns

Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
IsKey Boolean Whether the column is a primary key in the table referenced in the TableName table.
IsForeignKey Boolean Whether the column is a foreign key referenced in the TableName table.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.

Power BI Connector for 1010data 2020

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

          SELECT * FROM sys_indexes WHERE IsPrimary='false'
          

Columns

Name Type Description
CatalogName String The name of the database containing the index.
SchemaName String The name of the schema containing the index.
TableName String The name of the table containing the index.
IndexName String The index name.
ColumnName String The name of the column associated with the index.
IsUnique Boolean True if the index is unique. False otherwise.
IsPrimary Boolean True if the index is a primary key. False otherwise.
Type Int16 An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder String The sort order: A for ascending or D for descending.
OrdinalPosition Int16 The sequence number of the column in the index.

Power BI Connector for 1010data 2020

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Name Type Description
Name String The name of the connection property.
ShortDescription String A brief description.
Type String The data type of the connection property.
Default String The default value if one is not explicitly set.
Values String A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value String The value you set or a preconfigured default.
Required Boolean Whether the property is required to connect.
Category String The category of the connection property.
IsSessionProperty String Whether the property is a session property, used to save information about the current connection.

Power BI Connector for 1010data 2020

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Collaborative Query Processing

When working with data sources that do not support SQL-92, you can query the sys_sqlinfo view to determine the query capabilities of the underlying APIs, expressed in SQL syntax. See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

NamePossible Values
AGGREGATE_FUNCTIONSAVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNTYES
SUPPORTED_OPERATORS =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BYNO_RELATION
OUTER_JOINSYES
OJ_CAPABILITIESNESTED, LEFT, RIGHT, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
SUBQUERIESCOMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
STRING_FUNCTIONSASCII,CHAR,CONCAT,LEFT,LTRIM,REPLACE,RIGHT,RTRIM,SOUNDEX,SPACE,SUBSTRING
NUMERIC_FUNCTIONSABS,ACOS,ASIN,ATAN,CEILING,COS,COT,DEGREES,EXP,FLOOR,LOG,LOG10,PI,POWER,RADIANS,RAND,ROUND,SIGN,SIN,SQRT,TAN
TIMEDATE_FUNCTIONSCURRENT_DATE,CURRENT_TIMESTAMP,MONTH,YEAR
IDENTIFIER_QUOTE_OPEN_CHAR[
IDENTIFIER_QUOTE_CLOSE_CHAR]

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name='SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns

Name Type Description
NAME String A component of SQL syntax, or a capability that can be processed on the server.
VALUE String Detail on the supported SQL or SQL syntax.

Power BI Connector for 1010data 2020

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

         SELECT * FROM sys_identity
          

Columns

Name Type Description
Id String The database-generated Id returned from a data modification operation.
Batch String An identifier for the batch. 1 for a single operation.
Operation String The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message String SUCCESS or an error message if the update in the batch failed.

Power BI Connector for 1010data 2020

Connection String Options

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Getting Started.
Auto CacheAutomatically caches the results of SELECT queries into a cache database specified by either CacheLocation or both of CacheConnection and CacheProvider .
Cache ConnectionThe connection string for the cache database. This property is always used in conjunction with CacheProvider . Setting both properties will override the value set for CacheLocation for caching data.
Cache LocationSpecifies the path to the cache when caching to a file.
Cache MetadataThis property determines whether or not to cache the table metadata to a file store.
Cache ProviderThe name of the provider to be used to cache data.
Cache SessionThis option changes whether the provider stores the current session when you log in.
Cache ToleranceThe tolerance for stale data in the cache specified in seconds when using AutoCache .
Direct Query LimitLimits the number of rows when using the DirectQuery mode. This helps avoid performance issues at design time.
Enable Foreign Key DetectionWhether to detect the foreign keys in ODBC.
Firewall PasswordA password used to authenticate to a proxy-based firewall.
Firewall PortThe TCP port for a proxy-based firewall.
Firewall ServerThe name or IP address of a proxy-based firewall.
Firewall TypeThe protocol used by a proxy-based firewall.
Firewall UserThe user name to use to authenticate with a proxy-based firewall.
Kill SessionWhether to kill the any existing sessions under your account.
LocationA path to the directory that contains the schema files defining tables, views, and stored procedures.
LogfileA path to the log file.
Login URLThe URL of your 1010Data instance.
Maximum Column SizeThe maximum column size.
Max Log File CountA string specifying the maximum file count of log files. When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted.
Max Log File SizeA string specifying the maximum size in bytes for a log file (for example, 10 MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end.
Max RowsLimits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
OfflineUse offline mode to get the data from the cache instead of the live source.
OtherThese hidden properties are used only in specific use cases.
Page SizeThe number of results to return per request to 1010Data.
PasswordYour 1010Data password.
Proxy Auth SchemeThe authentication type to use to authenticate to the ProxyServer proxy.
Proxy Auto DetectThis indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
Proxy ExceptionsA semicolon separated list of hosts or IPs that are exempt from connecting through the ProxyServer .
Proxy PasswordA password to be used to authenticate to the ProxyServer proxy.
Proxy PortThe TCP port the ProxyServer proxy is running on.
Proxy ServerThe hostname or IP address of a proxy to route HTTP traffic through.
Proxy SSL TypeThe SSL type to use when connecting to the ProxyServer proxy.
Proxy UserA user name to be used to authenticate to the ProxyServer proxy.
Pseudo ColumnsThis property indicates whether or not to include pseudo columns as columns to the table.
Query PassthroughThis option passes the query to the 1010Data server as is.
RTKThe runtime key used for licensing.
SSL Server CertThe certificate to be accepted from the server when connecting using TLS/SSL.
TablesThis property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
TimeoutThe value in seconds until the timeout error is thrown, canceling the operation.
UserYour 1010Data login name.
VerbosityThe verbosity level that determines the amount of detail included in the log file.
ViewsRestricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
Power BI Connector for 1010data 2020

Auto Cache

Automatically caches the results of SELECT queries into a cache database specified by either CacheLocation or both of CacheConnection and CacheProvider .

Data Type

bool

Default Value

false

Remarks

When AutoCache = true, the connector automatically maintains a cache of your table's data in the database of your choice.

Setting the Caching Database

When AutoCache = true, the connector caches to a simple, file-based cache. You can configure its location or cache to a different database with the following properties:

See Also

  • CacheMetadata: This property reduces the amount of metadata that crosses the network by persisting table schemas retrieved from the 1010Data metadata. Metadata then needs to be retrieved only once instead of every connection.
  • Explicitly Caching Data: This section provides more examples of using AutoCache in Offline mode.
  • CACHE Statements: You can use the CACHE statement to persist any SELECT query, as well as manage the cache; for example, refreshing schemas.

Power BI Connector for 1010data 2020

Cache Connection

The connection string for the cache database. This property is always used in conjunction with CacheProvider . Setting both properties will override the value set for CacheLocation for caching data.

Data Type

string

Default Value

""

Remarks

The cache database is determined based on the CacheProvider and CacheConnection properties. Both properties are required to use the cache database. Examples of common cache database settings can be found below. For more information on setting the caching database's driver, refer to CacheProvider.

The connection string specified in the CacheConnection property is passed directly to the underlying CacheProvider. Consult the documentation for the specific ADO.NET provider for more information on the available properties.

SQLite

The connector simplifies caching to SQLite, only requiring you to set the CacheLocation property to make a basic connection.

MySQL

The installation includes the CData ADO.NET Provider for MySQL. The following are typical connection properties:

  • Server: The IP address or domain name of the server you want to connect to.
  • Port: The port that the server is running on.
  • User: The user name provided for authentication to the database.
  • Password: The password provided for authentication to the database.
  • Database: The name of the database.

SQL Server

To cache to SQL Server, you can use the .NET Framework Provider for SQL Server, included in the .NET Framework. The following are typical SQL Server connection properties:

  • Server: The name or network address of the computer running SQL Server. To connect to a named instance instead of the default instance, this property can be used to specify the host name and the instance, separated by a backslash.
  • Port: The port SQL Server is running on.
  • Database: The name of the SQL Server database.
  • Integrated Security: Set this option to true to use the current Windows account for authentication. Set this option to false if you are setting the User and Password in the connection.
  • User ID: The user name provided for authentication with SQL Server. This property is only needed if you are not using integrated security.
  • Password: The password provided for authentication with SQL Server. This property is only needed if you are not using integrated security.

Oracle

To cache to Oracle, you can use the Oracle Data Provider for .NET. The following are typical connection properties:

  • Data Source: The connect descriptor that identifies the Oracle database. This can be a TNS connect descriptor, an Oracle Net Services name that resolves to a connect descriptor, or, after version 11g, an Easy Connect naming (the host name of the Oracle server with an optional port and service name).

  • Password: The password provided for authentication with the Oracle database.
  • User Id: The user Id provided for authentication with the Oracle database.

PostgreSQL

To cache to PostgreSQL, you can use the Npgsql ADO.NET provider. The following are typical connection properties:

  • Host: The address of the server hosting the PostgreSQL database.
  • Port: The port used to connect to the server hosting the PostgreSQL database.
  • Database: The name of the database.
  • User name: The user Id provided for authentication with the PostgreSQL database.
  • Password: The password provided for authentication with the PostgreSQL database.

Power BI Connector for 1010data 2020

Cache Location

Specifies the path to the cache when caching to a file.

Data Type

string

Default Value

"%APPDATA%\\CData\\C1010 Data Provider"

Remarks

The CacheLocation is a simple, file-based cache. The connector uses SQLite.

If left unspecified, the default location is "%APPDATA%\\CData\\C1010 Data Provider" with %APPDATA% being set to the user's configuration directory:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/.config
Linux ~/.config

See Also

  • AutoCache: Set to implicitly create and maintain a cache for later offline use.
  • CacheMetadata: Set to persist the 1010Data catalog in CacheLocation.

Power BI Connector for 1010data 2020

Cache Metadata

This property determines whether or not to cache the table metadata to a file store.

Data Type

bool

Default Value

false

Remarks

As you execute queries with this property set, table metadata in the 1010Data catalog are cached to the file store specified by CacheLocation if set or the user's home directory otherwise. A table's metadata will be retrieved only once, when the table is queried for the first time.

When to Use CacheMetadata

The connector automatically persists metadata in memory for up to two hours when you first discover the metadata for a table or view and therefore, CacheMetadata is generally not required. CacheMetadata becomes useful when metadata operations are expensive such as when you are working with large amounts of metadata or when you have many short-lived connections.

When Not to Use CacheMetadata

  • When you are working with volatile metadata: Metadata for a table is only retrieved the first time the connection to the table is made. To pick up new, changed, or deleted columns, you would need to delete and rebuild the metadata cache. Therefore, it is best to rely on the in-memory caching for cases where metadata changes often.
  • When you are caching to a database: CacheMetadata can only be used with CacheLocation. If you are caching to another database with the CacheProvider and CacheConnection properties, use AutoCache to cache implicitly. Or, use CACHE Statements to cache explicitly.

Power BI Connector for 1010data 2020

Cache Provider

The name of the provider to be used to cache data.

Data Type

string

Default Value

""

Remarks

You can cache to any database for which you have an ADO.NET provider . The caching database is determined based on the CacheProvider and CacheConnection properties.

The following sections show connection examples and address other requirements for several popular database drivers. Refer to CacheConnection for more information on typical connection properties.

Loading the Caching Database's Driver

The CacheProvider property specifies the name of the DbProviderFactory used to cache data. You can find the provider factories available to you in your machine.config.

SQLite

The connector simplifies SQLite configuration; in most caching scenarios, you need to specify only the CacheLocation.

CacheLocation=C:\\Users\\Public\\cache.db;User=user@company.com;Password=password;

MySQL

To cache to MySQL, you can use the included CData ADO.NET Provider for MySQL:

Cache Provider=System.Data.CData.MySQL;Cache Connection='Server=localhost;Port=3306;Database=cache;User=root;Password=123456';User=myUser;Password=myPassword;Security Token=myToken;

SQL Server

You can use the Microsoft .NET Framework Provider for SQL Server, included in the .NET Framework, to cache to SQL Server:

Cache Provider=System.Data.SqlClient;Cache Connection="Server=MyMACHINE\MyInstance;Database=SQLCACHE;User ID=root;Password=admin";User=user@company.com;Password=password;

Oracle

To cache to Oracle, you can use the Oracle Data Provider for .NET, as shown in the following example:

Cache Provider=Oracle.DataAccess.Client;Cache Connection='User Id=scott;Password=tiger;Data Source=ORCL';User=user@company.com;Password=password;

The Oracle Data Provider for .NET also requires the Oracle Database Client. When you download the Oracle Database Client, ensure that the bitness matches the the bitness of your machine. When you install select either the Runtime or Administrator installation type;, the Instant Client is not sufficient.

PostgreSQL

You can use the Npgsql ADO.NET provider to cache to PostgreSQL, as shown in the following example:

CacheProvider=Npgsql;CacheConnection="Host=myserver;Username=mylogin;Password=mypass;Database=mydatabase";User=user@company.com;Password=password;

Power BI Connector for 1010data 2020

Cache Session

This option changes whether the provider stores the current session when you log in.

Data Type

bool

Default Value

true

Remarks

When using the 1010Data API, the connector must get a session before it can execute queries. Each login can have only one active session and acquiring a new session automatically removes all others.

By default this property is true, which means that the connector can share sessions between multiple processes. It does this by writing a file in your temporary directory and storing your session token in it.

If this property is false, the connector will only share sessions within a single process and will not write session information to a file. This will prevent multiple instances of the driver from running at one time.

If you are using an application like Excel for Mac that is sandboxed, you should set this property to false to avoid permissions issues.

Power BI Connector for 1010data 2020

Cache Tolerance

The tolerance for stale data in the cache specified in seconds when using AutoCache .

Data Type

string

Default Value

"600"

Remarks

The tolerance for stale data in the cache specified in seconds. This only applies when AutoCache is used. The connector checks with the data source for newer records after the tolerance interval has expired. Otherwise, it returns the data directly from the cache.

Power BI Connector for 1010data 2020

Direct Query Limit

Limits the number of rows when using the DirectQuery mode. This helps avoid performance issues at design time.

Data Type

string

Default Value

"10000"

Remarks

Limits the number of rows returned when using the DirectQuery Mode. This limit only applies when aggregation is not being used. Queries with SUM, MIN, MAX, GROUP BY, and so on will not be limited.

Power BI Connector for 1010data 2020

Enable Foreign Key Detection

Whether to detect the foreign keys in ODBC.

Data Type

bool

Default Value

false

Remarks

Whether to detect the foreign keys in ODBC.

Power BI Connector for 1010data 2020

Firewall Password

A password used to authenticate to a proxy-based firewall.

Data Type

string

Default Value

""

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

Power BI Connector for 1010data 2020

Firewall Port

The TCP port for a proxy-based firewall.

Data Type

string

Default Value

""

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

Power BI Connector for 1010data 2020

Firewall Server

The name or IP address of a proxy-based firewall.

Data Type

string

Default Value

""

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the connector uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

Power BI Connector for 1010data 2020

Firewall Type

The protocol used by a proxy-based firewall.

Data Type

string

Default Value

"NONE"

Remarks

This property specifies the protocol that the connector will use to tunnel traffic through the FirewallServer proxy. Note that by default, the connector connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

Type Default Port Description
TUNNEL 80 When this is set, the connector opens a connection to 1010Data and traffic flows back and forth through the proxy.
SOCKS4 1080 When this is set, the connector sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5 1080 When this is set, the connector sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

Power BI Connector for 1010data 2020

Firewall User

The user name to use to authenticate with a proxy-based firewall.

Data Type

string

Default Value

""

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

Power BI Connector for 1010data 2020

Kill Session

Whether to kill the any existing sessions under your account.

Data Type

bool

Default Value

false

Remarks

Each connection to 1010Data requires its own session. While multiple connections made with the connector can share a session, the connector cannot share sessions with other tools. This option controls what happens when the connector logs in and another session is active.

By default this property is false, and the connector will attempt to take ownership over an existing session. However, this will not work if the session is not responding due to a long-running query or other issues. In those cases this property should be set to true, which will destroy the existing session and any queries which might be active on it.

Power BI Connector for 1010data 2020

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

"%APPDATA%\\CData\\C1010 Data Provider\\Schema"

Remarks

The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\\CData\\C1010 Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/.config
Linux ~/.config

Power BI Connector for 1010data 2020

Logfile

A path to the log file.

Data Type

string

Default Value

""

Remarks

For more control over what is written to the log file, you can adjust its Verbosity.

Power BI Connector for 1010data 2020

Login URL

The URL of your 1010Data instance.

Data Type

string

Default Value

""

Remarks

This is the domain assigned to your 1010Data instance. For example, https://mycompany.edge.1010data.com/odbc.

Power BI Connector for 1010data 2020

Maximum Column Size

The maximum column size.

Data Type

string

Default Value

"16000"

Remarks

Some tools restrain the largest size of a column or the total size of all the columns selected. You can set the MaximumColumnSize to overcome these schema-based restrictions. The connector will not report any column to be larger than the MaximumColumnSize.

Set a MaximumColumnSize of zero to eliminate limits on column size, as shown in the following example:

SQLSetConnectAttr(hdbc, 20002, (SQLPOINTER)2048, 0);

The following are a few examples of how you can use this property to avoid compatibility issues with several tools:

  • Oracle ODBC Gateway: Set MaximumColumnSize=4000 to avoid the ORA-28562 data truncation error. Note that Oracle ODBC Gateway additionally requires that you set the MapToWVarchar connection property to false.
  • Microsoft Access: Set MaximumColumnSize=255 to report string fields as TEXT instead of MEMO in Access. MEMO fields have no length limit but have restrictions on joins and filters. TEXT fields have a fixed length but support more functionality in Access tables.

Power BI Connector for 1010data 2020

Max Log File Count

A string specifying the maximum file count of log files. When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted.

Data Type

string

Default Value

""

Remarks

A string specifying the maximum file count of log files. When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted. The minimum supported value is 2. A value of 0 or a negative value indicates no limit on the count.

Power BI Connector for 1010data 2020

Max Log File Size

A string specifying the maximum size in bytes for a log file (for example, 10 MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end.

Data Type

string

Default Value

"100MB"

Remarks

A string specifying the maximum size in bytes for a log file (for example, 10 MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end. The default limit is 100 MB. Values lower than 100 kB will use 100 kB as the value instead.

Power BI Connector for 1010data 2020

Max Rows

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Data Type

string

Default Value

"-1"

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Power BI Connector for 1010data 2020

Offline

Use offline mode to get the data from the cache instead of the live source.

Data Type

bool

Default Value

false

Remarks

When Offline = true, all queries execute against the cache as opposed to the live data source. In this mode, certain queries like INSERT, UPDATE, DELETE, and CACHE are not allowed.

Power BI Connector for 1010data 2020

Other

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Caching Configuration

CachePartial=TrueCaches only a subset of columns, which you can specify in your query.
QueryPassthrough=TruePasses the specified query to the cache database instead of using the SQL parser of the connector.

Integration and Formatting

DefaultColumnSizeSets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMTDetermines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filenameRecords the underlying socket data transfer to the specified file.

Power BI Connector for 1010data 2020

Page Size

The number of results to return per request to 1010Data.

Data Type

string

Default Value

"10000"

Remarks

The connector fetches results from 1010Data in batches to avoid long delays when reading large result sets. This option controls how many rows are fetched per request.

Power BI Connector for 1010data 2020

Password

Your 1010Data password.

Data Type

string

Default Value

""

Remarks

Your 1010Data password.

Power BI Connector for 1010data 2020

Proxy Auth Scheme

The authentication type to use to authenticate to the ProxyServer proxy.

Data Type

string

Default Value

"BASIC"

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the connector will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

  • BASIC: The connector performs HTTP BASIC authentication.
  • DIGEST: The connector performs HTTP DIGEST authentication.
  • NEGOTIATE: The connector retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
  • PROPRIETARY: The connector does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

Power BI Connector for 1010data 2020

Proxy Auto Detect

This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

Data Type

bool

Default Value

true

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

On Windows, the proxy settings are read from the Internet Options in the registry.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

Power BI Connector for 1010data 2020

Proxy Exceptions

A semicolon separated list of hosts or IPs that are exempt from connecting through the ProxyServer .

Data Type

string

Default Value

""

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the connector uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

Power BI Connector for 1010data 2020

Proxy Password

A password to be used to authenticate to the ProxyServer proxy.

Data Type

string

Default Value

""

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the connector uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

Power BI Connector for 1010data 2020

Proxy Port

The TCP port the ProxyServer proxy is running on.

Data Type

string

Default Value

"80"

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

Power BI Connector for 1010data 2020

Proxy Server

The hostname or IP address of a proxy to route HTTP traffic through.

Data Type

string

Default Value

""

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The connector can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the connector uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

Power BI Connector for 1010data 2020

Proxy SSL Type

The SSL type to use when connecting to the ProxyServer proxy.

Data Type

string

Default Value

"AUTO"

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:

AUTODefault setting. If the URL is an HTTPS URL, the connector will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYSThe connection is always SSL enabled.
NEVERThe connection is not SSL enabled.
TUNNELThe connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

Power BI Connector for 1010data 2020

Proxy User

A user name to be used to authenticate to the ProxyServer proxy.

Data Type

string

Default Value

""

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain
domain\user

Power BI Connector for 1010data 2020

Pseudo Columns

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

Power BI Connector for 1010data 2020

Query Passthrough

This option passes the query to the 1010Data server as is.

Data Type

bool

Default Value

false

Remarks

When this is set, queries are passed through directly to 1010Data.

Power BI Connector for 1010data 2020

RTK

The runtime key used for licensing.

Data Type

string

Default Value

""

Remarks

The RTK property may be used to license a build. Only use this property in environments that do not support the .NET licensing scheme. In most circumstances, this property is not necessary. The value of this property overwrites all licensing information, so use caution to ensure that the value is correct.

Power BI Connector for 1010data 2020

SSL Server Cert

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description Example
A full PEM Certificate (example shortened for brevity) -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate C:\cert.cer
The public key (example shortened for brevity) -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated) ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated) 34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Power BI Connector for 1010data 2020

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. For example, Tables=TableA,TableB,TableC.

Power BI Connector for 1010data 2020

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

string

Default Value

"60"

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the connector throws an exception.

Power BI Connector for 1010data 2020

User

Your 1010Data login name.

Data Type

string

Default Value

""

Remarks

This can be either an email address or a classic 1010Data username.

Power BI Connector for 1010data 2020

Verbosity

The verbosity level that determines the amount of detail included in the log file.

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the connector reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are described in the following list:

1Setting Verbosity to 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors.
2Setting Verbosity to 2 will log everything included in Verbosity 1, cache queries, and additional information about the request, if applicable, such as HTTP headers.
3Setting Verbosity to 3 will additionally log the body of the request and the response.
4Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.
5Setting Verbosity to 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.

The Verbosity should not be set to greater than 1 for normal operation. Substantial amounts of data can be logged at higher verbosities, which can delay execution times.

Power BI Connector for 1010data 2020

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. For example, Views=ViewA,ViewB,ViewC.

Copyright (c) 2020 1010data Software, Inc. - All rights reserved.
Build 19.0.7333.0