Dynamics 365 AX – Creating Data Entities for BYOD Azure Database including those without a natural key

Business Intelligence, Microsoft Dynamics AX, , ,

We, BI professionals, sometimes take for granted that we can access the database of an ERP system quite easily, especially one that is made by Microsoft. However, the live Dynamics (365) AX database cannot be assessed directly currently.

The diagram above shows that the Primary AX DB is copied into the Secondary AX DB for reporting purposes. However, the Primary and Secondary DB cannot be accessed directly in the Live system. The Entity Store, however, can be accessed either via OData feeds or by synching with an Azure Database.

Creating a Data Entity

Creating a Data Entity is easy. Start by logging into your VM. Start Visual Studio 2015 as an Administrator. 

Continue reading →

When not to use Management Reporter

Financial Reports, Management Reporter, Microsoft Dynamics AX

Management Reporter is easy to use to create Management Reports and it does not require a developer to do it. But there are times when Management Reporter is not the right tool for the job.

Here are some reasons why you should not use Management Reporter:

1. The data you need is not in General Ledger or Budgets module in Dynamics AX

This is kind of the kill all of Management Reporter. If your report asks for things not in the General Ledger or Budgets, then no matter how hard you try, it cannot be done, even if the data you need is in the subledgers.

Common examples are if you need customer, supplier or project detail. These are stored in the Sales Ledger, Purchase Ledger and Projects module respectively and the data cannot be found in General Ledger. 

Continue reading →

Using Organization Hierarchies as Reporting Unit filters with Management Reporter

Management Reporter, Microsoft Dynamics AX, ,

Well, hello. It has been more than three years since my last blog, since then I have been really busy at work and have a baby in the meantime.

That being said, I wonder how much joy you guys have with playing with MR.

I have been using Dynamics AX and MR for more than 3 years now and I am still able to learn new things. One of them is that MR is able to use the organizational hierarchies of Dynamics AX as the reporting tree.

For those who are clueless, in your organisation, you can define the organisational hierarchies of not just the standard organisation but for all your financial dimensions. The idea is that when you select an organisational hierarchy as the reporting tree, you do not need to create it from scratch. Furthermore, when the hierarchy changes in AX (I dropped the word “organizational” as it just makes typing too long), MR is able to reflect that based on the period selected for the report. I thought that was pretty cool.

What happens when you want to use the hierarchy as a filter in the column definition?

Terry: why in blazers would anyone want to do that?

Well, it so happened that I had a customer who needed to do just that.

Imagine the need to have different columns represent the amounts not at leaf level, but at a middle hierarchy level. Sure, you can filter on dimensions and manually create it, but there is no need to re-invent the wheel.

Continue reading →

Using PIVOT function without a CTE

SQL, SSAS

There are some cases when you need to use a PIVOT function in SQL but you cannot use a CTE. One such case is when you create a named query in a SSAS cube.

To do so, place the first part of the CTE query as a subquery of the Non-CTE query as shown here. Remember to add an alias to the subquery. The PIVOT portion of the code remains the same.

PIVOTwithoutCTE

The basic understanding of the PIVOT function can be found here.

Note: It may increase query performance when joining another table to the pivoted table to use join hints. e.g. LEFT OUTER HASH JOIN

Microsoft Dynamics AX 2012 – Multiple Rows In Demand Forecast Table [FORECASTSALES] for each item

Business Intelligence, Microsoft Dynamics AX, SQL

Why are there multiple rows for each item in the Demand Forecast Table in Microsoft Dynamics AX 2012?

This is helpful to know for BI for cubes that aggregate the data in the [FORECASTSALES] table.

Basically the overview line and each exploded line of the same item is stored in the same [FORECASTSALES] table. Therefore, if any measure in this table needs to be aggregated per item, filter out just the overview rows. You can do this by filtering the column [EXPANDID] = 0.

Continue reading →

Management Reporter Security

Business Intelligence, Management Reporter, Microsoft Dynamics AX

Are you confused by how user roles in AX 2012 are integrated with Management Reporter roles? Have problems adding or deleting users in Management Reporter? Get your answers here.

In the last couple of weeks I have been asked by AX developers about Management Reporter Security. Mainly, they were stumped by how assigning different roles to users in Management Reporter does not quite turn out as they had expected. Both times I felt I could only give a partial answer. Thus, I decided to do a little experiment to find out the truth once and for all… or until Microsoft comes up with the next Rollup I suppose!

In this topic, I will cover:

Management Reporter is able to integrate with different flavours of Microsoft Dynamics but in this topic I will cover integration with Microsoft Dynamics AX 2012 only, and only for up to RU5, simply because that is what is available to me for my experiment!
Continue reading →

Conditional Coloured Bar Charts in Excel with Upper and Lower Targets

Business Intelligence, Excel

I was just trying to gauge my BI knowledge level the other day and decided to chart it for visibility.

It should be able to tell me to focus on certain topics if 1) I know 20% or less of the topic and 2) if it reaches 100% – which means I need to expand the depth of the topic.

Here is what I want to achieve:

How do I get the conditional colour formatting of the bars in the chart?
Continue reading →

What is Microsoft Management Reporter for Dynamics AX?

Business Intelligence, Management Reporter, Microsoft Dynamics AX

It is a collaborative and interactive reporting solution from Microsoft for finance data.

View more here:

Management Reporter 2012 Overview

How is it different from standard SSRS?

It is designed to be collaborative. How often do you need discuss a report with other people? This software actually allows you to share via lync or sharepoint, and to insert comments at defined points of the report.

The other massively useful function is the ability to drillthrough all the way to the Dynamics AX entry.

The other functions I think which are useful are:

  • Multilanguage support
  • NO IT involvement required once setup
  • auto-incremental update of data (NO ETL required)
  • Multisource consolidation
  • Integration with Dynamics AX

The documentation for Management Reporter is available now on Technet.

http://technet.microsoft.com/en-us/library/dn435963.aspx

Note:

Microsoft SQL Server 2014 is not currently supported.

Minimum database requirements:

  • Microsoft SQL Server® 2005 Express Edition with Service Pack 4
  • Microsoft SQL Server 2008 or newer is required for the data mart database, system database, and the company  database for the ERP.

Introducing Microsoft Dynamics AX 2012 with SSRS Reports

Business Intelligence, Microsoft Dynamics AX, SSRS

Hi all,

I’ve started a new job with eBECS which is a Microsoft Gold Partner in the ERP Business.

Since I have been there, I have been learning how Dynamics AX is integrated with Microsoft BI Stack since 1) they are from the same company and 2) I need to support BI in an AX environment.

SSRS is now the primary reporting platform for Microsoft Dynamics AX since the 2012 incarnation.

Here’s the quote from Books On Line:

Microsoft SQL Server Reporting Services is now the primary reporting platform for Microsoft Dynamics AX. The default, preconfigured reports that are included with Microsoft Dynamics AX run on the Reporting Services platform.

Good news is that everything you know about SSRS can be applied into the world of Dynamics AX now. All the flexibility of being able to tweak every little detail is available to the report.

On the other hand, it would be useful for AX developers to know more about the SSRS development environment which includes:

  • SSRS services provides two URLs – one for the Report Manager, and one for web services
  • Administrator rights is required for deployment of reports
  • Report Security has to be defined in Report Manager. A new security role is available for this. It is called: DynamicsAXBrowser

For SSRS developers, it is useful to know the following:

  • The No. 1 rule is not to edit the report without saving the project back to the Application Object Tree (AOT). This is because if the AOT is rebuilt, all changes will be wiped out.
  • To create a new report, create a new query in the AX Development Workspace first before creating a new report.
  • Create a new report in Visual Studio 2010 and use the new Microsoft Dynamics AX Template called Report Model.
  • Creating a new report this way requires a whole different process than a normal SSRS report. (Which I will discuss in a separate post)
  • You can find out the SSRS url either from the AX Development Workspace (Tools -> BI Tools -> Report servers, or the usual way – via SSRS Configuration Manager.
  • Save the report project into AOT.
  • Then deploy the reports. There are two ways – via Visual Studio (which provides more output messages which is useful for troubleshooting, or via the AX Development Workspace)

For more information of SSRS in Microsoft Dynamics AX 2012, visit: http://technet.microsoft.com/en-us/library/dd309644.aspx

First take on PowerPivot

Business Intelligence, PowerPivot

What is PowerPivot for Excel?

PowerPivot brings the power of Data Modelling into your PC.

Traditionally, the DataWarehouse is built on a database server (SQL) and a data model is built on top of it (SSAS). Then, a front end is used to present the data to users.

Now, data can come from different sources and pulled into the PowerPivot engine, relationships defined, calculations done and reports presented on Excel.


Continue reading →

Understanding the PIVOT function

70-461, Certification, SQL

The PIVOT function is very useful but not easily understood at first. I thank Itzik Ben-Gan, Dejan Sarka and Ron Talmage and their study guide Exam 70-461: Querying Microsoft SQL Server 2012 for the brilliant explanation.

Here’s their example replicated here. Remember there are 3 columns used in PIVOT. The Grouping column is what you want each row to be aggregated to. The Spreading column becomes the columns after PIVOTing, and the Aggregate columns are the measures.

You need a pivot aggregate function (SUM in this case) and COUNT(*) cannot be used but COUNT(<column>) can.

The reason a common table expression is used to select only the necessary columns is because all other columns beside the Spreading and Aggregate columns will become the Grouping element which may not necessary be what you want.

Continue reading →