Power BI Dataflow and Power BI Datamart Best Practices

A detailed walkthrough of the best practices of creating a Power BI Dataflow and loading a Power BI Datamart.

Music license - ASLC-1F781895-FE1C88CCF2

[HachiAdachi]

Love learning best practices by other PBIers, especially from a data god! 👍
Q: Would you recommend against loading multiple tables into a single dataflow? Thought It might make sense to do so if the tables are refreshed simultaneously... I think scheduling refreshes and loading to datamarts/sets would be more efficient, but would love to hear your counterpoints. Thanks, Chris!! 😊

[carltonseymour]

A second question. With dataflows would one still require Analysis Services?

[osamaabd-elmohsen]

Q :
I have created datamart and in this datamart I am calling dataflow from power apps dataflow, the dataflow is in direct query Mode in the datamart and I can not change it to import Mode.
Could you help me
Regards

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

[tiagoa]

Is it possible to use Python in the dataflow to transform data? Thanks

[AVielot]

Thanks for the great explanation, providing all the steps. Currently using datamarts that we connect to Sharepoint folders to then create our PBI direct Query reporting dashboards. Will see if we can / should leverage this process and how beneficial it is!

[mayankmahajan]

I have a dataflow that is not under my ownership but I want to use that dataflow to get a filter in my dashboard. But When I am loading the dataflow into the DataMart and doing few changes in the dataflow inside the DataMart it never loads whereas if I load it without any changes it happens smoothly. (PS: it have even when I load dataset those are under my ownership.) Do you have any solution for this.

[imax]

I wish I'd seen this earlier. Been working on my first Datamart and adding tables within it. Every time I make a change to a table or the incremental refresh details I have to wait for it to save and refresh all the tables. Going to switch to this method for the reasons you've explained here. Thanks for the vid 👍

[myusrn]

I needed a story to compute some metrics [ e.g. count of records with foo, bar and foobar present ] on the 1st and middle of every month. I found that i could use power bi dataflow legacy to schedule execution of sql statement that produced a single record with each of those count based metrics in a column that it stuffed into an azure data lake storage gen2 container entry. Then i was able to use power bi data source connector support for azure data lake storage gen2 container to pull in the set of all those individual 1 record files and create a trend lines chart for all thos metrics as the months ticked by. Now with power bi data factor dataflows gen2 solution they no longer rely on an azure data lake storage gen2 container so this maneuver to get at all the scheduled metric computations as a data set isn't going to work. Any insights as to how i should be accomplishing this metrics computation and display of the set over time in current power bi experience?

[rameshpaskarathas]

Great video Chris. A question on loading the data to the workspace first as dataflows and then loading those dataflows inside Datamart. Wouldn't this create two copies of the same data (in the dataflow and its underlying storage as well as the SQL backed Datamart storage)? From a data management and security perspective, do you see potential issues with seeing copies of data in multiple places?

[bkundrat]

Excellent video and series Chris!

Question, have you used pipelines to land dimensional dataflows into Dev/UAT/Prod staging areas to allow you to build multiple Data Marts where those Data Marts load the dimensions that they require?

[lindseyrosin]

This is great, thank you! We're doing some testing using dataflows and datamarts and this was very helpful!

[indranilbanerjee]

Chrish I have one question.. Did you were in Norwestern Mutual Fund during the year 2018? Excuse me if I'm wrong..

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

[alejandrogiraldo]

I used datamart to build a dashboard but i noticed that dashboard takes more time to load elements compared with an import model, did i do something wrong?

[carltonseymour]

If in a small organization there is a single data analyst publish reports, would the old method of creating reports using Power BI Desktop be better than using dataflows. I guess once you have teams this is where dataflows shine. Awesome explanation. Thank you

[Scorpiank]

👍. Hi chris,
I have 2 pbi dataflows (1- active data, 2- hsitory data) & I want to append those into a single table & get that into the report using a live connection to the model. Anyway?
Note: Refresh will happen at dataflow level, no extra refreshes. I don’t want to make a additional refresh on top of that dataflows.

[SameeK]

how would you centralise measures for report building?

[chrismartin]

This was so helpful- thank you so much!

[christaulbee]

I'm just here for the logarithms.