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)
- Merged Idea (1)
RE: Power BI Datamarts need DDL / DML Support
Thinking out loudSlowly Changing Datasets - lets build up our history!!Ability to create/run/save Stored Procs against the DBCheersChris
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.
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.
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.
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.
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 possibleI added a blog for this use casehttps://datamonkeysite.com/2022/03/08/first-look-at-dynamic-m-query-parameter-using-sql-server/
RE: Power BI Datamarts need DDL / DML Support
Data Definition Language (DDL) / Data Manipulation Language (DML)I needed to look them up.
RE: Power BI Datamarts need DDL / DML Support
Also the ability to create materialized views