Forecaster Database Transfer Tool Permissions

A shout-out to Jake Friedl at Microsoft for pointing us in the right direction on this one. If you are using Forecaster with Management Reporter, you probably have been faced with using the Forecaster Database Transfer Tool to move the budget information from Forecaster to Microsoft Dynamics GP so that you can report on it out of Management Reporter.


Generally, this works great and is pretty easy to use. However, the need to run the tool on a regular basis to get the latest numbers on reports makes me miss FRx and DirectLink-- I cannot believe I just admitted to that :) It may make sense to give your budget administrators permissions to use the Transfer Tool to update the balances whenever they need. Since the tool starts out with asking you for credentials for both the Forecaster and the Dynamics GP databases, you will need to set up the proper permissions in SQL Server for a non-administrator user to run the tool successfully.

Since these steps involved modifying stored procedures as well as permissions, it is recommended that you are comfortable with SQL Server before proceeding and have recent backups of your Dynamics GP company and Forecaster databases.

Also, another side note, the first time you access the Forecaster Database Transfer Tool, there are some SQL Server Objects that need to be created. So, the first user who uses the tool must have dbo or db_ddladmin roles in both the Dynamics GP and Forecaster databases. Otherwise, the user can use the Create to File function to send the scripts to an administrator to be run. I am NOT recommending that you give either of these roles to a non-IT administrator, just that you might want to have an IT/SQL administrator run through the transfer the first time to avoid these issues.
So, let’s start with the GP side of things….

1. The user login that will be used to access the Forecaster Data Transfer Tool needs to set up as a user in the SQL Server under Security-Logins. This can be a windows user, or a specific SQL user, depending on how you want to administer the account. It CANNOT be an existing Dynamics GP login. When you set up the user, they must be assigned to your Dynamics GP company database with the DYNGRP role in the User Mapping section of the Login Properties window.

2. You need to modify the stored procedure FCBUDGETIMPORT in your Dynamics GP company database to comment out the last section. To do this, expand your Dynamics GP company-Programmability-Stored Procedures and locate FCBUDGETIMPORT. Right-click and choose Modify. Once you comment out the last section as noted in the screenshot below, click the Execute icon at the top of the Query window. This modification ensures that an error will not be produced each time the stored procedure is executed.

3. Update the permissions for the DYNGRP role by running the following scripts, substitute your Dynamics GP company database for [GPDatabase] in the first two scripts.


USE [GPDatabase] GRANT ALTER ON dbo.EE_GL00201 to DYNGRP
USE [GPDatabase] GRANT EXECUTE ON dbo.FCBUDGETIMPORT to DYNGRP
USE DYNAMICS GRANT ALTER ON dbo.SA_Forecaster7ImportErrorLog to DYNGRP

On the Forecaster side, things are a bit simpler…

1. The user must be added to the Forecaster database with at least the db_datareader role. This is done by expanding Security-Logins-Right-click on user and choose Properties. Database and role assignment is in the User Mapping section.

Good luck! Let me know if you run in to any issues, and thanks again to Jake for sharing the scoop!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Update on Management Reporter Dimension Sets and AX

A quick update on an earlier post, http://dynamicsgpland.blogspot.com/2012/02/management-reporter-dimension-sets-and.html, on an issue with Dimension Sets not working when using Management Reporter with AX2012.  Turns out, it is now an official quality report and should be resolved in MR2012 coming in March.  Let's keep our fingers crossed!

The combination of not being able to use Dimension Sets and the 512 character limitation for the string created by the dimensions (e.g., [Main Account}= 200050) in a row, column, or tree...it makes for some interesting report design (lots of individual NP rows followed by a total) when you are working with a large/complex chart of accounts.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Importing 725,000 Records Into Dynamics GP In 30 Seconds

At the MS Dynamics World Decisions Fall 2011 virtual conference, I gave a presentation titled Importing 10 Million Transactions Into Dynamics GP.  While the project discussed in that presentation was challenging, I was recently faced with a slightly different high-volume integration challenge.

The client works with several large trading partners, each of which provides a consolidated monthly payment, along with a data file that is effectively the remittance advice for the payment, indicating how the check should be applied to thousands of customers.  But this isn't a typical cash receipt.  A single payment may be up to $4 million, and the remittance advice is a 140 megabyte text file containing 725,000 rows of detailed data relating to thousands of invoices.  And that is just one of their trading partners.

The accounting staff have been manually processing these massive data files in Excel, manually entering thousands of transactions each month, then manually applying the payments to outstanding invoices, and then manually entering adjustments or memos for over-payments or under-payments.  Due to the highly detailed format of the remittance data file and quirks in the data, there was no way to directly import the file using a tool like Integration Manager, and even if they did manage to import the cash receipts, they would still have to apply the payments and manually enter the adjustments.

Although I have developed automated integrations that can process tens of thousands of transactions over the course of a day, I don't think I've ever had to process hundreds of thousands of records at once--while a user was waiting and watching the user interface.  It was obvious that performance was going to be a challenge.

I thought about a few different designs, and came to the conclusion that the best approach would be to get the raw data into a SQL table first, and then process it further from there.  I briefly considered loading the entire file into memory and then import it, but I need to perform several operations and transformations on the data that would be impractical and inefficient using ADO.NET.

So, I need to load a 140 megabyte CSV file into SQL Server programmatically.  Although I loved DTS, I am not a fan of SSIS, so programmatically running an SSIS job to bring the file in did not appeal to me--not worth the hassle.  However, I do have a C# class that does an excellent job of parsing delimited text files, and is blazing fast (it can load the 140MB CSV file into memory in 2 seconds).  So, I can read the text file and get it into a DataTable in memory, but then what?  How do I push that massive data table into SQL?

Performing over 700,000 separate insert statements is clearly out of the question--I didn't even bother to test it since I knew that it would take far too long.  I needed something faster...much faster...

After doing a little searching, I came across the .NET SqlClient SqlBulkCopy class.  Faaaaantastic.  All of the brains of the famous bcp utility, but in a stylish .NET package.  Just 5 lines of code.

using (System.Data.SqlClient.SqlBulkCopy bulkcopy = new System.Data.SqlClient.SqlBulkCopy(gpConn))
{
    bulkcopy.BulkCopyTimeout = 300;
    bulkcopy.DestinationTableName = tableName;
    bulkcopy.WriteToServer(dataTable);
    gpConn.Close();
}

With those few magical lines of code, SqlBulkCopy pushes all 725,000 records into SQL Server in 30 seconds (on my relatively modest virtual server).

Because the client (thankfully) doesn't want to actually import 725,000 separate cash receipts, they summarize the data by customer and month, which results in 2,100 summary cash receipts.  Because I have the data in SQL now, I'm able to use a query to summarize the data.  But even there I run into a performance issue.

Because there is so much data to summarize, my query initially took several minutes to run, and sometimes even caused my .NET app to receive a command timeout error.  Having read many articles about SQL query optimization, I knew that my best bet was to create a covering index on my data table.  Sure enough, after adding the covering index, the query dropped to 4 seconds.

So in roughly 45 seconds, the 140MB file is loaded and converted into 2,100 cash receipt transactions, ready to to be handed off to eConnect.  eConnect then becomes the bottleneck, taking a few minutes to create the cash receipts, apply the payments to invoices, and then create debit and credit memos for any over or under payments.

Needless to say the customer is amazed and thrilled with the new solution.  Compared to the manual process that they have been performing to get the data into GP, the custom integration is like magic.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com

Update Dynamics GP Notes Using SQL

In the last few weeks I've had two integration projects that required me to update notes in Dynamics GP.

One customer needed to import note updates from another system into existing Service Call notes in Dynamics GP.  The external system was able to provide the data in a SQL staging table, and I had to import the note data into Service Calls via SQL, appending the information to existing notes, or creating new notes if they didn't already exist.

The second was an eConnect 2010 RM cash receipt import where I needed to attach a note to some of the cash receipts.  Imagine my surprise when I realized that the RM Cash Receipt eConnect transaction does not have a NOTETEXT field!  Why not???  eConnect inserts a cash receipt record with a NOTEINDX value of zero!  Who's the summer intern who cut corners?

I looked for a stored procedure that might handle the note inserts and updates, but a trace of the GP windows seemed to indicate that GP just used straight INSERT and UPDATE statements.  But the GP note windows always send the full note text in the SQL for their updates, which is straightforward.  I needed the ability to insert a new note as well as append text to an existing note--ideally without retrieving the existing text first.

So I searched for a note insert or update stored procedure in the GP databases but found no such thing.  After a few attempts to find a solution online, I gave up and decided to roll my own.

It has been years since I have had to deal directly with SQL Server text fields.  Although it isn't "hard", the syntax to update a text field is far from obvious.  But with minimal Google-fu skills, you will find plenty of examples online.

To make my life easier, I packaged up my script into a stored procedure that accepts the note index and the note text to append. Also for simplicity, I use a varchar(8000) parameter for the text update.  And finally, I include newline characters to add white space before note updates.

IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name = 'csspInsertUpdateNote')
    DROP PROCEDURE csspInsertUpdateNote
GO

CREATE PROCEDURE csspInsertUpdateNote
    @NOTEINDX AS int,
    @NOTETEXT varchar(8000)
AS
BEGIN
  
    SET NOCOUNT ON;
    DECLARE @TXTFIELD varbinary(16);
    DECLARE @LENGTH int;
    DECLARE @CRLF varchar(10);
    DECLARE @APPEND varchar(8000);
    DECLARE @GPVERSION int;
   
    --GP 2010 uses CRLF for notes, whereas GP 9 and 10 use CR only
    SELECT TOP 1 @GPVERSION = versionMajor FROM DYNAMICS..DU000020 WHERE COMPANYID = -32767 AND PRODID = 0
    IF @GPVERSION >= 11
        SET @CRLF = CHAR(13)+CHAR(10);
    ELSE
        SET @CRLF = CHAR(13);
       
    SET @APPEND = @CRLF + @NOTETEXT;

    --Check if a note record exists
    IF (SELECT COUNT(*) FROM SY03900 WHERE NOTEINDX = @NOTEINDX) = 0
        BEGIN
            --If not, insert a new note record with the update
            INSERT INTO SY03900 (NOTEINDX, DATE1, TIME1, TXTFIELD) VALUES (@NOTEINDX, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())), CONVERT(VARCHAR(8),GETDATE(),108), @NOTETEXT)
        END
    ELSE
        BEGIN
            --If so, update the existing note
            --Get the text pointer for the note
            SELECT @TXTFIELD = TEXTPTR(TXTFIELD), @LENGTH = DATALENGTH(TXTFIELD) FROM SY03900 WHERE NOTEINDX = @NOTEINDX;

            UPDATETEXT SY03900.TXTFIELD @TXTFIELD @LENGTH 0 @APPEND;
        END

END
GO

GRANT EXEC ON csspInsertUpdateNote TO DYNGRP
GO

And if you have to generate note indexes like I did for the RM Cash Receipt transactions, you can use the DYNAMICS.dbo.smGetNextNoteIndex stored procedure.


Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com

Management Reporter Dimension Sets and Dynamics AX

Okay, okay, I am not AX knowledgeable...but, I have been working on some Dynamics AX implementations recently with Management Reporter.  And I came across an issue that I thought I would share in case there are others out there experiencing the same thing.

We created a number of reports with dimension sets, prior to their being data in the system.  So when their was data loaded, we started running the reports...and there were no numbers! Yikes.  We isolated that the rows without data were all using dimension sets.  And if we used the actual dimensions instead of dimension sets, we would get data. 

Erik Johnson from Microsoft took a look with me today, and confirmed that it seemed to be affecting any dimension set with more than one dimension in it.  So, for example, if I set up a dimension set with one department only or one range of departments, it works.  More than one department it doesn't.

It seems like it may be an issue with the most recent version of the data provider for Dynamics AX, as it can't be replicated on earlier versions of the provider.  The version that seems to be affected is 2.0.1700.75.

I'll share more info as I get it!  Have a great weekend!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Why care about User Classes?

With recent version of Dynamics GP, the user security model shifted away from user classes to security roles, tasks, and operations.  So, I am often asked why worry about user classes now? 

Microsoft Dynamics GP-Tools-Setup-System-User Classes

Users are assigned to a class in Setup-System-Users

Well, user classes still have a number of benefits in Dynamics GP.  I thought I would take you through some of them.

1. You can assign shortcuts to an entire user class.  Simply log in to Dynamics GP as 'sa', and you will see on your navigation pane a folder for each user class.  Add a shortcut in to one of those folders, and it will appear on the navigation pane for all users that belong to the class.
2. If you are using Account Level Security/Organizational Structures, you can assign an entire class to a branch of the organization structure, Cards-System-Organizational Tree.  This is especially useful when users are added to Dynamics GP, you can place them in to the correct class and they will automatically receive the associated account level security rather than having to assign their user individually to the organizational structure.
3. If you use project accounting, you can restrict access to certain processes and activities in project accounting by user class.  Microsoft Dynamics GP-Tools-Setup-Project-User Classes.  You can control things like being able to change project status, change the billing type of a line item, or add a budget line item "on the fly" from a transaction.

Please feel free to share your own continued uses for User Classes.  These functions may not be important on all implementations, but in some cases User Classes can still be very helpful!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Management Reporter- Using Dimension Sets for Report Design Efficiency

Every calendar year end, I feel like all I can come up with are payroll-related blog posts.  So, I am trying tonight to come up with something, anything other than payroll topics.  Thinking through the projects on my plate lately, Management Reporter is a common theme.   When working on large report projects in Management Reporter, I always try to find ways to make the report design (and therefore ongoing maintenance) more efficient.  One of the simplest way to do this is through the use of Dimension Sets.

Back in the FRx, these were called Account Sets and allowed you to group accounts for use on multiple reports.  And in Management Reporter, the functionality is similar.  You can create dimension sets to bundle groups of dimensions (any particular segment of your GL) in to named sets to be referenced in row, column, or tree definitions.

The benefits of using dimension sets include:

• Reference the same dimension set on multiple row, column, and/or tree definitions
• Have clients configure dimension sets in advance of report design, to simplify the report building process
• Update a dimension set, and all reports that reference the set are automatically updated
• Add, subtract, include ranges in a dimension et
• Simplify the "look" of a row definition, in particular, to make it easier to spot what is included in a row
• Centralize maintenance of key dimension sets referenced on several reports, like Net Sales or Administrative Expenses

Within a row, column, and/or tree definition you can reference:

• Multiple dimension sets (for example, multiple Account dimension sets, or an Account dimension set and a Department dimension set)
• Combinations of dimension sets and other dimensions (for example an Account dimension set plus a department segment)

Of course, dimension sets do require you to think through the design of a report before you begin actually creating it.  And it is even more beneficial if you analyze all reports to be created, to find the common groupings that can leverage dimension sets.

Once you have identified the dimension sets, it is very easy to create them in Management Reporter.  With any building block (row, column, or tree) open, simply go to Edit>>Manage Dimension Sets.

First, select the Dimension for the set using the drop-down list, you can select any segment of your chart of accounts (for example, main account or department or division).  Then you can either select an existing Dimension Set and click Modify or click New to begin a new one.  Note that you can also Delete and Save As for existing dimension sets.

After you click New, the New dimension set window appears and you can enter a Set Name and Set Description.  Then you can specify the segment values and ranges that should be included in the Dimension Set.  Use the + and - signs to add or subtract additional segment values and ranges within the set.  Click OK to save the dimension set.

To use a dimension set, you need to access the Dimensions window from either a tree, row, or column building block.  For example..

• In a row definition, double-click in the Link to Financial Dimensions field
• In a column definition, double-click in the Column Restriction field
• In a tree definition, double-click in the Dimensions field

Once you are in the dimension window, double-click in the segment/dimension field you wish to populate and the corresponding segment/dimension window will open (in this case, MainAccount).  Now, you can select the Dimension Set radio button and use the ellipsis (three dots) button to select from a list of available Dimension Sets for the selected segment/dimension.  You can also use the Manage Dimension Sets button as a shortcut to edit and create new Dimension Sets. 

Click OK to save the entries you have made, and then you can repeat the process for additional dimensions in the same window, or move on to another row, column, or tree branch on your report.  Now, if you need to add an segment value to Net Sales or to the Domestic Divisions rollup, you can simply edit the Dimension Set and all reports that reference the Dimension Set will be updated!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.