Forum Discussion
Question about scheduled refresh
If your users absolutely cannot have the data be older than 1 minute then you should build your report using Direct Query against a database. Note that you will need to make sure that your database has appropriate resources and is appropriately tuned to handle the additional query load, but this sort of low latency requirement is one of the use cases that Direct Query was designed to address.
A live connection probably would not help as you would still need to load the data into a SSAS Tabular model, but it might be an option if you could make use of an incremental processing stategy in order to reduce your processing time. (since PBIRS currently does not support any form of incremental processing)
As a rough rule of thumb once you get under 10-15 minutes for a refresh you are getting into the range where the cost of constantly loading and compressing the data could outway the benefits. If you have large data volumes or a high number of user queries you may need to push these times out, or if you have small datasets you might be able to get away with a smaller window.
You may even want to consider 2 models, one that is larger and more complex and has more historical data but is updated less frequently and a second that is in Direct Query mode that may be simpler and only have access to more recent data (to keep the queries small and fast). It depends on your requirements, I just wanted to point out that you may not have to solve all your reporting needs in a single data model.
If your users absolutely cannot have the data be older than 1 minute then you should build your report using Direct Query against a database. Note that you will need to make sure that your database has appropriate resources and is appropriately tuned to handle the additional query load, but this sort of low latency requirement is one of the use cases that Direct Query was designed to address.
A live connection probably would not help as you would still need to load the data into a SSAS Tabular model, but it might be an option if you could make use of an incremental processing stategy in order to reduce your processing time. (since PBIRS currently does not support any form of incremental processing)
As a rough rule of thumb once you get under 10-15 minutes for a refresh you are getting into the range where the cost of constantly loading and compressing the data could outway the benefits. If you have large data volumes or a high number of user queries you may need to push these times out, or if you have small datasets you might be able to get away with a smaller window.
You may even want to consider 2 models, one that is larger and more complex and has more historical data but is updated less frequently and a second that is in Direct Query mode that may be simpler and only have access to more recent data (to keep the queries small and fast). It depends on your requirements, I just wanted to point out that you may not have to solve all your reporting needs in a single data model.
d_gosbell Thanks for your elaborate answer! I will take it all into consideration.