Limiting the number of retrieved rows using Fill in ADOMD

Question

The following C# code runs a DAX statement and retrieves a DataTable. This works fine, but now I need to retrieve from the database up to N rows. Is there a way to limit the number of rows returned by the Fill function? If not, how can I retrieve the top N rows? Note that I need to keep this generic for any DAX statement, so you shouldn't change the DAX itself. Also, I don't want to retrieve all the data and then take the first N rows as the data may be too large.

    public static DataTable runDaxStatement(int maxRows) {

        var con = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
        AdomdConnection conn = new AdomdConnection(con);

        DataSet ds = new DataSet();
        ds.EnforceConstraints = false;
        AdomdCommand cmd = new AdomdCommand("evaluate customers", conn);
        AdomdDataAdapter da = new AdomdDataAdapter(cmd);
        da.Fill(ds);

        return ds.Tables[0];

    }

Answer 1

Came across the following TOPN function in the documentation.

This can be used to return the top N rows of the specified table.

For example

public static DataTable runDaxStatement(int maxRows) {
    var connectionString = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
    using(AdomdConnection connection = new AdomdConnection(connectionString)) {
        string commandText = $"EVALUATE TOPN({maxRows}, customers, <orderBy_expression_here>)";
        AdomdCommand command = connection.CreateCommand();
        command.CommandText = commandText;

        DataSet dataSet = new DataSet(){
            EnforceConstraints = false
        }

        AdomdDataAdapter adapter = new AdomdDataAdapter(command);
        adapter.Fill(dataSet);

        return dataSet.Tables[0];
    }
}