Power BI Questions

[Jai_Bankar]

Hello All,

I'm having some doubts regarding Power BI's functionality.

I am not sure how Power BI handles this.
Furthermore, I attempted to investigate but was unable to achieve the desired outcome.

Please help me clarifying the below points:

1. If a user changes a filter on dashboard, does PBI fire a new query to the database?
2. If another user at the same time opens the same dashboard, does PBI fire new query to database, or it is retrieved from cache?
3. In case of Direct Query, does PBI store any data? Is it cached? Does the processing happen in memory?
4. Is it possible to store historical data as import mode and only keep recent year's data in Direct Query mode, and combine both datasets — Direct Query and Imported data?
5. Customer is having 100 databases. How can we keep 1 PBIX file and still be able to retrieve data from 100 databases? What should be the approach and architecture?

[lbendlin · Accepted solution]

1. If a user changes a filter on dashboard, does PBI fire a new query to the database? Dashboards have no filters. Did you mean Report?  If the data source is in Direct Query mode then yes, in import mode no.
2. If another user at the same time opens the same dashboard, does PBI fire new query to database, or it is retrieved from cache? Depends on the connection type. New query for Direct Query mode
3. In case of Direct Query, does PBI store any data? Is it cached? Does the processing happen in memory? Not sure why this is relevant
4. Is it possible to store historical data as import mode and only keep recent year's data in Direct Query mode, and combine both datasets — Direct Query and Imported data? Normally it's the other way round. When you use aggregations you put frequently used data into import mode and rarely used data into Direct Query.  You may also want to look into incremental refresh
5. Customer is having 100 databases. How can we keep 1 PBIX file and still be able to retrieve data from 100 databases? What should be the approach and architecture? Depends on the size of the data and the Premium capacity SKU you have. Do they have just databases or do they have a data model across these?

[lbendlin · Accepted solution]

1. If a user changes a filter on dashboard, does PBI fire a new query to the database? Dashboards have no filters. Did you mean Report?  If the data source is in Direct Query mode then yes, in import mode no.
2. If another user at the same time opens the same dashboard, does PBI fire new query to database, or it is retrieved from cache? Depends on the connection type. New query for Direct Query mode
3. In case of Direct Query, does PBI store any data? Is it cached? Does the processing happen in memory? Not sure why this is relevant
4. Is it possible to store historical data as import mode and only keep recent year's data in Direct Query mode, and combine both datasets — Direct Query and Imported data? Normally it's the other way round. When you use aggregations you put frequently used data into import mode and rarely used data into Direct Query.  You may also want to look into incremental refresh
5. Customer is having 100 databases. How can we keep 1 PBIX file and still be able to retrieve data from 100 databases? What should be the approach and architecture? Depends on the size of the data and the Premium capacity SKU you have. Do they have just databases or do they have a data model across these?

[Jai_Bankar]

Hello @lbendlin ,

Thanks for helping out.

For the 4th point: I'm bit confused, Do you mean the manage aggregation feature?.

For 5th: They have seperate database, thus instead of storing 100 pix files, is it feasible to simply modify the database's path based on the user that signs in?

[lbendlin · Accepted solution]

#4 has just been answered at Ignite - they announced the availability of hybrid tables.

#5 is probably possible with Power Quey parameters but you will have to navigate the formula firewall issues. I have not tried such a scenario.