How To Compare two dimensions

Post Reply
late.vaibhav
Posts: 25
Joined: Mon Dec 09, 2013 1:00 pm
OLAP Product: tm1
Version: 9.5.2, 10.2.2
Excel Version: 2007 2010
Location: India
Contact:

How To Compare two dimensions

Post by late.vaibhav »

Hi
I have two customer dimensions one is directly linked with SAP which update on monthly basis. The other customer dimension is used in my model. Now I want to compare these two dimension and get the delta(New Customers in SAP) in any format(new dimension,csv etc.)

Currently I am doing this by taking export of both dimension in excel and compare this..
I can not link my customer dimension directly to SAP as the consolidation elements are different in both system.

Thank you in Advance!!!
Last edited by Alan Kirk on Thu Mar 13, 2014 4:14 pm, edited 1 time in total.
Reason: Moved to correct forum. This is NOT an enhancement request.
tomok
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: How To Compare two dimensions

Post by tomok »

If it were me I would write TI processes that export each dimension to a different SQL table and then inside SQL Server create a stored procedure that runs a query to give me all customers in Dimension A, not in B and vice-versa. This way you wouldn't have to manually do anything from that point forward except kick off the TI processes and then the stored proc.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
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: How To Compare two dimensions

Post by Wim Gielis »

Hello

As Tom suggests, I would use a TI process to connect to the SAP dimension (wherever it would be).
In the Metadata or Data tab, do a DIMIX to check the existence of the element in the other dimension.
Finally, in the Data tab, use CSV to export the results. If the DIMIX = 0, add a line to the CSV file.

This is all fairly easy in my opinion, only connecting to the SAP dimension will be the tough part. If that dimension is already in TM1, this exercise should be piece of cake.
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
late.vaibhav
Posts: 25
Joined: Mon Dec 09, 2013 1:00 pm
OLAP Product: tm1
Version: 9.5.2, 10.2.2
Excel Version: 2007 2010
Location: India
Contact:

Re: How To Compare two dimensions

Post by late.vaibhav »

Thanks Tomak and Wim Gielis for your quick response..
sevenbees
Posts: 2
Joined: Fri Mar 14, 2014 2:25 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2010

Re: How To Compare two dimensions

Post by sevenbees »

Greetings,

I've may have another way to do the compare of any two objects on two different servers without having to write a TI process - thinking about this. I'm not sure whether a line by line compare is the way or just look at the differences - and then what to do. Like to discuss. I'm at berryd7@gmail.com. Thanks.

Sevenbees
tomok
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: How To Compare two dimensions

Post by tomok »

sevenbees wrote:I've may have another way to do the compare of any two objects on two different servers without having to write a TI process - thinking about this.
If you have another idea then let's hear it. The purpose of this forum is to publicly exchange ideas, not solicit private conversations.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Drg
Regular Participant
Posts: 159
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: How To Compare two dimensions

Post by Drg »

Who faced the universal approach of comparing two dimensions with the comparison of hierarchies, implying that the serial numbers can be different?

until I came to the conclusion that one of the systems should be considered a donor by another recipient and overwrite the measurement at boot, but this is a very slippery way.

Is there any idea about the approach of comparison or is it an administrative task and is solved by use MDM to synchronize dimensions?
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: How To Compare two dimensions

Post by paulsimon »

Hi

I use this approach in all major dimension updates. I update an _Origin version of the dimension first. This copy of the dimension is not used in any cubes so if the update fails for some reason it does not affect any data. I then have processes that compare the updated _Origin dimension to the current dimension and write out files for all differences, eg elements inserted, elements that changed type, elements that changed parent, consolidation weight changes, etc. It then uses those files to update the main dimension, as well as providing an audit trail of changes.

It did take quite a while to develop that so I am not going to share it for free. However, at least you know that it is possible to write TI to compare two dimensions.

I am not that familiar with getting dimensions from SAP. Would it be possible to have just one dimension, and to update that from SAP and to then run a process to add on the additional hierarchies that are currently in your other dimension, rather than having two separate dimensions?

Regards

Paul Simon
Drg
Regular Participant
Posts: 159
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: How To Compare two dimensions

Post by Drg »

paulsimon wrote: Wed Sep 26, 2018 8:33 pm I use this approach in all major dimension updates. I update an _Origin version of the dimension first. This copy of the dimension is not used in any cubes so if the update fails for some reason it does not affect any data. I then have processes that compare the updated _Origin dimension to the current dimension and write out files for all differences, eg elements inserted, elements that changed type, elements that changed parent, consolidation weight changes, etc. It then uses those files to update the main dimension, as well as providing an audit trail of changes.
this like a real things what i mean. full compare dimension with attributes.
I agree that such things should not be distributed publicly, otherwise there will be many "professionals who know software like their 5 fingers" around.
Thanks for the suggestions, I will implement it on my own.

paulsimon wrote: Wed Sep 26, 2018 8:33 pm I am not that familiar with getting dimensions from SAP. Would it be possible to have just one dimension, and to update that from SAP and to then run a process to add on the additional hierarchies that are currently in your other dimension, rather than having two separate dimensions?

At the moment I am implementing the integration between the two bases tm1, which use different sources for measurements (which is a separate question about the introduction of the product)
Post Reply