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 » Thu Mar 13, 2014 12:40 pm

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.

User avatar
tomok
MVP
Posts: 2483
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 » Thu Mar 13, 2014 4:33 pm

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: 1809
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: How To Compare two dimensions

Post by Wim Gielis » Thu Mar 13, 2014 7:20 pm

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

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 » Fri Mar 14, 2014 6:32 am

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 » Wed Mar 19, 2014 4:35 pm

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

User avatar
tomok
MVP
Posts: 2483
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 » Wed Mar 19, 2014 5:08 pm

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
Posts: 84
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 » Wed Sep 26, 2018 8:23 am

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: 635
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: How To Compare two dimensions

Post by paulsimon » Wed Sep 26, 2018 8:33 pm

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
Posts: 84
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 » Fri Sep 28, 2018 7:07 am

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