Tag: Business Intelligence

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.


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.


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.


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


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.


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.


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


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.



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.


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.


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.


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.


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.


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


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.


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.


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


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.



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.


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.


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.

Installing and Configuring Power BI Gateway for Data Refresh

Hello once again, we are back with another exciting blog on Power BI and this time we are going to explore the Power BI Gateway that needs to be installed on your Database server so that the online app can refresh its datasets on a regular basis to make sure the reports and dashboards are updated with the latest data. For this demo, I am going to use the example from my previous blog  (https://axtricks.wordpress.com/2017/07/16/using-microsoft-dynamics-ax-as-power-bi-data-source/)

in which I created a Report for InventOnhand in Microsoft Dynamics AX based on the data available in SQL Server database for AX.

Let’s start, first and foremost we’ll need to publish our pbix file to the Power BI online app as we created it on our local environment using Desktop version of Power BI, I am going to publish this one to my Organisational group that I created in an earlier blog (https://axtricks.wordpress.com/2017/07/12/sharing-and-collaboration-in-power-bi/) instead of my Workspace.


As soon as my report is available online, I wanted to add a few more ‘Numerical’ visualisations for a proper monitoring of the data and its data updating.


Then, I created a Dashboard where I pinned all my visualisations for an end user scenario, if you are new and want to look at how to create reports and dashboards, you can look up an earlier blog of mine where I explained it in detail (https://axtricks.wordpress.com/2017/07/10/creating-a-simple-report-using-powerbi/).


Now, let’s move forward and install the Data Gateway for Power BI, you can easily download it from this link (https://go.microsoft.com/fwlink/?LinkId=820925&clcid=0x409). Once you’ve downloaded, open up the installer, first it will ask the type of gateway that you want to install, in my case and for the sake of simplicity I am going to use the Personal Gateway.


We get a few notes from Microsoft regarding the Gateway, make sure you follow all the best practices.


After you’ve chosen the version of Power BI Data Gateway, it will want you to sign in to your Power Bi account so it knows to what account does the gateway needs to be signed up.


The NEXT button will open up a new form where you can enter your account details, after you are done, hit SIGN IN.


After the Power BI account is Signed In, it will need your Database Server Credentials so that it can access the Database that you want the data to be refreshed from, just make sure you enter proper Username and Password as well as the domain for your User.


After all the configurations, it will finally give you the good news i.e. the Gateway is successfully set up and you are good to go with setting up your Data Refresh now. Please note that there are sometimes version or account issues with the Gateway, the first solution that I myself came up with was to uninstall the Gateway, sign out of my Power BI Desktop, install the Gateway again and then sign in form Power BI Desktop, after which, try reconfigure the gateway.


Now, moving towards our online service i.e. the Power BI app, click on the “Manage Gateways” menu from the location as shown in the following snapshot.


There, I created a new Gateway and defined my Database server i.e. server name, database name, credentials etc.


You can also add users that you want to have access to the gateway, in my scenario I didn’t need to so I let it go as it is.


Now, as our Power BI Data Gateway is installed, configured and defined on the online app as well, let’s create a schedule to refresh our data, you can also select a Refresh Now button to refresh the data manually.


It lets me go through the settings for the Dataset refresh, where I have my credentials and other information saved, let’s change “Schedule Refresh” from Off to ON.


But before defining the recurrence of Data Refresh let’s make sure our Gateway is properly set and ready to go, open the Gateway connection fast Tab and you can see in my case it shows online.


Moving forward, I am going to define the Time for refresh and set the frequency to refresh the Dataset daily.


After you are done, it shows a notification telling that the data refresh has been set and now your data will be update according to the recurrence defined by you in the refresh settings.


That is all for today, we learned how to set the Data Gateway and set the Refresh Schedule to update our datasets online using the Database server hosted anywhere as the Gateway acts as a bridge between them. See you again with a new topic soon. Thanks

Using Microsoft Dynamics AX as Power BI Data Source

In my previous Power BI blogs, we have discussed in detail the basics which included creating a simple report using an online webpage as the data source as well as exploring the sharing capabilities included in Power BI, but most of the topics were general and were related to the Power BI’s cloud app, so we decided to use the desktop version of Power BI this time in addition to using the most powerful ERP solution i.e. Microsoft Dynamics AX as the data source. For demo purpose, we decided to use the most popular scenario/problem in AX i.e.  real-time visualisations/analyzation of OnHand items and its related fields with a touch of Business Intelligence to it.

Power BI Desktop is a desktop version of Power BI used for on-premises creation of Datasets and Reports mostly using data files hosted locally like in our current case, once the reports are created you can easily publish them to the Power BI cloud app and use the app’s features i.e. sharing, dashboards etc. Power BI desktop is available online and can be easily downloaded from there. Let’s now go straight to the point and start making a report for OnHand items in AX.

After you have successfully installed the desktop version of Power BI, open the application and click Get Data.


It will open a dialog with option to select various data sources, we’ll be choosing Microsoft SQL Server as our AX’s database is hosted there.


Once you have selected SQL server as your data source, it will ask for the Database Server and the Database Name from where the data is to be fetched.


It will ask for the credentials to be used in accessing the Database Server, make sure you have the appropriate rights for it.


After selecting the Database Server, Database Name and entering the appropriate credentials, it will show all the tables that are available in Dynamics AX’s Database, in our scenario we’ll only be needing tables related to inventory and to be exact we are going to fetch three tables from AX which are inventDim, inventTable and inventSum.


As we have a multiple data tables scenario, we’ll need to define the relationships between our selected tables, to do that go to the relationship tab and select “Manage Relationships” from the toolbar.


It will open up a dialog where we need to define the columns in our tables that are the criteria for relations among them, in our case the inventSum and inventDim tables were joined on InventDimId whereas inventSum and inventTable were joined on ItemId field so it was pretty straightforward to create the relationships, if your tables have obvious columns for the relations you can also try the auto detect button at the bottom which will detect them automatically.


Once the relations are created, it will preview the ERD for you in the same relations tab. It can easily be seen that inventDim and inventTable tables both have one-to-many relationship with the inventSum table.


After describing and setting proper relations for our tables, lets now format our data according to our needs here, for my scenario I just defined the data types of the unidentified columns so that I can play with them in my report with their appropriate usage, another thing that I did in the formatting phase was removing all the irrelevant columns so that I have a clean set of data that only contains useful information as needed for my scenario.


After the data is formatted and ready to be used let’s move forward and create the Item OnHand report for Microsoft Dynamics AX. First of all, I am using a pie chart visualization to show the physical availability of all the items that are available in my warehouses.


Moving further forward, I am adding some more useful visualisations using the AX’s Onhand information that will help the user analyse the real time status and information for the available items in a very interactive way, I added a stack area chart with Ordered, Received and Posted Quantities as well another visual with comparison between Reserved Physical and Available Physical quantities. We can create a lot more visualisations according to our needs/requirement.


Being a very large ERP solution with multiple Legal Entities, Sites and Locations for an Organisation, a filter will be very helpful in analysing the data by each of the available options, therefore I am adding three filters to my report that will be Location, Legal Entity and Site filters. Also, as Power BI is already that much intelligent/interactive that we don’t have to define any logic behind the filters, you’ll just have to pick your selection and it will automatically filter all the visualisations according to it.


That is pretty much all I have today for the topic “Using MS Dynamics as Power BI data source”, nevertheless, we can create various reports using AX’s data and visualise the live status of the Organisation as well as predicting its future with the ability to define strategies using the historical data to make Organisation more profitable and up to date. There will be more coming your way on Power BI soon, so stay tuned. Also, if you have any queries or want us to focus on a particular feature in our next blogs, please feel free to contact me.


Creating a Simple Report using PowerBI

Hi everyone, today we are going to create a simple report using Power BI and will discuss the features at each step as we go along. For demo purpose, let’s take some data of all the Football World Cups held until now.

You can actually get data directly from a webpage in Power BI, in my case I have chosen Wikipedia, the simpler and free source of information. Open Power BI and click Get Data, a screen with data source options will show up:


Let’s put the URL of the webpage that we want to fetch the data from.


Once you hit OK, it will load all the corresponding Tables that it found through the webpage. I will choose Results table that has the information about the Winners, Runner Ups etc. of each Football World Cup until now, after you check the Results table, it will show a preview of what data is available in it.


As the data is not in our required form i.e. Columnar Form (recommended for Power BI), I will edit the data before loading it. After I click Edit button it will open up the Table and will provide many formatting/editing options that I can choose from.


Here, I have truncated the Year column to show only the Year number, as well as added a new column i.e. Country, as I will use it afterwards in my report, once the changes done, my data will look something like below, I can Publish my Data set now to the Power BI app.


So, after the formatting is complete and dataset is published to the Power BI app, let’s now play around with our data and create a simple report.

Let’s first use a map visualisation to show which nation/country has won how many Football World Cups to its name. In the example attached below you can see it shows me the WC counts i.e. 5 for Brazil.


Let’s extend our report and add another visualisation, this time a Pie Chart. I am adding the Country column, Host column and Winners column and it will break up the chart for Winning Nations and the Country where they won it i.e. the Host.


One more thing that is missing is the By Year information about the World Cups, so let’s add a Filter to our report, it can be added same as the previous two visualisations, just select Year for its value and it will work as a filter for our report, as shown below, when I select Year 2014, it automatically refreshes my other two visualisations and show appropriate results accordingly.


That is just a basic introduction of creating a report on Power BI app, but this is not all, there is more to come on this topic, so stay tuned. Thank you.

MS Dynamics AX 2012 Master Data Management with Master Data Services

As SQL Server Master Data Services or MDS is already covered in our previous topic, lets discuss Master Data Services usage along with Microsoft Dynamics AX 2012. Master Data Management in Data Import Export Framework is the area in AX that deals with exporting the non-transactional data to a data hub called the Master Data Repository.

MDS is used with AX by organizations who use multiple instances of Dynamics AX within the scope of their firm, having multiple instances of AX can cause data redundancy as well as possibility of unclean data that can use up reasonable resources of the organization both financially and physically. To overcome this, we use MDS, through which data can be exported as well as imported from the Master Data Repository that contains the clean and consistent data taking into account the changes made in each and every AX instance. As a hands on example, I will be publishing the Products entity from AX 2012 to the Master Data Repository.

First of all, make sure that you have configured Master Data Management correctly to connect with SQL MDS Database, and for that open the Microsoft Dynamics Powershell and run the following command after modifying it as per your database information,

Set-AXMasterDataManagementMDSSetup –MDSServerName <SQL MDS server name> -MDSDatabase <SQL MDS database name>

After the command has run, open AX and enter the parameters required to connect AX with Master Data Repository, parameters include Server name, MDS Database name and MDS site’s service url that you can get from browsing the site in IIS, afterwards, hit Test configuration button and it will open the infolog indicating successful connection,


After the MDM configuration is complete, select the “Publish entities to SQL Master Data Services” menu in the DIXF module to select the entities to publish, in my case I am only selecting Products entity, after selection, hit Create schema button to start publishing,

MDM_2Once the publishing is complete, open the SQL Master Data Services WebSite to confirm, you will see a new model is created for the Product Entity called DMFProductEntity,


The entity is created but there will not be any data for now, to export data from AX, so, to export data we need to create a Sync Group for Data Import/Export from/to MDS from a particular company. Once the group is created you can set the recurrence of the sync on whatever time period you want, it will continue to export/import data as per the schedule.


For demo purpose, I have manually run the synchronisation the first time, after the sync is complete, it shows Infolog with status of Import/Export of data,


Now, when you navigate to the MDS Repository web site, you can see the records filled in,


The question mark at beginning of each record shows that the data is not yet validated, you can apply whatever business rules that you require e.g. mandatory fields, fixed format for fields etc. Once the rules are defined, you can validate the records and it will specify how many records have passed the validation and how much failed. You can validate a version of data by navigating to the Version Management module of MDS website,


Once you hit validate button, it will try to validate the records that are in the Entity, after the data is validated, a green checkbox will show up for all the records that passes the validation,


You can also check the status of the entity to identify how many records passed,


That is all we have today for Master Data Management in AX 2012 and its integration with SQL Master Data Services.

Exploring the basics of SQL Master Data Services

MDS or Master Data Services is a component of Microsoft SQL Server introduced with SQL Server 2008 R2, it was a basic component at first with some irregularities but in its later versions i.e. 2012/2014 it was a much improved inclusion in the SQL Server family.

MDS uses a Master Data Hub as a base repository that can contain data from multiple sources with each source known as Spoke. MDS allows to create discipline and make the data across multiple instances clean and consistent.

It uses a basic object to organize and structure Master Data called ‘Model’. There may be multiple models in MDS each having different versions, the default is automatically created and is named VERSION_1. Versioning in MDS helps a lot in the agile way of applying business rules to the Master Data.  It is much easier to have a live version of the model that is in use while working on upgrades to the same model in a different version, we can lock a version and after validation commit it, once a version is committed users cannot make any change to that version.

A more granular component of Master Data Service is Entity, multiple entities are contained in an MDS model, each of them carrying records just like a table in Relation Databases, entity is defined by multiple attributes that are described as the columns and members which are the records or rows in the entity. Each entity can contain a unique attribute that is used to ensure that there is no duplicate data in the entity, it can also contain lookups in its attributes to make sure relevant data is used.

Application of Business Rules is a big feature in SQL MDS, it helps cleaning of data and to make sure the data is correct. Business rules can be applied to an entity through Administration Module Master Data Manager, there are numerous rules that you can apply to an entity like making columns mandatory, making sure that correct pattern is used while entering data etc. We can also improve the Data Quality by applying Business Rules on it.

Master Data can be accessed in two ways, i.e. with the help of the Web Interface that is hosted on IIS in the Master Data Manager or we can use MDS Excel Add-In that will add a tab with Master Data operations in Excel.

We are going to explore the Web Interface for Master Data Services in this blog, we will shower some light on all the features that come with MDS, once the installation phase is complete and you have configured IIS to host the web interface, the home screen looks something like below screenshot, you can access any of the available options according to the needs.


System Administration is the core component of SQL Master Data Services, actually, this is the module where you create and modify your models, entities, business rules etc. You can create multiple models in this section, apply different business logic on them, set the hierarchy of the objects etc.


The second most important is the Integration Management, in the main page of this module, there are options to choose your model and the Batch Grid will show up the pending data in the Staging table that needs to be processed. In the grid, you can see the entity, the version etc. with respect to the staging table that has the data. You can also create subscription views through this model to specify the viewership of the data.


Version Management is also an important part of Master Data Services, you can create multiple versions of the same Model, which will help to add new business rules on the same model while also having a standard version of it. Once, the model is finalized it is Locked from the Version Management in MDS Web UI and then committed so that it’ll be used as the current LIVE version of that model.


Explorer Menu of MDS is the main module where you actually work with the master data, add new members, define hierarchies, apply business rules to your raw data that came from the staging table. Once you apply the rules, you see a dialog that shows you the validation status of your data. Also, there is a space on the right pane that shows you the description of Validation Errors, if any.


The last module to discuss is the User permissions module and it deals with the security of the data and defining that who can access your data and to what level and roles. The default user is the Administrator who has all the rights by default.

Stay tuned for the next blog post where I will be discussing how we can leverage MDS with Master Data Management (MDM) of Microsoft Dynamics AX 2012.