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.
This supporting function can be used when the following prerequisites are met:
The following steps must be completed to define and create a dataset.
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.
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:
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.
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.
The Transaction Type - Statistics that are included in and regulate each dataset must be specified. The following transaction types can be used:
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.
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.
Create Data in Dataset
Data is accumulated in a dataset as follows, depending on the transaction type:
The following transaction types update active datasets automatically and can also be re-created:
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.
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.
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.
The table below illustrates an example of the structure of a dataset.
|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.