SAP HANA Connector – Effects of creating measures in Direct Query mode

As we know SAP HANA Connector can be used in both Direct Query and Import mode.

When using Direct Query mode, we do get the option to create measures in Power BI. Not all DAX functions are supported. Aggregations like SUM, MIN, MAX, COUNT, DISTINCTCOUNT are the only ones supported.

We need to be careful while creating these measures though. These measures are evaluated in Power BI, which means all the data is retrieved into Power BI before the measure is evaluated.

E.g. if you create a measure like – DISTINCTCOUNT(Product[ID]), complete Product table is retrieved into Power BI before DISTINCTCOUNT is applied. So if you have millions of rows in Product table, Power BI will try to bring in all these rows. And here is another caveat – in Direct Query mode, result set is capped at a million rows.

Bottom line, be judicious while creating measures in Direct Query mode. Try to query fold so you can use HANA’s resources to perform the aggregations.