BI-NSIGHT – Power BI (Service Update 2) – SQL Server (Dev Essentials) – SQL Server 2016 (RC2)

As of the time of writing this, there was not a lot going on this week. And it is quite understandable with all that happened in the Power BI area at the Microsoft Data Insights Summit. So here are this week’s updates.

Power BI – Service Update 2

I have to say that the Service Update 2, has a whole host of new features some of which were announced at the Microsoft Data Insights Summit.

Starting with the Dashboards it is great to see that you can now have a featured dashboard, which can showcase or you can put in there what is most important to you.

Along with this is the ability to Filter your dashboards, as already in my implementation we can see that there are a lot of dashboards created and shared by other people. So this makes the navigation that much easier.

With the Enterprise features I have to say it is great to have Usage reporting, of the reporting system (Ironic that!) but it does mean that we can see who is using and doing what. As I often find that you create dashboards or reports which the users are requesting with great importance, and then after you complete your work they hardly ever use it.

Next is the Row Level Security, and as previously explained and discussed, this is now like having SSAS Tabular in the cloud. And I have no doubt that it will get better with every release. And I am sure that at some point we will be able to put in some form of Dynamic Security, which will be great. But this does mean that we are on the way to have a SSAS Tabular SaaS.

And finally the ability to stop people from exporting the data is also something that can be of a concern to the Enterprise.

The other features I have already blogged about before, but finally it is good to see that us people in Australia can now sign up for the Power BI Service.

You can read all the info here: Power BI Service March Update Part 2

SQL Server – Dev Essentials free

I know that this has been on twitter for quite some time, but is great to see that you can now use SQL Server Developer Edition for free. I think it is a smart move by Microsoft, because it allows you to use all the features, and no doubt once you have developed a solution there is the potential that you have used an Enterprise feature, which would then require an Enterprise License. But even with that said it is still great to be able to use it for development purposes.

You can find out the details here: Microsoft SQL Server Developer Edition is now free

SQL Server 2016 – RC2

RC2 is out and one of the big improvements is that they have simplified the process for the R Services setup. Which is great as from what I read previously it was a tedious process to get this up and running.

It appears to me that the SQL Server team is working at a cadence that we have not seen before. I have no doubt that what the Power BI team is doing is starting to rub off on different areas within Microsoft.

I am sure that a lot of the details within RC2 is around the underlying code and bug fixes. But with that being said it is great to see another RC out, and I am sure there will be a few more before it goes to RTM.

You can find out the details here: SQL Server 2016 Release Candidate 2 now available

BI-NSIGHT – Power BI (Upcoming Updates, Forecasting in Power BI, Q&A Updates, Row Level Security, Excel in Power BI, Cortana Integration, New Visuals, Power BI App on Apple Watch, Desktop Trend Lines, Desktop Drill Through, Desktop Conditional Formatting, Power Apps) – SQL 2016 – Part 2

Well with the Microsoft Data Insights Summit I was under no illusion that there was going to be a whole host of updates and great new features to Power BI. And it was great to see that I was not disappointed.

So here are the highlights that I thought would be great to mention for this week’s blog post.

Here is the link to the entire Blog Post from Microsoft, and below are my details from what has been released: Over 5 million subscribers are embracing Power BI for modern business intelligence

Power BI – Forecasting in Power BI

This looks like a really great feature because what it means in my interpretation is that we can now leverage Azure Machine Learning (ML) to help forecast or predict what will happen based on the data we have provided.

I do not know all the details, but I know that this will be very welcome and at least give an indication of where your data is going, either better or worse or the same.

I know that there are some other products that have this capability, but I do think that this can be optimized and over time become a lot more accurate.

Power BI – Q&A Updates

It was great to see that after the initial updates to Q&A, they now not only are providing the capability to improve the service in terms of making it easier to use. They are now giving the ability for Q&A to be run on Direct Query Data Sources.

I was not sure initially how this was going to happen in the past, but in my view I was sure that it would be a feature that would be created or designed.

I do think that having Q&A is not only a great option in terms of selling Power BI, but it also makes it really easy for users to just ask a question and get answers. Which they can then save and have as part of their dashboard. And this will be something I would even use when looking for a quick result. I can just ask the question.

Power BI – Row Level Security for Cloud and Direct Query Sources

This is really a great new feature, and as with my next highlight below what this shows is that Power BI is almost at the point of SQL Server Analysis Services (SSAS) Tabular is cloud ready or SaaS.

I know already that there are a lot of good reasons and processes to use Row Level security, so that we can re-use the one data source for multiple people and only show them the data that they need to see.

There is not a lot to talk about here in terms of how Row Level security works, because it has been around in SSAS Multidimensional for some time, but it is great to see it in the cloud or Power BI.

Power BI – Power BI Data in Excel

This leads on from the previous highlight that it appears now that Power BI is a SaaS, and that once it gets released you can use your Power BI Models in the cloud and interact with them using Excel.

This then means that you can use all the standard Excel functionality. As we all know Excel is the most widely used product from Microsoft. And people are very comfortable using Excel, as well as having great skills in what they do with the data in Excel.

Now this just gives them the capability to do this. As well as it means that you potentially do not have to create a Power BI Model and then an SSAS Tabular Model for Excel. It now can all come from one source.

Power BI – Cortana Integration

It is great to see that they are integrating more of Cortana into Power BI. And in my mind I would think that it is leveraging Cortana to convert the voice text, and then pass this to Q&A which in turn can then provide the required data. I could be totally off the mark, but I would imagine it is something along those lines.

I am sure over time that this will get smarter and more integrated.

Power BI – New Visuals

As you can see above Microsoft have released a whole host of new Power BI Visuals.

I do know that the SandDance visual got a lot of applause, and it does have some great applications that were designed with the required datasets.

I particularly like the Attribute Slicer, because it appears that this one gives you the ability to slice the data, but that it will not take up a lot of screen real estate, as well as giving the option to search for your attribute that you want to slice by.

I did think I read somewhere that now Power BI has more visuals for the users to use than any of the Microsoft’s competitors.

Here are more details of the SandDance visual: Visual Awesomeness Unlocked – SandDance

And you can to here to view all the visuals here: Welcome to Power BI custom visuals

Power BI – App on Apple Watch

Just a quick note to see that they will release an App for the Apple watch. This is great for people who like to have the updates coming onto their smart watches.

Power BI – Desktop Trend Lines

As it would happen they also show cased what is coming up in next release of Power BI Desktop.

And it is great to see that there is the ability to put in a trend line, which can often lead the user to understand the trend of the data that they are looking at.

Power BI – Desktop Drill Through

I personally know that this is something that I have been looking for, as well as very often people have said that Power BI is great but there is no drill through functionality.

Well now it is here and it is awesome. And I know that already when chatting to my fellow peers that they are happy and amazed that it has been done. Which means we can then leverage more of Power BI.

Power BI – Desktop Conditional Formatting

I know that often it helps people to better understand the data when there is some conditional formatting that has been applied.

It is great to see that we now have the ability to do this which will further enrich the Power BI experience.

Power BI – Power Apps

I have no doubt that as time goes on there will be a better or tighter integration with Power BI and Power Apps, as a means of getting data into Power BI which can then be used to further enrich the Power BI experience as well as the related data.

SQL 2016 – Part 2

In part to of the details of SQL Server 2016 they go into the SSAS improvements and new features.

I know quite a bit of this has been released before, but this is one central page where you can see all the improvements, to which there are many as well as additional features.

If you want to see all the improvements and updates you can read about it here: Enable business insights for everyone with SQL Server 2016: Part 2

BI-NSIGHT – Power BI (Publish in Web App, Enterprise Gateway GA, Desktop Update, Troux Content Pack, New Visuals) – SQL Server 2016 (CTP3.3, eBook, R)

So from last week to almost having nothing to talk about, to this week having a whole host of updates.

Just to quickly mention I attended the first local Power BI User Group meeting in Brisbane this evening ( QLD Power BI User Group ), and it had a really great turnout, along with some great content for the first user group. I have no doubt that it will grow from strength to strength.

So let’s get into the details there is a lot to cover here.

Power BI – Publish in Web App

This has been one of the most requested things that have been voted on. And it is great to see that they have listened again to what the people and users are asking for and have delivered it.

There are a few things to be aware of is that once you embed it into another application all your security is not valid.

Along with this currently due to it being in preview there might be an additional cost to have this capability. Which I can understand in a way because they are actually providing this outside of Power BI, and by making it available for anyone to interact, that means that there are things that are happening within the cloud that needs to be accounted for.

You can find out the details here: Announcing Power BI “publish to web” preview

Power BI – Enterprise Gateway General Availability

This is something that I have covered in the past, and it is great to see that they have incorporated all the features into one product.

This means it will make it easier to install, configure and get people using your on premise data. As you can see that this is something that I am already using and will be a great feature going forward.

Also the ability to handle failovers, as well as having the performance counter information will be great for viewing what is happening as well as the performance of the gateway.

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

Power BI – January Desktop Update

This was announced last week, and there are a whole host of updates in the Power BI Desktop. I will go through a few ones here that I think are really important.

As you can see above, you can now add borders as well as Cartesian charts’ plot area.

Along with this, I really liked that you can now refresh data in individual tables, because sometimes in the past you did not want to refresh everything including your largest table.

And finally I see that they are still making performance improvements and cross rending is great. It has always been a fast visualization, but to make even that little bit faster means that everything will be that much quicker and who does not like speed?

You can find details here: Power BI Updates This Week: New Report Authoring Capabilities

Power BI – Troux Content Pack

This is yet another content pack, and if you are an existing Troux customer then I am sure that using Power BI will give you some great insights into your technology investments. Which will help you understand your data and how best you can leverage on your information.

You can find the details here: Explore your Troux data in Power BI

Power BI – New Custom Visuals

As you can see above there have been 3 new great visuals that have been released. And to me they are concentrated around mostly financials, which is great to see.

You can go here to see all the visuals: Power BI Custom Visuals

SQL Server 2016 – CTP 3.3

It was a surprise to see that they have released CTP 3.3 and most of the updates are around SSRS and SSAS Tabular.

It is great to see that now as per the visual above you can add your own favorite reports to your SSRS view of the world. Which I think is something that is so simple, but at the same time so valuable.

Along with this is the details and updates for SSAS Tabular.

It is really good to see that you can now create calculated columns in Direct Query mode. As well as applying Row Level security in Direct Query mode also. As I know personally in the past I did not implement Direct Query mode, due to the limitations, which now they have resolved.

It is good to see that they are adding a lot of updates and features for Business Intelligence to SQL Server 2016.

You can find details around SQL Server 2016 CTP3.3 here: Access your favorite KPIs and reports with SQL Server 2016 CTP 3.3

And then if you want to find out the details around SSAS Tabular you can find that here: What’s new for SQL Server 2016 Analysis Services in CTP3.3

SQL Server 2016 – eBook

This is an update from the original eBook, and there is a lot of great content in here, especially if you are not fully aware of what is coming in SQL Server 2016.

There are two versions for desktop and mobile.

You can find information about the eBook here: Free eBook: Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud, Preview 2

SQL Server 2016 – R


As you can see above R has come a long way, and is a great tool to use if you have the specific requirement.

The screenshot above was from a presentation by Jen Underwood. And there is some really valuable information in this slide deck.

If this is something of interest to you, you can view the slide deck here: Microsoft R Server and SQL Server R Services

BI-NSIGHT – Power BI (Q&A for SSAS Tabular, Weekly Service Update, Lithium Content Pack, Mobile App for Windows 10 Mobile) – Azure (SQL Server Virtual Machines) – Power BI and Leveraging the Cloud

Even though I think a lot of people took time off over the Christmas period it seems that the Power BI team at Microsoft was full steam ahead.

Which is a great thing to come back to work and have all these updates and new things to look into.

Power BI – Q&A for SSAS Tabular

The reason for me starting with this, is that currently I really enjoy the capabilities that you have when using Q&A within the Power BI service.

My only gripe was that when I am directly connecting to an On-Premise SSAS Tabular database I was not able to use Q&A. And often the On-Premise SSAS Tabular databases are where a significant amount of time and development has been spent getting it with all the required information.

And don’t get me wrong it is great to connect directly and create reports and have real-time queries running.

But in order to really showcase what it is capable of until now I felt that Q&A was the missing piece. And it is great to see that this is something that they have started working on. And I have no doubt that this will be a welcome feature and something that I will put to use as soon as it is available.

You can find out about it here: Add Q&A capability for datasets based on Analysis Services Tabular Models

Power BI – Weekly Service Update

This past week’s update was once again a big update with a whole host of additions.

Once again I am only going to highlight what I think is significant from the list below.

Dashboards

Collaboration

Reports

Connectivity 

Excel Reports

Other

With regards to the Dashboard and Reports section I do think that the ability to export the data is really something that people have been asking for, for a long time. And it does give the report consumer the ability to use and interact if required with the raw data.

How often do we hear the question, can we export this to Excel? Well now we can from Power BI!

Adding the image to Text box as a Widget is another great addition. I think more so, that this now means that there will be the ability going forward for more Widget type of applications to be added into the dashboards. Which can only lead to some really great ideas and implementations on the dashboards.

Printing and knowing the last time a Dashboard tile was refreshed is also very welcome. This gives the users to take the information to a meeting, or to showcase the dashboard. As well as quickly identifying if the data is current and up to date.

Under connectivity it is great to see that you can now connect to Files on SharePoint Team sites. I know in a lot of companies where they utilize SharePoint team sites for all their documentation and related files. And they are often working directly with those files. Now with this capability it means that as soon as the file gets update, within the timeframe the report will be updated also.

Under Excel it is great to see that you can now open and pin the Excel Charts to a dashboard. This is very welcome as often your chart tells a very interesting story and instead of having to try and re-create it, you can use all the hard work that has already been done.

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

Power BI – Lithium Content Pack

Another week another great content pack. This week it is Lithium, which enables brands and customers using social media to get the answers that they need as well as share their experiences. Which in turn leads to more revenue.

You can find out the details about it here: Analyze your Lithium community data in Power BI

Power BI – Mobile App for Windows 10 Mobile

This is without doubt the one mobile app that has taken some time to be released. And whilst I do understand that Microsoft’s direction is Mobile and cloud first and that there are a LOT more users using iPhone and Android devices I thought that they would have gotten this out sooner.

But without doubt it is a welcome addition for people who are using Windows 10 Mobile phones. And it looks to have the same great features as what has currently been released on the other platforms.

I am looking forward to see how this improves over time.

You can find out about the details here: Announcing the Power BI app for Windows 10 Mobile

And you can download it from here: Download from the Windows Store

Azure – Preconfigured SQL Server Virtual Machines

It is great to see that in Azure you can now have some help setting up a preconfigured SQL Server Virtual Machine.

In my mind there is almost no one better to aid with this process. I think they are managing the entire Azure SQL Server, Azure Data warehouse platforms so they will have a vast amount of experience as how best to configure the virtual machine. And I have no doubt that by doing this, they too can make customers get better returns.

You can find out about all the details here: Introducing a simplified configuration experience for SQL Server in Azure Virtual Machines

Power BI and Leveraging the Cloud

This is a quick plug on a previous blog post that I did this week.

It is just my personal view in terms of how Power BI is leveraging the Azure Cloud platform and how it all fits together. And it is quite interesting how well they have put it all together and how quick it really is.

You can read the blog post here: Power BI and Leveraging the Cloud (Microsoft Azure)

BI-NSIGHT – Power BI (IOS Power BI App for SSRS, Enterprise Gateway, Printing, AT Internet Content Pack) – SQL Server 2016 (eBook, Mobile Publisher)

The year is finally coming to a close and it has been a really busy but amazing year.

I am looking forward to what will be happening in the Microsoft BI space next year, with the release of SQL Server 2016, as well as I have no doubt that there are a great things planned for Power BI.

Power BI – IOS Power BI App for SSRS

Whilst this is not the most amazing picture that there has been with regards to Power BI, I do think that this is a rather significant thing to mention.

The reason being is that this is the first time that we are starting to see the integration of Power BI and SQL Server (On-Premise) becoming one mobile application. Which is what was in the roadmap that was presented by Microsoft a few months ago.

In my opinion I do see that as next year continues we will see more features integrated. Which I personally think will be really great. What this will mean is that for people on the go, or who want to have a quick view of the data or information, it will always be at their fingertips. They will no longer have to have to log into multiple locations or have multiple mobile applications. They can open one mobile app, and then see the information that they need to see immediately.

I really am looking forward to see how this evolves next year.

If you have an iPhone you can find the updated app here: Microsoft Power BI – iTunes

Power BI – Enterprise Gateway

I am really pleased to see that they have moved so quickly with regards to getting the Enterprise Gateway for Power BI to support both Multi-Dimensional and Tabular models of SSAS. As well as already providing the support for SQL Server direct connectivity. As well as to SAP HANA.

I my opinion I do think that Microsoft is moving in the right direction and starting to provide some functionality for some of their Enterprise customers. And this is one tool that I know will be used to make the administration and access to in premise data sources that much easier.

As well as something that I will be using in the future.

You can find out the details here: Power BI Gateway Enterprise now supports live connections to Analysis Services and SAP HANA

Power BI – Printing

Just a quick note that they now have made it really easy to print your Power BI reports directly from Power BI.

Power BI – AT Internet Content Pack

Here is another great content pack from Microsoft. This time it is related to AT Internet, which help you get insights with your data. And this is yet another valuable content pack that can help its customers and potentially yourself quickly gain the insights to the information you have, to get the best return.

You can find more information here: Explore your AT Internet data in Power BI

SQL Server 2016 – eBook

Microsoft released this eBook, which I personally thought was the perfect timing. With a lot of people being on holidays it did mean that we have the time to read the book.

I downloaded it onto my mobile device, and while I did not read every single page, I did read most of the book. And I thought it was a great read. Not too technical, but it did give a lot of information on the sections that they put into the book. I was particularly interested in the Row Level Security (which I found in my mind has a similar implementation to SSAS), the Stretch Database (which I can foresee being used for large old data sets that are still required to be online, but not accessed frequently) and then Reporting Services.

I did read the entire Reporting Services section, and I enjoyed reading about the printing and how they have finally removed the Active X control. As well as the option to Export to Power Point, which I think a lot of people have been doing manually in the past. As well as other sections which I have read in separate blog posts in the past (Pining SSRS reports to Power BI, New Chart Types)

You can find out the information here: Free eBook: Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud, Preview Edition

And you can download it from here: Free eBook – Printable Copy

SQL Server 2016 – Mobile Report Publisher

It was great to see that Microsoft was able to meet their own deadline and have another release of SQL Server 2016 CTP 3.2 which included the new SSRS features.

And as we can see from the picture above they have incorporated the SQL Server Mobile Report Publisher, as well as the application to download and install. Along with this there is also a great blog post by Christopher Finlan which walks you through how to get this up and running.

You can find out all the details here: Mobile Report Publisher preview now available

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 – SQL Server 2016 CTP 3.0 (SSAS, SSRS, SSIS) – Power BI (Chiclet Visual, SparkPost Content Pack, Weekly Service Update, Personal Gateway Update, Tiles in SharePoint)

I expected this week to be a really interesting week with SQL Pass happening. As I was sure to see some really good and interesting updates from Microsoft and it sure is living up to this.

There has been a lot of information on Twitter and on other blogs, so here is my take on the developments.

SQL Server 2016 CTP 3.0 (SQL Server Database Engine, SQL Server Analysis Services, SQL Server Reporting Services, SQL Server Integration Services)

There was a whole host up dates with SQL Server 2016 CTP 3.0, which is great to see, as well as some announcements of what we can expect in subsequent releases.

I am just going to highlight below what I think is relevant in the BI space. But there will be links below where you can find the related blog posts, which have more information from the Microsoft teams.

SSAS

With regards to SSAS, it is good to see how much effort and work is going into the Tabular model. Which is what I thought would be the case.

I think that it is really great to see that they have changed the underlying structure from XMLA to JSON. The way that I see it, this is how they have implemented Power BI in terms of having the SSAS database sitting in memory in Azure. And without a doubt I am sure that they have learnt a lot, and from this they can then leverage this and bring it into the On Premise product. We all know how fast it is online!

The MDX Support for Direct Query is also a great update. I can see a lot of people leveraging this, and when you partner this with APS you can pretty much start to enable real-time analytics. Which can be a real game changer.

All the other updates that are coming into SSAS have mostly been completed either in Power BI Desktop or in Excel 2016. So it is great to see this in the Server product which will go a long way to ensure that it can scale and perform for enterprise workloads.

SSRS

I have eagerly been waiting to see what was going to happen in the SSRS space. And whilst I had seen some of the now released information it is great to see it being released to the general public. As well as how well it has been received.

The pinning of SSRS reports into Power BI is a really smart move. And the ability to also refresh this report in Power BI is pure Genius. What this means now is you can leverage both of your On Premise and cloud investments. And to the users this will be seamless.

What I also really like is that you can often create really interesting SSRS reports, and the executives and high level managers do not need to see the details. They just want an overview. And now by leveraging this all into Power BI, it becomes their one stop shop!

SSIS

There does not seem to have been a lot of love for SSIS, and to be honest it is a stable and really good product.

But what I did see is the Control Flow Template, and I am hoping that this is something similar to what you can currently do with BIML. What that is how I perceived it to be. And I am hoping that you can create different control flow templates for different control flows. So for example you could create a control flow template for a SCD Type 2. And then once you have it designed the way that you want, any other developers can then utilize it. This would go a long way in enterprises where you want to standardize the way of doing things.

You can read about all of the above here:

Power BI – Chiclet Visual Slicer

The one thing that I have been struggling with in Power BI was how to get a slicer to work, so that it looked good.

And low and behold there is a new visualization which can how do this. And to have it with images also is really smart. As people love to click on Images.

Another great announcement was from James Phillips that Microsoft would be releasing a new visualization every month, indefinitely. This is really great and I am sure that we will see some really interesting and useful visualizations in the future.

You can read all about it here: Visual Awesomeness Unlocked: The Chiclet Slicer

Power BI – SparkPost Content Pack

This week there is another interesting and great Content Pack. This time for SparkPost. Which you can now use to monitor your Email campaigns.

You can read about it here: Monitor Your SparkPost data with Power BI

Power BI – Weekly Service Update

Not only was there a host of announcements at SQL Pass, there was the weekly Power BI Service update.

Once again I am going to quickly highlight what there is in this week’s update.

They have made quite a few improvements with regards to the way we can share the dashboards in Power BI. All of these updates make it a lot easier to share the dashboard and to enable people to see how good Power BI is. The additions are (Sharing the Dashboards with AD Groups, People Picker and Sharing with a large number of Email addresses)

Along with this is the ability to start passing parameters into the URL. I have no doubt that passing URL parameters will keep on increasing and giving additional flexibility in the Power BI service.

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

Power BI – Personal Gateway Update

There was an update late last week for the Power BI Personal Gateway and it is mostly around bug fixes and performance improvements. Which is great to see because I do know that often we want it to run as smoothly and quickly as possible

You can find more information here: New version of Personal Gateway is now live!

Power BI – Tiles in SharePoint

And finally the guys from DevScope have now created a Power BI Tile for SharePoint.

I think that this will work really well, because it will give the ability to showcase all the work done in your Power BI reports, as well as not having to re-create reports over and over again.

If you want to find more details and pricing, you can find it here: Power BI Tiles for SharePoint

BI-NSIGHT – Power BI (Custom Visuals, Visual Contest Winners, Row Values in Q&A Answers, Desktop Update, Weekly Service Update, Mobile App Update, Azure Enterprise Content Pack) – Datazen (Windows Publisher 7 App)

Once again in the past two weeks, the Microsoft machine has been adding more features and user requests to Power BI.

It continues to amaze me at how quickly they can get things into the product, as well as continuing listening to what the community looking for and responding.

Let’s get into all the details!

Power BI – Custom Visuals

I have read and also thought about this during the Power BI Visual contest, in terms of how they were going to be able people to get these amazing new visualisations into the product. And low and behold we now know how this can be achieved.

What amazes me is that they have created something that is so simple (to import the visualization), as well as by keeping it on an open source framework there is never any person who can monetize their visualization. And this is really great because it means that people will share and use other peoples great work. As well as promote Power BI.

I have to be honest I have not seen what the other competitors are doing with regards to this, but I am sure that they must be worried. The pace that Microsoft is delivering changes, as well as extending Power BI back to the users is incredible. And the way I see it, it is leaving the competitors in their dust. And this makes me really happy to be part of the Microsoft story. As well as making my job a whole lot easier.

You can read more about it here: Visualize your data, your way using custom visuals in Power BI

Power BI – Visual Contest Winners

This competition was a great idea from Microsoft and did show how smart and creative people are.

There were some amazing entries and worthy winners.

You can find out more about the competition here: Power BI Best Visual Contest – Announcing the Contest Winners!

Power BI – Using Row Values to answer questions in Q&A

This was something really interesting which I saw in Jen Underwood’s blog post.

It is now that Q&A will not only look for information in the Column names, but also in the row values.

This is really incredible and will mean that you can get even more answers from your data. As well as be able to have more creative questions for your end users.

Power BI – Desktop Update

Once again it was time for the Power BI Desktop update, and they did not disappoint.

There have been quite a few blogs from other people with regards to all of the updates. And they are all really welcome and interesting. It is great to see that they are almost in line with the updates that are happening on the service (Cloud) site.

I am not going to go into all the details, which you can read from the link further below.

But what did catch my eye, and once again I think is yet another smart move from Microsoft is to have the ability to Preview new features. I see this is a smart move, because what it is doing is to show the users of Power BI what is potentially coming down the road. But along with this, it also gives Microsoft the insight to getting valuable feedback from the real world. As well as to see who uses the preview features.

You can find out all the details about the Power BI Desktop Update here: Custom visualizations support and 22 other features in the Power BI Desktop October update

Power BI – Weekly Service Update

Another week starts to come to an end and another Power BI Service update.

This week they have listened to what the community is asking for and made the service updates.

What I really liked is the read-only members for your Power BI Groups. As often you could have spent a lot of time getting everything correct for your dashboards and your reports, to unfortunately get some user modify it.

Also having the ability to add featured questions to Q&A means you can guide and help the user find answers to their questions.

You can find more details here: Power BI Weekly Service Update

Power BI – Mobile App Update

Yet again another great update to the Power BI Mobile App.

This is a great area that they are improving on all the time. Which is really vital due to people being so mobile these days having a good mobile app means that people can leverage all the great work that was previously completed.

Having notifications, as well as making the experience faster means that people will use it more and want to interact with it more. Meaning that it will have a greater adoption with users. Along with improving the charts, as often as they say a picture is worth a thousand words.

You can find out about it all here: Power BI mobile Mid-October updates are here

Power BI – Azure Enterprise Content Pack

This I think is a really great content pack, due to having a lot of things happening in Azure, and it is often good to see what is happening within Azure.

There might be certain departments who are over utilizing Azure, and others who are not doing anything. And this gives you a great ability to see what is happening and great insights.

You can read all about it here: Visualize your Microsoft Azure Enterprise Data with Power BI

Datazen – Publisher Update for Windows 7 App

It is great to see that Microsoft is still investing time and effort into the Datazen publisher application specifically for the Windows 7 environment.

You can find more details and how to download and install the Publisher App here: Datazen Publisher for Windows 7 available now

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