TM1 Forum

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.

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.

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.

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.

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:
Here the dimension that I am facing the problem:
And here the mdx code that tm1 is generating on the process:
Where should i change to bring only the code when i update the dimension?

Thanks in advance.

JR.

Could anyone help me please?
Thanks in advance.
JR

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.

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.

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.

Daft question but do you have a default subset set for the dimension? Does it include the alias?

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?

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