Tm1 user with their group list transfer to sql

[dharav]

Hello All

I want to transfer the name of the user with their group in to sql. I have crated TI Using the control cube(}clientgroups) view 'abc'.


Please advise me the best suitable way with its code or procedure
> I export the data in to .cma file but dont know further proceedings.
Could you please let me know the code or procedure to load data in to sql from tm1 cubes?(exporting data from tm1 cubes to sql)

My code:
First create a view of }client groups called 'audit'
I create TI from View 'audit' and assign all variable and advanced tab look like following
Prolog:
Odbcopen('','User Name', 'password');

Data:

odbcoutput ('Name of server', Expand( 'INSERT INTO [dbTM1].[dbo].[Audit] ([Directory] ,[USER_NAME], [USER_GROUP] ) VALUES ( "%V1%", "%V2%","%V3%")'));

Epilog:
odbcclose('Name of server');

Thanks

[rmackenzie]

Are you asking if there is a problem with the code you posted?

[dharav]

Hi

I wrote that but couldn't able to get success. could you plz let me know what and where should i make improvement?

Thanks

Dharav

[Wim Gielis]

Hi

I wrote that but couldn't able to get success. could you plz let me know what and where should i make improvement?

Thanks

Dharav

It might help if you indicate if you get an error (if any), if you get some of the output or nothing, ... What is going on/going wrong and where do you need assistance?

[dharav]

I have utilised the view of control cube } client groups

I had created TI utilising Audit view ( from control cube } client groups). Variables V1, V2, V3 all are string and other selected for all in content.

Objective: To pull the data from TM1 cubes and insert in to sql

Prolog:

ODBCOpen('SQLserver', 'cognos', 'password' );

Data:

ODBCOutput ('SQLserver', Expand('INSERT INTO [dbtm1.dbo.Audit] [Directory ,USER_NAME,USER_GROUP) VALUES ( "%V1%", "%V2%","%V3%" )'));


Epilog:
ODBCClose('SQLserver');


Error:

"Admin","ADMIN","ADMIN",Data Source line (1) Error: Data procedure line (6): Error executing SQL query: "INSERT INTO [dbtm1.dbo.Audit] [Directory ,USER_NAME,USER_GROUP) VALUES ( "Admin", "ADMIN","ADMIN" )"


So could you let me know, what should i have to do to execute it?

Thanks

Dharav

[Wim Gielis]

Does the SQL code work in client in your relational database?
(So outside of TM1, does the generated query work as such ?)
You probably need ' instead of "

Please do not email me privately, use the forum here.

[dharav]

Yes it does.

TI in TM1
ODBCOutput ('PAR-DEV-TRISQL9', Expand('INSERT INTO dbtm1.dbo.Audit (Directory ,USER_NAME,USER_GROUP) VALUES ( "%V1%", "%V2%","%V3%" )'));

IN SQL database (When i entered following querey in sql, it works good)

INSERT INTO dbtm1.dbo.Audit (Directory ,USER_NAME,USER_GROUP) VALUES ( 'admin', 'admin','admin' )

instead of "%v1%", I put 'admin' in sql. Insert into is without inverted comma.

[dharav]

I would like to add further

The dimension in }client groups are

}clients
}groups
value
The sql database has three column
1st Datadirectory
2nd user_name
3rd group_name

my TI query is


ODBCOutput ( 'PAR-DEV-TRISQL9', Expand( 'INSERT INTO dbtm1.dbo.Audit ( directory, USER_NAME, USER_GROUP) VALUES ( "%V1%","%V2%" ,"%V3%")'));

As i am getting the error: error on executing sql query as i mentioned in my above post. So i tried to copy my code in to sql without expand and put individual values and it worked.
My statement in SQL:

INSERT INTO dbtm1.dbo.Audit ( directory, USER_NAME, USER_GROUP) VALUES ('admin','admin' ,'admin')

Any clue, where am i getting out of the track?

Thanks

[tomok]

So you are saying that this SQL string does not work:

Code: Select all

INSERT INTO [dbtm1.dbo.Audit] [Directory ,USER_NAME,USER_GROUP) VALUES ( "Admin", "ADMIN","ADMIN" )

While this one does:

Code: Select all

INSERT INTO dbtm1.dbo.Audit ( directory, USER_NAME, USER_GROUP) VALUES ('admin','admin' ,'admin')

Do you see the difference?

[Wim Gielis]

In addition, dharav, do you now understand why I wrote:

You probably need ' instead of "

in my last reply. It is always good to explore the help that others give you.

[dharav]

Hi All

Thank You so much to all for your prompt response.

I have resolved the problem. Wim, as u explained i needed two ' instead of ''. It worked and objective has been achieved,

Dharav