Best Practice for Your Power BI Data Sources

We all know there are lots of data sources; flat files like Excel files, CSVs that you import from another data source, connecting directly to a SQL Server or to SharePoint, or using a data warehouse architecture, as well as many others. The question is, are you using too many data sources with Power BI?

One problem we see as consultants is some companies use far too many data sources. Let’s say a company is connecting to a couple different tables off of SQL Server, they’ve got on view on SQL Server and they’re connecting directly to a SharePoint list to pull in some data and exporting files from Excel files; you get the picture, right?

This can be problematic when trying to put all these different data sources into one report. If you have multiple people creating reports based on a hodgepodge of different data sources, there may be some very different types of filtering that gets done, along with different types of DAX or M code that gets used. This can cause serious ‘one version of the truth’ problems.

I suggest a best practice is to have a long-term goal – this could be months or even a year or more – for getting your data sources in one place. This long term goal can be things such as connecting to a data warehouse or Tabular model or getting a multi-dimensional cube spun up; something that contains all your data and data sources in one spot.

That one spot is where you:

• Apply all your changes to these sources like any kind of DAX calculation that anyone would need is already there.
• Any kind of relationship that needs to be built, active or inactive, between any table is already set up.
• Ways to navigate through relationships that are inactive using certain calculations, DAX or others, are ready.

Doing this will give you one version of the truth throughout your company. Also, you will be connecting to one single data source when connecting to these via live connection. It will help prevent users from having access to data that others don’t (assuming they are supposed to have access).

In short, set your company up with a long-term goal for a type of data warehouse platform. Any person that creates reports will be able to connect to this and know they are getting one version of the truth, so they can create the reports they want with confidence.

If you want to learn more about data warehousing or how to set up a long-term goal for your data sources or anything about Power BI or Azure, we can help. Our experts can give you the guidance you need, help you create a plan and help you with implementation. Contact us or click the link below and start a conversation today.