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:
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.
Microsoft SQL Server Reporting Services (SSRS) can access Denodo data in its reports by using the SSRS built-in ODBC connector.
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.
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 Query… It is possible to test the associated to the dataset.
It is possible to create reports that use the dataset with parameters:
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.
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.
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.
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.
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”.
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
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.
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.
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)