Reference vs Duplicate - using subset of data from main query

[chris2001]

Hello,

I am querying my main database and it is huge!  Once I connect to the server, I only add product item so that I can filter on the products I need and not the whole database.   I have filterer the products and added all the other columns I needed i.e. quantities, stores, transaction date, etc. to get the table of data I need to create visuals.  This main query can be refreshed daily but it does take a long time... Now that I have created this filtered table connected to the main database, I find that I can't add measures as the query goes back to the main database and times out because it's too big.  Should I duplicate the filtered results and use that to create my measures.  How will the data refresh if it's a duplicate?

[v-lid-msft]

Hi @chris2001 ,

Based on this document about Reference and Duplicate query, When a query references a second query, it's as though the steps in the second query are combined with, and run before, the steps in the first query. So The Duplicate is as the same thing, combine the query manually. But reference queries can avoid the duplication of logic across your queries. 

It seems that you want to only get parts of data based on product type? Have you tried to assign native sql statement when connecting the data source, so that it will only retrive the nessary data from source?

We also cannot understand what you mean "can't add measures", measure is like a formula, does not store data and only calculate when nessary, are you meaning create calculated table or anything else?

Best regards,

[MattAllington]

If you are using direct query, you should get help from a DBA to optimise the queries (even if you are using import mode, your refresh may also benefit). 

if you are using import mode, then you can add measures at any time without the need to go back to the database. Maybe something different is happening. Are you sure you have your terminology right?