Loading MTD data as YTD data

[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.

[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.

[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.

[Wim Gielis]

Show us your month dimension please.

[djarko]

B.MIESIAC.jpg (104.95 KiB) Viewed 3961 times

[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.

[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 (70.67 KiB) Viewed 3951 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?

[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

.

[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