I'm trying to follow a tutorial on SQL Server Analysis Services (SSAS) but I'm having trouble on deployment.
Summarized, what I'm doing is:
- Open SQL Server Data Tools (SSDT)
- Creating a new Multidimensional project
- Creating a Data Source from a SQL Server database (succesfully connects)
- Creating a Data Source View (can succesfully explore data)
If I try to deploy now, it works.
- Creating a Dimension
The deployment doesn't work.
It gives me multiple errors such as:
- Error in OLE DB or ODBC
- Error in OLAP processing engine: An error ocurred when dimension, with ID "Dim Location", Name "Dim Location" was being processed.
- Error in OLAP processing engine: An error ocurrer when attribute "Name" from dimension "Dim Location" from database "MultidimensionalProject02" was being processed.
If instead of creating a Dimension, I create a cube (with its dimensions), it gives the same errors (errors are related with the dimensions?).
The dimension I'm using is simple. Its name is DimLocation and has two columns, LocationKey (bigint) and Name (varchar).
The fact table when creating a cube is also simple. Its name is FactConsumption and has the columns, ConsumptionKey (bigint), LocationKey (bigint) and Value (int).
How can I create these elements and sucessfully deploy?
I'm using SQL Server 2016 Developer (I also have Express installed).
Best Answer
I don't speak Portuguese very well but it looks as if it's saying that it can't connect to the datasource.
Please verify if your Analysis services service account has access to the data source.
Keep in mind that, unless you specify the username and password the processing is done using the Analysis Services service account while when you browse data from within visual studio you are using your own account.
Please read through Set Impersonation Options which explains in detail how the different data source options work.