Import OData feed with multiple workspaces into SSAS tablar

Jeroen van der Heide 1 Reputation point
2021-01-13T16:02:22.117+00:00

I want to create a tabular SSAS model, hosted in MS Azure, around a set of OData feeds, coming from a SAP ByDesign service.

This service has multiple workspaces: "things" and "data". The former is empty (in my case), the latter contains the tables that I need. See below for an excerpt: 

<?xml version="1.0" encoding="utf-8"?>
    <app:service xml:base="https://my-tenant.sapbydesign.com/sap/byd/odata/pro_projectmanagement_analytics.svc/" xmlns:app="http://www.w3.org/2007/app" xmlns:atom="http://www.w3.org/2005/Atom">
        <app:workspace sap:semantics="things" xmlns:sap="http://www.sap.com/Protocols/SAPData">
            <atom:title>Things</atom:title>
        </app:workspace>
        <app:workspace sap:semantics="data" xmlns:sap="http://www.sap.com/Protocols/SAPData">
            <atom:title>Data</atom:title>
            <app:collection sap:creatable="false" sap:updatable="false" sap:deletable="false" sap:requires-filter="false" sap:label="Projects - Line Items" href="RPFINCACU02_Q0001QueryResults">
                <atom:title>RPFINCACU02_Q0001QueryResults</atom:title>
            </app:collection>

Unfortunately, SSAS refuses to work with this feed because it has multiple workspaces: 

DataFormat.Error: The OData service URL points to an unparsable service document. Error: Multiple 'workspace' elements were found inside the service document. A service document must contain a single {http://www.w3.org/2007/app}:workspace element.

I haven't found any option on the SAP side to get rid of the empty workspace. However, this is valid OData so it really seems a limitation on the SSAS side.

I can manually import the individual query results (e.g. into Excel or PowerBI), but when trying to do so in SSAS I see data in the preview window, but it does not subsequently recognize that this data source contains a single table. I.e. I get just a Data source, but not a Table. Clicking "Import new tables" on the Data source does not list any "tables".

Any ideas on how to get the "master" service OData feed accepted, or to get individual reports imported as a table?

Thank you!

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,453 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,244 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,816 Reputation points
    2021-01-14T07:11:11.957+00:00

    Hi,

    Was this a SSAS tabular mode in Azure virtue machine(IaaS) or SSAS in Azure (PaaS) issue ?

    0 comments
  2. Jeroen van der Heide 1 Reputation point
    2021-01-14T07:13:08.623+00:00

    Thanks for your response.

    Haven't gotten beyond design in Visual Studio with the latest version (i.e. 1500) but it's intended to deployed to Azure's PaaS. I.e. it's the SSDT that throws me the error that it doesn't accept multiple workspaces.

    I've also tried importing the service using SSIS into SQL Server (and then load into SSAS from there), but there I get the same error about not accepting multiple workspaces.