Aggregation problem when linking cubes

Post Reply
ahmedamr
Posts: 11
Joined: Wed Sep 23, 2020 10:12 am
OLAP Product: Planning Analytics/TM1
Version: 2.0.x
Excel Version: 2016

Aggregation problem when linking cubes

Post by ahmedamr »

Hello everyone,

I am still new to TM1 so there are some concepts I haven't quite grasped.

I am trying to link two cubes together. The first cube has a measure element which I am trying to move to the second cube. Both cubes have the exact same dimensions except for one more dimension in the target cube. When I click the check box to slice on all members, the data link works and I can see the measure element data in the target cube, but it is multiplied by the number of elements in the dimension I sliced on in the target cube (I know it's not actual multiplication but rather a consolidation of all elements in the dimension). So my question is, am I doing the link in a wrong way, or am I trying to use it for something which it is not intended for and I will be better of moving data in a different way. I apologize for not including screenshots but I currently do not have access to the server.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Aggregation problem when linking cubes

Post by lotsaram »

Pictures might help. I can't figure out what you are trying to do.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Adam
Posts: 94
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.x
Excel Version: Microsoft 365 x64

Re: Aggregation problem when linking cubes

Post by Adam »

Hi Ahmed,

It sounds to me like you're using Performance Modeler.

I'm going to surmise your moving from let's say year dimensionality in the source cube to year + month dimensionality in the target cube, am I close?

In the target cube, if you have one additional dimension (e.g. month), which element (e.g. which month) did you want the source cube data to go to, if not each and every element (e.g. each and every month)?

You need to tell TM1 where to put it, otherwise it won't go where you expect.
Take care.
Adam
User avatar
PavoGa
MVP
Posts: 616
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: Aggregation problem when linking cubes

Post by PavoGa »

ahmedamr wrote: Wed Feb 24, 2021 11:23 am Hello everyone,

I am still new to TM1 so there are some concepts I haven't quite grasped.
Adam wrote: Wed Feb 24, 2021 8:10 pm Hi Ahmed,

It sounds to me like you're using Performance Modeler.

First concept to grasp may be abandoning Performance Muddler. ;)
Ty
Cleveland, TN
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Aggregation problem when linking cubes

Post by Alan Kirk »

PavoGa wrote: Wed Feb 24, 2021 9:58 pm
ahmedamr wrote: Wed Feb 24, 2021 11:23 am Hello everyone,

I am still new to TM1 so there are some concepts I haven't quite grasped.
Adam wrote: Wed Feb 24, 2021 8:10 pm Hi Ahmed,

It sounds to me like you're using Performance Modeler.

First concept to grasp may be abandoning Performance Muddler. ;)
I agree. It is not simply that it's a bad tool (and I am being SO charitable there), but IBM has already confirmed that it has no future. If you learn how to do things the Performance Muddler way, you'll need to re-learn them anyway.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
ahmedamr
Posts: 11
Joined: Wed Sep 23, 2020 10:12 am
OLAP Product: Planning Analytics/TM1
Version: 2.0.x
Excel Version: 2016

Re: Aggregation problem when linking cubes

Post by ahmedamr »

I will try to expand on the problem. So the source cube has to following dimensions: time, user, product, and, consumption_measures:order_count. The target cube has the following dimensions: time, user, product, raw_materials (the raw materials that goes into each product), and consumption_measures:order_count. What I am trying to do is to move the order_count data to the target cube. Since raw_materials dimension is only present in the target cube I click the check box which slices on all elements in the raw_materials dimension. The produces an outcome where the order_count data is put in each element, so if I have 12 elements in the raw_material dimension, the order_count is 12 times what it should be.

P.S. yes I am using performance modeler which reading IBM documentation thought was the correct way to go. If you have any advice on which client I should use for modeling I will be very appreciative.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Aggregation problem when linking cubes

Post by Alan Kirk »

ahmedamr wrote: Thu Feb 25, 2021 9:12 am P.S. yes I am using performance modeler which reading IBM documentation thought was the correct way to go. If you have any advice on which client I should use for modeling I will be very appreciative.
It's a little late in the evening for me to deal with your main question since I have quite a few things to do before I go to bed, but I'll deal with this one.

Back in the 10.2 days (which you are still on, and which I suggest you move away from because it's no longer supported), IBM was indeed pushing clients to use Performance Muddler. However they eventually "got" the customer feedback that it was a piece of cr@p.

One of the issues is that it attempted to "do stuff automatically" rather than having you get your hands dirty by manually writing TI code and rules. And it usually did that stuff really, really badly.

What other options do you have?

You can write TI in the TI editor in Perspectives, which is admittedly a dinosaur and painful to work with. In the days of yore a lot of admins wrote code in a text editing tool called Notepad++, and copied it into the TI editor when they were done.

Rules could be done using either the Rules editor, or using .xru worksheets. (Search the manuals to find information on those; they're easy to work with.)

In PA2 the replacement for performance Muddler is a web based solution called Planning Analytics Workspace (PAW). I have expressed my views about how PAW is deployed previously and have used my profanity quota for the day so I shan't go into that further. Besides, it's of no use to you on 10.2.

So that's my short term recommendation; write your TI code in Notepad++ (you'll find some useful extensions to that in the new Free Tools sub-forum) and write your rules in .xru sheets so that you really learn the code.

My medium term recommendation is that you upgrade to PA2. You can still use the classic clients (as I do presently) so you won't need to retrain all of your users in the short term, you'll be back on a supported version, and most importantly of all, you'll have the current version of the new API which will open up a world of other possibilities to you.

One of those possibilities is Cubewise Arc. My only connection with it is that I'm a paying customer, so I'm not advertising or shilling it... I'm just telling you that it's one of the most freaking awesome pieces of software I've used in the TM1 space because... it is. BUT... to get full functionality from it you need to be on PA2 because the REST API on 10.2 was still undeveloped. It's therefore not an option for you until or unless you upgrade.

Using Notepad++, the TI editor and XRU sheets is like a step back in time in some ways, but they ARE the best place to really learn the art and craft of TM1 development at the 10.2 level. IMHO.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
ahmedamr
Posts: 11
Joined: Wed Sep 23, 2020 10:12 am
OLAP Product: Planning Analytics/TM1
Version: 2.0.x
Excel Version: 2016

Re: Aggregation problem when linking cubes

Post by ahmedamr »

Thanks for taking the time to reply to my question. Actually I am on PA2, but I am not using PAW as I thought it is meant for the analyst role not the modeling role, so I was using PM and Architect. From what I understand my choice now is between Architect/Perspectives and PAW/PAx. Which do you think is more suitable to grasp now which will not go obsolete on the long run?
Wim Gielis
MVP
Posts: 3105
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: Aggregation problem when linking cubes

Post by Wim Gielis »

ahmedamr wrote: Thu Feb 25, 2021 10:14 am From what I understand my choice now is between Architect/Perspectives and PAW/PAx. Which do you think is more suitable to grasp now which will not go obsolete on the long run?
That's a no-brainer, PAW is the new modeling environment.

You also did not mention Notepad++ as an option, some of us prefer that one.
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
Adam
Posts: 94
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.x
Excel Version: Microsoft 365 x64

Re: Aggregation problem when linking cubes

Post by Adam »

ahmedamr wrote: Thu Feb 25, 2021 9:12 am So the source cube has to following dimensions: time, user, product, and, consumption_measures:order_count.

The target cube has the following dimensions: time, user, product, raw_materials (the raw materials that goes into each product), and consumption_measures:order_count.

What I am trying to do is to move the order_count data to the target cube. Since raw_materials dimension is only present in the target cube I click the check box which slices on all elements in the raw_materials dimension. The produces an outcome where the order_count data is put in each element, so if I have 12 elements in the raw_material dimension, the order_count is 12 times what it should be.
Back to your original question, based on your expanded business case (thank you for the details!), you told TM1 to apply the count to each raw material item, so it literally did what you told it, but sounds like it's not the expected outcome. There's several options, all driven by your business requirements.
  • Did you want the entire count to appear in a single raw material item? Probably not, but if yes, which one?
  • Did you want to allocate it in some way? In which way?
TM1F can't help identify the business requirements but once you tell us what they are, there's plenty of kind folks here willing to suggest best path forward.

Let us know.
Take care.
Adam
ahmedamr
Posts: 11
Joined: Wed Sep 23, 2020 10:12 am
OLAP Product: Planning Analytics/TM1
Version: 2.0.x
Excel Version: 2016

Re: Aggregation problem when linking cubes

Post by ahmedamr »

Thank you Adam for the reply, I am trying to allocate the count to each raw material in the same proportion that it is used in products. For example if the products are hot beverages and the total order count is = 10 and these products are tea and coffee. Then I have 3 raw materials for simplicity tea, coffee, and sugar. What is happening now is the the order count of 10 is move once to each of the raw materials so I get an order count of 30 in the target cube. What I want to happen is for the order count for the total raw material to be 10 and when I drill down to tea (assuming it was ordered 3 times) the order count will be 3. I recognize now that it was silly to expect TM1 to do it without me specifying it, but I wouldn't know how to do it.
Adam
Posts: 94
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.x
Excel Version: Microsoft 365 x64

Re: Aggregation problem when linking cubes

Post by Adam »

ahmedamr wrote: Thu Feb 25, 2021 3:23 pm I am trying to allocate the count to each raw material in the same proportion that it is used in products.
Are these proportions (“recipes”) already defined in TM1 in another cube?

(Answer has to be “yes” otherwise you cannot proceed.)

You’ll need to reference the proportions there to move the data from the higher level product to the lower level material. Then it can be as simple as (Raw Material / Product) * Order Count.
Take care.
Adam
User avatar
PavoGa
MVP
Posts: 616
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: Aggregation problem when linking cubes

Post by PavoGa »

ahmedamr wrote: Thu Feb 25, 2021 9:12 am
P.S. yes I am using performance modeler which reading IBM documentation thought was the correct way to go. If you have any advice on which client I should use for modeling I will be very appreciative.
I agree with Alan on everything regarding a development environment except that I, like Wim, use Notepad++ a great deal, for both rules and processes. Having said that, I started using ARC, which is outstanding in most every way, a couple of months ago and slowly weaning away from Notepad++ all the time.
Ty
Cleveland, TN
ahmedamr
Posts: 11
Joined: Wed Sep 23, 2020 10:12 am
OLAP Product: Planning Analytics/TM1
Version: 2.0.x
Excel Version: 2016

Re: Aggregation problem when linking cubes

Post by ahmedamr »

Adam wrote: Thu Feb 25, 2021 4:37 pm
ahmedamr wrote: Thu Feb 25, 2021 3:23 pm I am trying to allocate the count to each raw material in the same proportion that it is used in products.
Are these proportions (“recipes”) already defined in TM1 in another cube?

(Answer has to be “yes” otherwise you cannot proceed.)

You’ll need to reference the proportions there to move the data from the higher level product to the lower level material. Then it can be as simple as (Raw Material / Product) * Order Count.
The is yes, I am tried to link that cube that contains the recipes with the cube the contains the measures but it all got tangled up. There are three cubes one for the recipes, one which I import into the number of orders, and one where I consolidate all the data into one.
Post Reply