Import data from SQL table into TM1 cube with user input

Post Reply
tm1green
Posts: 22
Joined: Tue Mar 31, 2015 9:54 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Import data from SQL table into TM1 cube with user input

Post by tm1green »

Hi there,

There is a requirement to import data from a SQL table into a TM1 cube with user input data. The import should not wipe out existing user input in the cube.

How can this be implemented in a TI process?

Thanks.
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Import data from SQL table into TM1 cube with user input

Post by declanr »

Just have different measures for the user inputs as compared to the data load, then when the TI runs you just zero out the part it will load to and leave the rest of the cube alone.
Declan Rodger
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: Import data from SQL table into TM1 cube with user input

Post by lotsaram »

tm1green wrote: Fri May 25, 2018 12:24 am There is a requirement to import data from a SQL table into a TM1 cube with user input data. The import should not wipe out existing user input in the cube.

How can this be implemented in a TI process?
As Declan said, just make sure there is a separate measure for user input data versus date imported from the database.

If there aren't separate measures and you are expecting TM1 to know the input lineage of a cell and automatically skip overwriting user input cells when importing from a database. Well then you are in a bind and need to re-think things.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Import data from SQL table into TM1 cube with user input

Post by gtonkin »

Leading on from what Declan and Lotsaram have said. I normally create an attribute on my measures dimension to indicate that the measure will be used for input. This is generally referenced in the security model but leveraged in TI.

In my load process I use MDX to create an All N subset with a filter on my Input items. This subset is then used in the zero out.
Wim Gielis
MVP
Posts: 3103
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: Import data from SQL table into TM1 cube with user input

Post by Wim Gielis »

Or, just to add different options to the mix: input data (like adjustments) could come from a different cube - dimensioned differently. Then in that case, a rule could be written to transfer the data to the main cube. In that case, zeroing out all measures will not hurt you.
Though I agree that selecting the measures (be it hard-coded or with an attribute or name convention) would be beneficial.
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
Post Reply