Professional > Data management scripting > Publishing data > UNICOM Intelligence RDB DCS 2 schema update
 
UNICOM Intelligence RDB DCS 2 schema update
The UNICOM Intelligence RDB2 database is updated as follows in order to support the Publish Data feature. For more information about the RDB DSC2 schema, see Relational MR Database CDSC schema.
PublishLabels table
The PublishLabels table surfaces the category label.
The Language, Context, LabelType, MDD version, and project name are kept in the table in order to reduce the database size. The LabelId column is used in the CategoriesLookup, DBQLookup, and VariablesLookup tables.
Key
Column name
Column type
Yes
LabelId
Integer
 
Language
Nvarchar(100)
 
Context
Nvarchar(100)
 
LabelType
Nvarchar(100)
 
MDMVersion
Nvarchar(100)
 
ProjectName
Nvarchar(255)
CategoriesLookup table
The CategoriesLookup table surfaces the categorical value labels.
Key
Column name
Column type
Description
Yes
VariableID
Integer
Question identifier, that is the same as the identifier in the Variables table.
Yes
CategoryID
Integer
Category value for each category item.
 
CategoryIndex
Integer
Sequential order for each category item.
 
Label
Nvarchar(4000)
Category description for each category item.
Yes
LabelId
Integer
Defined LabelID that is used for referring to LCL, MDMVersion, and Project name.
The lookup views for each dimension question (categorical, boolean, and so on) are created with the name <QuestionName>_Lookup.
Categories table
The Categories table records factor information for single categoricals.
Key
Column name
Column type
 
VariableID
Integer
 
CategoryID
Integer
 
Factor
Float
VariableEX table
The VariablesEX table is used for sub-questions in an expanded loop. The table is extended when compared to the existing RDB2 Variable table.
In order to avoid duplicate label information for each variable instance (such as Order[First].Column; Order[Second].Column), when creating the question view for sub-questions in an expanded loop, a virtual variableID is created for Order.Column. variableID is used in the Categorieslookup and DBQLookup tables. A negative variableID value is used in order to avoid conflicts with existing variableIDs in the variables table. For example, when varaibleID (in the VariablesEX table) for Order.Column is 3, the Order.Column value is set as -3 in the CategoriesLookup table.
DBQLookup table
The DBQLookup table surfaces the categorical value labels for DBQ questions.
Key
Column name
Column type
Description
Yes
VariableID
Integer
Question identifier, that is the same as the identifier in the Variables table.
Yes
CategoryIndex
Integer
Sequential order for each category item.
 
Label
Nvarchar(4000)
Category description for each category item.
Yes
LabelId
Integer
Identifier for LCL, MDMVersion, and Project name.
 
IntVal
Integer
Keep fact value for database question of type Long.
 
RealVal
Float
Keep fact value for database question of type Double.
 
TextVal
Nvarchar(255)
Keep fact value for database question of type Text.
 
DateVal
DateTime
Keep fact value for database question of type Date.
 
BoolVal
bit
Keep fact value for database question of type Boolean.
VariablesLookup table
The VariablesLookup table surfaces labels with all LCL settings, as well as labels with detailed Language, Context, LabelType, MDMVersion, and other settings.
Key
Column name
Column type
Yes
VariableId
Integer
Yes
LabelID
Integer
 
Label
Nvarchar(4000)
Loop1, Loop2…LoopN
Loop1, Loop2…LoopN is used to create a loop question view for expanded loops. In the RDB2 database, case data for HDATA loops is stored in different table levels depending on the level depth. Conversely, case data for VDATA loops is not stored at table levels.
In order to create similar question views for HDATA loop questions, Loop1,Loop2…LoopN is used to retain the expanded loop structure. Case data is stored in different tables based on the loop depth. For example, case data is stored in Loop1 when the loop is at the top level. The following table lists metadata information for Person.Tvdays in Loop2.
Person.Tvdays metadata in Loop2
 
VariableName
Variable
ID
LevelID1
LevelID
2
NumLevel
ID1
NumLevel
ID2
VariableInstance
1
person.tvdays. Column
38
1
Channel_1
NULL
1
person[1].tvdays [{Channel_1}].Column
2
person.tvdays. Column
39
1
Channel_2
NULL
2
person[1].tvdays [{Channel_2}].Column
3
person.tvdays. Column
40
1
Channel_3
NULL
3
person[1].tvdays [{Channel_3}].Column
4
person.tvdays. Column
41
1
Channel_4
NULL
4
person[1].tvdays [{Channel_4}].Column
5
person.tvdays. Column
42
1
Channel_5
NULL
5
person[1].tvdays [{Channel_5}].Column
6
person.tvdays. Column
43
2
Channel_1
NULL
1
person[2].tvdays [{Channel_1}].Column
7
person.tvdays. Column
44
2
Channel_2
NULL
2
person[2].tvdays [{Channel_2}].Column
8
person.tvdays. Column
45
2
Channel_3
NULL
3
person[2].tvdays [{Channel_3}].Column
9
person.tvdays. Column
46
2
Channel_4
NULL
4
person[2].tvdays [{Channel_4}].Column
10
person.tvdays. Column
47
2
Channel_5
NULL
5
person[2].tvdays [{Channel_5}].Column
11
person.tvdays. Column
48
3
Channel_1
NULL
1
person[3].tvdays [{Channel_1}].Column
12
person.tvdays. Column
49
3
Channel_2
NULL
2
person[3].tvdays [{Channel_2}].Column
13
person.tvdays. Column
50
3
Channel_3
NULL
3
person[3].tvdays [{Channel_3}].Column
14
person.tvdays. Column
51
3
Channel_4
NULL
4
person[3].tvdays [{Channel_4}].Column
15
person.tvdays. Column
52
3
Channel_5
NULL
5
person[3].tvdays [{Channel_5}].Column
16
person.tvdays. Column
53
4
Channel_1
NULL
1
person[4].tvdays [{Channel_1}].Column
17
person.tvdays. Column
54
4
Channel_2
NULL
2
person[4].tvdays [{Channel_2}].Column
When the loop is categorical, NumLevelID is calculated based on the iteration's categorical name (the iteration's integer value is required for computing serial at a different level).
See also
Adding views
Publishing data