-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathORACLE.MDX_ETL_DEMO.pck
186 lines (149 loc) · 12.2 KB
/
ORACLE.MDX_ETL_DEMO.pck
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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
CREATE OR REPLACE PACKAGE YOUR_SCHEMA.MDX_ETL_DEMO IS
-- This is a sample Oracle package for MDX ETL, please see https://github.com/DataBooster/PyWebApi#mdx-etl for details.
TYPE DATE_ARRAY IS TABLE OF DATE INDEX BY SIMPLE_INTEGER;
EMPTY_DATE_ARRAY DATE_ARRAY;
EMPTY_STRING_ARRAY DBMS_UTILITY.LNAME_ARRAY;
EMPTY_NUMBER_ARRAY DBMS_UTILITY.NUMBER_ARRAY;
----------------------------------------------------------------------------------------------------------------------------------------------------------------
-- This task_sp the entry procedure of a MDX ETL. The characteristic that distinguishes it from an ordinary procedure is that:
-- it has an MDX_QUERY column and a corresponding CALLBACK_SP column in at least one output result set (OUT SYS_REFCURSOR).
PROCEDURE GET_MDX_TASKS
(
-- Input Parameters are only used internally by the procedure. There are no regulations. For example,
inParam1 DATE,
inParam2 VARCHAR2,
inParam3 SIMPLE_INTEGER := 0,
-- The essential characteristic of MDX ETL is reflected in the output result set, all of other parameters are optional.
RC1 OUT SYS_REFCURSOR, -- The result set should contain the following 4 columns:
-- MDX_QUERY: (required) A string (VARCHAR2/NVARCHAR2 or CLOB/NCLOB) of valid MDX query;
-- COLUMN_MAPPING: (optional) This is a string of JSON dictionary for specifying the mapping from the column name of the MDX result to the input parameter name of the corresponding CALLBACK_SP, if they are different;
-- CALLBACK_SP: (required) The fully qualified name of the procedure for processing the MDX query results;
-- CALLBACK_ARGS: (required) A string of JSON dictionary that contains the input parameters to be passed to the CALLBACK_SP procedure.
-- All results of this MDX and the above pipeline parameters will be merged into the input parameters of this CALLBACK_SP.
-- Every MDX_QUERY->CALLBACK_SP task flows in the same result set will be executed in parallel.
-- The names of Above 4 colums are a convention with the end client, and can also be specified by options. Any extra columns other than these 4 columns will be ignored.
-- If multiple result sets satisfy the above characteristics, every task group of the results set will be launched in series.
-- If the procedure does not have any result set, or no result record satisfies the above characteristic,
-- the MDX ETL engine treats the procedure as an ordinary procedure and just returns the result of the procedure to the end client without launching any MDX task flow.
-- The following two output parameters indicate the post-processing procedure that will be called after all MDX subtasks are completed.
-- The parameter names are a convention with the end client, and can also be specified by options.
OUT_POST_SP OUT VARCHAR2, -- The output value should be the fully qualified name of the post-processing procedure, even if it is in the same package.
-- You can also append any URL query string supported by dbwebapi to it, such as '?NamingCase=None'
-- If the post-processing procedure is pointed to another task_sp (or even recursively pointed to itself),
-- that process will be chained to the current process, so on and so forth.
OUT_POST_SP_ARGS OUT VARCHAR2, -- The output value must be a string of JSON dictionary that contains the input parameters to be passed to the post-processing procedure.
-- All the remaining output parameters will be pipelined into the input parameters of every MDX task procedures and the post-processing procedure if their parameter names match.
BATCH_ID OUT PLS_INTEGER
);
----------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Please go to the PACKAGE BODY to facilitate the interpretation in the following parts.
----------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE PROCESS_MDX_RESULT_1 -- Referenced from above GET_MDX_TASKS's 1st CALLBACK_SP: 'YOUR_SCHEMA.MDX_ETL_DEMO.PROCESS_MDX_RESULT_1'
(
BATCH_ID SIMPLE_INTEGER, -- From the output parameter BATCH_ID of GET_MDX_TASKS above
inTerritory VARCHAR2, -- From above GET_MDX_TASKS's 1st CALLBACK_ARGS: '{"inTerritory": "Southwest"}'
-- All parameters received from the MDX result must be some kind of array (PL/SQL Associative Array Parameters)
inYears DBMS_UTILITY.NUMBER_ARRAY, -- From the "Fiscal Year" column of MDX result - COLUMN_MAPPING: "Fiscal Year"=>"inYears"
inSales DBMS_UTILITY.NUMBER_ARRAY, -- From the "Sales Amount" column of MDX result - COLUMN_MAPPING: "Sales Amount"=>"inSales"
inTaxes DBMS_UTILITY.NUMBER_ARRAY -- From the "Tax Amount" column of MDX result - COLUMN_MAPPING: "Tax Amount"=>"inTaxes"
);
PROCEDURE PROCESS_MDX_RESULT_2 -- Referenced from above GET_MDX_TASKS's 2nd CALLBACK_SP: 'YOUR_SCHEMA.MDX_ETL_DEMO.PROCESS_MDX_RESULT_2'
(
BATCH_ID SIMPLE_INTEGER, -- From the output parameter BATCH_ID of GET_MDX_TASKS above
-- All parameters received from the MDX result must be some kind of array (PL/SQL Associative Array Parameters)
CALENDAR DBMS_UTILITY.LNAME_ARRAY, -- From the "Calendar" column of MDX result
INTERNET_SALES DBMS_UTILITY.NUMBER_ARRAY -- From the "Internet Sales Amount" column of MDX result - COLUMN_MAPPING: "Internet Sales Amount"=>"INTERNET_SALES"
);
PROCEDURE FINAL_POST_PROCESSING -- Referenced from above GET_MDX_TASKS's OUT_POST_SP: 'YOUR_SCHEMA.MDX_ETL_DEMO.FINAL_POST_PROCESSING'
(
BATCH_ID SIMPLE_INTEGER, -- From the output parameter BATCH_ID of GET_MDX_TASKS above
inComment VARCHAR2 -- From GET_MDX_TASKS's output parameter OUT_POST_SP_ARGS: '{"inComment": "This is an example of argument passed from the bootloader"}'
);
END MDX_ETL_DEMO;
/
CREATE OR REPLACE PACKAGE BODY YOUR_SCHEMA.MDX_ETL_DEMO IS
----------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE GET_MDX_TASKS
(
inParam1 DATE,
inParam2 VARCHAR2,
inParam3 SIMPLE_INTEGER := 0,
RC1 OUT SYS_REFCURSOR,
OUT_POST_SP OUT VARCHAR2,
OUT_POST_SP_ARGS OUT VARCHAR2,
BATCH_ID OUT PLS_INTEGER
) IS
BEGIN
OUT_POST_SP := 'YOUR_SCHEMA.MDX_ETL_DEMO.FINAL_POST_PROCESSING'; -- Fully qualified name
OUT_POST_SP_ARGS := '{"inComment": "This is an example of argument passed from the bootloader"}'; -- JSON dictionary
IF inParam3 > 0 THEN
BATCH_ID := inParam3;
YOUR_SCHEMA.UTIL_LOG.PROGRESS_UPDATE(BATCH_ID, 'Enter GET_MDX_TASKS');
ELSE
BATCH_ID := YOUR_SCHEMA.UTIL_LOG.CREATE_BATCH('MDX_ETL_DEMO');
YOUR_SCHEMA.UTIL_LOG.PROGRESS_START(BATCH_ID, 100, 'Start GET_MDX_TASKS');
END IF;
OPEN RC1 FOR
SELECT 'SELECT
{ [Measures].[Sales Amount], [Measures].[Tax Amount] } ON 0,
{ [Date].[Fiscal].[Fiscal Year].&[2002], [Date].[Fiscal].[Fiscal Year].&[2003] } ON 1
FROM [Adventure Works]
WHERE ( [Sales Territory].[Southwest] )' AS MDX_QUERY,
'{"Sales Amount": "inSales", "Tax Amount": "inTaxes", "Fiscal Year": "inYears"}' AS COLUMN_MAPPING,
'YOUR_SCHEMA.MDX_ETL_DEMO.PROCESS_MDX_RESULT_1' AS CALLBACK_SP,
'{"inTerritory": "Southwest"}' AS CALLBACK_ARGS
FROM DUAL
UNION ALL
SELECT 'SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS,
NON EMPTY {[Date].[Calendar].MEMBERS} ON ROWS
FROM [Adventure Works]' AS MDX_QUERY,
'{"Internet Sales Amount": "INTERNET_SALES"}' AS COLUMN_MAPPING,
'YOUR_SCHEMA.MDX_ETL_DEMO.PROCESS_MDX_RESULT_2' AS CALLBACK_SP,
'{}' AS CALLBACK_ARGS
FROM DUAL;
YOUR_SCHEMA.UTIL_LOG.PROGRESS_UPDATE(BATCH_ID, 'Exit GET_MDX_TASKS');
END GET_MDX_TASKS;
----------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE PROCESS_MDX_RESULT_1 -- Referenced from above GET_MDX_TASKS's 1st CALLBACK_SP: 'YOUR_SCHEMA.MDX_ETL_DEMO.PROCESS_MDX_RESULT_1'
(
BATCH_ID SIMPLE_INTEGER, -- From the output parameter BATCH_ID of GET_MDX_TASKS above
inTerritory VARCHAR2, -- From above GET_MDX_TASKS's 1st CALLBACK_ARGS: '{"inTerritory": "Southwest"}'
-- All parameters received from the MDX result must be some kind of array (PL/SQL Associative Array Parameters)
inYears DBMS_UTILITY.NUMBER_ARRAY, -- From the "Fiscal Year" column of MDX result - COLUMN_MAPPING: "Fiscal Year"=>"inYears"
inSales DBMS_UTILITY.NUMBER_ARRAY, -- From the "Sales Amount" column of MDX result - COLUMN_MAPPING: "Sales Amount"=>"inSales"
inTaxes DBMS_UTILITY.NUMBER_ARRAY -- From the "Tax Amount" column of MDX result - COLUMN_MAPPING: "Tax Amount"=>"inTaxes"
) IS
BEGIN
YOUR_SCHEMA.UTIL_LOG.PROGRESS_UPDATE(BATCH_ID, 'Enter PROCESS_MDX_RESULT_1 - ' || inTerritory || ' : ' || TO_CHAR(inVegas.COUNT));
FORALL i IN inSales.FIRST .. inSales.LAST
INSERT INTO YOUR_SCHEMA.TEMP_TABLE_1
(BATCH_ID, TERRITORY, FISCAL_YEAR, SALES_AMOUNT, TAX_AMOUNT)
VALUES
(BATCH_ID, inTerritory, inYears(i), inSales(i), inTaxes(i));
YOUR_SCHEMA.UTIL_LOG.PROGRESS_UPDATE(BATCH_ID, 'Exit PROCESS_MDX_RESULT_1');
END PROCESS_MDX_RESULT_1;
PROCEDURE PROCESS_MDX_RESULT_2 -- Referenced from above GET_MDX_TASKS's 2nd CALLBACK_SP: 'YOUR_SCHEMA.MDX_ETL_DEMO.PROCESS_MDX_RESULT_2'
(
BATCH_ID SIMPLE_INTEGER, -- From the output parameter BATCH_ID of GET_MDX_TASKS above
-- All parameters received from the MDX result must be some kind of array (PL/SQL Associative Array Parameters)
CALENDAR DBMS_UTILITY.LNAME_ARRAY, -- From the "Calendar" column of MDX result
INTERNET_SALES DBMS_UTILITY.NUMBER_ARRAY -- From the "Internet Sales Amount" column of MDX result - COLUMN_MAPPING: "Internet Sales Amount"=>"INTERNET_SALES"
) IS
BEGIN
YOUR_SCHEMA.UTIL_LOG.PROGRESS_UPDATE(BATCH_ID, 'Enter PROCESS_MDX_RESULT_2 ' || TO_CHAR(INTERNET_SALES.COUNT));
FORALL i IN INTERNET_SALES.FIRST .. INTERNET_SALES.LAST
INSERT INTO YOUR_SCHEMA.TEMP_TABLE_2 (BATCH_ID, CALENDAR, INTERNET_SALES) VALUES (BATCH_ID, CALENDAR(i), INTERNET_SALES(i));
YOUR_SCHEMA.UTIL_LOG.PROGRESS_UPDATE(BATCH_ID, 'Exit PROCESS_MDX_RESULT_2');
END PROCESS_MDX_RESULT_2;
PROCEDURE FINAL_POST_PROCESSING -- Referenced from above GET_MDX_TASKS's OUT_POST_SP: 'YOUR_SCHEMA.MDX_ETL_DEMO.FINAL_POST_PROCESSING'
(
BATCH_ID SIMPLE_INTEGER, -- From the output parameter BATCH_ID of GET_MDX_TASKS above
inComment VARCHAR2 -- From GET_MDX_TASKS's output parameter OUT_POST_SP_ARGS: '{"inComment": "This is an example of argument passed from the bootloader"}'
) IS
BEGIN
YOUR_SCHEMA.UTIL_LOG.PROGRESS_UPDATE(BATCH_ID, 'Enter FINAL_POST_PROCESSING: ' || inComment);
YOUR_SCHEMA.UTIL_LOG.PROGRESS_UPDATE(BATCH_ID, 'Enter FINAL_POST_PROCESSING');
END FINAL_POST_PROCESSING;
END MDX_ETL_DEMO;
/