BI

ByHariharan Rajendran

StreamGraph Visual in Power BI VS R

This post just shows the comparison of streamgraph in Power BI Custom Visual and R Script Visual.

Power BI Desktop

We have a streamgraph custom visual available in Power BI. Download and import into Power BI Desktop.

https://store.office.com/en-us/app.aspx?assetid=WA104380772&sourcecorrid=373810e3-72d4-4afd-96e7-6859505efb87&searchapppos=5&ui=en-US&rs=en-US&ad=US&appredirect=false

Here I used a very simple dataset to explore the visual.

Pass the above dataset values to chart.

R Studio  

To get the streamgraph visuals, we need to install the streamgraph package and other supporting packages .

Run the below script in R Studio or R console application.
install.packages(‘devtools’)
install.packages(‘Rcpp’)
install.packages(‘zoo’)
install.packages(‘DBI’)
install.packages(‘assertthat’)
install.packages(‘tibble’)
install.packages(‘yaml’)
devtools::install_github(“hrbrmstr/streamgraph”)
library(streamgraph)
# Create data:
year=rep(seq(2000,2005) , each=1)
name=c(letters[1:6])
value=c(1000,200,600,500,1250, 300)
data=data.frame(year, name, value)
streamgraph(data, key=”name”, value=”value”, date=”year”)

Power BI Desktop R Studio
Import Streamgraph visual Install Streamgraph and related packages
Visual values are keep

amending on the initial value

Identifies the maximum value and accommodate all the values within the maximum range
It is not interactive It is interactive
GUI Type R Script Type
Can’t export as HTML Can export as HTML
ByHariharan Rajendran

DAX Query CALENDARAUTO()

In Power BI, you can use the pre-defined function CALENDARAUTO() to create date table with a column called “Date”. It will generate a date for current 1 year by default.

This above function will only work when you have a model with at least one column with date or datetime data type.

In the below screenshot, I tried to use CALENDARAUTO() function in an empty model.

You can see the warning message above.

“CALENDARAUTO() function can not find a base column of DateTime type in the model”

In the same scenario, I just added a simple table with one date data type column then I used the CALENDARAUTO() in a new table and this time I got the expected result.

Happy Learning. Share your comments.

ByHariharan Rajendran

SharePoint List to Power BI & Extending Power BI to SharePoint

This article explains to you that how we can use SharePoint list as a source for Power BI and also extending the Power BI report to embed on SharePoint.

I am following the same strategy in all my blogs. Reading a big paragraph is tough than a step by step points as it is easy to follow and remember.

Flow,

Pre-Requisites:

  1. SharePoint List URL
  2. SharePoint Credentials (On-premises or O365)
  3. Power BI Account
  4. Power BI Desktop

Follow the below steps,

Step 1: Create a SharePoint list as per your business needs. It can be simple or complex with calculated columns. In my case, I created a simple SharePoint list with some sample content as like below.

Step 2: Get the SharePoint URL where the list is created.

https://xxxxx.sharepoint.com/Training

Step 3: Open Power BI Desktop and click Get Data.

Step 4: Choose SharePoint Online List, if you use O365. Click Connect

Step 5: Pass the O365 SharePoint URL and credentials.

Step 6: Choose the list and click Load.

Step 7: It will load all the columns from the SharePoint list. Choose the visualization and map with the respective fields.

Step 8: Save and publish the report to Power BI online services

Step 9: Login to app.powerbi.com with your credentials.

Step 10: Open your report from your workspace where you published.

Embed the Report in SharePoint

So far, we created a report using SharePoint list. Now we are going to embed the report in SharePoint.

Follow the below steps.

Step 1: Open the report in app.powerbi.com

Step 2: If we have O365 E series license then we can use “Embed in SharePoint Online (preview)” option otherwise choose the below option.

Step 3: It will ask you to publish.

Step 4: Finally, it will generate URL and iframe code to place on any web page of SharePoint.

Step 5: Go to SharePoint page and edit where you want to embed this report.

Step 6: Click Insert and choose the Embed Code

Step 7: Copy and paste the iframe

Step 8: Save the page.

Share your comments below.

Related Post:

Embed Power BI Report in Web page

ByHariharan Rajendran

Embed Power BI Report in Web page

This article explains that how can we show your Power BI report to everyone.

We have variety options in app.powerbi.com for the specific report.

The notable options are,

Publish to Web

Embed in SharePoint Online (preview)

Export to PowerPoint (preview)

Let us discuss “Publish to Web” Option

Open your Power BI report and click File option. You can see the options as like below.

Choose “Publish to Web” and click “Create embed Code”

Then click “Publish”, it will create a code for us.

We can share the link to anyone or else can embed the iframe in any web page or in SharePoint.

Check the report,

https://app.powerbi.com/view?r=eyJrIjoiN2UwNzRmYzYtNTAxYy00NTE1LTkxZTAtYzgyZTk1MzFhYWEyIiwidCI6Ijk2ODY3NjIxLTgxNDAtNGRjYS1iZTliLTMyMTkxMmU0NTY2NCIsImMiOjEwfQ%3D%3D

Sachin Tendulkar’s Cricket Data – Embedded Report

[field name=Iframe]

 

ByHariharan Rajendran

PowerBI Reports in SQL Server Reporting Services

Recently, Microsoft released a Technical Preview of PowerBI in SQL Server Reporting services. It has opened a door to create and deploy PowerBI reports within the Reporting services.

This will be a great feature that who are all searching for great report representations in SQL Server Reporting services itself.

This article illustrates, the new look and feel of SQL Server Reporting Services portal and diverse approaches to publish the PowerBI reports.

As we have PowerBI Desktop tool from PowerBI Team, we need to use this tool to develop the PowerBI Reports. It means, we need to use two different tools for developing and publishing reports, i.e. regular SSRS reports from Visual Studio data tools and PowerBI reports from PowerBI Desktop.

Look and Feel

As you know, the look and feel of SQL Server Reporting Services Portal has changed from SQL Server 2016.

In this technical preview, there is a new section added at the bottom to hold all the PowerBI Reports but still we can manage inside our regular report folders.

PowerBi

 

Approaches to publish PowerBI Reports to the portal,

Approach 1:

Develop reports in PowerBI Desktop and perform “Save As” to directly publish (deploy) to portal.

Bi1

 

BI2

Approach 2:

Save the report in local folder and upload manually to the portal.

Bi3

ByHariharan Rajendran

Dataset for Power BI from SharePoint Team Site

We discussed about preparing datasets using files from different environment (Local drive & OneDrive) in my previous articles.  Go through here.

We have another option under files section which is “SharePoint – Team Sites”.

It is very easy to get a file (dataset) from SharePoint as we are using business email to connect Power BI so no need to provide the credentials again.

Select the file from SharePoint and connect to Power BI. File will be added in to Datasets.

PowerBI9

 

ByHariharan Rajendran

Dataset for Power BI from Onedrive

In my last article, we discussed about how to prepare a dataset from local file.

This article speaks about how we can create a dataset which is stored in OneDrive. It can be personal or business OneDrive.

There is no changes up to 4th step from my previous article, after that follow the below steps

  1. Choose either OneDrive – Business or OneDrive – Personal where the file stored. PowerBI6
  2. Here am using OneDrive – Personal, click it.
  3. It will ask for Microsoft account credentials to access OneDrive.PowerBI7
  4. Choose the file from OneDrive and select Connect which is on top right.PowerBI8
  5. Selected file will be available under Datasets in Power BI

 

ByHariharan Rajendran

Prepare data for Power BI  – Local File

As we discussed the levels in Power BI, the first level is creating dataset.

To start prepare the dataset, first sign in to the power BI here,

This article will talk about, how to create a dataset from local file.

Steps to import the local file,

  1. Sign in to the Power BIPowerBI
  2. Check for Get Data and click as like below.
  3. Click on “Get” under FilesPowerBI1
  4. It will display the options to import the files from different places.PowerBI2
  5. Click on “Local File”
  6. Select the file from your local folder and press open. Make sure that file should have both dimension and measure values. Power BI only supports Excel and CSV files and Power BI Desktop files.PowerBI3PowerBI4
  7. Check for uploaded files under Datasets.PowerBI5
  8. Dataset is ready to create reports and dashboards.