USER MANUAL

Setting Up Data Transfer, Properties, Performance

The data transfer is integrated in the PipeChain Server software, but it needs to be activated and scheduled.

In the Business Detail screen of the PipeChain Client, Unlock the Analyzer module for all sites that should save analyzer data. For all such sites, an Analyzer Poll background job is created automatically.

The Analyzer Poll background job will handle writing of ALL object history, per site. The job can be configured in the following ways:

Analyzer Poll is normally run once per day. The time-logic demands that it is run between 24:00 and 12:00, preferably 24:00-03:00 to allow time to build OLAP cubes and dimensions before the end users get to work. It is wise to separate jobs (i e to set First Start Time) in time since this may increase writing performance.
It is possible to run Analyzer Poll more than once per day. This only affects the Inventory and Delivery data, and is of merit mainly if "daily mean values" are strongly preferable to "daily final values". Note that running Analyzer Poll more often may affect the performance of other operations, so this feature comes at a significant cost.
It is possible to disable any background job (screen Background job Detail). This is not recommended for Analyzer Poll since data will be lost, and most cannot be retrieved. If the job that is supposed to trigger Cube Processing is disabled, OLAP and Dashboard will not be updated at all.

The AnalyzerProperties table in the data warehouse database contain some ways to control the execution inside the background job. Each property is normally valid for one site. SystemId is found in the PipeChain database table OwnSystemNode, column SystemId.
If no property is found, default values are used - except for the first property below:

PropertyName: CubeProcessingJob_Command
PropertyValue: No default.
Example: sqlcmd -E -S MyServerAndInstance -Q "exec msdb.dbo.sp_start_job @job_name = 'MyJobName'"
Example: sqlcmd -U MyLogin -P MyPassword -S MyServerAndInstance -Q "exec msdb.dbo.sp_start_job @job_name = 'MyJobName'"
SiteKey: Is significant. Create only ONE row with this property. Select any site where the “Analyzer” module is unlocked, the Analyzer Poll job is enabled, and the site is not "excluded" - see below.
BizKey, UpdDate and marowver: not significant.
Description: This property triggers Cube processing as well as update of the Management Dashboard KPI-tables in the PipeChain database.
The value is used as the execution string to trigger the Cube Processing job. If Cube processing is not desired, but KPI-tables are, the value may be set to "dummy".
If absent (or multiple), a warning is written to the PipeChain Message Log, and nothing will be automatically triggered by the PipeChain server. Cube processing may still be run "scheduled" or manually.
If your Analyzer collects data from more than one SystemId, select the one that finishes running Analyzer Poll jobs last.
If your PipeChain application server does not have SQL Server installed (i.e. separate database server), follow the instruction in Advance Preparation for Installation.
PropertyName: CubeProcessing_Exclude_Site
PropertyValue: Not significant. The mere presence of the property triggers the function.
SiteKey: The site to be excluded.
BizKey, UpdDate and marowver: not significant.
Description: Cube processing is normally triggered when the LAST Analyzer Poll job has completed.
This property allows Cube processing to start before a certain site's job has finished, which may be desired if there is a large time zone difference.
PropertyName: HistoryLifeTime_FactFlowPeriodDay
PropertyValue: Integer value. Default 365 (days).
SiteKey: Is significant.
BizKey, UpdDate and marowver: Not significant.
Description: This data is not used for OLAP cubes, but may be used for custom SQL reports. You may wish to keep this data longer than a year, but it requires very much disc space.
PropertyName: HistoryLifeTime_FactFlowPeriodWeek
PropertyValue: Integer value. Default 400 (days).
SiteKey: Is significant.
BizKey, UpdDate and marowver: Not significant.
Description: As above, but this table is far less space consuming. The table is used for VMI products.
PropertyName: HistoryLifeTime_FactDelivForecastDay
PropertyValue: Integer value. Default 365 (days).
SiteKey: Is significant.
BizKey, UpdDate and marowver: Not significant.
Description: This data is not used for OLAP cubes, but may be used for custom SQL reports. You may wish to keep this data longer than a year, but it requires very much disc space.
PropertyName: HistoryLifeTime_FactDelivForecastWeek
PropertyValue: Integer value. Default 400 (days).
SiteKey: Is significant.
BizKey, UpdDate and marowver: Not significant.
Description: As above, but this table is far less space consuming. The table is used for Purchase Order products.
PropertyName: Limit_Years_Inventory
PropertyName: Limit_Years_Shipments
PropertyName: Limit_Years_FlowModelPrecisionDay
PropertyName: Limit_Years_FlowModelPrecisionWeek
PropertyName: Limit_Years_DelivForecastDay
PropertyName: Limit_Years_DelivForecastWeek
PropertyName: Limit_Years_Event
PropertyName: Limit_Years_DataExchange
These properties were used to limit (by no of years) the amount of data in cubes in versions earlier than 4.9.5.
A WHERE-condition appeared in the corresponding Views, which feed OLAP. This approach was sometimes a performance bomb, rather than a performance boost.
Instead, all views are set to THREE years (plus this year). This can easily be changed (both up and down) by any DBA. but note that such changes must be reapplied if a view definition is changed in an upgrade by PipeChain AB.
PropertyName: StockDays_Limit
PropertyValue: Integer value. Default 1000 (days).
SiteKey: Is significant.
BizKey, UpdDate and marowver: Not significant.
Description: Used by Inventory Cubes to exclude "Extreme Stock Days" (usually due to near zero outflow) from some key indicators which compute inventory averages.
PropertyName: UsePreviousDateForInventoryPoll
PropertyValue: Integer value [0 or 1]. Default false (0).
SiteKey: Must be 0.
BizKey: Is significant. Note that it is NOT possible to use different behaviour in two Sites of the same Business.
UpdDate and marowver: Not significant.
Description: If set to 1, inventory values are saved against the previous day. However, this property is not significant if the Inventory Poll background job is set to run more than once per day.

A note about performance

There are several aspects of PipeChain Analyzer performance. Firstly there is processing time of the Analyzer Poll background jobs and the size of the PipeChain Analyzer history database. The jobs must run daily (or rather, nightly), otherwise some "sampled" data will never be saved to the Analyzer. Thus the user can not affect this.

Then, there is the processing time and size of the OLAP database. This is strongly affected by the "Limit_Years_..." settings.

Lastly, there is the browsing performance in the OLAP interface (e g Excel). This is also strongly affected by the "Limit_Years_..." settings.

In general, browsing performance is handled in Analysis Services with aggregations and partitions. Since PipeChain Analyzer is a standard application, it must be able to handle very different sizes and types of data. Currently, aggregations are used only for the Inventory because most performance issues concern this cube.

Aggregation was introduced in version 4.7.0, thus OLAP database size may increase significantly in this version. The aggregation level chosen should give satisfactory browsing performance in most dimensions UNTIL you reach the lowest 1-2 levels. Here, the user must expect a significant wait, especially the first time the level is used (Analysis Services has a somewhat unpredictable caching function).

Partitions is a more advanced (than "Limit_Years_...") way of handling the trade-off between available data and browsing. Partitions are not used, since they are not available in SQL Server Standard Edition.

Next ->