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.
Thank – terrific post. I ran headlong into this issue earlier this week when I didn’t have an appropriate cloud space in which to host a file needed for the "Where in the World Is Enterprise DNA?" mapping project. I was able to come up with an interesting workaround, but having an Azure space would’ve saved a lot of time and effort. Didn’t realize it was so cheap to establish this capacity. I’m going to try to set this up over the weekend.
Thanks very much for the tip, and for taking the time to share it with the forum.
I’d be interested in the successes/pitfalls both of you may have encountered setting this up.
A step by step for the setup could be very useful for those of us a bit hesitant to go the Azure way.
I truly appreciate your feedback and share your opinion that (1) excels are not databases (however we still see them a lot) and (2) OneDrive is a great solution to store your data on-line.
However, I think that a simple storage account is still very handy, as a quick solution. I also see it as a good match for Brian his use case.
Hi @Rens, did the response provided by the users and experts helped you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Hi @Rens, we’ve noticed that no response has been received from you since the 10th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!
FYI – wanted to let you know that I haven’t forgotten about this. Just been slammed on a bunch of projects this past week, but hoping to get to it soon and write up the process over the next week.
