Automating refreshing of Power BI Reports and Dashboards with Excel Workbooks and Power Update

I recently tested to see if it was possible to upload an Excel Workbook to One Drive for Business, and then use this within Power BI. This was successful.

Then I saw, and it appeared that if you used an Excel Workbook from One Drive for Business, that it would automatically refresh the file. Which I thought it might possibly mean that if you updated your Excel Workbook in your One Drive for Business folder it would then update your dashboards and reports in Power BI.

The outcome was that it does indeed do this. Which means that we not have a means to update Power BI dashboards and reports from our Excel Workbooks which can be stored On Premise.

This also gives us the ability to take any source information that we can get into the Power Pivot Model and get this to update in Power BI. So this also means that anything that we do with Power Query, which then goes into our Power Pivot Model (Data Model) can then be updated in Power BI!

And below I will explain how to do this using Power Update

NOTE: The reason is you could manually update your Excel Workbook, and then save it to your One Drive for Business. But ideally if we can automate the process, this then means that we can have our Power BI Dashboards and reports updating automatically.

NOTE II: If you would like to know more about Power Update, please read the following Blog Post Power Pivot Pro (Rob Collie): Introducing Power Update

  • Within the version they do have a free option!

Example Data:

  • We are going to use an Excel workbook which I used previously which had some Adventure Works data. The details of where the Excel Workbook are is listed below.

Uploading Excel Workbook with Power Pivot and Power View to One Drive for Business

Below are the steps where I upload my Excel Workbook, which has a Power Pivot Data Model as well as a Power View Report to One Drive for Business.

  1. The first thing is that I created my Excel Workbook, with Power Pivot and Power View.
  2. NOTE: The reason that I created this is so that when it is uploaded to Power BI, the entire Power Pivot Model will be exposed in Power BI.
  3. In order to do this, please refer to my previous blog post to create it with your own data
    1. Power Pivot and DAX in Power BI
  4. Next I uploaded my Excel Workbook to One Drive for Business

Importing your Excel Workbook from One Drive for Business into Power BI and create a simple report and Dashboard

Next we will import our Excel Workbook using the file which we uploaded into One Drive for Business within Power BI

  1. Log into Power BI
  2. Then click on Get Data
  3. Once this opens, make sure you select Excel Workbook, then click on Connect
  4. Now on the next screen you will see the options for Computer, OneDrive – Personal, OneDrive – Business
    1. Click on OneDrive – Business
    2. NOTE: The first time it might prompt you to log in and authenticate you to your OneDrive for Business Folder
    3. Then go to the location where you saved your Excel Workbook in the previous steps.
    4. Then click
      Connect
    5. NOTE: As you will see above currently my Modified Date is from 5 days ago.
  5. Now under Datasets click on your Excel Workbook you just uploaded.
    1. Click on Explore
    2. NOTE: You will also see that the Last refresh succeeded is Tue Apr 28 12:11:34 BMT +1000
  6. We are going to create a very simple report and then a dashboard
    1. We then saved this report as AW Auto Update
  7. We then and created a new Dashboard with the same name as above.
    1. NOTE: If you create a dashboard with the same name as your report, it will automatically put the pinned
      items into that dashboard.
  8. We then went back to our report from step 6 and pinned both items to our dashboard, as shown below:
  9. Now we are at the point to use Power Update and to test the auto updating of Excel Workbooks from One Drive for Business

Using Power Update to update our data as well as Upload to One Drive for Business

In this next section we are going to configure Power Update, to refresh our data in our Power Pivot Model. And then automatically upload it into our One Drive for Business Folder

NOTE: You can download and find out how to install Power Update from this blog post by Power Pivot Pro (Rob Collie): Introducing Power Update

  • Within the version they do have a free option!
  1. Due to the way I brought the data into my Power Pivot Model in my Excel Workbook, I did go and update my source query to the AdventureWorksDW2014 database.
    1. But you would normally have your query dynamic where it is getting its source data from.
  2. For our example to show the automatic refreshing of data from One Drive for Business into Power BI, I am going to ensure that we have Sales for 2010 – 2013
  3. In the next steps I am going to show you how to configure Power Update to refresh your Excel Workbook.
    1. Open Power Update
    2. Click on New
    3. Give your Task a Name
      1. In our example it will be called AW Auto Update.
      2. Click Next
    4. For our example we set it to Daily (or Multiple times a day)
      1. Click Next
    5. On the Setup a daily schedule we just accepted the defaults
      1. Click Next
    6. On the Set up multiple times per day schedule, we set ours to run every 1 hour
      1. Click Next
    7. Now on the step for select Destination Type, ensure to select SharePoint
      1. NOTE: Even though we are using One Drive for Business the underlying
        data is stored within SharePoint. So it will work.
      2. Click Next
    8. For our example we only have a single workbook, so on the Select source workbooks, we selected Update a single workbook
      1. Click Next
    9. On the Select workbook to update, select the location of your Excel Workbook on your file
      system or shared folder.
      1. Click Next
    10. Now on the SharePoint Settings, we have to configure the following.
      1. First click on New under Credentials
        1. Make sure you put a tick next to Office 365 / Power BI.com
        2. Click
          Ok
      2. Now on the SharePoint Site URL you will need to put in the following:
        1. Go to your One Drive for Business Folder in your browser
        2. NOTE: Copy and paste the entire URL, Power Update is smart and it knows which part of the URL it requires.
        3. Then click on Select
          1. Now browse to the folder location where you previously uploaded your Excel Workbook in the previous section: Importing your Excel Workbook from One Drive for Business into Power BI and create a simple report and Dashboard
        4. So that once completed it will look similar to the following below:
      3. Click Next
    11. Now the Wizard is completed and you should see the following in Power Update
  4. Next we are going to manually run this now, so that we can show how it will auto refresh the data in Power BI once the process is completed.

Automatic Refresh of Power BI Report confirmation

Below we will see if Power BI refreshed the Excel Workbook from our One Drive for Business

  1. The first place to see if our Excel Workbook has been updated via Power Update
    1. As we can see above it has been updated.
  2. Now we will have to wait and for Power BI to go and check the Excel Workbook and update the reports and dashboard
    1. NOTE: From what I read, the Dashboard tiles auto update every 10 minutes, I am not sure what the schedule is for Excel Workbooks in One Drive for Business
    2. But there must be some mechanism in place where it looks and updates Excel Workbooks connected to One Drive for Business
  3. I then went back to my Datasets and clicked on the Ellipses button and after about 4 – 5 minutes I saw the following:
    1. As you can see above the Last refresh succeeded time has changed from our previous value to Tue Apr 28 12:58:13 GMT +1000
  4. Now I then went back to our Dashboard and saw the following below
    1. As you can see from above, we now have the year 2010 (albeit small) as well as the increase in Sales
      Amount.

       

So in conclusion you now have a process to fully automate getting data from your On Premise source Excel
Workbook, and getting your reports to Automatically update in Power BI.

 

The future for me

I was reading through the email from The Databse Weekly and I saw an article from Paul Randal with regards to Paul being your mentor. This really got me thinking and where is my future within Business Intelligence (BI).

I really love what I do. I get great satisfaction and enjoyment when I can tell people a story about their data. Or show them insights into their data for the first time.

I find it easy to grasp new concepts and get it all working. I work entirely within the Microsoft BI toolset.

With the new Office 365 and Power BI offerings there is a whole new world to get my head around. It really excites me.

I have also started working for a BI consulting company and it equally exciting times here. They are looking to expand and really get into the Microsoft BI space. That’s where I come into the picture.

I feel that I am at the right place at the right time. In terms of using my existing knowledge within the Microsoft space, as well as enabling the consultancy to grow from strength to strength going forward.

This is where I feel that the being mentored by Paul Randal could get me onto the next level, which is where I want to go and where I will eventually get to. Being mentored by someone as knowledgeable and experienced means I can get there a little quicker!

So where do I see myself in the future?

I see myself learning and going to that next level. Enabling businesses to gain insights to their data faster and quicker than ever. As well as driving the BI consultancy to become the go to consultancy for Microsoft BI.

Framework for Automating SQL Server Reporting Services (SSRS) data driven subscriptions – Part 2

Description: What the goal was to find a way to automate how an end user could subscribe to a SSRS Data Driven Subscription without any intervention from the Business Intelligence Developer. And also enable the end users to have their own selections. And finally if they did not want to receive the report any longer to then delete their subscription.

In Part-2 we will be creating our SSRS Report, creating the Data Driven Subscription and then finally linking this to our Power View Report

Here is the link to Part 1: FRAMEWORK FOR AUTOMATING SQL SERVER REPORTING SERVICES (SSRS) DATA DRIVEN SUBSCRIPTIONS – PART 1

So what we wanted to do, is to create a SQL Server Reporting Services (SSRS) report, then use the data driven subscription (DDS) to email the reports to the people that created the request in the Share Point List. And then finally have a link to a Power View report for more user interaction with their data.

NOTE: This will all be completed on SQL Server 2012

Example:

·         We have our data from our SharePoint List stored in the following table:

o    [dbo].[Mart_TD_SharePoint_ReportSubscriptions]

·         We are going to create a report which will show the Internet Sales.

o    Within this report it will have the Date Range Selected by the User

o    It will also have the Product Line selected by the User.

·         Once the report is created we will then create our Data Driven Subscription.

·         We will also create a Power View report based on the same Cube where our report gets its data from.

o    Once the Power View Report is created we will then add a link to our SSRS report for the users to click on for more information.

 

Creating our SSRS Report

The first thing that we are going to need to do is to create our SSRS report with the required parameters so that this can be used in our Data Driven Subscription.

 

As with our Example we will require the following parameters in our report.

·         Start Date

·         End Date

·         Product Line

 

1.       If need be create a new SSRS Project.

2.       Then as with our example we created our report with the following name:

a.        clip_image001

3.       Next we created our Shared Data Source to our SQL Server Analysis Services (SSAS) cube.

a.        We then added this Shared Data Source to our report.

4.       We are now also going to create another Shared Data Source which will connect to our SQL Server table where we have stored our details for our DDS

a.        NOTE: This will be your SQL Server where you have got your [dbo].[Mart_TD_SharePoint_ReportSubscriptions]

b.       We created the following Shared Data Source as per our example:

                                                               i.      clip_image002

5.       Next we are going to create our 3 Parameters as detailed above:

a.        clip_image003

b.       clip_image004

c.        NOTE: The reason for the above to Parameters that they are set to Text is because we are going to be getting these values later via our DDS, which will then be passed dynamically to our query.

                                                               i.      Along with this, the end user will never use this report due to it being emailed to them.

d.       Also we will be passing the DateKey Values to our MDX Query as explained further in the example.

e.       clip_image005

6.       Next we create our MDX Query

a.        NOTE: Initially we will hard code the values so that we can create our report and test that it works.

b.       So you would create your new Dataset as below:

c.        Here is the MDX Query:

Selectnonempty {[Measures].[Internet Sales Amount]} on 0

,nonempty {(

                

                  [Date].[Date].&[20070701]:[Date].[Date].&[20070731]

                 ,[Product].[Product Line].&[S]

                

                 )} on 1

from [Adventure Works]

                                                               i.       

d.       It would look like the following:

                                                               i.      clip_image006

e.       Next click on Fields, and we changed it to the following so that it is more friendly:

                                                               i.      clip_image007

f.         Then click Ok.

7.       We then created our Chart and completed all the formatting in order to get the report looking good.

a.        This is how we put in our values into the Chart:

b.       clip_image008

c.        So once completed the report will look like the following:

d.       clip_image009

8.       The next thing that we are going to do is add our Parameters to our MDX Query

a.        Go into your Data Set you created above: InternetSales_Dates_ProductLine

b.       On the right hand side next to query click on the Function button:

c.         clip_image010

d.       Remove the current MDX query and replace it with the following below:

=“Select non empty {[Measures].[Internet Sales Amount]} on 0 ,non empty {([Date].[Date].&[“+Parameters!StartDateKey.Value+“]:[Date].[Date].&[“+parameters!EndDateKey.Value+“],[Product].[Product Line].&[“+Parameters!ProductLine.Value+“])} on 1 from [Adventure Works]”

                                                               i.       

e.       NOTE: The above must all be on one line otherwise it will not work.

f.         Then click Ok to go back to your report.

9.       Now Preview the report putting in values for the Parameters:

a.        clip_image011

b.       After clicking View Report you should see the following:

c.        clip_image012

10.    Now you can deploy the report to your Reporting Server.

a.        NOTE: Remember to deploy the report as well as the Shared Data Sources.

 

Configuring and creating your Data Driven Subscription (DDS)

1.       The first thing that you will need to do is to change the data source for your report to use stored credentials.

a.        As with our example we are going to complete the following for our 2 Shared Data Sources from our Report we deployed earlier

                                                               i.      clip_image013

b.       The reason for this is so that when the report runs from the job schedule later, it will be running from a SQL Server Agent job, which will then require a way to be authenticated for the data source.

c.        So this has to be completed before configuring your DDS.

d.       We configured ours to use a Domain account as shown below:

                                                               i.      clip_image014

e.       Then click Test Connection to ensure that it will connect and work:

                                                               i.      clip_image015

f.         Then click Apply to save the changes.

2.       You can now test your report on the report server just to ensure that if you pass the correct parameters it will work.

3.       Next we are going to configure our DDS as explained below:

a.        Click on the arrow next to your report and select Manage as shown below

b.       clip_image016

c.        Then click on Subscriptions

d.       Now click on new Data-driven Subscription

                                                               i.      clip_image017

e.       This will then start the Report Subscription Wizard

f.         On the first screen Step 1 – Create a data-driven subscription: Internet Sales – Report Subscription we configured it with the following:

                                                               i.      clip_image018

                                                              ii.      As you can see from above we gave it a name.

                                                            iii.      We then specified that it will be delivered via email

1.       NOTE: You will have to have configured your SSRS Server to relay email through a server for this option to appear.

                                                            iv.      And finally we specified to use a Shared Data Source.

1.       NOTE: This is what we configured in step 1 above.

                                                              v.      Click Next.

g.        On the Step 2 – Create a data-driven subscription: Internet Sales – Report Subscription we then selected our Shared Data Source:

                                                               i.      clip_image019

                                                              ii.      NOTE: You must select the SQL Server data source connection because in the following step we are going to use a TSQL Query to get our required information.

                                                            iii.      Click Next

h.       On the Step 3 – Create a data-driven subscription: Internet Sales – Report Subscription this is where we use a TSQL Query to pass the details which will be required in the later steps

SELECT 

       [Product Line For Query]

      ,[Email Address]

      ,[StartDateKey]

      ,[EndDateKey]

         ,‘This report Subscription is for Product Line ‘+[Product Line]+‘ and Date Range: ‘+[Date Range]asSubjectDetails

  FROM  [dbo].[Mart_TD_SharePoint_ReportSubscriptions]with (nolock)

                                                               i.       

                                                              ii.      NOTE: You will see that we have put in an extra column so that we can use this as our Subject for our email.

                                                            iii.      Then click on Validate, this will ensure that it can make the connection to the Shared Data Source which you configured in the previous step.

                                                            iv.      clip_image020

                                                             v.      Click Next

i.         Now on the Step 4 – Create a data-driven subscription: Internet Sales – Report Subscription, we are going to configure the sending of the email with the following below:

                                                               i.      To:

1.       This is the to email address

2.       You will change it to Get the value from the database, and select Email Address

a.        clip_image021

                                                              ii.      Subject

1.       This is the subject for your email that is being sent

2.       You will change it to Get the value from the database, and select Subject Details

a.        clip_image022

                                                            iii.      In our example we changed the Include Link to No Value

1.       NOTE: This is because we did not want them to click back to the report that was emailed.

2.       clip_image023

                                                            iv.      Click Next

j.         On the Step 5 – Create a data-driven subscription: Internet Sales – Report Subscription, this is where you configure what parameters will be passed from your query in Step 3 above

                                                               i.      As with our example we configured it with the following:

1.       clip_image024

                                                             ii.      Click Next

k.        On the Step 6 – Create a data-driven subscription: Internet Sales – Report Subscription, this is where you can configure if you want to create a schedule.

                                                               i.      We wanted to create a schedule so that the report would be emailed out

                                                              ii.      clip_image025

                                                            iii.      Click Next

l.         On the final screen Step 7 – Create a data-driven subscription: Internet Sales – Report Subscription, this is where you can configure your schedule which for our requirements we configured it with the following below:

                                                               i.      clip_image026

m.      Then click Finish to complete your Data Driven Report Subscription

                                                               i.      NOTE: If you have not configured your Shared Data Source to use stored credentials you will get the following when you click Finish

                                                             ii.      The current action cannot be completed. The user data source credentials do not meet the requirements to run this report or shared dataset. Either the user data source credentials are not stored in the report server database, or the user data source is configured not to require credentials but the unattended execution account is not specified. (rsInvalidDataSourceCredentialSetting) Get Online Help

                                                            iii.      You will then need to go back and modify the Shared Data Source and then re-create your DDS

n.       You will then see the following:

o.       clip_image027

4.       Now if you want to manually test this and you have access to the SQL Server, you can find the report subscription if you go into your SSRS Server and open the SQL Server Agent, then the Job Activity Monitor

a.        Then the way to find it is to look for the Job Names that start with a GUID

b.       As with our example it had the following name:

                                                               i.      clip_image028

c.        You can also confirm by looking at the Next Run

                                                               i.      clip_image029

                                                              ii.      NOTE: When you create your subscription it will always be for the following day to start.

d.       Right click and select Start Job as Step

                                                               i.      It should complete very quickly and be successful

                                                              ii.      clip_image030

5.       Now if you go and check your email you should see the following below:

a.        NOTE: For our subscription we had selected the following

                                                               i.      Date Range: July 2007 – June 2008

                                                             ii.      Product Line: Mountain

b.       clip_image031

c.        Which as you can see from above we received.

 

Creating the Power View Report and putting the link into the SSRS Report

1.       We then went ahead and created our Power View report which connected to our SSAS Cube and looked like the following:

a.        clip_image033

b.       NOTE: This is before we put in any filters to only show our selection from our report.

2.       Next we went back into our SSRS report and put in the following below so that we can use it as a Hyperlink in our report

a.        Then in the bottom of the report right click and select Insert and then Text Box

b.       clip_image034

c.        Double click in the Textbox or within your table so that it is highlighted.

                                                               i.      Then right click and select Create Placeholder

                                                              ii.      clip_image035

                                                            iii.      Now this will open the Placeholder Properties

                                                            iv.      In the General Window you first need to put in your text or expression into the Value area.

1.       Click on the function button

2.       As with our example we put in the following below.

3.       You will see that we have already formatted it with our HTML Tags

=“<a href=””http://powerviewServer/powerpivot/_layouts/15/ReportServer/AdHocReportDesigner.aspx?RelativeReportUrl=/powerpivot/Shared%20Documents/Adventure%20Works%20DW%202012/Report%20Subscription%20-%20Adventure%20Works.rdlx&ViewMode=Presentation&PreviewBar=False&rf=[Product].[Product%20Line]%20eq%20%27Mountain%27 “”>Click HERE to view more information</a>”

                                                              v.      From the above example we have set the Preview Bar to false so that when people are viewing the report, they can only view the report and not make any changes.

1.       It is highlighted above in RED

2.       &PreviewBar=False

                                                            vi.      From the above example we have hardcoded our Filter above with Mountain

1.       It is highlighted in Purple

2.       &rf=[Product].[Product%20Line]%20eq%20%27Mountain%27

                                                           vii.      Now what we will do is to replace the hardcodes value with our value from our query, which is shown below:

&rf=[Product].[Product%20Line]%20eq%20%27″+First(Fields!Product_Line.Value, “InternetSales_Dates_ProductLine”)+”%27

                                                         viii.      So now the entire HTML Tag from above will look like the following:

=“<a href=””http://powerview2013dev/powerpivot/_layouts/15/ReportServer/AdHocReportDesigner.aspx?RelativeReportUrl=/powerpivot/Shared%20Documents/Adventure%20Works%20DW%202012/Report%20Subscription%20-%20Adventure%20Works.rdlx&ViewMode=Presentation&PreviewBar=False&rf=[Product].[Product%20Line]%20eq%20%27”+First(Fields!Product_Line.Value, “InternetSales_Dates_ProductLine”)+“%27 “”>Click HERE to view more information</a>”

                                                            ix.      Once you have formatted your expression with your HTML tags then click Ok.

d.       The final part is to enable your Placeholder for HTML.

                                                               i.      Under Markup Type click on HTML – Interpret HTML tags as styles

                                                              ii.      clip_image036

                                                            iii.      Then click Ok.

e.       It should now look like the following:

                                                               i.      clip_image037

3.       Once the report is run or emailed it will then look like this at the bottom of the report:

a.        clip_image038

4.       Finally deploy your changes to your report server.

5.       Then to test to make sure it works correctly, run the job again as explained above.

a.        Once you get the email, as with our example we had selected Mountain.

b.       After clicking the above link to view more information we saw the following Power View Report.

c.        clip_image040