Mixed Storage in Composite Models (DirectQuery + Import) for Desktop and Service

[Anonymous]

Good afternoon to the community,

I have a techincal question about the software:

We are implementing some reports using multiple Direct Queries as sources and we are merging them in the power query editor transforming them in import mode (then we have a composite model in mixed storage).

After this, we are building our visualizations through the imported tables, but we will need to upload these reports in BI Service in the next future. How will this work? When will we update the report in the service, will both the direct queries and imported merged tables be updated together?

This is important to know because the client expressely requested to use DirectQuery in order to have instantaneous answers from large tables. See the attached picture for a schema.

Kind regards.

[parry2k]

@MFelix Thanks for reaching out. 

@Anonymous Can you please put more details? When you say direct query and merge it with import table what does this mean? 

[Anonymous]

Thank you Felix and Parry,

So I am uploading Q_Rimborsi1 and Q_Rimborsi2 in DirectQuery from an Oracle DB. I cannot make a join in the SQL statement, so I have used the option "Combine/Merge Query" in Power Query Editor that creates an imported table -> Q_Step1, as represented in the screenshot below.

This is now a composite model/mixed storage. When I refresh the report in BI Service, will the merged imported table Q_Step1 simultaneously update together with the DirectQueries as they are the source of the imported table?

I hope this was clear enough, thank you for your help!!

Kind regards,

[Anonymous]

I am not sure that merged imported tables originated from DirectQueries are not refreshed as well, it sounds weird to me, but we will se that soon 🙂

The problem is that the original reports are composed by 4+ (up to 11) different SQL queries so that it becomes a bit complicated to apply full joins directly in the statements. Moreover, often they are joined on multiple fields (even 10) and it is difficult to link the models graphically. We are trying to simplify the process and speed up the project using BI capabilitie. That's why, do you have an other idea about this?

Kind regards,

[MFelix]

Let me try an call out a superuser that has some expertise on some implementations and maybe he can tell us if both tables are updated.

@parry2k  Can you please tell us if the composite models the tables in import mode are update always that the direct tables are also updated? I believe that they are not dispite they might be based on the DirectQuery tables.

I did not made any composite models implementation in this manner.

[MFelix]

Hi @Anonymous ,

The information will be updated at the same time however the import tables depending on the connection you are using (files, ODBC, SQL, ...) needs to be setup the connection also on the portal otherwise if you are making changes to the import and there is no conneciton setup those tables will always be the same and not updated.

Check the blog post below with the setup of the datasources refresh.

https://www.cittabase.com/power-bi-tips-schedule-refresh-on-composite-models/

[Anonymous]

Good Morning MFelix,

thank you for the answer, I have read the article: however we cannot schedule the refresh because the reports should act as they are DirectQuery only, even if merged tables are imported, so that all data are refreshed when the user launches the report in BI service (without a schedule, but on demand).

We are using a connection string for Oracle DB and the DB address for SQL Server embedded in the DirectQuery in the .pbix file in BI Desktop. Are all the tables (DirectQuery and merged imported) refreshed at the same time without any schedule?

We are not able to try this by ourselves as the client is installing the Gateway to enable BI Service.

Kind regards,

[MFelix]

Hi @Anonymous ,

The Direct Query refresh works on a different way from the imported tables so you will have the need to have schedule refresh for the imported and the direct query is automatic.

So summarizing the DirectQuery and Imported are not updated at the same time because they work in different ways for refreshing the data.

Check more detailed information in the link below:

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data

[Anonymous]

Thank you a lot Miguel,

So it is sure that the imported merged tables built starting from DirectQuery tables will not be refreshed in the BI Service, but they need a scheduled refresh in order to show the latest data even if the below DirectQueries are refresh and data updated. Is this correct or did I misunderstand?

Kind regards,

[MFelix]

Yes that is correct.

But sorry for the question but why are you making merge of tables that come from direct query?