Flexible Data Streams

This chapter covers the following topics:

About Flexible Data Streams

Demand Planning supports flexible data streams. This means that you can define, import, and use arbitrary data streams, such as sales forecasts, customer forecasts, and supply plans. This feature makes the demand planning process more flexible and configurable to the specific needs of your business.

You can define a data stream and indicate its source and the type of demand data that it will hold, the dimensions and dimension levels to which the data will be associated, and the ways to allocate and aggregate the data across dimension levels. These data streams enter Oracle Demand Planning during the plan definition phase. Inside the planner's or planning manager's workspace, you can view, manipulate and combine data streams, and then subsequently submit them as forecast scenarios. You can now use any kind of historical or forecast data in Oracle Demand Planning. The flexible data stream capability is meant to bring in the time series data only. Oracle Demand Planning does not support timeless or dimensionless data such as average allocation weights and economic performance indicators.

You can bring a flexible data stream into Oracle Demand Planning in one of three ways:

A flexible data stream consists of the following:

There are two modes of defining data streams: basic and advanced. Basic mode allows you to define and import most flexible data streams into Oracle Demand Planning. Advanced mode provides a superset of the basic mode's functionality, at the cost of some exposure to the technical details of the Oracle Demand Planning data model. The basic mode assumes that the data stream is not chaotic; that is, the data is available only at a fixed level in each dimension, for example: product family level in the product dimension, the week level in the time dimension, and the customer level in the geography dimension. For the basic mode, the aggregation and allocation rules are preseeded.

Flexible Data Stream User Procedures

The user procedure for flexible data streams will be explained by means of an example. In this example, we will bring the following two customer forecasts into Oracle Demand Planning as a flexible data stream.

Forecast Name Item Time Forecast Quantity Forecast Dollars
Forecast for customer 1, FCC1 A Week 50, 15Dec01 10 100
  A Week 51, 22Dec01 15 150
  B Week 50, 15Dec01 20 400
  B Week 51, 22Dec01 25 500
Forecast for customer 2, FCC2 A Week 50, 15Dec01 30 300
  A Week 51, 22Dec01 35 350
  B Week 50, 15Dec01 40 800
  B Week 51, 22Dec01 45 900

To define a customer forecast as a flexible data stream:

  1. Choose the Demand Planning System Administrator responsibility.

  2. To open the Select Data Definition window, select Setup > Data Streams > Define-Advanced in the Navigator. Or select Define-Basic if you are not familiar with the Oracle Demand Planning data model and architecture.

    Note: As described earlier, there are two modes of defining data streams: Define-Basic and Define-Advanced. You should select only one of them. If you are not familiar with Oracle Demand Planning data model and architecture, you should use the Define-Basic, which provides only the basic functionality. The steps in this section explain the functionality of Define-Advanced, and indicates anything that is similar or different for Define-Basic.

    There is no difference in this window for the two modes of setup, except that the window name reflects the mode; that is, either Define-Basic or Define-Advanced.

    the picture is described in the document text

  3. All the data streams on the Select Data Definition window are preseeded data streams (identified by Code and Name). You can add your own flexible data streams to the list of preseeded data streams.

    Composites allow you to group types of data (columns) by dimensions and data patterns (levels of dimensions). Composites allow the Demand Planning Engine to efficiently store and manipulate sparse data. You assign the same number to each of the data streams you want to form a Composite Group. The number is user defined. You can leave the Composite Group field blank. This can also serve as a composite group. If you want to change the composite group that a data stream belongs to, purge the Demand Planning database.

    Input data streams are organized into groups of matching dimensions and similar sparsity that allow Oracle Demand Planning to create multiple composites. Two sets of input data streams that have the same dimensionality can use completely different combinations of dimension values.

    The demand planning engine creates dimensional composites whenever data is dimensioned by more than one dimension. Except for the time dimension, which is not included because most data is dense along time given a certain combination of other dimension members. For example, when you set up a plan, dimensional composites are created when several data streams have the same dimensionality and similar sparsity patterns.

    Oracle Demand Planning automatically determines which data streams should be clustered together into the same composite so that overall data storage requirements for a demand plan can be minimized. Thus, data streams with the same dimensions are grouped automatically into a composite, resulting in reduced storage and more efficient processing.

    To set up automatic recommendation of composites:

    1. Logon as the Demand Planning System Administrator.

    2. Select Other > Requests > Submit a new request.

    3. Select the concurrent program, 'Suggest Data Composites'.

      the picture is described in the document text

    4. Select Yes or No for the parameter, 'Do you want to apply the suggested composites'.

      Selecting Yes will populate the Composite Group fields in the Select Data Definition. The user will still be able to change the groups.

      The default value is No. Selecting No means that the suggested composites will not appear in the Select data Definition screen.

      In either case of Yes or No, the program output will be displayed from the concurrent log of the launched request 'Suggest Data Composites'.

    5. Now, the data streams are grouped automatically in the demand planning server.

      Note that streams must have the same dimensions and the same read-in level for all dimensions. For multiple-stream streams, there must be data for only one designator.

    Once the demand plan has been built, the change in the groups will not impact the existing build but will take effect only for the new plan builds.

    You can find the characteristics or attributes of these data streams by selecting a data stream and selecting Next.

  4. Select New to define a new data stream in the Define Characteristics window.

    the picture is described in the document text

  5. Complete the required fields in the Define Characteristics window as follows:

    Field Value Description
    Code Preseeded value. Name of the data stream. This is a short name with less than or equal to 30 characters that Oracle Demand Planning uses internally to uniquely identify the data stream.
    Name User defined. Description of the data stream. This is a longer description with less than or equal to 240 characters that you will henceforward use to identify the data stream in Oracle Demand Planning.
    Source of Data Planning Server, ERP, Interface. Possible values are:
    Planning Server: where data comes from the same instance as that of Demand Planning Server.
    ERP: where data comes from some source database instance other than the Demand Planning Server.
    Interface: where data comes from a legacy system loaded as flat files into the Oracle Demand Planning staging tables.
    Type of Data Amount, Quantity, Amount and Quantity, Amount and Price, and Quantity and Price. Possible values are amount, quantity, amount and quantity, amount and price, and quantity and price. The selected value indicates what types of data will be stored in the data stream.
    Source View Applicability varies. Applicable when ERP is selected as the source of data. The Source View is a SQL view that defines where to find the quantity, amount, and price data for the flexible data stream. The Source View also defines where to find the dimension level values, such as product, geographic regions, time periods, sales channels, and customers to which those data are associated.
    Collect Filter Condition Applicability varies. Applicable when ERP is selected as the Source of Data. Enter the relevant Where clause that describes the data that should be collected into Oracle Demand Planning. This functionality is only available in Define-Advanced. For details, see: Uploading Flexible Data Streams.
    Planning Server View MSD_CS_DATA_V (example) This is the name of the view that Oracle Demand Planning accesses to populate its flexible data stream repository or fact table (for example, MSD_CS_DATA). If you are using Interface or ERP as the Source of Data, select the preseeded view MSD_CS_DATA_V. This view sits on top of the Oracle Demand Planning staging table MSD_ST_CS_DATA, which is the interface table that initially gets populated when you bring in flexible data streams from flat files or from an Oracle Applications source. Oracle Demand Planning reads specifically named columns from MSD_CS_DATA_V into specific columns in its flexible data stream fact table (for example, MSD_CS_DATA). If you are using the planning server as the Source of Data, then you must define your own view and enter its name here. This view must bring together information from the planning server tables of your choosing, and it must have the same column names as MSD_CS_DATA_V. The column names of MSD_CS_DATA_V are shown in the Define Data Usage window.
    Designator View Applicability varies. This view allows users to restrict the data stream names that show up in various list of values, such as input parameter list of values. It is primarily meant for planning server based data streams, such as supply plan and constrained forecast, because as you run more and more supply plans in Oracle Advanced Supply Chain Planning, over time the list of values for these supply plans gets very large and takes a long time to show up in Oracle Demand Planning.
    Multiple Stream Checked or unchecked. Check this box if many different streams with the same characteristics will be used; for example, if you will be using forecasts from multiple customers. If not checked, the stream identifier row (in Step 5) does not appear. The default status is not checked. This default status is non-editable in the Define-Basic mode.
    Editable Checked or unchecked. Check this box if the data needs to be modified in the Demand Planning Planner or Manager workspace. The default status is checked. This default status is non-editable in the Define-Basic mode.
    Enabled Checked or unchecked. Check this box if the data stream should be visible to the Demand Planning Planner or Manager workspace. If this is not checked, the data stream will not be used. The default status is checked.
    Data at Fixed Level Checked or unchecked. Check this box if the data are not chaotic, that is, all the data will be at one fixed Dimension Level. For example, all the data will enter Oracle Demand Planning at the Item level in the Product Dimension. If not checked, the Dimension Level (in Step 4) and aggregation/allocation functions (in Step 6) cannot be selected. The default status is checked.
    Allocation Allowed To Checked or unchecked.
    Stream Dimension Level
    Check this box if the data can be allocated down the Oracle Demand Planning hierarchies by the system. The default status is checked.
    If checked, you must specify the allocation floor from the drop-down list. This is applicable only when the 'Data at fixed level' box is checked.
    The allocation floor determines the level to which data is allocated for a measure. The possible values are Stream Dimension level and Lowest Dimension level. Selecting the Lowest Dimension level allocates the data to the lowest levels of each dimension included in the demand plan. Selecting the Stream Dimension level restricts the data allocation for this data stream to the dimension levels at which the data is brought into Oracle Demand Planning. For details, see: Input Parameters.
    Aggregation Allowed Checked or unchecked. Check this box if the data should be aggregated up the DP hierarchies by the system. Applicable only when the Data at Fixed Level checkbox is checked. The default status is checked.
    Dependent Demand Checked or unchecked.
    Drop-down values: read or calculate
    Check this box if the custom data stream is dependent demand enabled; that is the data stream contains dependent demand data in addition to independent demand data. Only when this box is checked can this data stream be used to calculate planning percentages.
    If Dependent Demand checkbox is checked, then Define Dimension and Levels pages automatically presets Product Dimension Level to Item Level.
    • Selecting Read means that the data stream contains dependent demand data. This value should be selected for sales history data streams when it contains the dependent sales history of options and option classes.

      Note that only booking history and shipment history can contain dependent demand data and are set to read in the dependent demand by default.

    • Selecting Calculate means that the data stream does not contain dependent demand data. It has independent demand for models, which should be exploded to the options and option classes based on the model bills of material and existing planning percentages collected from source ERP.

  6. Select Next to open the Define Dimension and Levels window.

    the picture is described in the document text

  7. In this window, you can select the Level, such as Item or Product Category, for each Dimension at which the information exists in the data stream. The levels can only be selected if the checkbox, Data at Fixed Levels in the Define Characteristics window (in Step 3) is checked. Select the Enable checkbox to activate a dimension for the data stream.

    In our example, select the Enable boxes for Geography, Product, Ship From Location, and Time Dimensions. You can then select a level for each of the chosen Dimensions.

    This functionality is not applicable for chaotic data and is optional for the data at fixed level. If you do not select a level here, the system assumes the level to be the same as that of the first record that it encounters at the time of data collections.

    In our example, select the following level values for the Geography, Product, Ship From Location, and Time dimensions respectively: Customer Level, Item Level, All Organization Level, and Manufacturing Week.

    Oracle Demand Planning offers automatic recommendation of composites to automatically detect similar sparsity patterns across different data streams. Composites define a subregion of the total possible dimension level value combinations at which demand can occur. When a measure is assigned to a composite, it allows Oracle Demand Planning to store that measure without setting aside storage for every possible combination of dimension level values.

  8. Select Next.

    The Define Data Usage window appears. This window tells you which columns in the MSD_CS_DATA table correspond to which columns in the view, MSD_CS_DATA_V, (or a view that you specify) used by Oracle Demand Planning to access the flexible data stream information. The MSD_CS_DATA table is where Oracle Demand Planning stores flexible data stream information.

    If the source of the flexible data stream information is Interface, then the default view is MSD_CS_DATA_V. It has columns displayed in Planning View Column that are automatically mapped to the columns of MSD_CS_DATA and displayed in Table Column. Note that the elements of Source View Column are not editable.

    If the source of the flexible data stream information is Planning Server, then the planning server view provided must have the same column names as MSD_CS_DATA_V. The mapping between the planning server view and table MSD_CS_DATA is done automatically, so the Source View Column is not editable.

    If the source of the flexible data stream information is ERP, then there is no restriction on the structure of the source view. In this case, the Planning View Column gets relabeled to Source View Column, and it becomes editable. Enter the column names in the source view that correspond to the structure of the table, MSD_CS_DATA, shown in the Table Column.

    You must select the Column Name for quantity, date, and amount from the list of values. The selected names are used to label the data in the Oracle Demand Planning user interface. For details, see: Customizing the Display Labels for Amount, Quantity, and Time.

    Several other rows appear for a dimension depending on the selection for the drop-down list at the top right of the window, where Dimension Level Primary Key has been selected from the list of values. The other rows that may appear include:

    • Dimension Level Value: If the level value (for example, name of the customer) is used for identifying the data.

    • Dimension Level Primary Key: If the level primary key is used for identifying the data.

    • Dimension Source Primary Key: If the source primary key is used for identifying the data.

      If you select any one of these values, only the corresponding record shows up for each Dimension. However, when the source of data is the Demand Planning Server, only Dimension Level Primary Key appears and the other two data identifiers are not applicable. These records do not show up for the Time Dimension. One row for Level ID appears for every selected dimension.

      Note: The drop-down list selection of Dimension Level Value, Dimension Level Primary Key, or Dimension Source Primary Key affects only which subset of rows are displayed in the Define Data Usage window. It does not alter which planning server view or source view columns are mapped to which columns in the demand planning flexible data stream table MSD_CS_DATA.

      To summarize, you will see the following rows on this window:

    • One row for each Type of Data, such as amount and quantity.

    • One row for the Date if Time Dimension has been selected (in Step 4).

    • One row for Level ID for each Dimension Level selected (in Step 4).

    • One row for Level Value, Level Primary Key, or Source Primary Key based on the selection.

    • Instance: Note that this field is for information only and cannot be modified.

    • Stream Identifier representing the name of the stream, such as Forecast for Customer 1. Note that this row appears only if the checkbox Multiple Stream on the Define Characteristics window was checked (in Step 3).

      This step applies to Define-Advanced only. Scroll to the right on the Define Data Usage window and check the UOM Conversion checkbox for the Quantity field.

      Checking this box ensures that the units of measure are appropriately converted when the data are rolled up (for example, aggregated up) along Oracle Demand Planning hierarchies.

      The UOM Conversion checkbox can be checked only for Quantity and Amount and cannot be checked for any other row. Quantity numbers must be brought into Oracle Demand Planning in the primary units of measure of the items.

      The aggregation and allocation functions are used to aggregate and allocate the data along the hierarchies defined in Oracle Demand Planning. These can be selected only for the Quantity and Amount. The list of values include:

    • Aggregation Function: Additive, Average, First, Last, Maximum, Minimum, and Weighted Average.

    • Allocation Function: Allocation based on Weights in Another Measure, Average Weights based on History, Even, and None.

      For example, three items X, Y, and Z belong to a product category C. The customer forecast for the three items is 10, 20, and 30 respectively for the Week 50 ending 15 December 2001. Then, the customer forecast for the product category C for Week 50 would be as shown in the following table:

      Aggregated Customer Forecast for Product Category C for Week 50 Aggregation Function
      60 Additive.
      20 Average.
      10 First. Note that this is assuming that item X forecast is the earliest entry.
      30 Last. Note that this is assuming that item Z forecast is the last entry.
      30 Maximum.
      10 Minimum.
      Based on the data stream selected at the time of defining the Demand Plan. Weighted Average.

      Extending the scenario, let us assume that the forecast for product category C for the Week 50 is 60. Then the customer forecast for the three items (X, Y, and Z) for week 50 would be as shown in the following table:

      Customer Forecast for the Three Items for Week 50 Allocation Function
      Based on the data stream selected at the time of defining Demand Plan. Allocations based on weights in another measure.
      Based on the history data stream selected at the time of defining the Demand Plan. Average weights based on history.
      20 for each of the items X, Y, and Z. Even.
      Zero for each item. None.

      For our example, select the following Aggregation and Allocation Functions from the lists of values:

    • Additive and Allocation based on Weights in Another Measure respectively for Amount.

    • Additive and Even respectively for Quantity.

  9. This step applies to Define-Advanced only. Select Next to open the Define Column Dimension Attributes window.

    the picture is described in the document text

  10. In this window you can specify different aggregation and allocation functions for different dimensions. For example, you may allocate the data for the product dimension, such as product category to items, differently than that for other dimensions. If an aggregation or allocation function is not selected for some dimension, the functions selected in the previous step apply.

    For our example, the functions selected in the previous step in the Define Data Usage window are used as the default for all the dimensions, such as geography, time, or ship from location (in Step 4) except for product dimension for which the selections in this window apply.

  11. Select Finish.

    The definition process for the flexible data stream is complete. The flexible forecast data stream appears on the Select Data Definition window, the first window with which the flexible data stream definition process started (in Step 2).

To load a customer forecast:

  1. Now the data needs to be staged in the staging table, MSD_ST_CS_DATA in the Demand Planning Server, as the Source of Data was selected as Interface in Define Characteristics window (in Step 3). For details on the format suitable for this staging table, see: Attributes of Staging and Fact tables. Generally, the data may not be available for all columns shown in the format and some columns may be empty. For the customer forecast in this example, assume that the information is available only for the following columns:

    Data Stream Definition ID (CS_DEFINITION_ID) Data Stream Name (CS_NAME) Instance Attribute Product Level ID Attribute Product Level Value Attribute Geography Level ID Attribute Geography Level Value Attribute
    21 FCC1 Legacy1 1 A 15 Customer 1
    21 FCC1 Legacy1 1 A 15 Customer 1
    21 FCC1 Legacy1 1 B 15 Customer 1
    21 FCC1 Legacy1 1 B 15 Customer 1
    21 FCC2 Legacy1 1 A 15 Customer 2
    21 FCC2 Legacy1 1 A 15 Customer 2
    21 FCC2 Legacy1 1 B 15 Customer 2
    21 FCC2 Legacy1 1 B 15 Customer 2

    The following table is a continuation of the columns of the previous table for customer forecast:

    Data Stream Definition ID (CS_DEFINITION_ID) continued Data Stream Name (CS_NAME) continued Org Level ID Attribute Organization Level Value Attribute Time Level ID Attribute Quantity Attribute Amount Attribute Date Attribute
    21 FCC1 29 All Organizations 1 10 100 2002-12-15
    21 FCC1 29 All Organizations 1 15 150 2002-12-22
    21 FCC1 29 All Organizations 1 20 400 2002-12-15
    21 FCC1 29 All Organizations 1 25 500 2002-12-22
    21 FCC2 29 All Organizations 1 30 300 2002-12-15
    21 FCC2 29 All Organizations 1 35 350 2002-12-22
    21 FCC2 29 All Organizations 1 40 800 2002-12-15
    21 FCC2 29 All Organizations 1 45 900 2002-12-22

    To find out the CS_DEFINITION_ID, choose the relevant data stream on the Select Data Definition window (in Step 2). From the Oracle Applications menu bar, select Help > Diagnostics > Examine.

    The Examine Field and Variable Values window appears.

    Select DEFN_QUERY for Block and CS_DEFINITION_ID for Field.

    The Value that appears is the Custom Data Stream definition identifier.

    Select OK.

  2. The data that has been loaded in the staging tables need to be pulled in to the Fact Table, MSD_CS_DATA using a preseeded Pull program. To perform this, select Collections > Pull Data > Custom Data Stream. Select a Data Stream from the Find window to open the Data Stream Collection window.

    the picture is described in the document text

    • Collection Type: set to Pull. This program moves data from the staging tables to the fact tables.

    • Single Step Collection checkbox: is for information only. It appears as checked if you have specified Yes for the profile, MSD_ONE_STEP_COLLECTION. The single step collection means that you want to bring the flexible data directly in to the Oracle Demand Planning fact tables rather than bringing the data first in to the staging tables and in the second step, pulling the data in to the fact tables.

    • Instance: identifies which source instance to collect the data from.

    • Complete Refresh checkbox: if the Complete Refresh box is checked, the previously collected data are deleted from the staging or fact tables, depending on the tables into which you brought the data.

    • Validate Data checkbox: is always checked and is not editable for pulling the data in to the fact tables or when the single step collections are performed. The data are always validated at the time of pulling into the fact tables.

    • Stream Name: it is possible to bring in only one individual data stream by specifying the Data Stream Name, such as FCC1 for Forecast for Customer 1. If this field is left blank, all the data pertaining to the Customer Forecast data stream definitions exist.

  3. Select Submit to run the data pull concurrent program.

    The collection process for the flexible data stream is complete. For details, see: Uploading Flexible Data Streams.

    To use a data stream in a demand plan, see: Procedure to define a Demand Plan.

Customizing the Display Labels for Amount, Quantity, and Time

To customize the labels for Amount, Quantity, and Time displayed:

  1. Choose the Demand Planning Administrator responsibility.

  2. To open the Column Names window, select Setup > Data Streams > Column Names in the Navigator.

    the picture is described in the document text

You can specify new column names that would then appear in the list of values for the Column Name in Define Data Usage window (in Step 6). New column names can be specified only for amount, time, and quantity columns. For example, if you want Amount to appear as Contract Purchases in the Oracle Demand Planning user interface, use the values in the following table:

Name Description Type User Prompt
Contract Purchases Based on flexible contracts Amount Contract Purchases

The label Contract Purchases is now displayed in place of Amount. From the Define Data Usage window (in Step 6), select Contract Purchases for the Column Name.

Uploading Flexible Data Streams

The following diagram explains the process of uploading the data into Oracle Demand Planning after the flexible stream has been defined.

the picture is described in the document text

Data can be staged into the staging table, MSD_ST_CS_DATA by using SQL*Loader or other utilities. Once data has been staged, you must run the Custom Data Pull program. This pull program pulls valid rows into the fact table, MSD_CS_DATA while invalid rows remain in MSD_ST_CS_DATA. This process was explained earlier in the User Procedure section (in Step 12).

Collecting data from an ERP source instance

If the source of data is an ERP Source Instance, the flexible data stream should be brought into the Oracle Demand Planning staging table, MSD_ST_CS_DATA by running a preseeded Custom Data collection program. The Demand Planning System Administrator can invoke the collection program.

To collect data into a flexible data stream from an ERP source instance:

  1. Choose the Demand Planning System Administrator responsibility.

  2. In the Navigator, select Collections > Collect Data > Custom Data Stream.

    Follow the collection process as described in the Data Collection section.

    Depending on the source of flexible data stream data, you may need to run Data Collection from the source instance. This brings data into the demand planning flexible data stream staging table. You may then need to run a Data Pull program to move the data from the demand planning flexible data stream staging table into the demand planning flexible data stream fact table (in Step 12). These dependencies are shown in the following table:

    Source of Data Collect Data into Staging Table, MSD_ST_CS_DATA Pull Data from the Staging Table to the Fact Table, MSD_CS_DATA
    Interface Not applicable. The data staged by some SQL Loader or other utility. Applicable.
    Planning Server Not applicable. Not applicable.
    ERP Applicable. Applicable.

    The data for a data stream must be at the same level as specified for that data stream. The data at levels other than the data stream definition are discarded during the data pull process.

Attributes of Staging and Fact tables

Oracle Demand Planning holds information about flexible data streams in two identically structured tables: a staging table MSD_ST_CS_DATA and a fact table MSD_CS_DATA. If you are bringing data from an ERP source instance into Oracle Demand Planning, that means running the preseeded collection program to bring your data into the demand planning flexible data stream staging table, and then running the data pull program to bring the data into the demand planning flexible data stream fact table. Or if you are bringing data from flat files into Oracle Demand Planning, that means you are populating the demand planning flexible data stream staging table directly, perhaps via SQL*Loader. In either case, you will need to know what kind of flexible data stream information each staging table column is intended to hold. In the first case, you will need this information so that you can appropriately fill out the mapping between the columns of your source data view and the staging table columns (in Step 5). In the second case, you will need this information to know which parts of your flat file information to load into which staging table columns.

If you are bringing information from the Demand Planning Server into Oracle Demand Planning, you will need to note the default mapping between the Planning Server view columns and the demand planning flexible data stream fact table columns provided in the Define Data Usage window (in Step 5), and build your planning server view appropriately so that the right kind of flexible data stream information gets populated into each column of the flexible data stream fact table MSD_CS_DATA.

The required information about what each column in the demand planning flexible data stream staging and fact tables is intended to hold is shown in the following table:

Table Column Column Identifier Comments
ATTRIBUTE_1 INSTANCE Instance that represents the data stream.
ATTRIBUTE_2 PRD_LEVEL_ID Level identifier for the Product Dimension.
ATTRIBUTE_3 PRD_SR_LEVEL_VALUE_PK Source primary key of the level value for the Product Dimension.
ATTRIBUTE_4 PRD_LEVEL_VALUE Level value for the Product Dimension.
ATTRIBUTE_5 PRD_LEVEL_VALUE_PK Primary key of the level value for the Product Dimension.
ATTRIBUTE_6 GEO_LEVEL_ID Level identifier for the Geography Dimension.
ATTRIBUTE_7 GEO_SR_LEVEL_VALUE_PK Source primary key of the level value for the Geography Dimension.
ATTRIBUTE_8 GEO_LEVEL_VALUE Level value for the Geography Dimension.
ATTRIBUTE_9 GEO_LEVEL_VALUE_PK Primary key of the level value for the Geography Dimension.
ATTRIBUTE_10 ORG_LEVEL_ID Level identifier for the Ship From Location Dimension.
ATTRIBUTE_11 ORG_SR_LEVEL_VALUE_PK Source primary key of level value for Ship From Location Dimension.
ATTRIBUTE_12 ORG_LEVEL_VALUE Level value for the Ship From Location Dimension.
ATTRIBUTE_13 ORG_LEVEL_VALUE_PK Primary key of the level value for the Ship From Location Dimension.
ATTRIBUTE_14 Not applicable. Reserved for future use.
ATTRIBUTE_15 Not applicable. Reserved for future use.
ATTRIBUTE_16 Not applicable. Reserved for future use.
ATTRIBUTE_17 Not applicable. Reserved for future use.
ATTRIBUTE_18 REP_LEVEL_ID Level identifier for the Sales Representative Dimension.
ATTRIBUTE_19 REP_SR_LEVEL_VALUE_PK Source primary key of level value for Sales Representative Dimension.
ATTRIBUTE_20 REP_LEVEL_VALUE Level value for the Sales Representative Dimension.
ATTRIBUTE_21 REP_LEVEL_VALUE_PK Primary key of the level value for the Sales Representative Dimension.
ATTRIBUTE_22 CHN_LEVEL_ID Level identifier for the Sales Channel Dimension.
ATTRIBUTE_23 CHN_SR_LEVEL_VALUE_PK Source primary key of the level value for Sales Channel Dimension.
ATTRIBUTE_24 CHN_LEVEL_VALUE Level value for the Sales Channel Dimension.
ATTRIBUTE_25 CHN_LEVEL_VALUE_PK Primary key of the level value for the Sales Channel Dimension.
ATTRIBUTE_26 UD1_LEVEL_ID Level identifier for the User Defined Dimension 1.
ATTRIBUTE_27 UD1_SR_LEVEL_VALUE_PK Source primary key of the level value for User Defined Dimension 1.
ATTRIBUTE_28 UD1_LEVEL_VALUE Level value for the User Defined Dimension 1.
ATTRIBUTE_29 UD1_LEVEL_VALUE_PK Primary key of the level value for the User Defined Dimension 1.
ATTRIBUTE_30 UD2_LEVEL_ID Level identifier for the User Defined Dimension 2.
ATTRIBUTE_31 UD2_SR_LEVEL_VALUE_PK Source primary key of the level value for User Defined Dimension 2.
ATTRIBUTE_32 UD2_LEVEL_VALUE Level value for the User Defined Dimension 2.
ATTRIBUTE_33 UD2_LEVEL_VALUE_PK Primary key of the level value for the User Defined Dimension 2.
ATTRIBUTE_34 TIME_LEVEL_ID Level identifier for Time Dimension; MSD_PERIOD_TYPE lookup.
ATTRIBUTE_35 Not applicable. Reserved for future use.
ATTRIBUTE_36 Not applicable. Reserved for future use.
ATTRIBUTE_37 Not applicable. Reserved for future use.
ATTRIBUTE_38 Not applicable. Reserved for future use.
ATTRIBUTE_39 Not applicable. Reserved for future use.
ATTRIBUTE_40 Not applicable. Reserved for future use.
ATTRIBUTE_41 QUANTITY Quantity.
ATTRIBUTE_42 AMOUNT Amount.
ATTRIBUTE_43 END_DATE Date: YYYY-MM-DD.
ATTRIBUTE_44 PRICE Price.

Staging the data using SQL*Loader

If you are bringing in a flexible data stream into Oracle Demand Planning via flat files, the data can be loaded into the staging table MSD_ST_CS_DATA using the SQL* Loader utility.

Following is a sample SQL*Loader control file, which can be used to upload data for any flexible data stream.

Control file stcsdata.ctl

LOAD DATA

INFILE stcsdata.csv

APPEND

INTO TABLE msd_st_cs_data

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\""

TRAILING NULLCOLS

(

CS_DEFINITION_ID INTEGER EXTERNAL,

CS_NAME CHAR "NVL( :CS_NAME, 'PRODUCTION')",

ATTRIBUTE_1 CHAR,

ATTRIBUTE_2 CHAR,

ATTRIBUTE_3 CHAR,

ATTRIBUTE_4 CHAR,

ATTRIBUTE_5 CHAR,

ATTRIBUTE_6 CHAR,

ATTRIBUTE_7 CHAR,

ATTRIBUTE_8 CHAR,

ATTRIBUTE_9 CHAR,

ATTRIBUTE_10 CHAR,

ATTRIBUTE_11 CHAR,

ATTRIBUTE_12 CHAR,

ATTRIBUTE_13 CHAR,

ATTRIBUTE_18 CHAR,

ATTRIBUTE_19 CHAR,

ATTRIBUTE_20 CHAR,

ATTRIBUTE_21 CHAR,

ATTRIBUTE_22 CHAR,

ATTRIBUTE_23 CHAR,

ATTRIBUTE_24 CHAR,

ATTRIBUTE_25 CHAR,

ATTRIBUTE_26 CHAR,

ATTRIBUTE_27 CHAR,

ATTRIBUTE_28 CHAR,

ATTRIBUTE_29 CHAR,

ATTRIBUTE_30 CHAR,

ATTRIBUTE_31 CHAR,

ATTRIBUTE_32 CHAR,

ATTRIBUTE_33 CHAR,

ATTRIBUTE_34 CHAR,

ATTRIBUTE_41 CHAR,

ATTRIBUTE_42 CHAR,

ATTRIBUTE_43 CHAR,

ATTRIBUTE_44 CHAR,

CS_ST_DATA_ID SEQUENCE(MAX,1)

)

The following table shows a sample flat file format used by the Control File:

Column Position Column Value
1 cs_definition_id
2 cs_name
3 instance
4 prd_level_id
5 prd_sr_level_value_pk
6 prd_level_value
7 prd_level_value_pk
8 geo_level_id
9 geo_sr_level_value_pk
10 geo_level_value
11 geo_level_value_pk
12 org_level_id
13 org_sr_level_value_pk
14 org_level_value
15 org_level_value_pk
16 rep_level_id
17 rep_sr_level_value_pk
18 rep_level_value
19 rep_level_value_pk
20 chn_level_id
21 chn_sr_level_value_pk
22 chn_level_value
23 chn_level_value_pk
24 ud1_level_id
25 ud1_sr_level_value_pk
26 ud1_level_value
27 ud1_level_value_pk
28 ud2_level_id
29 ud2_sr_level_value_pk
30 ud2_level_value
31 ud2_level_value_pk
32 time_level_id
33 quantity
34 amount
35 end_date
36 price

A sample flat file that follows the above format is shown following. The first row ending with the label price is a row of headings that is ignored by SQL*Loader. This flat file can be created in a spreadsheet by saving the spreadsheet in a comma-delimited (.CSV) format.

Sample flat file

cs_definition_id, cs_name, instance, prd_level_id, prd_sr_level_value_pk, prd_level_value, prd_level_value_pk, geo_level_id, geo_sr_level_value_pk, geo_level_value, geo_level_value_pk, org_level_id, org_sr_level_value_pk, org_level_value, org_level_value_pk, rep_level_id, rep_sr_level_value_pk, rep_level_value, rep_level_value_pk, chn_level_id, chn_sr_level_value_pk, chn_level_value, chn_level_value_pk, ud1_level_id, ud1_sr_level_value_pk, ud1_level_value, ud1_level_value_pk, ud2_level_id, ud2_sr_level_value_pk, ud2_level_value, ud2_level_value_pk, time_level_id, quantity, amount, end_date, price

121,GMTRIAL1,381,1,AS54888,,,,,,,,,,,,,,,,,,,,,,,,,,9,3360,1308662.6,12/5/01,

To load the data in the flat file into Oracle Demand Planning via the flexible data stream staging table, run SQL*Loader as follows:

sqlldr user/password@dbname control = stcsdata.ctl

Pulling data from Staging Table to Fact Table

Run the pull program to pull data from staging table into the fact table. The pull program pulls valid data only. Erroneous records remain in MSD_ST_CS_DATA OR_DESC will have details of the error. This error detail is a text string, an example of which follows:

MSD_CS_DATALOAD_INVALID_LVLID: For ORG and PRD

MSD_CS_DATALOAD_INVALID_DIM: For GEO

MSD_CS_DATALOAD_INVALID_DATE_FORMAT

The different segments of this error detail mean the following:

Collect Filter Condition

The Collect Filter Condition is an additional where clause that is applicable only to data streams that are based on information collected from an ERP source instance, which entails the creation of your own source view. To provide flexibility in what data are collected from a source instance, you can specify filter conditions on the Define Characteristics window (in Step 3). The syntax of the filter text is very similar to that of a SQL where clause, and is explained by means of the following examples.

Example 1 Filtering Data on the Basis of the Date Field

Booked_date between '&&DATE:MSD_FROM_DATE and '&&DATE:MSD_TO_DATE

This example filters the source data on the basis of the demand date as specified in the source view:

If you enter 01-JAN-2002 for MSD_FROM_DATE and 31-DEC-2002 for MSD_TO_DATE, this example will be translated at runtime as follows: BOOKED_DATE between 01-JAN-2002 and 31-DEC-2002.

Example 2 Filtering Data on the basis of Multiple Parameters

designator = '&&CHAR:MSD_FCST_DESIGNATOR and forecast_Date between '&&DATE:MSD_FROM_DATE and '&&DATE:MSD_TO_DATE

Impact of Custom Data Streams on Hierarchy and Fact Views

New Demand Planning Server views for Booking Data, Shipment Data, Manufacturing Forecast, and Sales Opportunities are created for the flexible data streams feature. These have different structures than the old views. Oracle Demand Planning Server no longer references the old views. The list of new Demand Planning Server views for flexible data streams is as follows:

Stream Name Old View New View
Booking Data MSD_BOOKING_DATA_V MSD_BOOKING_DATA_CS_V
Shipment Data MSD_SHIPMENT_DATA_V MSD_SHIPMENT_DATA_CS_V
Input Manufacturing Forecast MSD_MFG_FCST_V MSD_MFG_FCST_CS_V
Sales Opportunity MSD_OPPORTUNITY_DATA_V MSD_OPPORTUNITY_DATA_CS_V

If, before upgrading to when flexible data streams was introduced, you have customized an old planning server view to add filtering conditions, and the view still brings in data at the seeded lowest levels, you may simply customize in the same fashion the corresponding new planning server view. Examples of seeded lowest levels are Item, Ship to Location, and Organization. Alternatively, you may create a new flexible data stream to hold your data.

If you have customized a planning server view such that it brings in data at a level different than the seeded lowest level, you must create a new flexible data stream to hold your data. If you want to use multiple types of dates for the same data stream, you need to define one flexible data stream for each type of date. For example, Booked Date, Shipped Date, Promised Date, and Requested Date for the Booking History data stream. This is because the flexible data stream feature supports only one date type per data stream.