Though BI Tools are available with the MS Dynamics Ax
2012, and Third Party Tools for creating BI Reports and Models are available in
the market. But emergence of Power Query, Power View, Power Pivot, and Power
BI, with MS Excel turned the BI development to a new horizon.
As it is known that Microsoft Dynamics AX 2012 provides
a flexible pre-built business intelligence (BI) solution for mid-market
organizations. The use of built-in content—including Role Centers, analytic
reports, and analysis cubes—eliminates the need to build a solution from the
ground up, and therefore saves time and money. The Microsoft Dynamics AX 2012
business intelligence solution can be configured and extended to suit your
specific needs with the help of Microsoft Business Intelligence development
tools.
The Microsoft Dynamics AX 2012 business intelligence
solution consists of the following built-in features:
1.
Role Centers (dashboards):
Thirty-two default home pages that pertain to the work needs of various user
profiles in an organization.
2.
Analysis cubes: Eleven analysis cubes
that address the analytic requirements of the major functional areas within
Microsoft Dynamics AX 2012—including finance, supply chain, manufacturing,
professional services, and business processes.
3.
Key Performance Indicators (KPIs):
More than 60 of the most commonly used KPIs that help users evaluate the
success of business activities.
4.
Analytic reports: More than 150 analytic
reports that provide insight into business data.
No doubt these are sufficient enough for the user. But
development or modification needs customer support (development team). Further,
integration services, again requires a customer support.
Power Query is a free Add-in to Excel add-in that can be
used for data discovery, reshaping the data and combining data coming from
different sources. Power Query is one of the Excel add-ins provided as part of
Microsoft Power BI self-service solution.
This is an ETL tool (Extract, Transform, Load – like SSIS)
built into your familiar Excel to search or discover data from a wide variety
of data sources (both from your enterprise as well as from online public data
sources). Power Query has an intuitive and interactive user interface which can
be used to search, discover, acquire, combine, refine, transform and enrich the
data.
Power Pivot is a free add-in to Excel, it extends the
capabilities of the pivot table data summarization and cross-tabulation feature
with new features such as expanded data capacity, advanced calculations,
ability to import data from multiple sources, and the ability to publish the
workbooks as interactive web applications. As such, Power Pivot falls under
Microsoft's Business Intelligence offering, complementing it with its
self-service, in-memory capabilities.
Let us make some analytical reports by using the Power
Tools of Microsoft.
In this walk through we will design two refreshable
(updatable) reports with the help of the Microsoft’s Power Tools.
The first picture depicts analysis of Invoice Amounts of
Purchases of two sites, one is Ballabgarh and another is Chhainsa. Also there
are three types of Purchases eg Raw Material, Scrap and Import (these types are
indigenous or local to the organisation).
The second picture shows analysis of one expense account
named Freight Charges with different dimensions for different months, this data
is being transferred from a SSRS Report.
Make the First Report:
1.
Select
menu option Invoice Journal from the Inquiries Menu group from Accounts Payable
module, as shown below.
2.
A
list of transaction will appear in the form of grid form. It will show a list
of Invoice Account, Purchase Order, Date, Invoice, Voucher, Tax, Invoice Amount
etc.
3.
Press
Ctrl+T to export the grid data to an excel file.
This data in excel is in the form of Table (Excel Table),
with all the properties of Excel Table, like Automatic Filterable, Sortable,
able to Pivot etc. Save the table with a Name, say e.g. PurchaseInvoice.xlsx.
4.
Now
open a new Excel File. Select option From File from Menu Group Get
External Data of the Power Query Tab. If you do not have Power Query
Add-ins, please install the power query (it is a free tool from Microsoft).
Select the Form Excel menu option.
Select the File with the help of the File Dialog Box.
After selecting the Excel File, the following Navigator
will appear. Select the Table and not the sheet, as you have exported the data
in the form of Table having a Table Name.
5.
Click
Edit Button, as we want to transform the data, after Extraction.
Please note that each
activity done here is being recorded as a query command. To remove any
un-wanted column, select the columns and then remove it by using button from
the Manage Columns group or context menu could be used.
6.
Click
the Close & Load button from the Close
Menu Group. This will ask if you want to load the data as a table or want to
record this as a connection (to be used later on after some more extraction and
transformation).
After loading the query could be edited by opening the
workbook query from the option shown right to the excel table. Also all the
data could be refreshed with new or modified data. One has to export the excel
data from MS Dynamics to Excel Table and replacing the file
PurchaseInvoice.Xlsx in this case.
As you have seen that the ETL commands are recorded in
the workbook itself, any command can be deleted or added or modified after its
creation.
7.
Now,
let us split one of the column, Purchase Order.
Splitting can be done in
two ways, by some delimiter or some number of characters. Here let us use the
option by Delimiter.
8.
Select
the Column Purchase Order and Select the menu option By Delimiter.
This will ask for some parameters like Delimiter type,
here sign (-) is a custom type of delimiter. Then put the (-) in the second
text box and then select the split radio button (At each occurrence),
and click ok.
This will split the Purchase Order into three columns as
show below.
Now remove some un-wanted columns. Not to repeat that
these queries are being recorded into the workbook, for reprocessing
automatically during later use. So repetition of ETL is not need.
9.
Let
us use the split function by character. Here we need B/C from the first
character of the Purchase Order first chunk, also we need R/I/S from the Fourth
character of the first chunk like BPOR (we need B and R). So, select the Split
Columns-> By Number of Characters after selecting the Column showing
BPOR (that is Purchase order.1 column). Provide the parameter as required, shown
below.
This will split each character by one column, as shown
below. Rename the columns.
10.
Now
let us use the replace tool, in the Site Column, replace all B with Ballabgarh,
and C with Chhainsa.
In the same way replace in the Order Type Column, R with
Raw Material, S with Scrap, I with Import, as shown below.
11.
Now
Close & Load the Data.
Following set of data would load. As you can see it is
showing some errors (95 errors), by clicking the hyperlink one can view the
errors.
These Errors could be removed by using several ETL
techniques. In this example we could use the Remove Errors button
from the Reduce Rows menu group.
12.
Now
one can use some of the Excel’s new feature available in 2013 version, like
recommended Pivot Tables and Pivot Charts. Here is an example
By using a Pivot Table, Slicer, and Pivot Chart, some
analysis of the data is shown below.
This data could be refreshed or modified any time when
required.
Make the Second Report:
1.
Selected
a customized report from the Report menu group.
The report looks like as shown below.
Save this report with as Excel with a name FreightCharges.xlsx
2.
Transform
the Column Date to get Year and Month from the date type of data. This will add
two columns Year and Month.
3.
After
all Transformation the data will look like as shown below.
4.
To
remove any null row use filter query by using the drop-down in the ETL form.
This will generate a filtering query in the workbook.
Then create Pivot Table as it was done in the First Example.
5.
To
make use of Power View click on the Power View Button in the
Report Group, as shown below.
To have a knowledge of Power View, Power Query, Power
Pivot, Power BI, please visit websites and blogs.
