Power BI Datamarts need DDL / DML Support

[CWagner1]

We need the ability to create DDL and DML support in Power BI Datamarts so we can create stored procedures and views within the datamarts. This opens up endless possibilities for building and managing data within an analytics environment.

Sharing data with Apps can be easily done with stored procedures.

Consistent naming conventions, common business logic can be maintained in views.

Really, the list goes on and on.

Please enable this functionality.

Ideal it would be GUI driven for business users, but at a minimum programmatic creation works.

[fbcideas_migusr]

Also the ability to create materialized views

[fbcideas_migusr]

Data Definition Language (DDL) / Data Manipulation Language (DML)

I needed to look them up.

[nishalit]

Currently Datamart don't support the creation of Table function, we have a use case where we want to push some GIS calculation at runtime to datamart using parameter, currently it is not possible

I added a blog for this use case

https://datamonkeysite.com/2022/03/08/first-look-at-dynamic-m-query-parameter-using-sql-server/

[ryan_rakita]

DDL and DML in Datamarts would be a game-changing addition. Right now, our decision on whether or not to use Datamarts on a wide scale depends on whether DDL and DML will be available.

[nishalit]

Thank you all for your votes so far! Would love to understand what's most important to you as far as DML and DDL - and what scenarios you're trying to deliver. Feel free to respond here and let our team know! 🙂

[frans_vanbree]

DML is needed to delete and/or update data. That way, you can create more efficient delta loading mechanisms, without the need to truncate+load the data on each refresh. Incremental load cannot be used here, because it only supports "transactional" data (where each new record can be inserted) and not e.g. updates to existing fact data.

[Theo_S_Kontos]

Just commenting to add that this would be the absolute game changer for Power BI. Even if storage would need to be limited, DML / DDL would be incredible for our team to transform our BI analytics and for our clients. Would create so much more value from our BI due to the streamlining we could do with data sources into Power BI.

[theo_kontos]

We need to be able to do CRUD operations after the Power Query queries...for example, we'd like to be able to insert a weekly file into a table that will retain all the data over time. Rather than combine all files in a SharePoint each week upon Datamart refresh, it'd be more efficient to just insert the latest file each week into a growing historical "master" table.

[fbcideas_migusr]

Thinking out loud

Slowly Changing Datasets - lets build up our history!!

Ability to create/run/save Stored Procs against the DB

Cheers

Chris