DB formula not working when copying rule

Post Reply
kenship
Posts: 143
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

DB formula not working when copying rule

Post by kenship » Fri Jun 16, 2017 8:12 pm

I have been using Architect to rules and often copy rules from one cube to another (with same dimensions) for further tweaking.

I realize my DB formula of the rules often fail to work after pasting and require a rebuild (using simple editor) even the elements are identical.

Is there anything I need to know in copying rules?

Ken

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

Re: DB formula not working when copying rule

Post by Wim Gielis » Fri Jun 16, 2017 9:00 pm

I don't come across this one.
What happens if you first paste in Notepad++ and then copy from Notepad++ to the rules editor ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

kenship
Posts: 143
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: DB formula not working when copying rule

Post by kenship » Fri Jun 16, 2017 10:04 pm

I will try next Tuesday.

Would feeders affect this?

Kenneth

kenship
Posts: 143
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: DB formula not working when copying rule

Post by kenship » Tue Jun 20, 2017 1:22 pm

Wim Gielis wrote:I don't come across this one.
What happens if you first paste in Notepad++ and then copy from Notepad++ to the rules editor ?
I think I know what happens: cube dependency.

Kenneth

User avatar
qml
MVP
Posts: 1086
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: DB formula not working when copying rule

Post by qml » Tue Jun 20, 2017 2:28 pm

kenship wrote:I think I know what happens: cube dependency.
Cube dependency is not something that 'happens'. It is a relationship that is established by the TM1 server (automatically by rule analysis or manually through a TI function) so that the cache invalidation mechanism can work properly. I'm not sure how that would relate to your problem or how that problem even manifests itself.

What do you mean when you say 'my DB formula of the rules often fail to work'? There are many ways in which rules can 'fail to work' - they can refuse to compile; or compile but not get attached to the appropriate cube area (e.g. when you use Direct dimension update functions without recompiling the dimension); or get attached, but not return expected results, usually due to rule order / syntax / data / feeder issues.
Kamil Arendt

kenship
Posts: 143
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: DB formula not working when copying rule

Post by kenship » Tue Jun 20, 2017 2:43 pm

Thanks for the reply.

Let me explain it more thoroughly. As you notice already, I'm amateur in this.

I'm building a few cubes for salary calculation: some for assumptions, one for salary table and one for headcount input.
They are rule connected.

I did not have a TI for cube dependency created.

Due to design change, I create new versions for 2 cubes: the input and the salary table, there are a lot of connections.

The 2 new cubes also have new names.

After creating the cubes I start copying rules from the old version to the new, I updated the name the cubes as well.

However, I realized the DB formula not working until I delete it and use the DB button to create the formula then modify the parameters.

As such, I believe cube dependency is my issue. Although I don't understand why even the same DB formula working in different rules (for different intersection) still requires a re-add. But at this time I can only add these dependency manually one by one.

Thanks for looking.

User avatar
qml
MVP
Posts: 1086
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: DB formula not working when copying rule

Post by qml » Tue Jun 20, 2017 3:18 pm

kenship wrote:However, I realized the DB formula not working until I delete it and use the DB button to create the formula then modify the parameters. As such, I believe cube dependency is my issue.
Cube dependency is not the cause of your issue. As stated above, the concept of cube dependencies exists for cache invalidation, not to make rule calculations work.

You still don't specify what you mean by 'not working', but I suspect you simply made a mistake somewhere when copying and editing your rules and feeder statements. Is dimension order exactly the same in your old and new cubes? Maybe your original cubes have been reordered and therefore the order of dimensions seen in Architect is different from the internal one, which is used in rules?
Kamil Arendt

kenship
Posts: 143
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: DB formula not working when copying rule

Post by kenship » Tue Jun 20, 2017 3:33 pm

Not working means not calculating at all.

And it has nothing to do with dimension order or any of the text because after I use DB button to create the formula, I modify the parameters to match exactly like what I copied and it works.

I regard it as cube dependency because every time I used DB button to create formula and save it my log shows a cube dependency created.
qml wrote:
kenship wrote:However, I realized the DB formula not working until I delete it and use the DB button to create the formula then modify the parameters. As such, I believe cube dependency is my issue.
Cube dependency is not the cause of your issue. As stated above, the concept of cube dependencies exists for cache invalidation, not to make rule calculations work.

You still don't specify what you mean by 'not working', but I suspect you simply made a mistake somewhere when copying and editing your rules and feeder statements. Is dimension order exactly the same in your old and new cubes? Maybe your original cubes have been reordered and therefore the order of dimensions seen in Architect is different from the internal one, which is used in rules?

User avatar
qml
MVP
Posts: 1086
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: DB formula not working when copying rule

Post by qml » Tue Jun 20, 2017 3:41 pm

kenship wrote:And it has nothing to do with dimension order or any of the text because after I use DB button to create the formula, I modify the parameters to match exactly like what I copied and it works.
TM1 doesn't care how you created the code as long as the code is correct. There must be something incorrect in your code.
kenship wrote:I regard it as cube dependency because every time I used DB button to create formula and save it my log shows a cube dependency created.
Correlation doesn't equal causation. Cube dependencies are added automatically by the server, for example when a rule calculates for the first time or at server startup. Whenever that happens a message log entry is added. The fact you're not seeing that message in the log for your manual code supports the hypothesis that your code contains errors.
Kamil Arendt

kenship
Posts: 143
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: DB formula not working when copying rule

Post by kenship » Tue Jun 20, 2017 3:50 pm

No worry. I manually re-add each line by using DB button and match my copied codes. They all work now.
qml wrote:
kenship wrote:And it has nothing to do with dimension order or any of the text because after I use DB button to create the formula, I modify the parameters to match exactly like what I copied and it works.
TM1 doesn't care how you created the code as long as the code is correct. There must be something incorrect in your code.
kenship wrote:I regard it as cube dependency because every time I used DB button to create formula and save it my log shows a cube dependency created.
Correlation doesn't equal causation. Cube dependencies are added automatically by the server, for example when a rule calculates for the first time or at server startup. Whenever that happens a message log entry is added. The fact you're not seeing that message in the log for your manual code supports the hypothesis that your code contains errors.

User avatar
PavoGa
Community Contributor
Posts: 432
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: DB formula not working when copying rule

Post by PavoGa » Wed Jun 21, 2017 11:47 am

kenship wrote:No worry. I manually re-add each line by using DB button and match my copied codes. They all work now.
Just out of curiosity, because it appears you are seeing something the rest of us have not, would you post your non-working code (Before) and the corrected code (After)?
Ty
Cleveland, TN

kenship
Posts: 143
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: DB formula not working when copying rule

Post by kenship » Wed Jun 21, 2017 12:02 pm

They are the same code, not a single difference at all.

I do understand this is weird. Yesterday I tried to create new cubes and add the codes for testing again. This time it seems working quite fine and therefore I want to rest my case.

Kenneth
PavoGa wrote:
kenship wrote:No worry. I manually re-add each line by using DB button and match my copied codes. They all work now.
Just out of curiosity, because it appears you are seeing something the rest of us have not, would you post your non-working code (Before) and the corrected code (After)?

creahui
Posts: 1
Joined: Thu Jul 28, 2016 10:33 am
OLAP Product: TM1
Version: 11.7
Excel Version: 2016

Re: DB formula not working when copying rule

Post by creahui » Fri Jul 17, 2020 5:00 am

Today I encountered this 'not calculating' issue as awell

While I created a new cube in explorer and tried to use DB to mirror the source cube to the targeted cube.
I copied and modified the rules from consultant (thank you) and the cube view are all zero.
I even deleted and re-created the new cube and run the process to 'refresh' the feeders
still no luck with that.
At last I found this page and followed the way to click the buttons directly and it works like a charm, thanks very much...


[edit] sorry for pumping this 3-year old thread, hope some newbie like me can find this workaround in similar case.

kenship
Posts: 143
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: DB formula not working when copying rule

Post by kenship » Fri Jul 17, 2020 12:27 pm

Glad it helped. At least I'm not the only one.

Ken
creahui wrote:
Fri Jul 17, 2020 5:00 am
Today I encountered this 'not calculating' issue as awell

While I created a new cube in explorer and tried to use DB to mirror the source cube to the targeted cube.
I copied and modified the rules from consultant (thank you) and the cube view are all zero.
I even deleted and re-created the new cube and run the process to 'refresh' the feeders
still no luck with that.
At last I found this page and followed the way to click the buttons directly and it works like a charm, thanks very much...


[edit] sorry for pumping this 3-year old thread, hope some newbie like me can find this workaround in similar case.

Post Reply