-
Notifications
You must be signed in to change notification settings - Fork 74
/
Bedrock.Cube.Data.Copy.InterCube.pro
524 lines (437 loc) · 16.2 KB
/
Bedrock.Cube.Data.Copy.InterCube.pro
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
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
601,100
602,"Bedrock.Cube.Data.Copy.InterCube"
562,"NULL"
586,"Bedrock Source Cube"
585,"Bedrock Source Cube"
564,
565,"taPOx^_RmwDU=@d^jXMjaJjG_tBGwGnB2@9Sc<=[6;WxUWWCXZeif^Ppaw9<lbjHfzy`BkWa?FbkVCe1C0P2;s>zAgPtxP@4@kzrf2j`raJ?KzUpI5OQP4hA0z=8[^hOu7kH5HL]H\6[>b;T_Cc5]lEYcn^?CsLKoBpLBKWuu5ouWyb9obA95l_oVzEoTYbAgbIl>0<`"
559,1
928,0
593,
594,
595,
597,
598,
596,
800,
801,
566,0
567,","
588,"."
589,"."
568,""""
570,Default
571,
569,0
592,0
599,1000
560,17
pSourceCube
pFilter
pTargetCube
pMappingToNewDims
pSuppressConsol
pSuppressRules
pZeroTarget
pZeroSource
pDimensionDelim
pElementStartDelim
pElementDelim
pDeleteViewsAndSubsets
pView
pDebug
pFactor
pDropAndReattachRules
pCopyDataViaClonedCube
561,17
2
2
2
2
1
1
1
1
2
2
2
1
2
1
2
1
1
590,17
pSourceCube,"Source"
pFilter,"State: Victoria + Queensland & Source Measure: Revenue"
pTargetCube,"Target"
pMappingToNewDims,""
pSuppressConsol,1
pSuppressRules,1
pZeroTarget,1
pZeroSource,0
pDimensionDelim,"&"
pElementStartDelim,":"
pElementDelim,"+"
pDeleteViewsAndSubsets,1
pView,""
pDebug,1
pFactor,"1"
pDropAndReattachRules,0
pCopyDataViaClonedCube,0
637,17
pSourceCube,"REQUIRED: Cube data is being copied from"
pFilter,"OPTIONAL: Filter on source cube in format Year: 2006 + 2007 & Scenario: Actual + Budget. Blank for whole cube"
pTargetCube,"REQUIRED: Name of cube to copy the values to"
pMappingToNewDims,"REQUIRED IF TARGET HAS DIMS NOT IN SOURCE: DimX:InputElementForDimX & DimY:InputElementForDimY (specify an N level element for each new dim)"
pSuppressConsol,"OPTIONAL: Suppress Consolidations (Skip = 1) Only use 0 for strings"
pSuppressRules,"OPTIONAL: Suppress Rules (Skip = 1)"
pZeroTarget,"OPTIONAL: Zero out Target Element PRIOR to Copy? (Boolean 1=True) Clears combination of pFilter and pMappingToNewDims"
pZeroSource,"OPTIONAL: Zero out Source Element AFTER Copy? (Boolean 1=True). If pFilter is blank the whole source cube is cleared!"
pDimensionDelim,"OPTIONAL. Delimiter for start of Dimension/Element set"
pElementStartDelim,"OPTIONAL: Delimiter for start of element list"
pElementDelim,"OPTIONAL: Delimiter between elements"
pDeleteViewsAndSubsets,"OPTIONAL: 0 = Keep view and subsets"
pView,"OPTIONAL: Name of the views and subsets to create (if blank a timestamp and random number will be used)"
pDebug,"OPTIONAL: Debug = 0"
pFactor,"OPTIONAL: Multiply source value by factor (1 keeps the value as is). To modify existing values make the target element the same as the source with pZeroTarget = 0"
pDropAndReattachRules,"OPTIONAL: Drop rules before copying then reattach afterwards (1 = Yes"
pCopyDataViaClonedCube,"OPTIONAL: Copy via cloned cube. Only use if pSuppressRules = 0 and pDropAndReattachRules = 1 (1 = Yes"
577,0
578,0
579,0
580,0
581,0
582,0
603,0
572,342
#****Begin: Generated Statements***
#****End: Generated Statements****
#################################################################################################
##~~Join the bedrock TM1 community on GitHub https://github.com/cubewise-code/bedrock Ver 4.0~~##
#################################################################################################
# This is the INTER process used to copy to a different cube
# Not to be confused with the INTRA process which is used within a cube
# This process is used to copy data from a source cube to a target cube
# The target cube may have more or less or the same number of dimensions as the source cube
# Where the target and source cubes share the same dimensions, the process will match the dimensions
# even if their position in the cube is different
# An input element must be specified for each dimension which is in the target but not in the source
# using the parameter pMappingToNewDims
# The format of parameter pMappingToNewDims using default delimiters & and : is
# DimInTargetButNotSource1:ElementOfDim & DimInTargetButNotSource2:ElementOfDim
# The input element must be an N level unless pSuppressConsol is set to 0.
# For dimensions in the source but not the target, the process will accumulate the values of all n level elements
# (or all n level elements specified by the pFilter parameter)
# The pFilter parameter contains the dimensons and elements to be used for filtering the source cube
# The format of the pFilter parameter is as follows, using default delimiters & + :
# Dim1: Elem1 + Elem2 & Dim2: Elem3 + Elem4
# The dimension parameters do not need to be given in the index order of dimensions in the cube.
# The dimension name is specified as the first member of the delimited string of elements.
# If consolidations are skipped the N level children of any consolidated filter elements will be used
# Spaces are ignored so use them to make your filter more readable
# An example
# To copy the 2011 Actual Sales data from the Sales cube to the General Ledger cube
# set pFilter to Year: 2011 & Version: Actual
# Say the General Ledger cube has an Account dimension but the Sales cube doesn't.
# The Account for sales is 9999 (an n level element)
# so set the pMappingToNewDims parameter to Account:9999
# This will copy all Actual 2011 Sales to Account 9999 in the General Ledger
# If only sales for Company X are to be copied, set pFilter to Year: 2011 & Version: Actual & Company:X
# If sales from other companies are already in the General Ledger,
# set pZeroTarget to 0 to add Company X's data to the existing data
# Setting pZeroTarget to 1 will clear our data in the target cube for the elements specified in the
# pMappingToNewDims parameter and the pFilter parameter for dimensions that are in the target.
##Global Variables
StringGlobalVariable('sProcessReturnCode');
NumericGlobalVariable('nProcessReturnCode');
nProcessReturnCode= 0;
### Constants ###
cThisProcName = GetProcessName();
cUserName = TM1User();
cMsgErrorContent = 'User:%cUserName% Process:%cThisProcName% ErrorMsg:%sMessage%';
cTimeStamp = TimSt( Now, '\Y\m\d\h\i\s' );
cRandomInt = NumberToString( INT( RAND( ) * 1000 ));
cDebugFile = GetProcessErrorFileDirectory | cThisProcName | '.' | cTimeStamp | '.' | cRandomInt ;
cMsgErrorLevel = 'ERROR';
cMsgErrorConten = '%cThisProcName% : %sMessage% : %cUserName%';
cMsgErrorShell = Expand('Executing process %cThisProcName% has failed.');
cMsgInfoLevel = 'INFO';
cMsgInfoContent = '%cThisProcName% : %sMessage% : %cUserName%';
nErrors = 0;
nRulesDropped = 0;
### Initialise Debug ###
If( pDebug >= 1 );
# Set debug file name
sDebugFile = cDebugFile | 'Prolog.debug';
# Log start time
AsciiOutput( sDebugFile, 'Process Started: ' | TimSt( Now, '\d-\m-\Y \h:\i:\s' ) );
# Log parameters
AsciiOutput( sDebugFile, 'Parameters: pSourceCube : ' | pSourceCube );
AsciiOutput( sDebugFile, ' pFilter : ' | pFilter );
AsciiOutput( sDebugFile, ' pTargetCube : ' | pTargetCube );
AsciiOutput( sDebugFile, ' pMappingToNewDims : ' | pMappingToNewDims );
AsciiOutput( sDebugFile, ' pFactor : ' | pFactor );
AsciiOutput( sDebugFile, ' pSuppressConsol : ' | NumberToString( pSuppressConsol) );
AsciiOutput( sDebugFile, ' pSuppressRules : ' | NumberToString( pSuppressRules) );
AsciiOutput( sDebugFile, ' pZeroTarget : ' | NumberToString( pZeroTarget ) );
AsciiOutput( sDebugFile, ' pZeroSource : ' | NumberToString( pZeroSource ) );
AsciiOutput( sDebugFile, ' pDimensionDelim : ' | pDimensionDelim );
AsciiOutput( sDebugFile, ' pElementStartDelim: ' | pElementStartDelim );
AsciiOutput( sDebugFile, ' pElementDelim : ' | pElementDelim );
AsciiOutput( sDebugFile, ' pDeleteViewsAndSubsets : ' | NumberToString( pDeleteViewsAndSubsets ) );
AsciiOutput( sDebugFile, ' pView : ' | pView );
AsciiOutput( sDebugFile, ' pDebug : ' | NumberToString( pDebug) );
EndIf;
#Convert pFactor to a number
nFactor = StringToNumber(pFactor);
# Check if there is a rules file to drop
# if not, ignore the pDropAndReattachRules parameter and proceed without dropping the rules
If(pDropAndReattachRules = 1);
sRuleFile = pTargetCube | '.rux';
If( FileExists( sRuleFile ) <> 1 );
sMessage = 'Rule file not found for cube: '|pTargetCube;
LogOutput( cMsgInfoLevel, Expand( cMsgInfoContent ) );
If( pDebug >= 1 );
AsciiOutput( sDebugFile, sMessage );
EndIf;
pDropAndReattachRules = 0;
EndIf;
EndIf;
### Getting cube logging parameter
sCubeLogging = CellGetS('}CubeProperties', pTargetCube, 'LOGGING' );
nCubeLogging = IF(sCubeLogging@='YES',1,0);
#################### Calling Bedrock v4 processes #######################
# Drop the rule
If(pDropAndReattachRules = 1);
sProc = '}bedrock.cube.rule.manage';
nRes = EXECUTEPROCESS( sProc,
'pLogOutput', pDebug,
'pCube', pTargetCube,
'pMode', 'UNLOAD'
);
IF ( nRes <> ProcessExitNormal() );
sMessage = cMsgErrorShell;
LogOutput( cMsgErrorLevel, Expand( cMsgErrorContent ) );
if ( pDebug >= 1 );
AsciiOutput( sDebugFile, Expand( cMsgErrorContent ) );
AsciiOutput( sDebugFile, Expand( 'Process %sProc% exit code: %nRes%' ) );
AsciiOutput( sDebugFile, Expand( 'Process %sProc% Finished: ') | TimSt( Now, '\d-\m-\Y \h:\i:\s' ) );
endif;
ProcessError();
ENDIF;
Endif;
# Copy
If(pCopyDataViaClonedCube = 0);
# copy back to source cube
sProc = '}bedrock.cube.data.copy.intercube';
nRet = ExecuteProcess( sProc,
'pLogOutput', pDebug,
'pStrictErrorHandling',1,
'pSrcCube', pSourceCube,
'pFilter', pFilter,
'pTgtCube', pTargetCube,
'pMappingToNewDims', pMappingToNewDims,
'pSuppressConsol', pSuppressConsol,
'pSuppressRules', pSuppressRules,
'pZeroSource', pZeroSource,
'pZeroTarget', pZeroTarget,
'pFactor', nFactor,
'pDimDelim', pDimensionDelim,
'pEleStartDelim', pElementStartDelim,
'pEleDelim', pElementDelim,
'pTemp', pDeleteViewsAndSubsets,
'pCubeLogging', nCubeLogging
);
IF ( nRes <> ProcessExitNormal() );
sMessage = cMsgErrorShell;
LogOutput( cMsgErrorLevel, Expand( cMsgErrorContent ) );
if ( pDebug >= 1 );
AsciiOutput( sDebugFile, Expand( cMsgErrorContent ) );
AsciiOutput( sDebugFile, Expand( 'Process %sProc% exit code: %nRes%' ) );
AsciiOutput( sDebugFile, Expand( 'Process %sProc% Finished: ') | TimSt( Now, '\d-\m-\Y \h:\i:\s' ) );
endif;
ProcessError();
ENDIF;
Endif;
# COPYING THROUGH THE CLONE CUBE
# Set the parameter pCopyDataViaClonedCube to 1
# to avoid recalculating the cache each time a cell is copied
# Calculated values can be copied to a tempororary cloned cube
# then the rules can be dropped, the data can be copied
# and then the rules can be reattached on the epilog
If(pCopyDataViaClonedCube = 1);
sClonedCube = pSourceCube | cUserName | cRandomInt;
# Note that pSuppressRules = 1 to skip rules
# The Bedrock Cube Clone process uses 1 for the pRuleValues parameter to skip rules
# So pSuppressRules can be passed to pRuleValues
# copy to clone
sProc = '}bedrock.cube.clone';
nRes = EXECUTEPROCESS( sProc,
'pLogOutput', pDebug,
'pStrictErrorHandling',1,
'pSrcCube', pSourceCube,
'pTgtCube', sClonedCube,
'pIncludeRules', 0,
'pIncludeData', 1,
'pFilter', pFilter,
'pSuppressRules', pSuppressRules,
'pCubeLogging', 0
);
IF ( nRes <> ProcessExitNormal() );
sMessage = cMsgErrorShell;
LogOutput( cMsgErrorLevel, Expand( cMsgErrorContent ) );
if ( pDebug >= 1 );
AsciiOutput( sDebugFile, Expand( cMsgErrorContent ) );
AsciiOutput( sDebugFile, Expand( 'Process %sProc% exit code: %nRes%' ) );
AsciiOutput( sDebugFile, Expand( 'Process %sProc% Finished: ') | TimSt( Now, '\d-\m-\Y \h:\i:\s' ) );
endif;
ProcessError();
ENDIF;
# copy back to source cube
sProc = '}bedrock.cube.data.copy.intercube';
nRet = ExecuteProcess( sProc,
'pLogOutput', pDebug,
'pStrictErrorHandling',1,
'pSrcCube', sClonedCube,
'pFilter', pFilter,
'pTgtCube', pTargetCube,
'pMappingToNewDims', pMappingToNewDims,
'pSuppressConsol', 1,
'pSuppressRules', pSuppressRules,
'pZeroSource', 0,
'pZeroTarget', pZeroTarget,
'pDimDelim', pDimensionDelim,
'pEleStartDelim', pElementStartDelim,
'pEleDelim', pElementDelim,
'pTemp', pDeleteViewsAndSubsets,
'pCubeLogging', nCubeLogging
);
IF ( nRes <> ProcessExitNormal() );
sMessage = cMsgErrorShell;
LogOutput( cMsgErrorLevel, Expand( cMsgErrorContent ) );
if ( pDebug >= 1 );
AsciiOutput( sDebugFile, Expand( cMsgErrorContent ) );
AsciiOutput( sDebugFile, Expand( 'Process %sProc% exit code: %nRes%' ) );
AsciiOutput( sDebugFile, Expand( 'Process %sProc% Finished: ') | TimSt( Now, '\d-\m-\Y \h:\i:\s' ) );
endif;
ProcessError();
ENDIF;
# cleaning the source view in source cube if required
IF( pZeroSource = 1);
sProc = '}bedrock.cube.data.clear';
nRet = ExecuteProcess( sProc,
'pLogOutput', pDebug,
'pStrictErrorHandling',1,
'pCube', pSourceCube,
'pView', pView,
'pFilter', pFilter,
'pDimDelim', pDimensionDelim,
'pEleStartDelim', pElementStartDelim,
'pEleDelim', pElementDelim,
'pTemp', pDeleteViewsAndSubsets,
'pCubeLogging', nCubeLogging
);
IF(nRet <> 0);
sMessage = 'Error clearing the source view.';
nErrors = nErrors + 1;
LogOutput( cMsgErrorLevel, Expand( cMsgErrorContent ) );
ProcessError();
ENDIF;
Endif;
# destroy clone cube
sProc = '}bedrock.cube.delete';
nRes = EXECUTEPROCESS( sProc,
'pCube', sClonedCube,
'pCtrlObj', 0
);
IF ( nRes <> ProcessExitNormal() );
sMessage = cMsgErrorShell;
LogOutput( cMsgErrorLevel, Expand( cMsgErrorContent ) );
if ( pDebug >= 1 );
AsciiOutput( sDebugFile, Expand( cMsgErrorContent ) );
AsciiOutput( sDebugFile, Expand( 'Process %sProc% exit code: %nRes%' ) );
AsciiOutput( sDebugFile, Expand( 'Process %sProc% Finished: ') | TimSt( Now, '\d-\m-\Y \h:\i:\s' ) );
endif;
ProcessError();
ENDIF;
EndIf;
# Return the rule
If(pDropAndReattachRules = 1);
sProc = '}bedrock.cube.rule.manage';
nRes = EXECUTEPROCESS( sProc,
'pLogOutput', pDebug,
'pStrictErrorHandling',1,
'pCube', pTargetCube,
'pMode', 'LOAD'
);
IF ( nRes <> ProcessExitNormal() );
sMessage = cMsgErrorShell;
LogOutput( cMsgErrorLevel, Expand( cMsgErrorContent ) );
if ( pDebug >= 1 );
AsciiOutput( sDebugFile, Expand( cMsgErrorContent ) );
AsciiOutput( sDebugFile, Expand( 'Process %sProc% exit code: %nRes%' ) );
AsciiOutput( sDebugFile, Expand( 'Process %sProc% Finished: ') | TimSt( Now, '\d-\m-\Y \h:\i:\s' ) );
endif;
ProcessError();
ENDIF;
EndIf;
573,3
#****Begin: Generated Statements***
#****End: Generated Statements****
574,8
#****Begin: Generated Statements***
#****End: Generated Statements****
575,21
#****Begin: Generated Statements***
#****End: Generated Statements****
#################################################################################################
##~~Join the bedrock TM1 community on GitHub https://github.com/cubewise-code/bedrock Ver 4.0~~##
#################################################################################################
If( pDebug >= 1 );
# Set debug file name
sDebugFile = cDebugFile | 'Epilog.debug';
# Log finish time
AsciiOutput( sDebugFile, 'Process Finished: ' | TimSt( Now, '\d-\m-\Y \h:\i:\s' ) );
EndIf;
### End Epilog ###
576,CubeAction=1511?DataAction=1503?CubeLogChanges=0?
930,0
638,1
804,0
1217,1
900,
901,
902,
938,0
937,
936,
935,
934,
932,0
933,0
903,
906,
929,
907,
908,
904,0
905,0
909,0
911,
912,
913,
914,
915,
916,
917,0
918,1
919,0
920,50000
921,""
922,""
923,0
924,""
925,""
926,""
927,""