Using MySQL database as a Datasource

How to connect to MySQL database from PowerPivot

One of the supported data sources that you can use to connect to your workbooks and create PowerPivot reports is MySQL database. But before creating such connection you need to make sure that the following requirements are fulfilled:

  • Make sure your MySQL server is binded on public IP address and accept traffic from PlexHosted`s SharePoint app server where your site is deployed, on default port 3306 (port 3306 used for demonstration purposes in this case).
  • You have a user that have read only permissions to required databases (root user is used for demonstration purposes in this case) GRANT ALL ON employees.* TO root@11.22.33.44′ IDENTIFIED BY ‘verysecurepassword’; or GRANT ALL ON employees.* TO root@’%’ IDENTIFIED BY ‘verysecurepassword’; (if you have a firewall)
  • Your Desktop machines which are building excel reports must have permissions to connect to your MySQL server using public IP address.
  • The MySQL Connector/ODBC has been installed on the machine from which you are going to connect to MySQL. You can download and install it from [here].
Creating a connection to MySQL database
  1. Open the Excel workbook you would like to connect to the database and go to the PowerPivot tab. In the PowerPivot tab click Manage.
    How_to_connect_to_MySQL_database_from_PowerPivot-1
  2. When the PowerPivot for Excel window opened, click on From Other Sources button in the Get External Data section.
    How_to_connect_to_MySQL_database_from_PowerPivot-2
  3. Choose Others (OLEDB/ODBC) option.
    How_to_connect_to_MySQL_database_from_PowerPivot-3
  4. Type the name for your connection and enter the connection sting using the pattern below:
    Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=”DRIVER={MySQL ODBC 5.3 ANSI Driver};UID=readonlyuser;Server=11.22.33.44;Database=employees;Password=verysecurepassword;Option=3DRIVER={MySQL ODBC 5.3 ANSI Driver}”;Server=11.22.33.44;Database=employees;Option=3;”;
    Where:
    UID=<user with permissions to connect to your database>
    Password=<your user password>
    Server=<your MySQL server public IP address>
    Database=<your database name.
    After entering the connection string click on Test Connection button.
    How_to_connect_to_MySQL_database_from_PowerPivot-4 
  5. Click OK in the connection status window.
    How_to_connect_to_MySQL_database_from_PowerPivot-5
  6. Click Next.
    How_to_connect_to_MySQL_database_from_PowerPivot-6
  7. In the Table Import Wizard choose the option Write a query that will specify the data to import and click Next.
    How_to_connect_to_MySQL_database_from_PowerPivot-7 
  8. Enter the query to import the required data from the database and click Validate. Once you query is validated click Finish.
    How_to_connect_to_MySQL_database_from_PowerPivot-8
  9. The Table Import Wizard will start importing data from your database to the PowerPivot model. Once the status of the wizard changes to Success click Close.
    How_to_connect_to_MySQL_database_from_PowerPivot-9
     
  10. You can see the data imported to the model and can start creating your PowerPivot reports by clicking on the PowerPivot table button.
    How_to_connect_to_MySQL_database_from_PowerPivot-10
LinkedIn
Facebook
Facebook
Google+
Google+
http://blog.plexhosted.com/2015/10/04/using-mysql-database-as-a-datasource/

Leave a Reply

Your email address will not be published. Required fields are marked *