V_FORMULA_TO_RAW

Business Intelligence (BI) Interface Guide › Business Intelligence Interface › SQL Interfaces › Additional Calculated Data Interface › V_FORMULA_TO_RAW_DATA

V_FORMULA_TO_RAW_DATA

This view enables the user to retrieve raw data events while filtering those events according to the formulas that used them.

The view exposes the field FORMULA_ID which is a foreign key to T_FORMULAS and in addition the 112 fields that make up the table T_RAW_DATA_FIELDS:

Name	Type	Content
FORMULA_ID	number	Foreign Key to T_FORMULAS It points to a formula that uses this event.
RAW_DATA_ID	number	The ID of the event in T_RAW_DATA
READER_ID	number	The ID of the reader (adapter) which read this event
EVENT_TYPE_ID	number	The ID of the type of this event
RESOURCE_ID	number	The ID of the resource of this event
TIME_STAMP	date	The time stamp of this event
READER_TIME_ STAMP	date	The time stamp of the reader of the event
CREATED_BY	number	0 if the event is a regular event read by an adapter or of this event is a result of a correction this would be the correction ID
CORRECTED_BY	number	0 if this event is relevant and there is no correction overriding it any other number would be a correction id which overrides this event
CREATE_DATE	date	The timestamp at which this event was entered to the system
MODIFY_DATE	date	The timestamp of the last change to this event
DATA_SOURCE_ID	varchar2(60)	The name of the input file from which the event is retrieved and that the adapter reads.
HASH_DATA_KEY	varchar2(16)	A value used to distinguish between events for sake of event singularity
DATA_HTML	CLOB	Aggregation of all values of all the dynamic fields of the event in HTML format including field names.
HTML_DATA_STR	varchar2(4000)	The first 4000 characters of the HTML data. Note: This field content might not be complete if the event data is longer than 4000 characters.
FIELD_001	varchar2(4000)	The 1st value of the first dynamic field of this event
FIELD_002	varchar2(4000)	The 2nd value of the first dynamic field of this event
FIELD_099	varchar2(4000)	The 99th value of the first dynamic field of this event
FIELD_100	varchar2(4000)	The 100th value of the first dynamic field of this event

Each event has an event type and each event type has a predefined structure which dictates how many (if at all) dynamic fields it has, their names, order and type. So one event of a certain type may have three dynamic fields and another may have 10 fields.

Each event in this view has 100 such fields named field_000 – field_100 but only the ones relevant to the event in question according to its event type have meaningful values in them, the rest hold the a null value.

Furthermore those fields may hold values of one of the following four types – integer, float, string & date. However as the same set of 100 fields should be able to hold event fields of events of different event types and as such with different data types, they are all treated as strings and represented in the views as varchar2 columns.

The usual way one would use this view would be to retrieve all events consumed by a certain metric, probably for a certain period of time.

So if we would want to retrieve all the raw data events that were taken into account in the calculation of a certain service level or, in other words, we would wanted to make the correlation between a record in one of the v_psl views and a record in the t_raw_data_fields table we would do the following:

Retrieve the formula_id from the relevant v_psl view.
Use this formula_id to filter the relevant records from v_formula_to_raw_data.
We may also want to further filter the events according to their timestamp so as to achieve the appropriate correlation between a set of PSL records at a certain timeframe and the set of events that have been consumed by the engine during the calculation of those PSL entries.