BI-NSIGHT – Power BI (Collaboration, Mobile, Webtrends, Natural Language, Resizing Reports, Increased Limits, Dynamics AX Support) – SQL Server 2016 CTP 2.3 – APS Update 4 – Office 2016 (Updates, Publishing Directly to Power BI)

There is a whole stack of updates with regards to Power BI this week, as well as some other updates within the BI space.

I have to quickly point out that I was very fortunate to finally meet Jen Underwood. t (Twitter) It is really great to have the opportunity to have meet up with people that you have followed for so long, read up on all the great things that they have done. And the to finally meet them in person. I do not think that there are many industries where you can actually do this.

Ok enough about me and being star struck, let’s get back to all the BI goodness from the past week.

Power BI – Collaboration

I have to say that I think that this is a really great feature. Because you have already created the workspace within your Power BI Environment, ideally you want to know what other people are thinking and saying. And ideally you want to also be able to view the report to which they are referring to. This is a great way to keep everything in once place.

You can find more details about the Power BI Collaboration here: Collaborate with your Power BI group

Power BI – Mobile Updates

The Power BI team sure have been busy. In this past week they have also updated their Mobile app for Power BI. It is great to see that Microsoft is investing a lot of time and effort into the Mobile area. This really helps when you are showing people how awesome Power BI is, and you can just take your mobile device out of your pocket and quickly show them a Power BI report.

They have made a lot of changes and updates. The thing that I really like is that you can view the Excel files that are directly connected in your Power BI, as well as having the custom colors that you more than likely spent a little too much time just getting right. But at the end it makes for a great looking report.

You can read about it here: What’s new on the Power BI Mobile apps?

Power BI – Content Pack Webtrends

Here is another great Content Pack from the Power BI team and their partners. It is really great to see so many content packs available and I am sure that this is only the start. I am sure that at some point in time, there will eventually be a case where there will be a content pack which will make your life and the people who require some type of reports really happy.

The Webtrends content pack is a great way to see the visitors journey across all the different platforms that Web Trends monitors

You can read about it here: Visualize your Webtrends data in Power BI

Power BI – BI Weekly Service Updates

In the Power BI, BI weekly updates they have put in some great new service features.

The first one is that now they have put some more great work into the Q&A Natural Language engine. As per the blog post below, it now means that it makes it easier to see what other people have been asking in terms of questions. As well as to see what other people have pinned to their dashboard based on what Q&A questions were previously asked.

Next as per the screenshot above is the ability to change the view of your report. This is really great as everyone does not have the same screen size or resolution. So now you can change the view of your report, and then ensure if you know that it is going to be consumed by the mobile audience that it all fits in and looks good! We all like good looking reports.

And finally is that they have increased the limits for the data sets (200) and reports (200). Which is really great. Currently the only downside is that you will have to do quite a bit of scrolling if you have either that many datasets or reports. But I have no doubt that Microsoft will make this easier in due course.

You can read about it here: Power BI Weekly Service Update

Power BI – Dynamics AX Support

In my new role we are using Dynamics AX, and it sure would be great to have direct connectivity into Microsoft Dynamics AX.

It would mean that we could then create reports directly out of the Microsoft Dynamics AX cubes, which means that the users who need this vital information would be able to get the information that they require, when they want it. As well as across the different platforms.

I cannot wait for this to come out.

You can read about it here: Dynamics AX

SQL Server 2016 – CTP 2.3

Unfortunately, no nice picture for SQL Server 2016

But what I do want to highlight is that we are finally getting to see that BI is getting some love and attention in SQL Server 2016. I had no doubt that it was being worked on, but it is always nice to see something. It often gets me thinking of how best this can be used.

I would say that almost all of it relates to SSAS (SQL Server Analysis Services) Tabular and DAX. Which is great to see that SSAS is finally getting some attention. It is really a great product that is used on a daily basis On Premise. And to see that some of the features and work that has gone into Power BI, is making its way back to On Prem.

You can read about all the updates and details of SQL Server CTP 2.3 here: What’s New in Microsoft SQL Server Analysis Services Tabular models in SQL Server 2016 CTP 2.3

APS – Update 4

With regards to the Analytics Platform System, it is amazing to see how far the product has come. And it does appear that they are also starting to work at light speed, as they are doing with Power BI and SQL Server.

There are some great new updates which I will highlight here, with regards to PolyBase/Hadoop enhancements, T-SQL compatibility improvements to reduce migration friction from SQL SMP, Performance and OEM Hardware on AU4

You can read about it more in detail here: Analytics Platform System Update 4 Generally Available

Office 2016 – Updates

There has been a whole host of new updates and additions to Office 2016. And especially to Excel.

It is really great to see so many new features that are being incorporated and developed. To me personally it shows why Excel is used by so many people across the world, and with this continued investment they will continue to use Excel.

There are so many updates I cannot cover them all here but the three things that I want to highlight quickly is the ability to use your existing data to forecast what could potentially happen in the future. I think that this gives some great insight and is really easy to use.

Next is all the new chart types, which I know people have been asking for, for a long time. As well as being able to use them to show the data in a relevant manner. As we all know it is a lot easier to show things as a picture.

And finally is the ability to publish your Excel Workbook directly to Power BI. I think that it is really great and a smart move, because it allows the users to be able to take all their hard work, and then give them the ability to then leverage Power BI, enabling them to not only create amazing visualizations, but to also have a platform to share, collaborate as well as enable viewing on a mobile device.

You can read all about it here; What’s new for business analytics in Excel 2016

BI-NSIGHT – Power BI for Android Devices – Power BI Designer vs Excel – Power BI Refresh for On-Premise – Power BI Refresh from OneDrive – Power BI Interface Updates

Whilst I thought that this week might be a bit on the quiet side, I think that my previous suspicion is correct, and the wonderful people from Microsoft like to send out updates on a Wednesday.

Power BI for Android Devices

I was just browsing through twitter and I came across a tweet and clicked on the link, not entirely sure what it was about.

But it lead me to the Google Play store and I now could see the following below.


And here is the URL: Google Play Store – Microsoft Power BI

Power BI Designer vs Excel

I was reading another fantastic post by Rob Collie, where he gives some great insight for Power BI Designer vs Excel Where Rob mentions how Power BI can help out the Excel Pro’s, specifically in point number 7. What is assumed here by my interpretation is that in the future, Power BI Designer will be able to import a Power Pivot Model into Power BI Designer. I think that this would be fantastic as often I have created something in Excel, using Power Query and Power Pivot. And then a light goes off in my head, and I think this would be great to visualize in Power BI (Both for being in the cloud and mobile functionality). But when I have tried to import the Excel file, currently it only shows you any Excel tables. I do have a work around for getting your Power Pivot Model in Excel exposed within Power BI, which is the following and something that I have mentioned before. So here is the quick way to get this done.

  1. It is quite simple.
  2. All that you have to do is to create on Power View sheet within your Excel file, save your Excel file and then upload it into Power BI directly or via OneDrive.
  3. Once it opens and you go into creating your report, you will see the entire Power Pivot Model exposed!!

Give it a go, it is really quick and easy to setup. You can read Rob Collies post here: Power BI Designer vs. Excel: What’s Microsoft Up To?

Power BI Refresh for On-Premise

This is something that I have been waiting for as well as voted for some time ago. And it fills me with great pride to see this being implemented. It shows that the people at Microsoft are hearing our voices loud and clear. I have to say that this is going to be a real game changer in my opinion for the following reasons:

  • Firstly it is so simple to install and configure the Personal Gateway on your PC. Due to the installation being so simplistic, as well as not requiring Administrator privileges it means a lot more people will be able to install it and use the functionality.
  • Next is they have updated how often you can refresh your data.
    • The have chosen the magic number of 8.
    • Which to me represents your 8 hour working day.
    • Perfect to get hourly updates.
  • Integration with your domain account.
    • As we know a lot of sources integrate with your domain account, for single sign on, as well as easier controls on security.
  • Refresh history, and sending emails on failure.
    • This is another great feature that we are used to having, in terms of getting emails when things break.
    • I do like it, that it will only send you an email when it fails. So no need to clutter up your Inbox.

You can read in more detail here about all the details: Refresh for on-premises sources is here!

Power BI Refresh from OneDrive

This is something that I did figure out a while ago, which I did blog about previously. It entails using Power Update to update your Excel workbook for On-Premise, then Power Update uploading the updated workbook to OneDrive, after which Power BI will automatically update. You can read about it here: Automating refreshing of Power BI Reports and Dashboards with Excel Workbooks and Power Update It is great to officially have some more information. And a lot of companies are starting to use OneDrive as their document storage solution. So now we know that it will update your OneDrive files connected to Power BI once every hour. Which in most instances is pretty good? Another thing to note, is that from my workings with SharePoint online, it actually uses OneDrive to store all the documents. So if you have SharePoint online, you effectively have got OneDrive, which you can then connect to Power BI. (Disclaimer I am not 100% certain which subscription to SharePoint Online this is for.)

Power BI Interface Updates

From what I could see there are two additional updates within Power BI. The first is the change of the new Minty Fresh theme, which is good to see. I would not be surprised to see an option later to select your own themes as you can do with Power View in Excel. The second update is within the Dashboards in Power BI. When you click on the Tile Details button you now get an additional option as shown below You can now set a Custom Link, which I think is really great, because this could link to anything. Which gives the report developer more options, as well as allowing the Report Consumer to be able to interact with an additional dataset which is outside of the report.

BI-NSIGHT – Power BI Designer Refresh – SQL Plugin for Excel – SQL Server 2016 In Memory Tables – Azure SQL Data Warehouse – Office 2016 Update – Power BI Content Pack (UserVoice)

Another week has gone by, so let’s get into what is currently out there and happening in the BI world.

Power BI Designer Refresh

I do know that this came out last week, but I still wanted to comment on this.

Once again this is something that customers and myself have been waiting to be implemented and asked for on the forums.

For me personally this is really fantastic, because it gives me the opportunity to be able to get access to my data, shape it and then create my report interface using Power BI Designer. And through every iteration it is getting better, easier and smarter. And now with the ability to easily upload this into Power BI, and if you are using one of the supported data sources, it is simple as a few clicks in order to get the refresh up and running.

This really makes it easy for the people creating the reports, and with the future releases on the way, I personally see this becoming a valid competitor to the other visualisation tools that are currently out there.

You can read all about it here: Announcing Refresh Support for Power BI Designer files in the Power BI service

SQL Plugin for Excel

I came across this from reading through my twitter feeds.

I have to say that it does look very interesting. It appears that this can be used to pretty much have a SQL like interface when using Excel.

It also does appear that there is the potential to write data from your Excel workbook, back into SQL Server, via temp tables. This might be very handy, because I have had situations in the past where I have wanted to extract some data from a SQL database, in order for the users to make some changes. And then struggled to easily get the updated data back into my database. I could think of a method to be able to do this via the temp tables, and having a process to import the data from the temp tables into my database. Then once I have it in there, it would be easy to update the required changes!

More information here: thingiequery

SQL Server 2016 – In Memory Tables

Last week at the local (SSUG) SQL Server User Group, we were very fortunate to have some of the program managers from the SQL Server team present to us what is coming up in SQL Server 2016.

What I wanted to highlight is something that as far as I can see, so far has not really been highlighted.

It has to do with the In Memory tables in SQL Server 2016, and that Microsoft have almost resolved or allowed almost all of the query syntax is now available against In Memory tables. I do remember when I did try this before on SQL Server 2014 for my data warehousing loads, whilst there was not a big performance improvement, one of the issues was due to the fact, that there was a whole host of query syntax that I could not use.

Now with this almost having been resolved, as well as generally improving the In Memory tables, this is something that I am going to look at implementing where required in my data warehouse loads. Ideally I would imagine where there is a section of work, where it needs to be processed really quickly and having all the tables in memory means that this could potentially have a big performance improvement.

You can read about what is supported in SQL Server 2016 In Memory Tables here: Query Surface Area in Natively Compiled Stored Procedures

Azure SQL Data Warehouse

I am super excited about Microsoft starting to roll out the SQL Data warehouse preview in Azure.

I do think that this is something that is going to be a key driver for businesses to look at Microsoft for their cloud strategy going forward.

In the past few months, or closer on a year now, I have started to really enjoy how Microsoft has changed, and become a really forward thinking company, where they are listening to their customers and where they can taking the customers’ requirements and putting this into their products.

Along with this, especially in the cloud area, they are able to release and update their software at a pace that I have not seen ever. And when comparing it to other cloud vendors it is amazing how quickly they are able to do this. And for me as a consumer of these clouds services, and other services, it makes my work every interesting and keeps me on my toes!

I look forward to testing this out and see how it performs with my own data, when I can get access to it, and see if it lives up to all the hype!

You can read about it here: SQL Data Warehouse

Office 2016 Update

There is now an official blog update from Microsoft with regards to what they have done with Office 2016.

It is great to see what they showcased at Ignite to already be available for people to look at and test.

You can read more about it here: Office 2016 Preview—update 2

Power BI Content Pack – UserVoice

I am pretty certain that Microsoft is releasing a new content pack every week. When I log in and view what new blog posts are out there almost every week without fail there is another new content pack. Which is fantastic.

This week the content pack is for an application called UserVoice, which is allows it’s customers to provide feedback on their products. As well as for customers to share their experiences. And it looks like a really easy and simple interface to use. Which at the same time is very powerful.

You can find all the related information here: Monitor and Visualize your UserVoice Data with Power BI

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!

 

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.

Power QUery – Creating

Hi there, it has been quite a while since I have posted anything. After moving country and getting my first job. I have subsequently moved to my second job which is going really well.

So I have started working on all the new BI Features that Microsoft has been offering and below is something that I needed to do, is from our Date Table, I had a requirement to create the following Month-Year (Jan-15). This was so that we could then join to my source data.

I did the following steps below in Power Query

1.       I went into Power Query and clicked on the Add Custom Column

a.       clip_image002

2.       And then I put in the following in order to get my Month-Year

Date.ToText([Date], “MMM”) &”-“&Text.Range(Number.ToText([Year]),2,2)

a.       clip_image004

3.       I then clicked Ok and got the following as shown below

a.       clip_image006