-
Notifications
You must be signed in to change notification settings - Fork 3
Expand file tree
/
Copy pathcreate_attribute_dimension.sql
More file actions
152 lines (150 loc) · 4.28 KB
/
create_attribute_dimension.sql
File metadata and controls
152 lines (150 loc) · 4.28 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
DIMENSION TYPE TIME
USING time_dim
ATTRIBUTES
(year_id
CLASSIFICATION caption VALUE 'YEAR_ID'
CLASSIFICATION description VALUE 'YEAR ID',
year_name
CLASSIFICATION caption VALUE 'YEAR_NAME'
CLASSIFICATION description VALUE 'Year',
year_end_date
CLASSIFICATION caption VALUE 'YEAR_END_DATE'
CLASSIFICATION description VALUE 'Year End Date',
quarter_id
CLASSIFICATION caption VALUE 'QUARTER_ID'
CLASSIFICATION description VALUE 'QUARTER ID',
quarter_name
CLASSIFICATION caption VALUE 'QUARTER_NAME'
CLASSIFICATION description VALUE 'Quarter',
quarter_end_date
CLASSIFICATION caption VALUE 'QUARTER_END_DATE'
CLASSIFICATION description VALUE 'Quarter End Date',
quarter_of_year
CLASSIFICATION caption VALUE 'QUARTER_OF_YEAR'
CLASSIFICATION description VALUE 'Quarter of Year',
month_id
CLASSIFICATION caption VALUE 'MONTH_ID'
CLASSIFICATION description VALUE 'MONTH ID',
month_name
CLASSIFICATION caption VALUE 'MONTH_NAME'
CLASSIFICATION description VALUE 'Month',
month_long_name
CLASSIFICATION caption VALUE 'MONTH_LONG_NAME'
CLASSIFICATION description VALUE 'Month Long Name',
month_end_date
CLASSIFICATION caption VALUE 'MONTH_END_DATE'
CLASSIFICATION description VALUE 'Month End Date',
month_of_quarter
CLASSIFICATION caption VALUE 'MONTH_OF_QUARTER'
CLASSIFICATION description VALUE 'Month of Quarter',
month_of_year
CLASSIFICATION caption VALUE 'MONTH_OF_YEAR'
CLASSIFICATION description VALUE 'Month of Year',
season
CLASSIFICATION caption VALUE 'SEASON'
CLASSIFICATION description VALUE 'Season',
season_order
CLASSIFICATION caption VALUE 'SEASON_ORDER'
CLASSIFICATION description VALUE 'Season Order')
LEVEL month
LEVEL TYPE MONTHS
CLASSIFICATION caption VALUE 'MONTH'
CLASSIFICATION description VALUE 'Month'
KEY month_id
MEMBER NAME month_name
MEMBER CAPTION month_name
MEMBER DESCRIPTION month_long_name
ORDER BY month_end_date
DETERMINES (month_end_date,
quarter_id,
season,
season_order,
month_of_year,
month_of_quarter)
LEVEL quarter
LEVEL TYPE QUARTERS
CLASSIFICATION caption VALUE 'QUARTER'
CLASSIFICATION description VALUE 'Quarter'
KEY quarter_id
MEMBER NAME quarter_name
MEMBER CAPTION quarter_name
MEMBER DESCRIPTION quarter_name
ORDER BY quarter_end_date
DETERMINES (quarter_end_date,
quarter_of_year,
year_id)
LEVEL year
LEVEL TYPE YEARS
CLASSIFICATION caption VALUE 'YEAR'
CLASSIFICATION description VALUE 'Year'
KEY year_id
MEMBER NAME year_name
MEMBER CAPTION year_name
MEMBER DESCRIPTION year_name
ORDER BY year_end_date
DETERMINES (year_end_date)
LEVEL season
LEVEL TYPE QUARTERS
CLASSIFICATION caption VALUE 'SEASON'
CLASSIFICATION description VALUE 'Season'
KEY season
MEMBER NAME season
MEMBER CAPTION season
MEMBER DESCRIPTION season
LEVEL month_of_quarter
LEVEL TYPE MONTHS
CLASSIFICATION caption VALUE 'MONTH_OF_QUARTER'
CLASSIFICATION description VALUE 'Month of Quarter'
KEY month_of_quarter;
CREATE OR REPLACE ATTRIBUTE DIMENSION product_attr_dim
USING product_dim
ATTRIBUTES
(department_id,
department_name,
category_id,
category_name)
LEVEL DEPARTMENT
KEY department_id
ALTERNATE KEY department_name
MEMBER NAME department_name
MEMBER CAPTION department_name
ORDER BY department_name
LEVEL CATEGORY
KEY category_id
ALTERNATE KEY category_name
MEMBER NAME category_name
MEMBER CAPTION category_name
ORDER BY category_name
DETERMINES(department_id)
ALL MEMBER NAME 'ALL PRODUCTS';
CREATE OR REPLACE ATTRIBUTE DIMENSION geography_attr_dim
USING geography_dim
ATTRIBUTES
(region_id,
region_name,
country_id,
country_name,
state_province_id,
state_province_name)
LEVEL REGION
KEY region_id
ALTERNATE KEY region_name
MEMBER NAME region_name
MEMBER CAPTION region_name
ORDER BY region_name
LEVEL COUNTRY
KEY country_id
ALTERNATE KEY country_name
MEMBER NAME country_name
MEMBER CAPTION country_name
ORDER BY country_name
DETERMINES(region_id)
LEVEL STATE_PROVINCE
KEY state_province_id
ALTERNATE KEY state_province_name
MEMBER NAME state_province_name
MEMBER CAPTION state_province_name
ORDER BY state_province_name
DETERMINES(country_id)
ALL MEMBER NAME 'ALL CUSTOMERS';