Friday 7 March 2014

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.

4 comments:

  1. Hey Navid,

    I hope you are doing well. My name is Samuel and I’m the Managing Editor of Dynamics101.com, a recently launched training site for Microsoft Dynamics products.

    I just wanted to let you know that after careful consideration we have decided to include your site in our first annual list of Top 25 Dynamics AX blogs.

    I’ve mentioned your blog here http://www.dynamics101.com/2014/06/top-25-dynamics-ax-sites/, and we’ve created a badge that you could showcase on your site if you like. Just copy and paste the code from the bottom of my page.

    Either way, thanks for creating such a great blog! Keep up the good work.

    Best,
    Samuel Harper | Managing Editor
    http://www.dynamics101.com | @Dynamics_101

    ReplyDelete
  2. Hello Naved,

    Thanks for the great article!

    I tried using your code and added references to Adomd client and System.Data assemblies. But AX is unable identify below classes:
    System.Data.DataTable
    System.Data.DataRowCollection
    System.Data.DataRow

    Am I missing something?

    Thanks Again,
    Michael Brown

    ReplyDelete
    Replies
    1. Hi Michael,

      System.Data.* should be available in AX natively as these are .NET data types and does not require adding any assembly. The above code should work on AX2012 (didn't test on any other version).

      Thanks

      Delete
  3. Thank you so much for sharing this helpful code, which shows how to get query data from SQL Server Analysis Service for microsoft dynamics AX system, it's just what I was looking for.

    ReplyDelete