Creating a Power BI Dataset from a Datamart: A Comprehensive Guide - Intermediate

If you have a datamart containing your organization's data, you might wonder how to turn that data into actionable insights. In this video, we will show you how to create a Power BI dataset from a datamart, which will allow you to analyze and visualize your data in a way that is both intuitive and interactive.
We will cover the necessary steps, including connecting to your datamart, defining your tables and relationships, and transforming your data into a format optimized for reporting and analysis. We will also provide tips and best practices for creating a robust and scalable dataset that can handle large volumes of data and complex queries.
Whether a business analyst or a data scientist, this tutorial will provide the knowledge and skills you need to create a Power BI dataset from a datamart. With a Power BI dataset, you can create interactive reports and dashboards that provide insights into your data, helping you make better decisions and drive business outcomes.
So if you want to learn how to create a Power BI dataset from a datamart, be sure to watch this video and start unlocking the full potential of your data!
Music Licensing Rights - ASLC-1F781895-FE1C88CCF2

[@noahhadro8213]

Isn’t the data already imported? When you schedule the refresh does it have to move data from the data mart to the dataset? I’m hoping I can build one data mart and import everything one time and not have to import it again into different datasets. Ultimate I need 4 different datasets. But they all use a majority of the same tables. I don’t want to refresh 4 datasets when I’m just importing the same data 4 times.

[@nickytan3460]

Measures created in the datamart won't be available in the power bi desktop if connect via import mode.

[@juniorholder1230]

Great educational videos! So if im understanding this properly.. 1. Dataflow pulls data from source. Scheduled refresh requires 2. Datamart pulls from DFs. Scheduled refresh also required. 3. Dataset pulls from DM. Scheduled refresh again required. The anoubt of sequentially required refreshes is odd to me? Does MS not have a live connect to DFs that we can just build DAX and model on top?

[@ChrisWagnerDatagod]

They do NOW... lol with Microsoft Fabric, the new DataFlow Gen2 to OneLake will allow for DQ to those files. Tech always evolves. What is not right today, will get fixed tomorrow.

[@jidi10]

Great video! Would this function the same if using RLS and publishing the Dataset to a different workspace? It could be a great way to stop duplicating datasets/dataflows when you want users to be able to build off of a few tables, but need to keep RLS intact.

[@ChrisWagnerDatagod]

Mostly Yes and a little No. Yes - You can define RLS in this dataset, source the dynamic filters from the same table and on the same load pattern and persist the RLS across multiple datasets in multiple workspaces. This is in alignment with the BEST way today for managing RLS across many datasets . No - The RLS defined in the datamart and default dataset will not automatically flow into these downstream datasets. This is a brand-new piece of integration that speaks of an amazing future, but one that does not work on mini-models today. You will have to create the RLS definition in each one and persist through the tables and roles in the mini models.

[@imax8307]

Great video. Worked like a treat to begin but now something freaky. I'm getting refresh fails on the dataset due to duplicate values but there's no duplicates in my source, dataflows or datamart. Any thoughts?

[@ChrisWagnerDatagod]

That's really odd. Have you tried creating a new one using this code and looking through the data?

[@imax8307]

@@ChrisWagnerDatagod Thanks for the reply. In the end I added a step to the dataflow transformation to remove duplicates and that worked. This lead to an investigation of our Oracle db data as the field in question is a unique ID yet somehow we had one duplicated ID. All sorted now thanks. nice to know it wasn't anything with BI.

[@joelluis4938]

Hi So If I want to share the same datamodel to other college then I can uses shared datasets but being aware that my collegue won't be able to use power query inside of it because I'm the owner of the model. But if I want to give them access to all the transform features of power query because the user want to add extra tables or only use some tables or maybe apply some extra transformations, then I should be using a datamart? My assumption is fine? Because I'm still trying to see the difference between lot of tables inside of a dataset and datamart and it seems the same for me lol

[@bkundrat]

Nice! Can you optionally use TE3 to source the data from the Datamart SQL endpoint?

[@ChrisWagnerDatagod]

You sure can!!!

[@bkundrat]

@@ChrisWagnerDatagod Excellent. I might have some questions regarding performance you in the future as we progress on our end. Helpful video series.

[@VikasTechBytes]

Q: I have created a Central DataMart and pulled data from SQL server. After that I created some measures in that DataMart. Now I am creating a new DataMart based on previous DataMart, like pulling the data from other DataMart. In this process I am getting the table and all things but not the measures that I have created. Do you know how I can centrally store measures and pull that when needed? Please answer this question, it would be a great help. Thank you!

[@ChrisWagnerDatagod]

Oh my, please do not chain datamarts together. Look at building the semantic model to work for both sets of consumers. If you need to use RLS or OLS, do that to lock down content.

[@nehap561]

How is it different from a Dataset?

[@ChrisWagnerDatagod]

Great Question. The Datamart comes with an in-built Dataset with few options to modify. Creating a custom Dataset allows you full control over what tables, joins, measures, etc that you include in the model.

[@Golden-Horse]

I can't see Data Hub option in my PBI desktop

[@ChrisWagnerDatagod]

Update to the latest month of PBI desktop