topic Re: DirectQuery + composite model : What can be done ? in DAX Commands and Tips

DirectQuery + composite model : What can be done ?

jct999 — Sun, 28 Nov 2021 19:35:52 GMT

Hello

I'm going to build the following composite data model:

From 2 fact tables:

Fact_Table_1: Storage mode = DirectQuery, more than 10M rows
Fact_Table_2: Storage mode = Import, 1K lines

Create a GLOBAL_FACT_TABLE table which is the UNION of Fact_Table_1 and Fact_Table_2.
(Fact_Table_1 and Fact_Table_2 have the same columns and data types)

Then set up a star schema based on GLOBAL_FACT_TABLE with 4 dimension tables around: Dimension_Table_1
, Dimension_Table_2, Dimension_Table_3, Date_table.

Finally, create measures based on GLOBAL_FACT_TABLE and the 4 dimension tables.

Here is the described schema:

Is it possible ? I mean, is the DirectQuery + Composite model as magical as we could expect: can we make full use of some measures based on GLOBAL_FACT_TABLE without worrying about what's behind it to fetch the data from Fact_Table_1 and Fact_Table_2 ?

If not, what are the limitations?

Thanks and Regards

Re: DirectQuery + composite model : What can be done ?

lbendlin — Sun, 28 Nov 2021 22:10:08 GMT

When you do the UNION you are creating a new calculated table in memory, which will not be very different from having both of your fact sources in Import Mode. Not sure that is even possible.

Have you considered using Aggregations instead?

Re: DirectQuery + composite model : What can be done ?

AlexisOlson — Mon, 29 Nov 2021 05:06:01 GMT

I think lbendlin is correct. You can't union the tables without loading Fact_Table_1 into your model.

One possible solution is to have both fact tables hooked up to the same dimension tables and define your base measures like this

SumSales = SUM ( Fact_Table_1[Sales] ) + SUM ( Fact_Table_2[Sales] )

This abstracts away the separation for any higher-level measures that depend on such base measures.