I'm trying create ssas model using following code snippet. I'm able to create model when source is sql server but when the source is apache phoenix i'm getting mentioned error in title. Below is working and not working code, what is the reason it is not working for apache phoenix?
SQL Server:
if (srcType.Equals("Sql Server"))
{
dataSourceName = "Sql Server";
objDatabase.Model.DataSources.Add(new ProviderDataSource()
{
Name = dataSourceName,
Description = "A data source definition that uses explicit Windows credentials for authentication against SQL Server.",
ConnectionString = "Provider=MSOLEDBSQL;Data Source=" + strSourceServer + ";Initial Catalog=" + srcDB + ";Persist Security Info=True;User ID=" + srcDBUser + ";Password=" + srcDBPassword,
ImpersonationMode = Microsoft.AnalysisServices.Tabular.ImpersonationMode.ImpersonateAnonymous,
// Account = srcSysUser,
// Password = srcSysPassword,
});
}
//////After forming SQL
Console.WriteLine("Sql--> " + sqlQuery);
t.Name = strDesteTableName;
t.Description = strDesteTableName;
t.Partitions.Add(new Microsoft.AnalysisServices.Tabular.Partition()
{
Name = srcTable,
Source = new QueryPartitionSource()
{
DataSource = objDatabase.Model.DataSources[dataSourceName],
Query = sqlQuery,
}
});
objServer.Databases.Add(objDatabase);
objDatabase.Model.RequestRefresh(Microsoft.AnalysisServices.Tabular.RefreshType.Full);
objDatabase.Update(UpdateOptions.ExpandFull);
Phoenix (Not working):
else if (srcType.Equals("phoenix"))
{
dataSourceName = "DSN=Hortonworks Phoenix ODBC DSN";
string json = @"{""protocol"": ""odbc"",""address"": { ""options"": {""dsn"": ""Hortonworks Phoenix ODBC DSN"" } }}";
string credJson = @"{""AuthenticationKind"": ""UsernamePassword"",""kind"": ""Odbc"",""path"": ""dsn=Hortonworks Phoenix ODBC DSN"",""Username"": ""root"",""Password"":""D@ntl3tg0"" }";
objDatabase.Model.DataSources.Add(new StructuredDataSource()
{
Name = dataSourceName,
Description = "A data source definition that uses explicit Windows credentials for authentication against Phoenix.",
// ConnectionString = "Provider=MSOLAP;DSN=Hortonworks Phoenix ODBC DSN;UID=root;Pwd=D@ntl3tg0;",
//ImpersonationMode = Microsoft.AnalysisServices.Tabular.ImpersonationMode.ImpersonateAnonymous,
//Account = srcSysUser,
//Password = srcSysPassword,
ConnectionDetails = new ConnectionDetails(json),
Credential = new Credential(credJson)
});
}
Console.WriteLine("Sql--> " + sqlQuery);
t.Name = strDesteTableName;
t.Description = strDesteTableName;
t.Partitions.Add(new Microsoft.AnalysisServices.Tabular.Partition()
{
Name = srcTable,
Source = new QueryPartitionSource()
{
DataSource = objDatabase.Model.DataSources[dataSourceName],
Query = sqlQuery,
}
});
Getting the error:
Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] Native queries aren't supported by this value.. '. at Microsoft.AnalysisServices.Tabular.Model.SaveChanges(SaveOptions saveOptions) at Microsoft.AnalysisServices.Tabular.Model.SaveChanges(SaveFlags saveFlags) at Microsoft.AnalysisServices.Tabular.Database.OnAfterUpdate(UpdateOptions options) at Microsoft.AnalysisServices.MajorObject.Update(UpdateOptions options, UpdateMode mode, XmlaWarningCollection warnings) at Microsoft.AnalysisServices.MajorObject.Update(UpdateOptions options) at SsisExecute.Program.CreateDatabase(Server objServer, String strCubeDBName, String strDesteTableName, String srcType, String strSourceServer, String srcDBUser, String srcDBPassword, String srcDB, String srcTable, String partitionColums, String groupByCols)