Reporting Database

Detailing the reporting database generated by running the reporting suite may be useful to developers planning to contribute or extend the project for their own purpose. It has also become apparent that the database created may itself be an assets that can be used in another project/dashboard.

The database aims for 3rd form normalisation and not the star schema. The project places more value in data integrity and small storage requirements than in the possible speed increase offered by a star schema. Since the resulting database will likely reside on the analyst's workstation, storage constraints are an important consideration.

The suite uses an SQLite database for the backend by default. There are plans to allow the user to change to their preferred database. However, SQLite is robust and light weight enough to be easily relocated or even sent via email.

Table	Fields	Description
currency	id INTEGER (auto increment) currency_code VARCHAR (3) country VARCHAR (255) symbol VARCHAR (255)	Currency Codes for the reports.
forecast	id INTEGER (auto increment) forecast_quantity INTEGER forecast_type_id FOREIGN KEY INTEGER period INTEGER create_date DATETIME	Next periods forecast.
forecast_breakdown	id INTEGER (auto increment) period INTEGER analysis_id FOREIGN KEY INTEGER forecast_type_id FOREIGN KEY INTEGER level_estimates FLOAT trend FLOAT one_step_forecast FLOAT forecast_error FLOAT squared_error FLOAT regression FLOAT	Components of forecast calculation.
forecast_statistics	id INTEGER (auto increment) analysis_id FOREIGN KEY INTEGER forecast_type_id FOREIGN KEY INTEGER slope FLOAT p_vaule FLOAT test_statitics FLOAT slope_standard_error FLOAT intercept FLOAT standard_residuals FLOAT trending BOOLEAN mape FLOAT optimal_alpha FLOAT optimal_gamma FLOAT	Statical components used to generate the forecast.
forecast_type	id INTEGER (auto increment) type VARCHAR (10)
inventory_analysis	sku_id INTEGER (auto increment) abc_xyz_classification VARCHAR(2) standard_deviation INTEGER safety_stock INTEGER reorder_level INTEGER economic_order_quantity INTEGER demand_variability INTEGER average_orders INTEGER shortages excess_stock reorder_quantity economic_order_variable_cost unit_cost revenue date safety_stock_rank shortage_rank excess_cost percentage_contribution_revenue excess_rank retail_price gross_profit_margin min_order safety_stock_cost revenue_rank markup_percentage max_order shortage_cost quantity_on_hand currency_id transaction_log_id inventory_turns traffic_light backlog

Tables

This section describes the fields and relationships for each table and their use in the reporting UI. The SQLite command line or the SQLite client can be used to explore the reporting.db created by running the analysis process. To use the command line with the database created from running the report download sqlite, and run the following command:

$ sqlite3 reporting.db

sqlite> .tables

Currency

Table	Fields	Description
currency	id INTEGER (auto increment) currency_code VARCHAR (3) country VARCHAR (255) symbol VARCHAR (255)	Currency Codes for the reports.

The currency table contains currency codes for various denominations. The field symbol stores the HTML code for the currency symbol. The other fields are self-explanatory. Executing the following SQL code will retrieve all the DISTINCT codes stored in this table:

SELECT
	id,
	currency_code,
	country,
	symbol,
FROM currency;

The currency table has a simple relationship with the inventory_analysis table. The currency.id is a FOREIGN KEY in the inventory_analsis table.

The currency.id refers to the currency used in the analysis. Retrieving the distinct currency codes for a particular transaction (the very first report as indicated by the value 1 in the WHERE clause) from the inventory_analysis table would require:

SELECT DISTINCT cur.currency_code
FROM inventory_analysis AS ia
INNER JOIN currency AS cur ON (ia.currency_id == cur.id)
INNER JOIN transaction_log AS tlg ON (ia.transaction_log_id == tlg.id)
WHERE (tlg.id == 1);

The SKU, Dashboard and Raw Data views all use the currency table, for example:

@dashboard_blueprint.route('/sku_detail', methods=['GET'])
@dashboard_blueprint.route('/sku_detail/<string:sku_id>', methods=['GET'])
def sku(sku_id: str = None):
	# removed code
	cur = db.session.query(Currency).all()
return flask.render_template('dashboard/sku.html', currency=cur)

The currency code itself is retrieved in the jinja2 template, for exapmple page supplychainpy/supplychainpy/reporting/blueprints/dashboard/templates/dashboard/sku.html:

{% for sku in inventory %}
                <tr>
                    <th>Economic Order (qty):</th>
                    <td>{{sku.economic_order_quantity}}</td>
                </tr>
                {% for cur in currency if (cur.id == sku.currency_id) %}
                {% autoescape false %}
                {% set sym = cur.symbol %}
                <tr>
                    <th>Retail Price</th>
                    <td>{{sym}}{{"{:,.2f}".format(sku.retail_price)}}</td>
                </tr>
                <tr>
                ...

The reuslt can be seen in the UI:

Forecast

The forecast table stores the actual forecast for a given sku. The forecast type_id is a foreign key which enforces the one-to-many relationship between the forecast table and the forecast_type and inventory analysis. For every analysis performed on a single sku, there can have one forecasts for different periods across multiple types.

Table	Fields	Description
forecast	id INTEGER (auto increment) analysis_id FOREIGN KEY INTEGER forecast_quantity INTEGER forecast_type_id FOREIGN KEY INTEGER period INTEGER create_date DATETIME	Next periods forecast.

For example, to retrive all the forecasts for the holt's trend exponential smoothing for sku KR202-209:

select forecast_quantity, period, forecast_type_id from forecast as f
INNER JOIN inventory_analysis AS ia ON (f.analysis_id == ia.id)
INNER JOIN forecast_type AS ft ON (f.forecast_type_id == ft.id)
WHERE ia.sku_id == (SELECT id FROM master_sku_list WHERE sku_id == 'KR202-209') AND ft.id == 2;

The query will return something like this:

287.21309055714863|1|2
295.15146936370166|2|2
303.0898481702547|3|2
311.0282269768077|4|2

In the library the default setting for generating a forecast is 4 periods ahead. For example:

from supplychainpy.model_demand import holts_trend_corrected_exponential_smoothing_forecast

orders = [165, 171, 147, 143, 164, 160, 152, 150, 159, 169, 173, 203, 169, 166, 162, 147, 188, 161, 162,169, 185, 188, 200, 229, 189, 218, 185, 199, 210, 193, 211, 208, 216, 218, 264, 304]

htces_forecast = holts_trend_corrected_exponential_smoothing_forecast(
						forecast_length=4,
						alpha=0.5,
                    gamma=0.5,
                    demand=orders,
                    optimise=True
          		)

forecast = [forecast for forecast in htces_forecast.get('forecast')]

print(forecast)

results:

[287.21309055714863, 295.15146936370166, 303.0898481702547, 311.0282269768077]

The reporting database stores the forecast for all the forecast types for each sku. The forecast is then displayed in the SKU details view,

supplychainpy/supplychainpy/reporting/blueprints/dashboard/templates/dashboard/sku.html

images/htces_tab.png