Products »
Desktop Sales Forecasting » Here
This product is discontinued.
We suggest to upgrade to Lokad Safety Stock Calculator.
Developer guide for Lokad Sales Forecasting
This document is a
developer guide, its purpose is to explain the inner design of
Wants to discuss this application? Check
our web forums.
Getting the source code
The solution has been developed with Visual Studio 2005 and .NET 2.0. You can retrieve the latest version of the source code from the
Subversion repository of the
lokad project on Sourceforge.net. You will need a Subversion client to retrieve the code. You do not already have such a client, we suggest
TortoiseSVN.
Subversion URL: https://lokad.svn.sourceforge.net/svnroot/lokad/salesforecastingThree MsBuild project files are available
Those MsBuild scripts have several dependencies (listed below), but note that you do not need those dependencies unless you want to actually produce the MSI package associated with the
Sales Forecasting application. The compilation from Visual Studio should work
as such without any particular tweak.
Those custom MsBuild projects files have the following dependencies
- MsBuild Community Tasks that we are using to perform custom build operations (such as zipping the packages).
- Wix 2.0 that we are using to produce MSI packages.
In order to execute the MsBuild scripts, you need to include several directories in your Windows PATH
- The Visual Studio directory, needed to execute
msbuild.exe (typically C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin). - The .NET 2.0 directory, needed to execute
wsdl.exe (typically C:\Windows\Microsoft.NET\Framework\v2.0.50727). - The WiX directory, needed to execute
light.exe and candle.exe (typically C:\Program Files\wix-2.0).
Architecture
The SVN repository includes several directories.
Lokad.Configuration contains the classes used to manage the application typical settings relevant to Lokad (ex: Lokad user name and password).Lokad.Data contains all the sales providers (more details below).Lokad.Data.Tests contains unit tests for the sales provider. It is still a placeholder at this point. For convenience, the data dumbs (used to validate the providers) are stored in the directory ..\datadumps.Lokad.Excel.Addin is Microsoft Excel add-in library, see Excel Sales Forecasting.Lokad.Excel.Addin.Wix is WiX packaging project.Lokad.Excel.AddinShim is a C++ shim for Microsoft Excel integration.Lokad.Web.SalesForecasting is a stand-alone ASP.NET library (deployment requires changes in web.config).Lokad.Web.SalesForecasting.Mock is minimal ASP.NET website provided for testing purposes.Lokad.Web.Services contains the generated stubs used to interact with the Lokad Web Services.Lokad.Windows.SalesForecasting is the Lokad Desktop Sales Forecasting WinForm application.Lokad.Windows.SalesForecasting.Wix is a WiX packaging project.
It should noted that only the User Interface differs between
ASP.NET Sales Forecasting,
Desktop Sales Forecasting and
Excel Sales Forecasting. This situation is the result of an architecture (i.e.
Lokad.Data) that has been precisely designed in order to facilitate the implementation of user interface as a completely separate layer.
Lokad.Data
LASP has been designed for an easy extensibility through
sales providers. Basically, a sales provider is simply a piece of glue specific of a business application (typically eCommerce); and this piece of glue is used to retrieve the sales history. A sales provider inherits the
SalesProviderBase class which provides two abstract methods
GetDailySales and
GetTopProducts.
public abstract class SalesProviderBase
{
public abstract int GetDailySales(
int pageSize, int startIndex,
IList<SalesEntry> salesHistory, out DateTime yesterday);
public abstract List<string> GetTopProducts(int topProductCount);
}
When the method
GetDailySales is called, the inherited class is supposed to fill-up the collection
salesHistory with the sales history aggregated on a daily basis. Note that the method also return a
DateTime value. Indeed,
GetDailySales should return the aggregated daily sales up to the day of yesterday (present day being excluded because the data is only partially known) ; but if the method call occurs very close to midnight there can be an ambiguity for the value of yesterday, thus the output argument is provided to avoid ambiguity.
Beside the sales providers, a couple of utility classes exist
SalesDataSource.cs is a wrapper to be used with an ObjectDataSource.TimeSerieOperations.cs contains a list of helpers for time series manipulation.
Why daily sales aggregates? Indeed, the
SalesProviderBase hardcodes the fact that the data retrieved from the sales provider is aggregated on a daily level. This choice is a tradeoff between several constraints, namely
- design complexity (we want to support as many applications as possible, the provider must stay as simple as possible),
- performance (retrieving the raw sales data would be to heavy),
- forecast accuracy (retrieved data but be fine grained enough).
Daily aggregates is a good tradeoff for those constraints.
The method
GetTopProducts returns the list of most active products (represented by a string identifier). The purpose of the
GetTopProducts methods is to provide a way, for the webshop manager, to filter out the low-volume products that are not worth to be forecasted. Note that
SalesEntry.ProductId and the identifiers (represented by
strings) returned by
GetTopProducts must match.
The mock sales provider: The library includes a class named
MockSalesProvider. This class is not a real sales provider, it does not connect to any 3rd party business application. The
MockSalesProvider is just used to generate (potentially) large amount of pseudo-random sales data for testing purposes.
Understanding the data paging
Usually, it is NOT possible to retrieve the whole sales history from a database with a single request, typically SQL, because that would be like retrieving (more or less) the whole database from a single request. Such a request would typically
disrupt the quality of service if the database is used in a production environment. Also, the request is probably going to
fail if the database include a restrictive policy on query sizes (that often happen with databases provided in shared hosting environments).
public abstract int GetDailySales(
int pageSize, int startIndex,
IList<SalesEntry> salesHistory, out DateTime yesterday);
It is precisely for that reason that
GetDailySales includes two paging arguments, namely
pageSize and
startIndex. Those arguments are
per-product paging arguments. It means that if
pageSize=100 and
startIndex=10, the method
GetDailySales MUST returns the sales history of 100 products starting at the product indexed by 10 (according to some sorting criterion). The products with zero sales must be excluded from the data retrieval.
Caution: this is a per-product paging, NOT a per-sales-transaction paging. If
pageSize=100, then 100 products must be returned (or less if there is not products available), not 100 sales transactions.
Also, please note that there is
no such thing as out-of-range exception with the
GetDailySales method. If
startIndex=10 but the database only contains 5 products, then the query will simply not insert anything into
salesHistory.
The return value of
GetDailySales is an integer that represents the
total number of products with a least one sales transaction within the database. With this value, it becomes possible for the client caller to control to paged calls in order to retrieve the whole sales history.
What to do when data paging is not possible
Certain applications (for example QuickBooks) do not support paged queries. This situation typically happens when the data is not stored in a database, but in a custom flat file. With such application,
we do not have any choice but to go through the whole data file at once to retrieve the data. Nevertheless, if
GetDailySales is called with
pageSize=100, no more than 100 products should be returned (because the semantic of the method call must be respected).
Now, let's see what would happens if we have a flat file that contains 1.000 products and 100-products paged calls. Since, we are retrieving the products sales with pages of 100 products, there will be 10 calls to
GetDailySales. Yet, for each method call, the whole sales history is going to be retrieved. At the end, in this example, the whole sales history would have been retrieved 10 times which is clearly completely inefficient.
In order to avoid such inefficient behavior,
Lokad.Data includes a
SalesProviderAttribute. This class inherits the
System.Attribute and can be applied to a sales provider implementation.
[SalesProvider("QuickBooks through qbFC",
ConnectionString = "[Sample string] COMPANYFILE;COUNTRYCODE or can be left blank",
Option = "Leave it blank.",
PageSize = int.MaxValue)]
public class QuickBooksProvider : SalesProviderBase
{
// ...
}
In this example, we see that the
SalesProviderAttribute provide several meta-data about the
QuickBooksSalesProvider, but also a paging specific property
PageSize = int.MaxValue. The
SalesProviderAttribute.PageSize indicates the suggested page size that should be used when
GetDailySales. It is
only a suggestion, the client caller is NOT forced to comply (although, usually it will). This
PageSize is given as an indicated of the most efficient page size to be used for this particular provider. In the case of QuickBooks, the value is set to
int.MaxValue because QuickBooks do not support any paging. As result, it's best to retrieve all data at once.
Lokad.Web.Services
Lokad provides
Forecasting Web Services that can be integrated from any application (.NET or non-.NET as well). We have a
complete .NET tutorial to get started with our web services.
We have previously described the
Lokad.Data architecture with the
SalesProvider implementation. The main benefit of this architecture is that we only need a single component to interact with the Lokad Web Services (as opposed to one component per supported application). Let's see what
Lokad.Data contains:
TimeSeries.cs is generated by wsdl.exe, an utility application of the .Net framework.wsdl-lokad.bat and wsdl-sandbox.lokad.bat are batch scripts used to create TimeSeries.cs.
Since the Lokad Web Services calls might involve a certain delay (ex: uploading the sales history), many operations are actually handled though background threads. This ensure the responsiveness of the user interfaces.
As specified in the
TimeSeries documentation, each web method call to the Lokad WS is atomic. Yet, we might need to perform several web method calls to achieve certain results. In such cases, without proper synchronization, several background threads performing concurrently WS calls might lead to unexpected results. Thus we need to exclude mutual executions for the operations related to the Lokad WS.
Note:The directory also include a class named
Lokad.Web.Services.Locks. This class is specific of
ASP.NET Sales Forecasting. The class
Locks is used for synchronization tasks, it provides static objects that can be locked in order to safely perform WS operations. Indeed, ASP.NET is essentially a concurrent user interface (several requests could be made at the same time), whereas regular desktop interfaces could be easily made sequential by actively preventing the user to make several requests at once. This "forced-sequential" behavior is the behavior adopted in
Desktop Sales Forecasting.
SalesDataSource - paging helper
We did discuss here above of the paging issues when accessing the 3rd party application databases in order to retrieve the sales history. The
Lokad Web Services are limited in a very same manner. It is not possible to upload a large database at once through a single call to Lokad Web Services. Although, it's IS possible to upload large amount of data through many calls. Thus, the calls to the Lokad Web Services must be paged too.
The class
Lokad.Data\SalesDataSource.cs has two purposes. First, it acts as a
DataSource providing a direct tabular access to the sales report (with or without the forecasts). Second, it encapsulates the logic to interact with the web services. In particular, methods such as
SalesDataSource.UploadDailySales take care of applying the proper paging when interacting with the Web Services.
The
SalesDataSource constructor takes a
SalesDataProvider as argument, this provider will be called in order to retrieve the sales history. Then, in order to get the sales report, the
SalesDataSource.Select method is available:
public DataTable Select(bool includeForecasts, int maximumRows, int startRowIndex,
out bool insufficientSubscriptionPlan, BackgroundWorker worker)
The arguments
maximumRows and
startRowIndex are paging arguments, typically used when only fragment of the report should be displayed (this is especially useful for web based application such as
ASP.NET Sales Forecasting). The argument
insufficientSubscriptionPlan is a flag used to indicate whether an error did occur because the
Lokad account is not set to the right
subscription plan.
The
worker argument is a bit more tricky, but very useful in order to deliver a slight user experience. Intuitively, initial sales data retrieval and forecast download can take a significant amount of time (several minutes if there is a significant amount of data). Thus, we need to provide a progress bar feedback to the end user while the operations are
in progress (this is exactly the behavior of
Desktop Sales Forecasting. Thus, it is advised to run those operations through
BackgroundWorkers. Indeed, the method
BackgroundWorker.ReportProgress (built in .NET 2.0) can be used easily to update the display of a
ProgressBar.
Lokad.Web.SalesForecasting
For the sake of deployment ease,
Lokad.Web.SalesForecasting must remain a single assembly. This fact explains why the assembly contains in fact several namespaces such as
Lokad.Configuration,
Lokad.Data and
Lokad.Web.Services that could have been also naturally be provided as separate assemblies.
HttpHandler & dynamic pages
PageFactory.cs is the IHttpHandlerFactory that processes the URL and selects the page to instantiate.PageBase.cs is the abstract class for all the web pages of the library.SalesReportPage.cs is the main web page that acts as a container for the sales report.SalesReportControls.cs is the sales report itself.
web.config Settings
ASP.NET Sales Forecasting loads all its settings from the
web.config file. The
ASP.NET Sales Forecasting Setup Guide provides the detail of the custom sections that must be added to the
web.config file.
ServiceProviderFactory.cs is an utility class used to instantiate objects based on the configuration file.Settings.cs is a singleton that carries all the sales forecasting settings.SettingsPage.cs is web page that simply display the settings.
Tips for debugging the Excel addin
Under Windows Vista, we suggest first to run Visual Studio as administrator (this will facilitate, DLL registration when building the addin shim). Then, in order to debug the Excel addin directly through the Visual Studio editor, you need to use the
regsvr32 utility. With the command-line, go the directory
lokad/salesforecasting/Lokad.Excel.Addin/bin/Debug and run the command
regsvr32 lokadssf.dll (again you will need administrator permissions under Windows Vista to do that).
Within Visual Studio, open the
Lokad.SalesForecasting.sln solution, right-click on the project
Lokad.Excel.Addin and mark it at
Set as startup project. Right-click again and go for
Properties -< Debug. Select the option
Start external program put the path of Excel (typically
C:\Program Files\Microsoft Office\Office12\EXCEL.EXE).
Hit F5, and Visual Studio will launch Excel under the debugger.