Database credentials in TI

[escape18in]

Hi All,

i have requirement like , i don’t want to pass database information using datasource type ODBC (i.e. Providing DS name,User ID,password in TI). instead of these information should be kept somewhere from any file or any lookup cube, my data load TI then read those parameters from that file or cube, Process the query and load the data in Cube. This will solve my 2 purpose.

1. AS SOP of my project we cannot mention Database credential in a TI file.
2. If Credential is changes my chores will not lock my database, as well as I need not to change my credential by opening each TI.

I know any TM1 guru has already implemented this so their help will be much appreciated.

[tomok]

If you don't want to store the password in the TI you can just leave it blank and assign the password at run time by setting the variable DatasourcePassword to whatever value you want. You could accept the password as a parameter and then assign it like this:

DatasourcePassword = pPassword;

or you could get the password from a cube like this:

DatasourcePassword = CellGetS('Name of cube', Dim1, Dim2,....);

[jim wood]

We use a lookup cube for data source, user name and password as Tomok suggested. You just need to make sure that only Admins have access to the cube,

Jim.

[escape18in]

And how will my variables be created ,just like they are created in datasource tab once i write SQL query?

[BariAbdul]

Please go through Chapter4 of Turbo Integrator guide"Importing from ODBC Source".Thanks

[escape18in]

Please go through Chapter4 of Turbo Integrator guide"Importing from ODBC Source".Thanks

the chapter you are mentioing tells us to identify variabe in a normal way, please make a note the i am not passing DB credential in TI, it will be passed when TI will be run and via a look up cube , after that SQL query process and i got the data, now how to map that data in variables to perform further is my question.

[Wim Gielis]

For example:

Code: Select all

DataSourceType = 'ODBC';
DatasourceNameForServer = 'DWH_Prod';
DatasourceUserName = 'mylovelyusername';
DatasourcePassword = CellGetS('Name of admin cube', Dim1, Dim2,....);
DatasourceQuery = 'SELECT * FROM DWH_Actuals';

[lotsaram]

And how will my variables be created ,just like they are created in datasource tab once i write SQL query?

You.
Fingers.
Typing on keyboard.

[escape18in]

For example:

Code: Select all

DataSourceType = 'ODBC';
DatasourceNameForServer = 'DWH_Prod';
DatasourceUserName = 'mylovelyusername';
DatasourcePassword = CellGetS('Name of admin cube', Dim1, Dim2,....);
DatasourceQuery = 'SELECT * FROM DWH_Actuals';

Thanks Wim for your help, it works, i have one more query if you have answer.. can we encrypt password in Admin cube as well and it is decrypted when we call it in our TI, just like in tm1runti command we have a utillity to encrypt passwords...

[David Usherwood]

Don't believe there is a built in encryption feature in the TI toolset. But with 10.2.2 you should look at writing your own Java function to do this - there's bound to be a Java encryption library lying around somewhere.

[lotsaram]

I thought I remembered rmackenzie posting an encryption/decryption algorithm for a TI process a little while back but I can't find it.

----------
Edit: Found it here

[escape18in]

Now Another Strange Error i am getting in following line

DatasourceQuery = 'SELECT * FROM DB_Table';

this works fine when i use a single table and column from this table, but when i tried with complex query with some joins and functions like To_char, it starts failing, i tried with expand function, breaking query in multiple sql statement and then used like
ODBCOutput(DatasourceNameForServer,sql1,sql2,sql3);, But nothing works, Complex query run fine in Database as well as Data Source Tab in TM1, Any idea TM1 Gurus where i am making mistake?

[David Usherwood]

TM1 does nothing to your SQL apart from passing it to the database server. Output what you have in DataSourceQuery to a file, pick it up and paste it into your query tool of choice. You'll likely find a syntax issue.

[tomok]

Complex query run fine in Database as well as Data Source Tab in TM1, Any idea TM1 Gurus where i am making mistake?

If your SQL works in the data source tab then the SQL string you are creating with your code does not match what you had in the Data Source tab when you tested it. As mentioned, output the result of your string creation to see where the problem is. Most of the time it's due to a misplaced apostrophe or two.

[escape18in]

Thank All , for your generous help, i was able to achieve what i wanted , the last mistake I was doing is because of SQL beautifier, As TM1 engine was not able to understand some statement and merging some text like select col1from... etc. , I removed all the unnecessary spaces in my sql statements and it worked.

Now for a last requirement I need suggestion from gurus, My client want to develop a front end page by which an admin can input database credentials in form of password type (********), I tried with view and web sheets but it didn’t work out, I haven’t tried TM1 API way and try to avoid it as we are not using it in our application is there any alternate way??

[tomok]

Now for a last requirement I need suggestion from gurus, My client want to develop a front end page by which an admin can input database credentials in form of password type (********), I tried with view and web sheets but it didn’t work out, I haven’t tried TM1 API way and try to avoid it as we are not using it in our application is there any alternate way??

No. There is no concept of a password field in TM1 whereby what you type in is masked. I bet if your customer knew how much money they were wasting with this whole endeavor they would be furious with you. Sometimes you just have to man up and tell them when things are just not possible with certain tools. They may not like it initially but they'll grow to respect you more as time goes by.

[escape18in]

Now for a last requirement I need suggestion from gurus, My client want to develop a front end page by which an admin can input database credentials in form of password type (********), I tried with view and web sheets but it didn’t work out, I haven’t tried TM1 API way and try to avoid it as we are not using it in our application is there any alternate way??

No. There is no concept of a password field in TM1 whereby what you type in is masked. I bet if your customer knew how much money they were wasting with this whole endeavor they would be furious with you. Sometimes you just have to man up and tell them when things are just not possible with certain tools. They may not like it initially but they'll grow to respect you more as time goes by.

As per client overall exercise is to reduce TM1 admin cost license , they want to keep it minimum ,we are in 24*5 environment and platform team works on shifts so the main focus is to handle some task using front end or bypassing platform support team. But thanks for your suggestion i will take it forward accordingly.

[lotsaram]

No you can't create a "password mask" formatted field. However if you are using a websheet you can use Excel cell formatting custom type ;;;
This will render the cell blank. The cell can still be copied to and from and contains the value, just the format is blank. It doesn't meet the requirement of ******* but like Tomok said sometimes near enough has to be good enough and you have to man up and tell the customer that.

You can also achieve the same in cube viewer using c:;;; as the element format for a measure "password".

[kangkc]

Few years ago we did a project of interfacing TM1 with MSSQL and the requirement is authentication with SQL must be Windows integrated.
Actually it's pretty straight forward as TI will use the TM1 started service security context as the authentication with the MSSQL. As long as TM1 service is running under a domain account, and this account has the proper permission to access the SQL table/views/etc. It should work.