So after last week, and the annual Microsoft Ignite conference, there was a whole stack of product announcements and updates. This was really great to see, and it once again amazes me how quickly Microsoft is able to get this to market. And being a Microsoft BI enthusiast, it excites me.

I mean who does not like to play with the latest and greatest technology, as well as potentially be ahead of other players in the BI space.

So here are the updates and the BI related content for this week. I did download and go through some of the video’s from Microsoft Ignite, which had a wealth of information.

If you want to see all the available video’s here is the link: Microsoft Ignite 2015 – Channel 9

Excel 2016

There has been a whole lot of updates and additional functionality which has been put into Excel 2016. I am currently running the Preview version and I have to say that some of the new features even though they might seem small are really good.

I am not going to go into all the features that they have, but just the ones that I consider to be important!

Searching in your Pivot Table Field List

I have to say that this is very welcome. And that even when you have a pivot table, with a rather small amount of fields, I have often found myself scrolling quite a bit. And often scrolling past the field that I want, and then having to scroll back up again.

To be able to search for your field, makes the usability that much easier, which I feel, then makes the use of pivot tables that much better.

Below is a screenshot of searching for Full Name in my data set.

Making changes to Power Pivot Model and Measures

What happens now is if you make a change to a column name, calculated column name, measure or table name, it will not break your existing reports and pivot tables. I have to say that this is a massive improvement and I am sure something we all have been waiting for. I know there has been a lot of times in the past, when I had to go back and make a change, and then what ever change I made, my reports and pivot tables would no longer display the changed information. So I would take screenshots, so that I could fix the errors.

I have already used this, and at first I did not appreciate it, but when I did notice I was amazed. I have to think that they are now using an underlying system to create the mappings, so that when we make a change to the actual name, we are not breaking the underlying relationship!

Deferred Refresh

Another thing I noticed when working in the Power Pivot model, is that they now have what they have called is deferred refresh.

In a nutshell what this will be is that they will only refresh the data once you exit the Power Pivot Model. Which is great, and I am sure will make the modelling experience that much smoother and quicker. I have to admit it was a pain the past, to create a new calculated column or calculated measure, and have to wait for it to load. Especially when it was wrong, and you had to fix it and then reload again.

New Forecasting capabilities

I have to start by saying that these are not the same calibre forecasting that you might get with SSAS, or Azure ML (Azure Machine Learning), but they are really handy for the Excel pro.

The thing that I really liked about the forecasting, is that not only can you forecast forwards, but because it is in Excel, you can drag the formula backwards, and see how close it is to data where you already have a result. I think that this is fantastic, because it means you can get a certain level of confidence on how accurate it will be going forward.

Below is a screenshot where in Orange is the forecast, and the blue line is the historical values. And you can see that there is an overlap with the orange and blue. This is where they dragged the forecast backwards.

Here is a link to more details on the new forecasting functions: Describing the forecasting models in Power View

Time Grouping

This is another great feature, and I also viewed another video, where they were talking about the time grouping as well as automatically creating the default date related columns, when you have a Date column in your Power Pivot Model.

So what the guys have done is if you right click and select Group, and then select what you want to group it by. It will then create those columns for you in your Power Pivot Model. I think this is great, as a lot of people who use Excel, know how to group their data. But when they now want to use that grouping in another report, they have to do it all over again.

Now that it is part of the model, they can just pick it from the list.

Below is a simple example where I created some sample data, and then grouped it into Months and Quarters. You can see under the covers that it created a Month Index column.

I also remember hearing in another video, that the guys are looking to implement a Date Calendar table under the covers for when they identify a Date Column. This is once again a good thing, especially for when you have people who do not have the expertise, or do now know how to create a Date Calendar Table. I also did hear that they will give you the option to use your own table if required. As well as potentially looking to see if they can give the user the ability to select specific Date Calendar tables.

New Chart Types

They have added new chart types to Excel, namely the Treemap, Sunburst, Histogram, Box & Whisper and Waterfall. While I know that these are not really new chart types created by Microsoft they are an additional welcome to be able to use them within Excel.

I have to say that they look really amazing in Excel, when compared to some of the images in the links above.

Power BI Updates

Once again there are some additional Power BI updates, that were announced last week, but I had already posted my blog, so here they are.

New Connectors within Power BI

I was reading through twitter and saw that it appeared that there are new connectors within Power BI.

Azure SQL Database

This is really an interesting connection, due to the fact that it has a direct connection to your database.

So what this means is that it does not store any data within Power BI, but sends the query back to your Azure SQL Database and then returns the data back to your report. After reading some of the information, it does appear that there are some limitations.

But one of the things that I do like is that the tiles will automatically be refreshed every 15 minutes. So if you have a live production database, this means you can get a view to see your data in near real-time.

One thing that I would highlight, is that the performance on the report, might be affected if your Azure SQL Database has been setup on a lower specification of hardware. So something to potentially keep in mind.

You can read more about it here: Using Power BI to visualize and explore Azure SQL Databases

SQL Database Auditing

After a quick read this is to connect to your database auditing to see the activity and all related information on your Azure SQL Database

You can read more about it here: Azure SQL Database Auditing connector for Power BI

DAX with Variable Support

Once again I think that this is really a great thing, it will definitely help make the creation and use of DAX measures that much easier.

And from what I have read, I have also seen, it actually makes the DAX calculation that much easier to use and to read through.

You can read all about it here at Kasper de Jonge post, which explains it very well: DAX now has variable support!

Power BI App for Windows

After installing and playing around with the Power BI App for Windows, I have to say that it has a very similar look and feel to the Power BI App for IOS. Which makes sense to keep the experience very similar if not the same.

I did enjoy it, that after connecting I could then see my dashboards and reports. And then after interacting with either the dashboards or reports they were super quick.

I also liked the idea of using this instead of going to the website. Even though it is only a few extra clicks, to have it running as an application, just means it is that much quicker to access, and to get to see the data that I want, as easily and as quickly as possible.

Here is a screenshot of the landing page when you open it up:

And here are more details: The Power BI app for Windows is now available

Scheduled refresh of Power BI Designer Files

There was mention that the guys at Microsoft are working on also enabling the scheduled refresh of the Power BI Designer files. I think that this would be welcomed by a lot of people. Due to the fact that they use and create their reports using Power BI, which by using Power Query, contains a link to their source data.

So I am going to assume that it would mean that the reports can then be refreshed from the Power Query source.

Azure SQL Data Warehouse

I know that this has also been mentioned quite a lot, but I just wanted to quickly give my idea’s on the Azure SQL Data Warehouse. Especially after watching the video Azure SQL Data Warehouse: Deep Dive (It is an in depth presentation, and if you want to find out how it all works, then this is the video to watch)

Once again the way Microsoft has designed this product, and made it really easy to use is a smart move. When you compare it to the other offerings it is really ahead of the competitors. Along with this they have based on the APS (Analytics Platform System), which has been in production for a long time, and they have a lot of experience, which they can now leverage in the cloud.

The one thing that I really enjoyed was their example where they helped a large retailer, who had a query running for 1 week, and by changing it to use a very similar or same technology as the Azure SQL Data Warehouse, and optimizing it they got it to return the same query in 1 minute. Now if that was my business and they could make such a massive improvement I would be over the moon!

I think that this could be a game changer for some large businesses, due to having the flexibility in the cloud, and only paying for what you use, and when you use it. Let’s be honest, the storage in Azure is really cheap, and coupled that with the ability to scale your compute as required means if you need the query to run faster, you can increase your compute (DWU) and it will increase proportionally to how you have increased it. Well this is what was explained in the video.

I cannot wait to get my hands on this and test it out, to see how easy it is to get data in, then to run queries and test the system to see how it works in the real world!

You can read more about it here: Top reasons why enterprises should choose Azure SQL Data Warehouse