Skip to main content
Microsoft Idea

Power BI

Under Review

Power BI Datamarts need DDL / DML Support

Vote (149) Share
Christopher Wagner's profile image

Christopher Wagner on 02 Jun 2022 12:54:28

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.

Administrator on 31 Jan 2023 22:32:06

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! :)

Comments (8)
Christopher Wagner's profile image Profile Picture

Chris Burgess | ACTGOV on 10 Aug 2023 03:50:24

RE: Power BI Datamarts need DDL / DML Support

Thinking out loud


Slowly Changing Datasets - lets build up our history!!

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


Cheers

Chris

Christopher Wagner's profile image Profile Picture

Theo Kontos on 18 Mar 2023 05:40:02

RE: Power BI Datamarts need DDL / DML Support

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.

Christopher Wagner's profile image Profile Picture

Theo Kontos on 17 Mar 2023 15:04:18

RE: Power BI Datamarts need DDL / DML Support

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.

Christopher Wagner's profile image Profile Picture

F van Bree on 09 Feb 2023 15:05:39

RE: Power BI Datamarts need DDL / DML Support

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.

Christopher Wagner's profile image Profile Picture

R RR on 23 Aug 2022 18:40:57

RE: Power BI Datamarts need DDL / DML Support

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.

Christopher Wagner's profile image Profile Picture

on 14 Jul 2022 03:47:27

RE: Power BI Datamarts need DDL / DML Support

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/

Christopher Wagner's profile image Profile Picture

Jason Grundahl on 06 Jun 2022 20:08:34

RE: Power BI Datamarts need DDL / DML Support

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


I needed to look them up.

Christopher Wagner's profile image Profile Picture

Ashraf Ghonaim on 05 Jun 2022 05:27:51

RE: Power BI Datamarts need DDL / DML Support

Also the ability to create materialized views

Merged Idea (1)