Processing a dimension using AMO does not work if the connection to the “DataSource” used is “SQ

Processing a dimension using AMO does not work if the connection to the “DataSource” used is “SQL Server Authentication”

amoDimension.Process(ProcessType.ProcessFull)

The following exception is returned:

- ex {"OLE DB error: OLE DB or ODBC error: Login failed for user ' MyUserId '.; 42000.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'MyDataBase', Name of ' MyDataBase'.

Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Products', Name of 'ProductDim' was being processed.

Errors in the OLAP storage engine: An error occurred while the 'Products' attribute of the 'ProductDim' dimension from the ' MyDataBase SQL SERVER' database was being processed.

"} System.Exception

The connection string to the “DataSource” is:

ConnectionString "Provider=SQLOLEDB.1;Data Source=MyServer\SQLSERVER2005;User ID=MyUserId;Initial Catalog= MyDataBase " String

ConnectionStringSecurity PasswordRemoved {1} Microsoft.AnalysisServices.ConnectionStringSecurity

Everything work properly is if I switch the connection type to “Windows Authentication”

Thanks,

Yones

Do you have a password in the connection string ? If you do, have you called dataSource.Update() or database.Update(UpdateOptions.ExpandFull) before the dimension.Process() ? Because the server will not return the passwords from connection strings to the client (AMO included). Instead, the server removes the password and sets the ConnectionStringSecurity property (of DataSource object) to PasswordRemoved. And the Update method will save the connection string with no password. The work-around is to avoid calling Update or to re-specify the password.

Adrian Dumitrascu

|||

Thank you very much Adrian.

It seems like the problem is happening because of database.Update(UpdateOptions.ExpandFull) but I need to called this method because I am adding new attributes to some dimensions.

Could you please tell me how can I re-specify the password?

Thanks,

Yones|||

> Could you please tell me how can I re-specify the password?

Set the DataSource.ConnectionString before the database.Update, specifying the password (and this is a problem because you don't want to put the password in clear text in the source code; is it acceptable to invoke UI to ask for the password ? or read it from a secure place). As a reminder, you need to worry about the password only if DataSource.ConnectionStringSecurity == PasswordRemoved.

Adrian Dumitrascu

|||

I am not sure if the password is removed just after calling dataSource.Update() or database.Update(UpdateOptions.ExpandFull) because I just did the following two lines of code and when I checked the password, it was already removed.

m_amoServer.Connect(i_strDataSource)

m_amoDataBase = m_amoServer.Databases.Item(i_strDatabase)

The result:

m_amoDataBase.DataSources (0)

ConnectionString"Provider=SQLOLEDB.1;Data Source=MyServer\SQLSERVER2005;User ID=MyUserId;Initial Catalog= MyDataBase "String

ConnectionStringSecurityPasswordRemoved {1}

Thanks Again,

Yones

|||

The server doesn't provide the password to AMO (for security - a server admin can setup the connection strings, while database admins can do other tasks without needing to know the passwords). The server keeps the password internally (so you can re-process objects), but it doesn't give it to clients. Because of this, the Update method would send back to the server an incomplete connection string, overwriting the existing full connection string.

Adrian Dumitrascu

Processing a dimension using AMO does not work if the connection to the “DataSource” used is

Processing a dimension using AMO does not work if the connection to the “DataSource” used is “SQL Server Authentication”

amoDimension.Process(ProcessType.ProcessFull)

The following exception is returned:

- ex {"OLE DB error: OLE DB or ODBC error: Login failed for user ' MyUserId '.; 42000.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'MyDataBase', Name of ' MyDataBase'.

Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Products', Name of 'ProductDim' was being processed.

Errors in the OLAP storage engine: An error occurred while the 'Products' attribute of the 'ProductDim' dimension from the ' MyDataBase SQL SERVER' database was being processed.

"} System.Exception

The connection string to the “DataSource” is:

ConnectionString "Provider=SQLOLEDB.1;Data Source=MyServer\SQLSERVER2005;User ID=MyUserId;Initial Catalog= MyDataBase " String

ConnectionStringSecurity PasswordRemoved {1} Microsoft.AnalysisServices.ConnectionStringSecurity

Everything work properly is if I switch the connection type to “Windows Authentication”

Thanks,

Yones

Do you have a password in the connection string ? If you do, have you called dataSource.Update() or database.Update(UpdateOptions.ExpandFull) before the dimension.Process() ? Because the server will not return the passwords from connection strings to the client (AMO included). Instead, the server removes the password and sets the ConnectionStringSecurity property (of DataSource object) to PasswordRemoved. And the Update method will save the connection string with no password. The work-around is to avoid calling Update or to re-specify the password.

Adrian Dumitrascu

|||

Thank you very much Adrian.

It seems like the problem is happening because of database.Update(UpdateOptions.ExpandFull) but I need to called this method because I am adding new attributes to some dimensions.

Could you please tell me how can I re-specify the password?

Thanks,

Yones|||

> Could you please tell me how can I re-specify the password?

Set the DataSource.ConnectionString before the database.Update, specifying the password (and this is a problem because you don't want to put the password in clear text in the source code; is it acceptable to invoke UI to ask for the password ? or read it from a secure place). As a reminder, you need to worry about the password only if DataSource.ConnectionStringSecurity == PasswordRemoved.

Adrian Dumitrascu

|||

I am not sure if the password is removed just after calling dataSource.Update() or database.Update(UpdateOptions.ExpandFull) because I just did the following two lines of code and when I checked the password, it was already removed.

m_amoServer.Connect(i_strDataSource)

m_amoDataBase = m_amoServer.Databases.Item(i_strDatabase)

The result:

m_amoDataBase.DataSources (0)

ConnectionString"Provider=SQLOLEDB.1;Data Source=MyServer\SQLSERVER2005;User ID=MyUserId;Initial Catalog= MyDataBase "String

ConnectionStringSecurityPasswordRemoved {1}

Thanks Again,

Yones

|||

The server doesn't provide the password to AMO (for security - a server admin can setup the connection strings, while database admins can do other tasks without needing to know the passwords). The server keeps the password internally (so you can re-process objects), but it doesn't give it to clients. Because of this, the Update method would send back to the server an incomplete connection string, overwriting the existing full connection string.

Adrian Dumitrascu

Process Add by AMO

Hi,

I want to write a programm which adds entries to an sql table and then process the dimension with processadd to only add this single row.

I have checked the samples and internet forums - but could not find out howto do..

anyhow I miss the link between a querybinding and the process command of the dimension...

I thought it would be something like...

Code Snippet

/* connect to lap server */

olapClient.Connect("DataSource=" + OLAPServerName);

/* get the Database */

Database olapDB = olapClient.Databases.GetByName(DatabaseName);

/* get the Dimension */

Dimension olapDim = olapDB.Dimensions.GetByName(DimensionName);

/* process the dimension and add the items i have created before */

olapDim.Process(ProcessType.ProcessAdd, new QueryBinding(DataSourceName, "select * from dbo.Slot where Slot='" + ResultID + "'"));

but this only raises an error ("

Errors in the metadata manager. The object reference is not valid. It does not match the structure of the metadata class hierarchy.")

How is this done correctly?

I want to use AMO to not write the xmla command statically - if there are further changes - but the dimension is build up to 100% of the single table dbo.Slot.

An example or a link would be helpfull.

HANNES

Nobody an idea?|||

Check this out. I think this guy has worked out the issues:

http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

|||

I have seen this. My Question is - how do I programmatically create the xmla command. It seems AMO always creates wrong commands - the command always looks like a Process ADD for a partition - even if we do it on the dimension object.

It you confirm that there is no other way I will go by manually write xmla. But the drawback is - if we change the dimension I have to manually change the XMLA - and parts of the code - and otherwise the would be much less maintenance.

Hannes

|||

When I review the blog, it appears he works out both techniques. In his first example, he shows how to use query bindings which works for the Partition version. Towards the bottom, he shows you that you must create a DSV in AMO to provide the data source for the Dimension verions. (The DSV is used instead of the query bindings.)

I've looked through the Melomed et al. (SAMS) and Lachev (PROLOGIKA) books and do not find any additional info that would be of help to you. In the Harinath and Quinn book (WROX), an XMLA example of the process add against a dimension is shown on page 443. It too shows a DSV being generated to hold the query for the process add on the dimension.

Good luck,
Bryan

Process Add by AMO

Hi,

I want to write a programm which adds entries to an sql table and then process the dimension with processadd to only add this single row.

I have checked the samples and internet forums - but could not find out howto do..

anyhow I miss the link between a querybinding and the process command of the dimension...

I thought it would be something like...

Code Snippet

/* connect to lap server */

olapClient.Connect("DataSource=" + OLAPServerName);

/* get the Database */

Database olapDB = olapClient.Databases.GetByName(DatabaseName);

/* get the Dimension */

Dimension olapDim = olapDB.Dimensions.GetByName(DimensionName);

/* process the dimension and add the items i have created before */

olapDim.Process(ProcessType.ProcessAdd, new QueryBinding(DataSourceName, "select * from dbo.Slot where Slot='" + ResultID + "'"));

but this only raises an error ("

Errors in the metadata manager. The object reference is not valid. It does not match the structure of the metadata class hierarchy.")

How is this done correctly?

I want to use AMO to not write the xmla command statically - if there are further changes - but the dimension is build up to 100% of the single table dbo.Slot.

An example or a link would be helpfull.

HANNES

Nobody an idea?|||

Check this out. I think this guy has worked out the issues:

http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

|||

I have seen this. My Question is - how do I programmatically create the xmla command. It seems AMO always creates wrong commands - the command always looks like a Process ADD for a partition - even if we do it on the dimension object.

It you confirm that there is no other way I will go by manually write xmla. But the drawback is - if we change the dimension I have to manually change the XMLA - and parts of the code - and otherwise the would be much less maintenance.

Hannes

|||

When I review the blog, it appears he works out both techniques. In his first example, he shows how to use query bindings which works for the Partition version. Towards the bottom, he shows you that you must create a DSV in AMO to provide the data source for the Dimension verions. (The DSV is used instead of the query bindings.)

I've looked through the Melomed et al. (SAMS) and Lachev (PROLOGIKA) books and do not find any additional info that would be of help to you. In the Harinath and Quinn book (WROX), an XMLA example of the process add against a dimension is shown on page 443. It too shows a DSV being generated to hold the query for the process add on the dimension.

Good luck,
Bryan

Process Add by AMO

Hi,

I want to write a programm which adds entries to an sql table and then process the dimension with processadd to only add this single row.

I have checked the samples and internet forums - but could not find out howto do..

anyhow I miss the link between a querybinding and the process command of the dimension...

I thought it would be something like...

Code Snippet

/* connect to lap server */

olapClient.Connect("DataSource=" + OLAPServerName);

/* get the Database */

Database olapDB = olapClient.Databases.GetByName(DatabaseName);

/* get the Dimension */

Dimension olapDim = olapDB.Dimensions.GetByName(DimensionName);

/* process the dimension and add the items i have created before */

olapDim.Process(ProcessType.ProcessAdd, new QueryBinding(DataSourceName, "select * from dbo.Slot where Slot='" + ResultID + "'"));

but this only raises an error ("

Errors in the metadata manager. The object reference is not valid. It does not match the structure of the metadata class hierarchy.")

How is this done correctly?

I want to use AMO to not write the xmla command statically - if there are further changes - but the dimension is build up to 100% of the single table dbo.Slot.

An example or a link would be helpfull.

HANNES

Nobody an idea?|||

Check this out. I think this guy has worked out the issues:

http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

|||

I have seen this. My Question is - how do I programmatically create the xmla command. It seems AMO always creates wrong commands - the command always looks like a Process ADD for a partition - even if we do it on the dimension object.

It you confirm that there is no other way I will go by manually write xmla. But the drawback is - if we change the dimension I have to manually change the XMLA - and parts of the code - and otherwise the would be much less maintenance.

Hannes

|||

When I review the blog, it appears he works out both techniques. In his first example, he shows how to use query bindings which works for the Partition version. Towards the bottom, he shows you that you must create a DSV in AMO to provide the data source for the Dimension verions. (The DSV is used instead of the query bindings.)

I've looked through the Melomed et al. (SAMS) and Lachev (PROLOGIKA) books and do not find any additional info that would be of help to you. In the Harinath and Quinn book (WROX), an XMLA example of the process add against a dimension is shown on page 443. It too shows a DSV being generated to hold the query for the process add on the dimension.

Good luck,
Bryan