Loading MTD data as YTD data

Post Reply
djarko
Posts: 5
Joined: Mon Dec 04, 2017 10:33 am
OLAP Product: Cognos Express
Version: 10.2.2
Excel Version: 2013 Professional
Location: Poland / Warsaw

Loading MTD data as YTD data

Post by djarko » Wed Apr 17, 2019 1:52 pm

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
MVP
Posts: 2084
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Loading MTD data as YTD data

Post by Wim Gielis » Wed Apr 17, 2019 2:24 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

djarko
Posts: 5
Joined: Mon Dec 04, 2017 10:33 am
OLAP Product: Cognos Express
Version: 10.2.2
Excel Version: 2013 Professional
Location: Poland / Warsaw

Re: Loading MTD data as YTD data

Post by djarko » Wed Apr 17, 2019 2:29 pm

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
MVP
Posts: 2084
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Loading MTD data as YTD data

Post by Wim Gielis » Wed Apr 17, 2019 2:41 pm

Show us your month dimension please.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

djarko
Posts: 5
Joined: Mon Dec 04, 2017 10:33 am
OLAP Product: Cognos Express
Version: 10.2.2
Excel Version: 2013 Professional
Location: Poland / Warsaw

Re: Loading MTD data as YTD data

Post by djarko » Wed Apr 17, 2019 2:48 pm

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

Wim Gielis
MVP
Posts: 2084
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Loading MTD data as YTD data

Post by Wim Gielis » Wed Apr 17, 2019 2:54 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

djarko
Posts: 5
Joined: Mon Dec 04, 2017 10:33 am
OLAP Product: Cognos Express
Version: 10.2.2
Excel Version: 2013 Professional
Location: Poland / Warsaw

Re: Loading MTD data as YTD data

Post by djarko » Wed Apr 17, 2019 3:12 pm

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 263 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
MVP
Posts: 2084
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Loading MTD data as YTD data

Post by Wim Gielis » Wed Apr 17, 2019 3:28 pm

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
.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

djarko
Posts: 5
Joined: Mon Dec 04, 2017 10:33 am
OLAP Product: Cognos Express
Version: 10.2.2
Excel Version: 2013 Professional
Location: Poland / Warsaw

Re: Loading MTD data as YTD data

Post by djarko » Thu Apr 18, 2019 12:36 pm

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 :-)

Post Reply