Page 1 of 1

Loading MTD data as YTD data

Posted: Wed Apr 17, 2019 1:52 pm
by djarko
Hi,

I'm newbie in developing TM1. I have such issue - in ODBC Source (SAP) I have transactional data from General Ledger with some dimensions (Line of Business, Distribution Channel...).
I would like to load aggregated data (not transactional) into TM1 cube but into dimension 'Interval' = YTD



I wrote sql in query field to gather only data which I wanted:

SELECT
[GJAHR] as Rok
,[MONAT] as Miesiac
,[LDGRP] as Ledger
,[HKONT] as Account
,[ZZPRODUCT] as Product
,[ZZDISTCHAN] as Distchan
,sum([DMBTR]) as Amount
FROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS]
where [LDGRP] not in ('S2')
group by
[GJAHR]
,[MONAT]
,[LDGRP]
,[HKONT]
,[ZZPRODUCT]
,[ZZDISTCHAN];

Then I specified:
Variables on Variables tab
CubeName, ZeroOutPortion, Dimensions on Maps tab

In Advanced tab I specified:
Parameters for
Year - P_Year
Month- P_Month

In Prolog I prepared views:
P_Miesiac = DimensionElementPrincipalName( 'B.MIESIAC', P_Month );

SubsetDeleteAllElements('B.SCENARIUSZ', 'TechParametr');
SubsetDeleteAllElements('B.MIESIAC', 'TechParametr');
SubsetElementInsert('B.SCENARIUSZ', 'TechParametr', P_Year | ' Actual', 1);
SubsetElementInsert('B.MIESIAC', 'TechParametr', P_Month, 1);


#****Begin: Generated Statements***
OldCubeLogChanges = CUBEGETLOGCHANGES('A.SAP');
CUBESETLOGCHANGES('A.SAP', 0);
VIEWZEROOUT('A.SAP','ZERO_Dane_SAP');
#****End: Generated Statements****




DatasourceQuery= 'SELECT
[GJAHR] as Rok
,[MONAT] as Miesiac
,[LDGRP] as Ledger
,[HKONT] as Account
,[ZZPRODUCT] as Product
,[ZZDISTCHAN] as Distchan
,[ZZSOURCE] as Source
,[ZZACTIVITY] as Activity
,[ZZREINSCON] as REINSCON
,[ZZIP] as IP
,sum([DMBTR]) as Amount
FROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS]
where [LDGRP] not in (S2)
and Rok = ' | P_Year | '
and Miesiac <= ' | P_Miesiac | '
group by
[GJAHR]
,[MONAT]
,[LDGRP]
,[HKONT]
,[ZZPRODUCT]
,[ZZDISTCHAN]';

And the problem is that all data are in every Month <= P_Miesiac.
My intension was to store aggregated YTD data in Month=P_Miesiac and then to create rules calculating MTD data.

Where is problem? Pls help.
If you want more details/prt screens, pls tell me.

Re: Loading MTD data as YTD data

Posted: Wed Apr 17, 2019 2:24 pm
by Wim Gielis
Not entirely sure I follow, but you would have to do a CellIncrementN into the P_Month month to add up values.
However, to avoid rules, I would advise to load data month by month, and use consolidations / rollups to calculate the YTD values.

Re: Loading MTD data as YTD data

Posted: Wed Apr 17, 2019 2:29 pm
by djarko
But my YTD loading is intentional. I want to avoid situation, when I closed month in TM1, but in SAP where booked some i.e. invoices.
Those invoices I want to include in managerial reporting in next Period.

Re: Loading MTD data as YTD data

Posted: Wed Apr 17, 2019 2:41 pm
by Wim Gielis
Show us your month dimension please.

Re: Loading MTD data as YTD data

Posted: Wed Apr 17, 2019 2:48 pm
by djarko
B.MIESIAC.jpg
B.MIESIAC.jpg (104.95 KiB) Viewed 475 times

Re: Loading MTD data as YTD data

Posted: Wed Apr 17, 2019 2:54 pm
by Wim Gielis
Thanks. Example: loading April data.

1. Zero out April.
2. Write an additional process that copies Jan, Feb, Mar into Apr but negatively (i.e. reverse the sign). A zero out is not needed, you already did that. Call this helper process with ExecuteProcess in the Prolog tab of your main process.
3. Load YTD values (Jan, Feb, Mar, Apr) cumulatively into the month of April, positively (i.e. keep the sign). The net effect will be both April values as well as changes w.r.t. Jan, Feb, Mar.

Re: Loading MTD data as YTD data

Posted: Wed Apr 17, 2019 3:12 pm
by djarko
Isn't there posibility to manipulate with month dimension on Variables tab?
I would like to use, as viariable, P_Month parameter, instead of dimension from SAP table (see attachment).
Variables.PNG
Variables.PNG (70.67 KiB) Viewed 465 times
what do you mea:
Load YTD values (Jan, Feb, Mar, Apr) cumulatively into the month of April
This is what I want to achieve :-)
How to do it?

Re: Loading MTD data as YTD data

Posted: Wed Apr 17, 2019 3:28 pm
by Wim Gielis
I understand that you are using the wizard but without the wizard I mean:

Code: Select all

CellIncrementN( Value, 'cube name', 'element reference 1', 'element reference 2', 'element reference 3', ..., P_Month, 'element reference 4', 'element reference 5', measure );
This code goes into the Data tab of the process.

If you use the wizard, that can work too, but he will give you:

Code: Select all

CellPutN( Value + CellGetN( ), ..., ... );
instead of

Code: Select all

CellIncrementN
.

Re: Loading MTD data as YTD data

Posted: Thu Apr 18, 2019 12:36 pm
by djarko
Hi Wim,

I manipulated with SQL code, and, I think reached my target:

on Maps\Cube tab "Data Action" field I set as "Store values".
In Sql code I used MAX(MONAT) instruction, so Advanced\Prolog tab looks like:

DataSourceQuery = 'SELECT
[GJAHR]
,max([MONAT])
,[LDGRP]
,[HKONT]
,[ZZPRODUCT]
,[ZZDISTCHAN]
,[ZZSOURCE]
,[ZZACTIVITY]
,[ZZREINSCON]
,[ZZIP]
,sum([DMBTR]) as Amount
FROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS]
where [LDGRP] not in (''S2'')
and GJAHR = ' | P_Rok | '
and MONAT <= ' | P_Miesiac | '
group by
[GJAHR]
,[LDGRP]
,[HKONT]
,[ZZPRODUCT]
,[ZZDISTCHAN]
,[ZZSOURCE]
,[ZZACTIVITY]
,[ZZREINSCON]
,[ZZIP]';

instead of:

DataSourceQuery = 'SELECT
[GJAHR]
,[MONAT]
,[LDGRP]
,[HKONT]
,[ZZPRODUCT]
,[ZZDISTCHAN]
,[ZZSOURCE]
,[ZZACTIVITY]
,[ZZREINSCON]
,[ZZIP]
,sum([DMBTR]) as Amount
FROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS]
where [LDGRP] not in (''S2'')
and GJAHR = ' | P_Rok | '
and MONAT <= ' | P_Miesiac | '
group by
[GJAHR]
,[MONAT]
,[LDGRP]
,[HKONT]
,[ZZPRODUCT]
,[ZZDISTCHAN]
,[ZZSOURCE]
,[ZZACTIVITY]
,[ZZREINSCON]
,[ZZIP]';

Anyway, thank you for your help. I'm impressed by your website/blog. I have it in my Favorites :-)