Need Advice: Import Mode versus Direct Query (or composite) for Small Enterprise reporting

[Anonymous]

Hello Community - I am in a debate with our sysadmin as to which way we should go for our enterprise reporting initiative.  

I do the reporting in Power Bi and we do have a lot of custom calculations that include the use of cross-table lookups, calculated tables, etc  (which I beleive Direct Query cannot accomadate).   

Also, our data only consists of about 150k rows (or less).   

We want to create golden datasets for each major department (supply chain, quality, marketing, etc).  I use dataflows to create datasets and I use import mode, and I make sure that the same data modeling (calculated columns, measures) are present in each data model.   For example, two different datasets may both contain the orders, shipments, and customers table.   I make sure that both datasets contain exactly structure and calculations.    If I change in something in one, I make sure to change the other.  

His opinion is to use direct query and use the same direct query dataset across multiple reports.   I do see the convenience of this, but in my mind, aside from the limiations that exist, there will also be times when modificaitons (add a new column for example) will be necassary in one dataset serving Marketing....but will have no relevance to the dataset that the Quality team is using.  So why try to create one dateset that tries to please everyone?     Thoughts?

[lbendlin]

"if we had an existing golden dataset and we needed to augment it, we would bring in the new source via import mode"

Who is "we"  in this scenario?  You and the sysadmin, or the report developers? You may want to plan to have some sort of a data stewart role (or team) in your company.

[Anonymous]

The "we" is me.   Mostly.   The sysadmin does not really create reports.   At some point we may have some power users that we allow to create reports in the service, by connecting to a particular dataset.  For example, we would have a "Quality Dept" dataset, and of their team may connect to that for ad hoc reporting needs. 

[lbendlin]

"would connect to those using "live connection".    Try to stay away from direct query.  "

Nice(*) idea but not enforceable. Composite models allow you to bend live connections into direct query with local data model copy.  This is a rather required feature as soon as your report connects to more than one golden data source

* - said in jest.  One of the biggest aspects of what we are doing is the fundamental question "Are we trying to promote the usage of Power BI or are we trying to make it harder for the users?" .  If you are getting in the way of the users'  quest for actionable insights then they will go elsewhere and you have failed.

[Anonymous]

If the "golden datasets" each contained all of the required tables (and calculations and measures) needed for the creation of various reports, all in import mode,  my thinking is that there would be no need for a report to connect to multiple data sets.   And if new information is needed, it can be brought into the core (golden) data set via a dataflow  (in import mode).   

I guess my concern based on what I am reading is that using direct query (with purely dq or in mixed mode) could present with challenges based on the existing DAX code we have in many of our custom business logic formulas, not to mention potential performance issues.  

[lbendlin]

One thing to keep in mind.  There is a difference in cost when you connect via Direct Query to an on-premise data source versus connecting to a Power BI Dataset in the Azure cloud via live/direct query.

So while you are right trying to prevent the former, you should encourage the latter.

NOTE:  Connecting to a Dataflow via Direct Query may be a bad idea. Mandatory reading: DirectQuery and dataflows in Power BI – BI Polar (ssbipolar.com)

[Anonymous]

Thanks @lbendlin     We do not have any on-prem datasources  (everything we'd connect to would be in the cloud, or we'd create a dataflow for it).   I'm definitely not an expert in this stuff, but it seems to me the beauty of dataflows is in their flexibility (you can make them from all types of different sources) and create datasets from them (using import mode exclusively).  

[lbendlin]

Oh, then my initial suggestion on how to talk to your IT guy is a bit moot.  You're not paying by usage, are you?

[Anonymous]

@lbendlin    We are a relatively small company ($150 million), but owned by a much larger company (billions).   We have premium accounts (myself and the sysadmin) and can create premium workspaces.   Ideally any source we would need to connect to  (dataflows, Excel, 3rd party sources, etc) we would connect via APIs and dataflows to "import" the data using import mode.   So, for example, if we had an existing golden dataset and we needed to augment it, we would bring in the new source via import mode.  

[lbendlin]

Tell him about the impact Direct Query has on the source system, with thousands of little queries constantly hitting the databases - versus import mode where you have a limited number of nice spools.  

The "golden data source"  concern is valid but it can be alleviated with shared / promoted / certified datasets.

[Anonymous]

@lbendlin   I think the way to go for us is to try and have all "golden datasets" built using import mode.    Any reports that get built (whether in the service or on desktop) would connect to those using "live connection".    Try to stay away from direct query.  

Changes that a report author may need to make (adding a column or measure that did not prevoiusly exist in the core model) would have to be requested and then I would update the core model (dataset) accordingly, and re-publish it.    Does that make sense?