Home Power BI Template
Category:

Power BI Template

I love automating things, I have read somewhere, a good developer always tries to automate things, but in my case, I am just lazy, so I try to automate! That is why I started exploring various Power BI related API’s. I was working towards a single resource which I can use to answer questions related to Power BI Tennant. During this process I have written different posts:  Power Bi Template to document the Power Bi Service, HOW TO FIND ALL POWER BI USERS IN AN ORGANISATION? I would recommend reading those posts as they cover how I created Azure Authorization Token a bit more in detail. So, after exploring all those API’s, I still had few unanswered questions like:

  • What’s happening on my tenant?
  • Who is doing what?
  • Users interest in?
  • Should I disable export or print etc.?
  • Does anyone ever users dashboards?
  • Using workspaces is a good idea?
  • Who updated the App?
  • Many more

These questions guided me to Power BI Admin Auditing; it has all the data I wanted. But, I didn’t want to do search and export every single time I wanted to do something, and I am rubbish with PowerShell, that’s out of scope for me, there I went for searching for another API …

Office 365 Management API:

Office 365 management API enables you to do many management tasks using API, like Audit search.

Office 365 Management API and Power BI:

I don’t know why I play with API’s, most of API service providers are very good at sending pointless error information, and I had no special luck with Office 365 API. Among all Office 365 API was a bit forgiving one. The root URL of the API is https://manage.office.com/api/v1.0/{tenant_id}/activity/feed/{operation}. All API requests require an Authorization just like Power BI API. This page  https://msdn.microsoft.com/en-us/office-365/office-365-management-activity-api-reference covers all information about requests and various parameters.

The Activity API supports the following operations:

  • Start a subscription to begin receiving notifications and retrieving activity data for a tenant.
  • Stop a subscription to discontinue retrieving data for a tenant.
  • List current subscriptions
  • List available content and the corresponding content URLs.
  • Receiving Notifications sent by a webhook when new content is available
  • Retrieving content by using the content URL.
  • List notifications sent by a webhook.
  • Retrieve resource friendly names for objects in the data feed identified by guides.

From <https://msdn.microsoft.com/en-us/office-365/office-365-management-activity-api-reference>

To get data from API, you first need to enable a subscription. For this, we need to POST requests using Power BI. This is where I stumbled most, Chris Webb has a fantastic blog post explaining POST Requests using Power BI – https://blog.crossjoin.co.uk/2014/04/19/web-services-and-post-requests-in-power-query/

Calling office 365 API using Power BI/ Power Query

I first created several parameters: TennantID, ClientID,ClientSecret,UserName,Password,publisher identifier. The first thing is to get authorization token. Getting started with Office 365 management API explains how authentication works

let

    //App Details

    client_id = client_id,

    client_secret=client_secret,

    //Authentication


    //URI's

    redirect_uri = "http://localhost:13526/redirect",

    token_uri = "https://login.windows.net/common/oauth2/token",

    authorize_uri = "https://login.windows.net/common/oauth2/authorize",

    resource="https://manage.office.com",


    //User Details 

    username=username,

    password=password,
 

   tokenResponse = Json.Document(Web.Contents(token_uri,         

                                [

                                    Content=

                                    Text.ToBinary(Uri.BuildQueryString(

                                    [

                                        client_id = client_id

                                        ,client_secret=client_secret

                                        ,username=username

                                        ,password=password

                                        ,resource=resource

                                        ,grant_type = "password"

                                        ,redirect_uri = redirect_uri

                                    ]))

                                    ,Headers=

                                        [Accept="application/json"]

                                        , ManualStatusHandling={400}

                                ])),

                                access_token = tokenResponse[access_token],

    token = tokenResponse[access_token]

in

    token

To make sure I have a valid connection, I create a query to pull all services on my Office 365

let

    getgroups_uri ="https://manage.office.com/api/v1.0/"&TennantID&"/ServiceComms/Services ",

 GetGroups = Json.Document(Web.Contents(getgroups_uri,

                [

                Headers= [                    Authorization="Bearer "& O365RefToken                    ]
                                  ]

                )),

    value = GetGroups[value],

    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Id", "DisplayName", "Features"}, {"Id", "DisplayName", "Features"})

in

    #"Expanded Column1"

Then starting a subscription. URL to start subscription is https://manage.office.com/api/v1.0/”&TennantID&”/activity/feed/subscriptions/start?contentType=Audit.PowerBI. Below query did not work at all for a while and it started working suddenly, I genuinely don’t know why. But below is the query I used to start the subscription. When I run this script, it returns an error, but subscription was any way enabled.

let
   getgroups_uri ="https://manage.office.com/api/v1.0/"&TennantID&"/activity/feed/subscriptions/start?contentType=Audit.General",

   content = "{

        ""webhook"" : {

        ""address"": ""https://webhook.myapp.com/o365/"",

        ""authId"": ""o365activityapinotification"",

        ""expiration"": """"

        }

    }",

Auth = "Bearer "&O365RefToken,

     GetGroups =  (Web.Contents(getgroups_uri,

            [

                Headers = [#"Authorization"=Auth

            //              ,#"Content-Type"="application/json;charset=utf-8"

            ]
                ,

                Content = Text.ToBinary("")
            ]
                )),

    #"Imported JSON" = Json.Document(GetGroups,65001)
in
    #"Imported JSON"

You can view enabled subscriptions using following script

let
   getgroups_uri ="https://manage.office.com/api/v1.0/"&TennantID&"/activity/feed/subscriptions/list",


 GetGroups = Json.Document(Web.Contents(getgroups_uri, 
                [
                Headers=
                    [
                    Authorization="Bearer "& O365RefToken
                    ], 
                ManualStatusHandling={400}
                ]
                )),
    #"Converted to Table" = Table.FromList(GetGroups, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"contentType", "status", "webhook"}, {"contentType", "status", "webhook"})
in

    #"Expanded Column1"

Office 365 Management API for Power BI Audit Logging:

I am interested only in Power BI Audit, but you can use this to view other Audit logs too. As per API Schema, all Power BI Activities has RecordType 20, So I used following script to pull all Power BI Content.

let
   getgroups_uri ="https://manage.office.com/api/v1.0/"&TennantID&"/activity/feed/subscriptions/list",


 GetGroups = Json.Document(Web.Contents(getgroups_uri, 
                [
                Headers=
                    [
                    Authorization="Bearer "& O365RefToken
                    ], 
                ManualStatusHandling={400}
                ]
                )),
    #"Converted to Table" = Table.FromList(GetGroups, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"contentType", "status", "webhook"}, {"contentType", "status", "webhook"}),
    contentType1 = #"Expanded Column1"[contentType],
    #"Converted to Table1" = Table.FromList(contentType1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "Content"}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "GetContent", each GetContent([Content])),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Invoked Custom Function", {{"GetContent", null}}),
    #"Expanded GetContent" = Table.ExpandTableColumn(#"Replaced Errors", "GetContent", {"contentUri", "contentId", "contentType", "contentCreated", "contentExpiration"}, {"contentUri", "contentId", "contentType", "contentCreated", "contentExpiration"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded GetContent", each ([contentUri] <> null) and ([contentType] = "Audit.General")),
    #"Invoked Custom Function1" = Table.AddColumn(#"Filtered Rows", "Fn_GetURIData", each try Fn_GetURIData([contentUri]) otherwise null),
    #"Added Custom" = Table.AddColumn(#"Invoked Custom Function1", "Custom", each List.Count(Table.ColumnNames([Fn_GetURIData]))),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Errors", "Custom.1", each Table.Contains( [Fn_GetURIData],[Workload="PowerBI"])),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = true)),
    #"Expanded Fn_GetURIData" = Table.ExpandTableColumn(#"Filtered Rows1", "Fn_GetURIData", {"Id", "RecordType", "CreationTime", "Operation", "OrganizationId", "UserType", "UserKey", "Workload", "UserId", "ClientIP", "UserAgent", "Activity", "ItemName", "WorkSpaceName", "DatasetName", "ReportName", "WorkspaceId", "ObjectId", "DatasetId", "ReportId"}, {"Id", "RecordType", "CreationTime", "Operation", "OrganizationId", "UserType", "UserKey", "Workload", "UserId", "ClientIP", "UserAgent", "Activity", "ItemName", "WorkSpaceName", "DatasetName", "ReportName", "WorkspaceId", "ObjectId", "DatasetId", "ReportId"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Fn_GetURIData",{{"Content", type text}, {"contentUri", type text}, {"contentId", type text}, {"contentType", type text}, {"contentCreated", type datetime}, {"contentExpiration", type datetime}, {"Id", type text}, {"RecordType", Int64.Type}, {"CreationTime", type datetime}, {"Operation", type text}, {"OrganizationId", type text}, {"UserType", Int64.Type}, {"UserKey", type text}, {"Workload", type text}, {"UserId", type text}, {"ClientIP", type text}, {"UserAgent", type text}, {"Activity", type text}, {"ItemName", type text}, {"WorkSpaceName", type text}, {"DatasetName", type text}, {"ReportName", type text}, {"WorkspaceId", type text}, {"ObjectId", type text}, {"DatasetId", type text}, {"ReportId", type text}, {"Custom", Int64.Type}, {"Custom.1", type logical}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Content", "contentUri", "contentId", "contentType", "contentCreated", "contentExpiration", "RecordType", "OrganizationId", "UserKey", "Custom", "Custom.1"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "UserId", "UserId - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "UserId - Copy", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"UserId - Copy.1", "UserId - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"UserId - Copy.1", type text}, {"UserId - Copy.2", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"UserId - Copy.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","."," ",Replacer.ReplaceText,{"UserId - Copy.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Replaced Value",{{"UserId - Copy.1", "Name"}, {"WorkSpaceName", "WorkSpace Name"}, {"ReportName", "Report Name"}, {"DatasetName", "Dataset Name"}, {"CreationTime", "Creation Time"}})
in
    #"Renamed Columns1"

And we can create beautiful reports like this

Power BI Template:

I also created a template; you can download the template here –  https://1drv.ms/u/s!Avm7gbgZtlMlvkMAQEGMyQdLsfHu

When you open the template it will ask for loads of the parameters:

  • Get data for last 24 months  – A boolean value to pull data for last 24 hours or the given date. If this value is true, Power BI excludes values given in the next two parameters
  • AuditlogStartTime & AuditLogEndTime -Start Time of the Audit, Audit start and end has to be with 24 hours apart as per API documentation. and date has  to be in below format

YYYY-MM-DD
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS

  • TennantID – Office 365 Tennant ID
  • PubisherIdentifier – I have not used this, but it is recommended to use this for API calls
  • ClientID – Azure AD Client ID
  • ClientSecret – Azure AD App Client Secret
  • UserName& Password – User who has permission to view Office 365 Audit log

So does it answer my questions:

Well, I still have many unanswered questions, but this does help me in many ways. Using this template I can see the user behaviour, not only how many views etc. but interesting information about user activities. For example, in a certain workspace, if most of the users are exporting the report, then users are not happy with the way I presented the data.  Ideally, I don’t encourage users to be added to the workspace, using this I can see who are adding members to groups. I can see when a Power BI App created or updated. I can see how many users are printing the report, if many people are printing reports, probably I should not use many bookmarks, drill through functionality and add more Titles.

Summary:

There are many things I learned doing this process, PowerQuery Web.Contents can be used to POST and ZoomCharts introduced a logo underneath the visual to encourage users to buy their visuals 🙂

This entire model has many limitations, first of all, I can not schedule it as I am using two different API’s one to get Token and other to call API, but if you have Power BI Report Server, this can be scheduled. Next, data is always only for 24 hours, this is a limitation from API and I can go back to 7 days only.  Users need access to the Azure AD App and make sure Azure AD App has access to Office 365 Management API access. This page explains all steps – https://msdn.microsoft.com/en-us/office-365/get-started-with-office-365-management-apis . 

Hope this helps someone out there

Keep Smiling,

Prathy 🙂

25 comments
4 FacebookTwitterPinterestEmail

Back in September, I promised I would write another blog post with a beautiful looking Power BI template. Even though I did the model long ago, I did not get a chance to write a post until recently one of my blog readers reminded me. Thanks, Martin 🙂

Quick Recap:

As I mentioned in my previous blog post Calling Power BI API using Power BI Desktop to document Power BI Service, we can get a lot of metadata from Power BI service. I feel like, it is essential to have an overview of what’s happening on your tenant. The only place I know to get this information is Power BI API. I used Power BI API a data source in Power BI, I needed a bearer token to get any data from Power BI Service, so I used “https://login.windows.net/common/oauth2/token” URL to get the token. Which mean I looked at two different URL’s which restricted me to manual refresh in Power BI Desktop than having an automated schedule. Hopefully, it will change shortly.

You can use this template to answer many questions like:

  • Show me all dashboards in my organization
  • Total number of reports in the organization
  • Users who have admin rights
  • Number of refreshes
  • Number of failures
  • Why some refresh failed
  • When was the last time a certain dataset refreshed, if it’s failed why
  • And many more…

Template to Document the Power BI Service:

You can download the template here – https://1drv.ms/u/s!Avm7gbgZtlMlqGprbKHS6zHd0aPf

Above download link has a zip file with PBIT file and a CSV file. CSV file has user details required for authentication. This blog post covers the approach I used to call the API – https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-register-app/.Once you updated “UserDetails.CSV” file with essential information, save it to your preferred location.

When you open the template, you will see a pop message asking Full path of the UserDetails file, e.g. C:\Users\xxxx\OneDrive\xxx\PBI Portal\PBI\UserDetails.csv. Enter file location and click on the Load button. That’s all; it should just work. Sometimes, you may get a timeout error. Most of the times refreshing again will do the trick if not go to edit queries and choose “Refresh All queries”.

The first page has drill through functionality enabled where it shows the lil driller icon. Are you impressed with above screenshots, want to know how I designed this model? Find it here – https://wp.me/p5u6Pw-AQ 

Summary

Remember, you can see all data points only if you have enough rights and level of access is maintained through Azure App. Also, you can only retrieve the data from workspaces where the username you used to call API has the access. This Power BI model gives exposure to a lot of information but not all. Using this model, I can find all users who have access to a Workspace but not to Power BI Apps. If you want to know all users on your tenant, use Graph API as I showed here – http://prathy.com/2017/11/find-power-bi-users-organisation/. Ideally, I would love to see an OData compatible API like Microsoft Graph API so that I can schedule the refresh. Also, I don’t think we have an endpoint to get Power BI Apps or username of data source connections. Power BI hides Username in data source connections, so this would be something handy to understand which username we used to set up the data source. You may have noticed many placeholders, I would like to add much more information to it, and design reports a bit nicer.  If I make any changes to the template, I will keep this post updated or write another with changes. Hope this helps someone out there! Thanks for reading 🙂

Keep smiling,

Prathy 🙂

 

10 comments
2 FacebookTwitterPinterestEmail

When it comes to writing, I have the least trust in my writing skills. I always wanted my blog to sound more like a personal journey than some docs, whenever I look back mostly it doesn’t. I hope at least this post will sound like a journey. I am not an expert with visualizations; this post is all about my thought processing when I designed this template. Presentation slides or Power BI templates are like babies, you like yours. So, I would really like to hear your feedback on my approach. Feel free to use the comments section or send an email to Prathy@outlook.com

For me, one of the hidden pleasures of working with Power BI is working with colours and choosing the layouts. Of course, primary usage is the ability to design something to promote data-driven decisions in an organisation. I am not encouraging anyone to splash lots of colours on to your report. Designing reports in Power BI remind me of my Digital scrapbooking days. An empty page looks like a blank canvas except, the gesso smell. While I work with business obviously I follow branding and give importance to the user input; when it comes to my templates like my previous blog post “How to find all Power Users” and “POWER BI TEMPLATE TO DOCUMENT THE POWER BI SERVICE“, it gets difficult I keep on change things. This Template is still an unfinished project. I don’t think it will ever get finished. However, I learned a lot while creating this one. Hopefully, I will use my learnings in the future blog posts.

Let’s look at the first Page:

I usually create background images, decide the layout and then use that image as back ground in Power BI. I used image background for this template too. The background colour and left pane are part of image background. I could use frame and all other things as an image as well. That will look and work better. Although for this template, I kind of went with the flow and it was too late to change to the image. Frames: The sole reason behind the Frames was just because I like Frames. Anyone who worked with Power BI can easily guess why I went for the frame which has a lil gap on the top. It’s because of that little bar you get on top of every visual in Power BI  https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/12247677-optionally-remove-focus-mode-box

I tried following a pattern with the look and feel of below image

Most of the times, I used left pane for slicers or any valuable information I wanted to show. I kind of followed inverted pyramid concept, but excluding the left pane, I suppose. Colours: I have various data categories and I wanted to differentiate all those categories with different colours. I looked for popular colour combos and went for the colours I used in the below image. You can find popular colour combos here – https://www.colorcombos.com/popular-color-combinations/2

Pushpin affect, that’s just to show off my digital scrapbooking tact. I used treemap visual and layered that with a pushpin image.

The left pane, there is a lot of information here. Bottom table is something unnecessary, I needed something to fill up that space so I added by creating a calculated table. It’s a bit misleading or needs a better naming. It has Failed, Failures
Today and I also have a dynamic title with failure information. I was duplicating the same content which was not a wise choice.

Next comes the drill through affect: I added a drill through icon to show users that it has Drill Through functionality. Not sure that’s doing any justice. When I googled for drill through, that was the first image came up in my search feed also it was a perfect image to add a background colour. As I wanted to follow my pattern, so I went for colours which were good to show black foreground. When the user uses navigates to drill through page, that page will have the drill through icon background colour theme. Each frame which has some characters are separate table visuals. I formatted them to show like labels.

First, I changed Column Headers Font colour and Background colour to the background I used in the left pane, next increased Row padding to 11 so it aligns nicely with my drill icon. Then bottom to top, I formatted them to send Back, so when the user right clicks to choose drill through it will not interfere. Only when a user clicks on the table, they will be able to see the column header interfering with previous one otherwise it looks nice. Then I used a ribbon chart to show the number of refreshes and number of failures by Date to get an overview. Another pattern I followed is on each page, I added Last refresh data time, obviously to show when was the last time report refreshed.

rest of the pages are pretty much similar apart from few extra affects.

Page Workspaces:

Noticeably assorted colour pattern. Then, I used Bookmarks functionality.

I added an effect like clicking check boxes. Basically, I had several visuals on this page, I created three bookmarks by selecting the visuals I wanted to show. Then on each relevant bookmark, I went for ticked checkbox image than an unticked one. So when users access it using Power Bi service, it will look like a proper checkbox. You can find more information about bookmarks here – https://docs.microsoft.com/en-us/power-bi/desktop-bookmarks

Page Datasets:

Two noticeable approaches here are, using images and numbers to highlight some measures. I used https://www.iconfinder.com/ to get all my icons and I think, they do stand out quite well. Then, I used slicers as a lil button with full frame and I edited interactions to work only with the chart. I also separated them with other visuals using lines so it kind of explains they belong to chart only.

Page Gateways:

I have a lot of placeholders here. I do want to add more information from Gateway Data source query. One noticeable thing I did here is, in the Gateway contact information, I was showing a green circle to show it is live.

I am using an image reference here. I created a measure with following DAX

Status = IF(Gateways[Gateway Status]="Live","https://upload.wikimedia.org/wikipedia/commons/0/0e/Ski_trail_rating_symbol-green_circle.svg",Blank())

Then, I updated table properties to use image height as 24, 24 is the minimum in the current version.

All rest of the pages are similar, nothing too exciting. Hope it gives some ideas to someone out there. Let me know what you think about it.

Prathy

2 comments
2 FacebookTwitterPinterestEmail

If you are the goto person for Power BI in your organisation, then I am sure at least once you may have been asked about Power BI users question, I get asked many times. Of course, I can go to Power BI Service, check each workspace and Power BI Apps and see who has access to what; not a productive way when I am working with many workspaces. Or as I blogged before, I can use Power BI API to get all users data, probably a better way when I am working with many workspaces. If I share content using Power BI Apps and given access using security group, It gets difficult to understand who has access to what, who is a member of what groups.

If you have enough rights, you can view users information and members of which groups in Office 365 portal, again not a very productive way.  However, by using Power BI and Graph API, we can create a report to show all users information. I will be able to see all users who have Power BI license. I can slice this data by Location, Team and many more. It’s not only Power BI; I can view all other licenses. For example, Power Apps and Microsoft Flow. Now I can easily see who has access and monitor users and license, If you want to know about Graph API and how to use it with Power BI, check out my earlier blog post – Access Microsoft Graph API using Power BI.

The Template:

By using Microsoft Graph API, I created a template, where you can see all users, their groups and their license details. You can download the template here – https://1drv.ms/u/s!Avm7gbgZtlMlqB12UF4WXFGzzgXk .

Using template:

When you open the Pbit file, you will see a pop up window with two parameters.

Enter these values Graph API URL = https://graph.microsoft.com/  and Graph API  Version =  beta. After a while, based on size, the template should get populated with data.  If you want to understand the logic behind the template, carry on reading …

10 comments
2 FacebookTwitterPinterestEmail