none
Suggestions on improving our PowerBI workflow. RRS feed

  • Question

  • We are a small business starting to use PowerBI to put in place some basic business intelligence reporting throughout the company. We have used Power Pivot for over a year and have more recently moved onto using Power BI Desktop.

    Our current process is cumbersome and has the following elements:

    1.        MySQL Database – the  raw data
    2.        Transform Spreadsheet - excel spreadsheets into which data is first imported (using SQL queries) and cleaned using Power Query
    3.        Power BI Desktop – where we build relationships, measures, reports, visuals etc
    4.        PowerBI.com – where we publish our reports
    5.        PowerBI Personal Gateway – to faciliate data transfer between PowerBI Desktop and .com

    Based on this workflow updating the data displayed on PowerBI.com involves the following steps:

    1.        Open Transform spreadsheet and refresh all data.
    2.        Open Power BI Desktop and refresh all data
    3.        Publish Power BI Desktop file to PowerBI.com
    4.        Edit and update content pack on PowerBI.com to make data available to users.

    The boss is not satisfied with this at all however and wants to implement a solution which can be updated, if not real-time, then on scheduled refresh with the schedule set to refresh as many times in the data as PowerBI will allow. He wants the users on the viewing end of PowerBI.com to have information updated daily without anyone having to manually update anything.

    My questions is therefore -  is this possible without changing our infrastructure? Can I somehow connect to the MySQL data, transform and clean it, publish it, and update the content packs without any kind of manual interaction and at scheduled times throughout the day?

    Any suggestions much appreciated.

    We don't currently have the resources to invest in completely overhauling our system i.e. moving to a Microsoft SQL Server, data warehouses etc 

    Thanks. 

    Tuesday, November 24, 2015 1:28 PM

Answers

  • Hi Maracles. A first step would be to eliminate the Transform Spreadsheet. Power Query is built into Power BI Desktop, so you should be able to do the importing and cleaning of the MySQL data directly in PBI Desktop.

    After that, the next step would be to make it all refreshable from powerbi.com, without the need for any manual refreshes. You can find more info on how to set up powerbi.com scheduled refresh of MySQL data here:

    http://biinsight.com/mysql-and-power-bi-how-does-it-work/

    Hope that helps!

    Ehren

    • Proposed as answer by Maxim ZelenskyMVP Wednesday, November 25, 2015 7:11 PM
    • Marked as answer by Maracles Thursday, November 26, 2015 9:51 AM
    Tuesday, November 24, 2015 6:44 PM
    Owner

All replies

  • Hi Maracles. A first step would be to eliminate the Transform Spreadsheet. Power Query is built into Power BI Desktop, so you should be able to do the importing and cleaning of the MySQL data directly in PBI Desktop.

    After that, the next step would be to make it all refreshable from powerbi.com, without the need for any manual refreshes. You can find more info on how to set up powerbi.com scheduled refresh of MySQL data here:

    http://biinsight.com/mysql-and-power-bi-how-does-it-work/

    Hope that helps!

    Ehren

    • Proposed as answer by Maxim ZelenskyMVP Wednesday, November 25, 2015 7:11 PM
    • Marked as answer by Maracles Thursday, November 26, 2015 9:51 AM
    Tuesday, November 24, 2015 6:44 PM
    Owner
  • Hi Ehren,

    Thanks for your reply - I did actually notice some of the Power Query features in Power BI Desktop but wasn't sure if it was a complete replacement for PowerQuery in Excel - is all of the functionality copied over?

    I also use Excel for a couple of data sources which are not in the MySQL database, for example financial transactions which are currently only recorded in Excel. They pull in data from an Worksheet that is updated once  a day, manually, by a different department. Can this type of data sourcebe updated through Scheduled Refresh?

    Another reason that I've been using Excel is that my SQL knowledge sometimes isn't good enough to extract tables that I want in a single query, instead I extract two or more simpler tables and then combine them in PowerQuery using 'Merge' or 'Append' until I have the table I want - not ideal I know! I think the only solution to that is improving my SQL or getting someone in to write the queries...

    Thank you for the link by the way, that link and the others linked from it are very useful.




    • Edited by Maracles Wednesday, November 25, 2015 10:44 AM
    Wednesday, November 25, 2015 10:44 AM
  • Hi Maracles.

    a) Yes, local Excel files can be refreshed via the Personal Gateway.

    b) You don't need to write SQL...that's what Power Query is for. :) PBI Desktop contains the same Merge and Append functionality as the PQ addin. Just click the Edit Queries button, and you should see the same Query editor you're already familiar with from Excel.

    Let me know if you have further questions.

    Ehren

    Wednesday, November 25, 2015 7:35 PM
    Owner