Using query folding to optimise Luminesce queries in Power BI Desktop

Query folding is the ability for a Power BI Desktop Power Query to use a single SELECT query statement to retrieve and transform data.

With query folding, data is both retrieved and transformed at the source. Without query folding, excess data may be fetched from the source before being separately transformed within the Power BI Desktop Power Query engine. This may lead to hefty increases in the time it takes for a request to be processed. 

For example, you might write the following query to retrieve the first 100 transactions from a particular portfolio...

select * from Lusid.Portfolio.Txn
where PortfolioScope = 'Finbourne-Examples'
and PortfolioCode = 'Global-Equity'
limit 100

...and want to connect to the data via DirectQuery (that is, dynamically querying the data source rather than importing data that is then queried):

Once you have connected to the data source, you can Load the data and begin efficiently building visualizations, such as the following example, by only accessing the data you need. 

As you used a single SELECT query statement in the underlying native query, you can ‘fold’ over it, for example by applying a filter on a column in Power BI's Power Query Editor:

Query folding works by generating a wrapping SELECT statement which the base native query is then nested within. For the example above, the base native query might be wrapped inside of the filtered query as follows:

select "LusidInstrumentId",
    "TradePrice",
    "Units"
from
(
    select ^ from Lusid.Portfolio.Txn
where PortfolioScope= 'Finbourne-Examples'
and PortfolioCode = 'Global-Equity'
limit 100
)
where "TradePrice" >= 2.5
and LusidInstrumentId != 'LUID_ZZZZZZZZ'
and LusidInstrumentId != 'CCY_GBP';

Not all operations are currently supported for query folding. If you are having trouble when filtering or performing other operations, it is recommended to check the native query and remove areas which can interfere with query folding, for example ORDER BY clauses or GROUP BY statements.

Query folding is supported in version 1.x and above of the Luminesce Power BI Connector when using the Luminesce (Beta) data source. See how to update to the latest version of the connector.

Read more on query folding in the Power BI help documentation.

Using dynamic M query parameters for efficient filtering

You can further refine your control over the data being retrieved and transformed by using dynamic M query parameters in your query. This can ensure that filter selections in Power BI Desktop incorporate into your base query at the right point to improve query performance. 

You can create a new parameter for your query in the Power Query Editor via Manage Parameters. Once created, you can add the dynamic M query parameter to your query via the Advanced Editor, for example:

let
  FromEffectiveAt = Date.ToText(Date.From(ParameterStartDate), [Format="yyyy-MM-dd", Culture="en-GB"]),
  Sql = Text.Combine({
    "select * from Lusid.Portfolio.Txn where PortfolioScope = 'Finbourne-Examples'
    and FromEffectiveAt = '", FromEffectiveAt, "'"
  }),
  Source = Value.NativeQuery(Luminesce.Platform("<your-domain>",
    [CommandTimeout=null, UseMetadataCache=null, UseQueryCache=null, QueryCacheDuration=null])
    {[Name="Luminesce",Kind="Database"]}[Data],
    Sql, null, [EnableFolding=true])
in
  Source

To provide values for the parameter, you need to create a table of values which you can then bind to your query parameter and use to slice and filter data. Read more on using dynamic M query parameters in the Power BI help documentation.