question

JeroenvanderHeide-0629 avatar image
0 Votes"
JeroenvanderHeide-0629 asked EmiljoJ-3311 commented

Import OData feed with multiple workspaces into SSAS tablar

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-servicessql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered

Hi,

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeroenvanderHeide-0629 avatar image
0 Votes"
JeroenvanderHeide-0629 answered EmiljoJ-3311 commented

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I dig a little bit as this limit exists both in SSIS and SSAS. I currently find no work around for this, I will try to find some new ways if possible.

0 Votes 0 ·

@LukasYu-msft Any updates ?

0 Votes 0 ·

It seems like a limit of the driver that could not be around for now.

0 Votes 0 ·

Facing a similar issue extracting ODATA using SSIS into SQL Server. Let us know if by now you went through with a different tool or found a workaround in SSIS?

0 Votes 0 ·