Rule Question

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

Rule Question

Post by Jorge Rachid » Mon Nov 25, 2019 1:07 am

Hi guys, I need some help.

I have two similar cubes and the only difference between then is the period dimension.

In one cube I have the complete period, like 11/25/2019, and on another cube I have only month and year, like 11/2019.

I am facing some doubt to bring the data from the cube which has complete data using DB, because I only want the value on mm/yyyy format.

Any ideas that could help me?

Thanks a lot.

JR

Wim Gielis
MVP
Posts: 2280
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: Rule Question

Post by Wim Gielis » Mon Nov 25, 2019 1:10 am

You have over 100 posts here. Why can't you post the rules and feeders that you now have ?
Basically you need the SUBST function and/or an attribute.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

kangkc
Community Contributor
Posts: 196
Joined: Fri Oct 17, 2008 2:40 am
OLAP Product: TM1
Version: 9.x
Excel Version: 200x
Location: Singapore

Re: Rule Question

Post by kangkc » Mon Nov 25, 2019 2:03 am

Given a choice I will prefer the path of using TI to copy data from one cube to the other. It will be a very straight forward unless the dates you are storing is serial number date format which you may need some date formatting function in combination of SUBST etc.

If insist of using rule, I will just create hierarchy eg
11/2019
11/01/2019
11/02/2019
....
12/2019
12/01/2019
.....

You cna then use rule to pull based on the parent easily.
However real life model can be much more complex than what I have illustrated above.

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

Re: Rule Question

Post by Jorge Rachid » Mon Nov 25, 2019 2:30 pm

Hi Wim. Sorry about that.

I did not put the screenshots here because I tried to do the rule and not worked. I didn't understand how SUBST function would work in this case.

I will put the cube structure of the two cubes here so you can help please?

This is the first cube with the complete period:
cube1.png
cube1.png (10.19 KiB) Viewed 105 times
This is the dimension with the complete period:
complete period.png
complete period.png (57.89 KiB) Viewed 105 times
Now the cube with another dimension of period:
cube2.png
cube2.png (9.9 KiB) Viewed 105 times
Please repair that this cube does not have nrolan and nrolot dimension. In this case I get the total of dimension.

I will put the screenshot of the dimension of period of this cube on another post.

Thanks.
Wim Gielis wrote:
Mon Nov 25, 2019 1:10 am
You have over 100 posts here. Why can't you post the rules and feeders that you now have ?
Basically you need the SUBST function and/or an attribute.

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

Re: Rule Question

Post by Jorge Rachid » Mon Nov 25, 2019 2:33 pm

The period dimension of the cube that I want to do the rule:
short period.png
short period.png (33.99 KiB) Viewed 104 times
I want to match this period here with the same period of the first cube via rule. How could SUBST help in this case?

Thanks a lot.

JR.
Jorge Rachid wrote:
Mon Nov 25, 2019 2:30 pm
Hi Wim. Sorry about that.

I did not put the screenshots here because I tried to do the rule and not worked. I didn't understand how SUBST function would work in this case.

I will put the cube structure of the two cubes here so you can help please?

This is the first cube with the complete period:

cube1.png

This is the dimension with the complete period:

complete period.png

Now the cube with another dimension of period:

cube2.png

Please repair that this cube does not have nrolan and nrolot dimension. In this case I get the total of dimension.

I will put the screenshot of the dimension of period of this cube on another post.

Thanks.
Wim Gielis wrote:
Mon Nov 25, 2019 1:10 am
You have over 100 posts here. Why can't you post the rules and feeders that you now have ?
Basically you need the SUBST function and/or an attribute.

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

Re: Rule Question

Post by Jorge Rachid » Mon Nov 25, 2019 2:35 pm

Hi, thanks for help!

I am working on TI process but it is not working yet. I prefer to do the rule, but I am facing some difficult too.

My dimensions of period has the hierarchy as you said. I put some screenshots here.

On rule you would use the ELPAR function?

Can you explain more?

Thanks, JR.
kangkc wrote:
Mon Nov 25, 2019 2:03 am
Given a choice I will prefer the path of using TI to copy data from one cube to the other. It will be a very straight forward unless the dates you are storing is serial number date format which you may need some date formatting function in combination of SUBST etc.

If insist of using rule, I will just create hierarchy eg
11/2019
11/01/2019
11/02/2019
....
12/2019
12/01/2019
.....

You cna then use rule to pull based on the parent easily.
However real life model can be much more complex than what I have illustrated above.

kangkc
Community Contributor
Posts: 196
Joined: Fri Oct 17, 2008 2:40 am
OLAP Product: TM1
Version: 9.x
Excel Version: 200x
Location: Singapore

Re: Rule Question

Post by kangkc » Tue Nov 26, 2019 1:09 am

Jorge Rachid wrote:
Mon Nov 25, 2019 2:35 pm
Hi, thanks for help!

I am working on TI process but it is not working yet. I prefer to do the rule, but I am facing some difficult too.

My dimensions of period has the hierarchy as you said. I put some screenshots here.

On rule you would use the ELPAR function?

Can you explain more?

Thanks, JR.
No, you do not need to use ELPAR at all as the target dimension will have the exact matching element in the source dimension, just that at target it is N element and at source it is a C element. Hence a simple rule statement using a direct DB will do the trick. This is on the assumption there are no other mapping with other dimensions required.

Post Reply