Datamart Capabilities

[smpa01]

Datamart is awesome, to say the least, and I intend to take this on a test drive and eventually convert it into a prod element. I have a massive prod use case for which there is no better solution than datamart.

Before I take this on a spin, I am looking for a few answers.

A. For data ingestion into datamart - Do we have Incremental Refresh? (If not currently available, is it on cards?)  It is currently available and this is simply mindblowing.

B.  What is the purpose of being able to write SQL queries on the SQL console currently available in DM?

Are we able to write SQL queries and ingest the transformed table back into the same DM?

As far as my understanding, we can access the Azure SQL server (the DM is connected to) and write fully qualified SQL queries and create dataflow/dataset as we please.

But I simply have not understood what can we do with the SQL query written on the SQL console in DM. What can we do with the query result?

C. Who can access the SQL sever that each DM is connected to? Are all the PBI admins of the workspace can access the SQL server? If yes, how can I restrict access of admins to the server and prevent the inefficient SQL writers off that particular server?

D. Is it possible to pick and choose POWER BI admins (I don't want any other admins to mess around with prod datamart's query ingestion) to provide access to a particular DM?

E. I have also seen that DM has data modelling capacity- does it mean that we can access the same db from a SSAS server (if not now, later- that would be simply mindblowing)?

F. How do I delete added tables from DM? I am currently getting this error when I try delete one of the previously added tables from DM. How can I seamlessly do it without wreaking any havoc? I don't want to find myself in a position where I need to delete a DM in order to delete a particular table within a DM

DatamartUserWorkloadError
ErrorMessage
User workload error
Param0
Capacity operation failed with error code DmsObservableUniverseHasChanged

Thank you in advance.

@TomMartens 

[smpa01]

Thanks @TomMartens 

D. Is it possible to pick and choose POWER BI admins (I don't want any other admins to mess around with prod datamart's query ingestion) to provide access to a particular DM?

D
Unfortunately not, this is the reason why we turned the feature off. I'm not sure if a feature will be added of the course of time approaching GA that allows a more fine-grained control by adding security groups.

But I saw that roles can be defined for RLS. Does it not restict access of other admins?

Can someone from Microsoft please shed some light on

F. How do I delete added tables from DM?

[TomMartens]

Hey @smpa01 ,

here are my takes on your question, please be aware that I'm not MSFT (no matter how much I'm in love with Power BI and the whole data platform).

I change the order a little bit

A

Absolutely agree!

D
Unfortunately not, this is the reason why we turned the feature off. I'm not sure if a feature will be added of the course of time approaching GA that allows a more fine-grained control by adding security groups.

B
At the current moment you just can have look, but my assumption is that over the course of time DDL capabilities will be added, e.g., allowing you to create views on top of the tables and other you can create using DDL.

C

To be honest, I can't answer your question, as I'm currently testing in my private tenant, but will certainly test all this in the next couple of week. My current guess, every admin of the workspace is able to access the SQL DB, not sure about the ability to create dataflows to create tables.

E

I don't think so. At the current moment you can only create a base model, meaning no Calculation Groups, perspectives etc. Nevertheless, you can create a composite model based on the datamart dataset and add the features. I already tested that and it works as expected. I also like the idea of having one core dataset, and one or multiple composite models that use the core dataset as a foundation.

F

I have no idea 🙂

Hopefully, this helps you somehow.

Regards,

Tom