Acumatica 6 added a very interesting feature: You can now add a Power BI tile directly to a Dashboard!
Things that you add to Dashboards in Acumatica are called Widgets and there is now a Power BI Tile widget in Acumatica 6 (for more on how Dashboards work in Acumatica, click here).
I’m a big fan of Acumatica. I’m a big fan of Power BI. So this new widget looks very interesting to me.
So I decided to set off on an adventure to try and figure out how to use this so called Power BI Tile in the Add Widget menu on Dashboards in Acumatica 6.
First, I did what anyone would do when they want to learn about something in Acumatica. I checked the HELP. This is something that I’ve had to train myself to do because I’m used to Sage 500 (my old ERP application) where the HELP was not very helpful. In Acumatica, there are some useful HELP articles. It’s almost like a traditional HELP combined with a Knowledgebase, and it continues to improve with every new version of Acumatica.
There is a great HELP article that explains how to connect Acumatica to Power BI which is required before you can use the new Power BI Tile widget.
Azure Active Directory
But the first thing mentioned in the HELP article is that you need an Azure Active Directory instance configured. This concerned me because I have no idea what Azure Active Directory is. However, I do know that Power BI runs on Azure, so hopefully I can access Azure Active Directory from within Power BI.
Let’s find out if that’s the case.
Since it seems that we will need to be some kind of Administrator in Power BI / Azure, I decided to create an account using an email on my augforums.com domain so I can be sure to be the Administrator for my domain.
I went to PowerBI.com and created a new account. You have to use a “work” email which basically means something other than @gmail.com, @yahoo.com, etc. That’s why I used an email associated with augforums.com. Specifically, I used pal@augforums.com. This blog used to be called PerpetualAcumaticaLearner.com (or PAL for short) which is why I chose pal@augforums.com.
I signed into my account on PowerBI.com, then I wanted to look for some “evidence” that Azure Active Directory was in fact lurking behind-the-scenes in my PowerBI.com account which I suspected, but wasn’t 100% sure. That’s when I went to trusty old Google and searched for “power bi azure active directory” (brilliant huh?) which took me to this article: https://powerbi.microsoft.com/en-us/blog/azure-active-directory-and-power-bi/.
It appears that my suspicion was correct. Azure Active Directory is in fact lurking behind-the-scenes in your PowerBI.com account.
But how can we “see” it?
I decided to try clicking the yellow icon in the upper-left-hand corner. And, what do you know, there is something called Admin. Looks promising! Let’s give it a try.
After clicking the Admin icon, it takes me to a page asking if I want to be the admin for augforums.com. It seems like you can only have one admin for a domain and since (hopefully) no one else has registered an email with Power BI that is @augforums.com, I should be able to click the Yes, I want to be the admin button and expect some kind of success. Let’s try it.
Once I did that, it then asks me to add some kind of record with my hosting provider. It’s pretty cool that it already knows I’m hosting augforums.com with Bluehost so it shows that to me on the page below. Then it even gives me a link for Step-by-step instructions for adding a TXT record which are written specifically for Bluehost, my hosting provider. I used those instructions and I have to say that they are very thorough, including screenshots, etc. I won’t bore you with the details because your hosting provider might be different with a different set of instructions, but I do encourage you to take a look at the step-by-step instructions for your hosting provider. If they are anything like the instructions for Bluehost.com, they are well done.
I blacked out the special code in the screenshot below because it seems like something that I should keep private, but that is the code that I pasted into the appropriate place at Bluehost.com, my hosting provider.
Once I registered the “secret” code above with my hosting provider, I then clicked the button on the bottom of the page called Okay, I’ve added the record. I love these buttons by the way, so user-friendly. Acumatica should do something like this with the RELEASE button in Acumatica and rename it something like Make sure you want to do this, you can’t change your mind after you press it.
Once I clicked the button, it did some verifying, then it gave me a congratulations message. Hooray! That was relatively painless.
After clicking the Ok button above, it took me to a pretty “beefy” Azure admin area. I had to give my phone number and an alternate email for additional security.
Once I did that, it took me back to the main portal called Office 365 Admin center. Here is what it looks like:
The cool thing is that I started by signing up for a free PowerBI.com account and now I’m in an Office 365 admin area, but I never had to put in my credit card info.
The last icon on the left looks promising. It’s called Azure AD which should stand for Azure Active Directory if I’m not mistaken.
I clicked the Azure AD link and it took me to another sign-up page where I had to put my phone number in again. Seems like this is different than the regular Office 365 account. Maybe? I’m not sure, but that’s my guess.
When I was done, I got this message. So it seems like the first step was to be the admin for augforums.com within Office 365 and the second step was to subscribe to Microsoft Azure. Interesting…
The admin area for Microsoft Azure looks entirely different from the Office 365 admin area. Very, very interesting. See the next screenshot and you’ll see what I mean.
Side note: I recently attended a Power BI user group here in Columbus, OH where Greg Deckler showed machine learning within Azure. This is the area that he went to in order to add it. Now I know how he got here! It also looks like this is where you can setup Power BI Embedded which I’m pretty sure is completely different from the Acumatica Power BI Dashboard Widget and could be the topic of a future post.
Connecting Acumatica to Power BI
I’m not entirely sure if all of the Azure steps that I did are required. Maybe give this a try without them and leave a comment below to let me know how it goes.
So all of that was great, but now I need to get back to setting up the connection from Acumatica to Power BI.
To do that, I have to go to a secret URL that is mentioned in the HELP article within Acumatica.
That secret URL is: https://dev.powerbi.com/apps
Once you click on the secret URL, you get taken to a page within PowerBI.com.
Step 1. Click the Sign in with your existing account button.
Step 2. Fill in the fields.
Step 3. Select all of the options.
Step 4. Click the Register App button. Let it think for a moment. Then make a note of what Power BI populates in the Client ID and Client Secret fields.
Step 5. Once you have your Client ID and Client Secret, you can then go back into your Acumatica 6 Dashboard, click DESIGN, then click add a new widget.
Step 6. Choose Power BI Tile, then click NEXT.
Step 7. Take your secret Client ID and Client Secret from the previous Power BI steps and enter them here (optionally enter a Caption to display on top of your Acumatica dashboard widget).
Step 8. It will open a webpage and you need to click the Accept button.
Step 9. When you get returned back to Acumatica, there will be a couple of new fields that you can enter: Dashboard and Tile.
Both fields refer to PowerBI.com. Pick the Dashboard and the Tile within PowerBI.com that you want to access.
Step 10. Click FINISH.
Once you do that, the Power BI tile that you selected will be displayed within Acumatica!
Let’s think about what we just did. We took a Tile from PowerBI.com and displayed it within Acumatica. That means we can do some incredible analytics in Power BI, but show the results inside of Acumatica as if it’s a part of Acumatica.
This is very cool.
This part shocked me
All of this was great, but it basically behaved as I expected it to. Nothing too surprising.
What did surprise me was what happened next.
I opened an Incognito window in Google Chrome. Since both Acumatica and Power BI are browsed-based software, opening an Incognito window is basically like logging in from a completely separate computer that has no idea who you are. Acumatica doesn’t know who you are and Power BI doesn’t know who you are.
Within the Incognito window, I signed into Acumatica as a different user (not admin). I intentionally picked a user that had access to the Dashboard, but not many permissions other than that.
This is what I saw:
I expected this. I have access to the Dashboard, but not to all of the widgets on the Dashboard. Most of these widgets are based on Generic Inquiries and the BEAUVOIR user doesn’t have access to all of the needed Generic Inquiries. So you just see a lock icon. Totally fine and totally expected.
Also, the Widget that points to Excel Online is asking me to sign-in because SharePoint Online (Office 365) doesn’t know who I am since I’m using an Incognito window. I would need to sign-in to Office 365 to see the Excel Online file. Totally fine and totally expected.
But then I scrolled to the bottom of the dashboard to where my Power BI widget is located.
And I was shocked!!!!!!!!
I was totally expecting to see something similar to the widget that uses Excel Online. I thought that PowerBI.com would want me to login to see the Power BI Tile Dashboard Widget.
But it didn’t!!! The Client ID and Client Secret that we entered earlier somehow made the widget intelligent enough to display without requiring me to login to PowerBI.com.
This is huge! Now you can have one (or a few) power users in your organization building Power BI tiles that can be shared securely within Acumatica without requiring all your Acumatica users to go out and create separate PowerBI.com accounts. Seriously? This is very cool.
But, wait, before I get too excited, let’s make sure that this tile is “live”.
I flipped back to my regular Google Chrome window, went into PowerBI.com, and changed the title of my Tile.
Then I flipped back to my Incognito window and refreshed the page.
As I was hoping, it picked up my changes!
So, what we have here is a “live” Power BI tile that doesn’t require Acumatica users to have Power BI accounts. They get to leverage the power of Power BI without the inconvenience of having to maintain separate PowerBI.com accounts. This is really cool.
Now, all is not blissfully wonderful. Although the tiles are “live”, showing up-to-date information, they are not “interactive”. If you click on a Power BI Tile to try and do anything with it, it opens a separate browser tab and requires you to login to PowerBI.com. So, if you want to interact with a tile, you will need a PowerBI.com account and one that has access to that specific tile within PowerBI.com.
But I still think this is cool because Power BI is very good at aggregating data from multiple sources and displaying the information. Just the fact that I can now securely share my Power BI creations with other Acumatica users without requiring them to go off and create a PowerBI.com account is a big win in my opinion.
Also, even though you can’t interact with the Tile inside Acumatica, at least you can hover over something to see what the actual number is like this:
And, of course, since you can securely expose any Generic Inquiry to Power BI using OData (click here), you can feed Power BI with Acumatica data, then schedule it to automatically refresh from Acumatica once per day (for free) or more frequently using a $10/month user account. If you want that more than once per day refresh, since the Acumatica users don’t need separate PowerBI.com accounts, you only need to purchase one PowerBI.com account which is very nice and very affordable.
Overall, job well done Acumatica. I expect that Power BI will become even more popular because of this new feature in Acumatica 6 once people figure out how to use it. Hopefully this post helps you get started.
Disclaimer: I tested this on Acumatica build 6.00.1129, but who knows, judging by the rate at which Acumatica introduces new cool things, we might have even more Power BI goodies in future versions, especially now that Acumatica is adhering to a Continuous Release model.