Friday 21 November 2008

Off to the UK

Some of you may know that I am relocating to the UK in the new year. The reasons are many but primarily for the personal safety of my family as South Africa is becoming quite a dangerous place to live.

So I’m off to London tomorrow for 10 days to meet with some prospects and I believe that it is pretty damn cold at the moment. Wish me luck!

- Paul Steynberg

Tuesday 18 November 2008

PerformancePoint Server Planning and Management Reporter - Further Considerations

When I originally listed a few things that one should bear in mind when designing a PPS Planning Model which will ultimately be used for reporting via the Management Reporter I forgot to mention this little tit-bit of information.

One should be very careful in naming your Entity Dimensions especially if you require Management Reporter Designer to self generate your reporting tree definitions. As in our situation it is quite possible to have a number of entities that have unique labels but duplicate names or descriptions. So as an example you may have the entity called “Finance” in a number of your entity hierarchies pertaining to various companies or divisions within your structure. Because these are identified separately in PPS Planning due to the label being different the problem is not immediate. The problem becomes apparent when you try and import all your entities into a Management Reporter Reporting Tree. The system identifies that you have duplicate Unit Names and will not allow you to save the Reporting Tree. (Ours lit up like a red Xmas Tree)

How to fix this. Luckily it is quite easy to create your descriptions in SQL or Excel and then just copy and paste them into the Reporting Tree Grid in the designer. All we did was concatenate the label and the name with a hyphen in between from SQL. This way you are assured of name and description uniqueness in your reporting structure. Or make sure that your Entity dimension names are unique.

- Paul Steynberg

Saturday 15 November 2008

Interfaces to Dynamics AX 2009

A while back I penned about our dismal interlude into the BizTalk to Dynamics Application Interface Framework. I am led to believe that the problem was not with the AIF but with Dynamics and that it has been resolved. We however did not have any slack time in our very ambitious timetable to wait for it so had to take a different route.

I spent countless hours talking to people and browsing the Internet trying to find a robust way of creating interfaces and found absolutely nothing worth reading. Here is the route that I took.

I created a new Database specifically for interfaces into Dynamics AX. This Database is housed on the same server as our Dynamics Database. Within this database we then created a number of tables that are populated by our other systems within the organization. Based on our install I had 2 distinct types of interfaces. Invoices for Accounts Payable and Journals for the General Ledger. Most of the fields for both are shared anyway. Each system that was being integrated was assigned a "Source" code so that we would never lose track of where the data originated from. In a more complex environment I would suggest that you create a schema within the database for each source system. This will help with control and security.

Within Dynamics AX (DAX) we then created a new setup table and created a form called "External Interface Import Parameters". This table/form held the following information:

  • Import Type – A short identifier of the interface
  • Name – A full description of the external interface
  • External Server Name – The name of the server that housed the Interface Database being called.
  • Database Name – The name of the interface database mentioned above.
  • Journal Name – We set up a different journal name for each interface so that they could easily be identified when looking at the transactions in an account.
  • One Voucher Number Only – A bit switch. If set on this will create one voucher number for the entire interface run. If set off it will generate a new voucher number when it reaches a point of balance.
  • Stored Procedure – This is the name of the stored procedure within the database to call in order to get the data.
  • Write Back Table Name – The table that will be used to stamp back information once the interface has successfully run.
  • ODBC Source Name – We had to use ODBC connections due to the way the systems run from the client or from the batch server. (Some history here but it was necessary).
  • Batch Class Name – If this job was going to be called by the Batch server this is the name of the job to call.
  • Auto Post – If set on it will load and auto post the interface journal.

This information is then used repeatedly throughout the interfaces. Here are the steps that all interfaces go through:

  1. An interface call is made either from a menu item by an operator or from the batch manager in DAX.
  2. Based on the Import Parameters mentioned above the job will then call a Stored Procedure on the Interface database and will pass it the Company code as the only parameter. This is often referred to as the DataAreaId in DAX.
  3. The Stored Procedure fires on the Server and passes back to the job all the records for the interface.
  4. DAX then creates the Journal based on the Journal Name and will either auto post or leave it as unposted.
  5. When complete it will the write back to the table the Journal Number and Date/Time Completed.
  6. The batch job will keep firing for the company until the stored procedure returns no records back.

So what do the Stored Procedures do?

All records that are new have a TransactionStatus of "NEW". Those in the process of being posted are stamped "WIP" and those completed are stamped "FIN". The stored procedures finds records that are "NEW" and then selects the Top 1 record summarized by your extraction criteria into a table variable. As an example our POS Journals are summarized by Company, Trading Date, Currency and ExtractID from the LOB System. This table variable is then used to join back to the table to return the result set. As part of this procedure we also stamp the records as "WIP" with the date/time it was sent to Dynamics.

Each interface has been specifically written to meet the LOB criteria. I am busy working on a more generic system that will completely future proof the business. Once the framework is in place no additional development will be required. The only work required will be set up a record in the Import Parameters Table, set up a Journal Name, Number Sequence and just populate the interface tables.

I would be very interested to hear from other parties how they tackled this.

- Paul Steynberg