Datamart: Toggling Storage Mode (From DirectQuery to Import)

Hi, I'm a little late to the party, but wanted to share my experience:

TL;DR - set up a scheduled refresh dataflow that has all the tables for your datamart (so is imported), then point your datamart at the dataflow - so it's directquery, but at an imported source.

I wanted to connect to the same database several times with different credentials (a 3rd party server, - they used credentials to limit sharing of data, and I needed to combine data from several departments, e.g finance_reportingaccount, sales_reportingaccount, hr_reportingaccount).

However, different credentials for the same datasource aren't allowed in the Power BI service.

So I thought I'd set up multiple datamarts, each individually connecting to the same server with different credentials. This almost worked - I could query the combined datamart in both excel, and the built in datamart sql query tool, however in the datamart tables area, and when connecting from a report, it gave the error Expression.Error: The key didn't match any rows in the table

In the end I realised that my report was direct querying daisy-chaining all the way to the 3rd party server, and hence causing an error because it was trying to connect to the same database using different credentials.

The scheduled refresh function in the Datamart seemed to do sweet fanny adams, and I can only assume the fact I could query the tables fine using the built in SQL query tool was either due to some fortuitous caching, or simply that this bog-standard functionality accidentally slipped through without Microsoft realising they'd missed another oppurtunity to shoot themselves in the foot!

I then ran into DarylK_MA's issue, as if I could switch to import, then I my combined datamart wouldn't be connecting to the source 3rd part database - problem solved! Unfortunately not.

However, I found a solution in setting up a dataflow for each credential. Then, after setting up a scheduled refresh to import the data, I could appended the tables together into a final combined datamart.

All in all, datamarts have the potential to be so useful, and futhermore appear to get around a lot of the issues with powerquery steps that are non-compatible with direct query (I tried to force the datamarts into import mode by appending tables, but it seems to work fine!) However, as usual, Microsoft get soooo close to making a really useful and intuitive product, then snatch defeat from the jaws of victory!