Scroll

Assembly Data Sources

Exago BI supports the ability to retrieve data from .NET assemblies. Assembly Data Sources (ADS) are custom programmatic .NET Framework binaries for transforming and exposing arbitrary data in a format that can be read by Exago BI.

ADS can be used as data connectors and drivers for unconventional data sources. They can also be used as ETL (Extract, Transform, Load) layers for processing, combining, and/or caching data before it reaches the reporting engine. This article describes how to write custom .NET ADS for Exago BI.

ADS must be written for, and compiled with supported versions of the .NET Framework (which, at the time of this writing, is version 4.5 or higher). .NET Core and .NET Standard are not supported. The ADS must be compiled as a class library (.dll).

Interface

ADS must be coded to fit the following interface.

The entry point or points for Exago BI are public static methods which accept at least an integer Call Type parameter and return an ADO.NET System.Data.DataSet object. The Call Type parameter name must be defined in the Admin Console Programmable Object Settings.

ParamNames.png

Parameter names defined in the Admin Console

Although the return type is a DataSet, each ADS method is expected to return only a single data table. This is usually done by merging in a System.Data.DataTable object which has a schema and has been populated with data. If a DataSet with multiple tables is returned, only the first one will be recognized.

Entry methods can optionally accept some or all other parameters described in the Programmable Object Settings, in order to have additional context for the method call. All the parameters in use must have defined names in the Admin Console.

In Exago BI versions 2016.2 and above, entry methods may also access a WebReports.Api.Common. SessionInfo object. This contains the active session's configuration settings and references to the active API objects. It can be used to identify properties as well as mutate session state. An assembly reference to bin\WebReportsApi.dll is required for the SessionInfo class definition.

Method Examples

Considering the following parameter names defined in the configuration:

  • Call Type Parameter Name: CallType
  • Column Parameter Name: Columns
  • Filter Parameter Name: Filter

The following are valid entry method signatures:

  • public static DataSet Method(int CallType)
  • public static DataSet Method(int CallType, string Columns, string Filter)
  • public static DataSet Method(int CallType, string Columns = "")
  • public static DataSet Method(SessionInfo Session, int CallType)
  • public static object Method(SessionInfo Session, object CallType)
    

There can be more than one entry method. Each public static method in the class will appear as a virtual table when adding the ADS data model to the Admin Console.

Alternatively, one method can support multiple tables by incorporating the Object Id parameter, which passes the object Id as defined in the configuration. See Handling Multiple Data Tables for details.

Additional Notes

1. Methods cannot be overloaded, as it will be ambiguous which one Exago BI should use.

2. For security reasons, only the entry methods in the class should be marked as public, so that any other utility or misc methods are not erroneously visible in the Admin Console.

Class Definition

The entry methods must reside within a public class (non-static) within a defined namespace. The class must be at the top level of the namespace, not within another class. Its default constructor must be public (defining the constructor is optional).

Example

namespace AssemblyDataSource {
    public class Tables {
        public Tables() { ... } /* optional */
        public static DataSet EntryMethod(...) {...}
    }
}
Note: Due to a bug, in versions prior to v2017.3.4, an assembly cannot contain multiple data source classes. This has since been addressed.

Implementation

Each ADS query is a discrete atomic operation. While you may choose to have Exago BI cache the assembly in memory, the application will not maintain a connection with it between queries. Unless you store application state on disk, queries will have no knowledge of each other.

ADS method calls are synchronous, meaning that the main application thread will wait until the method returns before continuing.

Exago BI queries ADS for three distinct reasons:

  • Schema - The column/field names and data types. Called whenever the schema needs to be retrieved (such as expanding a category's fields in the ExpressView data pane) and the Schema Access Type is set to Datasource. The frequency of schema calls can be significantly reduced by specifying a static schema for the ADS in the Column Metadata.
  • Data - The data values for the table. Called whenever a report is executed.
  • Filter values - Data values for a single column, possibly filtered by an input string. Called when values need to be populated for a filter dropdown list.

Each type of query requires a different amount of information to be returned. The Programmable Data Object parameters are passed alongside each query to indicate the information that Exago BI is expecting. With this in mind, you can write ADS with certain optimizations to improve performance.

Note: If performance is a strict concern, consider that databases will almost always be more performant for large data sets than ADS.

See Optimizing for Query Types for more details.

Data Format

Exago BI expects an ADO.NET DataSet object to be returned from the ADS method. A DataSet is an in-memory representation of a relational data model.

To return no data, for any reason, construct and return a DataSet with the schema and no rows. Sending null or an empty schema will cause an exception to be thrown.

Exago BI reads only the first DataTable in a set, at index DataSet.Tables[0], regardless of the number of tables comprising the set. To return multiple tables from an ADS, return a different DataSet depending on an input parameter or method name. See Handling Multiple Data Tables for more information.

See DataSets, DataTables, and DataViews for the ADO.NET DataSet documentation.

Optimizing for Query Types

Avoid retrieving the full data set whenever possible. This is usually the most computationally intensive query task. A number of Programmable Object Parameters are passed to the ADS in order to determine the amount of processing required for each query.

ADS.png

The ADS query types, what data they expect, and how the data is processed by Exago

Using the Call Type Parameter

Call Type is an integer parameter, with value 0 for a schema query, 1 for a data set query, and 2 for a filter values query.

Schema queries are expected to be quick, and do not require the actual data table. Returning a static schema is the most performant way to handle this. Do not make the application wait to populate the full table.

It is highly recommended to set the Schema Access Type for the ADS to Metadata in order to eliminate schema queries to the data source. This will significantly improve the responsiveness of the user interface.

Filter values query a single data column, with a text filter applied to limit the number of values returned. Therefore this call type works best with the Column and Filter parameters.

Column(s), Filter, and Full Filter Parameters

For data queries (Call Type 1), it is not strictly necessary to implement these parameters to filter the data in the ADS, because Exago BI will filter the data regardless once it is returned to the application.

Supressing Application Filtering & Sorting

Beginning with version 2018.1, you have the option to suppress the default application filtering and sorting of data returned for data queries. You can choose to filter and sort in the ADS code instead, which allows for more control over the manner in which these processes are done.

To suppress application filtering and sorting for selected objects, set the following Admin Console setting for each object to True:

( Objects ) Suppress Sort and Filter

Note: Suppression will only occur for reports where the object is joined exclusively to zero or more programmable objects which also have suppression enabled. In-memory processing such as advanced joins, special Cartesian processing, and sort and filter formulas will override this setting and force application processing to occur.

Exago BI will not filter the dropdown values for filter values queries (Call Type 2) by default. It is highly recommended to apply filtering for these queries in the ADS, to avoid inaccurate and potentially insecure behavior for the filter dropdown lists. Therefore, these parameters should not be seen as optional for all but the most trivial applications.

The following table shows the parameter values for either data or filter values queries.

  Data Filter values
Call Type 1 2
Column List of requested column database names
"col1,col2,col3"
Requested column database name
"col2"
Filter Data object filter SQL
"(col1 = 'value') AND (tenantId = 'id')"
Filter dropdown value
"value"
Full Filter Report filter SQL
"([obj].[col1] = 'value') AND ([obj].[tenantId] = 'id')"
Tenant and row-level filter SQL
"([obj].[tenantId] = 'id')"
Sort

Sort SQL

"col3 asc, col1 desc"
Empty string 
Expected Return DataSet with requested columns, optionally filtered DataSet with single column, filtered

The Column parameter will give the list of fields necessary for a report execution (data query) or the field requested by a filter dropdown (filter values query). It may be more performant to build the DataSet with only the requested columns.

Filtering for data queries is only required if the Suppress Sort and Filter setting (v2018.1+) is True. Otherwise Exago BI automatically filters the data set in memory for data queries.

In a future version of Exago BI, users will be able to filter by formulas, using the application's internal formula engine. It is impossible to handle formula filters in an ADS code.

You must apply filtering for filter value queries.

What is a filter values query?

Several activities in the application involve querying a table for a list of values from a single field, possibly filtered by a user input string. This occurs when users interact with filter dropdowns, so that they can view and select valid field data when creating report filters.

Dropdown.png

Example of interaction with a filter dropdown

Note: Report viewer and dashboard interactive filters are populated by the data query for the report execution, not by a separate filter values query.

You can prevent this type of query entirely by setting the following Admin Console setting to False:

( Filter Settings ) Read Database for Filter Values

Also note that it is not possible to implement Filter Dependencies in an ADS.

Typing into a filter dropdown field causes it to return values filtered by the input text. This filtering must be done manually in the ADS for two reasons:

1. Accuracy: Dropdown lists are not filtered by Exago BI, so typing to filter will be ineffective if not implemented manually.

2. Security: Row and column tenancy filters must be applied to the dropdown list so that users cannot view data values without access rights.

The Column, Filter, and Full Filter parameters must be combined together to generate the proper filtering. With Call Type 2, the Column parameter contains the name of the requested data field, and the Filter parameter contains the input text. These can be combined to generate the necessary SQL WHERE clause.

Example

string SQL = string.Format("(CONVERT({0}, System.String) LIKE '%{1}%')", Columns, Filter);
// Note: DateTime columns should use a different comparison

The Full Filter parameter already contains the necessary tenancy SQL specified in the data object and active role settings, so this can be easily added to the input filter SQL to create the full clause. Then apply it to the data set and return only the resulting rows.

SQL += " AND " + FullFilter;
DataRow[] DropdownRows = FullTable.Select(SQL);
// Note: In a proper implementation be sure to sanitize the SQL before passing it to the db

Additional Considerations

DateTime comparisons should be handled in a different manner than with a LIKE operator.

Only distinct values are shown in the dropdown, so there is no need to use a DISTINCT clause in the SQL statement.

A maximum of 100 values are shown in the list. You can choose to implement a TOP 100 clause, if it would improve performance.

As of Exago BI version 2017.3, for database sources, end-users have the option of selecting, via a menu, whether their filter dropdown input should apply a Starts With or Contains filter to the data field. This behavior cannot currently be implemented in an ADS, because the value of this setting is not passed in a parameter.

As of 2017.3, values are queried incrementally as the user scrolls the dropdown list, for supported sources. This is also not currently supported by ADS.

Sort Parameter

It is only required to sort in an ADS if the Suppress Sort and Filter setting (v2018.1+) is True. Otherwise Exago BI automatically sorts the data set in memory for data queries.

Formula sorts use the application's internal formula engine. It is impossible to handle formula sorts in an ADS code.

Note: The sort parameter is irrelevant for filter values queries. Filter values are always sorted in ascending order.

Handling Multiple Data Tables

There are two ways which an ADS can allow for multiple data tables to be returned as separate objects in the Exago BI data model. The first involves using multiple entry methods. The second involves specifying table names in the metadata.

Multiple Methods

Separate tables can be implemented as separate entry methods within the ADS. This has several advantages. Each public method from a source will appear as a "table" in the Admin Console when adding objects to the data model. Method names can describe the tables in a self-documenting manner.

Example

/* Public methods seen by Exago BI */
public static DataSet EmployeeTable(int call) { return GetTableData("Employee", call); } public static DataSet ProductsTable(int call) { return GetTableData("Products", call); } /* Common data retrieval method */ private static DataSet GetTableData(string tableName, int call) { ... }

However, this does not allow retrieval of previously undefined tables without editing the ADS and adding additional methods in support. So for a database where objects are likely to be added, programmatic maintenance of the ADS will be required. That isn't ideal.

Single Method with Parameters

The Data Category and Data Object Id parameters correspond to the user-input metadata fields Category and Id (respectively) in each data object's properties in the Admin Console. The Id field, in particular, can accept arbitrary input without affecting the user interface. So table names or identifiers can be defined in the Id field. Thus one entry method can support multiple tables based on a conditional string from the metadata. This allows an ADS to support a more dynamic data structure, since any subsequent tables can be added simply by editing the metadata.

Example

public static DataSet GetTableData(string objectId, int call)
{
    switch (objectId)
    {
        case "Employee": { ... }
        case "Products": { ... }
        default: { throw ... }
    }
}

However, adding metadata elements to the config can be error-prone, so ensure that admins always have the up-to-date mapping of Ids to tables.

Configuration

Once written, the following steps are taken for Exago BI to access the ADS.

1. Ensure that the assembly is in a location accessible by the IIS application pool user (and the Exago Scheduler service, if in use), with read and execute permissions. 

2. Add the ADS to the Admin Console data sources (with Type=Assembly) using the following connection string format:

assembly=\path\to\assembly.dll;class=Namespace.Class

Click the lightning bolt icon to verify that the assembly and class are accessible.

3. Define the method parameter names in the Programmable Object Settings.

4. Add the entry methods to the Admin Console data objects (the Automatic Data Discovery tool does not support ADS). Then add any relevant joins and metadata aliasing.

Maintenance

There are several notes to keep in mind when updating an ADS and/or updating Exago BI.

System Locks

The web server maintains a lock on the ADS file as long as it is running. Additionally, if using any classes in the WebReports.Api.Scheduler namespace, or if there are any scheduled or cached reports that use the ADS, the scheduler service(s) will lock it as well.

When updating the ADS, the web server and schedulers will need to be disabled while the file is replaced. So it is generally not appropriate to update an ADS in a live environment.

WebReportsApi.dll Version

If you are using SessionInfo or any other Exago .NET API classes, the ADS must be recompiled with the new version of the WebReportsApi.dll assembly whenever Exago BI is updated.

Utility libraries, such as SQLUtils.dll are updated less frequently than the main web application binaries. Even if only referencing utilities, verify nevertheless that the ADS works without needing recompilation.

Example

The following example is provided for reference. While trivial, the example is fully working and demonstrates a typical ADS framework. It can be freely used, extended, and redistributed for any custom implementation.

Download the example.


Hidden Article Information

Article Author
Exago Development
created 2018-04-20 14:47:00 UTC
updated 2018-05-15 20:57:48 UTC

Labels
data, sessionInfo, dropdown, filter, table, set, object, net, c#, query, api, schema, programmable, pdo, etl, framework, clr, ads, dll, library, sharp,
Have more questions? Submit a request