Friday, September 21, 2018

SSRS - Display data from Hadoop (Hortonworks)


Data Source: First, you need to download the ODBC driver from the Hortonworks add-ons page (http://hortonworks.com/hdp/addons/).

Install and configure the HORTONWORKS ODBC driver on Windows7
The Hortonworks ODBC driver enables you to access data in the Hortonworks Data Platform from Business Intelligence (BI) applications such as SSRS, Microsoft Excel, Tableau, Qlik, Micro Strategy, Cognos, and Business Objects.

You  can see steps into below site:

Once you configure the ODBC driver for same, you can format you connection string in below form:

Data Source(Screenshot from SSRS):

Credentials: Connection will establish only for valid credentials
 

Dataset: (Hadoop Query format) to pull the data from Hadoop HDFS environment for the report:
SELECT   
regexp_replace([ProductID],' "','') AS [ProductID],
regexp_replace([Name],'" ','') AS [Name],
regexp_replace([ProductModel],' "','') AS [ProductModel],
regexp_replace([CultureID],'"  ','') AS [CultureID],
regexp_replace([Description],' "','') AS [Description]
FROM  exlog.main

Dataset Filters: to limit the data for the report
Report Execution: