PowerBI Embedding – AppOwnsData

There are two main scenarios for embedding Power BI content:

Watch this: https://www.youtube.com/watch?v=5OF0s2t0N3w

https://app.powerbi.com/embedsetup

AppOwnsData

If you need to see how it works in single page web application, please scroll down to the end of this article.

Read this article first:
https://cloudarchitected.com/2019/03/embedding-power-bi-content-with-a-service-principal/

The script in above article can be downloaded from here:

https://trello-attachments.s3.amazonaws.com/5dd74caa1ac8d95449e57295/5dd74e74f2ae0822de6c12f4/a7d389968401e2c7328e2f6d20caf4d0/InitAppOwnsData.ps1

1. Update the parameters, then execute the script.
2. Ensure step 4 in that article: new AzureADGroup should be in “Developer settings” -> “Allow service principals to use Power BI APIs”

image
3. Verify Service Principal (application name) in Group Members

image-1
4. Verify AzureADGroup and Service Principal are having Admin permission in Power BI workspace

image-2

For testing an embedded app: Clone the Power BI Developer Sample at https://github.com/Microsoft/PowerBI-Developer-Samples. Open the App Owns Data solution in Visual Studio. Edit the following sections of the Web.config file. Include your Application ID (for your service principal), workspace ID and report ID.

5. Go to the solution in Visual Studio, ensure $key in step 2 is using as “applicationSecret” in web.config (we can also use the key generated in App Registration -> Certificates & secrets)

image-4
6. Optional: Go to Azure portal, grant permission in Enterprise Application and App Registered if needed

Single Page Web application for Power BI Embed

1. Clone the source at: https://github.com/CriticalPathTraining/PowerBiEmbedded

2. Go to “Demos\PowerBiDaySPA” and open it using Visual Studio Code

3. Open “AppSettings.ts” and modify value

image-5

4. Run npm install if needed

5. npm run start to try the embed in single page application

Background for Power BI Chiclet Slicer

Let’s say that you want to color your slicer to become something like this:

ChicletSlicerWithBackgroundColor

First, you need to create a color table for it:

TestColours =
UNION (
ROW ( “Index”, “1”,”RiskType”, “Low”, “ImgURL”, “https://htmlcolors.com/color-image/85e38d.png”, “ImgAsString”, “data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAfQAAAH0AQMAAADxGE3JAAAABlBMVEUAAACF442kwz8WAAAAeklEQVR4nO3LMQ0AAAgDsPlXiwMmgo+0f7MnE9/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/33/wCFkN6z5EHC68AAAAASUVORK5CYII=” ),
ROW ( “Index”, “2”,”RiskType”, “Medium”, “ImgURL”, “https://htmlcolors.com/color-image/fdf542.png”, “ImgAsString”, “data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAfQAAAH0AQMAAADxGE3JAAAABlBMVEUAAAD99ULpYmwIAAAAeklEQVR4nO3LMQ0AAAgDsPlXiwMmgo+0f7MnE9/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/33/wCFkN6z5EHC68AAAAASUVORK5CYII=” ),
ROW ( “Index”, “3”,”RiskType”, “High”, “ImgURL”, “https://htmlcolors.com/color-image/ff9005.png”, “ImgAsString”, “data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAfQAAAH0AQMAAADxGE3JAAAABlBMVEUAAAD/kAUa1PO3AAAAeklEQVR4nO3LMQ0AAAgDsPlXiwMmgo+0f7MnE9/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/33/wCFkN6z5EHC68AAAAASUVORK5CYII=” ),
ROW ( “Index”, “4”,”RiskType”, “Critical”, “ImgURL”, “https://htmlcolors.com/color-image/ff0000.png”, “ImgAsString”, “data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAfQAAAH0AQMAAADxGE3JAAAABlBMVEUAAAD/AAAb/40iAAAAeklEQVR4nO3LMQ0AAAgDsPlXiwMmgo+0f7MnE9/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/3fd/33/wCFkN6z5EHC68AAAAASUVORK5CYII=” )
)

ColorTable

in that table:

Index: Index of the color
RiskType: The meaning of color name in this case
ImgURL: https://htmlcolors.com/color-image/hex-color.png (hex-color can be the value of the color in hex format)
ImgAsString: Use https://www.base64-image.de/ to convert the color fron ImgUrl to string, this can help improve performance when use color as background.

Then, In ChicletSlicer visualization:

ChicletSlicerSettings

 

 

Risk matrix in power bi

There are a lot of questions in community for how to build a risk matrix in Power BI. I’ve seen some of them and realize that I can do it in another way. Here is how I solve it in my report:

1. Definition of Risk Matrix

My risk matrix is defined by 2 axes: Propability (Y) and Consequense (X), the graph I want to build is similar this:

RiskMatrixDefinition1

2. Number in cells

The number in each cell is the count of risk, based on the risk title. Formula for this measure column is like this:

StrRiskCount = if(DISTINCTCOUNT(AllRisks[Title]) = BLANK(), “”, “”&DISTINCTCOUNT(AllRisks[Title]))
Humm, you can see on the image above, the numbers are difference in each cell, how can we do that? Yes, we need to know the position of the cell first. I defined the position like this:
RiskMatrixDefinition
This is how we can specify the row in the specific cell: Example for the cell 1-5
PositionCustomColumn.png
To check the value in each cell, we can use the “Card” in visualization, for example with the cell 1-5 above, we can do like this:
CardVisualization
3. Color for the cells
Depended on the location of  the risk, we can divide the risk to 4 risktypes: Low, Medium, High, Critical. Each type will associate to a specific color:
Low #85e38d
Medium #fdf542
High #ff9005
Critical #ff0000
If you need the ImageURL to the color, here is the very good link: https://htmlcolors.com/color-image/hex-color.png, replace hex-color with the value of hex-color to see the real image
Example:
If you want to get the image url for the low level above, here is the URL: https://htmlcolors.com/color-image/85e38d.png
Put cells to level, here is the example for low level:
LevelGroup
The type for the risk:
RiskType
RiskTypeNo
Setup color-background for the cell:
MinRiskType = if(MIN(AllRisks[RiskTypeNo]) = BLANK(), 0, MIN(AllRisks[RiskTypeNo]))
CustomRiskColor
RiskTypeColor
Adjust the card visualization to make it like a square, then clone it to 24 more instances. Make sure you modify the filters in all cards to display the correct number.
Then, align them like a big square (contain 25 smaller cards)
Same as other MS tools, you can use “Format painter” to copy the format from 1 card to another card.

Excel REST API in SharePoint Online to PowerBI

Let’s says that you have an excel file located in a SharePoint library, and you know where to get data like this, and you want to display them in Power BI:

ExcelTableName

Yes, PowerBI can get data from an excel file, but how can we do that for excel online? Luckily, there are some example URL from excel services that we can use to fetch data from Excel:

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/sample-uri-for-excel-services-rest-api

For PowerBI, Odata maybe the best one to selected

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/using-odata-with-excel-services-rest-in-sharepoint

1. Get data from web (past odata link to the URL)

DataFromWeb

2. Sign in by your organization account

SignIn

3. Make sure you have data in Power Query Editor

PowerQueryEditor

Like this

QueryResult

Silent Sign In on SharePoint hosted app

This is for you to embed sharepoint hosted app to your main web with silent login:

Let’s say your app start page is like this:

https://your_sp_ab-e231aa523fe845.sharepoint.com/YourApp_Viewer365App/YourApp_ViewerStartPage.aspx?entrypoint=https://editor.YourApp_.com/intra/portal#/model/787e8934a8184c4a99152cca823a08cd&SPHostUrl=https://your_sp_ab.sharepoint.com&SPHostTitle=your_sp_ Intranet&SPAppWebUrl=https://your_sp_ab-e231aa523fe845.sharepoint.com/YourApp_Viewer365App&SPLanguage=en-US&SPClientTag=2&SPProductNumber=16.0.19131.12021&SenderId=543305360

Then you need to sign-in from your app

https://your_sp_ab-e231aa523fe845.sharepoint.com/YourApp_Viewer365App/_layouts/15/silentSignIn.aspx?redirect_uri=https://your_sp_ab-e231aa523fe845.sharepoint.com/YourApp_Viewer365App/YourApp_ViewerStartPage.aspx?entrypoint=https%3A%2F%2Feditor%2EYourApp_%2Ecom%2Fintra%2Fportal%23%2Fmodel%2F787e8934a8184c4a99152cca823a08cd&SPHostUrl=https%3A%2F%2Fyour_sp_ab%2Esharepoint%2Ecom&SPHostTitle=your_sp_%20Intranet&SPAppWebUrl=https%3A%2F%2Fyour_sp_ab%2De231aa523fe845%2Esharepoint%2Ecom%2FYourApp_Viewer365App&SPLanguage=en%2DUS&SPClientTag=2&SPProductNumber=16%2E0%2E19131%2E12021&SenderId=543305360

then sign-in from your main web

https://your_sp_ab.sharepoint.com/_layouts/15/silentSignIn.aspx?redirect_uri=https://your_sp_ab-e231aa523fe845.sharepoint.com/YourApp_Viewer365App/_layouts/15/silentSignIn.aspx?redirect_uri=https%3A%2F%2Fyour_sp_ab%2De231aa523fe845%2Esharepoint%2Ecom%2FYourApp_Viewer365App%2FYourApp_ViewerStartPage%2Easpx%3Fentrypoint%3Dhttps%253A%252F%252Feditor%252EYourApp_%252Ecom%252Fintra%252Fportal%2523%252Fmodel%252F787e8934a8184c4a99152cca823a08cd%26SPHostUrl%3Dhttps%253A%252F%252Fyour_sp_ab%252Esharepoint%252Ecom%26SPHostTitle%3Dyour_sp_%2520Intranet%26SPAppWebUrl%3Dhttps%253A%252F%252Fyour_sp_ab%252De231aa523fe845%252Esharepoint%252Ecom%252FYourApp_Viewer365App%26SPLanguage%3Den%252DUS%26SPClientTag%3D2%26SPProductNumber%3D16%252E0%252E19131%252E12021%26SenderId%3D543305360

you should encode the redirect_uri to be able to use it in your iframe.

https://your_sp_ab.sharepoint.com/_layouts/15/silentSignIn.aspx?redirect_uri=https%3A%2F%2Fyour_sp_ab%2De231aa523fe845%2Esharepoint%2Ecom%2FYourApp_Viewer365App%2F%5Flayouts%2F15%2FsilentSignIn%2Easpx%3Fredirect%5Furi%3Dhttps%253A%252F%252Fyour_sp_ab%252De231aa523fe845%252Esharepoint%252Ecom%252FYourApp_Viewer365App%252FYourApp_ViewerStartPage%252Easpx%253Fentrypoint%253Dhttps%25253A%25252F%25252Feditor%25252EYourApp_%25252Ecom%25252Fintra%25252Fportal%252523%25252Fmodel%25252F787e8934a8184c4a99152cca823a08cd%2526SPHostUrl%253Dhttps%25253A%25252F%25252Fyour_sp_ab%25252Esharepoint%25252Ecom%2526SPHostTitle%253Dyour_sp_%252520Intranet%2526SPAppWebUrl%253Dhttps%25253A%25252F%25252Fyour_sp_ab%25252De231aa523fe845%25252Esharepoint%25252Ecom%25252FYourApp_Viewer365App%2526SPLanguage%253Den%25252DUS%2526SPClientTag%253D2%2526SPProductNumber%253D16%25252E0%25252E19131%25252E12021%2526SenderId%253D543305360

See more here:

https://splucy.wordpress.com/2014/11/20/getting-the-host-page-url-in-an-app/

http://giancode1.blogspot.com/2013/06/get-host-query-string-parameters-in-app.html?m=1

Cool.

 

Share PowerBI dataset to external user

From any existing user has PowerBI Pro license, you can share the reports to user who don’t have it => your shared user will be in your tenant AD, and will be logged on as external user.

  1. Share the dataset ManagePermissionOnDataset
  2. Make sure the Non – Pro Power BI user is hereDatasetShared.png
  3. Copy the report link from a reportReportLink
  4. Logon to the shared user and browse to the link above, will be a dialog to “Try Pro for free”TryProForFree.png
  5. Yeah, just try itStartTrial.png
  6. CongratulationCongratulation.png
  7. Check again on your Power BI serviceMakeSure.png

Wildcard SSL for SP and high-trust-certificate

NOTE: The certificate for  high trust does not support CNG enabled. Have to create with a legacy option.

I. Follow this to create a new certificate using for SSL:

https://support.jetglobal.com/hc/en-us/articles/235636308-How-To-Create-a-SHA-256-Self-Signed-Certificate

Example:

$cert = New-SelfSignedCertificate -DnsName “*.spdev.com” -CertStoreLocation “cert:\LocalMachine\My”

Then, go to iss web application -> bindings -> edit -> https -> SSL certificate: *.spdev.com -> host name: http://www.spdev.com -> port 443

launch the web application, on address bar, click to install the certificate to the computer

II. Follow this to create a new certificate for SP high trusted app:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/create-high-trust-sharepoint-add-ins

Example:

  1. Create a self-sign-certificate
  2. Export to pfx
  3. Export to cer
  4. Open MMC, install certificate to “Trusted Root Certificate Authority” and “Personal”
  5. On Personal, right click to the certificate -> All Tasks -> Manage Private Keys…-> Add user in your wep application pool to here
  6. Share certificate folder to everyone
  7. ……………………
  8. New-SPTrustedRootAuthority (remove first if already existed)
  9. New-SPTrustedSecurityTokenIssuer (remove first if already existed)
  10. AllowOAuthOverHttp
  11. Update OnPrem.config for all web applications
  12. IIS reset all

 

Get User Profile Properties with REST API

Thanks Vardhaman Deshpande for this tips:

1) Get all properties of current user:

http://siteurl/_api/SP.UserProfiles.PeopleManager/GetMyProperties

2) Get single property of current user:

http://siteurl/_api/SP.UserProfiles.PeopleManager/GetMyProperties/PictureUrl
OR
http://siteurl/_api/SP.UserProfiles.PeopleManager/GetMyProperties?$select=PictureUrl

3) Get Multiple Properties for the current user:

http://siteurl/_api/SP.UserProfiles.PeopleManager/GetMyProperties?$select=PictureUrl,AccountName

4) Get all properties of Specific User:

For Office 365/SharePoint Online:
http://siteurl/_api/SP.UserProfiles.PeopleManager/GetPropertiesFor(accountName=@v)?@v=’i:0%23.f|membership|vardhaman@siteurl.onmicrosoft.com’

For SharePoint 2013 On-Premises:
http://siteurl/_api/SP.UserProfiles.PeopleManager/GetPropertiesFor(accountName=@v)?@v=’domainusername’

5) Get Specific UserProfile Property of Specific User:

For Office 365/SharePoint Online:
http://siteurl/_api/SP.UserProfiles.PeopleManager/GetUserProfilePropertyFor(accountName=@v,propertyName=’LastName’)?@v=’i:0%23.f|membership|vardhaman@siteurl.onmicrosoft.com’

For SharePoint 2013 On-Premises:
http://siteurl/_api/SP.UserProfiles.PeopleManager/GetUserProfilePropertyFor(accountName=@v,propertyName=’LastName’)?@v=’domainusername’

6) Get Multiple UserProfile Properties for Specific User:


http://siteurl/_api/SP.UserProfiles.PeopleManager/GetUserProfilePropertiesFor