BI-NSIGHT – Power BI (Publisher for Excel Public, New Visuals) – SQL Server 2016 (Temporal Tables)

It would appear this week that there is not a lot of news in the BI world. Which is a welcome change. I have no doubt that by the time I am finished this blog post or wake up tomorrow morning there will be some updates!

Power BI – Publisher for Excel Public

It would appear that I started this blog post just in time.

And it now appears that you can now install an add-in for Excel, and then tale your table, Pivot Table, chart or any Excel element and pin it directly to a dashboard in Power BI.

You can find more details here: Cloud Platform Release Announcements for January 13, 2016

Power BI – New Visuals

This is the first of two visuals that you can now get for Power BI.

I do think that this first one is really useful as it often is very handy to see a breakdown on how your data is as it goes through the different stages.

Next is the Percentile Chart, which is also a great visualization to have for specific questions or data that you want to visualize. And can often very quickly show where the drop off is, or the uptake!

You can find these visualizations as well as all the Power BI Visualizations here: Power BI Visuals

SQL Server 2016 – Temporal

This is a new addition to SQL Server 2016 which is most welcome.

I already have a server installed with SQL Server 2016 CTP 3.2 so I quickly read the blog post and got straight into the details. And tested it out based on the blog post.

I have to admit that it was really simple and almost effortless to enable the temporal support for the related tables. Either doing it with a totally new table or an existing table.

And then to query the data across both tables was once again super easy. I have to say hats off to the guys at Microsoft for making something that in the past especially in data warehousing has been so complex to implement and manage that now it will be really easy and simple.

I also honestly think that the performance impact will be so small that it will not have a direct impact on performance.

What this does mean is that going forward we will simply just have to query the data, and by default it will be slowly changing.

I also see the opportunity for doing snapshots of the data at a point in time, which if I understand it correctly could mean that potentially there will no longer be a requirement to snapshot the database every day. (Well as long as you have enabled Temporal support on your related tables that are involved in the snapshot.)

You can find out about the details here: Effortlessly Analyze Data History Using Temporal Tables

BI-NSIGHT – Power BI (Pin Excel Range to Dashboard, Weekly Service Update, New Custom Visualizations, Power BI Community, R, VMOB Content Pack) – Azure (Data Science Virtual Machine)

So I have been offline for a little while, quite a few things going on, but I am back on track and here are the latest BI-NSIGHT updates that I have been looking into!

Power BI – Pin Excel Range to Dashboard

It is really great to see that the Power BI team is realizing that having the ability to put Excel sheets and charts into Power BI is something the existing Excel users will absolutely love, as well as use. And this in turn will get the people to see how best they can leverage all the other Power BI Features.

I personally think that it is a great way to showcase a lot of work that people have already completed in Excel. And as I am sure we all can agree is that sometimes it is easiest as well as best way to show some actual figures in a simple and efficient manner.

As well as the dashboards themselves updating if the Excel files are stored in OneDrive for Business.

You can read about it here: Pin a range from Excel to your dashboard!

Power BI – Weekly Service Update

It was another big release of updates in the Power BI Service.

I have already touched on the ability to Pin an Excel Range to a dashboard.

Some of the other updates which I would like to highlight is having the Full screen mode for dashboards and reports. I am certain that already in a lot of organizations, as well as my own workplace we will be leveraging Power BI to have dashboards on big screens. And this makes it really easy to get this up and going!

I also think it is something very small, but it is always good to know where your data is stored. So that if you need to know this for compliance reasons you can rest assured if it can or cannot be stored in Power BI.

Along this line it is always great to see that they are improving the performance of the Power BI service. I know in the past that I have seen some products where the performance is good, and it pretty much stays the same. I am sure that Microsoft are learning a lot from the Power BI implementation. And it is great to see that they are putting what they have learnt into practice!

The other updates which you can read about in the link below are Sharing Dashboards directly to Another users Workspace, Improved Google Analytics Connection and the ability to close your Power BI account ( I have no idea why anyone would want to do that.)

Here are the Weekly Service Updates: Power BI Weekly Service Update

Power BI – Custom Visualizations

.

Since my last blog post, Microsoft as well as other providers have been releasing new custom visuals very quickly.

I have to say once again, to me this is a big game changer. I know that in my current work environment we have been looking at Power BI, and with the ability to incorporate Excel sheets, and all the custom visuals we are not at the point where we can leverage Power BI as our starting point for our users to start looking at their data.

And I believe that this is a quick win for us, because by having it in Power BI we will be able to having it available to the people who are mobile, as well as to the end users. All that they will need is an internet connection and browser and they are good to go. No need to download, or install software.

You can see all the custom visualizations here: Power BI Visuals Gallery

Power BI Community Blog

This is another smart move from Microsoft with the Power BI Community blog.

What I see is that Microsoft is already a great community driven company. And this just once again shows how we can leverage off each other’s experience. Which in turns means we all can get things done quicker and smarter and learn something in the process.

I have no doubt that in time to come this will be one of the areas to find out how to do some really cool and amazing things in Power BI.

To find our more details and what has been blogged you can find the details here: Microsoft Power BI Community

Power BI – R

The guys at Power BI are moving at such a rapid pace that at times it is difficult to keep up. But I prefer to have new information and to be challenged, than to have to wait 6 months or a year or even longer for a product to be released.

And as I have said before they are enabling more features and integration, which in my mind will make Power BI the go to destination for organizations.

And whilst I currently do not use R, I have no doubt that it will be something in the near future that I will be looking into. And to know that Power BI has the capability means that we already have our front end, graphics completed. It will just be a matter of getting the data we require along with the R script!

You can read about it here: Visualizing and operationalizing R data in Power BI

Power BI – Content Pack VMOB

Once again this week there is another great content pack for people who use the Vmob for their personalize marketing campaigns.

If you are interested in VMob or are an existing customer, then you can find more details here: Visualize your VMob data in Power BI

Azure – Data Science Virtual Machine

From what I can see and the way that BI is moving in the future, a big part of that going forward is going to have the ability to be able to try and predict what is going to happen in the future.

Along with this, one of the ways that you can be successful at this, is in my view is to start taking your data and starting to play around with Machine Learning and even R. And often in the past I can remember having to first find hardware, install the Operating System, then install the tools which you had to find and download as well as often configure. Which is very time consuming and often quite complex.

With the advent of the cloud as is shown here, you can simply click a button and in a few minutes you have what you need and can begin working or starting to see what can be done.

I have no doubt going forward there will be more BI specific Virtual Machines available to use.

You can read all about what is part of the Data Science VM here: Announcing the Availability of the Microsoft Data Science Virtual Machine

Power BI – Creating a Dashboard when not automatically created

What I found was that in some instances when you imported data into Power BI, it would not automatically create the dashboard.

This would also result in your dataset not being indexed (if that is the correct terminology) so that you could then use Q&A in order to enable users to easily search for results using Q&A

As with our example below, in order to replicate what would happen when Power BI does not create a dashboard, we are going to create a new report also. In effect this could be the case when for some reason Power BI does not create a report and or dashboard. And then how to quickly create the dashboard and then ensure that you can then use Q&A to interact with your data.

NOTE: We will be using our Excel file which we imported the data previously: Importing your Excel Workbook from One Drive for Business into Power BI and create a simple report and Dashboard

Creating your Power BI Report and Dashboard

  1. Log into your Power BI.
  2. Click on Datasets, then click on Explore
    1. As you can see with our example above for our Auto Update of Adventure Works DW
  3. We then create a very simple report as shown below based on our Adventure Works Data
    1. Then click on Save, and as below we saved our report with the following name:
  4. Now we can see our report under Reports
  5. Next will be to create the dashboard.
    1. The trick that I have currently found is that in order to create the dashboard which will map to the same
      report is to give it the identical name
    2. So click on the plus sign next to Dashboard to create your new dashboard as shown below
    3. So once complete it will look like the following:
    4. And the initial screenshot will also be blank, as well as in the Q&A section there will be no text prompting you to “Ask a question about the data on this dashboard
  6. Now go back to your report you created in step 3 above.
    1. Now pin an item to your dashboard
    2. Now when you go into your dashboard that you created in step 5 above you will see your pinned item or tile.
  7. Now in order to get the Q&A
    working, from what I have read you have to wait for the Power BI service to index or go through your data in order to create the required synonyms.
    1. NOTE: This can take a few
      minutes or longer to create the required synonyms.
    2. NOTE II: I have sometimes found that closing the Power BI app in your browser and opening it again, results in the Q&A then being there.
  8. If you go back into your dashboard after some time you should be greeted with the following as shown below:
    1. And now you can ask a question such as “Sales by City”

BI-NSIGHT – Azure Analytics Services – Power BI Dynamics AX Connector – Auto refreshing of Excel Files in Power BI – Excel 2016 Updates

Once again this past week Microsoft as released even more products. So let’s get started.

Azure Analytics Services

On 29 April at the annual Microsoft Build conference they announced new services on the Azure platform. I really think that Microsoft is doing a great job of having an entire cloud platform available for customers.

Due to the digital age and a lot of data is coming from mobile devices, which in turn are using digital services, I can see looking forward that as Microsoft says in their video, there will be a major requirement going forward for businesses to be able to use digital platforms in order to gain insights into their data.

With the Azure Analytics Services, this enables businesses to easily use products, without having to actually build the infrastructure and knowledge on the platform.

I am going to highlight the two new services which relate to BI. The Azure SQL Data Warehouse and the Azure Data Lake.

Azure SQL Data Warehouse

I really think that this is a smart move from Microsoft. It is something that we have been looking for and asking for from Microsoft. From the video (Build 2015: Azure SQL Data Warehouse) it appears to me that not only will it be a data warehouse, but also enable you to move data in and out of the data warehouse using SSIS.

I do hope that my assumption is correct, as this would be a great feature to have SSIS on a cloud platform. And this has the potential to create a whole host of new possibilities.

Along with this, it also appears from what I read that they are using the APS (Microsoft Analytics Platform System) as a basis for the Azure SQL Data Warehouse. If this is indeed true, I think that this could be a game changer. Due to the fact that we already know how easily it is to scale out with the APS. Which gives amazing performance.

Another great advantage is it looks like you can use the Azure SQL Data Warehouse as a source in Power BI. Which then enables all the features of Power BI. This combination I think would make a great competitor to other BI cloud products on the market.

Also as per the video, they have made it really easy to change the scale of computing that you require. It is really easy to complete and it can be changed in seconds!

Credit: Microsoft

Azure Data Lake

This is another clever move, allowing businesses to store all their data in a Data Lake. Which then gives the business the flexibility to then use this with the various Azure Services.

Here is a link to the video (Build 2015: Data Lake)

What I think will be great is that you can then take data from your data lake, either put this into Azure SQL Data Warehouse, or then use a Machine Learning API to run over your data. After you have got the required results, you could then put this into your Azure SQL Data Warehouse, and put Power BI on top of this to visualize your results and see based on your specific Machine Learning API, what affect it could potentially have!

You also have the potential to use existing Machine Learning API developed by other people, which means that you do not have to spend countless hours trying to get the correct Machine Learning algorithm!

Here is an overview of the Azure Analytics Services from Microsoft

Credit: Microsoft

Here is the blog post from Microsoft: Microsoft Announces Azure SQL Database elastic database, Azure SQL Data Warehouse, Azure Data Lake

Power BI Dynamics AX Connector

Just a quick note that I received an email that the Power BI guys have started planning for the integration of Dynamics AX as a connector in Power BI.

I am sure that this is welcoming news for Microsoft Dynamics AX customers

Power BI Dynamics AX – Planned

Automatic Refreshing of Excel Workbooks in Power BI

Just a quick review of my blog post this week, which was really well received.

It just explains how using a Power Query and Power Pivot, in which you use to get data into Excel Workbook, you can then have a mechanism how to get your Power BI report updated automatically.

This means that you can have your data on premise which you can then ensure gets into Power BI and then can have the potential for real-time updates.

AUTOMATING REFRESHING OF POWER BI REPORTS AND DASHBOARDS WITH EXCEL WORKBOOKS AND POWER UPDATE

Excel 2016 Updates

Just another quick note if you want to read up about the updates for Excel 2016, head over to Chris Webb’s blog post which has all the relevant information.

Chris Webb – What’s New In The Excel 2016 Preview For BI?

That is it for this week, another busy week and a lot going on!

 

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.

 

Power Query – Adding Leading Zero to Number (EG: Month Number)

Below is an example if you have a requirement and you want to add a leading zero

Example: You have a Month Number that starts with “1”, and you want it to start with “01”

  1. In your Power Query Window, click on Add Custom Column and then put in the following syntax

    Text.PadStart(Text.From([Month]),2,”0″)

    1. NOTE: In the above example, we have our column name as Month
  2. So once completed it will look like the following:

Error: An Item with the same key has been already added – Unable to load the tables in the Power Pivot Window

NOTE: First thing to do is to make a copy of your Excel Workbook, before making any changes to your Power Query Source. If you do NOT, and change your Power Query source, your Power Pivot Model could become unusable!

1.      I had an issue where if I changed the Power Query source from a CSV after I made changes to my data in my Data Model, once saving the Excel workbook, and then going back in I would get the following error:

a.      clip_image002

2.      So what I did to try and fix it was to do the following

a.      Get the data into Power Query correctly, so that it was working as expected.

3.      Then went into the Loading of the Power Query data to the Data Model.

a.      Right click and selected Load To:

                                                    i.     clip_image004

b.      Then un ticked both options

                                                    i.     clip_image006

                                                   ii.     You will then get prompted with the following below:

                                                  iii.     clip_image008

                                                 iv.     Click Continue

c.      It will then come back saying that the Load is Disabled:

                                                    i.     clip_image010

d.      Now go back and again Right click and selected Load To:

                                                    i.     clip_image004

e.      This time select the following:

                                                    i.     clip_image012

f.       Now this will load your data into the Data Model.

4.      Next you can open Power Pivot

5.      What I did next was to rename my Sheet which I am hoping will break the association with the old sheet that was in the Data Model.

a.      Renamed it from:

                                                    i.      clip_image014

b.      To:

                                                    i.      clip_image016

6.      I then went and saved my Excel Workbook, closed it. Opened it again and went into Power Pivot.

a.      So far so good.

7.      I then went back and renamed the sheet in the Power Pivot Data model, back to the Original Name

8.      And I got the same error:

a.      clip_image017

9.      So currently the only way I can see to get this to work, is to keep my new Name.

10.   Which means having to re-do or add my fields to the existing reports.

11.   NOTE: Reading further down the error, it talks about Synonyms, so my educated guess is that it has something to do with the Synonym data, not allowing changes to be made within the Data Model.

Sharepoint 2013 – refreshing excel workbook with direct connection to sql server analysis services (SSAS) cube

I have not blogged in a while, due to moving countries and starting a new job. So i do hope that this blog will help someone or let them know how easy it is to use SharePoint 2013 to refresh data in an Excel spreadsheet.

What I needed to do, was to use an existing Excel spreadsheet which connected directly to an SSAS cube. We then wanted SharePoint to manage the refreshing of the data. In the past I thought that this was only applicable to Power Pivot Excel workbooks, but after today I realized that you can do this directly to your SSAS cube.

 

Getting the location of where you will store your Data Connections in SharePoint

The first thing that you need to do, is to ensure that you have the location of where you want to store your Connection File in your Excel workbook.

You will also need to have the Data Connections created in your SharePoint site.

 

Example:

·         In our Example we are going to be connecting our Existing Excel Workbook, to a SQL Server Analysis Services (SSAS) cube, using a data connection that is stored within SharePoint.

·         Once we have created our connection, we are then going to use the PowerPivot Refresh within SharePoint to refresh our Excel Workbook from the cube.

 

Assumptions:

·         This is based on SharePoint 2013 Enterprise Edition and SSAS 2012

·         We are going to assume that you have already got your SharePoint site set up.

·         We are also going to assume that you have already created your Excel Workbook, which connects to a cube.

·         We are also going to assume that you have either created a Documents Library, or are going to use an existing Documents Library.

·         And then you have uploaded your Excel Workbook to your documents library.

 

 

NOTE: You will be required to have Owner rights to do the following below within your SharePoint site.

 

1.       Log into your SharePoint site and click on Site Settings

2.       Then click on Data Connections

a.       clip_image001

3.       Once this opens you will need to copy everything before the /Forms/AllItems.aspx

4.       As with our example we copied the following:

http://SharePointBIWebSite/sites/wcsa/Data%20Connections

5.       Now either save the above link or copy the link which will be used in the next steps.

 

Changing our Excel File to use the stored connection within SharePoint

1.       Open your Excel File from your SharePoint location

a.       NOTE: The easiest way is to navigate to where you have uploaded your Excel file and then say Open in Windows Explorer

2.       Then open it in Excel

3.       Then click on Data, and click on Connections

a.       clip_image002

b.      Then click on Properties

4.       Once the Connection Properties Window opens click on Definition

5.       Next what you need to do is where it says Connection Name, change this to something more meaningful and possibly shorter than the default.

a.       We changed ours to the following name:

b.      clip_image003

6.       Now at the bottom where it says Export Connection File click on the button

a.       clip_image004

7.       This will then open the File Save Window

a.       Now at the top where it asks you the location of where you want to save the file click on the Drop down in the Address Bar and put in the URL which we either saved or Copied in Step 4  above and paste it:

                                                               i.      clip_image005

                                                             ii.      Now where it says File Name you can either leave this with the default, but what I recommend is changing it to then match your Connection Name, as we did with our example:

1.       clip_image006

                                                            iii.      Then click Save

b.      This will then open the Web File Properties Window, where it asks for some more information.

                                                               i.      Once again we checked to ensure that our Title Matched our Connection File Name

                                                             ii.      clip_image007

c.       Then click Ok.

8.       Now when you go back to your Connection Properties Window you will now see that your connection File has changed to the location of our ODC which is saved to your SharePoint data connections site.

9.       The next thing that you need to do is to make sure you put a tick in the box, “Always use connection File

a.       NOTE: This is so that whenever and where ever the Excel spreadsheet is used it will always use this connection file

b.      NOTE 2: This is so that when it is uploaded or run from SharePoint it will then use the associated ODC file.

c.       clip_image008

10.   The next thing that you need to do, is to configure the Excel Services Connection.

a.       NOTE: This is required as part of SharePoint so that it can use an Excel Services connection to make the authentication to the SSAS Cube to actually refresh the data.

b.      NOTE 2: You will have to ensure that the person responsible for your SharePoint installation has configured the Secure Store Service (SSS), as well as that the domain account that is linked to the SSS has access to the SSAS cubes.

c.       Click on the Authentication settings.

                                                               i.      Now in the Excel Service Authentication Settings screen configure it with the following below

1.       clip_image009

2.       NOTE: The name of our SSS ID is ExcelDataConnection

                                                             ii.      Then click Ok.

11.   Now when you click OK it is going to go and refresh all your sheets within your current Excel Workbook.

12.   You can now save and close your Excel Workbook.

 

Configuring your Data Refresh in SharePoint for your Excel Workbook

In the steps below we will now configure our Excel Workbook to have a scheduled refresh, so that when people open it up it will have the latest data based on the refresh.

 

1.       Now go to SharePoint where you have got your Excel spreadsheet uploaded.

2.       Click on the Open Menu Ellipses, then the Ellipses again, and finally Manage PowerPivot Data Refresh

a.       clip_image010

3.       This will then open the Manage Data Refresh web page.

a.       You can then configure it with the following:

b.      Under Data Refresh, you must Enable it.

                                                               i.      clip_image011

c.       You can then select  your Schedule Details based on your requirements

                                                               i.      NOTE: If you want to test this now, you must select the Also refresh as soon as possible tick box

                                                             ii.      clip_image013

d.      For the Earliest Start time select when you want the data to be refreshed.

                                                               i.      clip_image014

e.      For the E-mail notifications, this is for people you want to notify if the refresh fails.

                                                               i.      clip_image015

f.        Under Credentials, this is where you MUST specify the same SSS that you configured in your Excel Authentication settings

                                                               i.      As with our example we put in the following:

                                                             ii.      clip_image016

g.       Then finally for the Data Source, it should be configured with your Existing Data source you configured earlier

                                                               i.      clip_image017

h.      Then click Ok.

4.       Now if you selected step 3c above, you can wait a few minutes and see if it worked by going back into Manage PowerPivot Data Refresh and you should see the following:

a.       clip_image018

5.       Now you have completed the data refresh when connecting to an SSAS Cube via SharePoint