Exago Logo
Search
Generic filters
Exact matches only

Data Sources

This article applies to the Admin Console > TreeData.png Data > TreeDataSource.png Sources settings.


Data Sources establish the connection between Exago and a database or a web service. Although typically only one database is used, Exago can join data from different sources into a single report.

Note

To utilize some types of data sources, the appropriate driver may need to be downloaded and installed separately from the Exago install process. Refer to Data Source Drivers below.

Creating, Editing or Deleting Data Sources

  • To add a new data source click TreeDataSource.png Sources in the Main Menu and either:
    • click the Add + icon at the top of the main menu
    • right-click and select + Add from the context menu
  • To edit a source either:
    • double click it
    • select it and click the Edit pencil icon at the top of the main menu
    • right-click it and select pencil Edit from the context menu
  • To delete a source either:
    • select it and click the Delete AdminDelete.png icon at the top of the main menu
    • right-click it and select X Delete from the context menu
  • To save changes and new objects click the Apply or Okay buttons.

Data Source Properties


Creating a new Data Source in the Admin Console in v2021.2+

Creating a new Data Source in the Admin Console in pre-v2021.2

Each data source must have the following:

Name

A friendly name for the Data Source that will be referenced in the application.

Type

The type of source being used. Valid types include:

  • Relational databases
    • SQL Server — Microsoft SQL Server
    • MySQL
    • Oracle
    • Postgres — PostgreSQL
    • DB2 — IBM db2
    • Informix — IBM Informix
    • ODBC — ODBC Driver

    • SQLite v2021.1.8+
  • Non-relational databases
    • MongoDB v2018.2+
    • ElasticSearch v2018.2+ — ElasticSearch/ELK database (For more information see CData Drivers.)
  • Data warehouses
    • Redshift v2019.1.11+
    • Snowflake v2019.1.9+

    • Google BigQueryv2021.1.10+ (For more information see CData Drivers.)
  • Other

Schema/Owner Name (blank for default)

If using schema to provide multi-tenant security, provide a database schema. Otherwise, leave this field blank. For more information, review the Multi-Tenant Environment Integration article.

Connection String Input

Describes how Exago shall connect to the Data Source. Typically, a server name or IP address, database name and login credentials are specified here. Different Data Source types have different connection string requirements, although the most common connection string parameters are:

  • Server — the server host name or IP address where the database is physically located
  • Database — the name of the database on the Server
  • User ID and Password — the user name and password credentials to access the Database

Choose either the integrated Connection String Builder (Parameterized mode), or to provide a connection string manually (String mode) from the Connection String Input dropdown. Switching between Parameterized and String is possible, and Exago will remember the last mode selected. The Data Source connection is still stored as a standard connection string in the configuration file regardless of mode selected.

Note

Some Data Source types only support Parameterized or String mode. In these cases, Exago will select the correct mode and disable the Connection String Input dropdown.

In Parameterized mode, Exago queries the database driver for all of the available connection string options/parameters. Then, these parameters may be added one at a time in key-value format to build a complete connection string.

NoteIf the database driver does not return any connection string parameters, the parameter names must be manually typed instead of chosen from a dropdown list.


Adding the Port connection string parameter to a Data Source by choosing its name from the Parameter Name dropdown
  • To add a new connection string parameter to the Data Source:
    1. Click the X Add Connection Parameter button.
    2. Enter the name of, or choose the name of a parameter from the dropdown list.
    3. Provide a value for the parameter in the space provided.
  • To hide/show a connection string parameter, click the Make Connection String Visible  or Connection String Hide  icons accordingly.
  • To remove a connection string parameter, click the Delete  icon at the end of the line. This icon is only visible when there are two or more parameters for a Data Source.

Connection strings vary by type:

TypeConnection Strings
mssql, oracle, postgres, mysql sqlite (v2021.1.8+) and olap

Refer to connectionstrings.com for database connection strings.

If using Integrated Authentication from an IIS Application Pool to connect to a remote MSSQL or Microsoft SQL Server Analysis Services (SSAS) server, review these MSDN and Stack Overflow articles to ensure the correct credentials are sent to the SQL server.

The database driver specified in Database Settings determines the character set used in the application. If the data in the data source uses a different character set, it should be explicitly set in the connection string.

For PostgreSQL data sources using the Npgsql driver and all lowercase schema names, set the UseDelimitersOnSchema directive in dbconfigs.override.json to true for best results. See the Overriding dbconfigs Information section of the Managing the dbconfigs.json File article for more information.

assembly

Required parameters:

  • assembly – The full path of the assembly name.
  • class – The class name in the assembly where the static methods will be obtained.
fileRequires the physical path to the Excel or XML file and the file type.
Example: File=C:example.xls;Type=excel;
mongodbFor more information see CData Drivers
elasticsearchFor more information see CData Drivers
snowflakeFor more information see CData Drivers
redshiftFor more information see CData Drivers
Google BigQueryFor more information see CData Drivers

  • Click the Test Connection CheckmarkAdmin.png icon to verify the connection succeeds.
  • Click the Make Connection String Visible open eye or Connection String Hide closed eye icons to show/hide the connection string in the Data Source tab.

Data Source Drivers

Below is a list and the associated links for recommended ADO.NET drivers for each type of data source.

MySQL on Linux v2019.2+

As of v2019.2 the Exago Linux Installer does not install a MySQL ADO.NET driver at the time of installation. Instead, clients wishing to use a MySQL data source for either reporting or for Storage Management will need to provide their own.

Exago has provided wrappers around two popular MySQL data drivers that clients may choose to install on their own.

  • Devart dotconnect free edition
  • MySQL ADO.NET

Contact your Customer Success Manager or the Exago Support Team for assistance. Install the driver by extracting the contents of the download and then running either installMySql.sh or installDevartMySql.sh as root. Provide the Exago installation path to the installer script. For example:

sudo ./installDevartMySql.sh /opt/Exago

Once installed, update the Admin Console > General > Database Settings to reflect the new data provider.

If using the Storage Management command-line transition utilities add this line to LoadReportsToDb.exe.config between <system.data><DbProviderFactories>:

<add name="dotConnect for MySQL" invariant="Devart.Data.MySql" description="Devart dotConnect for MySQL" type="Devart.Data.MySql.MySqlProviderFactory, Devart.Data.MySql, Version=8.3.215.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />

.NET Assemblies

.NET Assemblies can be used as data sources. This is possible when the .NET Assemblies underlying methods are setup as data objects.

 

An advantage of doing this is being able to use high-level language to manipulate the data being reported on at run-time. The main disadvantage is not being able to take advantage of the database to perform joins with other data objects; data from methods can still be joined, but the work to do this is done within Exago. For more information see Note about Cross Source Joins.

Parameters

Parameters are passed from Exago to .NET Assemblies. Three types of parameters can be passed but only Call Type is required.

Call Type (required)

Integer that specifies what Exago needs at the time of the call. There are three possible values. You may specify the name of this parameter in the Programmable Object Settings of the generalsection.png General section.

  • 0 : Schema –  returns a DataSet with no rows.
  • 1 : Data – returns a full DataSet.
  • 2 : Filter Dropdown Values – returns data for the filter dropdown list. The Data Field being requested is passed in the column parameter. The filter type is passed in the filter parameter (see below).

Column, Filter and Sort Strings

To optimize performance Exago can pass user-specified sorts and filters to the .NET Assembly. This process reduces the amount of data sent to Exago. If these parameters are not used, all of the data will be sent to Exago to sort and filter. Column, filter and sort strings are sent as standard SQL. You may specify the name of these parameters in the generalsection.png GeneralTreeGeneralNode.png Programmable Object Settings section.

Custom Parameter Values (optional)

Additional parameters can be specified to be sent to individual methods in the Data Object Menu.

Important

To increase performance, when a .NET Assembly is first accessed it is compiled and cached within Exago. Therefore, Exago will not be aware of any changes within .NET Assembly. If the assembly is subsequently changed, reset the internal cache by clicking the Test Connection CheckmarkAdmin.png icon restarting the web server.

Note

If an Exago .NET API application needs to access reports with an assembly data source, it must include a reference to the assembly WebReportsAsmi.dll.

SessionInfo (optional) v2016.2+

Session state variables. See SessionInfo for more information.

.NET Assemblies

It is important to note that when a connection string for .NET Assembly is set the class name must match the name of the class where the static methods will be searched. UNC or absolute paths may be used. Make sure that the assembly has read privileges for the IIS user running Exago. Below is an example of a .NET Assembly connection string:

assembly=MyServerNameMyShareNameMyAssembly.dll;class=Main

.NET Assembly methods must be static. Below is an example of a .NET Assembly method.

public class Main{  public static DataSet dotnet_optionees(int callType, string columnStr, string filterStr, int myCustomParameter)  {    switch (callType)    {      case 0:        // return schema      case 1:        // return data      case 2:        // return filter values for dropdown    }  }}

Method signature using SessionInfo (v2016.2+):

public class Main{  public static DataSet dotnet_optionees(WebReports.Api.Common.SessionInfo sessionInfo, int callType, string columnStr, string filterStr, int myCustomParameter)  {    switch (callType)    ...  }}

Excel and XML Files

Exago can read Microsoft Excel workbooks and XML files as Data Sources.

Caution

Excel workbooks and XML files will have less speed, performance, and security of a database. Using Excel and XML files is recommended only if the dataset is small or if the information is only available in this format.

Excel

Each worksheet in the Excel file will be read as a separate table with the sheet name as the table’s name. The top row will be read as the column headers, and the remaining cells will be read as the data. Do not leave any blank rows or columns.

excelexample.png

Connection String Example

File=C:example.xls;Type=excel;

XML

The XML document must begin with the schema. After defining the schema the data must be placed into the appropriate tags. For reference see the working example below:

<?xml version="1.0" encoding="UTF-8"?><ExagoData>   <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="ExagoData">      <xs:element name="ExagoData" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">         <xs:complexType>            <xs:choice minOccurs="0" maxOccurs="unbounded">               <xs:element name="Call">                  <xs:complexType>                     <xs:sequence>                        <xs:element name="CallID" type="xs:unsignedInt" minOccurs="0" />                        <xs:element name="StaffID" type="xs:string" minOccurs="0" />                        <xs:element name="VehicleUsed" type="xs:unsignedInt" minOccurs="0" />                     </xs:sequence>                  </xs:complexType>               </xs:element>               <xs:element name="Staff">                  <xs:complexType>                     <xs:sequence>                        <xs:element name="StaffID" type="xs:unsignedInt" minOccurs="0" />                        <xs:element name="Rank" type="xs:string" minOccurs="0" />                        <xs:element name="LastName" type="xs:string" minOccurs="0" />                        <xs:element name="FirstName" type="xs:string" minOccurs="0" />                     </xs:sequence>                  </xs:complexType>               </xs:element>            </xs:choice>         </xs:complexType>      </xs:element>   </xs:schema>   <Call>      <CallID>890</CallID>      <StaffID>134</StaffID>      <VehicleUsed>12</VehicleUsed>   </Call>   <Call>      <CallID>965</CallID>      <StaffID>228</StaffID>      <VehicleUsed>4</VehicleUsed>   </Call>   <Call>      <CallID>740</CallID>      <StaffID>1849</StaffID>      <VehicleUsed>2</VehicleUsed>   </Call>   <Staff>      <StaffID>134</StaffID>      <Rank>Captain</Rank>      <LastName>Renolyds</LastName>      <FirstName>Malcom</FirstName>   </Staff>   <Staff>      <StaffID>228</StaffID>      <Rank>Lieutenant</Rank>      <LastName>Brown</LastName>      <FirstName>Bill</FirstName>   </Staff>   <Staff>      <StaffID>1849</StaffID>      <Rank>Sergeant</Rank>      <LastName>John</LastName>      <FirstName>Pepper</FirstName>   </Staff></ExagoData>

Connection String Example

File=C:example.xml;Type=xml;

OLAP and MDX Queries

Exago can query OLAP Data Sources using MDX Queries. OLAP Data Sources and Objects are identical to a regular data base type object, with the following exceptions:

  • Data Objects belonging to OLAP type Data Sources must have their Schema Access Type set to Metadata and must have Column Metadata set for all fields.
  • OLAP Data Objects will always be MDX Queries written in the Custom SQL Object dialog. These queries are passed directly through to the data source. Exago simply passes through the MDX query to the data source and does not modify it in any way. Therefore, all filtering and sorting are done in memory, tenanting at the data object level and database aggregation do not work with OLAP data sources.

Connecting to a Microsoft SQL Server Analysis Services (SSAS) data source will use Integrated Authentication. See the notes in the Connection String section above how to handle this situation. In a general sense, the IIS Application Pool will need to login with a credential assigned in an appropriately provisioned SSAS role on the data source.

Sample Connection String

Data Source=SERVER1MSSQLSERVER2014;Catalog=AdventureWorksDWAnalysis;

Tip

The Catalog parameter should match the Database name in Microsoft SQL Server Manageent Studio.

rZbvE7fP8N.png

Using Azure based SSAS

In order to use an Azure based Microsoft SQL Server Analysis Services Database (SSAS) you need the most up to date ADOMD.NET driver, which is available from Microsoft. Then, in order to properly configure Data Object entities through joins, the following information must be provided:

  • The entity containing the primary key should be specified in the “From” section.
  • The entity containing the foreign key should be specified in the “To” section.
  • The IDs used in the join configuration must be included in the metadata fields and should be available in the design model.

ODBC Drivers

Exago can use ODBC drivers to connect to data sources. When connecting to an ODBC data source, an extra option will appear to set the Column Delimiters. The delimiter character depends on which type of data base you are connecting to.

Examples

MySql

' (grave accent)

MsSql, OLAP

[] (brackets)

DB2, Informix, Oracle, Postgres, Sqlite

" (quotation marks)

If you don’t know which delimiter character to use, contact your database administrator.

Warning

If your data objects have spaces in their names, you must set the correct delimiter in order to access the data. Otherwise, improper SQL will be generated and you will see errors or erroneous data.

CData Drivers

As of v2018.2+, Exago allows for additional data source types through CData ADO.NET drivers. For more information, see the article on CData Drivers.

Was this article helpful?
0 out of 5 stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
How can we improve this article?
Please submit the reason for your vote so that we can improve the article.
Table of Contents