Error processing ssas dimension

Question

Hi,

I have ssas cube with one dimension pointing to SQL server. When I try to process this dim in SSDT 2015, I am constantly getting below error.

Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: [500] Could not execute the specified command: Input string was not in a correct format.;Input string was not in a correct format.
Input string was not in a correct format..
SQL queries
SELECT
DISTINCT
[dbo_Users].[dbo_UsersLoginID0_0] AS [dbo_UsersLoginID0_0]
FROM
OPENROWSET
(
N'SQLNCLI11.1',
N'',
N'
SELECT [LoginID] AS [dbo_UsersLoginID0_0]
FROM
(
SELECT VendorUserID, LoginID
FROM dbo.Users_Macys
)
AS [Users_Macys] '
)
AS [dbo_Users]

Answer 1

Hi ,

Have you exam the the query in your SSMS enviroment to see if the query could run?

I think the query has missed the second arguement for openrowset function ,that should be { 'datasource' ; 'user_id' ; 'password' | 'provider_string' } ? You could check and exam the OPENROWSET fuction at MS Doc: OPENROWSET (Transact-SQL)

Regards,
Lukas

Answer 2

Hi,

I have simply build dimension from view in data source view. But when I process the dimension, I am getting this error.

Actually, the openrowset SQL is embedded in the error. I haven't build it manually.

Answer 3

It looks like an error raised by the database engine. What datasource are you using ? Was other dimension processed correctly? Could this table be viewed in database engine ?

Answer 4

The issue was that if there are 2 data sources (DS) in ssas, one of the DS is made primary. Now, the data source view (DSV) can have tables from both DS but later the dimensions cant be processed because it tries to set up linked server queries between 2 DS.
So, I had to have single DS, single DSV and then process dim. Then it does not need to have linked server queries.