Importing SSRS Data into a Tabular Model via an Atom Data Feed

Andrew Smith Posted on

The SSAS tabular model supports data access from a wide variety of data sources, including atom data feeds.

An SSRS report can be used to implement an atom data feed, which can be a useful way of providing data for a tabular model in the case where SSRS and tabular reports need to be running from shared data, or if SSRS functionality can be used to present data to a tabular model in a more suitable way than the originating data source can.

MSDN here elaborates on this latter point:

“Using reports as a data feed gives you an additional way to provide data to applications when the data is not easy to access through client data providers, or when you prefer to hide the complexity of the data source and make it simpler to use the data. Another benefit of using report data as a data feed is that you can use Reporting Services features such as Report Manager, security, scheduling, and report snapshots to manage the reports that provide data feeds.”

In this post I use SSRS to grab exchange rate data from an XML document on the internet, which is used to populate a shared dataset that becomes the source for a simple table-based report. This report is then exported as an Atom Service Document, which becomes a source for a tabular model.

I’m using the Euro exchange rate data available here:

http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml

an excerpt of which looks like:

ssrsatomforex1

First I create an SSRS shared data source based upon the XML data source type and the URL from above:

ssrsatomxmldatasource

There are 2 options for credentials for XML data sources: “Current Windows User (Integrated Security)” and “No Credentials Are Required”. I used the latter, since that’s the only option if cache refresh plans are to be used (which they are! – see below). For this latter option to work, the Unattended Execution Account must be set up in the Reporting Services Configuration Manager.

I then create a shared dataset based upon that data source:

ssrsatomxmldataset

and a simple report with a table that uses the time (labelled “Date” in the header below), currency and rate fields from the dataset:

ssrsatomxmlreport

The use of a shared dataset for the exchange rate data has 2 advantages in this scenario. Firstly it can be shared between multiple reports in the same SSDT project (or by any Report Builder generated reports), and secondly it can have a cache refresh plan associated with it, e.g.:

ssrsatomcacherefresh

This means that the exchange rates can be refreshed at a controlled interval, e.g. once per night, assuming that the cache for the same report is set to expire just before the cache refresh runs.

With the project deployed to the Report Server, the simple report created above can be exported to an atom service document by clicking the button circled in red below:

ssrsatomexport1

This creates a file with an .atomsvc extension which needs to be stored on disk in a place where the tabular project can pick it up. This file is basically an XML wrapper around this URL:

http://desktop-au915r1/ReportServer?/Currency Demo/CurrencyConversion/EURReport&rs:Command=Render&rs:Format=ATOM&rc:ItemPath=EuroFXRates

which is a standard Report Server HTTP request, suitably parameterised for the atom data export.

All we need to do now is create a new SSAS tabular project in SSDT and create a new connection, using the 3rd data feed option below:

ssrsatomtabularfeeds2

The following dialog appears, confirming the source table found within the atom service document:

ssrsatomfeedtable

Note that the “EuroFXRates” name comes from the Name property of the tablix in the SSRS project, which I changed away from the default of “Tablix1”.

The table is then processed into the model, as can be seen here:

ssrsatomtabulartable

DAX can be used to interrogate this table, where the exact implementation details are subject to how it is to be used within the model being built. The exchange rate data in this table will only be current as of the last time it was processed, so a schedule needs to be set up to reprocess the table as required, e.g. nightly, shortly after the SSRS cache refresh plan has run for the associated data set. This scheduled processing can be implemented using SQL Server Agent via an XMLA script, e.g.:

ssrsatomxmla

This does a full process, which is suitable for the scenario where only the latest day’s Euro exchange rates are required.

(If the exchange rate table needs to be added to each day such that previous days’ history is required, then a better approach would be to have an exchange rates table in SQL Server added to day-by-day by SSIS (or similar), and then to load the tabular model off this. This approach supports a full reload of the history into the tabular model at any time, which isn’t possible with the atom data feed mechanism described above.)