TM1 Data insert into SQL

Post Reply
Markuss
Posts: 4
Joined: Thu Apr 30, 2020 3:32 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010 + 2016
Location: UK

TM1 Data insert into SQL

Post by Markuss » Tue Jul 21, 2020 11:17 am

Hi All,

Another interesting problem came up when working with TM1 as newbie :)
Well, two issues actually..

1st - I'm writing out element description (using vDesc=ATTRS('WIN',vWIN,'Description'); ) to SQL table

result is "123456 - Product Description"

How to remove part of string starting with 1st character and ending with "-" character. so it would be only "Product Description"

2nd - Some of product descriptions contains single quotation mark - '1234567 - SANTA'S BASKET' which leads SQL to fail with message "Incorrect syntax near 'S'

How do I wrap this correctly?

Thanks in advance :)

Wim Gielis
MVP
Posts: 2487
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TM1 Data insert into SQL

Post by Wim Gielis » Tue Jul 21, 2020 11:22 am

Hi,

Problem 1: you can add an additional alias (or text attribute) to the relevant dimension. If you handle these operations when you update the dimensions, you don't have to bother anywhere else (input/reporting/send to DWH/loading data/calculations/...)

Have a look at the DELET function together with SCAN.

Problem 2: Again, SCAN, and this time INSRT function instead of DELET. However, if it's possible that multiple single quotation marks occur in a string then a WHILE... END loop can be needed. It's not difficult at all, you can find many examples on the forum here.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Markuss
Posts: 4
Joined: Thu Apr 30, 2020 3:32 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010 + 2016
Location: UK

Re: TM1 Data insert into SQL

Post by Markuss » Tue Jul 21, 2020 12:20 pm

Thank you Wim,

Found something that solves #2 issue - removes single quotation mark. I think that code below can be adapted to resolve #1 issue same time..

Code: Select all

vDesc= ATTRS('WIN',vWIN,'Description');

#Remove singe quote from description string. without this loop SQL insert will fail.
iClean = 0;
WHILE ( iClean <> 1 );
	iPos = SCAN ( '''', vDesc );
	IF ( iPos <> 0 );
		vDesc  = DELET (vDesc , iPos, 1 );
	ELSE;
		iClean = 1;
	ENDIF;
END;

scrumthing
Posts: 43
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

Re: TM1 Data insert into SQL

Post by scrumthing » Tue Jul 21, 2020 5:43 pm

Markuss wrote:
Tue Jul 21, 2020 12:20 pm
Found something that solves #2 issue - removes single quotation mark. I think that code below can be adapted to resolve #1 issue same time..
Yes, you could do that easily with the snippet. But I would stay with Wims suggestion to add a second alias or if you only need it for your export a simple text attribut would do as well. If you don't want to play around with the dimension process you could fix that probably with a rule on the elementattributes cube. But I don't know all your edge cases with the data. :-)
There is no OLAP database besides TM1!

Post Reply