I am failing to create a cube in SSAS from a SDF file (SQL CE) as a datasource. I am assuming it's possible because SQL CE supports OLEDB interface. I am doing this in C# with AMO library.
The error I'm facing during Process of dimensions is the following :
Microsoft.AnalysisServices.OperationException
HResult=0x80131500
Message=Errors In The High-Level Relational Engine. SqlCeCommand.CommandTimeout does not support non-zero values.. The exception was raised by the IDbCommand interface.
Errors in the OLAP storage engine : an error occured while processing « dimension » with ID « dimension_test » and name « dimension_test ».
...
Source=Microsoft.AnalysisServices.Core
StackTrace:
at Microsoft.AnalysisServices.Core.AnalysisServicesClient.SendExecuteAndReadResponse(ImpactDetailCollection impacts, Boolean expectEmptyResults, Boolean throwIfError)
at Microsoft.AnalysisServices.Core.AnalysisServicesClient.Process(IMajorObject obj, ProcessType type, IBinding source, ErrorConfiguration errorConfig, WriteBackTableCreation writebackOption, ImpactDetailCollection impact, XmlaWarningCollection warnings, JaXmlSerializer serializer)
at Microsoft.AnalysisServices.Core.Server.Process(IMajorObject obj, ProcessType processType, IBinding source, ErrorConfiguration errorConfig, WriteBackTableCreation writebackOption, XmlaWarningCollection warnings, ImpactDetailCollection impactResult, Boolean analyzeImpactOnly)
at Microsoft.AnalysisServices.Core.Server.SendProcess(IMajorObject obj, ProcessType processType, IBinding source, ErrorConfiguration errorConfig, WriteBackTableCreation writebackOption, XmlaWarningCollection warnings, ImpactDetailCollection impactResult, Boolean analyzeImpactOnly)
at Microsoft.AnalysisServices.ProcessableMajorObject.Process(ProcessType processType, ErrorConfiguration errorConfiguration, XmlaWarningCollection warnings)
at Microsoft.AnalysisServices.ProcessableMajorObject.Process(ProcessType processType)
at CreateCubeAMO.Program.CreateAndProcessDimensions(Database database, String dataSourceViewName) in C:\Users\...\source\repos\CreateCube\CreateCubeAMO\Program.cs:line 447
The datasource is setup as below :
DataSource dataSource = database.DataSources.Add(dataSourceName, dataSourceName);
dataSource.ConnectionString = @"Data Source=file.sdf";
dataSource.ManagedProvider = "System.Data.SqlServerCe.4.0"
My understanding of the issue is : by default SqlCommand.CommandTimeout is 30 seconds, however SqlCeCommand only accepts 0 (for infinity) as a commandtimeout, so the provider of SQL CE does not like the sql command created by default.
The solution would be to set the timeout on the command created by the datasource of the cube, however DataSource only allows to override timeout of the connection.
So my questions :
1 - is what I'm trying to do possible ?
2 - is there another way to avoid this issue ? either another provider or perhaps another way to configure this timeout
I've tried setting up global Server property "ExternalCommandTimeout" but it didn't work.
I've tried using visual studio and SSMS, same result.
I've tried using the native provider (with connectionstring as below) but I get an internal error :
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID
The later looks like an impersonation error, but I've tried default impersonate, service account and personal account with and without password, nothing. Also the SDF file is correct since I can open an OleDbConnection with code below and send queries.
OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=file.sdf");
Also the whole process works if I use a normal MS SQL Server as datasource, but I want to use the *.sdf.