Stephen Smith's Blog

All things Sage 300…

Posts Tagged ‘sage 300

Installing the Sage 300c Web UIs

with one comment

Introduction

In my last post I introduced the new Web UIs for Sage 300. This blog post is on installing the new Web UIs for Sage 300. The product has just gone to beta so I thought it might be helpful to provide a few details on the installation process. I also included details about some rough edges in the beta installation. I’ll update this article as we go along with any problems encountered and helpful workarounds discovered. As we go to final release, I’ll clean up this article to represent the final released state.

For testing all the new features in the classic VB UIs then the normal installation procedures apply. This article is specific to the Web UIs.

Prerequisites

The beta only officially supports Windows Server 2012R2 and SQL Server 2014. By release we will support other versions of Windows and our usual suspects for SQL Server. This only applies to the Web Server where you want to host the Web UIs. All the regular versions of Windows are supported for Workstation setup type installs to run the VB UIs. For accessing the Web UIs from other computers and devices just ensure you are using the latest version of Edge, IE, Firefox, Safari or Chrome.

The main prerequisite is that you must install the Web Server (IIS) server role from the Server Manager. Then you should add all the Application Development sub features. The key ones being that we need the .Net ones and the ISAPI ones.

serverrole

Installer Options

The new Web UIs are an option in our installer. They are checked by default, but you only want to install them on the Web Server where they should run from (similar to the older Portal option).

wsinstall

When you select this the files for these will be added in an online folder under the folder you decided to install to. A number of Windows features will be enabled like .Net 3.5 (needed for Crystal) and Message Queuing. Virtual folders and application pools will be added to IIS. No manual configuration or adjustment of IIS is required.

Database Setup

With Sage300 version 6.0A we introduced a Portal database. This held 4 tables required for the 6.0A Portal. The new web screens also need some database tables. We added them to the Portal Database so that you don’t need to worry about yet another SQL database. So like previous versions you need to create a Portal database in SQL Enterprise Studio, and then run Database Setup to configure it from the “Portal…” button. The dialog from database setup is exactly the same as it was in previous versions.

One difference is that the older Portal populated its tables when it ran. The new Web UIs need a few things populated ahead of time and this is done via Database Setup. So if you delete and re-create the Portal Database then you must run Database Setup again to get these tables re-populated.

Running the Web UIs

Now you should be ready to run. On the server there will be start menu link to the new Home Page. For other computers it will be //servername/sage300. You can use the IP address of the server if you don’t have a proper DNS entry for it.

By default we configure IIS to run these from HTTP. If you expose these to the external internet, make sure it is either via VPN or you enable HTTPS (you need a digital certificate) and turn off the HTTP port in IIS.

Beta Issues

The beta still has a few issues so I thought I would list them all here to put them in one place. I may delete this section when the product ships to avoid confusion once these issues are resolved.

  • You must use Windows Server 2012R2 and SQL Server 2014. Installing in a virtual image (like Virtual Box) is fine.
  • You can’t install both the existing (Orion) Portal and the Web UIs on the same web server. Right now the two Crystal runtimes will conflict and Crystal reports won’t work for one or the other.
  • Database Setup has a bug where the first time you select the Portal… options, you will get an error about dbconfig.xml not existing. Hit Ok again and it should be created fine.
  • After running Database Setup you need to go to Control Panel – Administrative Tools – Services and restart the Sage.CNA.WindowsService service or functions like posting and import/export may not work.
  • Web UIs that use the system link won’t save properly, this includes Security Groups, Authorizations and the Currency functions.

Diagnostics

One problem with the previous Web versions of Sage 300 is that DBSpy and RVSpy didn’t work on Windows Services (starting with Windows XP). Now these programs have been updated to spy on Sage 300 business logic that is run from a Windows service like IIS. So these tools are quite helpful in diagnosing problems in this new Web UI environment.

There are two main components for the new Web UIs both of these are installed under the online folder for the Sage 300 program files. Each has its own diagnostic log which provide useful information when things go wrong (there is also quite a bit of audit information logged here).

Under the online folder the web folder contains all the files and programs that comprise the ASP.Net MVC program that runs from IIS. Here there is a logs folder which contains a trace.log file which will contain various audit information as well as much more detail on any problems that occur in the system.

Under the online folder the worker folder contains files and programs that make up the Sage.CNA.WindowsService. This service processes any long running jobs like posting functions, create new year and import/export. This to make the web server more responsive to other users when someone is running a long running process. Here there is a logs folder that contains a trace.log file which has detailed diagnostic information when something goes wrong here.

folders

If you want to just reset the Web UIs programs, you need to restart the World Wide Web Publishing Service (IIS) and the Sage.CNA.WindowService Windows Services. Note that the Sage 300 Tomcat and Sage 300 .Net Remoting services are still there, but these aren’t used by the new Web UIs.

Summary

We a really excited that the first beta of our new Web UIs is shipping and we are looking forward to getting feedback and input ahead of the main general release in October. The earlier we can get feedback the sooner it can be incorporated into the product. Hopefully this article will help you get up and running and trying out these new screens.

Written by smist08

August 2, 2015 at 6:41 pm

Sage 300 Moves into the Browser

with 16 comments

Introduction

Probably one of Sage’s worst kept secrets is that we’ve been working on a true Web version of Sage 300. Now finally at Sage Summit 2015 the cat is out of the bag and the product is officially announced as Sage 300c. You can see the announcement in this video.

We will be including Web UIs for G/L, A/P, A/R, Bank, Tax, C/S and A/S screens in the upcoming Sage 300 2016 release which is slated for October 2015 (where the year in the product roughly matches car model years).

These are true web screens that run in all the main browsers including IE, Edge, Chrome, Firefox and Safari. There are no plug-ins required, everything is pure HTML, CSS and JavaScript. This means you can access these screens from all sorts of devices like Macs, Windows Desktops and Laptops, Chromebooks, iPads, Android Tablets, Linux Workstations, Raspberry Pi’s and many other devices.

The operations modules including I/C, O/E and P/O will follow shortly after early in 2016 as part of a Product Update.

New PDX Treatment

We have a new Home Page (like the traditional desktop) and all redesigned screens.

Sage300Home

There are 12 KPIs that you can choose from to put on your home page. You run all the screens from the menu and can move between open screens using the little Windows widget on the right.

People familiar with Sage 300 ERP will pick up this new treatment very easily since it is still the same application, just re-imagined to fit into a modern Web environment.

cna2arinv

All the familiar elements are there including finders, import, export and printing via Crystal Reports. There are a lot of extra visual cues to help make the screens easier to learn and use. For instance you don’t have to know to hit tab when in a key field to get it processed, you can just hit the little go button next to it (but of course hitting tab still works as well). In the grid you don’t need to know to hit the Insert key to create a new line, there is an explicit “Add Line” button at the top of the grid.

The controls aren’t as packed together as they were in the classic VB UIs. This is to make it easier to use the screens on touch devices like tablets. Now it’s much easier to touch buttons and controls with your fingers when you don’t have the fine control of a mouse. Although these screens work quite well on tablets, it isn’t recommended you use them on a phone since this then involves a painful amount of scrolling and having a more responsive design for phones is still a version or two away.

Hybrid Offering

If you upgrade to this release, you don’t need to run the Web UIs. You can still run all the classic VB UIs. In fact you can have some users running the new Web UIs and some users running the classic VB UIs. Since everything goes through the same business logic (Views) you can mix and match as you like. Of course for modules like PJC where there aren’t any Web UIs yet, then you must run the VB UIs.

There isn’t a full Web based Financial Reporter yet, so Financial Reporting is still done though the usual built in F/R that is included with G/L or via Sage Intelligence. Both of these are based on the on-premise version of Excel.

Many customers rely on customized versions of the Sage 300 screens. There won’t be a customization toolkit with the first release, it will be provided later. Even if it was available with the initial release, not everyone would want to immediately port their customizations to the new technology. It takes time overcome the learning curve and become proficient in the new technologies. To help with this scenario we provide a tool that lets you hide any UIs in the Web home page, so that users won’t accidentally run the Web UI when you want them running the VB UI (probably due to customizations).

New Technology Stack

This new architecture is based on using standard ASP.Net MVC along with re-using the standard Sage 300 Business Logic. Below is an architectural block diagram of the new components that sit on top of our traditional business logic.

cna2arch

To speed bringing the product to market we were careful to stick to standard off the shelf technology components rather than creating out own. For instance the UI widgets like the editable grid, the graphical charts and the date picker are from the Kendo UI toolkit. We use knockout.js for databinding. We make extensive use of jQuery and leverage quite a number of other standard technologies that are all tried and true in many large scale web applications. All the server code is written in C# and all the Browser code is written in JavaScript.

You can see that there is a Wrapper layer than translates the world of Sage 300 Business Logic into the world of ASP.Net MVC. This way our application is a very standard implementation where the Wrapper layer exposes the standard Sage 300 business logic in more modern interfaces allowing the usage of newer languages and technologies like LINQ in the higher layers.

Lots of Questions

I imagine many people have lots of questions on everything that is going on. This is the first of many articles on the new Sage 300 Web UIs where I’ll be going into much more technical detail. But if you leave questions in the comments of this article, I will endeavor to either answer them or make them the topic of a future posting.

Summary

We are really excited to finally be launching our Web UIs for Sage 300 into the real world. This is a first step in fully modernizing the product and providing a strong foundation for future development.

Our R&D department has been operating using Agile development methodologies and a continuous delivery process. After the first release we are looking to release upates very frequently to add value very quickly to this rather large base release.

Written by smist08

July 29, 2015 at 12:40 am

Performance and the Sage 300 Views Part 2

with 2 comments

Introduction

Last week we discussed avoiding table scans when using the Sage 300 ERP APIs. This week we are going to look at some other issues to do with updating data and with processing meta-data.

Last week I showed a cheetah running as an example of performance and speed (the fastest land animal), but this week here she is resting and getting some attention.

cheetah3

AOM/UI Info/ViewDoc

First, just if you are wondering where to find out what indexes a View supports, there are quite a few tools to determine this. Plus you can always look in SQL Management studio, but then you won’t know which index it is by our numbering scheme. Anyway ViewDoc is a good tool that comes with the SDK that gives this information. UI Info comes with System Manager and can drill down through the UI Info to get detailed View Info. Then there is the Sage 300 Application Object Model (AOM) located here. Just note that to use the AOM, you must use Internet Explorer for some obscure reason.

Updating Data

Often if you are manipulating lots of records it’s in a header/detail situation. In this case all the database operations are done when you insert or update the header. The nice things about this is that the Views know a lot about our database API and will do this in an optimal manner so you don’t need to worry about it. Similarly if you delete a header, the View will delete all attendant details for you in an efficient manner.

But suppose you want to update a bunch of records using our .Net API and want to know the most efficient way to do this. Say we want to add something to the end of every A/R Customer Name. Our easy brute force way to do this would be:

arCUS.RecordClear();
while (arCUS.Fetch(false))
{
arCUS.Fields.FieldByName(“NAMECUST”).SetValue(
arCUS.Fields.FieldByName(“NAMECUST”).Value + “A”, false);
arCUS.Update();
}

 

This works but you might find it a bit slow. We can speed it up quite a bit by bracketing the whole thing in a database transaction:

mDBLinkCmpRW.TransactionBegin();
arCUS.RecordClear();
while (arCUS.Fetch(true))
{
arCUS.Fields.FieldByName(“NAMECUST”).SetValue(
arCUS.Fields.FieldByName(“NAMECUST”).Value + “A”, false);
arCUS.Update();
}
mDBLinkCmpRW.TransactionCommit();

 

The times from the sample program (the same one as last week but with a bit added) is:

Time to update all customers: 00:00:00.087
Time to update all customers in a transaction: 00:00:00.038

So putting things in a database transaction helped. This is for Sample Data so there are only a few customers. The updated sample program is the PerformanceSamples project located here (both folder and zip file).

Database Transactions

Generally when using our API you don’t need to worry about database transactions, but occasionally like in the above example they are necessary. In the above example the first method has the side effect that each update is done in a separate transaction. That means you have the overhead of starting and committing a transaction with every record update. In the second example we start a transaction so all the records are committed as a single transaction. Strictly speaking the two examples don’t do the same things, if the first example throws an exception part way through then all the updates done up to that point will be in the database, whereas in the second example they will be discarded since the transaction will be rolled back. This difference can be quite important if there are database integrity issues to consider. Generally Sage 300 ERP uses transactions to go from one state where the database has full integrity to another. This way we can rely on database transactioning to always maintain full database integrity.

There is overhead to setting up and committing a transaction, but there are also resources used for every operation done inside a transaction. At some point the above example will start to slow down if you have too many A/R customers. Generally you might want to commit the transaction every thousand customers or so for optimal performance (but make sure you maintain database integrity along the way).

Also keep in mind that while records are updated in a transaction they will become locked from the point of update through to the end of the transaction, so updating a lot of records in a transaction will lock a lot of records and cause anyone else going to read that record to have to wait until your transaction completes. So try to keep transactions quick. Definitely don’t do any UI type operations in the middle of a transaction (like asking the user a question).

Revisioned Views

Revision List type views will store all insert/updates/deletes in memory until you call Post. Generally these are detail views and you don’t see this functionality because it’s handled by the header. But occasionally you may need to deal with one of these (like perhaps GLAFS). In this case since each Post is a transaction, you just need to be aware of how often you call it as this will have the same effect on performance as mentioned above.

Deleting

Although you can delete records as above just replacing the Update with a Delete call, there is a better way. The Views have a FilterDelete method where you pass in a browse filter and all the records that match will be deleted. This will prove to be quite a bit faster than the above.

Meta-Data

If you run RVSpy with all the View calls selected you will see a lot of meta-data calls, getting information on fields and such. Generally meta-data calls are quite fast and don’t involve going to the database. However if you really go crazy you can slow things down quite a bit. If you make everything dynamic then you could end up making lots of meta-data calls and cumulatively these slow you down a bit. Similarly using constants in things like getting fields are slightly faster than passing field names because you avoid a dictionary lookup (admittedly quite fast but not as fast as direct access). Mostly people exercise good judgement and don’t go too wild driving everything from meta-data, but we have seen some crazy cases.

Summary

Just a quick overview of some performance tips. Hopefully these all help to make your use of the Sage 300 API more efficient.

 

Performance and the Sage 300 Views Part 1

with 5 comments

Introduction

The Sage 300 ERP Views (Business Logic) give you a great deal of power to perform Accounting operations through our various APIs. However as in any programming, performance must always be taken into account. The Sage 300 ERP Views have a lot of features to help you perform operations with good performance, but like anything if they are used incorrectly, performance can be miserable.

This article is going to talk about various features and options that you can take advantage of to improve your application’s performance. As I am writing the article, it’s getting quite long, so I think I’m going to break it into two parts.

cheetah2

Measure and Test

One of the big mistakes people make when performance tuning, is to just make assumptions and changes without doing real measurements. If you have your code in a source control system, first establish a base line for how long something takes, then make you changes and re-measure the time. Only check in your changes if the time is faster, if it isn’t then you are just churning your code and potentially adding bugs. Performance is subtle and often the best ideas and intentions just make a process slower.

Multi-User versus Single-User Performance

This article is about optimizing processes for single users. Often if you want to optimize better multi-user throughput then it’s all about reducing locks and keeping resource usage down. Sometimes these goals align, i.e. 1 person doing something quicker translates to 100 people doing things quicker, sometime they are opposing, i.e. one person can do something way quicker if he takes over all available resources at the detriment to everyone else.

Read-Only versus Read-Write

You can open our database links and views either in read-write mode or read-only mode. Generally if you aren’t updating the data then you want to open in read-only mode as this makes things quite a bit faster. If you might update the data then we have to use more expensive SQL operations so that if you do update the data, the update is fast and multi-user considerations are handled. If you open a table or link read-only then we use much lighter weight SQL operations and the data is returned much quicker. Finders use this to display their data quicker.

FilterSelect/FilterFetch versus Browse/Fetch

When you Browse/Fetch you can always update or delete the record fetched. As mentioned above that can introduce extra overhead and slow things down. Making the table or link read-only will help Browse/Fetch, but perhaps a better method is to use the FilterSelect/FilterFetch methods which are better optimized for SQL Server than Browse/Fetch. The results from these can’t be updated or deleted but at the same time the access method is always light weight whether the link is open read-only or read-write.

Indexes

Sage 300 will always use an index to read data. We have a lot of code to optimize access based on available indexes. If you use the indexes provided your code will be much faster.

For example, suppose you want to know if there are any open G/L Batches. A quick bit of code to do this is:

glBCTL.Browse(“BATCHSTAT=1″, true);
bool isOpenBatch = glBCTL.GoTop();

This works pretty good on sample data, but then you go to a client, suddenly this becomes quite slow. The reason is that since BATCHSTAT isn’t part of the primary index, the GoTop basically goes looking through the Batch table until it reaches the end or finds an open batch. Since open batches are usually at the end, this tends to be sub-optimal. Practically you could speed this up by searching through the table backwards since then you would probably find one quicker, but if there are no open batches you still search the whole table. Fortunately there is a better way. The GLBCTL table has two indexes, one is its primary default index of BATCHID and the other secondary index is on BATCHSTAT and BATCHID (to make it an index without duplicates). So it makes sense to use this index:

glBCTL.Order = 1;
glBCTL.Browse(“BATCHSTAT=1″, true);
isOpenBatch = glBCTL.GoTop();

Simple adding the Order property makes this search much quicker. I included a sample program with timers and the full code. The results on sample data show the speed difference (not that it was all that slow to start with):

Time to determine if there are open batches: 00:00:00.034
Time to determine if there are open batches take 2: 00:00:00.007

The sample program is located here. Its PerformanceSamples one (folder and zip).

So generally you want to use an index that matches the fields that you are searching on as much as possible. Usually having clauses in your browse filter that uses the index segments from left to right will result in the fastest queries.

This example may look a little artificial, but once you get into the operational modules like O/E and P/O this becomes crucial. That is because the main tables like the Order Header have a uniquifier as the primary index. When you want to look something up it’s usually by something like order number and to do this efficiently you have to use an alternate index. So once you are using these modules you will be using alternate indexes a lot. In these modules also be careful that quite a few alternate indexes allow duplicates, so you might get back quite few records unexpectedly.

RVSpy/DBSpy

RVSpy and DBSpy are good tools for identifying bad behavior. The logs contain time information so you can see where the time is being used, but more often than not doing something bad for performance results in a series of operations appearing over and over in these logs. Usually scrolling to the middle of the output file is a good way to see something going awry. You can also use SQLTrace or ODBCTrace, but I find these slightly less useful.

When using RVSpy for this purpose, it helps to turn off logging to a Window (slow) and only log to a file (make sure you specify one). Further choose the View calls you want to log, usually disabling anything to do with meta-data and anything that is field level.

So if you see output like:

[5b8.7ff.37b0] CS0003: CSCCD    [01:12:06.58].Fetch(view=0x2F1047AC)
[5b8.7ff.37b0] 0 <==[01:12:06.58;t=0;ovh=0] {}
[5b8.7ff.37b0] CS0003: CSCCD    [01:12:06.58].Fetch(view=0x2F1047AC)
[5b8.7ff.37b0] 0 <==[01:12:06.59;t=0;ovh=0] {}
[5b8.7ff.37b0] CS0003: CSCCD    [01:12:06.59].Fetch(view=0x2F1047AC)
[5b8.7ff.37b0] 0 <==[01:12:06.59;t=0;ovh=0] {}
[5b8.7ff.37b0] CS0003: CSCCD    [01:12:06.59].Fetch(view=0x2F1047AC)
[5b8.7ff.37b0] 0 <==[01:12:06.60;t=0;ovh=0] {}
[5b8.7ff.37b0] CS0003: CSCCD    [01:12:06.60].Fetch(view=0x2F1047AC)
[5b8.7ff.37b0] 0 <==[01:12:06.60;t=0;ovh=0] {}

Going on for pages and pages then you have something wrong.

Avoid Table Scans

Most of this article is about avoiding table scans, but just to re-iterate table scans are bad. People are often fooled by testing on sample data. Many of the tables in sample data are quite small and it doesn’t really matter what you do. However in the real world with real customer databases things will usually be quite different. For instance sample data has 9 tax authorities, which you might think is reasonable. But in the USA where any municipal government agency can charge a sales tax, there are over 35,000 tax authorities. If you read all these (like to populate a combo-box to pick one from), then you will run very slowly and your customers will be unhappy.

Summary

Sage 300 ERP has many mechanisms to access and manipulate data efficiently. But as with anything in programming, if you use APIs without due care and attention then performance (and quality in general) will suffer.

Written by smist08

March 10, 2015 at 9:44 pm

Drilldown in Sage 300 ERP

with 3 comments

Introduction

Much accounting detail is entered in one application and passed on to another for recording. Drilldown is the ability to reverse the audit trail and display, application by application, the document back to its original entry into the Sage 300 ERP system. For example, in Sage 300 General Ledger (G/L), you can drilldown from General Ledger Transaction History to the Journal Entry, from the Journal Entry to the originating transaction in Accounts Receivable, and from the Invoice, Credit Note, or Debit Note, to the originating transaction in Order Entry.

The way this works is a bit cryptic in Sage 300 ERP’s database and this blog article will attempt to explain some of the internal workings so that developers and customizers who want to use this data for other purposes can hopefully figure out how to interpret it.

The documentation for the full drilldown infrastructure for third party developers is contained in Appendix L of the SDK’s Programming Guide.

drilldwn

Drilldown Database Fields

The drilldown fields in a document provide a link to the application that created the document. They are done in a generic way so any application (Sage or third party) can provide this information and their screens can be drilled down to. As a result the fields are fairly generic and it’s up to the drilldown target to provide what it needs when it creates the document. There are three fields, one is the source application (our usual two character application id like AP), then a drill down type (each application may have several document types like invoices or receipts), and last there is a generic link field which is a large number where the application packs in whatever it needs to do a link.

For example you can drill down from G/L Journal Entry back to the application that created the Journal. In the GLJEH table there are three fields: DRILAPP, DRILSRCTY, DRILLDWNLK. Suppose P/O creates a Journal Entry, it might populate DRILAPP with “PO”, DRILSRCTY with 3 (for Receipt) then DRILLDWNLK with 1740 (where 1740 is a link to PORCPH1.RCPHSEQ).

This is rather cryptic since these fields are meant to be internal to the application that will be drilled down to. But suppose you want to use these fields for other purpose. Here I’ll give a few examples of how Sage applications use these, which should help for many cases. Plus they will give an indication on how these are built so you can reverse engineer other cases.

Here are the ones used for I/C, O/E and P/O. These are pretty straight forward due to the way data is indexed in these applications. Here are the various types and links used in these applications.

IC:

Receipt: 1: ICREEH.DOCUNIQ
Shipment: 2: ICSHEH.DOCUNIQ
Adjustment: 3: ICADEH.DOCUNIQ
Transfer: 4: ICTREH.DOCUNIQ
Assembly: 5: ICASEN.DOCUNIQ

OE:

Shipment: 3: OESHIH.SHIUNIQ
Invoice: 1: OEINVH.DAYENDNUM
Credit and Debit Note: 2: OECRDH.CRDUNIQ

PO:

Receipt: 3: PORCPH1.RCPHSEQ
Invoice: 5: POINVH1.INVHSEQ
Return: 4: PORETH1.RETHSEQ
Credit Note: 6: POCRNH1.CRNHSEQ
Debit Note: 7: POCRNH1.CRNHSEQ

A/R and A/P are a bit more difficult. Here they have to pack quite a bit of information into that field. A 10-byte BCD can hold up to 18 digits. Into this we want to pack the Posting Sequence Number, Batch Number and Entry Number. The way this works, the first digit is the size of the Posting Sequence Number, then the second digit is the size of the Batch Number. Then you have the Posting Sequence Number, then the Batch Number then the left over is the Entry Number. Since the first two digits are used for sizes, the sum of the lengths of the Posting Sequence Number, Batch Number and Entry Number must be less than or equal to 16.

For instance if the DRILLDWNLK is 222765000000000001 then the length of the Posting Sequence Number is 2 as is the length of the Batch Number. The Posting Sequence Number is 27, the Batch Number is 65 and the Entry is 1.

Drilldown View

Knowing the raw format is fine for some applications. But if you are operating in an environment with access to the Sage 300 Business Logic then you can call the application’s View to interpret this value for you and give it in the format of a UI to run and the parameters to pass it, to get the correct information displayed.

Here we will write a small .Net application that uses the Sage 300 API .Net API to process through the drill down information in the G/L Journal Header and process the A/P drill down information. You can find the project here, it is the Drilldown one.

Each application that supports drilldown has such a view. It is defined in its xx.ini file (in this case ap.ini) in the [setup] section there will be a DrillDownView=aannnn entry which specifies the drill down view (in this case AP0062). In the sample program, I just hard code the View and leave it as an exercise to the reader to generalize and load these from the .INI file.

Basically you use this view by setting the drill down type and link and then calling Process(). This then populates the other fields. This gives you a status field of whether you can drill down on this, a roto id of a UI to run and the parameters to pass the UI. Note that UI parameters are separated by line breaks.

So in this case we run the application we get lines specifying the drill down info followed by the drill down View’s interpretation of it. For instance:

Drill down info in GLJEH: AP 0 223055000000000001

UI Information to run for this: AP2100 MODE=1\nBATCH=55\nENTRY=1

Here is the main part of the code that processes this:

// Cycle through all of GLJEH and printout all the drill down information
 while (true == glJEH.Fetch(false))
 {
        string drillSrce, drillLnk, rotoid, parameters, drillkey, drillInfo;
        int drillType;
        drillSrce = glJEH.Fields.FieldByName("DRILAPP").Value.ToString();
        drillType = Convert.ToInt32(glJEH.Fields.FieldByName("DRILSRCTY").Value.ToString());
        drillLnk = glJEH.Fields.FieldByName("DRILLDWNLK").Value.ToString();
        Console.WriteLine("Drilldown: " + drillSrce + " " + drillType + " " + drillLnk);
        if ( drillSrce.Equals("AP") )
        {
                 apDrill.Fields.FieldByName("SRCETYPE").SetValue(drillType, false);
                 apDrill.Fields.FieldByName("DRILLDWNLK").SetValue(drillLnk, false);
                  apDrill.Process();
                  drillInfo = apDrill.Fields.FieldByName("DRILLTYPE").Value.ToString();
                  rotoid = apDrill.Fields.FieldByName("ROTOID").Value.ToString();
                 parameters = apDrill.Fields.FieldByName("PARAMETERS").Value.ToString();
                 drillkey = apDrill.Fields.FieldByName("DRILLKEY").Value.ToString();
                Console.WriteLine(drillInfo + " " + rotoid + " " + parameters + " " + drillkey);
         }
  }

Summary

Drill down is a useful feature in Sage 300 ERP and hopefully this information helps people leverage the infrastructure for some new interesting customizations and integrations.

Written by smist08

February 27, 2015 at 8:48 am

On Calculating Dashboards

with 4 comments

Introduction

Most modern business applications have some sort of dashboard that displays a number of KPIs when you first sign-in. For instance here area a couple of KPIs from Sage 300 ERP:

s300portal

To be useful, these KPIs can involve quite sophisticated calculations to display relevant information. However users need to have their home page start extremely quickly so they can get on with their work. This article describes various techniques to calculate and present this information quickly. Starting with easy straight forward approaches progressing into more sophisticated methods utilizing the power of the cloud.

Simple Approach

The simplest way to program such a KPI is to leverage any existing calculations (or business logic) in the application and use that to retrieve the data. In the case of Sage 300 ERP this involves using the business logic Views which we’ve discussed in quite a few blog posts.

This usually gives a quick way to get something working, but often doesn’t exactly match what is required or is a bit slow to display.

Optimized Approach

Last week, we looked a bit at using the Sage 300 ERP .Net API to do a general SQL Query which could be used to optimize calculating a KPI. In this case you could construct a SQL statement to do exactly what you need and optimize it nicely in SQL Management Studio. In some cases this will be much faster than the Sage 300 Views, in some cases it won’t be if the business logic already does this.

Incremental Approach

Often KPIs are just sums or consolidations of lots of data. You cloud maintain the KPIs as you generate the data. So for each new batch posted, the KPI values are stored in another table and incrementally updated. Often KPIs are generated from statistics that are maintained as other operations are run. This is a good optimization approach but lacks flexibility since to customize it you need to change the business logic. Plus the more data that needs to be updated during posting will slow down the posting process, annoying the person doing posting.

Caching

As a next step you could cache the calculated values, so if the user has already executed a KPI once today then cache the value, so if they exit the program and then re-enter it then the KPIs can be quickly drawn by retrieving the values from the cache with no SQL or other calculations required.

For a web application like the Sage 300 Portal, rather than cache the data retrieved from the database or calculated, usually it would cache the JSON or XML data returned from the web service call that asked for the data. So when the web page for the KPI makes a request to the server, the cache just gives it the data to return to the browser, no formatting, calculation or anything else required.

Often if the cache lasts one day that is good enough, there can be a manual refresh button to get it recalculated, but mostly the user just needs to wait for the calculation once a day and then things are instant.

The Cloud

In the cloud, it’s quite easy to create virtual machines to run computations for you. It’s also quite easy to take advantage of various Big Data databases for storing large amounts of data (these are often referred to as NoSQL databases).

Cloud Approach

Cloud applications usually don’t calculate things when you ask for them. For instance when you do a Google search, it doesn’t really search anything, it looks up your search in a Big Data database, basically doing a database read that returns the HTML to display. The searching is actually done in the background by agents (or spiders) that are always running, searching the web and adding the data to the Big Data database.

In the cloud it’s pretty common to have lots or running processes that are just calculating things on the off chance someone will ask for it.

So in the above example there could be a process running at night the checks each user’s KPI settings and performs the calculation putting the data in the cache, so that the user gets the data instantly first thing in the morning, and unless they hit the manual refresh button, never wait for any calculations to be performed.

That helps things quite a bit but the user still needs to wait for a SQL query or calculation if they change the settings for their KPI or hits the manual refresh button. A sample KPI configuration screen from Sage 300 is:

s300portalconfig

As you can see from this example there are quite a few different configuration options, but in some sense not a truly rediculous number.

I’ve mentioned “Big Data” a few times in this article but so far all we’ve talked about is caching a few bits of data, but really the number of these being cached won’t be a very large number. Now suppose we calculate all possible values for this setup screen. Use the distributed computing powe of the cloud to do the calculations and then store all the possibilities in a “Big Data” database. This is much larger than we talked about previously, but we are barely scratching the surface of what these databases are meant to handle.

We are using the core functionality of the Big Data database, we are doing reads based on the inputs and returning the JSON (or XML or HTML) to display in the widget. As our cloud grows and we add more and more customers, the number of these will increase greatly, but  the Big Data database will just scale out using more and more servers to perform the work based on the current workload.

Then you can let these run all the time, so the values keep getting updated and even the refresh button (if you bother to keep it), will just get a new value from the Big Data cache. So a SQL query or other calculation is never triggered by a user action ever.

This is the spider/read model. Another would be to sync the application’s SQL database to a NoSQL database that then calculates the KPIs using MapReduce calculations. But this approach tends to be quite inflexible. However it can work if the sync’ing and transformation of the database solves a large number of queries at once. Creating such a database in a manner than the MapReduce queries all run fast is a rather nontrivial undertaking and runs the risk that in the end the MapReduces take too long to calculate. The two methods could also be combined, phase one would be to sync into the NoSQL database, then the spider processes calculate the caches doing the KPI calculations as MapReduce jobs.

This is all a lot of work and a lot of setup, but once in the cloud the customer doesn’t need to worry about any of this, just the vendor and with modern PaaS deployments this can all be automated and scaled easily once its setup correctly (which is a fair amount of work).

Summary

There are lots of techniques to produce/calculate business KPIs quickly. All these techniques are great, but if you have a cloud solution and you want its opening page to display in less that a second, you need more. This is where the power of the cloud can come in to pre-calculate everything so you never need to wait.

Written by smist08

February 14, 2015 at 7:25 pm

Follow

Get every new post delivered to your Inbox.

Join 305 other followers