Replace 1: On the time of penning this weblog put up (Aug 2015) Energy BI Service referred to as Energy BI Internet. I hope it doesn’t make any confusions.

Replace 2: MySQL information supply is out there in “On-premises Knowledge Gateway – Enterprise Mode” as properly. So in case you are setting this up for an organisation, then “Private Mode” (AKA Energy BI Private Gateway) wouldn’t be appropriate. Once I wrote this weblog put up solely “Energy BI Private Gateway” was accessible.

On this put up I clarify the best way to use MySQL and Energy BI. This put up covers the next areas:

• Get information from MySQL
• Schedule refresh on-premises MySQL from energy BI internet app

To start with I’d like to say that on this put up I take advantage of AdventureWorksDW which is imported into MySQL. If you wish to accomplish that you should use “Migration Wizard” from “Database” menu on MySQL Workbench.

I’m not going to elucidate the migration course of because it’s out of scope.

MySQL is without doubt one of the world’s hottest relational database administration methods (RDBMS) broadly utilized by the business. It’s open supply, works with many various system platforms together with Microsoft Home windows and Linux. So it’s value to take a look at it and see the way it works with Energy BI.

Fortunately Microsoft offered the built-in connector in Energy BI Desktop. That is the way it works all collectively:

I’d wish to say that it’s not essential to create studies in Energy BI Desktop. You may get information from a MySQL database then publish it to the Energy BI cloud then setup a schedule information refresh within the Energy BI internet app. Then you may create your studies and dashboards on the cloud and share them along with your colleagues very simply.

As we mentioned earlier than in considered one of my earlier posts you may schedule an information refresh on a number of totally different information sources together with MySQL. Loading information from MySQL is 99% just like what we’ve completed earlier than on Knowledge Visualisation with Energy BI Desktop. As I acknowledged earlier, I’m not going to cowl all points of information visualisations on this put up once more, so in case you are on the lookout for a element dialogue about the best way to create studies and visualise your information with Energy BI Desktop you will discover it right here.

• Choose MySQL Database then click on join

• Enter the server and database names then click on OK

• Click on “Database” the n go username and password then click on Join

• For this pattern I chosen the next tables:
  1. 1. FactResellerSales
     2. DimDate
     3. DimProduct
     4. DimProductCategory
     5. DimProductSubCategory
     6. DimSalesTerritory
     7. DimGeography
• Click on Load

To this point we received information from MySQL. In actual world we’ll must tidy up the tables and fields to make the report extra readable and extra person pleasant. I depart it to you.

Energy BI Desktop robotically detects desk relationships after getting information from MySQL. To see the relationships simply click on on the “Relationships” view.

You may also modify relationships by clicking on the “Handle Relationships” button from the ribbon.

Now it’s time to create some studies with Energy BI Desktop.

OK, as you may see I created “Complete Gross sales Quantity”, “Gross sales by Product Class” and “Gross sales by Territory” studies.

I additionally efficiently printed the studies to my Energy BI cloud account.

I encourage you to take a look at this put up which expresses rather more particulars about relationship administration, creating and publishing studies and rather more.

To have the ability to setup a schedule information refresh you must set up “Energy BI Private Gateway” on a machine in your community. For extra details about “Energy BI Private Gateway” you may see “Refreshing On-Prem SQL Server Database On Energy BI Utilizing Energy Bi Private Gateway”. I additionally recommend you take a look at “Energy BI Private Gateway, 5 Issues You Should Know” as properly.

To setup a schedule information refresh comply with the steps under:

• Login to your Energy BI account on Energy BI web site
• Discover your MySQL from Datasets
• Click on on “Open Menu” ellipsis
• 
• Click on “Schedule Refresh”
• 
• Develop “Schedule Refresh”
• Click on “Hold your information up-to-date”
• Choose “Refresh frequency” as desired
• Select your time zone
• Setup the time
• You may also add extra refresh instances by clicking on “Add one other time”
• Tick “Ship refresh failure notification e mail to me” if essential then click on “Apply”

All completed!