There are several ways to feed the foundation layer depending on the use case and based on the strategy chosen.
Feed Lookup Tables
Typically the first action and data load is to feed all used lookup tables with the authorized values for each of the case, and one row for default (Unknown/Undefined) code. For the later, standard in Oracle Communications Data Model is to use "-5000". It has the advantage of generally not being used or associated with a meaning by any applications (contrary to "-1"), and it is a (negative) number, so that the rule of using numbers as code is followed as well as the possibility to easily exclude those undefined rows from an analysis by leveraging something like (to_number(code)>=0);Lookup tables could be fed on the fly as data come or one time only as definitive input in one specific ETL. The advantage of the second method is to prevent data quality issues in the input. Rows with unknown or undefined codes will be rejected before being loaded into Oracle Communications Data Model - due to foreign key constraints (if they are not switched off). For this method (all lookup at initial load), we suggest to use a simple Excel File with a small program to turn the CODE, NAME and Description field (typical for lookup) into a small SQL script as a list of insert that can then be run once for all... and eventually reused if some new values must be added.
Feed Reference Tables
For Primary keys, you may decide to use either automatic sequences - which are pre-defined in several of the main reference tables - or your own primary key generated as you prefer.
In some cases, it makes it easier to join with other applications. Please note that the SOURCE SYSTEM IDENTIFIER attribute (
SRC_SYS_IDNT column) should be used to store the code of the original record of the main source application that provided the natural key (stored normally in the "CODE" attribute). The "Main" source means that in case of multiple sources (multiple CRM, multiple Billing, and so on...), the code of the source application which got priority to provide the information for this row shall be used.
Foreign keys can and should be switched off if they have no interest (or no data) at the time of loading. Note that updating the rows later with the full information could become costly. So if information is available, it is usually better to store it right from the beginning.
Feeding Super Types and Sub-types
Whenever in LDM, entities related to sub-types and to a super-type are all physicalized, it is advisable to have data in both the sub-type and the super-type.
In the Sub-type, you should have the full information with all details. It is there that it should be maintained.
In the super-type, only the mandatory keys should be filled: Typically, the Primary Key, the
XXX_TYP_CD (being normally the entity name of the sub-type) and some mandatory FKs (if constraints were not switched off)
In case of hierarchies of super-type, one should feed only the highest meaningful one, and some of the ones in-between if and only if there is a relationship required that cannot be built otherwise (from neither the sub-type nor the highest super-type).
Example: If we need to feed end-user devices, let us assume handset, we could use the HANDSET INSTANCE table (
DWR_HNDST_INSTNC), which is a sub-type of EQUIPMENT INSTANCE (
DWR_EQPMNT_INSTNC), itself a sub-type of PRODUCT (
Nota: You may notice that it is also related to a Resource but it is not a sub-type of Resource as Oracle Communications Data Model takes into account the fact you can buy a handset from the store (hence, available as purchasable product). But of course, it is itself related to Resource (when required) via the FK of RESOURCE (
DWR_RSCE) to PRODUCT (
Normally, we could feed only
DWR_HNDST_INSTNC table, and
DWR_PROD table (as there is a FK between the 2). In
DWR_PROD, one would feed only the columns:
PROD_CD (possibly - optional)
Feed Base Tables
After filling lookup and references tables, Base tables can then be fed, unless another strategy is chosen.
Base tables contain the transactions and hence transform into facts. They are therefore critical from a content as well as from an availability perspective (for end-users).
In the normal case, the base tables should have all their relationships (via FKs) to lookup and reference tables set correctly at loading time, so that intra-ETLS can run smoothly.
Of course, this assumes that the content in transactions are timely coherent with the load of reference data (information related to customer, account, subscription etc... must be synchronized.).
This is usually where it can hurt and that sometimes, it is preferable to load the transaction data "as is", and then let the intra-ETLs do the join work. The other use cases where this option should be chosen is also for Real-time feed (of Oracle Communications Data Model) for operational reporting and/or when the foundation layer of Oracle Communications Data Model is used as Operational Data Store.
Please note that a near-real-time feed (say every 5mns) could still cope with timely coherent reference data, especially when Change Data Capture techniques are used and when the source applications of transactions and reference data are the same. Then, we can assume that the application did the coherence check and the full load can be done.
In any case, before moving data to the analytic layer, the association work between the information in the transaction data and the reference/lookup tables is required.
Examples of real-time feed:
Network Data (Data from Network Elements, Alerts and Resource alarms, Signaling data, etc...), typically for real-time operational reporting, also because they are sometimes already pre-aggregated.
CDRs (raw or mediated mainly, as CDRs from a rating engine, you could expect a timely coherent feed from the rating or billing system)
Events from Policy Applications
Data center feeds
Note that in each case, depending on the information density of the data, one should think of possibly leveraging a Big Data appliance to catch and concentrate the information before loading it into the enterprise data warehouse where disk space and maintenance is more critical.
Additionally, a real-time feed usually requires to add a specific transaction table to Oracle Communications Data Model (simple customization) and to change the intra-ETL correspondingly (NB: Making a copy of the original program and then customizing the copy is the recommended approach in such case).