Thursday, 31 March 2016

Setup Acumatica and MS Sql Database on the Same Server

Hi All,

When you plan to how to better install small edition of Acumatica (edition for 2 cores), you may notice that most of all modern servers usually have 4-8 and more cores. And what to do if Acumatica just uses 2 cores from 8?
Acumatica uses 2 cores from 8

In this case you may want to install Database Management Server on the same machine. It is good option, as you can utilize other "unused" cores.
Usually Acumatica recommends to have 2 separate servers for application and database and this is totally better and more stable configuration. So every-time when you can setup 2 separate servers do it.

But Acumatica totally supports configuration with single server, in this case you just need to make sure that you have correct configuration:

  1. Limit memory for SQL server for not more than 50% of total server memory. Sql server can use unlimited memory on server, but sometimes it may affect Acumatica application.
    SQL Server Memory Usage
  2. Explicitly set cores that should be used for SQL Server. This will help to dedicate some cores to Acumatica.
    SQL Server CPU Restriction
  3. Explicitly set cores that should be used by IIS worker process. This option should be specified on application pool advanced options. Please note, that when you configure it, all applications within the same application pool will use the same cores. It is good idea to put all test sites to separate application pool and specify different cores. 
    If you set the value to 1 (which corresponds to 00000000000000001 in binary), the worker processes in an application pool run on only the first processor. If you set the value to 2 (which corresponds to 0000000000000010 in binary), the worker processes run on only the second processor. If you set the value to 3 (which corresponds to 0000000000000011 in binary) the worker processes run on both the first and second processors. So if i want to use just 4 and 5 cores (note that they are counting from 0) I need to specify 0x30 = 48 to affinity mask.
  4. Do not forget to back up your server after configuration, to simplify disaster recovery.
There is one good blog article on MSDN that can help you to calculate affinity mask.

Have a nice Installation!

Wednesday, 30 March 2016

Database Data Saving Test

Hi All,

Some time ago, one client asked me: "How fast can you upload data into the database". Unfortunately I had no answer for this question and actually no one has answer for this question. The problem here is just in the question itself - it has not enough information:
  • What data (documents) do you want to upload?
  • How many documents do you have?
  • How many lines per document do you have?
  • How many field per single line do you have?
  • How many Business Accounts do you have in the system?
  • How many Documents per Business Account?
  • Do you have Field-Level Audit?
  • Do you need to release documents right after import?
  • What do you want to do with this data after import?
  • Will some one else use system during import?
  • How do you plan to import data? 
    • Integration Services?
    • Web Service API?
    • Customization with direct import?
  • Do you plan to use multiple thread?

So there are a lot of questions. And especially it is very important to understand what type of data do you want to import. Sales orders will be definitely much slower that GL Transactions.

Here I want describe an optimistic scenario of importing simple tables into the database direct through Acumatica Framework. In this case we reading file through the code and uploading it directly to business logic container. We are personally responsible for correct flow of running data validation.

For testing I have prepared 2 different scenarios:
  • Uploading of data from Excel file directly to one table with some limited validation
  • Uploading of data into Journal Transactions as a separate batches with 500 or 1000 details lines.
IF you are interesting in result, welcome in this article details.

Tuesday, 29 March 2016

Read Excel File In Acumatica

Hi All,

Today want to share with you the easiest way to read Excel or CSV file in Acumatica.
Acumatica core (PX.Data.dll) contains 2 classes that may help you with reading some files:
  • PX.Data.XLSXReader - can read Excel file row by row,
  • PX.Data.CSVReader - can read any CSV file row by row.
In my example today we will read transactions from excel file and import it to some table.

Code snippet:
Just notice here several important things
  • Do not forget to close reader by calling Dispose method
  • Excel or CSV files may contains first row with headers - you need to skip it manually.
  • You need to read data from reader by indexes, if you want to read it by names, create a dictionary with mapping index and name by first row. Reader can do it automatically if you call Reset() method.
  • MoveNext() method will move cursor to next row.
  • Sometimes you have external data representation in the file and internal in DAC (Like with Accounts - CD in UI and ID in Database). You can use Cache.SetValueExt(...) method to convert external representation to internal.
  • Note that all Acumatica fields are nullable, so "" is not equals to null. Make sure that you reading empty columns correctly. You can use String.IsNullOrEmpty(...) method to validate for empty sting.

Have a nice development!

Process File In Memory

Hi All,

Sometimes you want to upload file to Acumatica but you do not want to save it to the database. This may requires for in-place data processing or uploading data from specific format.
Today I want to show you one way, how you can allow user to upload file in the browser and handle it with your server logic.
Acumatica Upload File Dialog
In high level, to achieve it you need to do 3 steps
  1. Add special PXUploadDialog panel to your page. This control will handle file upload and save somewhere. By specifying configuration properties, you can control where Acumatica will save a file. In our case we want to store it in session.
  2. Defile dummy data member that will be associated with upload dialog. Buy calling Ask() method here, you will trigger panel and user will be able to upload file.
  3. Add button and call dummy data member from previous step. After uploading Acumatica will automatically call your method second time, and then you will be able to get file from session.

Monday, 28 March 2016

Multiselect Combo Box

Hi All,

Today I want to discuss with you one short topic - how to implement Combo box with multiple options for select.

As an example I want to use scenario when we need to have a department associated with each particular customer. We have fixed and predefined number of departments so we do not want to configure them. Plus each customer can be associated with multiple departments, so we need to have a multiple options simultaneously.
Based on provided requirements, we can find that Combo Box (Drop Down control) is the best option here.
Acumatica Multiselect Combo Box

To do this I will use Acumatica customization engine. Lets go there and do it step by step: