Adding table to composite model from Direct Query model

[Declan1984]

I created a dataset using two existing Power BI datasets using the composite model Direct Query functionality. I then in turn added a new table in one of the two "parent" models. However, when I refreshed the composite model, it didn't pull in this new table automatically.

Th existing table that was added through Power Desktop is defined as a "Partition (DQ over AS)" Object Type and the Expression is "Direct Query to AS - {Dataset Name}"

I tried to add the table in Tabular Editor by Tables > Create > Table, however, the Object Type is "Partition (Legacy - Direct Query)" and I don't see any way to change the type.

Is there a way to update the schema of the composite model to add new tables added from the Direct Query source tables?

[otykier]

Hi @Declan1984
We're currently investigating what options we have for updating a composite model (DQ over AS) from the source. Naturally, we want this process to be as smooth as when updating using Power BI Desktop. But keep in mind that this is still a preview feature on Microsoft's side, so we're hesitant to invest too much in it, until we know the final specs.

Short term, we may provide functionality needed to "manually" add/edit DQ over AS tables (i.e. by providing the Entity Partition Type, in addition to the Legacy and M partition types that can be used in Tabular Editor today).

[Declan1984]

Thanks @otykier

Composite models seem like a must-have feature for Power BI in the enterprise space because it seems like the best(/only?) way to provide model re-usability inside of Power BI service without needing to manually replicate measures across different datasets/models that need to calculate the same values.

Until this short-term solution to manually add/edit DQ over AS tables is incorporated into TE3, do you recommend not even connecting to composite models via XMLA end point? After connecting via XMLA end point, the model can no longer be downloaded and edited via Power BI desktop, so that would effectively freeze the composite model to any future schema/measure changes if we couldn't modify via TE3.

[otykier]

@Declan1984

Until this short-term solution to manually add/edit DQ over AS tables is incorporated into TE3, do you recommend not even connecting to composite models via XMLA end point?

That's a good point. For now, I would say the answer is yes (you can connect, but you should not make any changes to the model - i.e. hit the Save button after connecting).

This recommendation will change once either:

a) TE3 gets a better way to update DQ over AS tables (pending investigation and reply from Microsoft, as we prefer to provide this feature in a supported way), or,
b) The PBI service lets you download datasets that have been modified by external tools through XMLA

[Declan1984]

@otykier thanks for that feedback. For now, I think I will steer clear of composite models. For re-usability of measures, my plan is to maintain C# scripts that can be run against each distinct data set that needs the measures. Whenever measures are added or modified, we'll just run the script against the various datasets from TE3.

[otykier]

Hi @Declan1984
Just an update: We actually do support Entity (DQ over AS) partitions today (the short-term solution I mentioned above). The feature is available for any model with a compatibility level of 1561 or above:

Once such a partition has been added to a table, you must assign the Entity Name or Expression Source properties. I recommend opening an existing composite (DQ over AS) model to see how these values should be assigned.

Note that we cannot yet update the column metadata from such a partition automatically, pending point a) in my reply above.

[MadsVRasmussen]

Any update on this item? I am implementing composite model datasets and think I may have run into this issue. Most of the tables in the datasets are using different Dataflows as their source. Three dimensions tables are separated out as stand alone datasets. The reason for this is to reuse- and ensure uniform logic across all datasets for these dimensions.

In Tabular Editor 3 these three tables are set up as object type Table(DQ over AS). The main issue I am facing is that when I run Update table schema, there are no changes detected on these tables. Any ideas on how to resolve this?

[otykier]

I was actually working on a script to automate table imports from remote datasets, until we have the full feature integrated in TE3. You can find the script here: https://gist.github.com/otykier/cd64d4d89f53499a74f975d2db905d87

I was working on a blog post to accompany the script, but that got deleted due to a glitch on LinkedIn :-( and I haven’t had time to rewrite it, but let me know if you have any questions.

The script is not able to update existing tables, but if you haven’t made local modifications to the DQ over AS tables, simply delete them and run the script to re-import them.

That being said, I would strongly advice against using composite models as a shortcut to obtain confirm dimensions, if that’s the only reason why you’re doing it. You’ll take a huge performance hit. It’s much better to keep these objects synchronized as part of your deployment procedure, for example using Tabular Editor scripts or simply by copy-pasting the tables from your “master” model to all of the models that need the same logic.

[MadsVRasmussen]

@otykier thank you for feedback and for sending over the script! Do you know when this might be integrated to TE3? Composite Models does seem to have its use cases even though you do take a performance hit. A built in connector for Power BI Datasets could also be useful :)

Regarding using a script to do this process. Is there a way to set this up so that a script is reusable across different datasets? I am especially thinking about the part which sets up relationships between tables: Could a script identify which columns to go from and to automatically working with different fact and dimensions tables? Our tables are set up with a naming standard which ensures identical names in for key columns in facts and dimensions.

[mlonsk]

Hi @MadsVRasmussen

It is certainly possible to detect relationships based on naming standards.
You can see an example of doing this here: https://docs.tabulareditor.com/te2/Useful-script-snippets.html#auto-creating-relationships
You would have to build this into the script provided by @otykier.

[edhans]

Is it possible to create a new partition that is DQ to an relational database table (SQL Server) - the classic DQ mode? I've a need to bring in a single DQ table from a dataflow with the Enhanced Compute Engine turned on. This is simple in Power BI Desktop. I just create the connection in PQ, and when it starts to load, it asks if it will be Import or Direct Query.

[otykier]

Just an update on this - I rewrote the article I mentioned above, and it has now been published here: https://www.linkedin.com/pulse/composite-models-tabular-editor-daniel-otykier/

Moreover, Kurt Buhler wrote an excellent article on the benefits and use cases of composite models: https://blog.tabulareditor.com/2023/08/18/composite-models-in-power-bi-and-fabric/

We are still planning to add native support for adding/maintaining remote tables in a composite model, in Tabular Editor 3. Stay tuned!

[Rasenkantenstein]

Hi Daniel,

just here to support this feature. Maintaining DQ over AS through PBI is really cumbersome. Since it's been a year since the last update, is this item still on the roadmap?