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.

 

BI-NSIGHT | Power Pivot &  DAX in Power BI – SharePoint 2016 – Power BI AS Connector – Power BI Designer Preview Update – New Power BI Content Packs

In a week there have been a whole host of updates and changes within the Microsoft BI space. So let’s get to it.

Power Pivot & DAX in Power BI

This is related to my blog past that I created earlier this week (Power Pivot and DAX in Power BI). And it was amazing to see that later on in the week there was a Power BI Designer update which now also allows DAX calculations as well as a whole host of other changes.

The thing that I want to highlight in terms of my blog post. Is that once you have created your Power Pivot Model, and then created a Power View report in Excel, everything that you can do in Excel, will not be available to you when you upload your Excel workbook into Power Pivot. So this will give you some additional functionality which is currently not available by default in Power BI.

Below is a screenshot of the dashboard that I created using Power Pivot and Excel.

SharePoint 2016

Microsoft last week also released some information with regards to SharePoint 2016 and what we can expect. The major take away that I got from this blog post from Microsoft was that I was looking for any new features with regards to BI. And there was a little glimmer of hope, where they said that they were looking to build a hybrid approach, so that companies could leverage the best of both. They did not go into too much detail, as it is still a long way off their release date. But to me it does look promising in terms of having some of their amazing work that they have done in the cloud space (Power BI), put back into the on premise space.

You can read their blog post here: SharePoint Server 2016 update

Power BI AS Connector Update

Last week Friday, Microsoft also released an update for the Power BI AS Connector. It was great to see that they added some additional features.

Starting with the Automated checks to ensure that your account can connect to the Power BI Service. This will ensure that once you have resolved all the errors, you will then be able to successfully connect to Power BI.

Support for Network Proxy, which is great as a lot of companies have network proxies in place for various reasons. And now you can still connect to Power BI via your network proxy.

Notification when a new Power BI AS Connector is available. Which I think is really great and a feature that I see in some of Microsoft’s other products (Power Query). I find it great, so that I do not have to go and make a reminder to go and look to see if there is a new version, it will just notify me that there is.

There is now support for all languages, which is a good thing. As not everyone has their native language in English.

They have also resolved a whole stack of bugs and diagnostics. The one that I really liked is that it will now let you know if your password has expired. Meaning that you cannot connect to the Power BI Service. This is great, because in the past I have had services running where the password for the domain account expired. And it took at times a lengthy amount of time to actually find the cause of the issue!

You can read the blog post from Microsoft here: What’s new in the April release of the Analysis Services Connector?

Power BI Designer Preview Update

I have to say that this is a major update in terms of the offering for the Power BI Designer.

I am not going to go into detail for all the updates, but just highlight the ones that I think are game changers.

Initial Support for DAX Measures

As per my blog post at the top, I could see that they had already provisioned Power BI to be able to seamlessly work with DAX measures. And now it is available as part of the Power BI Designer. This is really great and something that a lot of customers and users have been asking for.

This will enable people to create great reports and dashboards, because they can now create their custom DAX measures which will also improve the reporting experience.

As part of the support for DAX Measures they have also included the following:

  • Formula bar, so that you can now create your DAX Measures in the formula bar, with the intelli-sense and using Multiple lines
  • Comments, this allows you to put comments into your DAX Measures, which is great because if you come back at a later point, at least you can have some idea of what you were doing. As well as if other people are going to be working on your data, they can have some comments to guide them in terms of what you were doing with the particual DAX Measure.
  • Saving Incomplete Measures, this is another great feature, because there have been loads of times when I have either been chatting to someone, or you want to go and validate some data before adding it to your DAX measure. And in the past it would either error, or you would have to cut it out, remove the DAX Measure and then come back and add it in again. This is really useful.

Data Types, formatting, rename and delete in the Report View

I have bundled all these updates into one here, because they all relate to working within the Report View.

What this means that if you want to change a data type, or the formatting, renaming a column or measure, or even delete the column or measure, you can now do this directly in the report view.

I see this as a great feature. As once again when working in Power Pivot, you always have to open Power Pivot if not open, go back in, make the change to your column or measure, then go back into your sheet, which will reflect the update and then continue on working. As you can just see from my typing out what would need to be done how much time that takes.

Instead now in Power BI Designer you can just right click, make your change and you are done! Such a time saver as well as great functionality.

You can read about all the other features and updates here: 16 New Updates to the Power BI Designer Preview

Power BI Content Packs

Today the Power BI Team also announced and released to additional content packs for Power BI.

I think that these content packs are really a smart move from Microsoft. It is a great starting point, because often there is so much new data, that to try and understand where to start or what to look for can be quite daunting. So Power BI gives you a launching pad to start from.

The two additional content packs that got release today are below:

Monitor and explore your Microsoft Dynamics Marketing data with Power BI

Stream sensor data to Power BI with Temboo

I want to quickly highlight that with Temboo, if you have any IDE (Integrated Drive Electronics) this allows you to go and upload it directly into Power BI. And the data can be uploaded in real-time. Which then means that your reports and dashboards will also update in real-time. To me there are so many sensors as well as people putting these types of devices into their own homes, that this is a great feature.

Go and click on the video to see what they are doing. Really powerful stuff.

That’s it for this week! Looking forward to see what is going to be coming out next week! Stay tuned.

Microsoft Power BI Designer Now supports DAX Calculated Measures

Just a quick update, I was busy working on a Power BI presentation and thought let me just download the Microsoft Power BI Designer and see if it has been updated.

And to my great surprise it has. It appears as though it was updated on 21 April 2015.

I then went and installed the downloaded version of Microsoft Power BI Designer.

Upon first look I did not see anything significantly different until I came across this below

As you can see you have the option to click on New Measure, and when you want to create your new measure, it would appear to me that it is using DAX!

And from my previous blog post POWER PIVOT AND DAX IN POWER BI , I know that DAX is already supported in Power BI.

You can download the Power BI Designer update here: Microsoft Power BI Designer

Power Pivot and DAX in Power BI

Introduction: Something got me thinking the other day when I used the Google Analytics Connector and saw what looked like to me was a Power Pivot Model, as well as an image. I know in the current Power BI Designer as well as when you import an Excel file into Power BI on the web front end, you do not get to see these options. So it got me thinking, how is Microsoft doing this?

After some playing around I found a simple and easy solution!

NOTE: This does not currently work with Excel 2016

What this enables you to do, is to have your entire Power Pivot Model, along with your DAX calculations, as well as the synonyms within your Power Pivot Model available in Power BI!

Below is a screenshot of my completed dashboard, after which I will explain how I did it, and then an example with the Power Pivot Model, DAX calculation, Image as well as a synonyms!

I have used data from the AdventureWorksDW2014 database to get data into my Power Pivot Model.

 

How to get Power Pivot, with DAX calculations and Synonyms into Power BI

You guy are going to love how simple this really is to get completed.

  1. Create your Power Pivot Model, DAX Calculations and if required your synonyms.
    1. NOTE: You can load your data from Power Query into your Data Model (which is Power Pivot)
  2. Then what I did was to first create a really simple
    Pivot Table with a chart.
  3. Next I then created a Power View report, by going into the Insert
    Ribbon and then clicking on Power View, after which I created my Power View Report as shown below
  4. Then I saved my Excel Workbook.
  5. Next I uploaded the Excel Workbook to Power BI.
    1. NOTE: I uploaded it directly from my PC, as well as from One Drive for Business and they both achieved the same results.
    2. I had the following while it was being imported
  6. Now before I go on, I wanted to show you what my Power Pivot Model looked like in Excel.
    1. From the above screenshot, you will see that I have created two DAX
      Calculations.
      1. Sales Amount
        1. This is just a sum of the SalesAmount
      2. PM Sales
        1. This is a DAX
          Calculation that I found in http://www.daxpatterns.com/time-patterns/
        2. I then used this and applied it to my data.
      3. NOTE: The two DAX Calculations above is to show that they can then be used within Power BI
  7. Now once I had uploaded my Excel
    Workbook I went into the report in Power BI and saw the following:
    1. As you can see above this is identical to my Power View report in Excel.
  8. Then if I click on Edit Report, I see the following in Power BI, as you will notice below, I added an additional Combo Report, using the Sales and PM Sales amount.
    1. Which as you can see above, is my original Power View report, but added an additional chart, using the DAX Calculations from my Power
      Pivot Model.
    2. NOTE: You can see that it has my DAX Calculations of Sales Amount and PM Sales available and ready to be used.
  9. As you can see from above, you can now have a fully functional and working Power Pivot Model, with most of the Power Pivot features such as DAX Calculations and Synonyms available to be used with Power BI!
  10. So in a nutshell all that you need to do is to add a Power View report within your Excel workbook. Which will then enable the Power Pivot model to be available in Power BI

For the Synonyms to work, I simply added them within my Power Pivot Model, under the Advanced tab.

Adding an Image for Power BI from Excel

  1. To get the image embedded, I simply added it to my Power View Report in Excel.
  2. Then once I uploaded my Excel Workbook, I could then Pin the image to my dashboard.

In future blog posts, I will explain how I got Dashboards working when they are not created automatically in Power BI.

Link to Excel File

Below is a link if you want to use my Excel File and then upload it to your Power BI site

Please note, the only reason for the image for my blog, is I had to put in some type of image and I could not think of anything else to put in there!

Adventure Works DW – Power BI Power Pivot Example.xlsx

BI-NSIGHT | Mobile BI from Microsoft – SQL 2014 SP1 – Power BI (Google Analytics Connector)

This is a slightly different post from what I have been posting in the past, which has been completely technically focused!

I have decided that I read up and look into so many things relating to the Microsoft BI eco system, that I should also share this with the readers and followers of my blog. Where I can give my thoughts and ideas on how best to use and leverage the multitude of offerings that are out there now in the BI space.

I have to admit that the updates, changes as well as new products is happening at a furious rate! And I am just trying to keep up with all of this! But I do enjoy it a lot and love having to research something or read about something new almost on a daily basis.

Right so let’s get into the good stuff!

Mobile BI from Microsoft

As we are all well aware Microsoft has been lacking in the Mobile BI space, and even though they have created an IOS app for Power BI, it is still in my mind a first release so there is a lot of catch up work to be done.

Now with Microsoft’s announcement of acquiring Datazen, they have made a very smart move in my book. (Yes I know that there are some people who have already voiced their opinion, in the hope to ensure that Microsoft does not complete another ProClarity debacle!) By acquiring Datazen, they have a great starting point for a Mobile BI offering. Which I have no doubt that they will work with the existing Datazen developers, and integrate this into the Microsoft BI eco system.

You can read about the details from the Microsoft blog here: Microsoft acquires mobile business intelligence leader Datazen


Credit: blogs.microsoft.com

Initial findings of Microsoft Mobile BI from Datazen

I am fortunate that I did have some time to try and see how easy or difficult it is to get the Datazen up and running. I will admit that I did skim read through the installation document, and from my skim reading it did seem pretty straight forward. The actual installation was rather easy to get the product installed.

It was then when I had to configure it to actually use the product that I once again quickly read the documentation, but I did not find it all that straight forward. But I think it is just to understand how they have developed the product, and follow the process. Even though it said during the installation that for the Active Directory integration you could configure it to point at the Active Directory server and you could then use Active Directory integration, but when I did complete that I then tried to create a domain user, and then log in as that user but it failed and did not work.

I also then had a bit of trouble creating a New Data Connection, when connecting to SQL Server Analysis Services, and when I looked online to try and see what the connection settings are, I could not find much information. I finally got it working by not specifying the Username and Password in the connection string! When did create a new Data Connection for SQL Server that worked perfectly!!

Then when I went to the next step to create a New Data View, I then put in my MDX query, and the Data View that was returned did not return the data as I expected, which was to include all the column information that you get when running an MDX query in SQL Server Management Studio.

SQL Server Management Studio Query Results vs Datazen Data View

Finally I got that working and then tried to use the Datazen Publisher App, which is for Windows 8, it was another new thing to learn on how to create the dashboard and link the data. But to me that is the fun part.

So all in all a mixed reaction to getting it up and running. But it does look really promising!

SQL Server 2014 SP1

Microsoft also announced the release of SQL Server 2014 SP1, and from what I read there are a whole host of bug fixes and updates to SQL Server.

As far as I could tell, there are no new products or significant changes, especially in the Microsoft BI space. It is great that they have SP1 already, which is always good to get all the initial release issues resolved in a Service pack.

You can read about all the details and updates here: SQL Server 2014 Service Pack 1 release information

And to download SP1 here is the link: Download Microsoft SQL Server 2014 Service Pack 1 (SP1)

Power BI (Google Analytics Connector)

And in my final thoughts and findings for this week, I also read up about the new Google Analytics Connector within Power BI.

The busy guys within the Power BI team have created the Google Analytics content pack, and once you have connected to it, it automatically creates a default Dashboard, as well as a whole host of underlying reports.

I personally did try this out, and it honestly took me at most 5 minutes to get it completed and I had all the data available to me and ready to use. The other great thing, is that you can still go and create your own reports and add items to your dashboard as required. So it is not locked down, and not allowing you to customize it!

I think that for certain data connectors this is really a smart move, because it allows people who do not understand the underlying data, to be able to connect to specific data and then interact with the data to start getting an understanding of how they can best utilize their data and enable their business.

Another thing that I also noticed is the way that they have embedded images, as well as created the calculations. While you can do some of this in the current version, I do not think currently you can customize it to the extent that they have. But what it does do is to give us a little insight to what will be coming up in the future!

Here is a screenshot of what I am talking about! And if you can do this already then please let me know!

Credit: www.powerbi.com

Here are the details if you want to read up more about it: Visualize and Explore your Google Analytics data with Power BI

That is it for my first non-technical blog post, I do hope it was an interesting read, and if anyone has any suggestions please let me know on how to improve this or things to have a look at in the comments section!