What is the advantage of a datamart over a Power BI dataset from a published desktop file

[TomBI]

Hi, 

I've been reading and watching a lot about datasets, dataflows and datamarts, but there is one thing I miss in these overviews that seems like it's maybe the best option to me. When I create a file in Power BI desktop I transform and model data, create measures and ofcourse the report itself. When I publish the report, the dataset gets published automatically.

When I create a new report in Power BI desktop I can connect to any of the datasets that have been published this way, including relationships and measures. This seems like a great option to me since I won't have to recreate the whole model in a datamart. 

• Is this a Power BI Dataset (as it is called in the overview of connectors in desktop) or more like a datamart? As far as I'm concerned you cannot create relationships and measures when you create a dataset in Power BI online (but you can with a datamart)
• Are there any differences that I'm missing other than creating it in desktop vs online? 
• Is it true that whether you choose a dataset, dataflow or datamart, you always work with a direct query by definition? Is there no way to import this data? 

Hope you can help me!

[Tutu_in_YYC · Accepted solution]

• Is this a Power BI Dataset (as it is called in the overview of connectors in desktop) or more like a datamart?
  It is a Power BI dataset. For Power BI Datamart ,when you create a Power BI datamart, it auto-creates a Power BI dataset for you to connect to.
  As far as I'm concerned you cannot create relationships and measures when you create a dataset in Power BI online (but you can with a datamart)
  Correct ( Correct)
  
  
• Are there any differences that I'm missing other than creating it in desktop vs online? 
  When you create online, you can only use Direct Query/Live  ( because you are connected to an existing dataset). In desktop, you have the ability to use different modes Import, Direct Query, Dual (Composite model).
  
  
• Is it true that whether you choose a dataset, dataflow or datamart, you always work with a direct query by definition? Is there no way to import this data?
  No, Power BI Dataset is direct query ( but you can create a separate composite model based on an existing dataset, check this tutorial out  ). Power BI Dataflow can be imported. For Power BI Datamart, you can either import a table or connect to the dataset attached to the datamart

Hope this helps!

[otravers]

Check out this entry I wrote when datamarts were announced, it might clear some of the confusion:

https://www.oliviertravers.com/power-bi-dataset-dataflow-datamart-dataverse-sql-server/

You are correct that datasets can't be modeled online (yet), you need Power BI Desktop.

You are incorrect to assume you always work with Direct Query, you absolutely have the ability to use Import (cached data in the dataset), it's the recommended default for most use cases.

[TomBI]

Hi @otravers, 

Thanks for your reply. Your article was clear but it doesn't answer all my questions.

• You say a dataset can't be modeled, but when I use a dataset from a published desktop report, there ARE relationships. Is this a different kind of dataset then? 
• How do you import a dataset or datamart instead of using direct query? 

Thanks

[otravers]

1. I said a dataset cannot be modeled online, i.e. like you said you need to create relationships in Power BI Desktop then publish to the online service.

2. You can import a datamart via its SQL endpoint.

https://www.oliviertravers.com/power-bi-datamarts-sql-server-desktop-tools-connection/

3. You cannot import a dataset, you can connect directly to it via a Live Connection report:

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-report-lifecycle-datasets