mpe_makedatamodel.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Creates the empty Data Controller tables
4  @details Creates the empty DC tables during the initial deployment.
5 
6  Usage:
7 
8  %mpe_makedatamodel(lib=DCxxxx)
9 
10  <h4> SAS Macros </h4>
11  @li mf_existfeature.sas
12 
13  @version 9.2
14  @author 4GL Apps Ltd
15  @copyright 4GL Apps Ltd. This code may only be used within Data Controller
16  and may not be re-distributed or re-sold without the express permission of
17  4GL Apps Ltd.
18 **/
19 
20 %macro mpe_makedatamodel(lib=);
21 %if &syscc ne 0 %then %do;
22  %put syscc=&syscc exiting &sysmacroname;
23  %return;
24 %end;
25 
26 %local notnull;
27 %if %mf_existfeature(COLCONSTRAINTS)=1 %then %let notnull=not null;
28 %put &=notnull;
29 
30 proc sql;
31 create table &lib..mpe_alerts(
32  tx_from num format=datetime19.3,
33  alert_event char(20),
34  alert_lib char(8),
35  alert_ds char(32),
36  alert_user char(100) ,
37  tx_to num &notnull format=datetime19.3
38 );quit;
39 proc datasets lib=&lib noprint;
40  modify mpe_alerts;
41  index create
42  pk_mpealerts=(tx_from alert_event alert_lib alert_ds alert_user)
43  /nomiss unique;
44 quit;
45 
46 proc sql;
47 create table &lib..mpe_audit(
48  load_ref char(36) label='unique load reference',
49  libref char(8) label='Library Reference (8 chars)',
50  dsn char(32) label='Dataset Name (32 chars)',
51  key_hash char(32) label=
52  'MD5 Hash of primary key values (pipe seperated)',
53  tgtvar_nm char(32) label='Target variable name (32 chars)',
54  move_type char(1) label='Either (A)ppended, (D)eleted or (M)odified',
55  processed_dttm num format=E8601DT26.6 label='Processed at timestamp',
56  is_pk num label='Is Primary Key Field? (1/0)',
57  is_diff num label=
58  'Did value change? (1/0/-1). Always -1 for appends and deletes.',
59  tgtvar_type char(1) label='Either (C)haracter or (N)umeric',
60  oldval_num num format=best32. label='Old (numeric) value',
61  newval_num num format=best32. label='New (numeric) value',
62  oldval_char char(32765) label='Old (character) value',
63  newval_char char(32765) label='New (character) value'
64 );quit;
65 proc datasets lib=&lib noprint;
66  modify mpe_audit;
67  index create
68  pk_mpe_audit=(load_ref libref dsn key_hash tgtvar_nm)
69  /nomiss unique;
70 quit;
71 
72 proc sql;
73 create table &lib..mpe_column_level_security(
74  tx_from num &notnull format=datetime19.3,
75  tx_to num &notnull format=datetime19.3,
76  CLS_SCOPE char(4) &notnull,
77  CLS_GROUP char(64) &notnull,
78  CLS_LIBREF char(8) &notnull,
79  CLS_TABLE char(32) &notnull,
80  CLS_VARIABLE_NM char(32) &notnull,
81  CLS_ACTIVE num &notnull,
82  CLS_HIDE num
83 );quit;
84 proc datasets lib=&lib noprint;
85  modify mpe_column_level_security;
86  index create
87  pk_mpe_column_level_security=
88  (tx_to CLS_SCOPE CLS_GROUP CLS_LIBREF CLS_TABLE CLS_VARIABLE_NM)
89  /nomiss unique;
90 quit;
91 
92 proc sql;
93 create table &lib..mpe_config(
94  tx_from num &notnull format=datetime19.3
95  ,tx_to num &notnull format=datetime19.3
96  ,var_scope varchar(10) &notnull
97  ,var_name varchar(32) &notnull
98  ,var_value varchar(5000)
99  ,var_active num
100  ,var_desc varchar(300)
101 );quit;
102 proc datasets lib=&lib noprint;
103  modify mpe_config;
104  index create
105  pk_mpe_config=(tx_to var_scope var_name)
106  /nomiss unique;
107 quit;
108 proc sql;
109 create table &lib..mpe_datacatalog_libs(
110  TX_FROM num &notnull format=datetime19.3,
111  TX_TO num &notnull format=datetime19.3,
112  libref char(8) label='Library Ref',
113  engine char(32) label='Library Engine',
114  libname char(256) format=$256. label='Library Name',
115  paths char(8192) label='Library Paths',
116  perms char(500) label='Library Permissions (if BASE)',
117  owners char(500) label='Library Owners (if BASE)',
118  schemas char(500) label='Library Schemas (if DB)',
119  libid char(17) label='LibraryId'
120 );quit;
121 proc datasets lib=&lib noprint;
122  modify mpe_datacatalog_libs;
123  index create
124  pk_mpe_datacatalog_libs=(libref tx_to)
125  /nomiss unique;
126 quit;
127 proc sql;
128 create table &lib..mpe_datacatalog_TABS(
129  TX_FROM num &notnull format=datetime19.3,
130  TX_TO num &notnull format=datetime19.3,
131  libref char(8) label='Library Name',
132  dsn char(64) label='Member Name',
133  memtype char(8) label='Member Type',
134  dbms_memtype char(32) label='DBMS Member Type',
135  memlabel char(512) label='Data Set Label',
136  typemem char(8) label='Data Set Type',
137  nvar num label='Number of Variables',
138  compress char(8) label='Compression Routine',
139  pk_fields char(512)
140  label='Primary Key Fields (identified by being in a constraint that is both Unique and Not Null)'
141 );quit;
142 proc datasets lib=&lib noprint;
143  modify mpe_datacatalog_TABS;
144  index create
145  pk_mpe_datacatalog_TABS=(libref dsn tx_to)
146  /nomiss unique;
147 quit;
148 proc sql;
149 create table &lib..mpe_datacatalog_vars(
150  TX_FROM num &notnull format=datetime19.3,
151  TX_TO num &notnull format=datetime19.3,
152  libref char(8) label='Library Name',
153  dsn char(64) label='Table Name',
154  name char(64) label='Column Name',
155  memtype char(8) label='Member Type',
156  type char(16) label='Column Type',
157  length num label='Column Length',
158  varnum num label='Column Number in Table',
159  label char(512) label='Column Label',
160  format char(49) label='Column Format',
161  idxusage char(9) label='Column Index Type',
162  notnull char(3) label='Not NULL?',
163  pk_ind num label='Primary Key Indicator (1=Primary Key field)'
164 );quit;
165 proc datasets lib=&lib noprint;
166  modify mpe_datacatalog_vars;
167  index create
168  pk_mpe_datacatalog_vars=(libref dsn name tx_to)
169  /nomiss unique;
170 quit;
171 proc sql;
172 create table &lib..mpe_datastatus_libs(
173  TX_FROM num &notnull format=datetime19.3,
174  TX_TO num &notnull format=datetime19.3,
175  libref char(8) label='Library Name',
176  libsize num format=SIZEKMG. label='Size of library',
177  table_cnt num label='Number of Tables'
178 );quit;
179 proc datasets lib=&lib noprint;
180  modify mpe_datastatus_libs;
181  index create
182  pk_mpe_datastatus_libs=(libref tx_to)
183  /nomiss unique;
184 quit;
185 proc sql;
186 create table &lib..mpe_datastatus_tabs(
187  TX_FROM num &notnull format=datetime19.3,
188  TX_TO num &notnull format=datetime19.3,
189  libref char(8) label='Library Name',
190  dsn char(64) label='Member Name',
191  filesize num format=SIZEKMG. label='Size of file',
192  crdate num format=DATETIME. informat=DATETIME. label='Date Created',
193  modate num format=DATETIME. informat=DATETIME. label='Date Modified',
194  nobs num label='Number of Physical (Actual, inc. deleted) Observations'
195 );quit;
196 proc datasets lib=&lib noprint;
197  modify mpe_datastatus_tabs;
198  index create
199  pk_mpe_datastatus_tabs=(libref dsn tx_to)
200  /nomiss unique;
201 quit;
202 proc sql;
203 create table &lib..mpe_datadictionary
204  (
205  TX_FROM num &notnull format=datetime19.3,
206  TX_TO num &notnull format=datetime19.3,
207  DD_TYPE char(16),
208  DD_SOURCE char(1024),
209  DD_SHORTDESC char(256),
210  DD_LONGDESC char(32767),
211  DD_OWNER char(128),
212  DD_RESPONSIBLE char(128),
213  DD_SENSITIVITY char(64)
214 );quit;
215 proc datasets lib=&lib noprint;
216  modify mpe_datadictionary;
217  index create
218  pk_mpe_datadictionary=(tx_to dd_type dd_source)
219  /nomiss unique;
220 quit;
221 proc sql;
222 create table &lib..mpe_dataloads(
223  libref varchar(8) &notnull,
224  dsn varchar(32) &notnull,
225  etlsource varchar(100) &notnull,
226  loadtype varchar(20) &notnull,
227  changed_records int,
228  new_records int,
229  deleted_records int,
230  duration num,
231  user_nm varchar(50) &notnull,
232  processed_dttm num format=datetime19.3,
233  mac_ver varchar(5)
234 );quit;
235 proc datasets lib=&lib noprint;
236  modify mpe_dataloads;
237  index create
238  pk_mpe_dataloads=(processed_dttm libref dsn etlsource)
239  /nomiss unique;
240 quit;
241 proc sql;
242 create table &lib..mpe_emails(
243  tx_from num &notnull format=datetime19.3,
244  tx_to num &notnull format=datetime19.3,
245  user_name char(50) &notnull,
246  user_displayname char(100),
247  user_email char(100) &notnull
248 );quit;
249 proc datasets lib=&lib noprint;
250  modify mpe_emails;
251  index create
252  pk_mpe_emails=(tx_to user_name)
253  /nomiss unique;
254 quit;
255 proc sql;
256 create table &lib..mpe_excel_config(
257  tx_from num &notnull format=datetime19.3,
258  tx_to num &notnull format=datetime19.3,
259  xl_libref char(8),
260  xl_table char(32),
261  xl_column char(32),
262  xl_rule char(32),
263  xl_active num
264 );quit;
265 proc datasets lib=&lib noprint;
266  modify mpe_excel_config;
267  index create
268  pk_mpe_excel_config=(tx_to xl_libref xl_table xl_column)
269  /nomiss unique;
270 quit;
271 
272 proc sql;
273 create table &lib..MPE_XLMAP_DATA(
274  LOAD_REF char(32) &notnull,
275  XLMAP_ID char(32) &notnull,
276  XLMAP_RANGE_ID char(32) &notnull,
277  ROW_NO num &notnull,
278  COL_NO num &notnull,
279  VALUE_TXT char(4000)
280 );quit;
281 proc datasets lib=&lib noprint;
282  modify MPE_XLMAP_DATA;
283  index create
284  pk_MPE_XLMAP_DATA=(load_ref xlmap_id xlmap_range_id row_no col_no)
285  /nomiss unique;
286 quit;
287 
288 proc sql;
289 create table &lib..mpe_xlmap_info(
290  tx_from num &notnull,
291  tx_to num &notnull,
292  XLMAP_ID char(32) &notnull,
293  XLMAP_DESCRIPTION char(1000) &notnull,
294  XLMAP_TARGETLIBDS char(41) &notnull
295 );quit;
296 proc datasets lib=&lib noprint;
297  modify mpe_xlmap_info;
298  index create
299  pk_mpe_xlmap_info=(tx_to xlmap_id)
300  /nomiss unique;
301 quit;
302 
303 proc sql;
304 create table &lib..mpe_xlmap_rules(
305  tx_from num &notnull,
306  tx_to num &notnull,
307  XLMAP_ID char(32) &notnull,
308  XLMAP_RANGE_ID char(32) &notnull,
309  XLMAP_SHEET char(32) &notnull,
310  XLMAP_START char(1000) &notnull,
311  XLMAP_FINISH char(1000)
312 );quit;
313 proc datasets lib=&lib noprint;
314  modify mpe_xlmap_rules;
315  index create
316  pk_mpe_xlmap_rules=(tx_to xlmap_id xlmap_range_id)
317  /nomiss unique;
318 quit;
319 
320 proc sql;
321 create table &lib..mpe_filteranytable(
322  filter_rk num &notnull,
323  filter_hash char(32) &notnull,
324  filter_table char(41) &notnull,
325  processed_dttm num &notnull format=datetime19.
326 );quit;
327 proc datasets lib=&lib noprint;
328  modify mpe_filteranytable;
329  index create filter_rk /nomiss unique;
330 quit;
331 proc sql;
332 create table &lib..mpe_filtersource(
333  filter_hash char(32) &notnull,
334  filter_line num &notnull,
335  group_logic char(3) &notnull,
336  subgroup_logic char(3) &notnull,
337  subgroup_id num &notnull,
338  variable_nm varchar(32) &notnull,
339  operator_nm varchar(12) &notnull,
340  raw_value varchar(4000) &notnull,
341  processed_dttm num &notnull format=datetime19.
342 );quit;
343 proc datasets lib=&lib noprint;
344  modify mpe_filtersource;
345  index create
346  pk_mpe_filtersource=(filter_hash filter_line)
347  /nomiss unique;
348 quit;
349 proc sql;
350 create table &lib..mpe_groups(
351  tx_from num &notnull format=datetime19.3,
352  tx_to num &notnull format=datetime19.3,
353  group_name char(100) &notnull,
354  user_name char(50) &notnull,
355  group_desc char(256)
356 );quit;
357 proc datasets lib=&lib noprint;
358  modify mpe_groups;
359  index create
360  pk_mpe_groups=(tx_to group_name user_name)
361  /nomiss unique;
362 quit;
363 proc sql;
364 create table &lib..mpe_lineage_cols
365  (
366  col_id char(32),
367  direction char(1),
368  sourcecoluri char(256),
369  map_type char(256),
370  map_transform char(256),
371  jobname char(256),
372  sourcetablename char(256),
373  sourcecolname char(256),
374  targettablename char(256),
375  targetcolname char(256),
376  targetcoluri char(256),
377  Derived_Rule char(500),
378  level int,
379  modified_dttm num format=datetime19.3,
380  modified_by char(64)
381 );quit;
382 proc datasets lib=&lib noprint;
383  modify mpe_lineage_cols;
384  index create
385  pk_mpe_lineage_cols=(col_id direction sourcecoluri targetcoluri map_type map_transform)
386  /nomiss unique;
387 quit;
388 proc sql;
389 create table &lib..MPE_LINEAGE_TABS
390  (
391  tx_from num &notnull format=datetime19.3,
392  jobid char(17),
393  srctableid char(17),
394  tgttableid char(17),
395  jobname char(128),
396  srctabletype char(16),
397  srctablename char(64),
398  srclibref char(8),
399  tgttabletype char(16),
400  tgttablename char(64),
401  tgtlibref char(8),
402  tx_to num &notnull format=datetime19.3
403 );quit;
404 proc datasets lib=&lib noprint;
405  modify mpe_lineage_tabs;
406  index create
407  pk_mpe_lineage_tabs=(tx_to jobid srctableid tgttableid)
408  /nomiss unique;
409 quit;
410 proc sql;
411 create table &lib..mpe_loads(
412  csv_dir char(255),
413  user_nm char(50) ,
414  status char(15) ,
415  duration num ,
416  processed_dttm num format=datetime19.3,
417  reason_txt char(2048) ,
418  approvals char(64)
419 );quit;
420 proc datasets lib=&lib noprint;
421  modify mpe_loads;
422  index create csv_dir /nomiss unique;
423 quit;
424 proc sql;
425 create table &lib..mpe_lockanytable(
426  lock_lib varchar(8) &notnull ,
427  lock_ds varchar(32) &notnull,
428  lock_status_cd varchar(10) &notnull,
429  lock_user_nm varchar(100) &notnull ,
430  lock_ref varchar(200),
431  lock_pid varchar(10),
432  lock_start_dttm num format=E8601DT26.6,
433  lock_end_dttm num format=E8601DT26.6
434 );quit;
435 proc datasets lib=&lib noprint;
436  modify mpe_lockanytable;
437  index create
438  pk_mpe_lockanytable=(lock_lib lock_ds)
439  /nomiss unique;
440 quit;
441 proc sql;
442 create table &lib..mpe_maxkeyvalues(
443  keytable varchar(41) label='Base table in libref.dataset format',
444  keycolumn char(32) format=$32.
445  label='The Surrogate / Retained key field containing the key values.',
446  max_key num label=
447  'Integer value representing current max RK or SK value in the KEYTABLE',
448  processed_dttm num format=E8601DT26.6
449  label='Datetime this value was last updated'
450 );quit;
451 proc datasets lib=&lib noprint;
452  modify mpe_maxkeyvalues;
453  index create keytable /nomiss unique;
454 quit;
455 /* no PK defined as it is a transaction table */
456 proc sql;
457 create table &lib..mpe_requests(
458  request_dttm num &notnull format=datetime19.,
459  request_user char(64) &notnull,
460  request_service char(64) &notnull,
461  request_params char(128)
462  );
463 proc sql;
464 create table &lib..mpe_review(
465  table_id varchar(32) &notnull,
466  reviewed_by_nm varchar(100) &notnull,
467  base_table varchar(41) &notnull,
468  review_status_id varchar(10) &notnull,
469  reviewed_on_dttm num &notnull format=datetime19.3,
470  review_reason_txt varchar(400)
471 );quit;
472 proc datasets lib=&lib noprint;
473  modify mpe_review;
474  index create
475  pk_mpe_review=(table_id reviewed_by_nm)
476  /nomiss unique;
477 quit;
478 proc sql;
479 create table &lib..mpe_row_level_security(
480  tx_from num &notnull format=datetime19.3,
481  tx_to num &notnull format=datetime19.3,
482  RLS_RK num &notnull,
483  RLS_SCOPE char(8) &notnull,
484  RLS_GROUP char(128) &notnull,
485  RLS_LIBREF char(8) &notnull,
486  RLS_TABLE char(32) &notnull,
487  RLS_GROUP_LOGIC char(3) &notnull,
488  RLS_SUBGROUP_LOGIC char(3) &notnull,
489  RLS_SUBGROUP_ID num &notnull,
490  RLS_VARIABLE_NM varchar(32) &notnull,
491  RLS_OPERATOR_NM varchar(12) &notnull,
492  RLS_RAW_VALUE varchar(4000) &notnull,
493  RLS_ACTIVE num &notnull
494 );quit;
495 proc datasets lib=&lib noprint;
496  modify mpe_row_level_security;
497  index create
498  pk_mpe_row_level_security=(tx_to RLS_RK)
499  /nomiss unique;
500 quit;
501 proc sql;
502 create table &lib..mpe_security(
503  tx_from num &notnull format=datetime19.3,
504  tx_to num &notnull format=datetime19.3,
505  libref char(8) &notnull,
506  dsn char(32) &notnull,
507  access_level char(10) &notnull,
508  sas_group char(100) &notnull
509 );quit;
510 proc datasets lib=&lib noprint;
511  modify mpe_security;
512  index create
513  pk_mpe_security=(tx_to libref dsn access_level sas_group)
514  /nomiss unique;
515 quit;
516 proc sql;
517 create table &lib..mpe_selectbox(
518  ver_from_dttm num &notnull format=datetime19.3,/* timestamp for versioning*/
519  ver_to_dttm num &notnull format=datetime19.3, /* timestamp for versioning */
520  selectbox_rk num &notnull, /* surrogate key */
521  select_lib varchar(17) &notnull, /* libref (big enough for uri)*/
522  select_ds varchar(32) &notnull,
523  base_column varchar(36) &notnull, /* variable name against which to apply selectbox */
524  selectbox_value varchar(500) &notnull, /* selectbox value */
525  selectbox_order num , /* optional ordering (1 comes before 2) */
526  selectbox_type varchar(32) /* column type (blank for default, else
527  sas or js to indicate relevant system functions)*/
528 );quit;
529 proc datasets lib=&lib noprint;
530  modify mpe_selectbox;
531  index create
532  pk_mpe_selectbox=(ver_to_dttm selectbox_rk)
533  /nomiss unique;
534 quit;
535 proc sql;
536 create table &lib..mpe_signoffs(
537  tech_from_dttm num &notnull format=datetime19.3,
538  tech_to_dttm num &notnull format=datetime19.3,
539  signoff_table varchar(50) &notnull,
540  signoff_section_rk num &notnull,
541  signoff_version_rk num &notnull,
542  signoff_name varchar(100) &notnull
543 );quit;
544 proc datasets lib=&lib noprint;
545  modify mpe_signoffs;
546  index create
547  pk_mpe_signoffs=(tech_to_dttm signoff_table signoff_section_rk)
548  /nomiss unique;
549 quit;
550 /* mpe_submit */
551 proc sql;
552 create table &lib..mpe_submit(
553  table_id varchar(32) &notnull,
554  submit_status_cd varchar(10) &notnull,
555  base_lib char(8) &notnull,
556  base_ds char(32) &notnull,
557  submitted_by_nm varchar(100) &notnull,
558  submitted_on_dttm num &notnull format=datetime19.3,
559  submitted_reason_txt varchar(400),
560  input_obs num,
561  input_vars num,
562  num_of_approvals_required num &notnull ,
563  num_of_approvals_remaining num &notnull ,
564  reviewed_by_nm char(100),
565  reviewed_on_dttm num
566 );quit;
567 proc datasets lib=&lib noprint;
568  modify mpe_submit;
569  index create table_id /nomiss unique;
570 quit;
571 proc sql;
572 create table &lib..mpe_tables(
573  tx_from num &notnull format=datetime19.3,
574  tx_to num &notnull format=datetime19.3,
575  libref char(8) &notnull,
576  dsn char(32) &notnull,
577  num_of_approvals_required int,
578  loadtype char(12) ,
579  buskey char(1000) ,
580  var_txfrom char(32) ,
581  var_txto char(32) ,
582  var_busfrom char(32) ,
583  var_busto char(32) ,
584  var_processed char(32) ,
585  close_vars varchar(500),
586  pre_edit_hook char(200),
587  post_edit_hook char(200),
588  pre_approve_hook char(200) ,
589  post_approve_hook char(200) ,
590  signoff_cols varchar(500),
591  signoff_hook varchar(200),
592  notes char(1000) ,
593  rk_underlying char(1000) ,
594  audit_libds char(41)
595 );quit;
596 proc datasets lib=&lib noprint;
597  modify mpe_tables;
598  index create
599  pk_mpe_tables=(tx_to libref dsn)
600  /nomiss unique;
601 quit;
602 proc sql;
603 create table &lib..mpe_users(
604  user_id char(50) &notnull,
605  last_seen_dt num &notnull format=date9.,
606  registered_dt num &notnull format=date9.
607 );quit;
608 proc datasets lib=&lib noprint;
609  modify mpe_users;
610  index create user_id /nomiss unique;
611 quit;
612 proc sql;
613 create table &lib..MPE_VALIDATIONS
614  (
615  TX_FROM num &notnull format=datetime19.3,
616  BASE_LIB varchar(8),
617  BASE_DS varchar(32),
618  BASE_COL varchar(32),
619  RULE_TYPE varchar(32),
620  RULE_VALUE varchar(128),
621  RULE_ACTIVE num ,
622  TX_TO num &notnull format=datetime19.3
623 );quit;
624 proc datasets lib=&lib noprint;
625  modify mpe_validations;
626  index create
627  pk_mpe_validations=(tx_from base_lib base_ds base_col rule_type)
628  /nomiss unique;
629 quit;
630 proc sql;
631 create table &lib..mpe_x_test(
632  primary_key_field num &notnull,
633  some_char char(32767) ,
634  some_dropdown char(128),
635  some_num num ,
636  some_date num format=date9.,
637  some_datetime num format=datetime19. informat=ANYDTDTM19.,
638  some_time num format=time8.,
639  some_shortnum num length=4,
640  some_bestnum num format=best.
641 );quit;
642 proc datasets lib=&lib noprint;
643  modify mpe_x_test;
644  index create primary_key_field /nomiss unique;
645 quit;
646 
647 %mend mpe_makedatamodel;