Export Dimension along with attribute data into excel

Post Reply
manojnr44
Posts: 3
Joined: Wed Aug 23, 2017 5:56 pm
OLAP Product: Cognos TM1
Version: 10.1
Excel Version: MS Office 2010

Export Dimension along with attribute data into excel

Post by manojnr44 »

Hi,

I have a requirement of exporting complete dimension structure along with the attribute values for each element into an excel file. Can anyone please suggest how this can be achieved by using TI process.I have searched many sites but could only find details for exporting just dimension structure but not along with attribute values.I could achieve it but my code contains mostly redundant code and the max number of attributes is considered as 10 and the asciioutput statements are hard coded with if conditions for different no of attributes.

What I am looking for is a more generic way of implementing it without much hard coding and which can accommodate any no of attributes without having the need to set threshold limit for no of attr.

Looking forward for your valuable suggestions.Thanks in advance.
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: Export Dimension along with attribute data into excel

Post by Alan Kirk »

manojnr44 wrote: Wed Aug 23, 2017 10:31 pm Hi,

I have a requirement of exporting complete dimension structure along with the attribute values for each element into an excel file. Can anyone please suggest how this can be achieved by using TI process.I have searched many sites but could only find details for exporting just dimension structure but not along with attribute values.I could achieve it but my code contains mostly redundant code and the max number of attributes is considered as 10 and the asciioutput statements are hard coded with if conditions for different no of attributes.

What I am looking for is a more generic way of implementing it without much hard coding and which can accommodate any no of attributes without having the need to set threshold limit for no of attr.

Looking forward for your valuable suggestions.Thanks in advance.
It depends on what your intention is for using the file.

If you envisage this as looking like a standard dimension .xdi (which is the standard form of export) but with columns for the various attributes, the problem with generic code is that it would need to be flexible enough to allow for variable numbers of columns and for differing data types since TI will not do an implicit conversion from numeric to string for writing.

But if you have some flexibility in the format of the output, I think it's worth looking at Bedrock.Dim.Export from the Bedrock library. This will do the export "vertically"; each row represents either an element and its parent, or the attribute and its value, with a code in the first column identifying which is which. Yes, it's a non-standard format but it's very easy to work with and given that it can work with any number of formats it is certainly far more straightforward than trying for a full tabular structure.

If the intent is to use this to copy a dim and its attributes from one server to another there is an import process that will do that automatically as well.
"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.
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: Export Dimension along with attribute data into excel

Post by lotsaram »

If you want a human readable form of viewing dimension structure (like subset editor) then you should look at the TM1 Tools add-in. It has a nice feature for exporting dimension structure to Excel and using standard Excel features like grouping to expand and collapse rows.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
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: Export Dimension along with attribute data into excel

Post by Alan Kirk »

lotsaram wrote: Thu Aug 24, 2017 8:13 am If you want a human readable form of viewing dimension structure (like subset editor) then you should look at the TM1 Tools add-in. It has a nice feature for exporting dimension structure to Excel and using standard Excel features like grouping to expand and collapse rows.
I hear from some semi-reliable source or other that it also allows you have specific attributes fed into the output too. Just not all of them. I have it on good authority that such a feature isn't currently intended for inclusion in the updated version that's currently being worked on... but it wouldn't be impossible if there was a real need for it.
"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.
manojnr44
Posts: 3
Joined: Wed Aug 23, 2017 5:56 pm
OLAP Product: Cognos TM1
Version: 10.1
Excel Version: MS Office 2010

Re: Export Dimension along with attribute data into excel

Post by manojnr44 »

Thank You for the responses. But actually my requirement was to write a TI process to take backup of dimension structure along with attribute values which can be executed before updating the dimension so that the original structure can be restored if any issue happens with updation process.
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: Export Dimension along with attribute data into excel

Post by lotsaram »

manojnr44 wrote: Mon Aug 28, 2017 12:58 pm Thank You for the responses. But actually my requirement was to write a TI process to take backup of dimension structure along with attribute values which can be executed before updating the dimension so that the original structure can be restored if any issue happens with updation process.
In which case the Bedrock.Dim.Export process should be perfectly adequate for your requirement.

However, be aware that should you DELETE a leaf element as part of a dimension update against which cube data is stored then restoring the dimension will do just that = restoring the dimension. It will not restore the data.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Post Reply