ODBO Process

Post Reply
Jorge Rachid
Posts: 100
Joined: Fri Jul 22, 2016 8:33 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

ODBO Process

Post by Jorge Rachid » Fri Jun 21, 2019 1:52 pm

Hi guys,

I did a ODBO process to bring data from another TM1 server to a new application, but I have a little problem with it.

I have a dimension of account that elements are numbers, and there is a alias of number + description. Example: 14001 / 14001 - Bank Account

When this dimension of account is being updated on destination server, the element created is with alias (14001 - Bank Account of the example). The problem is that the elemnts of account dimension that already exists on the new server has only number so when am going to do rules between the cubes to bring data it does not work because on one hand I have number + description and on another I got only numbers.

So I would like to know what I could do to update this dimension only with the numbers and not with the alias.

Any tips?

Many thanks.

JR.

declanr
MVP
Posts: 1619
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: ODBO Process

Post by declanr » Fri Jun 21, 2019 3:06 pm

It just sounds like your source query is picking up the alias and you only want it to be the element name.

You can change the statement to bring only the element name or you can change the process so that it works out which part is code and which part is the name.
Declan Rodger

Jorge Rachid
Posts: 100
Joined: Fri Jul 22, 2016 8:33 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Re: ODBO Process

Post by Jorge Rachid » Fri Jun 21, 2019 3:35 pm

Hi, exactly.
But how can I do this?
Tm1 is generating the code and the MDX.
Where should I change to bring only the code?
Thanks a lot.
JR.
declanr wrote:
Fri Jun 21, 2019 3:06 pm
It just sounds like your source query is picking up the alias and you only want it to be the element name.

You can change the statement to bring only the element name or you can change the process so that it works out which part is code and which part is the name.

tomok
MVP
Posts: 2561
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: ODBO Process

Post by tomok » Fri Jun 21, 2019 4:06 pm

So I assume you are using a cube view as the data source. Change the view to not use the alias for the dimension(s) in question.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Jorge Rachid
Posts: 100
Joined: Fri Jul 22, 2016 8:33 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Re: ODBO Process

Post by Jorge Rachid » Tue Jun 25, 2019 1:53 pm

tomok wrote:
Fri Jun 21, 2019 4:06 pm
So I assume you are using a cube view as the data source. Change the view to not use the alias for the dimension(s) in question.
No, there is no source view as data source.

See some screeshots below. Where should I change to bring only the code and not the complete alias?

Here the cube on mapping:
img1.JPG
img1.JPG (72.19 KiB) Viewed 215 times
Here the dimension that I am facing the problem:
img2.jpg
img2.jpg (111.33 KiB) Viewed 215 times
And here the mdx code that tm1 is generating on the process:
img3.JPG
img3.JPG (86.06 KiB) Viewed 215 times
Where should i change to bring only the code when i update the dimension?

Thanks in advance.

JR.

Jorge Rachid
Posts: 100
Joined: Fri Jul 22, 2016 8:33 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Re: ODBO Process

Post by Jorge Rachid » Thu Jun 27, 2019 12:48 pm

Could anyone help me please?
Thanks in advance.
JR

User avatar
jim wood
Site Admin
Posts: 3701
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: ODBO Process

Post by jim wood » Thu Jun 27, 2019 3:34 pm

Jorge,

I've only done a bit with ODBO but in my experience the GUI is flakey at best. Once I generated the required MDX I straight away set the input to no action, set the dimensions to data load only and set the override data source in the prologue. Also make sure you stop the generated code from working. What I found was that every time I opened the process It would default to something else. Negating the generated could got rid of this. Then I inserted code to export the result of the ODBO MDX query to a flat file. From there you should be able to tweak the MDX to fit. Once the output is as expected you can move on.I found the whole ODBO a pain not easy to use but it works if you get it right,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
jim wood
Site Admin
Posts: 3701
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: ODBO Process

Post by jim wood » Thu Jun 27, 2019 3:36 pm

Oh and btw, Tomok was right. If you've selected a cube as the source then it will be using a view as the source. Based on what I can tell from your screen shots this is the case. I can't see the full generated MDX query. It might help if you post that here.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

Jorge Rachid
Posts: 100
Joined: Fri Jul 22, 2016 8:33 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Re: ODBO Process

Post by Jorge Rachid » Fri Jun 28, 2019 5:21 pm

Hi Jim, I will post here the mdx generated.

WITH
SET [periodo_gmd Set] AS
'{ FILTER( { [datlan_hst_visao_geral].MEMBERS }, ISLEAF( [datlan_hst_visao_geral].CURRENTMEMBER ) ) }'
SET [area_bu Set] AS
'{ FILTER( { [area_bu_hst].MEMBERS }, ISLEAF( [area_bu_hst].CURRENTMEMBER ) ) }'
SET [pacotes_despesas_gmd Set] AS
'{ FILTER( { [pacotes_gmd_hst].MEMBERS }, ISLEAF( [pacotes_gmd_hst].CURRENTMEMBER ) ) }'
SET [codemp_gmd Set] AS
'{ FILTER( { [codemp_hst].MEMBERS }, ISLEAF( [codemp_hst].CURRENTMEMBER ) ) }'
SET [cta_orcamentaria_gmd Set] AS
'{ FILTER( { [cta_orc_visao_big].MEMBERS }, ISLEAF( [cta_orc_visao_big].CURRENTMEMBER ) ) }'
SET [rsg.075.base_despesas_gmd.measures Set] AS
'{ FILTER( { [resumo_acomp.measures].MEMBERS }, ISLEAF( [resumo_acomp.measures].CURRENTMEMBER ) ) }'
SELECT NON EMPTY {
[periodo_gmd Set] * [area_bu Set] * [pacotes_despesas_gmd Set] * [codemp_gmd Set] * [cta_orcamentaria_gmd Set] * [rsg.075.base_despesas_gmd.measures Set]
} ON COLUMNS FROM [resumo_acomp]

The problem is on dimension "cta_orc_visao_big" on bold. I am udptading the dimension "cta_orcamentaria_gmd" based on this dimension, but this code brings me the full code and I would like only the code, without the alias.

I have already tried to manipulate this mdx but is still the same.
jim wood wrote:
Thu Jun 27, 2019 3:36 pm
Oh and btw, Tomok was right. If you've selected a cube as the source then it will be using a view as the source. Based on what I can tell from your screen shots this is the case. I can't see the full generated MDX query. It might help if you post that here.
On data tab I use a simple CellPutN, as below:

CellPutN(nValue, 'RSG.075.Base_Despesas_GMD', datlan_hst_visao_geral,codemp_hst, cta_orc_visao_big, area_bu_hst, pacotes_gmd_hst,resumo_acomp.measures);

Thanks a lot.

JR.

User avatar
jim wood
Site Admin
Posts: 3701
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: ODBO Process

Post by jim wood » Mon Jul 01, 2019 11:11 am

Daft question but do you have a default subset set for the dimension? Does it include the alias?
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

Jorge Rachid
Posts: 100
Joined: Fri Jul 22, 2016 8:33 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Re: ODBO Process

Post by Jorge Rachid » Mon Jul 01, 2019 6:28 pm

Yes, I have. It was with alias, but I have changed and tried again but it still with the same problem.
jim wood wrote:
Mon Jul 01, 2019 11:11 am
Daft question but do you have a default subset set for the dimension? Does it include the alias?

declanr
MVP
Posts: 1619
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: ODBO Process

Post by declanr » Mon Jul 01, 2019 9:31 pm

Bit difficult to sort this kind of thing out without access to the system and a bit of trial and error.

But one “workaround” solution would be that if your account dimension is always a certain number of characters then to use a substring which feeds the cellputn.

This can also work if it’s not a set number of characters, but only if the account already exists in the target (and it sounds like you aren’t doing the metadata side here)... just loop back taking a character off until it returns a dimix of greater than 0 in your target dim and that element is an n level (DType.)
Declan Rodger

Post Reply