Forum Discussion
Mixed Storage in Composite Models (DirectQuery + Import) for Desktop and Service
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.
9 Replies
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/
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,
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
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,
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.
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,
