Now that we have two databases, we need to tell MicroStrategy how to find and how to connect to them. Being in a Windows world, all the connections are done through ODBC, 32-bit ODBC to be correct.
We will create the two DSN:
- Click on Start | Administrative Tools | Data Sources (ODBC) and select the System DSN tab.
- You will find some DSNs already created for you by the setup; we need to create two more (yes, you're right: one for the metadata and one for the data warehouse).
- Click on the Add… button, scroll down the list to select SQL Server Native Client 11.0 and click on Finish.
- In the Create a New Data Source to SQL Server window, type these values:
- Name:
metadata - Server:
(localdb)v11.0
- Name:
- Click on Next and then on Next again.
- Click the checkbox labeled Change the default database to and in the drop-down list underneath, select metadata. Leave all the remaining default values and click on Next and on Finish.
- Click on Test Data Source… and look at the message: if it says TESTS COMPLETED SUCCESSFULLY!, you're good to go. Click on OK and then on OK again.
- Repeat all the steps to create another DSN to the data warehouse, this time with the following values:
- Name:
datawarehouse - Server:
(localdb)v11.0 - Change the default database to: AdventureWorksDW2008R2
- Name:
Every interaction with the relational DBMS is done through ODBC. MicroStrategy ships with a list of out-of-the-box drivers for the most common databases. Nevertheless, you may use other ODBC drivers if so you wish, provided that they work in 32 bits.
In our recipes, we will connect to a SQL Server instance, so we need to use the Microsoft provided Native ODBC. If, for example, you use Oracle in your BI environment, you will find the provided Oracle Wire driver being very convenient and light-weight. The company behind the shipped drivers is DataDirect.