When we build a Power BI report, we basically have the 4 pillars that Sam talks about:
- We prepare our model in the Query editor
- We set-up our model by creating relationships between tables
- We create measures with DAX
- We create the visualizations
We transform (sometimes completely raw) data into a stunning visual somewhere in the cloud or on a local machine.
What I want to talk about in this post is the step before we get the data into Power BI. This data can from different systems: an SQL database, an ERP system, flat files (.csv,.xls,…). Let’s talk about SQL and ERP another time, and focus on small different excel files floating around the company.
=> Where do we store these files and how do we make sure that our Power BI Desktop and Power BI service have access to these files?
One possible solution is to store the excel files on a local server. You install Power BI desktop on this server and build your reports there. This poses some minor challenges (which can be solved):
When publishing to the Power BI Service (which I hope you all want to do!) you need to make sure to have a Gateway installed on that server.
When sharing the .pbix through e-mail, you need to make sure that colleague X has access to the same server where your files are stored (it might better not be your home drive!)
Another solution is to use an online service like Azure Data Storage. (Azure is Microsoft its own cloud platform. Given that Power BI is also a Microsoft product, you can expect good integration between the products).
For a very cheap price per month (Note that you can get a free account for a couple of months) - and with cheap I mean really cheap, a couple of cents - you can store your data in a “container” and have your Power BI reports connect to them. No more need for this “annoying” (sorry on-premises lovers!) gateway that shuts down once per couple of months because it needs an update.
If you want to know more, feel free to google for “How to create an Azure storage account” and if you do not want to pay you can check “How to get a free Azure subscription”.
The goal of this post is not to sell you " MS Azure" (I am by no means affiliated), but I just want to give you a taste of the other services that Microsoft has to offer in order to have some complementary tools for your Power BI expertise.