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

BI-NSIGHT – Power BI (Power BI Tiles Office Add in, Content Pack Dynamics NAV, Weekly Service Update, Contest Update) – White Papers for Power BI Overview & Security

So this week it is all pretty much about Power BI, Microsoft seems to be steam rolling ahead which is great to see.

And it is understandable that for the on premise products this takes a bit longer to get completed and implemented, and I am looking forward to see how this plays out in the future.

Power BI – Tiles Office Add In

I know that this has been blogged by quite a few people but it is a really fantastic add in and it is something that I honestly have been thinking about, and I do think that I did mention it my previous BI-NSIGHT blog post.

This really does mean that your presentation can be that much more interactive and is something that I will definitely use in the future.

If you want to find our more details on how to use it then use this link: Integrating Power BI Tiles into Office documents

And if you want to install the Power BI Tiles Add In you can get it from here: Power BI Tiles

Power BI – Content Pack – Microsoft Dynamics NAV

This week is another Content Pack, this time it is Microsoft Dynamics NAV.

It is interesting to see that Microsoft are also creating content packs for some of their own offerings. As with Dynamics NAV where it is aimed more at the small to medium organizations, which is often the organization size that does not have the skills or time to create great reporting. This enables the organizations to report on their financials and business processes.

You can read all about it here: Exploring your Microsoft Dynamics NAV Data with Power BI

Power BI – Weekly Service Update

Another week of great updates for the Power BI service.

I have to say something that I have been thinking about recently is that what if you wanted to have a different name for a column name in your Power BI model. Whilst this doesn’t directly do this (As you could do with Power Pivot and synonyms) it does mean that you can suggest some search terms which will prompt the people to use other search terms that are available to them.

It is good to see that they are also incorporating having shapes integrated as that can often enable you to change the focus of your report so that it is immediate to the user.

And finally it is good to see that you now can have the option to just share the URL with other users in your organization. This also then means that the user can actually bookmark the URL, which means that they can quickly and easily view the data, without having to look through their email.

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

Power BI – Contest Winner – Advanced Time Slicer

This was another worthy winner and something that I could see myself using in the future.

You can find more information here: Advanced time-slicer (brush-chart) by Cambridge Technology Partners Team

Power BI – Overview White Paper

I am not sure if this has been seen before as the Power BI Overview White Paper looks to have been released on July 2015, but I know that I have not read it yet, and I plan to read it asap.

You can download it from here: http://www.jenunderwood.com/PowerBIOverviewWhitepaper.docx

Power BI – Security White Paper

At least this Power BI Security White paper was released this month. This is once again another great white paper to read. Specifically because I would like to understand how the security works. It is essential to understand how it all works and pieces together so that you can be aware of the implications of reports or dashboards are shared.

You can download it from here: Power BI Security

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 – SQL Server 2016 (CTP 2.4) – Power BI (Azure Audit Logs Content Pack, Visual Contest, Weekly Service Update) – Dashboard Design

So I am happy to report that this week there are still quite a few updates but not as many as what there has recently been.

It is great to see that there are still so many updates and new things to look into in the Microsoft BI space.

Personally a lot has been going on within my working environment which has been challenging me, as well as finding solutions to issues that will enable the entire company to showcase BI and enable the business to succeed. I do plan to blog about my experiences, once I get a better understanding and can establish what I am currently looking at. But it will be based around the following:

  • SSAS Multi-Dimensional & Tabular Performance.
    • Looking at it from all angles (ASTrace, Perfmon, SSAS DMV’s)
  • How to easily control and administer Role Security in both SSAS Multi-Dimensional & Tabular cubes

So I look forward to blogging about the details above, in which I have had to learn a lot not only about performance, but also how roles affect performance and how best to leverage our data.

But let’s get into this week’s BI-NSIGHT details.

SQL Server 2016 – CTP 2.4 (SSRS Updates)

With the latest CTP update for SQL Server 2016, it is nice to see that SSRS is finally getting some traction.

Whilst at times it does appear that it is not getting anywhere, I also have to acknowledge that it must take a lot of time and effort to get the required changes into SQL Server 2016. And along this journey these small incremental changes will add up and at the end we will hopefully have a great new SSRS experience which will enable us to deliver even better and more meaningful reports to our end users or consumers.

So it is great to see that with the new parameters we now have the ability to be able to put our parameters where we would want them to go. This does seem like a small change, but in terms of creating the reports it does help make it a more meaningful report.

Also having them starting to change the code base so that it will render in any browser is really great. And this will mean going forward we can have a consistent reporting experience.

Along with this is also the ability to export to Power Point, which is great when you want to have it as part of a presentation. What I think would be great is to have the ability to be able to interact with the SSRS report directly from within Power Point. They did do this with Power View and I thought this was a great way to enable people to ask questions whilst you are presenting, and giving you the ability to answer their questions there and then.

There are a few other updates both in the SSRS space, as well as in CTP 2.4 which you can read all about it here: More Reporting Services enhancements in SQL Server 2016 CTP 2.4 and SQL Server 2016 Community Technology Preview 2.4 is available

Power BI – Azure Audit Logs Content Pack

Once again another week, another Content Pack. This week it is with regards to the Azure Audit Logs. Which can quickly and easily enable people to see what is going on with regards to your Azure logs. This can give you some great insights into what is going on with your Azure tenant.

You can read all about it here: Visualize your Azure Audit Logs with Power BI

Power BI Visual Contest Updates

I know that the above picture is not all that great, but it sure does remind me of a decomposition tree from Performance Point. And I have to say that it did often provide a great way for users to understand how the number was derived.

There are still a whole host of additional entries into the Power BI Visual contest which I still wait in anticipation to see what the people are creating.

You can follow it here: Power BI Visual Contest

Power BI – Weekly Service Update

This week was another week with some great updates. As I have read from a few other people on Twitter, it really takes some doing to just keep up with all the updates and releases from the Microsoft Power BI team. And I have to think that other competitors must be thinking how can they even compete with such a fast paced offering.

As shown with the picture above, we can now create a custom page size for our reports, which just enables any possible page size that might be required.

As well as having the ability for additional formatting options for images and bubble charts.

Along with this is the ability to be able to email out to alternate email addresses. The caveat is that it still is only to emails related to your company. So you cannot send it out to any email address. But it seems like they are putting in the underlying steps to work in this information going forward.

And finally the support for Office Dedicated customers. Which is great to see that everyone is covered.

Dashboard Design

I read about this blog post from the Power BI team for having a good dashboard design. And this is really important in Power BI because a lot of the work that you are doing is about creating meaningful and good dashboards. Even the underlying reports are a different version of a dashboard in my personal view.

So ensuring that you create a good dashboard, it means that the users can easily understand what they are looking at. As well as also being able to interact and derive some meaningful and insightful results from the data being presented to them.

I would suggest that everyone have a read through, as I often find re-reading it again after a certain amount of time you can always learn something new.

You can read about it here: The Art and Science of Effective Dashboard Design