Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
DarylK_MA
Regular Visitor

Datamart: Toggling Storage Mode (From DirectQuery to Import)

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? 

 

DarylK_MA_2-1669710786633.png

 

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! 😀

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

Hi @DarylK_MA ,

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

 

Best regards,
Community Support Team_Binbin Yu

 

View solution in original post

2 REPLIES 2
JConroyNYCC
Regular Visitor

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 tableEvaluationError.png

 

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
Community Support
Community Support

Hi @DarylK_MA ,

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

 

Best regards,
Community Support Team_Binbin Yu

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors