AX to D365

Post Reply
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

AX to D365

Post by michaelc99 »

Good Morning Everyone,

Currently, we have a process in place to manually extract Excel files from Microsoft AX and load into TM1 via Excel . At one point, I converted the Excel files into CSV and built TI processes to improve performance; however, I am only seeing a marginal performance improvement, so it has not been fully adopted. Though, we are in the early stages of implementing MS Dynamics 365. I am looking to connect TM1 to D365 via TI processes to feed TM1 and thus be able to utilize chores.

I am wondering, though, has anyone else followed a similar path to D365 and are willing to share their experiences? Did you end up connecting directly to D365, or did you use a Data Warehouse (or similar) as an intermediary step? Did you hit any roadblocks (performance or other)? Did you come across any pitfalls?

Thank you in advance!

Thank you,
Michael
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: AX to D365

Post by paulsimon »

Hi

This may be a bit out of date now, but 10 years ago we used TI to extract data directly via ODBC from the SQL Server database underlying MS Dynamics - it was just transitioning from being called Great Plains at the time. Not sure how much it has changed in Dynamics 365.

Anyway this is perfectly possible. From memory, during month end we set the chore to load data every 30 minutes, might have been even more frequent, possibly every 10 minutes. This was great for the accountants as they could post a journal and see an update in TM1 in the sort of timescales that they needed to close month end.

Not sure if this has changed but I think GL11111 is the historical balances table, while GL11111 is the current balances table. You want YEAR1 for the Financial Year, PeriodID for the Financial Period, then ACTNUMBR_1 ... N for the Chart of Account Segments. We subtracted DEBITAMT - CRDTAMT to get the net movement.

For Transactional Drill Thru it is GL20000.

I would check to see if they have defined views on the SQL Server DB as by now they might have created views to translate the numeric table names to something more meaningful.

At least the column names are relatively easy to understand, unlike some GLs I could mention.

Regards

Paul Simon
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: AX to D365

Post by michaelc99 »

paulsimon wrote: Sat Oct 30, 2021 12:20 pm Hi

This may be a bit out of date now, but 10 years ago we used TI to extract data directly via ODBC from the SQL Server database underlying MS Dynamics - it was just transitioning from being called Great Plains at the time. Not sure how much it has changed in Dynamics 365.

Anyway this is perfectly possible. From memory, during month end we set the chore to load data every 30 minutes, might have been even more frequent, possibly every 10 minutes. This was great for the accountants as they could post a journal and see an update in TM1 in the sort of timescales that they needed to close month end.

Not sure if this has changed but I think GL11111 is the historical balances table, while GL11111 is the current balances table. You want YEAR1 for the Financial Year, PeriodID for the Financial Period, then ACTNUMBR_1 ... N for the Chart of Account Segments. We subtracted DEBITAMT - CRDTAMT to get the net movement.

For Transactional Drill Thru it is GL20000.

I would check to see if they have defined views on the SQL Server DB as by now they might have created views to translate the numeric table names to something more meaningful.

At least the column names are relatively easy to understand, unlike some GLs I could mention.

Regards

Paul Simon
I appreciate the insight and advice. I will keep an eye out on GL11111 and transactional details in GL20000, as well as the fields you mentioned for building the SQL query. Separately, I am working on setting up Drill-though using SQL server but this may change/be upgraded as well once D365 is implemented.

Thank you,
Michael
Post Reply