Datasets

This supporting function is used when creating Dataset, which are storage sites for budget and statistical information. Datasets are defined with key fields and accumulator fields used for reporting purposes. In this way, datasets provide the framework for budgeting and reporting activities in the system.

Before you start

This supporting function can be used when the following prerequisites are met:

Follow these steps

  1. Create Dataset

    The following steps must be completed to define and create a dataset.

    • Enter the Basic Information for the dataset in 'Dataset. Open' (OSS401).

      The basic information in the dataset includes a general description, the fields that determine the information updated into it and the date range for this information. A period type, such as weekly or monthly, must be specified to control allocation of the information.

      When sales forecasts are calculated using the dataset, information such as the seasonal curve, forecast method and budget version for the start forecast must also be specified. See Sales Forecasting.

    • Enter the Key Fields for the dataset in 'Dataset. Open' (OSS401).

      Datasets are used to store important information at different levels to meet statistical and reporting needs quickly. Key fields are entered and ordered to accumulate this information at the correct level for reporting needs. Examples of key fields are customer number and item number.

      Up to six key fields can be combined in one dataset and each level can be reported separately. For example, the same dataset can be used when accumulating statistics for both customer group and customer. Customer data will be ordered on a lower level than the groups unless otherwise specified.

      Many different key fields can be used, though much of the data is retrieved from the basic data files such as the item file. This data is accumulated in the dataset and is not stored in the detailed statistics. This data is therefore still available in its raw form when the dataset is re-created.

      For the dataset to function as quickly as possible, the values can be accumulated on more than one level within the dataset. The accumulated values shorten response times but require greater capacity during updating. Therefore, accumulation is done by default in M3 for the following:

      • All customers are totaled one level above customer number.
      • All items are totaled one level above item number.

      Each key field can be assigned a user-defined column heading to be used when OS/400 files are created. The headings are also defaulted for all reports in the report generator. See Report Processing.

    • Enter the Accumulator Fields in the dataset in 'Dataset. Connect Accumulator Fields' (OSS402).

      Accumulator fields define which data is updated in the dataset. Many different data elements can be selected, such as invoiced quantity, invoice totals, cost value, etc.

      The advantage of selecting accumulator fields to include in a dataset is that unimportant information can be excluded to save disk space. Also, as many accumulator fields with a common key field as needed can be in the same dataset. This reduces the number of datasets and increases the combinations possible for different reports.

      For datasets containing sales forecasts, each accumulator field can be specified to be included in the forecast calculation. See Sales Forecasting.

      User-defined column headings can be assigned to each accumulator field for use when the OS/400 files for the dataset are created. The column headings are also used as default headings in all reports in the report generator. See Report Processing.

    • Enter the Transaction Types for the dataset in 'Dataset. Connect Dataset Trans Types' (OSS406).

      The Transaction Type - Statistics that are included in and regulate each dataset must be specified. The following transaction types can be used:

      • 30 = Delivered not invoiced statistics – customer orders
      • 31 = Order entry statistics – customer orders
      • 32 = Sales statistics – customer orders
      • 33 = Sales budget – customer orders
      • 34 = Sales forecast – customer orders
      • 35 = Lost sales – customer orders
      • 39 = Calculated base forecast
      • 70 = Delivered not invoiced statistics – service orders
      • 72 = Statistics – service orders
      • 73 = Sales statistics – maintenance orders
      • 81 = Order received statistics – project orders
      • 82 = Statistics – project orders
    • Complete Field Selection to the dataset in 'Dataset. Connect Selection Fields' (OSS403).

      The statistical data contained in dataset can be limited by making a selection in 'Dataset. Connect Selection Fields' (OSS403). For example, a selection can be used if a dataset should only be used for service level evaluations where it is not desirable to include the order type for internal orders.

  2. Activate Dataset

    When a dataset is activated, a data file containing the data for the dataset and an API program for processing the read-ins and updates to the file are created. The program and file are assigned the same name using the syntax Occcnnn.

    ccc represents the company number and nnn the sequence number assigned automatically. When the dataset is changed or reactivated, M3 automatically uses the same name as closely as possible.

    File names for active datasets are displayed in field File in 'Dataset. Open' (OSS401/E). File names are necessary if the data is used in external routines, such as with ODBC connections or queries.

    The status of the dataset is raised from 10 (preliminary) to 20 (active) when the dataset is activated.

  3. Create Data in Dataset

    Data is accumulated in a dataset as follows, depending on the transaction type:

    • Automatically
    • When re-created
    • Manual entry
    • Calculation routines

    The following transaction types update active datasets automatically and can also be re-created:

    • 30 = Delivered not invoiced statistics – customer orders
    • 31 = Order entry statistics – customer orders
    • 32 = Sales statistics – customer orders
    • 70 = Delivered not invoiced statistics – service orders
    • 72 = Sales statistics – service orders
    • 73 = Sales statistics – maintenance orders
    • 81 = Order received statistics – project orders
    • 82 = Sales statistics – project orders

    Transaction types 33 (Sales budget customer order) and 35 (Lost sales customer order) update active datasets by manual entry. Budget values can also be created in other ways. See Sales Budgeting.

    Transaction type 34 (Sales forecasts) updates active datasets by calculation of other data. See Sales Forecasting.

  4. Deactivate Dataset

    Before the definition of a current dataset is changed, the dataset must be deactivated in 'Dataset. Open' (OSS401). All the data in the dataset is deleted when deactivated. The dataset can then be opened to enter the changes necessary and re-create the same data.

    Only the transactions listed below can be re-created in this way.

    • 30 = Delivered not invoiced statistics – customer orders
    • 31 = Order entry statistics – customer orders. See procedure Re-create Order Entry Statistics in Datasets.
    • 32 = Sales statistics – customer orders. See procedure Re-create Sales Statistics in Datasets.
    • 70 = Delivered not invoiced statistics – service orders
    • 72 = Sales statistics – service orders
    • 73 = Sales statistics – maintenance orders
    • 81 = Order received statistics – project orders
    • 82 = Sales statistics – project orders

    Transaction types 33 (Sales budget customer order) and 35 (Lost sales customer order) can be saved only in a new dataset to which they are copied. Transaction type 34 (Sales forecasts) can be both re-created or copied. See procedure Create Sales Forecast.

    Note that the data files for re-activated datasets can be assigned new names. This has no effect on the M3 report generator, but the external routines using the files in the dataset must be redefined as described above in activity Activate Dataset.

    The status of the dataset is lowered from 20 (active) to 10 (preliminary) when the dataset is deactivated.

Description

The table below illustrates an example of the structure of a dataset.

Type Field Name Forecast
Key UCCUCL Customer group
Key UCCUNO Customer number
Key UCITGR Item group
Accum. UCIVQT Invoiced quantity in basic U/M
Accum. UCSAAM Invoiced amount local currency Yes
Accum. UCUCOS Cost of goods sold Yes
Accum. UCFULL Number of deliveries – correct time and quantity
Accum. UCTDEL Number of deliveries

This dataset can be used to obtain a budget and statistics for item groups per customer with totals for each customer and customer group.

The accumulator fields contain information for calculating turnover, contribution margin ratio, contribution margin and service level for each key level in the dataset.

The invoiced amount and cost of goods sold can also be forecasted to estimate profit/loss for a specific period, such as at year-end.

Related topics