Rule Erroring on Duplicative Dimension in DB Formula
-
- Posts: 36
- Joined: Fri Mar 10, 2017 8:26 pm
- OLAP Product: TM1
- Version: PA 2.0.8
- Excel Version: 2016
- Location: Dallas, TX
Rule Erroring on Duplicative Dimension in DB Formula
Hi All and Happy Friday!
I am experiencing an issue with a rule that is trying to plug the difference between two cubes by rule. Unfortunately, the name of the first cube and one of its dimensions repeats, which causes the rule to error out.
Normally in a rule you can call out the dimension in front of elements with a duplicative name, but how do you handle a duplicative dimension name within a DB formula? Is this even possible? Unfortunately, remaking the cube is not currently an option.
[{'Midstream Contracts':'No_Contract','Midstream Customer':'No_Customer'}] = N: DB('Midstream Metrics',!Metrics Scenario,!MidstreamAssets,!MidstreamMetrics,!Year,!Period) - DB ( 'Midstream Metrics by Contract' , !MetricScenario , !MidstreamAssets , 'Midstream Contracts Subtotal' , 'Midstream Customers Subtotal' , !MidstreamContractMetrics , !Year , !Period ) ;
I am experiencing an issue with a rule that is trying to plug the difference between two cubes by rule. Unfortunately, the name of the first cube and one of its dimensions repeats, which causes the rule to error out.
Normally in a rule you can call out the dimension in front of elements with a duplicative name, but how do you handle a duplicative dimension name within a DB formula? Is this even possible? Unfortunately, remaking the cube is not currently an option.
[{'Midstream Contracts':'No_Contract','Midstream Customer':'No_Customer'}] = N: DB('Midstream Metrics',!Metrics Scenario,!MidstreamAssets,!MidstreamMetrics,!Year,!Period) - DB ( 'Midstream Metrics by Contract' , !MetricScenario , !MidstreamAssets , 'Midstream Contracts Subtotal' , 'Midstream Customers Subtotal' , !MidstreamContractMetrics , !Year , !Period ) ;
- gtonkin
- MVP
- Posts: 1212
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Rule Erroring on Duplicative Dimension in DB Formula
At a quick glance it looks like your braces are the issue, different dimensions within the braces, not removing ambiguity by specifying dimensions.
-
- Posts: 36
- Joined: Fri Mar 10, 2017 8:26 pm
- OLAP Product: TM1
- Version: PA 2.0.8
- Excel Version: 2016
- Location: Dallas, TX
Re: Rule Erroring on Duplicative Dimension in DB Formula
Thanks for looking at my issue, but removing the brackets did not help. The issue is that the cube "Midstream Metrics" and one of its dimensions have the same name.
-
- MVP
- Posts: 1817
- 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: Rule Erroring on Duplicative Dimension in DB Formula
What error message does it give you?
And what editor are you using: Architect, Workspace, Performance Modeler, Arc etc?
I don’t think any of my models have had cubes and dimensions with the same name but I feel like some of the sample IBM models did and I’m pretty sure it shouldn’t cause an issue - since the bang notation always refers to a dimension or hierarchy and not a cube.
And what editor are you using: Architect, Workspace, Performance Modeler, Arc etc?
I don’t think any of my models have had cubes and dimensions with the same name but I feel like some of the sample IBM models did and I’m pretty sure it shouldn’t cause an issue - since the bang notation always refers to a dimension or hierarchy and not a cube.
Declan Rodger
-
- Regular Participant
- Posts: 359
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: Rule Erroring on Duplicative Dimension in DB Formula
Hi,
Maren
If that is the issue, then have you created another cube with a different name and confirmed it resolves the problem?"The issue is that the cube "Midstream Metrics" and one of its dimensions have the same name."
Maren
-
- MVP
- Posts: 3667
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Rule Erroring on Duplicative Dimension in DB Formula
I definately have models where cubes and dimensions share the same name. This doesn't cause any issue in rules. Your issue is something else.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
- gtonkin
- MVP
- Posts: 1212
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Rule Erroring on Duplicative Dimension in DB Formula
Looks like something else - one 's' missing in second DB by the looks of it:
[{'Midstream Contracts':'No_Contract','Midstream Customer':'No_Customer'}] = N:
DB('Midstream Metrics',!Metrics Scenario,!MidstreamAssets,!MidstreamMetrics,!Year,!Period) -
DB('Midstream Metrics by Contract',!MetricScenario,!MidstreamAssets,'Midstream Contracts Subtotal','Midstream Customers Subtotal',!MidstreamContractMetrics , !Year , !Period );
-
- Posts: 36
- Joined: Fri Mar 10, 2017 8:26 pm
- OLAP Product: TM1
- Version: PA 2.0.8
- Excel Version: 2016
- Location: Dallas, TX
Re: Rule Erroring on Duplicative Dimension in DB Formula
Thanks for noticing the misspelling, but the error occurs earlier. Syntax error on or before: ' !Midstream Metrics , invalid string expression etc. See attached.
I am fairly certain remaking the cube with a different name would work, but the cube owner has business reasons not to want to do that at this particular time.
I am fairly certain remaking the cube with a different name would work, but the cube owner has business reasons not to want to do that at this particular time.
- Attachments
-
- Error
- error.JPG (20.21 KiB) Viewed 14769 times
-
- MVP
- Posts: 1817
- 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: Rule Erroring on Duplicative Dimension in DB Formula
Do all of these dimensions exist in the cube that you are writing the rule for:
!Metrics Scenario
!MidstreamAssets
!MidstreamMetrics
!Year
!Period
!MetricScenario
!MidstreamContractMetrics
I expect that the issue you are seeing is more fundamental than the name of the cube/dimension being the same.
!Metrics Scenario
!MidstreamAssets
!MidstreamMetrics
!Year
!Period
!MetricScenario
!MidstreamContractMetrics
I expect that the issue you are seeing is more fundamental than the name of the cube/dimension being the same.
Declan Rodger
-
- Regular Participant
- Posts: 359
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: Rule Erroring on Duplicative Dimension in DB Formula
Can't you make absolutely certain by doing it as a test, call the cube ztest or something? When the test is done, delete the cube.I am fairly certain remaking the cube with a different name would work, but the cube owner has business reasons not to want to do that at this particular time.
And if that is the issue then tell the cube owner the rule cant be added while the cube is named as it is!
Maren
- Steve Rowe
- Site Admin
- Posts: 2424
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Rule Erroring on Duplicative Dimension in DB Formula
Just to chip in that I fairly often call a cube and its measure dimension the same name and I've never known it cause an issue.
As others have said, it seems that the most likely answer is that there is a basic syntax / dimension reference error in your rule.
The invalid string expression error is normally arising from a !dimname reference to a dimension that is not in the cube in which the rule is written, as suggested by Declan
As others have said, it seems that the most likely answer is that there is a basic syntax / dimension reference error in your rule.
The invalid string expression error is normally arising from a !dimname reference to a dimension that is not in the cube in which the rule is written, as suggested by Declan
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 4
- Joined: Wed Oct 06, 2021 4:17 pm
- OLAP Product: TM1
- Version: PAoC
- Excel Version: Office 365
Re: Rule Erroring on Duplicative Dimension in DB Formula
You have curly brackets on the left hand side of the rule that are not required. You are referencing different dimensions within them which may cause a problem
-
- MVP
- Posts: 600
- Joined: Wed Aug 17, 2011 1:19 pm
- OLAP Product: TM1
- Version: 9.5.2 10.1 10.2
- Excel Version: 2003 2007
- Location: York, UK
Re: Rule Erroring on Duplicative Dimension in DB Formula
It's been a very long time since I looked at the parsing code but I seem to recollect that you can't do bang notation (!dimname) on a dimension name with a space in it. I'm probably wrong. Perhaps someone closer to the product could confirm or refute for me.
-
- MVP
- Posts: 3128
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Rule Erroring on Duplicative Dimension in DB Formula
Should be no problem.Duncan P wrote: ↑Thu Oct 07, 2021 10:39 pm It's been a very long time since I looked at the parsing code but I seem to recollect that you can't do bang notation (!dimname) on a dimension name with a space in it. I'm probably wrong. Perhaps someone closer to the product could confirm or refute for me.
This being said, I cannot imagine that a rule syntax takes over a week to be corrected, unless there’s holidays or other priorities of course.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- PavoGa
- MVP
- Posts: 618
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Rule Erroring on Duplicative Dimension in DB Formula
It is not a problem. Do it all the time.Duncan P wrote: ↑Thu Oct 07, 2021 10:39 pm It's been a very long time since I looked at the parsing code but I seem to recollect that you can't do bang notation (!dimname) on a dimension name with a space in it. I'm probably wrong. Perhaps someone closer to the product could confirm or refute for me.
Ty
Cleveland, TN
Cleveland, TN
-
- MVP
- Posts: 600
- Joined: Wed Aug 17, 2011 1:19 pm
- OLAP Product: TM1
- Version: 9.5.2 10.1 10.2
- Excel Version: 2003 2007
- Location: York, UK
Re: Rule Erroring on Duplicative Dimension in DB Formula
Looking back through some old posts reminded me that the problem that I was misremembering was when you have it would think that the @ was part of the dimension name. Sorry for the confusion / memory loss.
Code: Select all
!dimname @= 'string'
- PavoGa
- MVP
- Posts: 618
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Rule Erroring on Duplicative Dimension in DB Formula
Never saw a response on this regarding what the problem/solution was, but @flecky was correct on this it seems: the curly braces in the LHS addressing are telling the rule that elements from two dimensions are part of the same set.
should be:
Code: Select all
[{'Midstream Contracts':'No_Contract','Midstream Customer':'No_Customer'}] = N:
Code: Select all
['Midstream Contracts':'No_Contract', 'Midstream Customer':'No_Customer'] = N:
Ty
Cleveland, TN
Cleveland, TN