The Gist: When working with Microsoft SQL Server Analysis Services Projects, if you decide to take the “Top Down” development approach, you should create an empty database for use with the “Generate Relational Schema” wizard prior to attempting to complete the wizard steps.
Having worked with Entity Framework Code First and other database scaffolding technologies, and now database migrations, I have grown comfortable with the idea of code generated databases and database objects. Well, while completing a PluralSight tutorial on developing Analysis Services projects using BIDS (Business Intelligence Development Studio) , I had successfully followed the steps of creating the datasource connection, creating the cube, and defining the dimension objects. However, when it was time to generate the relational schema based on the design completed in the previous steps, I was presented with a connection error.
I don’t know why, but when presented with an error for the first time, our immediate response can at times be to the extreme. The only part of the error message that initially stood out to me was…”Error de início de sesión.” or “Connection Error”. So I launch the SQL Server Configuration Administrator and started to troubleshoot the database connection settings, protocols, and services. I then launched SQL Server Management Studio and began to look at permissions for the login being used. When the results of these efforts were the same, I decided that it was time to take a deeper look at the error message (one of these fine days I will do this in the first step). Looking in the Error Panel I saw an even more vague message:
“Invalid Permissions”…is the gist of it. But I decided to stop and think. The only missing element here is the actual database. Not to mention that the trainer’s actual words were…”using the name of a database that I had already created”…so without wasting anymore time, I created the database in SQL Management Studio and went back to generate the schema in BIDS.
Flipping through the following wizard steps, this time I did not receive the previously mentioned error.
and of course the final result being, that the data source view had been successfully created.
This purpose of the post was to illustrate how easily simple mistakes can occur and how easily we can overlook the the simplest solution by over analyzing the error. Happy coding!