Datamart: Toggling Storage Mode (From DirectQuery to Import)

[DarylK_MA]

Hello!

I am hoping to get some help from the community relating to the toggling of storage modes in datamarts; it is currently defaulted to DirectQuery but I would like to use it in Import mode. As shown in the screenshot attached, the toggle is blurred out. Not sure if anyone else is facing the same issue? 

For context, I am trying to build a datamart to store some data extracted and transformed from an SQL server. Queries take about a minute or so to resolve and that I do not have access to the server at all times when I am working with the datamart. Thus, DirectQuery is not a practical option. 

Wonder if this is a caveat since Datamart is only in a preview stage or if there is a specific toggle that either my organisational administrator or myself will need to switch on? Will appreciate any advice on this, thanks! 😀

[JConroyNYCC]

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!

[v-binbinyu-msft · Accepted solution]

Hi @DarylK_MA ,

To my knowledge, Datamart doesn't support this function.

Best regards,
Community Support Team_Binbin Yu