I am trying to retrieve data from Azure Analysis Services using ADOMD.NET from a deployed model in cloud. The code snippet is as below, but i am getting an error that the ConnectionString in invalid.
using Microsoft.AnalysisServices.AdomdClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Test_Analysis_Service_retrieval
{
class Program
{
static void Main(string[] args)
{
string queryString = @"SELECT [MAP_CUST_NAME] FROM [AAS_MAPLOOKUP] where [MAP_ACT_NO] = '120000810';";
string connectionString = @"Data Source=asazure://westus.asazure.windows.net/bbacloud;UserName=xyz@gmail.com;Password=ABC@123;";
using (AdomdConnection connection = new AdomdConnection(connectionString))
{
CellSet AASOutput = null;
System.Xml.XmlReader reader = null;
try
{
string s = connection.ConnectionString;
Console.WriteLine(s);
connection.Open();
AdomdCommand command = new AdomdCommand(queryString, connection);
command.CommandTimeout = 100000;
reader = command.ExecuteXmlReader();
Console.WriteLine(reader.ReadOuterXml());
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
if (reader != null)
{
reader.Close();
}
connection.Close();
}
}
}
}
}
The first thing you need to do is to ensure you have the latest ADOMD.NET (AdomdClient) installed. Download it from here. After you have installed it, then make sure your C# project has a reference to it at:
C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.AnalysisServices.AdomdClient\v4.0_13.0.0.0__89845dcd8080cc91\Microsoft.AnalysisServices.AdomdClient.dll
Next, you need to change your connection string to:
Note a few things. First, it's User ID not UserName. Second, the user needs to be an Azure Active Directory user (an organizational account, not a personal LiveID). Finally, you need to specify the Initial Catalog to ensure that you connect to the correct database in case you ever have multiple databases deployed.
Turns out that this issue is due to the RTM version of AdomdClient not actually supporting the RTM version of Azure Analysis Services. As a result, the following Nuget Package is required:
https://github.com/ogaudefroy/Unofficial.Microsoft.AnalysisServices.AdomdClient
Once you remove / uninstall the version 12 ( the RTM version ) of Microsoft.AnalysisServices.AdomdClient.12.0.2000.8 and install the above AdomdClient everything works just fine. In short, the v12 version doesn't have the code built in to parse asazure:// Data Sources
Highly annoying with no documentation on Microsoft's site nor support relating to the problem. However this will address your question.