Tag: Excel to Power BI

Power BI with Dynamics 365 for Operations

Hello everyone, this Power BI blog is going to focus on how it is used with New Dynamics AX i.e. AX 7 or Dynamics 365 for Operations. Apart from Power BI being integrated within AX 7, there are also some pre built content packs that can be used to analyze the retail and cost management models of our AX. So, to cut the long story short, we are going to see how we can connect AX 7 with one of these pre-built content packs in Power BI.

Let’s start, first of all open and login to Power BI cloud using your account, it is pretty important and must be noted that the user you are going to login afterwards must be an enabled user in AX 7, otherwise it will throw authentication error while fetching data.

Now, first of all, click on Get Data from the main workspace and then click on Services Data option.

1pb

After the available content packs show up, navigate to the ones that refer to AX, in this demo, I am going to use the Microsoft Dynamics AX Cost Management Content Pack.

2pb

After the available content packs show up, navigate to the ones that refer to AX, in this demo, I am going to use the Microsoft Dynamics AX Cost Management Content Pack.

3pb

After pasting the URL, click next and you will be asked the Authentication method to sign in to AX 7, select OAuth2 from there.

10pb

After clicking on Sign in button, a new tab page will open that will ask for your Office 365 or AX 7 credentials, make sure you enter the correct ones here.

11pb (1)

After the authentication is complete, it will show a status bar that indicates that data fetch is in process.

12pb

Once done, it will open up the Dashboard that is created using your AX 7 data, you can now play around with Natural Language Query and other cool stuff available within Power BI.

4pb-1024x497

Multiple reports are also filled up with data in the Reports section, you can easily modify or create new reports on top of those.

45pb-1024x494

That’s it, it is as simple as that. And this content pack covers all the related scenarios of Cost Management, there are more AX data sources anticipated to be added to the already amazing content packs list and then again there is Power BI embedded in AX 7 as well. See you again.

 

Advertisements

Quick Insights Feature in Power BI

Microsoft Power BI contains numerous useful features which makes it a unique and intelligent Business Intelligence solution, apart from being simple and extensive its data source connectivity and support is huge, it supports most of the prospective data sources that users can use to improve their businesses. One of those unique features is the Quick Insights feature that is available in the Power BI service, this feature is really cool as it can help you make heavy Reports and Dashboards without you having to define the visuals or spend hours on deciding the appropriate scenarios.

Actually, what this feature does is that it takes the data and its structure from whatever data source that you’ve defined/used and automatically generates all the possible visuals based on the data, isn’t it great that all of your work is efficiently handled by Power BI itself, imagine you having to create large reports/dashboards in a very limited time, this ‘Quick Insights’ feature can solve that headache for you and create all the possible solutions and you just have to pick the ones that seem important to your organization. Let’s move forward and have a small demo on this.

I am going to use a sample data source and this time for the sake of simplicity and showing the adaptability of Power Bi, I am going to use an Excel sheet instead of a complex data source. If you want a demo on other data sources you can refer to my previous blogs defining multiple data sources that can be used. So let’s start.

The Excel file that contains my sample data is located in a folder on my local directory.

1

It contains a very simple data model i.e. Item Sales by Date, Units and Region with a limited set of data as shown in the below snapshot.

2

Open up the online Power BI app, click on Get Data and then from the available four initial options select the Get Data from Files button as shown below.

pb1

Then it will ask the location of that file as in Power BI you can upload files from multiple locations e.g. One Drive, SharePoint or Local etc. I am going to select the Local File option.

3

After that, you’ll have further options which will basically ask if your Excel file contains a Report that you want to view in Power BI or it just contains raw data that you have to use in your Reports etc. As in my case, it contains only data I’ll select the Upload file option.

5

Once the data upload is complete, it’ll notify us through a Power BI notification.

6

You can now access your Data in the Datasets section of your Workspace, when you click the options on that dataset, there’ll be a option named Quick Insights, click on that button.

7

Once you hit that button, it’ll take a few minutes(based on the volume of data) and will try to create all the possible visuals that can be created from the available data.

8

After the Insights are ready, click on the View Insights option to see the automatically created visuals.

9

There are various visuals created covering all the possible scenarios/ways that the data can be used or manipulated, I’ve attached snapshots of a few below.

10

11

Now, I’ll have to select the visuals that I think are important and useful and pin those on my dashboard for the current dataset.

12

Now, I’ll have to select the visuals that I think are important and useful and pin those on my dashboard for the current dataset.

13

That’s it for today, just to recap, we explored the Quick Insights feature available in Power BI and created a Dashboard without us having to even create a single visual or report. See you soon.