Query SSAS from X++ using DMX

For one of our projects we needed to query data from SQL Server Analysis Service (SSAS). We choose DMX (Data Mining Extensions) to do that. Following code shows how to do it

static void QuerySSASUsingDMX(Args _args)
{
    str                                dmxConnectionString, dmxQuery, value;
    int                                i, rowCount;
    
    System.Data.DataTable              dataTable;
    System.Data.DataRowCollection      collection;
    System.Data.DataRow                row; 
    System.Object                      object;
    
    Microsoft.AnalysisServices.AdomdClient.AdomdConnection      adomdConn;
    Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter     adomdDataAdapter;
    
    dmxConnectionString = 'Data Source=SSAS_SERVER;Catalog=CafeAssociation';
    dmxQuery = 'Select flattened predictAssociation ([CafeAssociation].[v Cafe Assoc Seq Line Items], INCLUDE_STATISTICS, 20) from [CafeAssociation]';
    
    dataTable = new System.Data.DataTable();
    
    adomdConn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(dmxConnectionString);
    adomdDataAdapter = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(dmxQuery, adomdConn);
    adomdDataAdapter.Fill(dataTable);
    
    collection = dataTable.get_Rows(); 
    rowCount = collection.get_Count();    
    
    for (i = 0; i < rowCount; i++) 
    { 
        row = collection.get_Item(i); 
        object = row.get_Item(0);
        value = object.ToString();
        
        info(strFmt('%1. %2', i+1, value));
    } 
    
    adomdConn.Close();
}

Microsoft.AnalysisServices.AdomdClient is not available in Dynamics AX and can be manually added to the "References" node in AOT.

This posting is provided "AS IS" with no warranties. Use code at your own risk.