BI - PSS Database Replica

Owned by Ezequiel Rozenberg
Last updated: Feb 26, 2021
2 min read

Sales Tables

In these tables, you will find information related to the issuance of documents (TKT, EMD, RFND).

Document and coupon amounts (fare, fee, taxes, commission) are converted to Currency_1 and Currency_2 currencies which are configured in the PSS JJSTAT table. A change of currency in JJSTAT won’t affect the existing records; the new currency will be effective for the records to be inserted after the change is done.

In case a holding owns more than one host, information of all carriers will be populated in the same database in order to consolidate all the information.

BI_Sales_Documents

This table has the information of issued documents. Each row represents a form of payment (FOP). For example, if a document was paid with CASH and CREDIT CARD, two records will be here for that document. For counting purposes, “Quantity” column should be used as only the first FOP will have value “1“.

If a ticket is voided, new records will be inserted in this table with negative amounts and quantity. The “Document_Transaction“ will be TKTX.

BI_Sales_Coupons

This table has the information of each coupon within the documents.

In order to join the tables Coupons and Documents use “Main_Document_Number”, “Year_of_Issue“ columns and “Form_Of_Payment_Number” equal 1.

 

Flown Tables

In this set of tables, you will find flown data. When a flight is closed at check-in (either manual or automatic) at least BI_Flown_Legs BI_Flown_Cabins, BI_Flown_Passengers, and BI_Flown_Leg_Passenger tables will be populated.

To join the data across tables you should use the columns:

  • "Carrier"

  • "Flight_Number"

  • "Flight_Departure_Date"

  • "Airport_Number_From"

  • "Airport_Number_To"

In case of adding BI_Flown_Passengers, BI_Flown_Leg_Passengers, BI_Flown_Leg_Passengers_Not_Boarded or BI_Flown_Passengers_Not_Boarded you should also include the “Cabin” column.

 

BI_Flown_Legs

This table has information about flown legs. For each leg you will find a record (Key "Carrier", "Flight_Number", "Flight_Departure_Date", "Airport_Number_From", "Airport_Number_To) and the general information about it, origin, destination, equipment, cost, distance, capacity.

If the flight was automatically closed by KIU DCS, the “Flight_Status” will be “KIU CHECK-IN CLEAN”

BI_Flown_Cabins

This table has information about cabins. For example, if a flight has economy and business, two records will exist here. The field “AircraftCapacity“ in this context makes reference to the cabin capacity.

KPIs such as AKO, PKT are present in this table.

BI_Flown_Leg_Delays

In case of a registered delay in a leg, you will find the code, description, and the delay here.

BI_Flown_Leg_Passengers

This table has information about transported passengers within each cabin. If a passenger is in a multileg flight, a record for each leg will be inserted. The fare is prorated between these legs based on the distance flown. This division in each leg allows us to obtain the load factor of the leg/cabin.

BI_Flown_Leg_Passengers_EMD

This table has information about the EMDs used in the leg. To join this table with BI_Flown_Leg_Passengers, you must use "Carrier", "Flight_Number", "Flight_Departure_Date", "Airport_Number_From", "Airport_Number_To”, “Record_Locator” and “Passenger_Name”.

BI_Flown_Leg_Passengers_Not_Boarded

This table has information about the not-boarded passengers in the leg. No show or denied boarding passengers can be found here.

BI_Flown_Passengers

The same information as BI_Flown_Leg_Passengers but in a segment level.

BI_Flown_Passengers_EMD

The same information as BI_Flown_Leg_Passengers_EMD but in a segment level.

BI_Flown_Passengers_Not_Boarded

The same information as BI_Flown_Leg_Passengers_Not_Boarded but in a segment level.