BI-NSIGHT – Power BI (PowerPivotPro & Microsoft Helping displaced workers, Visualizing weather from Microsoft OMS) – SSAS (Memory Usage (1200 Compatibility Only) report using Power BI) – SQL Server (SP1, SSRS Updates

Another week close to Christmas and another week of great news in the Business Intelligence world.

Power BI – PowerPivotPro & Microsoft helping displaced workers

A quick mention on how it is great to see both the people at Power Pivot Pro & Microsoft are working together to help people find employment.

The great thing about Power BI, is all that you need is a computer and an internet connection and you are good to go.

You can find the details here: PowerPivotPro and Microsoft help displaced workers with near-free Power BI Training Dec 7th

Power BI – Visualizing weather from Microsoft OMS

This is a great blog post where the people from Microsoft explain how using OMS they can leverage the weather data that they collect and publish it to Power BI.

If you currently use Microsoft OMS this could be a great resource.

You can read up about it here: Visualizing weather information in Power BI with data from Microsoft OMS

SSAS (SQL Server Analysis Services) – New SSAS Memory (1200 Compatibility Only) report using Power BI

Here is another really great resource of information from Kasper De Jonge, where he has updated his SSAS Memory usage report, with the latest versions of SSAS, but also to use Power BI to visualize your memory usage.

Also Kasper has put it on GitHub which means that it is open for anyone to use, as well as to contribute and hopefully make it into an amazing Memory usage report.

You can find the details, as well as how to download the file here: New SSAS memory usage report using Power BI

SQL Server – Service Pack 1

It is not often that there I would blog about a service pack, but this is an exception. As you can see above Microsoft have made available a whole host of features that were previously only for the Enterprise Edition which is now available in not only Standard Edition but also Web, Express & LocalDB.

One thing to note is that it would appear that all of the licensing updates are in the RDBMs space.

There are 3 updates for SSAS whichi is firstly that SSAS is NUMA Aware, I know that this is something people have been asking for, for a long time. As well as Memory Allocation and Heap Fragmentation.

You can find the blog post here, which has all the detailed information, as well as what additional update there are in SP1: SQL Server 2016 Service Pack 1 (SP1) released !!!

SQL Server – SSRS (SQL Server Reporting Services) updates with SP1

Not only where there the licensing features with SQL Server 2016 SP1, but also updates to SSRS, as you can see above the SSRS team is listening to what clients and customers are asking for and have updated the Details view.

They have also updated the Execution logs, so that you can now get some basic information in terms of who is accessing and viewing mobile reports.

And finally direct navigation options for KPI’s, which makes it easier to navigate to a direct link when viewing a KPI.

You can find all the details here: What’s new in SQL Server 2016 SP1 for Reporting Services

BI-NSIGHT – Power BI (Mobile Apps Update, Microsoft Teams, Extending Project Online Content Pack, Featured Data Stories, Streaming Data Set Example from Chris Webb, Dashboard Makeover Webinar) – Azure (Analysis Services)

Another week has gone by, and some more updates in the world of Business Intelligence

Power BI – Mobile Apps Update

There are quite a few updates for the Mobile BI apps, and it is good to see that it is across all platforms.

The easier navigation is always welcome because with a phone it is always tricky to navigate and see what you want to see. It also makes perfect sense that when you open your Mobile app, you want to see your favourites dashboard, because that is what you want to see first.

With regards to notifications, these are great updates, not only is the notification more descriptive, but also easier to dismiss once you have seen the notification.

As well as being able to view your report by location (on windows phone) and the ability to try the SandDance visual on the surface hub.

You can find the mobile apps blog update here: Power BI Mobile Apps feature summary – October 2016

Power BI – Microsoft Teams

Last week or this week Microsoft announced a new platform to enable productivity and collaboration called Microsoft Teams. You can find the details with regards to Microsoft Teams here: Microsoft Teams – Group Chat

And now this week they have now enabled this for Microsoft Power BI. This will further add to the Power BI story in terms of collaboration and I think it will make it even easier for people to communicate effectively and quickly.

You can find more details here: Power BI teams up with Microsoft Teams

Power BI – Extending the Project Online Content Pack

The Office team has been busy creating and extending the Project Online Content Pack. This blog post not only shows you where to get the data, but it also explains how to utilise the data and tips and tricks to get the most out of your data.

If you use Project Online read their blog post here: Extending the PowerBI content pack for Project Online

Power BI – Featured Data Stories

Once again this month from the Power BI Community is the featured data stories.

I always go and have a look, as there are some really good data stories told.

You can find the details here: Congratulations to this month’s Featured Data Stories Gallery submissions

Power BI – Streaming Data Set Example from Chris Webb

This is a really great and insightful post from Chris Webb. Not only does he show and explain how to get the streaming data sets working, but has an example that you can try out with yourself. (I personally did try it and it works!)

For me there are two things to take away from this. The first is that it is really quite easy and simple to create a streaming data set. Second is that I am already thinking of how to leverage this for other scenario’s.

You can find Chris’s Blog post here: Pushing Data From Excel To Power BI Using Streaming Datasets

Power BI – Dashboard Makeover Webinar & Submit your data

There is a Webinar on 17 November 2016, where you can see how they do a dashboard makeover.

Not only that but there also is the option for you to submit your entry for it to be made over.

You can find the details here: Submit your work and attend our next Dashboard Makeover

Azure – Analysis Services

In the link below is a great blog post by Kasper De Jonge and he goes into some great high level details as to why you would want to use Analysis Services in Azure. It is a really great article and does give some food for thought.

An example would be never having to upgrade to another version of Analysis Services, as well as having it being Highly Available, which in the past takes a significant amount of effort.

You can find all the details here: Analysis Services in Azure, when and why…

BI-NSIGHT – Power BI (August Power BI Desktop Update, Future Download Report & Snap to Grid, IntelliSense coming to Power Query, Power BI Training, WebTuna Content Pack, Building a Real-Time IoT Dashboard, Chris Webb Loading Data from Multiple Excel Workbooks saved in OneDrive, SCCM Solution Template) – SQL Server (SSAS Integrated Workspace Mode, SSMS Update, Report Builder Update)

Last week there was not a lot on the go, but it seems to have picked up this week, so here are the latest insights.

Power BI – August Power BI Desktop Update

As I am sure some of you would have already seen and heard and I personally have to echo what other people have said, it is great to see how the Power BI team, as well as Microsoft take on board the issues and problems that their users face, and in this instance how quickly they have made the change, so that we can get not only the new functionality. But as requested also have the ability to use the older functionality also.

What I am referring to here is the Drill experience.

So you can use the double down arrow which will do the previous experience which will show the next level of the hierarchy.

And the new experience which is the split arrow which will perform the new inline hierarchy experience.

You can find the details here: August Power BI Desktop Update: Updated Drill experience

Power BI – Future state (Download Report) & Snap to Grid

As you can see with the image below on certain versions of the Power BI Service they have started provisioning the ability to download your Power BI report as a PBIX file. Which will be a great addition.

Next you can see that they have started working on the Snap to Grid functionality which is another great addition which will be available sometime in the near future.

Power BI – IntelliSense coming to Power Query

Just a quick update that IntelliSense is coming to Power Query, I have been working in the nuts and bolts of Power Query and this will be a very welcome addition.

Power BI – Training


There are some great free training available to get into Power BI, as you can see above there is a “Dashboard in an Hour” as well as “Dashboard in a Day”. Not only that but there is also a really great and valuable edX course (which I have already completed) which offers some great content and learning experiences.

You can find the details about the different options here: Power BI Dashboard in a Day and Dashboard in an Hour training near you

Power BI – WebTuna Content Pack

Here is another content pack, this time from WebTuna, which is an on-demand service for alerting and monitoring the performance of your internet or internet sits. And I am sure due to the nature of the business model as well as analytics in terms of usage and where it is coming from this will give some great insights into their customers data.

You can find all the details here: Explore your WebTuna Data with Power BI

Power BI – Building a Real-Time IoT Dashboard

In this blog post from the Microsoft Power BI team they show you how to setup, configure and create a real-time IoT dashboard.

Whilst the concept is very basic (which I think is a good thing to start learning) it also shows how powerful this can actually be.

You can find the details here: Building a Real-time IoT Dashboard with Power BI: A Step-by-Step Tutorial

Power BI – Tracking Changes in PBIX files

This is a blog post from Helen Gore, where they have created something that appears to be very simple, but actually is very powerful.

What she explains in her blog post is how they track changes in their Power BI Desktop (PBIX) files, so that when other people are working on the same file, they know what the previous changes were.

You can find all the details here: HOW TO TRACK CHANGES IN POWER BI DESKTOP

Power BI – Chris Webb – Loading Data from Multiple Excel Workbooks saved in OneDrive

This is an excellent blog post from Chris Webb where he shows how to not only load multiple Excel Workbooks, but ones that are saved on OneDrive Personal or OneDrive for Business, as well as ensuring that they get refreshed on your schedule.

I will not go into all the details, as you can read them on his blog post here: Loading Data From Multiple Excel Workbooks Into Power BI–And Making Sure Data Refresh Works After Publishing

Power BI – SCCM (System Center Configuration Manager) Solution Template

If my memory serves me correctly this is the second Power BI Solution Template from the Microsoft Power BI team.

And this time it is for SCCM, and having worked with this data in the past, it is really great to see how easily it can be used to show insights into your SCCM data. As well as it does appear that you can integrate 3rd party information in SCCM also.

You can find the details here: Announcing the Power BI solution template for System Center Configuration Manager

SQL Server – SSAS (SQL Server Analysis Services) Integrated Workspace Mode

This is a great update for SSDT and the modelling experience when working with SSAS Tabular models. You now can use an integrated workspace, which means you do not have to connect to an SSAS Tabular Server.

The thing to note is that you might need additional memory, as well as they highlight in the blog post drivers for both 32bit and 64bit depending on the version of SSDT.

You can find the details here: Introducing Integrated Workspace Mode for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)

SQL Server – SSMS (SQL Server Management Studio) Update

Here is the monthly update for SSMS, and there are quite a few updates in this release.

You can find all the details here: Download SQL Server Management Studio (SSMS)

SQL Server – Report Builder Update

It appears that for SQL Server 2016 the report builder will be following a similar release process as SSMS and SSDT where there will be more frequent updates to report builder.

I am sure that this will enable a lot of future updates and enhancements for report builder.

You can find the details here: SQL Server 2016 Report Builder update now available

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

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