Data Source definition

The template will produce the framework for a Multidimensional project, and on the right hand side of the Data Tools window, you should now see the Solution Explorer with default folders that are suitable for this kind of project.

The first thing we need to do is to tell upon which Data Source this project should be defined. As mentioned earlier, the Stage database is where we copy data from our source system(s) and where we modify and fine tune these data for analytical purposes. Therefore, while the NAV database is the data source for our Stage database, the Stage database is the data source for our Multidimensional database.

Right-click the Data Sources folder to start defining a Data Source for the Multidimensional project.

Since this is our first Data Source definition, we will have to create it as a new connection.

Click the “New” button to start creating a new connection.

Clicking the New button will open the Connection Manager dialogue box.

The new connection must point to the Stage database on our SQL server. To access databases on our SQL server, you should select Native OLE DB\SQL Server Native Client 11.0.

Note: From January 2024, Microsoft recommends using the Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server or the latest Microsoft ODBC Driver for SQL Server. See this article: Installing SQL Server Native Client.

Type in Localhost as server name, use Windows authentication and select the Stage database from the database list.

In the next step of the Data Source Wizard we can decide which credentials will be used when the Analysis Services project want to read data from the Stage database.

At this point let us just assume that the credentials of the current user will be sufficient. After all, we were able to open the Stage database earlier on, weren’t we?

Finally, complete the wizard by naming the new data source. The suggested name Stage will do fine as it clearly tells that this data source is pointing to the Stage database.