ODBO Process
-
- Posts: 113
- Joined: Fri Jul 22, 2016 8:33 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
ODBO Process
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.
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.
-
- MVP
- Posts: 1815
- 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
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.
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
-
- Posts: 113
- Joined: Fri Jul 22, 2016 8:33 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
Re: ODBO Process
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.
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.
-
- MVP
- Posts: 2831
- 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
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.
-
- Posts: 113
- Joined: Fri Jul 22, 2016 8:33 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
Re: ODBO Process
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.
-
- Posts: 113
- Joined: Fri Jul 22, 2016 8:33 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
Re: ODBO Process
Could anyone help me please?
Thanks in advance.
JR
Thanks in advance.
JR
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: ODBO Process
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.
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.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: ODBO Process
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.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- Posts: 113
- Joined: Fri Jul 22, 2016 8:33 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
Re: ODBO Process
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.
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.
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.
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.
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: ODBO Process
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.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- Posts: 113
- Joined: Fri Jul 22, 2016 8:33 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
-
- MVP
- Posts: 1815
- 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
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.)
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