AAS processing through Linked server: JSON DDL Request - Input string was on in a correct format

Question

We have a setup where we are using azure analysis services instances for Power BI. These instances are connected via a data gateway down to on premise SQL servers. In order to process these AAS models we are using linked servers, as we can execute JSON statements towards that and do a full process. This setup has functioned perfectly without any issues, until recently.

The errorcode is: "OLE DB provider "MSOLAP" for linked server "XXXX" returned message "The JSON DDL request failed with the following error: Input string was not in a correct format.."

Nothing has changed in our setup, it just stopped working. The code we use is as follows:

Problem solving:

• The exact same setup works on a different server (meant for the danish department), same code and everything.

• I can manually process the cube so it's not gateway related, nor is it related to parsing data types into wrong columns

• I can execute MDX scripts in the same way and those return data to me, so shouldn't be any issue with the linked server or the AAS:

• Switching to XMLA didn't help and i'm not sure one can run XMLA towards AAS after reading abit about it.

• Creating a new Linked server with the same setup and connection gave same error.

• the compitability of the AAS is 1465, and the SQL server is 14.0.3370.1

EDIT 1

Turns out i can't deploy to the AAS instance at all either, with the same error message as at the top of the post. Points me in the direction that it's the analysis services instance that is at fault here. Additionally it is only the instance running in the region North Central US that i can't deploy to. Works fine on an instance running in North Europe

Answer 1

I've ran into this problem today. Looking at this thread;

https://github.com/otykier/TabularEditor/issues/749

there is speculation that this is a microsoft issue - see the comment stating;

Microsoft are aware and are working on fixing this. No timeline yet.Some more context: Hey folks, there’s a regression related to special characters in object name references (e.g. Perspective table/column names that use certain special characters). You may be able to workaround this by deleting the DB, removing special characters like '.', '[', ']', ':', '$' and redeploying, but we’re still validating the details of the problem and will hopefully rollback the regression soon…

Edit;

I got a reply from MS today;

I would like to inform you that your research is correct. Yes, this is a regression due to a recent deployment related to special characters in object name references. Our Product team have identified this regression and they have already created the fix for it. This fix will be deployed to all Azure AS cluster by this Sunday EOD (In the Pacific hours).

Yes as you have mentioned, by removing special characters like '.', '[', ']', ':', '$' and redeploying the model will solve the issue but I believe this will be tedious task for you. So for now the workaround that I would suggest is to use the XMLA script from the SQL Server Management Studio and not the JSON/TOM command to process your model. Please refer the script as below:

<Batch Transaction="false" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
       <Refresh xmlns="http://schemas.microsoft.com/analysisservices/2014/engine">
              <DatabaseID>AdventureWorsVariableException</DatabaseID>
              <Model>
                     <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql">
                           <xs:element>
                                  <xs:complexType>
                                         <xs:sequence>
                                                <xs:element type="row"/>
                                         </xs:sequence>
                                  </xs:complexType>
                           </xs:element>
                           <xs:complexType name="row">
                                  <xs:sequence>
                                         <xs:element name="RefreshType" type="xs:long" sql:field="RefreshType" minOccurs="0"/>
                                  </xs:sequence>
                           </xs:complexType>
                     </xs:schema>
                     <row xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
                           <RefreshType>1</RefreshType>
                     </row>
              </Model>
       </Refresh>
       <SequencePoint xmlns="http://schemas.microsoft.com/analysisservices/2014/engine">
              <DatabaseID>AdventureWorsVariableException</DatabaseID>
       </SequencePoint>
</Batch>