Stephen Smith's Blog

All things Sage ERP…

Posts Tagged ‘sage 300 erp

Drilldown in Sage 300 ERP

with one comment

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

The Sage 300 SDLC

leave a comment »

Introduction

A lot of my blog posts are to answer questions that I frequently receive. Then I have a ready answer of a blog posting link, or perhaps people read my blog and it saves me receiving an e-mail. This blog posting is along the same lines as I get asked quite frequently about our SDLC (Software Development Lifecycle). Usually this is in regards to someone trying to fill out a giant RFP full of questions that are mostly irrelevant to purchasing ERP software.

I covered various aspects of our development process in other blog posting which I’ll refer to here. Plus our process is always evolving as we learn from our experiences and try to improve. What I’m writing about here is specifically what the development team for Sage 300 ERP does, but a lot of it is also used by other Sage teams on other projects. There are always slight variations as the different teams do have their own preferences and as long as they follow the general standards that’s ok.

Within R&D we use the Agile Development Methodology, but R&D exists within a larger context within Sage, much of which doesn’t use Agile frameworks. As a result our Agile development has to fit somewhat within a larger non-Agile system that tracks and coordinates the various projects going on around Sage. This is to ensure all departments know what is going on and can plan accordingly.

Not-Agile

We have a general PMO department that tracks all the various projects. It coordinates getting approval for projects, determining release criteria and coordinating all the various departments such as Marketing, Product Management, IS, etc. So they can do their pieces at the appropriate time.

Initial product ideas come from our Innovation Process, but before converting an innovation idea into a larger development effort there is usually some POC work and some initial rough estimates that then lead to a project kickoff process where an initial business plan, along with an initial project plan are presented and either approved or rejected.

The project is then tracked by PMO as it goes through development and then at the end when the Agile part of the development is done, there is a release certification meeting where all the stakeholders get together that the solution is ready for customers. This includes that the software is ready and of a high quality, but also that support is ready, training material is available, back end systems are setup to take orders, marketing is ready and all the other pieces that go into a product launch.

Web_SDLC_GoodPMO

Also at this time we run a final regression to ensure that everything is working correctly. Generally this is only a couple of weeks as a sanity check that the Agile process below worked correctly.

Before making the product available to all customers, we first spend a few months in a controlled release with a handful of live customers to ensure everything works well for them. This not only tests the software, but their ability to be on-boarded, supported and trained. After this has proceeded successfully then the product is made available for everyone.

Some of these reasons for this non-Agile framework is that a number of parts of our organization having adopted Agile yet and eventually they will need to if we are to have truly effective cloud based products. The other reason that I blogged about here, is the need to coordinate many disparate teams working on different parts of a larger solution.

Agile

Within R&D we use the Agile Development Methodology. I’ve blogged about Agile development a number of times: here, here, here, here and here.

We’ve been using Agile programming for a number of years now. We use 2 week sprints, have sprint planning, maintain a backlog, have daily standups, sprint demos, sprint retrospectives and all the other aspects of Agile. We use VersionOne to manage our projects. In Agile you execute the backlog story by story and have very tight rules on what it means for a story to be “done”. This way as each story is completed, it is fully tested, documented and ready for use. The important thing here is to not build up a large list of defects that need to be fixed near the end of the project. Basically when the last story is finished (done) then the product should be ready to put in customer’s hands.

agile

The doneness criteria vary a bit by Agile team, but here are the doneness criteria for a team on one of our current projects:

  • All tasks items in a backlog story have a closed status
  • The code builds successfully without compiler errors and warnings and without ReSharper issues
  • The code is deployed to test environment successfully
  • The code is checked into the correct repository and branch
  • The code conforms to Sage Branding Guidelines.
  • The code performs to performance standards
  • The code is reviewed for applicable use of all documented standards.
  • The code is refactored and reviewed for good OOP implementation
  • The code conforms to UX wireframes, design and CSS guidelines.
  • Code coverage minimum percentage is 70% with a target of +90%
  • The UI displays correctly in all supported browsers (IE, Safari, Firefox, Chrome)
  • Unit tests are included and run successfully
  • Automation tests are included and run successfully in test environment
  • The environment has not been corrupted (database, etc.)
  • The QA and QA Review tasks are included and complete
  • Reviewed and accepted by the Product Owner
  • Implement and document any build and/or deployment changes
  • Knowledge Transfer Activities (wiki updated, code walkthrough, group code review, etc.)
  • Remaining hours for tasks are set to zero and the backlog item is closed

 

Ensuring stories are done is the key to a well-run Agile process, probably more important than a well-structured prioritized backlog.

As part of developing for the cloud, we want to release fairly regularly and can’t afford long manual regression tests. As a result we have a lot of emphasis on Unit Tests and Automated tests, such as those blogged here.

Similarly branching strategy, source code management, build management and continuous integration are also important parts of this process.

Summary

This was a quick overview of our Sage 300 SDLC. With any big project there are always a lot of moving parts and these have to be tracked accurately and reliably. Agile doesn’t mean that we don’t have deadlines or firm requirements. It does mean that we develop the most important things first and build quality in from the very start.

How to Run Customized Sage 300 Screens from Sage CRM

with 2 comments

Introduction

From the Sage 300 ERP to Sage CRM integration there is the ability to run a number of Sage 300 ERP screens. These are the older VB screens being run as ActiveX controls from the IE browser. Not to be confused with the newer Quote to Order web based screens. A common request is how to customize these screens to you run the customized screen from Sage CRM rather than the base screen.

This blog posting covers how to run customized screens from Sage CRM. As a bonus, as part of this it also shows how to wrap a Sage 300 screen, so that it handles version updates seamlessly and doesn’t require you to re-compile your solution when we release a new version of the base screen. As a result this mechanism requires you use VB to wrap the base control for deployment. The ideas presented here probably can be ported to other programming systems, but it may not be easy.

A sample project that wraps Order Entry is located on Google Drive here. This project will be used for most of the examples in the document, so feel free to load it up and follow along.  In order to view the wrapper, simply unzip the file, and open up the CRMOEOrderUI.vbp.

Create the Wrapper

The following instructions will show the basic steps on how to create a Sage 300 UI Browser Wrapper.  The wrapper can then be referenced by an ASP page. There should be a constant interaction between the UI, the wrapper, and the ASP page (ie. UI calls UI_OnUIAppOpened in the wrapper, the wrapper raises the UIWasUnLoaded event to the ASP page, and the ASP page in turn catches the event, and closes the window containing the wrapper (and attached Accpac UI).

Instructions

1. Open up Visual Basic and select a new Active X Control. Click Open.

custcrm1

2. Go to Project/ References, and select ACCPAC COM API Object, ACCPAC Data Source Control, ACCPAC Signon Manager, VB IObjectSafety Interface, ACCPAC Application Installer, and ACCPAC Session Manager.

custcrm2

3. The project name determines the name of the wrapper (OCX).  In this case, the wrapper name will be “eCRMOEOrderUI”.

4. The name that you give the UserControl should be descriptive of what is contained on it.  In this case, give the UserControl the same name as the Accpac UI that is wrapped (in this case, OEOrderUI).

custcrm3

5. When you are coding refer to the Accpac UI as “UserControl” (ie. UserControl.Width, UserControl.height).

6. We use the VBControlExtender to wrap the Order Entry OCX control dynamically when UserControl_Show is called (see code for UserControl_Show accompanied with this document). When referencing elements and methods within the Order Entry OCX control you would use ctlDynamic.object. The control is installed and opened using the AccpacOcxRegHelper.CLS which makes entries in to the registry that allows the VBControlExtender to reference the control by name as opposed to CLSID which is returned from Roto.

custcrm4

7. Now you are ready to begin writing the code that will catch the events thrown by the Accpac UI, and raise your own events to the ASP that will contain your wrapper.

8. Go into your code view and begin instantiating your events, objects, and variables.

9. Begin by declaring your objects that are going to handle events thrown by the AccpacDataSource controls in the related Accpac OCX controls.  In this case, event handlers of the AccpacOE1100 class are being declared so that they can detect the events thrown by the class.

custcrm5

10. Next, declare the events that you will want to raise to the ASP page.

custcrm6

11. Declare your public variables

custcrm7

12. Declare your remaining variables.  In this case, mSignonMgr is going to be used to sign on the Accpac UI with the signon manager so that the signon screen does not keep popping up every time that the UI is loaded.  mlSignonID is going to be the signon ID.

custcrm8

13. Outline your functions that will be called by the ASP Page.  In this case, the ASP page will give the values that are to be used to populate the UI, or to insert the customer ID into the UI’s customer field for a new customer quote.

custcrm9
14. Next, list out the events that can be called by the UI AccpacDataSources.  In the screenshot below, you can see that the wrapper is checking the eReason variable being passed, and depending on what eReason is being passed, a different event will be raised to the ASP page (AddNew, Delete etc) in the RaiseEventEX sub.

custcrm10
custcrm11
custcrm12

 

15. Other functions are also called by the Accpac UI. The wrapper will be notified of these events through ctlDynamic_ObjectEvent (see below). Once the UI has opened ctlDynamic_ObjectEvent is called with an event name of  “OnUIAppOpened” and a private sub UI_OnUIAppOpened is called and objects in the wrapper are initialized, and the UIWasLoaded event is raised to the ASP page notifying it that the UI has been opened.
custcrm13
custcrm14

16. Finally, define the Get properties that are available to the ASP page so that it can resize its windows when the UI has been loaded onto the ASP page.  In this case, the ASP page will resize its windows to be the same width, height, and unit of measurement as the UI.

custcrm15

17. Now, you have successfully entered all the code that the wrapper will use to receive the function calls from the UI, as well as raise the events to the ASP page.

Customize the Sage CRM ASP Page

You now have a wrapped OCX now you can follow the ASP page in Sage CRM (for example, OE_OrderUI.asp as follows) to call your customized OCX.

http://bcr512115/CRM/CustomPages/Accpac/OE_OrderUI.asp?SID=15427674933819&Key0=1&Key1=44&Key2=58&F=Accpac/OE_Orders.asp&J=Accpac/OE_OrderUI.asp&QUOTENUM=ORD000000000076&CUSTID=AA20130815&DATABASE=SAMINC

Then it will open the OE Order Entry screen for order ORD000000000076.

In OE_OrderUI.asp file, it has following code:

 

<!–

eCRMOEOrderUI raises the following events:

UIWasLoaded(), UIWasUnLoaded(), AddNew(), Delete(), Update(), FieldChange(), Init(), Read(), Fetch()

eCRMOEOrderUI exposes the following Properties:

UIWidth(Read Only), UIHeight(Read Only), TwipsPerPixelX(Read Only), TwipsPerPixelX(Read Only)

eCRMOEOrderUI exposes the following Functions:

PopulateUI(OrderID As String, CustomerID As String);

CreateNewQuote(CustomerID As String);

–>

<SCRIPT for=”eCRMOEOrderUI” Event=”UIWasLoaded()”>

var width  = eCRMOEOrderUI.UIWidth / eCRMOEOrderUI.TwipsPerPixelX;

var height = eCRMOEOrderUI.UIHeight / eCRMOEOrderUI.TwipsPerPixelY;

if ((BrowserDetect.browser==”Explorer”) && (BrowserDetect.version >= 7))

{

width  += 35;

height += 130;

}

else

{

width  += 35;

height += 100;

}

var left = (screen.width – width) / 2;

var top = (screen.height – height) / 2;

window.resizeTo(width, height);

window.moveTo(left,top);

PopulateUI(<%=EnESCDocNum%>, <%=EnESCCustomer%>);

width  = eCRMOEOrderUI.UIWidth  / eCRMOEOrderUI.TwipsPerPixelX;

height = eCRMOEOrderUI.UIHeight / eCRMOEOrderUI.TwipsPerPixelY;

BorderWidth  = ClientWidth()  – width;

BorderHeight = ClientHeight() – height;

bLoaded = true;

resize();

</SCRIPT>

Summary

Hopefully you find this helpful in customizing Sage 300 ERP screens. Even if you don’t run them from Sage CRM, not having to re-build them for each Product Update can save you some time.

Written by smist08

October 11, 2014 at 4:14 pm

Sage 300 ERP 2016 SQL Server Only

with 19 comments

Introduction

If you were able to attend the Sage 300 ERP roadmap sessions at Sage Summit you would have heard that the next major release of Sage 300 ERP (named 2016 but released in 2015) will be dropping support for Pervasive.SQL and Oracle as database servers. This means the only supported database will be Microsoft SQL Server. Although we will support several versions of SQL Server long with the Azure SQL flavor.

The intent of this article is to help make sure everyone has plenty of advanced warning about this change. To help explain the rationale behind this decision, and to help people formulate migration plan if you aren’t already running SQL Server.

Pervasive.SQL

PervasiveLogo

The first Windows version of Sage 300 ERP (then called CA-Accpac/2000) was released supporting one database which was good old Btrieve 6.15. We all have fond memories of those days when the world was much simpler, we just needed a simple robust database manager without any other real concerns. At that time we had a good bundling deal with Btrieve so we could include a database engine with every System Manager. At that time Btrieve was owned by Novell. At that point in time Btrieve was a good low cost database manager that supported transactioning, it was used by many ERPs, and was relatively easy to install and administer. Novell sold off Btrieve back to its original developers and that evolved into Pervasive.SQL and last year that was acquired by Actian.

Pervasive.SQL still has the same qualities that Btrieve 6.15 had, but it hasn’t really kept up with its competitors. SQL Server now has a free edition and Microsoft is much more favorable to doing bundling deals. Plus there are now many better low cost database alternatives such as SQLLite and MySQL.

Over that past years the higher end databases have become much easier to install and manage. Long gone are all the configurable parameters that would plague SQL Server installations (i.e. the defaults now work for most cases). So now Pervasive.SQL isn’t as easy to use.

Anyway Btrieve was the first database that Sage 300 ERP supported, and I think a lot of people have fond memories of Btrieve, but today it doesn’t seem to have a place anymore.

A lot of Sage 300 ERP installations require integrations to many other products, and nearly none of these support Pervasive.SQL. Hence if you want integration with BI tools, or other ERP related software, you are almost always forced to use SQL Server anyway. In the early days of Sage 300, SQL Server was very expensive and most products supported Btrieve as a low cost alternative, but today the need for that has disappeared and we are one of the last vendors to still be supporting Pervasive.SQL.

Oracle

Oracle1

We’ve had Oracle support for a while now. However the sales numbers have never really justified the resources required to support this platform. Oracle tends to be the database of choice for larger companies that tend to be bigger than Sage 300’s target market. We’ve made a few government and large company sales because we support Oracle, but generally these customers would have been as well served by SQL Server.

Our perspective is that the demand for Oracle has waned and that they are really pursuing larger and larger companies and moving further and further away from our market segment.

Multiple Product Integrations

Most Sage 300 ERP sites these days involve multiple products working together such as Sage CRM and Sage HRMS. Generally people only want to work with one database system and the common one across the various products is SQL Server. Most products support a choice of databases, like Sage CRM supports Oracle and SQL Server and Sage HRMS supports FoxPro and SQL Server. To get a more uniform experience across all these products really only works well if you choose SQL Server. It’s generally nicer to have just one set up database operations for things like backup.

Further when you start to use more advanced cross product reporting tools, these can only do their job if all the products are based on the same database engine (so that SQL joins can work properly, etc.).

Database Architecture

The Sage 300 ERP architecture is still the same and supports multiple databases, whether we support another database than SQL Server in the future will depend on the future of the database market. It might be a lighter weight SQL engine like SQLLite is best. Or one of the new NoSQL databases that are becoming popular like HBase or MongoDB. Certainly the NoSQL databases support capabilities that SQL Server can only dream of. Similarly products like SQLLite also run on all the mobile an alternate operating systems opening up all sorts of other possibilities. Chances are these will be introduced in a hybrid manner combined with SQL Server rather than as solutions that handle 100% of our system’s needs.

sql-server-logo

For the short term we will be concentrating on SQL Server which means can use some features that are more specific to SQL Server. However one of our keys to success has been sticking to the core SQL engine functionality. That we work fine with SQL Express and Azure SQL (unlike a number of competitors). So we will be careful to ensure anything we do doesn’t break our database independence or our flexibility in supporting all flavors of SQL Server.

Moving to SQL

If you are running an unsupported database and want to move to Sage 300 ERP 2016 then you will need to convert the database. To convert from an unsupported database like Pervasive.SQL, DB2 or Oracle, you need to run Database Dump on your databases, create SQL databases for these in SQL Management Studio, create the entries in Database Setup and then run Database Load. Make sure that you update and test your backup/restore and disaster recovery plans to ensure that you are still protected.

The conversion must be done before upgrading, since the 2016 version doesn’t include the unsupported database drivers and can’t access these databases and hence can’t do a Database Dump on them.

If you leave Pervasive, DB2 or Oracle databases in Database Setup then these won’t show up in any sign on dialogs. We’ve changed the message when you run the desktop, so that if you don’t have any databases because they are unsupported, why this is the case and to let you run Database Setup.

If you don’t want to switch to SQL Server, it just means the last version you can upgrade to is Sage 300 ERP 2014. This will be supported for its normal lifecycle. When it goes out of support, of course your software will still operate. But you won’t be able to get any new Service Packs or Hotfixes. This should present a quite large window on when to switch. These days, nearly all new sales are SQL Server and the number of SQL installs is the largest share and of course every one already running SQL Server won’t be affected.

Summary

The database world is changing and Sage 300 ERP needs to change with it. That’s why we are making these changes. We hope that converting your Pervasive or Oracle databases to SQL Server won’t be too painful and that you will get quite a few long term benefits from this move.

 

Written by smist08

September 2, 2014 at 12:23 am

Sage Intelligence Go!

with 4 comments

Introduction

A new flavor of Sage Intelligence was demonstrated by Himanshu Palsule during his keynote at Sage Summit called Sage Intelligence Go! (SIG). I thought I’d spend a bit of time this week providing a few more details about what it is and where it fits into the scheme of things.

Sage Intelligence is a business intelligence/reporting tool used by many Sage ERP products. For those who have been around the Sage 300 ERP community, they will recognize it as Alchemex which Sage purchased a few years ago. This is a product that runs as a Microsoft Excel add-in which extracts data from the ERP to be manipulated by the power of the Excel spreadsheet engine. This is a very popular way of doing Financial Reporting since you often want to present the data graphically or you want to perform complex calculations or you want to slice and dice the data using pivot tables. Excel provides a great platform for performing these tasks. The original Financial Reporter bundled with Sage 300 ERP works in a similar manner as an Excel Add-in. Sage Intelligence is used for quite a few things beyond Financial Accounting including Sales Analysis and other predictive type BI functions.

Sage Intelligence has been around for a while and is a good mature product. Since it is written with the full Excel add-in SDK, it must run in Excel and specifically the locally installed version of Excel. This means it isn’t particularly well suited for cloud applications such as Office Online.

Microsoft has now released a newer SDK for developing Office apps. This new SDK is designed to allow you to develop applications that can still run in the locally installed full Microsoft Excel, but they can also run inside the cloud/web based Excel Online as well as the new specialty versions of Office like the one for the iPad.

As Sage ERP applications move to the cloud, you would want to have your Financial Reporter be cloud based as well. You would want to be able to edit Financial Reports in the browser as well as display and interact with reports on any device including your tablet or smart phone. This is where Sage Intelligence Go! comes in. It is written in the new Office Apps SDK and will run on the online and device version s of Excel as well as the full locally installed Excel. This you don’t need a Windows PC at all to use your cloud based ERP and cloud based Financial Reporter. However you still have all the power of Excel helping you visualize and manipulate your reports.

sig1

Sage One Accounting

Sage One Accounting by Sage Pastel is such a cloud based ERP system. This product has Sage Intelligence Go! as an option for performing various reporting needs. When SIG started out here, it was much simpler. The original Office Apps SDK was very simple and didn’t nearly have the power of the various older SDKs supported by local Excel. However as time as gone by, the Office App SDK has become much stronger and the functionality is now much more in line with what we expect of such a solution.

Office Apps

For those of you who attended Sage Summit and saw James Whittaker’s keynote, they would have seen the importance Microsoft is placing on the new Office Apps. Basically to switch from using the browser and search, or using Apps from a mobile device App store, you will get your data directly in your business applications via this new sort of App. If you go to the Office App Store, and search on Sage you will find a Sage One app for Outlook and Sage Intelligence Go! If you search a bit more broadly, you won’t find that many non-trivial applications. Sage Intelligence Go! is probably the most sophisticated Office App in the store.

Notice that you don’t run SIG reports from the ERP application, they run from Excel. Once you have the XLSX file, you can just run it anytime from any version of Excel and have full access to your data. This is really a new paradigm for doing reporting. For SIG this works really well. Whether other applications fit this model is yet to be seen.

Web Services

SIG needs to communicate both with the Office API (whether cloud or local) as well as with the ERP to get the data to process. Both of these functions are accomplished via RESTful web services. The communication with the ERP must be via Web Services since these could originate from the SIG Office App running in the Microsoft Office Online Cloud or from the Office App running in the local Excel. It isn’t possible to use the traditional methods of database integration like ODBC when the two may be running in completely different locations.

Basically the Sage Cloud based ERP exposes a standard set of Web Services that are protected by Sage ID. When the user starts SIG, they get a Sage ID login prompt from with-in Excel and then this is transmitted through to the Sage Cloud ERP which uses the Sage ID to look up which tenants this user runs as. This information is related back to SIG in case it needs a second prompt to act which tenant (company) to access.

Sage Intelligence Go consists of a server component that runs in the cloud as well as the Office App that runs in Excel. The Server portion of SIG uses the provided Web Services to load the data to be reported on from the ERP into its in-memory database for processing. This in-memory database is maintained in the cloud where the SIG service runs. Then the Office App part of SIG interacts with the server side to present the correct required data and perform various processing functions.

sig2

ERP Integration

This solution requires the ERP provide Web Services that are exposed to the Internet in general, so that it can be access via Excel running anywhere whether installed locally, running on an iPad or running as a Web Application in the cloud. This means these Web Services need to be secure and available 24×7 with very good availability. For this reason you will see SIG first integrating to Sage Cloud based ERPs (like the current Sage One Accounting by Sage Pastel) and later via the Sage Data Cloud which will offer these services on behalf of on-premised installed ERP systems.

Summary

Sage Intelligence Go! is our solution for cloud reporting. Its primary purpose is to provide Financial Reporting capabilities, but it is also capable of handling other BI type reporting needs. This is our solution to moving a lot of reporting functions into the cloud.

sig3

 

 

Follow

Get every new post delivered to your Inbox.

Join 288 other followers