Professional > Data management scripting > Publishing data > UNICOM Intelligence RDB DCS 2 schema update > Adding views
 
Adding views
Note The following section uses the UNICOM Intelligence Developer Documentation Library Household sample.
Views are created when data is published. Except for CategoriesLookupView, DBQLookupView, and VariablesLookupView (which are used to surface category labels), most views are used to surface case data in Cognos. The view names vary according to the surfaced questions names.
VariablesLookupView
The view is used to surface question labels (VariableID, VariableName, Language, Context, LabelType, and so on).
CategoriesLookupView
This view is built against the CategoriesLookup and PublishedLabels tables and includes label information for all categorical questions based on specific Language, Context, LabelType, metadata version, and project name. The view is used to create a lookup query object, in the Cognos Framework Manager Model, for each category question filtered by the VariableID column. The following table shows the Respondent.Origin question labels and includes four columns:
VariableID
The same as VariableID in the Variables table, and serves as an identifier for each question.
CategoryIndex
Lists category items, in sequential order, for categorical questions. The index is used to support categorical data and can be sorted according to the metadata order when rendering reports in Cognos.
CategoryID
The category value for each category that is defined in the MDD file.
Label
Label information for each category that is defined in the MDD file. The information is based on specific Language,Context, LabelType, metadata version, and project name.
Note NA (Auto Created) is the label for empty responses that are not defined in the MDD file (the label is created when publishing data to Cognos).
Respondent.Origin question labels
Variable
ID
Category
Index
Category
ID
Label
3
1
127
Scan
3
2
128
Interviewer Server/HTML Player
3
3
129
Interviewer Server CATI/HTML Player
3
4
130
Interviewer Server CATI/TTY Player
3
5
131
Data Entry Player
3
6
32
Other
3
7
7
NA (Auto Created)
<QuestionName>_Lookup View
This is the label lookup view for each dimension question label (categorical, boolean, and so on) for all surfaced LCL. The view is helpful when rendering reports that are based directly on RDB2 data in SQL Server.
DBQLookupView
This view is built against the DBQLookup and PublishedLabels tables, and includes label information for all database questions, which are based on a specific Language, Context, LabelType, MDD version, and project name. The view is used to create a lookup query object, in the Cognos Framework Manager Model, for database questions. DBQLookupView is similar to the CategoriesLookupView view. The view's structure is:
DBQLookupView structure
Variable
ID
Category
Index
Label
IntVal
RealVal
TextVal
DateVal
BoolVal
The main difference between DBQLookupView and CategoriesLookupView is that DBQLookupView factors for each category, not the category value that exists in the view. Because database question factors are stored in the RDB2 case data, and the factor types may be different, the factor values may be stored in different columns depending on their type. For database questions with a type of mtLong, factor values are stored in IntVal.
The view names for the Household sample are:
dbo.DataCleaning.ReviewStatus
dbo.DataCleaning.Status
dbo.DataCollection.Status
dbo.Dimensions
dbo.Fact
dbo.person.Dimensions
dbo.person.Fact
dbo.person.languages
dbo.person.trip.country
dbo.person.trip.Dimensions
dbo.person.trip.Fact
dbo.person.trip.purpose
dbo.person.tvdays.Dimensions
dbo.Respondent.Origin
dbo.vehicle.Dimensions
dbo.vehicle.Fact
dbo.vehicle.rating.Dimensions
Dimension table for single value questions
There is one dimension table in each level. The following table shows how the dimension table is constructed in different levels.
Level
Table name
Structure
Top
Dimensions
 
Region
Tenure
House Type
AgeOfBuilding
 
 
1
46
51
59
64
 
 
2
47
52
61
67
 
 
3
49
51
60
66
 
 
4
48
52
62
64
 
 
5
48
52
61
70
 
 
6
49
52
59
66
 
 
7
47
52
62
67
 
 
8
46
52
61
67
Person
Person. Dimensions
 
Serial
Person Serial
Level
ID
Gender
Occupation
 
 
1
1
1
1
33
71
 
 
2
2
2
1
34
72
 
 
3
2
3
2
33
72
 
 
4
2
4
3
34
77
 
 
5
2
5
4
33
77
 
 
6
3
6
1
34
74
 
 
7
3
7
2
33
74
 
 
8
4
8
1
34
71
Tvdays
Person. Tvdays Dimenions
 
Serial
Person Serial
Tvdays. Serial
PersonLevel ID
LevelID
Column
 
 
1
1
1
1
1
113
54
 
 
2
1
1
2
1
114
55
 
 
3
1
1
3
1
115
54
 
 
4
1
1
4
1
116
57
 
 
5
1
1
5
1
117
54
 
 
6
2
2
6
1
113
56
 
 
7
2
2
7
1
114
55
 
 
8
2
2
8
1
115
54
Dimension table for multiple response questions
The structure is similar to the Dimensions table, except there is only one Response column for the questions value. The labels related to the chosen LCL are also similarly exposed. The following example is for the Person.Languages question:
 
Serial
personSerial
Response
1
1
1
84
2
1
1
85
3
2
2
84
4
2
2
94
5
2
3
84
6
2
4
84
7
2
4
85
8
2
4
94
Fact table
There is one Fact table in each level. The following example shows how Fact tables are constructed for different levels:
Level
Table name
Structure
Top
Fact
 
Serial
Variable ID
VariableName
Response
TextVal
RealVal
 
 
1
1
30
address
NULL
158 Park Avenue, Harrogate, HG1 4TY
NULL
 
 
2
1
31
numpersons
1
NULL
NULL
 
 
3
1
32
numtrips_h
2
NULL
NULL
 
 
4
1
33
numvehicle
2
NULL
NULL
 
 
5
1
35
household
1
NULL
NULL
 
 
6
1
36
numrooms
4
NULL
NULL
 
 
7
1
37
pets
0
NULL
NULL
 
 
8
1
41
ageofbuilding
1
NULL
NULL
Person
Person. Fact
 
Serial
Person Serial
Variable ID
Variable Name
Response
TextVal
RealVal
 
 
1
1
1
44
age
25
NULL
NULL
 
 
2
1
1
46
numtrips
2
NULL
NULL
 
 
3
1
1
47
newspapers
10
NULL
NULL
 
 
4
1
1
48
name
NULL
Nigel Sorrell
NULL
 
 
5
1
1
49
person
1
NULL
NULL
 
 
6
1
1
50
weight
NULL
NULL
152.5
 
 
7
2
2
44
age
45
NULL
NULL
 
 
8
2
2
46
numtrips
0
NULL
NULL
 
 
9
2
2
47
neswpapers
3
NULL
NULL
Trips
Person. Trips.Fact
 
Serial
Person Serial
Trip Serial
VariableID
Variable Name
Response
TextVal
RealVal
 
 
1
1
1
1
56
daysaway
14
NULL
NULL
 
 
2
1
1
1
57
satisfaction
NULL
NULL
8.5
 
 
3
1
1
1
58
transportmodes
3
NULL
NULL
 
 
4
1
1
1
60
trip
1
NULL
NULL
 
 
5
1
1
2
56
daysaway
7
NULL
NULL
 
 
6
1
1
2
57
satisfaction
NULL
NULL
9
 
 
7
1
1
2
58
transportmodes
4
NULL
NULL
 
 
8
1
1
2
60
trip
2
NULL
NULL
 
 
9
4
8
3
56
daysaway
27
NULL
NULL
 
 
10
4
8
3
57
satisfaction
NULL
NULL
7.75
 
 
11
4
8
3
58
transportmodes
6
NULL
NULL
 
 
12
4
8
3
60
trip
3
NULL
NULL
See also
UNICOM Intelligence RDB DCS 2 schema update