In this exercise, you will establish connectivity to a new Microsoft Access database, creating a Database Instance, Database Connection, and Database Login:
You will need Microsoft Access installed to create a new database.
- Create a new Microsoft Access database. Add a dummy table. Name both the database and the table Chapter7.
If you don't know how to create a MS Access database, simply right-click on the Data Explorer in the machine where it is installed | New | Microsoft Access Database.
- For this exercise, we will use an MS Access 2000 file with the *.mdb. If your machine has an MS Access driver for .accdb, you can use such a file. We will learn in the following step how to add a DSN and verify which MS Access drivers are installed in your machine. To change a *.accdb file to *.mdb, open the Microsoft Access database, click on File | Save As | Access 2000 Database(*.mdb):
- Create a System DSN: Open the 32-bit ODBC Administrator or ODBC Data Sources (32-bit) depending on your Windows OS version (you can use the Windows search option to locate it). The System DSN is the second tab:
In this exercise, we use 32-bit, since we utilize an MS Access database. Check with your Database Administrator on how to create the DSN for your specific database type, version, and environment.
- Click Add and locate Driver do Microsoft Access (*.mdb) or Microsoft Access Driver (*.mdb) | Finish.
You might also have a newer version of the MS Access driver, such as Microsoft Access Driver (*.mdb, *.accdb)
- For the Data Source Name, type Chapter7DSN | Database | Select… | Locate your Chapter7.mdb (or .accdb) file on your machine | OK. You should be able to see the DSN listed under the System DSN tab | OK to exit the ODBC Data Source Administrator.
- In MicroStrategy Developer, log in to the MicroStrategy Analytics Modules Project Source.
- Open Administration | Configuration Managers | Database Instances and right-click then choose New | Database Instance. The editor will open.
- For the Database instance name, type Chapter7DBI. In the Database connection type dropdown, locate your MS Access version; for this exercise, it is Microsoft Access 2007. Don't close the Database Instance editor yet:
- We will create a MicroStrategy Database Connection within the Database Instance. Click New in the Database Instance editor on the Database connection (default) section | The Database Connection editor will open | for Database connection name, type Chapter7DBC. Locate in the Local system ODBC data sources list the DSN you created in previous steps, Chapter7DSN, and select the radio button. Don't close the Database Connection editor yet.
- We will create a MicroStrategy Database Login within the Database Connection. Click New in the Database Connection editor, in the Database login name section | The Database Login editor will open | for Database login, type Chapter7DBL | Under Login ID type Administrator and Password: <blank> (for MS Access, it doesn't matter what you type unless you have specified login credentials) | OK:
- Before closing the Database Connection editor, make sure both the Chapter7DL Database Login and the Chapter7DSN Local system ODBC data source are selected:
By default, MicroStrategy will not select the radio button for the newly created object, so you need to double-check everything is correct before closing the editor.
- Click OK to close the Database Connection editor | You might see a warning message mentioning that, on a 3-Tier connection, the Intelligence Server must be restarted, while on a 2-Tier connection, the Projects need to be reloaded (connected and disconnected) for changes to take effect; click OK. Before closing the Database Instance editor, make sure the Chapter7DC Database Connection is selected:
- Click OK to close the Database Instance editor | You will see your newly created Database Instance in the Configuration Manager.
- We will now test the connection by creating a new Project and associating it with the Database Instance. Go to Schema | Create New Project | Create project ; name it Chapter7 and leave the defaults, click OK, and wait until the Project is created.
- Click on Select tables from Warehouse Catalog | Select Chapter7DBI in the dropdown | OK | The Warehouse Catalog should open and your test table Chapter7 should be there. You can right-click on the table and see its Table Structure to double-check it is the one you created Click Save and Close then OK to close the Project creation assistant.
- This finalizes the exercise.