Accessing a MySQL Database with Business Intelligence Development Studio

Well I tried the SQL Server Management Studio solution to connect to a MySQL database then lost my way wondering what was I trying to achieve?

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.
Scenario:
  • 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.

  1. Download and install the MySQL ODBC driver (at time of print v5.1)
    1. Visit http://dev.mysql.com/downloads/connector/
    2. Click on "Connector ODBC"
    3. Download a version suitable to your system
    4. Install it
  2. Setup the ODBC Connection
    1. Start > Programs > Administrative Tools > Data Sources (ODBC)
    2. Click on the System DSN tab
    3. Select "MySQL ODBC 5.1 Driver" > Finish
    4. Data Source Name (should be system name followed by Live/Test/Dev for reference)
    5. Description can be some long winded text that very few will ever use
    6. TCP/IP Server is the server name (without the DNS suffix) or the IP address of the server
    7. Port should be 3306 by default
    8. User is the mysql username
    9. Database is your selection and should auto-populate if the connection is successful
    10. TEST > OK
  3. Open the data connection
    1. Open Business Intelligence Development Studio (BIDS)
    2. Click on "View" > "Server Explorer"
    3. Right-click on "Data Connections" > select "Add Connection"
    4. Click on "Change..."
    5. Select "Microsoft ODBC Data Source" > OK
    6. Select the data source from the dropdown (the one you just created earlier should be listed)
    7. Enter credentials if necessary and click on "Test Connection"
    8. Expand the data connection you just made and expand "Tables" just to see if the connection is working properly.
  4. Create the Data Source
    1. Still within Business Intelligence Development Studio (BIDS)
    2. Right-click on "Shared Data Sources" in the Solution Explorer > select "Add New Data Source"
    3. Give the DataSource1 a more memorable name
    4. Click on the "Edit..." button
    5. Click on the "Change..." button
    6. Select "ODBC" and click on "OK"
    7. Select the system data source name
    8. Enter the login information
    9. Test the connection and click on OK
  5. Create the Report
    1. Still within Business Intelligence Development Studio (BIDS)
    2. Right-click on "Reports" in the Solution Explorer > select "Add New Report"
    3. Select the "Shared Data Source" you just created > Next
    4. Click on the "Query Builder" button
    5. Right-click on any empty space in the first frame > Select "Add Table..."
    6. Add and specify the query as you would with any SQL query. (to create the dataset)
    7. Select the Report Type (for just a table with a heading row at the top, select "Tabular") > Next
    8. Click on "Details" until everything is added. > Next
    9. I'm boring and old-school so my table style will be "Generic". > Next
    10. FINISH
That's all folks! Hopefully this will save you time. I spent a whole morning following someone else's complicated connection using SQL Server Management Studio and would not recommend anyone to go through the same rubbish.

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
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
  1.  An error occurred during client rendering. 
  2.       An error has occurred during report processing. 
  3.            Cannot impersonate user for data source 'BUKB_Test'
  4.                 For more information about this error navigate to the report server on the local server machine, or enable remote errors 
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.

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

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.