Power BI Datamart vs Dataflow vs Dataset

Datamart, Dataflow, and Dataset are all Power BI components dealing with the data. I have presented about these a lot, and one of the questions I get is: What is the difference between Dataflow, Dataset, and Datamart? So I thought to explain it in a video and help everyone in that understanding. In this video, you will learn the differences between these three components, when and where you use each, and how they work together besides other components of Power BI.
*******************

Want to learn about Power BI or AI? or take your Power BI and AI knowledge to the next level for building reports? Check out our courses in RADACAD Academy for all aspects of Power BI and AI.

*******************

********
CONNECT with US!
********

********

#PowerBI #PowerBIfromRookieToRockStar #PowerQuery #DAX #Visualization #RADACAD

[thomasmcneilly]

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).

[Brunob]

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

[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

[josephgrane]

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?

[abhishekstatus_]

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

[hamedrismanian]

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.

[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.

[oscarcampos]

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 😊

[ruanduplessis]

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?

[wojciechjaniszewski]

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?

[joelgagnon]

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

[mrnimbus]

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!

[czowiekktoryzaduzowidzia]

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

[amirbashir]

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.

[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.