2gLDB
Database Dictionary
Introduction:
The core of the LDB is composed by a relational database whose object is to manage time series information. The present technical document will focus on describing the objects and relations that compose the LDB system in order to respond to the following issues related to time series management, among them:
● High frequency data;
● Multiple sources, multiple versions;
● Sub-national data
● Multiple owners & security
This document should only be used as a technical description of the LDB system. It aims at describing the main objects (tables, views, procedures, functions and indexes) which are used by the system to manage time series. Some objects from the system will not be described here, mainly because they are World Bank specific objects, temporary ones or simply past attempts to solve specific issues. These latter can be safely removed from the system.
Table Objects:
Name / DescriptionCOUNTRY / Information about the different countries managed by the LDB
AG_REGION / List of country groupings per defined region
REGION / List of all the regions for which countries belong to
CETS / Information about all the indicators (CETS) used by the system
TOPIC / Topics table of the corresponding indicators
RDB / Header of time series
PITVAL / Point in Time values for the time series
ACCESS_RIGHTS / Access rights on the time series
AG_RULES / Aggregation rules of the indicators
AG_WEIGHTS1995 / Value of specific indicators for 1995 – used by aggregation rules
DV_RULES / Derivation rules of the time series
EQUATIONS / Equation table of the time series
LINE_(% / Temporary table to hold computed values for aggregations
RDBFLAT / time series table in a “flat” format
SOURCE_DIM / Sources of the time series. Source dimension for cube.
TIME_DIM / Range of dates for values taken by indicators. Time dimension for cube.
USER_RIGHTS / Access rights per user to manage system
VERSION_DIM / Versions of the different time series. Version dimension for cube.
1
Data Dictionary
Schema used by the LDB:
1
Data Dictionary
I. Tables currently used by the LDB
TABLE COUNTRYName / R.I. / Type / NULL / Description
CN_CODE / PK / CHAR(3) / Country code
CN_SUBNAT / PK / VARCHAR(32) / Sub-national level for the considered country
CN_DESC / VARCHAR(100) / Country description
CN_NAME0 / VARCHAR(32) / Country Name
REG_CODE / FK / CHAR(3) / Region code (foreign key on REGION)
CN_VAPORVAB / CHAR(3) / VAP or VAB country
CN_BASEYEAR / CHAR(4) / Base year for national account purposes
CN_CURRENCY / VARCHAR(22) / currency used in the country
CN_TRADE1 / VARCHAR(20) / X / Commodity 1
CN_TRADE2 / VARCHAR(20) / X / Commodity 2
CN_TRADE3 / VARCHAR(20) / X / Commodity 3
CN_TRADE4 / VARCHAR(20) / X / Commodity 4
POPULATION / NUMERIC / X / Country population
GNP_PER_CAPITA / NUMERIC / X / Country gnp per capita
TABLE AG_REGION
Name / R.I. / Type / NULL / Description
REG_CODE / PK / CHAR(3) / Code for the aggregated region
CN_CODE / FK / CHAR(3) / Country belonging to the aggregated region (FK on COUNTRY)
TABLE REGION
Name / R.I. / Type / NULL / Description
REG_CODE / PK / CHAR(3) / Code for the region
REG_DESC / VARCHAR(60) / X / Description of the region
TABLE CETS
Name / R.I. / Type / NULL / Description
CETS_CODE / PK / CHAR(25) (*) / CETS code
CETS_DESC / VARCHAR(50) / CETS description
CETS_SOURCE / VARCHAR(250) / X / Source of the CETS
TOPIC_KEY / INT / Topic key associated to the cets code (FK)
NUMBER_FORMAT / VARCAHR(20) / Describes the numeric format through which the indicator will be represented in the user interface
CETS_SCALE / VARCHAR(20) / Scale in units, thousands, … of the format
D_EQUATION / VARCHAR(250) / Derivation equation associated to the indicator
A_EQUATION / VARCHAR(250) / Aggregation equation associated to the indicator
T_EQUATION / VARCHAR(250) / Time equation associated to the indicator
LRANGE / REAL / Not used
HRANGE / REAL / Not used
TOPIC_CODE / CHAR(10) / Not used
CETS_CLDB / CHAR(10) / Currently not used
CETS_DEF / VARCHAR(1024) / Supplementary description for the indicator
(*) Some LDB systems have that field defined as CHAR(50) or CHAR(60) (Senegal)
TABLE TOPICName / R.I. / Type / NULL / Description
TOPIC_KEY / PK / INT / X / Primary key
TOPIC_CODE / CHAR(200) / Secondary topic description
TOPIC_MAIN / CHAR(100) / Main topic description
TABLE RDB
Name / R.I. / Type / NULL / Description
RDB_ID / PK / NUMERIC (ID) / Identifier of the time series
CN_CODE / FK / CHAR(3) / Country associated to the time series
TOPIC_CODE / FK / CHAR(10) / Topic associated to the time series (not maintained)
CETS_CODE / FK / CHAR(25) (*) / CETS code associated to the time series
RDB_CAT / FK / CHAR(1) / Category of the time series
RDB_VERSION / FK / CHAR(1) (**) / Version in the category
RDB_OWNER / CHAR(10) / Owner of the time series
RDB_FREQ / CHAR(1) / Frequency of the time series (Y,Q,M,W,D)
RDB_SCALE / REAL / Scale of the time series
RDB_UPDATE / DATE / Date of the last update of the T.S.
RDB_SUBREG / VARCHAR(32) / Sub-national level of the time series
(*) Some LDB systems have that field defined as CHAR(50) or CHAR(60)
(**) Some LDB systems have that field declared as integer.
TABLE PITVALName / R.I. / Type / NULL / Description
RDB_ID / PK / NUMERIC (ID) / Identifier of the time series
VAL_DATE / PK / DATE / Date associated with the value
VAL_VALUE / FLOAT / Value
VAL_EQUATION / VARCHAR(250) / X / Equation associated with a PIT value
TABLE ACCESS_RIGHTS
Name / R.I. / Type / NULL / Description
RDB_ID / PK / NUMERIC / Identifier of the time series
AR_OWNER / PK / CHAR(10) / Owner of the time series
AR_GRANTEE / PK / CHAR(10) / User to which access right is given
AR_RIGHT / PK / CHAR(1) / Access right given (W,R)
(PK: primary keyFK: foreign key)
TABLE AG_RULESName / R.I. / Type / NULL / Description
CWA / CHAR(1) / X / If NULL, then rules will not be processed, any other character: row will be processed. (not currently used)
no / int / aggregation line to be processed
[function] / varchar(50) / function applied to the current equation
result / varchar(50) / resulting aggregated indicator computed by the function.
arg1 / varchar(50) / first argument of the function
arg2 / varchar(50) / X / second argument of the function
arg3 / varchat(50) / X / third argument of the function
arg4 / varchar(50) / X / 4th argument of the function
arg5 / varchar(50) / X / 5th argument of the function
TABLE AG_WEIGTHS1995
Name / R.I. / Type / NULL / Description
cn_code / char(3) / Country code
cp_$_atlas_cap / float
cp_$_atasl_gnp / float
pr_popn / float / population
cp_exp_total / float / total exports
cp_imp_total / float / total imports
cp_l_agr_gdp / float / GDP for agriculture sector
cp_l_ind_gdp / float / GDP for industry
cp_l_man_gdp / float / GDP for manufactory
cp_l_setc_gdp / float / GDP in
cp_d_atlas_cap / float
cp_d_atlas_gnp / float
TABLE DV_RULES
Name / R.I. / Type / NULL / Description
CWA / CHAR(1) / X / If NULL, then rules will not be processed, any other character: row will be processed. (not currently used)
no / int / derivation line to be processed
[function] / varchar(50) / function applied to the current equation
result / varchar(50) / resulting derived indicator computed by the function.
arg1 / varchar(50) / first argument of the function
arg2 / varchar(50) / X / second argument of the function
arg3 / varchat(50) / X / third argument of the function
arg4 / varchar(50) / X / 4th argument of the function
arg5 / varchar(50) / X / 5th argument of the function
TABLE EQUATIONS
Name / R.I. / Type / NULL / Description
Result / varchar(255) / Result indicator
EqDesc / varchar(1024) / X / Short description of the equation
Equation / varchar(4000) / Equation to compute the result
Tolerance / float / tolerance between the result of the equation and the value taken by result
VapVab / char(3) / X / Not used - reserved
TABLE Line_95
Name / R.I. / Type / NULL / Description
cn_code / CHAR(3) / country code
value / float / temporary value used by the ax_total95 procedure
TABLE ownership
Name / R.I. / Type / NULL / Description
rdb_owner / CHAR(10) / owner of time series, as coded in the RDB table
name / varchar(25) / X / external name of the owner
TABLE RDBFLAT
Name / R.I. / Type / NULL / Description
country / varchar(50) / country name
country_name / float / country description
IND1 / varchar(50) / indicator code (CETS)
IND1_DESC / varchar(250) / indicator description
YRxxxx / float (repeated) / value taken by the indicator for year xxxx
YRxxxxQq / float (repeated) / value taken by the CETS, for year yyyy, quarter q
YRxxxMmm / float (repeated) / value taken by the CETS, for year yyy, month mm
Please note that the last 2 sets of fields are not yet implemented in the current system but will be soon for the WEB Query.
TABLE Source_dimName / R.I. / Type / NULL / Description
sourceDesc / varchar(25) / Source description
sourcekey / char(1) / source key
TABLE Time_Dim
Name / R.I. / Type / NULL / Description
Year / nvarchar(30) / Current year for the Day field
YearForWeeks / nvarchar(30) / Year associated to the week of the Day field
Semester / nvarchar(30) / Current Semester related to the Day field
Quarter / nvarchar(30) / Current quarter related to the Day field
Month / Current month related to the Day field
Week / Current week related to the Day field
MonthSort / integer value representing the current month
TimeDate / datetime / date related to the Day field
Day / nvarchar(30) / current Day field
WeekSort / int / integer value representing the current week
DayOfWeek / varchar(10) / day of week
DaySort / int / integer value representing the current day
Note: this table is automatically generated by a procedure. The range of dates is from 1965 to 2010. Run the procedure populate_time_dim to modify this range.
TABLE user_rightsName / R.I. / Type / NULL / Description
user_name / varchar(100) / account name in SQL linked to owner
rdb_owner / char(10) / owner of one or multiple time series (in RDB table)
Note: this table is used in the uploading and managing tools
TABLE VERSION_DIMName / R.I. / Type / NULL / Description
Versiondesc / varchar(25) / Version description
Versionkey / PK / char(1) or int / key of the time series version
Note:Some LDB systems are using an integer key for the version.
II. Views used by the LDB system
The following views are mainly used as dimensions for the corresponding cubes. All other views not described here are not used by the system.
View V_FACT_MONTH
Object:View on the RDB and PITVAL tables for monthly information (rdb_freq=’4’)
Used as a fact table for the monthly cube.
View V_FACT_QUARTER
Object:View on the RDB and PITVAL tables for quarterly information (rdb_freq=’3’)
Used as a fact table for the quarterly cube.
View V_FACT_SEMESTER
Object:View on the RDB and PITVAL tables for semi-annual information (rdb_freq=’2’)
Used as a fact table for the monthly cube.
View V_FACT_YEAR
Object:View on the RDB and PITVAL tables for annual information (rdb_freq=’1’)
Used as a fact table for the yearly cube.
View V_FACT_WEEK & V_FACT_DAY
Object:These views are not implemented because there is currently not need for that, but follow the same logic as the other views and can be easily built from them.
View V_GEOGRAPHY_DIM
ObjectGeography dimension for the corresponding cube. Gives the breakdown from the regional level down to the sub-national level.
View V_GEOGRAPHY_DIM
ObjectGeography dimension for the corresponding cube. Gives the breakdown from the regional level down to the sub-national level.
View V_TIME_DIM
ObjectTime dimension for the corresponding cube. Gives the breakdown from the yearly level down to the day level. Used in the corresponding cubes.
View V_CETS_FACT
Object:View on RDB & PITVAL tables containing information about all the times series in the system for which owner=’dba’, version=’1’ and source=’1’. dba (data administrator) owner is the default owner of the time series, version=’1’ is considered to be the nominal one and source=’1’ is supposed to be the institutional level where the solution is implemented.
For example, at the World Bank, owner=’dba’; source=’World Bank’ and version=’nominal’ corresponding to the indicated value in the LDB system.
III. FUNCTIONS
1. CHTD
Object:Converts a time string into a date
Syntax:FUNCTION CHTD( @str varchar(20)) RETURNS date
Input@strVARCHAR(20)date in form of a string
Outputdateassociated to the @str string
Environment:T-SQL, views
Examples:a) select dbo.chtd(‘YR2000’)returns 2000-01-01 00:00:00.000
b) select dbo.chtd(‘MO1201’) returns 2001-12-01 00:00:00.000
2. COALCOUNTRY
Object:Coalesce all countries belonging to a country group in a string.
Syntax:FUNCTION COALCOUNTRY (@region varchar(3)=NULL) RETURNS varchar(8000)
Input@regionVARCHAR(3)region code for which the user wants the list of associated countries. Can be NULL
Output:VARCHAR(8000)List of associated countries.
Environment:T-SQL, views
Example:select dbo.COALCOUNTRY ('CFA')
will return:
Benin,Burkina Faso,Central African Republic,Cote d Ivoire,Cameroon,Congo,Comoros,Gabon,Guinea-Bissau,Mali,Niger,Senegal,Chad,Togo
Remark: This function is used to provide a fly-over capability in ProClarity.
3. CONVERTDS
Object:Converts a date in a LDB compatible string format
Syntax:FUNCTION CONVERTDS( @date datetime, @freq integer) RETURNS varchar(40)
Input Arguments:@datedate for which the user wants a conversion
@freqfrequency associated to the date
OutputVARCHAR(40)
Environment:T-SQL, views
Examples:a) select dbo.convertds(convert(datetime,'01/06/02'), 1)will return YR2002
b) select dbo.convertds(convert(datetime,'01/06/02'), 4)will return MO062002
Notes:1) The function needs to be modified if working in a non-English environment where the date coding is different.
2) The LDB system utilizes the following convention to code the frequencies:
1=Year
2=Semi-annual
3=Quarter
4=Month
5=Week
6=Day
IV. Specific procedures not defined in the technical guides
The following notes only describe the procedures which have been defined for specific computational purposes. For derivation and aggregation, please refer to the corresponding technical guides and user manuals.
1. Procedure aggregate_1RallR
Object:Computes an aggregation rule for all regions,
Syntax:procedure aggregate_1RallR @no
Usage:exec aggregate_1RallR <line nr.>
where <line nr> is the line number of the aggregation rule to be executed in the aggregation table AG_RULES.
Environment:T-SQL, (also management tool, if enabled)
Example:execaggregate_1RallR 1200
will execute line 1200 in the aggregation table.
2. Procedure aggregate_all
Object:Computes all aggregations for all regions,
Syntax:procedure aggregate_all
Usage:exec aggregate_all
EnvironmentT-SQL
Example:execaggregate_all
All aggregations defined in the AG_RULES table will be executed for ALL the regions that are defined in the AG_REGION table.
3. Procedure aggregate_one_region
Object:Computes all aggregations for a specific country group.
Syntax:procedure aggregate_one_region <region name>
Usage:exec aggregate_one_region <region name>
will compute all aggregations for the <region name> country group.
EnvironmentT-SQL, (also management tool, if enabled)
Example:exec aggregate_one_region ‘AFR’
will compute all aggregations for the AFR (Africa) country group
4. Procedure derivation
Object:Computes all derivations defined in the DV_TABLES for all indicators, all countries
Syntax:procedure derivation
Usage:exec derivation
EnvironmentT-SQL, (management tool, if enabled)
Example:exec derivation
5. Procedure devpart
Object:Computes all derivations defined in the DV_RULES for all indicators, all countries
Syntax:procedure derivation
Usage:exec derivation
EnvironmentT-SQL, (management tool, if enabled)
Example:exec derivation
6. Procedure dev_onerule
Object:Computes the corresponding derivation defined in the DV_RULES for the specified rule, for all countries defined in the COUNTRY table
Syntax:procedure dev_onerule
Usage:exec dev_onerule <rule nr>
EnvironmentT-SQL, (management tool, if enabled)
Example:exec dev_onerule 100
executes rule number 100 in the DV_RULES table.
7. Procedure check_equations
Object:Will check all the equations defined in the EQUATIONS table, for the specified country or for all the countries.
Syntax:procedure check_equations [<cn_code>]
Usage:exec dev_onerule <cn_code>
EnvironmentT-SQL
Example:exec check_equations ‘SEN’
will check all the equations for the country Senegal.
Note:Please refer to the Equation Checker technical manual for a complete explanation of implementation.
8. Procedure FillRDBFLAT
Object:This procedure will “flatten” the times series defined in the RDB-PITVAL tables into the RDBFLAT table.
Syntax:procedure FillRDBFlat
Usage:exec FillRDBFlat
EnvironmentT-SQL
Example:exec FillRDBFlat
9. Procedure SwapCETSCodes
Object:This procedure will replace a CETS code by another one which is defined in the LDB system.
Syntax:procedure SwapCETSCodes <CETS1> , <CETS2>
Usage:exec SwapCETSCodes <cets1>, <cets2>
EnvironmentInternally used internally by LDB management tool. DO NOT MODIFY this procedure.
10. Procedure SwapTopicKeys
Object:This procedure will replace a topic code by another one already defined in the LDB system.
Syntax:procedure SwapTopicKeys <TOPIC1>, <TOPIC2>
Usage:exec SwapCETSKeys <topic1>, <topic2>
EnvironmentInternally used internally by LDB management tool. DO NOT MODIFY this procedure.
Note:Some other procedures which are not defined either here or in the derivation/aggregation technical guides are used by the World Bank for internal purposes, mainly data transfer among databases.
V. INDEXES defined over the LDB
table_name / constraint_name / column_name, / ordinal_positionACCESS_RIGHTS / PK_ACCESS_RIGHTS / RDB_ID / 1
ACCESS_RIGHTS / PK_ACCESS_RIGHTS / AR_OWNER / 2
ACCESS_RIGHTS / PK_ACCESS_RIGHTS / AR_GRANTEE / 3
ACCESS_RIGHTS / PK_ACCESS_RIGHTS / AR_RIGHT / 4
AG_REGION / PK_AG_REGION / REG_CODE / 1
AG_REGION / PK_AG_REGION / CN_CODE / 2
AG_RULES / PK_AG_RULES / no / 1
CETS / FK_CETS_TOPIC / TOPIC_KEY / 1
CETS / PK_CETS / CETS_CODE / 1
COUNTRY / cn_region_fk / REG_CODE / 1
COUNTRY / PK_COUNTRY / CN_CODE / 1
COUNTRY / PK_COUNTRY / CN_SUBNAT / 2
DV_RULES / PK_DV_RULES / no / 1
META / PK_META / cn_code / 1
META / PK_META / cets_code / 2
META / PK_META / timestamp / 3
Ownership / PK_Ownership / rdb_owner / 1
PITVAL / FK_PITVAL_RDB / rdb_id / 1
PITVAL / PK_PITVAL / rdb_id / 1
PITVAL / PK_PITVAL / val_date / 2
rdb / FK_RDB_CETS / cets_code / 1
rdb / FK_RDB_COUNTRY / cn_code / 1
rdb / FK_RDB_COUNTRY / rdb_subreg / 2
rdb / FK_RDB_Source_Dim / rdb_cat / 1
rdb / FK_RDB_VERSION_Dim / rdb_version / 1
rdb / IX_rdb_2 / cn_code / 1
rdb / IX_rdb_2 / cets_code / 2
rdb / IX_rdb_2 / rdb_freq / 3
rdb / IX_rdb_2 / rdb_owner / 4
rdb / IX_rdb_2 / rdb_cat / 5
rdb / IX_rdb_2 / rdb_version / 6
rdb / IX_rdb_2 / rdb_subreg / 7
rdb / PK_rdb / rdb_id / 1
REGION / PK_LKP_REGION / REG_CODE / 1
Source_Dim / PK_Source_Dim / SourceKey / 1
tblComment / PK_WbCETsComments / CetsCode / 1
tblComment / PK_WbCETsComments / DataSource / 2
tblComment / PK_WbCETsComments / Geography / 3
tblComment / PK_WbCETsComments / Time / 4
tblComment / PK_WbCETsComments / Version / 5
Time_Dim / PK_Time_Dim / TimeDate / 1
TOPIC / PK_TOPIC / TOPIC_KEY / 1
USER_RIGHTS / PK_USER_RIGHTS / rdb_owner / 1
VERSION_Dim / PK_VERSION_Dim / VersionKey / 1
1
Data Dictionary