How does your org handle data centralization so Power BI can pull everything it needs?

[rainynights]

70% of my company's data is in an AWS MySQL RDS. The data here I can connect to easily enough via an ODBC connector, and setting up the gateway up for this is straightforward too.

The problem is the rest of the data that's scatter all over other locations.

We have some XML/CSV files on S3 buckets on AWS that get put in there daily, which my boss wants me to start looking at. And then there's also data from google spreadsheets (surprisingly enough there's no straightforward to refresh data from private google sheets), and also data from various other places like CRMs and whatnot.

I'm at a bit of a loss as to how to go about even starting to look into hooking power BI into these data sources. It's left me feeling a bit like a one-trick pony because this whole time, I've only been getting data from one primary source.

Curious about how other people handle situations like this in their orgs!

[rainynights]

Thanks a lot for the insight 🙂

[v-chuncz-msft]

@rainynights 

By the way, you may share feedback via Ideas to help improve Power BI.

[Tad17 · Accepted solution]

Hey @rainynights 

I am similarly struggling with this now. The easy answer is that there is no easy answer. Querying all of the data into one Power BI dataset is what Power BI was designed for in theory, but reality is often more complicated than the tutorials. 

In reality the concept of garbage in, garbage out applies here. At my company they are currently working on replacing a few systems with one master system which will fix some, but not all of my more complex issues currently. 

One thought on the google drive and excel/csv files is getting your company to use microsoft OneDrive for shared files and file storage. This allows you to actively query the files while still allowing people to update the files. If they are constantly creating new files you can look into getting them to use sharepoint sites so you can query all of the files in a sharepoint site/folder which will automatically update to include new files. 

As for CRM, depends on what CRM software you are using. If it is microsoft's that's easy enough. Otherwise you will have to look up connectors or other ways to query the data (export to excel into OneDrive/Sharepoint works).

Biggest issue I see you facing with any of this is possible duplication of data.

You're not a one trick pony. Your workplace is just innefficiently trying to straddle and ride too many horses all at once. To keep with the analogy, Power BI should be the carriage connected to all of the horses, but if the horses are all different sizes then the analogy really falls apart. Trying to find ways to consolidate your company's data tracking and reporting is going to be the first, gruelling, painful step.