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];

    }

Traverse the table for the desired count, passing the data to a new table. — Nkosi, Jun 14 '20 at 12:58
I thought about that, but that means that I have to retrieve all the rows from the database, I want to limit that number as the data may be very large. — ps0604, Jun 14 '20 at 12:59
Found this https://learn.microsoft.com/en-us/dax/topn-function-dax — Nkosi, Jun 14 '20 at 13:03
`da.Fill(ds,,,)` - Do you have this kind of overloaded Method ? (In ADO.NET - its available) — Nareen Babu, Jun 14 '20 at 13:04
@user2932057 This worked: da.Fill(0, maxRows, dt); where dt is the DataTable to return — ps0604, Jun 15 '20 at 12:15
@ps0604 - That's good. Better you need to control maxRows (No of records) in the query rather than perform filtering in the dataAdapter filling. — Nareen Babu, Jun 15 '20 at 12:28
Yes, but the query is entered by the user and I don’t have control — ps0604, Jun 15 '20 at 12:30

Nkosi · Answer 1 · 2020-06-14T13:37:35.547

2

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];
    }
}

edited Jun 14 '20 at 13:37

answered Jun 14 '20 at 13:32

Nkosi

235,767
35
427
472

thanks, but this implies that I need to change the DAX statement, one of the requirements is not to change it. – ps0604 Jun 14 '20 at 13:33
@ps0604 technically you need to change the statement in order to use the dax function that limits the rows retrieve. check the linked doc – Nkosi Jun 14 '20 at 13:35
@ps0604 look at the two statements and you will see how it targets the same tabla as before. – Nkosi Jun 14 '20 at 13:36
what about using the arguments in the Fill statement? I can specify a limit, as user2932057 said. I cannot change the statement because it's entered by the user. – ps0604 Jun 14 '20 at 13:37
@ps0604 Under the hood it is basically doing exactly what I had originally suggested. it will pull all the rows then apply the filter – Nkosi Jun 14 '20 at 13:38
Is there a way to avoid retrieving the rows? I’m mostly interested in knowing the field types. If there are one million rows then that could take a long time. – ps0604 Jun 14 '20 at 21:26
@ps0604 Anything done by the adaptor will be happening on the client side. well after the rows have been returned from the server – Nkosi Jun 14 '20 at 21:52
@ps0604 I would suggest you try the suggestion in the comments and use the adaptor fill with the max row. And see how the performance goes.. – Nkosi Jun 14 '20 at 22:00
after changing a little bit the response in the comments, it worked, not sure about performance yet as I don't have a big table. – ps0604 Jun 15 '20 at 12:16
@ps0604 that is good news. Test it will some large sets and check the performance. – Nkosi Jun 15 '20 at 12:59

Limiting the number of retrieved rows using Fill in ADOMD

1 Answers1