Improve Direct Query performance with SQL 2016

[sekic]

Hi,

I got several very big tables on transaction database ("fact") , and much more corresponding tables ("dimensions"). Much of tables have also calculated fields.

Customer ask for Direct Query only.

According performance issues, is it better to:

1. query separate tables in Power BI and connect them using Manage relationships (slow because can't manage how will Power BI make joins and selections), or

2. make one view per "fact" table, adding all necesary "dimension" on database (slow because view is slow on database, Power BI don't much downgrade performance) , or

3. make kind of "temporary" table in database which is copy of view in 2, add all necessary indexes and convert computed columns in ordinary columns (much faster, but it not show actual data, it depend how much and how frequent table is refilled, eg once every 15 min, or on user request, or when user start Power BI session....).

4. any other idea?

Thanks

[Anonymous]

Hi @sekic,

Perhaps you can add a date filter on power query to filter recently data, it may increase the performance.

Regards,

XIaoxin Sheng

[sekic]

Hi,

thanks, but it's not an option. Because need to read data from several years.