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>

Answer 2

Disclaimer: This is not a fix if you want to keep a similar setup as i had with linked servers and so on. Below is a workaround that utilizes a different setup than linked server. This also doesn't fix demployment issues

What i ended up doing was developing an azure function that could process the data model, using this guide and some similar: https://sqldusty.com/2017/06/21/how-to-automate-processing-of-azure-analysis-services-models/

A few notes:

• the benefit of this is that it is possible to call the function URL in SQL so it can be inserted at the end of a SQL Agent job that maintains a datawarehouse as an example.
• I created a Http trigger function so i didn't have to schedule anything, but could parse a query string to the function telling it to execute.
• Be aware that the runtime needed for the above guide is .Net framework (ie the lowest version available). This is selected when creating the function app, that holds the functions. If you are better at C# then i am, then i'm sure you can make a better version.
• There are other possibilities (logic app and runbook) in azure that could do the same. I needed one that could be called in SQL, which is why i went with the above guide.
• Let me know if you need a more detailed guide :)

Answer 3

Regarding the data processing, you can try to create Azure runbooks, this worked for me. I've created PowerShell ones, instruction can be found here - https://sqlitybi.com/how-to-process-azure-analysis-services-tabular-models/.
How about the deployment issue? Did you find solution here? I am still struggling with that.

Answer 4

We had a similar issue. Problem was AAS update from 2nd to 3rd of January, I guess. Problem was measure names contained a ".". Actually, it only seemed a problem in the perspectives when relating single measure names to particular perspercitves. Anyhow, we ended up renaming all measure names with a "." and that fixed it for us.

Best, Jonas