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 Desktop (Monthly & Service Update) – Excel 2016 (Add-In for Machine Learning) – Power BI (Sankey Visual, Power BI Community, Office365Mon Content Pack)

As this year begins to really wind down it is good to see that the Microsoft machine also appears to be having a break.

I have no doubt that in 2016 it will be full steam ahead and back into the swing of things.

And before I could finish this blog post I checked if there was any news and low and behind there is a MASSIVE update for Power BI Desktop and the Power BI Service.

I won’t lie there are so many good and improved features that put down what you are doing and read below. It will sure make your week!

So let’s get into it!

Power BI Desktop  – Monthly & Service Update

Wow there is a lot of information in the blog post from Microsoft.

I am not going to go into all the details, but I will go into what I find especially great improvements or additions.

The first section with regards to the Formatting Pane & Ribbon really does have some great features. What I like is that you can now change the text size, the number of decimal places and formatting of data labels per category or report or item.

I also like the fact that you can now align your charts, reports or whichever report item that you insert. I personally know that previously this took a lot of time and effort to get this working. As well as the format painter option which means you can apply the same formatting across the entire report. Which will not only save time, but in my opinion make the reports look even more polished and professional.

In the next section with regards to the Enhancements to Visualizations they have done a lot of work. From ensuring that the visual cues are easier to see and understand. As well as improving how the data labels are displayed so that the actual charts show more meaningful information.

I see that they have snuck in another Visual, which could be used in quite a few ways to show valuable information. As well as adding the tooltips which I have already seen and used in the Power BI Service, and something very similar in the mobile app. This does make interacting with the reports and looking at the right information a lot easier and quicker.

Next is the ability to create visuals based on R scripts. I already know that the SQL Server stack is getting a lot of praise for including this in their next release. And it is really great to see it making it into Power BI also. I have no doubt that this will get a whole lot of interest with regards to R. And by the looks of things they have made it really easy to get the R scripts and information into Power BI.

In the Data Modeling area, they have once again added a lot of functionality. I like that they have added the relationship view, which means that it makes it easier to navigate when you have a large set of tables with relationships.

I am sure that there will be a lot of people will be very happy to see that SSAS Multi-dimensional is getting some love. And that now after you enable the preview feature you can now use your Hierarchies in your Power BI report. This will go a long way to get report authors to very quickly create some amazing reports.

They have also sneaked in another two connectors. Namely Stripe Connector which is for manage their online payments. And Smartsheet Connector which will allow users understand how Smartsheets is being used in the ways of workspaces, sheets, top collaborators etc…

The Enter data, was something that I possibly had thought of in the past that would be useful. For instance, you might have some reference data that you know is not going to change. And before today you would have to put it into a CSV or a table to get the data into your model. Now you can quickly do a copy and paste and your data is there. What a fantastic thing to have at your fingertips!

I see that they have made more improvements with Direct Query, which is great to see, considering that if your underlying source system will be SQL Server 2016 in the future with Column Store Indexes or In-Memory tables, this gives you the ability to have near or real time data in your reports.

Those are the details which I think are really important and valuable.

Below is the entire list of updates:

  • Report Authoring
    • Formatting Pane & Ribbon
      • Format data labels per category series
      • Change number of decimal places showed in visuals
      • Change text size in visuals
      • Ability to lay out visuals accurately: alignment, distribute, size, position (requires Power BI Desktop for authoring)
      • Set styles across multiple visuals through Format Painter (requires Power BI Desktop for authoring)
    • Enhancements to Visualizations
      • Visuals cue for sort state in Table visual
      • New Visual: Stacked Area chart
      • Smart tooltips for Area and Line charts on hover
      • Ability to create Reference line/region for a Cartesian visual
      • Improved data labels for pie and scatter chart
  • R Visuals integration in Power BI Desktop (Preview Feature)
  • Suggested table to table relationships when trying to create visuals involving two tables which are not related (Power BI Desktop only)
  • Optimized “Home” ribbon layout in Power BI Desktop
  • Data Modeling (Power BI Desktop only)
    • Relationships View
      • Zooming Slider
      • Fit Zoom to Screen
      • Reset Layout
      • Ability to Zoom in using Ctrl-Mouse selection rectangle
    • Data Connectivity (requires Power BI Desktop for authoring)
      • SSAS Multidimensional support – Hierarchies support (Preview Feature)
      • Stripe Connector
      • Smartsheet Connector
      • “Enter Data”: Paste or enter data to create a table
      • DirectQuery Improvements: Support for all data types of T-SQL and SAP HANA, resulting in Performance improvements.
      • ODBC Connector: Support for selecting User/System DSNs
      • CSV Connector: Ability to specify Column Delimiter in the Source dialog

You can find the Power BI Desktop Blog update here: More Power BI Feature Updates: Power BI Desktop December Update and new Power BI service features

Excel 2016 – Add-In for Machine Learning

It is great to see that they are putting some of the Machine Learning (ML) funcationality into Excel. Which as explained as per the blog post, means that you can then take this data and very easily do sentiment analysis on your data.

Which I know can provide some very insightful information.

They have also made it really easy and simple to use. And I can hopefully see myself using this in the near future.

You can get all the details here: General Availability of Free Excel Add-In for Azure ML

Power BI – Sankey Visual

Once again this is very good and insightful visual that has been created.

It appears that it is used primarily in the energy industry but I think that it could have quite a few other uses for all different types of data.

You can find out the details and how to get it from the following blog post: Visual Awesomeness Unlocked – Sankey diagram

Power BI – Community

This is a quick blog post to let people know that there are more and more Power BI community groups popping up all over the world.

I think that this will be a great way to find like-minded people. As well as to see what other amazing things people are doing in the Power BI space.

So I suggest that you either join or create a local group.

You can find more information about it here: Microsoft Power BI Community

Power BI – Office365Mon Content Pack

This is another great and insightful content pack.

To me when using the cloud services, you often do not have a clear visibility in terms of what has been happening with your actual service that you are receiving.

This content pack gives you all that information at your fingertips.

I also have to admit in my opinion that it is really great to see that Microsoft are making this information available. To me is shows that they know that they are not perfect and that at times things do go wrong. But hopefully not every often. And this gives you a way to see if or when something happened.

You can find details about the content pack here: Analyze your Office365Mon data in Power BI

BI-NSIGHT – SQL Server 2016 (CTP 3.1, SSAS Tabular) – Power BI (Cortana, Mobile App, Enterprise Gateway, Bing Content Pack, API Updates, Sentiment Analysis) – Data Insights Summit – Power Apps

Once again this week there has been a lot of activity in the BI space. Which is great and is definitely keeping me on my toes!

SQL Server 2016 – CTP 3.1 & SSAS Tabular

This week Microsoft released a small update to SQL Server 2016, and one of the features that I am looking forward to testing is upgrading an existing SSAS Tabular project to SQL Server 2016.

I am hoping that there will be some performance improvements, as well as making it easier to use the model now that it is based on JSON.

There were some additional updates that were released as part of the SQL Server 2016 CTP 3.1 which will be in the links below:

You can find out about the Analysis Services Updates here: What’s new for SQL Server 2016 Analysis Services in CTP3.1

And you can find out about what is new in SQL Server 2016 CTP 3.1 here: SQL Server 2016 Community Technology Preview 3.1 is available

Power BI – Cortana Insights

Once again the Microsoft amazing machine has released a whole host of changes since last week.

The first one that I am going to talk about here is the integration with Cortana. I think that this is a good move, as it seems that the world is moving towards using speech to do things, instead of having to type things out.

This is also another great wow factor for showing how powerful Power BI will be. And this means another way to quickly and easily find out insights from your data.

I also have no doubt that this will link up with the Cortana Analytics suite and will make things even easier in the future.

You can read all about it here: Announcing Power BI integration with Cortana and new ways to quickly find insights in your data

Power BI – Mobile App Update

Once again there is another batch of updates to the Power BI App, which is great to see. I know personally from my side that this will be used a great deal going forward.

What I like about this update is that they are focusing on both the iPhone and Android platforms. The one thing that still amazes me that they do not have an app for their Windows Phone which I would think would be good to have.

Along with this having the charts showing a more detail I think is the way to go. The resolution of the phone screens are often higher than what you can get on many monitors. So it can easily and clearly visualize your data and insights.

The other feature that I also like is the welcome page, as often people who are starting to use the app or are wanting to find information often need a starting point from which they can navigate from. I think this is especially the case when there is the potential to have a lot of dashboards. And this would make the experience seamless.

You can find out about the Mobile Updates here: Power BI mobile apps update – November 2015

Power BI – Gateway for Enterprise Deployments

I am very excited to see that as promised or eluded to there now is a Gateway for Enterprise deployments. I can already see in my organization that potentially having to have multiple gateways installed, and then to still configure who has access to what could very quickly spiral out of control.

This is where the Enterprise gateway over time will come into it’s own. I also like the feature that you can see the usage. Which will be very useful, because in my mind if this shows the usage stats as to what cubes or SQL Servers are being used will mean we can see if it is actually being used or not at all. As well as simplifying the deployment of how we manage the connection between the cloud and on premise.

I know that right now it is in Preview as well as only allowing a direct connection to SQL Server, but I have no doubt that over time this will become the required gateway and the Analysis Services Gateway will be replaced by the Enterprise Gateway.

You can find out the details here: Announcing preview of Power BI gateway for enterprise deployments

Power BI – Bing Content Pack

The Bing Content pack is a genius creation from the Power BI Team.

And the reason that I say this, is due to the fact that you can use any search term and it can quickly show you what searches have been happening relating to your search term. As well as showing some related news information.

I have already put it to use within my organization, and it just means another data source which in turn you can then use to quantify your data and what you are working on!

What I also enjoyed about this content pack is it is something you can put into use with immediate effect. It is quick and easy to get it up and running.

You can find out about the Bing Content Pack here: Search Analytics from Bing on your Power BI Dashboards

Power BI – API Updates

There are some great updates for the Power BI API, and whilst I am no developer it does mean that for the development team that I work closely with we can easily and quickly take our Power BI Reports and dashboards and integrate them into our existing applications.

This will mean that we can leverage all the hard work that we have done and have it in an application. Which then means that there is no need for the users to leave their application. As well as having the functionality that Power BI brings to the table.

The new API that was released today was the “Report integration API”

You can find out all about the Power BI APIs here: Power BI for Developers: Reports API and a simple App Registration experience

Power BI – Sentiment Analysis

I came across this blog post by Patrick Leblanc and I thought this was a really amazing blog post.

It really delights me to see how there are really quick and easy ways to integrate other solutions into Power BI, which then enriches your data. And I do think that having sentiment analysis is a great tool to see how people perceive your company or your product.

You can find out how Patrick got this done by viewing his blog: Sentiment Analysis with Power BI

Data Insights Summit

I really wish that I would be able to attend the Data Insights summit.

We are planning on using Power BI as our starting point for our BI journey and I do think that these two days in March would be invaluable not only to find out directly from the Microsoft team what is currently happening, but also what is coming and what is planned for the future.

As well as having the ability to network with fellow people who are also implementing Power BI solutions.

You can find out about it here: Data Insights Summit

Power Apps

Finally, but not lastly I do think that Power Apps is going to drive and change the way we get data from different sources as well as from different content area’s into a digital format.

I can already see how we can leverage this, to make things easier and simple for the users in my organization.

The other thing that I really like is that you can create the app once, but can leverage it on all the different platforms (Web, Mobile, Tablet). And to me this means that the adoption and the use of the app will be that much better used and will ensure it succeeds.

You can read about it here: Introducing Microsoft PowerApps

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

Dynamic SSAS (SQL Server Analysis Services) Tabular Role Security – One Role to multiple cubes.

It has been quite a while since my last actual blog post. I do hope that this will help people to use a simple security model, which is easy to implement and maintain. The idea is further down the line to have an application sitting above the security table, so that the right people can add and remove access without our intervention.

What we are going to explain below is to use Dynamic SSAS Role Security.

But we are also going to achieve the following:

  • You will also be able to Control that the users have access to the cube.
    • This will enable you to only define one role, and put in the AD Group once with everyone in this AD group.
  • You will be able to Control which user has access to which Dimensional Data
    • In this example User A will only be able to see information from the Countries USA and Australia

Example:

  • We had a requirement where we needed to simplify our SSAS Role Security.
    • It was sitting at already almost 60 SSAS Roles and it was only going to grow.
    • Along with this, there was performance related issues with so many roles.
    • Not to mention the admin headache of adding and removing users in multiple AD Groups, as well as having to find out which AD group in a particular role the user must be added to.
  • For our working example below, I am going to create a really simple scenario.
  • We have got Tennis Rankings.
    • We want DOMAIN\GilbQue to only be able to see data for the Countries from the USA and Australia.
    • We also want DOMAIN\GilbQue to be able to view this data in our cube called “Tennis Rankings“.
    • Then we will change DOMAIN\GilbQue who will also need to see the data for the same countries.
      • But DOMAIN\GilbQue must see data for another Cube called “Swimming Rankings
      • NOTE: We are not going to create another cube calledSwimming Rankings“. It is to show how the security will work when a user tries to access a cube to which they have potentially access to the Country Dimension table, but not to the actual cube.
  • With the above example it will allow us to have one Security Table in which we can control which users have access to which Cubes.
    • As well as if they can access the cube, to which Country information they can view.

Loading Data and Relationships

The first step is to load the data, and create the relationships

  1. The following tables below are our Dimension Tables.
    1. Country
    2. Cube Name
  2. The following table below is our Fact Table
    1. Tennis Rankings
  3. Then finally this table contains all our Security Information table.
    1. Security Table.
      1. NOTE: In the above picture you would need to change it to your Domain Login details.
  4. Then once we have created the relationships it will look like the following:
    1. We created the following
      relationships
      1. In the first
        column we created a relationship from our Fact TableTennis Rankings” and the column called “Country” to the same column nameCountry” in our Dimension Called “Country”
      2. In the second column we created a relationship from our Fact Table “Tennis Rankings” and the column called “CubeName” to the same column nameCubeName” in our Dimension Called “Cube Name
      3. In the third column we created a relationship from our Security Table “Security Table” and the column called “Country” to the same column name “Country” in our Dimension Called “Country
        1. NOTE: The reason for this is so that next when we create our Role and assign the dynamic Security that there is the required relationship.
      4. In the fourth column we created a relationship from our Security Table “Security Table” and the column called “CubeName” to the same column nameCubeName” in our Dimension Called “Cube Name
        1. NOTE: The reason for this is so that next when we create our Role and assign the dynamic Security that there is the required relationship.
  5. NOTE: When creating the relationships you MUST ensure that your dimension tables have all the related information in your Dimension Table.
    1. The reason for this is if your dimension table does not have a related value, then the Dynamic Role Security will not be able to Filter it out.
    2. For Example, in the above in our Country table we had no value for Serbia & Montenegro
    3. When I first tested my security below I was getting a blank row with some values.
    4. Once I then added Serbia & Montenegro I could only see my required or related information.

Creating the Role and creating the Dynamic Security

In the steps below we will now create our Role and configure the Dynamic Security

  1. In your Tabular Model, click on the Roles
  2. When the Role Manager Window opens, click On New
  3. We then configured it with the following as shown below:
  4. Now in the steps below is where we configure the Dynamic Security as explained below:
    1. Due to the reason that we want to ensure that both the Country and Cube Name are used to grant people the correct access to the cube, we are going to configure the security to both tables below.
    2. First is Country, where we will be adding the following DAX function:

      =Country[Country]=LOOKUPVALUE(‘Security Table'[Country],

      ‘Security Table'[DomainLogin], USERNAME(),

      ‘Security Table'[Country], Country[Country])

      1. To explain what we are doing above is the following:
        1. We are specifying that we are using the column name called “Country” from our “Country” Table in RED
        2. Next we are using the lookup function to then lookup in our “Security Table” the related “Country” Column. In GREEN
        3. Then it validates if in our “Security Table” the column called “DomainLogin” has a corresponding username, which is passed when we log in. in PURPLE
        4. And finally it then verified that the “Country” data in both tables matches. In ORANGE
    3. Then for our Security Table we want to set the Permissions to False.
      1. =FALSE()
    4. Then we go and once again do the same as step 4b above, but this time it is for our Cube Name table.

      =’Cube Name'[CubeName]=LOOKUPVALUE(‘Security Table'[CubeName],

      ‘Security Table'[DomainLogin], USERNAME(),

      ‘Security Table'[CubeName], ‘Cube Name'[CubeName])

      1. To explain what we are doing above is the following:
        1. We are specifying that we are using the column name called “CubeName” from our “Cube Name” Table in RED
        2. Next we are using the lookup function to then lookup in our “Security Table” the related “CubeName” Column. In GREEN
        3. Then it validates if in our “Security Table” the column called “DomainLogin” has a corresponding username, which is passed when we log in. in PURPLE
        4. And finally it then verified that the “Cube Name” data in both tables matches. In ORANGE
  5. So once it is completed it will look like the following:
  6. The final step is to click on the Members and ensure that you add an AD Group in which all your members will be a part of it.
    1. Click Add
    2. I then clicked on Advanced and then on Object Types I changed it to Groups Only
    3. I then selected Domain Users as shown below
  7. Then clicked Ok and then Ok again.

Testing the Dynamic SSAS Tabular Security

In the steps below we are going to test and see if this dynamic security now works.

  1. From within our model we click on the Analyze with Excel Button
  2. Then on the Analyze in Excel screen we select the Role we just configured
  3. Then click
    Ok.
  4. Now based on our Security Table configuration that we did above, I should be able to see data only from USA and Australia
    1. Here is the Security Table as it currently is
    2. Now when I opened
      Excel and dragged in Total Points and Country I saw the following:
    3. And the Pivot Table has the following:
      1. Which is what we wanted to see.
    4. Now if I had to go and change the above table and change the CubeName
      values to “Swimming Rankings“, what we would expect to see is no values from our cube.
      1. Due to the fact that the user does not have access to see the cubes. Which is related and contained in our Fact Table and Security Table.
      2. So our Security Table now looks like the following:
    5. Now when we browse the cube as we did in step 2 above we see the following in Excel
    6. And the Pivot Table has the following:
    7. Which is showing us what we wanted to see.
  5. The only thing to Note, is that if you give the user access to the cube name on either one or both of the rows in the security table the user will see all their related information.
    1. EG: You only gave the user access for USA, when they view the cube they would see both USA and Australia
    2. If you only wanted the user to see USA, then remove the line with Australia

BI-NSIGHT – Power BI (Desktop Update, Service Updates, API Updates, Mobile App Update, Visual Contest Results, Content Pack – Stripe) – Office 2016 (Excel Updates, Power Query Update, Excel Predictions) – SQL Server Analysis Service 2012 Tabular Update – SQL Server Analysis Services 2016 Extended Events

So this week there was once again a whole host of updates with Power BI, as well as finally the official release of Office 2016.

It sure is a busy time be in Business Intelligence space, especially in the Microsoft space.

So let’s get into it…

Power BI – Desktop Update

So I woke up this morning to see that there has been a massive release in the Power BI Desktop application. I immediately downloaded and installed the update. I have already used some of the features today.

And there was a whole host of updates, too many to go through all of them here, but I would just like to highlight the ones which I think are really great additional features.

I am really enjoying the Report Authoring features, and I have mentioned it before but the drill up and drill down features are really great and allows for more details to be in the report which you will not initially see on face value.

Then under the data modelling section I have to say that I am currently not any DAX guru, but I do appreciate how powerful it is, and how you can really extend your data with so many DAX functions. In particular is the Calculated Table, which Chris Webb has already blogged about and has some great information here: Calculated Tables In Power BI

And there are some great new features with regards to Data Connectivity as well as Data Transformations & Query Editor improvements, which all forms part of Power Query. Which once again enables the author of the reports to enrich the data, which in turn will create great visualizations.

You can find out all about all 44 updates here: 44 New Features in the Power BI Desktop September Update

Power BI – Service Updates

So yet another great update on the Power BI platform.

I think that finally being able to customize the size of the tiles is really good. So that you can fit more meaningful information on your dashboard.

Another great service update is to be able to Share Read Only dashboards with other users. This is great because often you create dashboard and reports, which you want to share with users and let them interact with your data, but not make any changes.

As well as having more sample content packs which will be a great way to showcase how powerful Power BI is.

You can read about it and all other updates here: Power BI Weekly Service Update

Power BI – API Updates

There is no pretty picture for the API updates, but there are some great new features.

The one that I think is really good is the ability to be able to embed a Power BI tile into an application. So at least it gives you the ability to have the great features of Power BI in your application without having to go directly to Power BI.

I also see that there is the ability from what I can see to pass some filters or parameters into the Power BI report via the URL which is really good and can prove to extend the functionality.

These are the details in the API updates

  • Imports API
  • Dashboards API
  • Tiles and Tile API
  • Groups API
  • Integrating Tiles into Applications
  • Filtering Tiles integrated into your Application

You can find out all about it here: Power BI API updates roundup

Power BI – Another Mobile Update

The Microsoft team must be working 24 hours a day with the amounts of updates and additions that are coming out from Microsoft.

They have released some additional updates into the Mobile application which are great, as we all are well aware that having a mobile application really can help showcase your solution. As well as ensure that it gets to the right users and that they can see the related information.

You can find out about the updates to the IOS, Windows and Android details here: Power BI mobile Mid-September updates are here

Power BI – Visual Contest Results – People’s Choice Awards

As you can see above this is the first people’s choice award for the Visual Contest result, which I can see myself using that with my existing data.

You can find out about it and the other entries here: Power BI Best Visual Contest – 1st People’s Choice Award!

Power BI – Content Pack Stripe

Once again this was another great content pack update this week.

There are a whole host of people who are using the Stripe platform payment for their online business. From the really small guys to the big guys. And this gives everyone a really great and easy way to understand and visualize your data. As well as what payments you are getting.

You can find out more about it here: Monitor and Explore your Stripe data in Power BI

Office 2016

I am very happy to see that Office 2016, has been released before the actual year of 2016.

I have mainly been focused on all the features in Excel, due to being in BI. But there are a whole host of updates, fixes and new additions in Office 2016.

You can read all about it and all the details here: The new Office is here

Excel 2016 – Features

With Office 2016 being released this blog post from the Office team shows a lot of the great features that are available in Excel 2016.

It does showcase a lot of great features focused on Business Analysts, as well as how people can leverage all the new features in Excel 2016.

You can read up all about it here: New ways to get the Excel business analytics features you need

Power Query Update

With so many things going on within Power BI, there has been another great release with Power Query.

As you can see with the picture above it is great to finally have the ability to write your own custom MDX or DAX query to get the data which you require from your SSAS source.

Another great feature is the ability to extract a query from one of the steps within your current Power Query query, and then you can use this in another Power Query window. As they say it gives you the ability really easily use the same code over again, without having to do it all over again.

You can read all about it here: Power Query for Excel September 2015 update

Excel Future Prediction

I recently came across a really interesting article from some of the Industry experts within the BI space, and for them to predict how they see Excel’s use as well as where they see it fitting into the BI space in the upcoming years.

There were some really insightful and interesting details, which made me think about how Excel has evolved over the years, and with the current additional investments going into Excel, how this is going to be leveraged and improved in the years to come.

You can read all about their thoughts here: 27 MICROSOFT EXCEL EXPERTS PREDICT THE FUTURE OF EXCEL IN BUSINESS INTELLIGENCE

SQL Server Analysis Service 2012 – Tabular Update

There has been a CU update for SQL Server 2012, and one of the great updates relates to SSAS Tabular for columns that have a high cardinality. Which was a performance issue before this release.

It is great to see that this has been addressed, especially due to the fact that in SSAS Tabular there will be cases when columns will have a high cardinality. And even though it is often super quick, we would like everything to be as fast as possible.

You can read all about the updates to SQL Server CU 8 here: Cumulative update package 8 for SQL Server 2012

SQL Server Analysis Services 2016 – Extended Events

I think that it is great to see that we are finally getting some additional features and updates to Analysis Services.

When I read up about the extended events, this is something really great to see. I actually have been in an exercise to monitor what has been going on our SSAS instance both in terms of performance, as well as which users are accessing the cubes and what they are doing. And currently there is not a super elegant solution to achieve this.

With the extended events this makes it a lot easier and gives you the ability to quickly get the information that you require.

I also love it that you are able to have a live query, which you can use to see if you are specifically running something. As well as if you want to ensure that you are capturing the right events.

This is definitely something that I will be looking to use when we finally can install and use SQL 2016.

You can find out about all the details here: Using Extended Events with SQL Server Analysis Services 2016 CTP 2.3

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 – Multiple Power BI Announcements – Cortana Analytics – Particle and Microsoft – Azure Data Catalog – Power Query Update

So the past week has been filled with a whole host of announcements, particularly in the Power BI space, but as well as with Microsoft’s cloud based solutions.

A lot of the information that I have found due to the current World Partner Conference!

So as we do every week, let’s get into the amazing details.

There is quite a bit of content this week, so please scroll down and then down again.

Multiple Power BI Announcements

So there was a whole stack of Power BI Announcements, which has been fantastic news for the BI community

Power BI General Availability – 24 July 2015

There has been a whole host of activity with regards to this fantastic news, as well as the blog post from Microsoft detailed below. So I will quickly give my personal view of this news.

I do think it is the perfect time for Microsoft to from a Preview product to General Availability (GA). It ties in with their release of Windows 10, and being summer in the United States I am sure it is an amazing time there. Also by Power BI coming out of Preview and into an actual product this gives it some additional credibility and traction within a very competitive market.

In terms of Power BI as a whole it has been a whole new way that Microsoft have released a product. Along with this they have actively engaged with the customers and end users and actually listened and often taken the advice from the community and put this into the product. I find this amazing and something that has not been done in the past. And this is where I feel going forward is where Microsoft will leap frog the other vendors.

I also think it is a VERY smart move to open the Visualizations and give the power to the developers. This will result in a massive increase in new charts that were previously never used or never thought of. And this all lends itself to people who want to implement their own charts to start looking at Power BI. Along with this it also continues with the Microsoft community, as I pretty sure without doubt that people will share their visualizations with each other. This means not only does Microsoft benefit, but also the customer or end user.

I might have totally misunderstood all the hype around the above, but I am sure somewhere that there was a hint that there might be a content pack for Twitter data. Now that would be something that would be amazing!

If you want to read about all the details and updates, please read their blog post: Announcing Power BI general availability coming July 24th

Power BI New Capabilities

Microsoft have been super busy and they have released a great blog about the new capabilities that have been released with the General Availability coming up on 24 July 2015

Below are the sections, and you can read the blog post afterwards for more details.

I would just like to highlight the Q&A Visual formatting. I think that this is another great feature. Due to allowing you to be able to format a Q&A question. And the reason that this works so well, is that you will have some questions that will be common amongst the business people. And in my mind you would want this to look really great. As well as potentially really POP when the question is asked. And this now gives the report developer the ability to do this.

  • Office 365 App Launcher
  • Custom URL Support
  • Storage Management
  • Q&A visual formatting

Read here for more details of the above: New capabilities added to Power BI

Power BI – Spark

Once again Microsoft have released another Content pack which I personally think keeps on elevating Microsoft in terms of competing with the likes of QlikView, Tableau etc.

By integrating directly with Spark on HDInsight it allows customers and end users to easily view their data that they have been processing within the Spark. In terms of Spark on HDInsight, that already has made the barrier of entry to use an appliance and software like Spark that much easier and quicker to deploy. Then along with this they plug this into Power BI, which then means that it is a great and powerful interface to visualize their machine learning algorithms and graph computations.

You can read more details here: Visualize big data with Power BI and Spark on Azure HDInsight

Power BI – Acumatica Content Pack

Here is another great content pack from Microsoft and Power BI.

Acumatica Cloud ERP is a cloud based platform that provides business management applications such Financials, Distribution, CRM and Project Accounting.

You can read more about it here: Analyze and Explore your Acumatica Cloud ERP data with Power BI

Power BI – Development of content pack for tyGraph

I read about tyGraph analytics getting a development pack for Power BI. This is really good news as it appears that tyGraph is used by a lot of customers. And by using Power BI it will enable the existing tyGraph customers to leverage Power BI for the reporting requirements.

You can read more about it here: tyGraph Analytics Is Announcing the Development of a Content Pack for Microsoft Power BI

Cortana Analytics

The Cortana Analytics Suite is once again why I really enjoy working within the Microsoft space. It appears to me that they are not only looking to create great cloud based products, but also innovate and create something that as a whole really makes sense for people to want to use and realize the potential of the data that they have, or data that they can create.

I really like the way they have integrated a whole host of applications, to make it easier and simpler for people to be able to use it as a product. And as with anything once you get to use a new product, get a better understanding of the product, you then want to be able to do more. And with Cortana Analytics Suite, they provide out of the box functionality, but going forward it also gives the customer or end user the functionality to dig in a whole lot deeper and create their own custom solutions.

I watched this video below and it is amazing to see how technology can enable people to make smart decisions which in turn makes us happier and healthier. In my view it also shows where this is going in the future. As well as how there are unbounding possibilities of how we can integrate things (IoT) and make quicker decisions than ever before.

Dartmouth-Hitchcock ushers in a new age of proactive, personalized healthcare using Cortana Analytics Suite – Watch the Video inspiring stuff

You can read more about Cortana Analytics here: Cortana Analytics

Particle and Microsoft

I think that this is another great partnership, especially the way we are starting to see the IoT happening and starting to gain some real traction. I also find that Particle is a great company that is enabling their customers to do things that previously were either too expensive to implement or were never thought about! And I am pretty sure further down the line, this will integrate with Power BI, or Particle will create an interface using the Power BI REST API.

You can read more about it here: Microsoft + Particle

Azure Data Catalog

This is another great cloud based product from Microsoft. The reason that I thought to put it in my blog, is that there are a lot of businesses that are looking to see how to manage their metadata catalog, as well as where the business information is stored. And this is where the Azure Data Catalog comes in.

This enables almost any business user to be able to put up some information about their data and where they got it from. As well as enriching their data source information with what they are doing with the actual data. This enables other people within the business to quickly identify where the actual source of their data is. I think that this will mean the more people that know where to get the data, the more the data will be used. Which in turn will lead to better insight into their data. Which will finally or potentially lead to business value!

You can read about it here: Announcing the Public Preview of Azure Data Catalog

Power Query Update

I just saw that there was another Power Query update for Excel.

There are a total of 6 updates listed below:

  • Improvements to ODBC Connector
  • Navigator dialog improvements
  • Option to enable Fast Data Load versus Background Data Load
  • Support for Salesforce Custom environments in Recent Sources list
  • Easier parsing of Date or Time values out of a Text column with Date/Time information
  • Unpivot Other Columns entry in column context menu

You can read about it here: 6 updates in Power Query for Excel

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 – Power BI Designer Update – Power BI Twilio Connector – Excel 2016 Visualisations – Azure Data Lake

Fortunately this week, the Microsoft machine was a little quieter, but still as expected new updated in the Power BI Space.

Power BI Designer Update

 

I have read quite a few blog posts, downloaded and viewed the Power BI Designer update. And I have to say they are some welcome changes.

In my mind I see that over time, they are porting some of the functionality within Power Pivot into Power BI Designer. Which is a great welcome, and whilst they are doing this, they are also making additions, such as the DAX intelli-sense, which is amazing. As well as giving the user to create a column within the Report screen. It makes the report authoring experience that much quicker and better.

I do think that the product is moving along in leaps and bounds. Especially for customers and users who do not have access to Excel 2013 and potentially Excel 2016!

You can read all about the updates here: Power BI Designer May Update

Power BI – Twilio Connector

 

Just a quick note, that there is yet another great connector for Power BI. I personally have not come across this before, but in my mind it is starting to show how Power BI is starting to gain momentum from other companies, which can leverage their reporting and product exposure by utilizing Power BI. As well as a great benefit for existing customers, to have a great easy to use as well as informative reporting platform.

I am certain that the Twilio connector, will show some companies a great insight into their data and real-time communications.

You can read more about it here: Visualize and analyze your Twilio data in Power BI

Excel 2016 Visualisations (Charts)

 

Example Treemap Visualisation below

There are some great new visualisations or new charts coming in Excel 2016. I did watch the Channel 9 video from Ignite which had more details and it did showcase the new charts and how they can enable you to view your data in a totally different way. I think that this is really welcome.

Here is a link to the video: What’s Next for Visualizations in Microsoft Office

As well as a great blog post by Clint Huijbers, in which he does a great job to show examples of the new visualisations (charts): New visualizations in Excel 2016

Azure Data Lake

 

I once again have to say that this is another smart move by Microsoft. In my mind this is a great product. The reason for that is now you could have the potential to run your entire organisations data within the cloud.

You could start by storing all your data, within the Azure Data Lake, thereafter if you require a data warehouse you could then leverage the Azure SQL Data Warehouse, from which you could then visualise your data using Power BI. This is a great story to be able to tell and to explain to potential customers as to why they should potentially choose Microsoft Azure.

In terms of the Azure Data Lake, from what I have read it is not to be confused with just storing your data in the cloud. It gives you the ability to quickly and efficiently get your required data out of your data lake. The great benefit is that you can store as much data as is required. You do not have to worry about the structure of your data, you can just store it in the Azure Data Lake, and then when there is a requirement to gain insights into your data, it is immediately available.

I also see that if you had to put as much data as you won, or could get your hands on, into your Azure Data Lake, when you start looking at gaining insights, it just means that you will have a whole stack of information at your fingertips. Which then means that you could potentially cross relate data, where previously you did not have it, or it was stored in another system that was hard to access, or to extract data. If you then have to couple this with Azure SQL Data Warehouse as the extraction layer, and run that through Machine Learning, you could potentially be onto something that competitors have not even begun to realize the benefits!

You can read about it here: Introducing Azure Data Lake