Power BI Datamart vs Dataflow vs Dataset

[@thomasmcneilly1138]

Thank you Reza good explanations. I will now look at detailed vids in turn. So a BI datamart contains 3 parts: DataFlow(data/transform) -> Database(db model) -> DataSet(model). It gives auto provision of database storage (Azure SQL Db), and typically Dataset would access this in DirectQuery mode. In storage terms we then have file(BLOB)->db->file(BLOB). This opens up new possibilities, as you can now carry out some additional work in the DB layer (choose between DB vs Dataset). If not Datamart, and not direct query mode, then it is DataFlow(memory) -> DataSet(memory).

[@Brunob007]

HI, thank you so much for this video! I'mi n process of gaining some more technical knowledge on BI and this was extremely clear and informative, wish you all the best :)

[@josephgrane7917]

Very well-done explanation with good 'detailing' links. This makes the decision processes must easier. However, the one big question surrounds the ultimate licensing and resource impact of using the Datamart dynamic SQL server. While we are currently in preview, there hasn't been much talk about the impact to licensing, storage and resources. Do you have any insight as to whether this should be a consideration before diving too deeply into the Data Mart world? The unified world is attractive, but the SQL addition may not be really needed in all environments. Finally, how does the DataMart created dataset compare in speed to a native import dataset?

[@danhorus]

Between these three, I just use datasets. Dataflows force you to use the Commom Data Model data format, which I don't like; and Datamarts force you to use a DirectQuery dataset on top of a regular SQL Server, which is suboptimal for large data volumes

[@abhishekstatus_7]

Very well explanation Reza! We are still using Datasets...

[@MrSparkefrostie]

Question...I want to give access to a user with excel, I need to limit the out put to 3 months. It seems my best option is to setup my dataflow, set up a dataset or another dataflow and give access to that is theis the best ir is there a better approach. The full dataflow is too large for their needs but suits mine.

[@joelgagnon1]

Datamart is great! Do you think it will ever get GA?

[@oscarcampos3054]

I can see the power of datamarts, but there are a few good features you lose when building reports. Like filter parameters. Have you worked out a way to use filter parameters whilst connecting to datamarts? Apparently, filter parameters do not support live connections. Any help would be appreciated 😊

[@mrnimbus2556]

Hello, my datasource is excel everyday i upload the excel file to power bi in order to show the latest data in the dashboard. Is it possible in dataflows and how? Thanks!

[@wojciechjaniszewski9086]

Hi Reza, Thanks for a great content, can we sink data from DataFlow into Datamart directly or we still need to put them in data lake or dataverse first and Datamart would pull it from there?

[@hamedrismanian6056]

Hello, Thanks a lot for all your videos! Is it possible to use Gateway in Datamart? Specifically, I need to pull data from Jira (on-premises) into Power BI Datamart.

[@RADACAD]

Yes, absolutely. your data source can be on-prem, and you can use a gateway to connect to it.

[@ruanduplessis5963]

Hi Reza, thank you for the great explanation! Reza, you briefly touched on this in this video but is it possible to use a SQL query (after the point of importing your data from a source like Salesforce) to produce a new table which can be used/referenced when building visuals?

[@amirbashir7680]

How would you handle a situation where different databases need to run the same Power BI report. The report delivered is not the final one and would require changes from time to time. would you recommend import or Direct query or liver connections. Thanks in advance for your time on this.

[@RADACAD]

I would certainly always recommend IMPORT option first, to build your dataset, and then thin reports with live connection to it. you can then modify your dataset anytime you want.

[@czowiekktoryzaduzowidzia5612]

Great video. Can we in datamarts use SQL functions such as insert, update, merge. In general I want to support SCD2.

[@RADACAD]

the actual SQL Server tables won't be exposed to you. you will interact with Views instead. So I think update and insert won't be an option for now.

[@datasapien]

Nice content

[@Vaultdorr]

Ok, so I still don't understand exactly when datamart should be used. It seems like all it can do can already be done in datasets? You provide examples when to use datasets over datamart, but not the other way around.

[@MsNRodrigues]

Exactly my question. Still not able to understand why I should go for Datamart if I can very well do all of these things in Dataset.