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
AX to D365
-
- Posts: 46
- Joined: Mon Jul 26, 2021 12:55 pm
- OLAP Product: TM1
- Version: 2.0.0
- Excel Version: Office 365
- 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
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
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
-
- 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
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.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
Thank you,
Michael