Updating Attributes using TI and ODBCoutput

[KevinB]

Our Accountants update/change accounts all the time and I am trying to find the best way to update the Account (add attributes such as account name) in TM1 when we load data from Oracle.

During my Load Actuals for the current month process, I am trying to create a way where If the account does not exist then it is created (done in the metadata section using DIMIX and DimensionElementInsert) then I would like to query Oracle to return the Account attributes and use the AttrPutS() function to update the account names.

From my understanding I have to create the account in the Metadata tab but in the Data Tab I would then alter the Attribute?

So here is what I hope will work...

Metadata TAB
# Check to see if the Seg3 Consoidation exists #
IF(DIMIX('IS Accounts',SEG3)=0);
# If Not... Make a Seg3 Consolidation #
DimensionElementInsert('IS Accounts', '', SEG3, 'C');
# And Add it to the Orphaned Accounts Consolidation #
DimensionElementComponentAdd('IS Accounts', 'Orphaned Accounts', SEG3, 1);
# Then Add the new account to the New SEG3 Consolidation in the Orphaned Accounts #
DimensionElementInsert('IS Accounts','', GL_ACCOUNT,'n');
DimensionElementComponentAdd('IS Accounts', SEG3, GL_ACCOUNT,1);

# if the Segment3 exists place the new account in the Seg3 consolidation
ELSEIF(DIMIX('IS Accounts',SEG3) <>0);
DimensionElementInsert('IS Accounts','',GL_ACCOUNT,'n');
DimensionElementComponentAdd('IS Accounts', SEG3, GL_ACCOUNT,1);
ENDIF;

And then in the Data Tab I would get the Attribute name from Oracle..

DATA TAB

IF(DIMIX('IS Accounts', GL_ACCOUNT)=0);
ODBCOpen('DataSource','username','password');
variableAccountName = ODBCOutput('DataSource','select account_name from accounts where account = '%GL_ACCOUNT%'');
AttrPutS(variableAccountName,'IS Accounts',GL_ACCOUNT,'AccountName');
ODBCClose('DataSource');
ENDIF;


Can this work? Do I have to create a variable in the Metadata tab that will hold the current new account and then the Data Tab will see the variable?

Please Help.

~Kevin Beckley
Portland, OR

[Steve Rowe]

Hi Kevin,

I can see where you are coming from here and I think that with this approach that you are going to end up with a v complex TI.

With a well maintained Oracle ledger my preference would be to have two TIs.

TI1 - Updates Metdata only
1.Flatten the structure, to remove all consolidations, assumes that the consolidation information is in your ledger.
2.Query Oracle to get the current account information, including the consolidation information.
3. Update this in the TI.

TI1 - Load data
1.Clear the cube for the area you are loading to.
2.Load the data.

If you can process the account code changes in a different process to the load you should find things more straight forward. Mixing the two steps can really overcomplicate things. Obviously though you need to make sure the structures you are rebuilding are stored in Oracle though.
Not sure that this will help that much but it can help alot to consider maintaining the metadata as a different problem to loading data.
Cheers

[KevinB]

Thanks Steve.

Unfortunatly the consolidation information is not stored in Oracle (At least no one seems to know how to query it.) I have had to load it from a spreadsheet into TI. That is why I don't want to re-create the consolidations.

If I did make a separate Accounts update processs and ran it before the Load Data process, could I create a process that uses the ODBCoutput function to retrieve the account names and add them to the attributes? This would be great as the accountants are changing/adding things all the time.

Would something like this work?

METADATA
#If the account doesn't exist add it to the account dimension#
DimensionElementInsert('IS Accounts','', GL_ACCOUNT,'n');

DATA
#Get the account name and add it to the account attribute#
ODBCOpen(source, user, pass);
vAcctName = ODBCOutput(Source, 'Select account_name from accounts where account = 'GL_ACCOUNT'';);
ODBCClose(source);
AttrPutS(vAcctName, 'IS Accounts', GL_ACCOUNT, 'Account Name');

Thanks!

~Kevin

[Steve Rowe]

Hi Kevin,
I'd actually simplify this even more I think. Since the atrributes of already existing accounts code change, I'd just set your query up as the data sourece and run it for all accounts and update your account attributes accordingly.

Add new elements in the metadata, one tip here is to omit the dimix test. Adding an element that already exists to a dimension is OK and has (should have) no effect. This cuts out a load of If tests that just hit the speed of the TI.

Update the attributes of all elements.

To do a partial update of elements attributes would be tricky.

Something like this would work
Metadata
If you hit a new element (you need the dimix now) add it to the consolidation "New Element"

Data
If an element is a child of New Element (use ElIsAnc)
Run your query
Update attribute
Remove element from consolidation, if you don't do this here you need to make sure you clean up the New Element structure so that you don't update everything on the next run.

I would not expect this to run very well due to the repeated opening and closing of the ODBC connection. You may be better off opening the ODBC connection in the prolog and the closing in the epilog, so that the ODBC link is open for the duration of the TI. This might casue an issue if the TI fails and the ODBC is left open....
HTH