Dataflow & Direct Query to Source

[TSHECKEL]

Does anyone happen to know if a table within a dataflow can be configured as a direct query to the data source versus import mode? The reason I ask as we're trying to maintain regular (strong) connections in a mixed / composite model dataset. Thanks!

[TSHECKEL · Accepted solution]

The initial  ask was round if a data flow object could be configured as a direct query to the source content. From what I surmise, I don't believe this is possible. 

[TSHECKEL]

1:N with the 1 (dual) filtering the N (direct query).

[lbendlin]

Dataflows are glorified CSV files. They have no concept of "direct query against a data source".  You are already using composite semantic models - there you can implement your Direct Query components.

(Don't get me started on "Direct Query against dataflows" (aka Datamarts).  I can't even. )

[TSHECKEL]

Thanks. Yeah, that's our issue ... the dataset composite model we have contains DQ queries to a data source and additional "dual" connections to the dataflow. However, the use of dual through the dataflow results in weak or limited connections with the existing DQ queries. That's not the case if one omits the dataflow and uses dual to the other data sources. For some reason, data flow and dual connections result in limited connections. The thought was to get around that by possibly configuring the DQ connections through a dataflow as well. 

[lbendlin]

For some reason, data flow and dual connections result in limited connections. 

What problem are you trying to solve?

[TSHECKEL]

We're trying to maitain a dataset that contains strong / regular connections between all objects. Our dataset is composite and contains both dual and direct queries. This works outside of a dataflow. However, if we source the dual objects from a dataflow, the connectons then become limited / weak.  

We do not want to import the DQ content into a dataflow due to the size of it. However, if a dataflow could be configured as a DQ (datflow to external db), this may allow us to establish a regular / strong connections again with the dual objects. 

[lbendlin]

Meh?  What's the cardinality of the join columns?

[TSHECKEL]

1:N with the 1 (dual) filtering the N (direct query).

[lbendlin]

I was asking about the cardinality on the "1"  side.

[TSHECKEL]

The dual side is a "single" filter to the direct query and a 1 to many. Sorry, my example diagram could be intepreted as "both" but it is not. Thanks!

[lbendlin]

"cardinality"  means - how many distinct values do you have on the 1 side of the relationship?  Recommendation is to keep that below 50K.

[TSHECKEL]

The dimensional one side is unique across all records, but they can exceed millions of records. The model maintains a regular or strong relations between both the DQ and Dual queries / objects when they both source from an external data source. However, if we point the Dual objects to a dataflow, the relationships now become limited or weak. Thanks 

[lbendlin]

I recommend you watch the query size. That high cardinality is a much bigger problem.

[TSHECKEL]

We have sales across millions of items and, at times, we need the ablitilty to look at the most granular item level. Unfortunately, we can't reduce the cardinality in this case. Thanks for your help.