cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TomBI
Regular Visitor

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

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!

1 ACCEPTED SOLUTION
Tutu_in_YYC
Super User
Super User

  • 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!

View solution in original post

4 REPLIES 4
Tutu_in_YYC
Super User
Super User

  • 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
Super User
Super User

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.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

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

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

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors