relationship - direct query / import mode

[Anonymous]

May I ask is there any way I can achieve the below, without using limited relationship,

Fact table: direct query or dual mode

Dim table: import mode

Thanks

[Anonymous]

@speedramps @SpartaBI, thanks for your reply

I found that, in this case, regular relationship only apply to when

Fact table: Import

Dim table: Dual mode

(* : 1)

Im seeking a way to achieve the opposite because I want to import a large Dimtable connect to my fact table(direct mode). Thanks

[SpartaBI]

@Anonymous 
Why don't make the dim Dual? If the fact is dual and you query something on both of them, than better if the dim will also be Dual. In this scenario you will have a regular relationship between both of them. WDYT?

[Anonymous]

because I need the real-time data from Fact table(Direct Query)

old data from a Dim table, which is quite huge.

If I make the Fact table as Direct Query and Dim table as Dual mode, it seems that everytime when I update the report, the data from Dim table is linking to real-time data, which cannot help the performance. So i am trying to see if any way I can fetch only the import data from Dim table. 

[SpartaBI]

Hmm.. I see your point. Not sure it will solve this but maybe try to do the fact table as hybrid?

[Anonymous]

I tried as well. Intestingly, when Dim table set as incremental, it cannot build a regular relationship to Fact table with Dual mode or Direct query or incremential.

Thanks for your opinion anyways!

[SpartaBI]

My pleasure. It's an intersting use case. 
I meant that the FACT will be incremental with Hubrid option and DIM not but in Dual

[Anonymous]

Yes, this is possible. unfortunately, historical data ran a bit faster but the real time data run slower.

However, the Dim table is the huge piece but seems cannot make it as import in this case.

[SpartaBI]

If Dim = Import and Fact = Hybrid won't work for that, than I think all is left is to try to put both in Direct and improve as much as possible the queries that run to the source. At least you will have a regular relationship. Also , In case you are positive that there are no rows in fact that are missing in dim you can check the "Assume Referal Integrity" in the relationship dialog box which will create INNER JOINS and not Left Outer Joins in the source which will improve the performance.

[Anonymous]

Yes, I think put them both as direct query may be the only possible way to do that at this point.

Besides, I am thinking that for direct query model, would it be better if I could do all the query/transformation in the database before fetching to BI, which I have never tried before.

I am wondering the possibilty to create a view in database using some ETL tools.

Pleaseadvise if you have any experience on this

[SpartaBI · Accepted solution]

In case we are doomed to use DQ and not the Vertipaq than pushing as much as possible to the source in DQ is always the best option, and optimize everything you can there. In DQ you don't use the Vertipaq storage engine of Power BI rather the storage engine of the data source, and in Power BI you only use the FE, which is 1 threaded, so best to go and optimize whatever you can in the data source (queries, indexes etc) and just let the Power BI be the visualization layer.
Hope that helps.

[SpartaBI]

If one table is in Dual and one is in Import - and of course the granularity is not m:m - than both relatioships exist simultaneously and the engine will use the one he fits best. It's reasonable to assume that if you will run a query that only consist of table that has a version in Import (Dual or Import) it will use the regular relationship. The only reason I'm not saying 100% is becasue there is no documentation on that. You can test your specific scenario and queries to verify.
Hope that answered your question.

[speedramps]

Hi alexng

Yes, it is called a composite model

See https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models 

I am a unpaid Power BI volunteer. Please click the thumbs up if you like me trying to helping you and click solved if it fixes your pronlem.