best practice for combination of datasets and other sources

[Anonymous]

Hi there, 
I am wondering, how to best combine different data sources:

- two PBI Datasets
- Sharepoint Lists

- data from external SQL via Gateway

In desktop I can create a composite model, but how to best configure all this in the service? can I create a new dataset based on these sources? or a dataflow?

Thanks in advance

[Anonymous]

Thanks for your reply.
I agree, that a DWH would be best, but it seems like a bit of an overkill to create this for reporting a bunch of KPIs from different systems.
Regarding Datamarts I thought that these were still in preview, weren't they?

[ibarrau · Accepted solution]

Well, if the plan is a continious data development of many projects, then it's not overkill. If this is a only one time requirement, then it might be.

Yes they are. Datamarts are still in preview.

You also have Fabric, that's also in preview.

I guess what I don't like about dataflows is that you can only get their data with PowerBi Desktop. You can't query with other tool like the others.

I hope that make sense.

[ibarrau]

Hi. As best best practice, I would say that you need a place to build a single source of truth. I'm talking about a warehouse, lakehouse o even a database. A place where you store all the data that will be used for analytics purpose. In there you can transform and create models (facts and dimensions for star schemas). Then you can connect power bi datasets to tables you need for you model. That's the best practice. Using composite model that gets direct query from two datasets and import sharepoint list will work, sure, but it's maintainability and performance can be tricky. 

As an alternative you can use PowerBi Datamart for building the single place of truth, or even dataflows if you only have a pro license. The idea is keeping all analytics tables in a single place and avoid datasets connecting other datasets. A good practice is having datasets taking data from a single place that stores the analytics data.

You should take a look at Fabric, the new Microsoft Product for Data projects. It might help building a lakehouses as a single place for data analytics.

I hope that make sense.