I've written this article because this is how I connected to a MySQL database from within the Business Intelligence Development Studio (BIDS) IDE from Microsoft.
Required:
- Windows XP (ODBC Data Source Administrator)
- MS Business Intelligence Development Studio 2008
- Test/Sample MySQL Database to connect to (server and username + password).
- Admin Access to the reporting server if you plan on deploying the report to it.
- We want to report on a MySQL database
- MySQL login information is a user who exists as a MySQL user and a server user.
- Reporting server is remote as well as the MySQL database.
- BIDS is on the client machine.
- Download and install the MySQL ODBC driver (at time of print v5.1)
- Visit http://dev.mysql.com/downloads/connector/
- Click on "Connector ODBC"
- Download a version suitable to your system
- Install it
- Setup the ODBC Connection
- Start > Programs > Administrative Tools > Data Sources (ODBC)
- Click on the System DSN tab
- Select "MySQL ODBC 5.1 Driver" > Finish
- Data Source Name (should be system name followed by Live/Test/Dev for reference)
- Description can be some long winded text that very few will ever use
- TCP/IP Server is the server name (without the DNS suffix) or the IP address of the server
- Port should be 3306 by default
- User is the mysql username
- Database is your selection and should auto-populate if the connection is successful
- TEST > OK
- Open the data connection
- Open Business Intelligence Development Studio (BIDS)
- Click on "View" > "Server Explorer"
- Right-click on "Data Connections" > select "Add Connection"
- Click on "Change..."
- Select "Microsoft ODBC Data Source" > OK
- Select the data source from the dropdown (the one you just created earlier should be listed)
- Enter credentials if necessary and click on "Test Connection"
- Expand the data connection you just made and expand "Tables" just to see if the connection is working properly.
- Create the Data Source
- Still within Business Intelligence Development Studio (BIDS)
- Right-click on "Shared Data Sources" in the Solution Explorer > select "Add New Data Source"
- Give the DataSource1 a more memorable name
- Click on the "Edit..." button
- Click on the "Change..." button
- Select "ODBC" and click on "OK"
- Select the system data source name
- Enter the login information
- Test the connection and click on OK
- Create the Report
- Still within Business Intelligence Development Studio (BIDS)
- Right-click on "Reports" in the Solution Explorer > select "Add New Report"
- Select the "Shared Data Source" you just created > Next
- Click on the "Query Builder" button
- Right-click on any empty space in the first frame > Select "Add Table..."
- Add and specify the query as you would with any SQL query. (to create the dataset)
- Select the Report Type (for just a table with a heading row at the top, select "Tabular") > Next
- Click on "Details" until everything is added. > Next
- I'm boring and old-school so my table style will be "Generic". > Next
- FINISH
I just took a step back and thought this through and this is what I came up with!
Additional
The above will all work if you're working on a local report within BIDS connecting to remote servers. If you want the report to be viewable on a specified Reporting Server than you'll need to deploy the report.
If you get the following error (like I did) when you view the deployed report on the Reporting Server:
copyraw
I fixed this by repeating stages 1 and 2 (above) on the Reporting Server. Ensure you name them the same thing as you did in the report.An error occurred during client rendering. An error has occurred during report processing. Cannot impersonate user for data source 'BUKB_Test'. For more information about this error navigate to the report server on the local server machine, or enable remote errors
- An error occurred during client rendering.
- An error has occurred during report processing.
- Cannot impersonate user for data source 'BUKB_Test'.
- For more information about this error navigate to the report server on the local server machine, or enable remote errors
I am looking at ways of doing this without logging into the reporting server or making any changes to it. If anyone knows of a way, please feel free to comment!
Category: MySQL :: Article: 312