Database/dataset to report - best practices

#1

Hi Sam,
Looking for a bit of guidance on best practices regarding how to structure queries, models and reports etc.

We have been developing Power BI reports at our company for a couple of years now and have defaulted to importing tables/views from the database into each Report and building the report as required. This has resulted in the same table/view being imported into several different reports, which is creating a lot of server activity as refreshes are performed (we try to stagger them and only update once overnight where possible).

Recently we have been shifting to creating power bi datasets to reduce server load, but we are now runing into the limitation that a report can only use one dataset (which means many many datasets are requried, some with only subtle differences).

Can you suggest are there any best practices for setting up datasets/data sources?
Or is there a way to build reports that use several datasets?

Thanks in advance!

#2

Have you looked into Dataflows?
Here’s an introduction video: A quick look at Power BI dataflows (at this time it’s no longer in Preview)

#3

As Melissa mentioned I would have a really good look at dataflows here and see if that suits your scenario. It sounds like it does.

I personally haven’t created much material on this but will be looking to do so in the future.

I guess one thing to look at also is why has there been a lot of duplication around querying the same data tables. To me that seems like it could be optimised internally some way.

Maybe I’ve got that wrong that you maybe are all hitting the same database by the sounds of it.

Anyway, if there a way you could collaborate a little bit better and not have to hit the database so much, maybe consolidate the reporting. I know this isn’t always easy.

I think your datasets strategy is pretty good by the sounds of it. I guess where this falls down as a where there is slightly different requirements, even minor ones like you mentioned.

I guess the only way improve things is to look to refine as much as possible by only incorporating key tables as those datasets and then just being more efficient around who and when the database is being hit.

This is really all I have for you now I think. Hopefully this given you some ideas.

Sam