DirectQuery vs Import conumdrum

[RobertG]

We've developed a Dashboard using the Import option.  I was naive to think that a move to DirectQuery would be easy. Subsequently I've realsied the difficulty in doing this as the queries to the data source are non-optimal and don't fit the mould for DirectQuery - they pull back too much data and I've got a nested join within my DAX code.

The easiest way around this would be for Microsoft to allow half houlry refereshes of the data with the Import option - why can't this be done?

Or I could have 6 versions of the Dashboard (i.e. 6*8=48) so I can have a least one upto date dashboard for each hhr during the day. 😉 

[Seth_C_Bauer]

@RobertG Have you tried to optimize the queries? Using views would also be a way to limit/shape the data set the way you need it using DQ. Even in the import scenerio it would be worthwhile so you don't pull extra data you aren't going to use anyway.

[RobertG · Accepted solution]

Thanks Eno for the reply.

I've been doing a bit of investigation and it looks as though you have do things 'properly' with DirectQuery. i.e write efficent queries and move as much as the processing into the SQL layer as possible. With DirectQuery you don't have the added luxury of preprocessing and shaping the data in the PowerQuery layer.

This just means more work which I had hoped to avoid. At least I'll be wiser next time.