Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ BI Tools and Applications _ Powerbi & Access

Posted by: lord_kaiser Dec 8 2017, 01:44 AM

Hi Everyone,

Just wanted to know if someone can point me in the right direction with turtorials resources of integrating MS Access with PowerBI?

Looking to create reports and a dashboard?

Thanks

Posted by: MadPiet Dec 8 2017, 02:35 AM

There's not really anything to it.

You connect to Access from PowerBI, then import whatever you want into PowerBI. You can grab data from queries and/or tables, and filter those down (both rows and columns) in PoweBI.

Look around on www.powerbi.com and see what you find there. There are tutorials all about it. Also there are tutorials on YouTube as well.

Posted by: GroverParkGeorge Dec 8 2017, 08:48 AM

Power BI can consume data from a large variety of sources.


So, you can easily integrate your data from Access.

You can publish your Power BI visuals to your Power BI server and use the link to the URL in your Access forms to launch and display them.

Up until recently, you could display the PBI visuals in a browser control in an Access form, but the November update to PowerBI broke that capability. Their response to my inquiries was, basically, tell Access to fix their Web Browser Control. I was very disappointed.

Nonetheless, the combination is a very powerful one, IMO.


Posted by: ChiliDog Dec 8 2017, 11:16 AM

GPG thanks very much for this information. I've been trying to sort out Access / Power BI integration and this helps clear the fog.

Can I post my own question here. I've been tasked with, and I don't think this is possible...integrating PBI *into* Access. Meaning, we have an Access frontend/SQL server backend, and we want to be able to launch PBI *from Access*, and I suppose link to SQL Server tables (but connections must not be exposed). I think this is not doable, correct ? And the client will never go for any cloud-based solution...

I'm starting to conclude the closest we could come is to launch Excel, with a pre-defined Power Query template to ETL the SQL server data in a form the user can understand, and use Excel reporting tools.

If anyone has any clues I'd be grateful. Thanks.



Posted by: GroverParkGeorge Dec 8 2017, 11:49 AM

Well, you can open any PBI file from within Access. In turn, that PowerBI file can display the charts and graphs you want to display. or you can use a line like this to display the visuals in your browser:

Application.FollowHyperlink "https://app.powerbi.com/view?r=YourCustomPowerBIIDStringGoesHere"

Posted by: lord_kaiser Dec 8 2017, 11:06 PM

Thanks for the suggestion everyone.

I'll start looking inot this further.

Posted by: MadPiet Dec 9 2017, 07:03 AM

You don't need Access for this at all. You can call stored procedures directly from PowerBI Desktop. You could just put all the objects users need to do their analysis in a single schema in SQL Server, grant users execute rights to them, and then you'd be all set.

Posted by: MadPiet Dec 9 2017, 07:13 AM

If you're brand new to PowerBI/DAX/PowerPivot, look up chandoo's stuff on YouTube, so you can get your head around some of the stuff in PowerBI. The hardest part is learning DAX - for that, buy Rob Collie & Avi Singh's book - it's on powerpivotpro.com. As for "connecting Access and PowerBI Desktop", well, there isn't much there, because PowerBI Desktop is pretty much the Analysis Services engine with a bunch of connectors, and then the visuals. PBID can use almost anything as a source, including Access... but it works best with star schemas. (That's what it "inherits" from coming from SSAS).

If you read Rob & Avi's book, they walk you through getting data, creating relationships, building measures using DAX, etc. And you can download all the files they use from Rob's website.

Posted by: GroverParkGeorge Dec 9 2017, 09:05 AM

The question was, as I recall, can you integrate PBI with Access, not can you bypass Access and go straight to PBI.

The point is, I would imagine, whether someone using Access as their database for an application--both data store and interface--can take advantage of the superior visual presentations possible with PBI.

That said, of course, PBI certainly has a lot of, well, power, beyond that starting point.

Posted by: cocoflipper Jan 26 2018, 07:24 PM

Hi George,

Do you know if there is any movement on the "PBI visuals in a browser control within an Access form", that broke with the November PBI update? It would be nice to utilize the web browser control with PBI. You also later in this post mention that you can open any PBI file from within Access, but if it is not with the web browser control, how do you do it?

Posted by: GroverParkGeorge Jan 27 2018, 10:02 AM

Sorry, somehow I didn't get a notice of a reply.

I just use a hyperlink to open the PBI visual in a new browser window. Nothing fancy.

I have a sense that the PowerBI people could care less about breaking the web browser control in Access. I have given up any hope of getting progress on this.

On the other hand, the annual MS MVP Summit is coming up in March and I'll be face-to-face with the people involved, so maybe it'll be harder to ignore me. woohoo.gif

Posted by: cocoflipper Jan 27 2018, 06:15 PM

Go get 'em George. Have to keep Access relevant.

Posted by: MadPiet Sep 5 2018, 03:25 PM

I know this is sort of a dead question, but anyway...

It looks like Access is out of the picture in terms of PowerBI. Sure you can get data from Access, but PowerBI is moving toward integration into Reporting Services. About the only way I can think of "linking" Access and PowerBI is to use hyperlinks to a specific report on your report server.