How to access Denodo Virtual DataPort from Microsoft SQL Server Reporting Services

Applies to: Denodo 7.0 , Denodo 6.0
Last modified on: 22 May 2018
Tags: External clients ODBC driver

Download document

Introduction

Microsoft SQL Server Reporting Services (SSRS) is part of Microsoft’s BI offerings included in SQL Server since SQL Server 2000 along with SSAS (SQL Server Analysis Services) and SSIS (SQL Server Integration Services).

Reporting Services is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources. Reporting Services includes a complete set of tools to create, manage, and deliver reports, and APIs to integrate or extend data and report processing in custom applications.

SSRS uses Data Processing Extensions and Rendering Extensions:

  • Data Processing Extensions are used to query a data source and return a flattened row set. Reporting Services includes processing extensions for SQL Server, Analysis Services, Oracle, SAP NetWeaver Business Intelligence, Hyperion Essbase, Teradata, OLE DB and ODBC data sources, and users can develop their own extensions. Reporting Services can also use any ADO.NET data provider.
  • Rendering Extensions transform data and layout information from the Report Processor into a device-specific format. Reporting Services includes seven rendering extensions: HTML, Excel, CSV, XML, Image, PDF, Microsoft Word(Since SQL Server 2008) and Atom (Since SQL Server 2012)..

In SQL Server 2012, Reporting Services introduces Power View, an Add-in for Microsoft SharePoint Server 2010 Enterprise Edition. Power View is a browser-based Silverlight application that provides access to data represented by tabular models.

How to access Denodo Virtual DataPort from SSRS

Microsoft SQL Server Reporting Services (SSRS) can access Denodo data in its reports by using the SSRS built-in ODBC connector.

Setup (SQL Server Report Builder)

To set up the connection to Virtual DataPort from SSRS, the Denodo ODBC driver must be used. Note that, for Denodo versions older than 6.0, the PostgreSQL ODBC driver must be installed.

  1. Create a DSN that points to a Virtual DataPort database as explained in the section Access Through ODBC of the Virtual DataPort Developer Guide.
  2. In Microsoft SQL Server Report Builder create a new data source using ODBC as connection type, and Dsn=<DSN_NAME> as connection string.

  1. Add a dataset. Choose the data source created in the previous step. Choose text as Query type and write the query.


  1. Use the fields from the query to create a report.

Setup (SQL Report Builder parameterized query)

It is also possible to parameterize a query in the Report Builder. To specify query parameters the ‘?’ character can be used in the query when adding a new dataset. See figure below:

By right clicking on a dataset and selecting QueryIt is possible to test the associated to the dataset.

It is possible to create reports that use the dataset with parameters:

Setup SSRS for Windows Integrated Security.

This is straightforward to set up, and relies on Kerberos Authentication.

In order for SSRS to use Windows Integrated Security with Denodo (so no username and passwords are required to be passed to Denodo), both the SSRS server and Denodo server need to be configured to use Kerberos authentication.

SSRS Server Config

This Microsoft article provides an overview of how to configure the SSRS server for SSO using Kerberos.

The report data source (connected to Denodo) would then be configured to use Windows Integrated Security.

Denodo Server Config

In Denodo, the server needs to be configured to use Kerberos Authentication using the Denodo Admin Tool > Administration > Server Configuration > Kerberos Configuration option.

This specifies the Kerberos settings for authentication, plus the LDAP/AD source for authorization (pulling back the user roles once user authentication is verified). Privileges are assigned to the roles in Denodo as we saw for LDAP/AD configured authentication in the PoC.

You can read more about the Kerberos Authentication section of the VDP Administration Guide.

Denodo ODBC Driver

Section Set up a DSN on Windows of the VDP Developer Guide describes how to configure the DSN including (at the end of this section) information on setting the DSN to support Kerberos SSO.

It will be supported by default as long as the Denodo Server is configured for Kerberos authentication and the client application that uses the DSN belongs to the Windows domain.

Troubleshooting

Introspection does not work

It is not possible to use the Query Designer with TableDirect as Command type as introspection does not work for ODBC sources.

This limitation not only affects Denodo Virtual DataPort but other sources as well. According to this Microsoft technical note: “in Report Builder, to specify a query for data source types Oracle, OLE DB, ODBC, and Teradata, you must use the text-based query designer”.

Unable to Connect to Denodo Data Source - Architecture Mismatch

When connecting to Denodo in a 64-bit environment from SSRS following the steps in this document, an architecture mismatch error can be thrown from the Report Builder Tool when adding a dataset:

ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

architecture_mismatch.png

In a 64-bit environment bear in mind that ReportBuilder is a 32-bit application and the 32-bit Denodo ODBC driver will have to be installed and selected to create the DSN that will be used to connect from Report Builder.

Using query parameters crashes SSRS

In some cases, when creating a dataset with a parameterized query the connection to Virtual DataPort may cause SSRS to crash

On the event of this problem one the following solutions can be used.

1) Explicitly declare which fields to project

Instead of using a SELECT * clause, define the fields to project in the query so SSRS requests the query metadata differently. In order to this solution to work,the Server side prepare option must be disabled in the DSN settings.

2) Enable ODBC option “Disallow premature”

Enable the Disallow premature option in the ODBC DSN configuration to avoid having to reply to PreparedStatements with parameters.

disallow_premature.png

The drawback of this solution is that, when creating a report, SSRS will issue a query where the parameter is set to NULL. For instance, when creating a report with the query:

SELECT * FROM customer WHERE id = ?

SSRS will first issue the following query:

SELECT * FROM customer WHERE id = NULL

 

Keep this in mind in case parameters are not defined over PK fields or when working with very large tables. When using unnamed parameters (the ones defined as ?), it will be useful to rename them at a prompt level, so the users can set them properly when running the reports. To do so, edit the parameter properties to enter a prompt name for the parameter.

Note: only unnamed parameters are allowed. Named parameters (those defined as @PARAMETER are not supported)

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training