Business Process Analysis in PowerBI using R visuals
Today companies store huge amounts of data related to their various business processes. This data can help discover, monitor and improve your actual business process. The process of extracting process knowledge from data is called Process Mining. Process Mining can help gain better visibility, improve KPIs and eliminate bottlenecks. One of the popular open source packages to help with process mining is bupaR. It is an open-source, integrated suite of R packages for the handling and analysis of business process data. It was developed by the Business Informatics research group at Hasselt University, Belgium. It currently consists of many packages which can help in calculating descriptives, process monitoring and process visualization. The bupaR is the core package of the framework. It includes basic functionality for creating event log objects in R. It contains several functions to get information about an event log and provides specific event log versions of generic R functions. Together with the related packages, each of which has its own specific purpose, bupaR aims at supporting each step in the analysis of event data in R, from data import to online process monitoring. The good news is that now PowerBI service supports bupaR visuals. Let’s explore what we can do! Our attempt here is to just quickly show a few possibilities with bupaR and PowerBI. You can read more about bupaR in some of the links below. For more information on how to create R visuals in the Power BI service, please see Creating R visuals in the Power BI service and Create Power BI visuals using R. Let’s consider the scenario of patients arriving in an emergency department of a hospital. The event data in this example comes from "patients" dataset from eventdataR package. I made the sample data as a .csv file, then imported the data into PowerBI desktop and next I will show you how to use bupaR to create event logs and plot visuals from PowerBI. The data looks like below picture in PowerBI desktop. If you are interested to see the process map for the "completed" patients event log, which starts with "Registration" and ends with "Check-out", you can create the R visual in the Power BI Desktop with the following R script: Once it gets published to Power BI service, we can see it renders as the following image. If you want to see frequency in the process map, it can be created explicitly using the frequency function. The colors can be modified through the color_scale argument. library(bupaR) library(DiagrammeR) patientsData <- dataset patientsData$time <- as.POSIXct(patientsData$time, tz = "GMT", format = c("%Y-%m-%d %H:%M:%OS")) x <- patientsData %>% eventlog( activity_id = "handling", case_id = "patient", resource_id = "employee", activity_instance_id = "handling_id", lifecycle_id = "registration_type", timestamp = "time" ) %>% process_map(type = frequency("relative", color_scale = "Purples"), render=FALSE) export_graph(x, "result.png", file_type = "png") Another example below uses Performance profile focusing on processing time of activities. library(bupaR) library(DiagrammeR) patientsData <- dataset patientsData$time <- as.POSIXct(patientsData$time, tz = "GMT", format = c("%Y-%m-%d %H:%M:%OS")) x <- patientsData %>% eventlog( activity_id = "handling", case_id = "patient", resource_id = "employee", activity_instance_id = "handling_id", lifecycle_id = "registration_type", timestamp = "time" ) %>% process_map(performance(median, "days"), render=FALSE) export_graph(x, "result.png", file_type = "png") Different activity sequences in the event log can be visualized with trace_explorer. It can be used to explore frequent as well as infrequent traces. The coverage argument specifies how much of the log you want to explore. Below example shows the most frequent traces covering 98.5% of the event log. library(bupaR) patientsData <- dataset patientsData$time <- as.POSIXct(patientsData$time, tz = "GMT", format = c("%Y-%m-%d %H:%M:%OS")) patientsData %>% eventlog( activity_id = "handling", case_id = "patient", resource_id = "employee", activity_instance_id = "handling_id", lifecycle_id = "registration_type", timestamp = "time" ) %>% trace_explorer(type="frequent", coverage = 0.985) The last example below shows in how many cases each of the activities is present. library(bupaR) patientsData <- dataset patientsData$time <- as.POSIXct(patientsData$time, tz = "GMT", format = c("%Y-%m-%d %H:%M:%OS")) patientsData %>% eventlog( activity_id = "handling", case_id = "patient", resource_id = "employee", activity_instance_id = "handling_id", lifecycle_id = "registration_type", timestamp = "time" ) %>% activity_presence %>% plot Known limitation: The dataset in PowerBI is a dataframe. To use bupaR, you'll need to convert it to event logs as the given sample R scripts. References: 1. https://en.wikipedia.org/wiki/Process_mining 2. https://www.bupar.net/index.html 3. https://www.r-bloggers.com/bupar-business-process-analysis-with-r/ Lei Qian | Software Engineer at Microsoft Power BI (Artificial Intelligence) team
Regresion lineal multiple R studio Power Bi
Hola, ejecuté un script de r para calcular una regresión lineal multiple. Pude obtener el intercepto y slope y traspasarlos a una tabla utilizando library broom tidy(model). library(broom) model <- lm(Energía_electrica~ Grados + Transacciones,dataset) model <- tidy(model) esta es la base de datos Mes Energía_electrica Grados Transacciones Tienda ene-22 93.471 337 58.567 CD feb-22 82.053 319 51.095 CD mar-22 79.777 295 55.652 CD abr-22 67.907 204 49.633 CD may-22 71.634 185 49.520 CD jun-22 68.864 164 49.280 CD jul-22 69.559 159 50.992 CD ago-22 67.204 161 49.913 CD sept-22 63.007 172 43.913 CD oct-22 65.902 204 48.704 CD nov-22 66.469 256 46.081 CD dic-22 73.164 326 50.385 CD ene-22 132.321 354 66.405 hc feb-22 108.787 313 61.665 hc mar-22 114.572 304 65.195 hc abr-22 94.501 207 56.435 hc may-22 98.740 170 55.612 hc jun-22 87.222 151 52.630 hc jul-22 81.592 123 54.133 hc ago-22 74.998 142 50.118 hc sept-22 67.039 188 43.383 hc oct-22 77.155 212 49.618 hc nov-22 108.387 260 48.931 hc yo necesito calcular la regresión para cada tienda y que la tabla con slope e intercepto me haga una distinción de cual es para cada tienda o que con los filtros los números se actualicen (debo tener slope e intercepto en una tabla porque luego necesito ocupar esos numeros para formulas). Se puede hacer algo como un ciclo for o alguna distinción en esta nueva tabla para filtrar por tienda?
Creating a Custom Gauge Visual
I'm trying to create a custom visual that shows the distribution of data through a visualisation similar to a boxplot. I then want to have a point on that plot that moves along the boxplot depending on the value of a slicer. But the problem is my slicer moves the boxplot and the scale aswell. I created my visual with R and I've included the code below: Dar <- ggplot(data = dataset, aes(x = 0, y = Data)) + scale_color_viridis_d( option = "mako", name = "Level:", direction = -1, begin = .15, end = .9, labels = function(x) paste0(as.numeric(x)*100, "%") ) + ggdist::stat_interval() + coord_flip() + stat_summary(geom = "point", fun = "median", color = "red", size = 6, pch = 17, aes(x = -0.05, y = Data) ) Dar And this is what my visualisation looks like: Any help would be greatly appreciated!
Personal Gateway for R script
Hi there, I have used an R script in my BI Dashboard, published it and set an auto refresh with a personal gateway. The personal gateway is on and ready to use, the auto refresh works with no error but the column that refrences the R code does not show the correct data. The column is meant to show Trues and falses but it only shows False. When I refresh in desktop it works perfectly and the column displays the correct info. I think I am missing something in the personal gateway or in the actual R code but I have read everything I can find on this and just can't seem to get it right. I am new to using R in BI so the whole process has been trial and error but it is just odd that it works in desktop but not service. The R code is used as a step in one of my tables in query editor, this is how it starts off: new_dataset <- dataset new_data <- data.frame(new_dataset) HS <- new_data$"US_HTS_Code__c" COO <- new_data$"Country_of_Origin2__c" new_data$ADDFlag1 <- Any guidance will be greatly appreciated, thanks so much!Scheduled refresh of SQL source but with R script transformstions
Hello. I am exploring using PBIRS as the server to host Power BI reports, which will extract/source data from a SQL Server Database, and which will subsequently use R scripts to transform the data in various ways. Per the link below, as far as I understand R scripts themselves are not a source which can have PBIRS scheduled refreshes applied to them. As such, I'm wondering the following: 1.) Is there a way to work around this so that I can in fact schedule refreshes of a single R script which will hold the code for the entire ETL, 2.) If SQL Server Database is the source and transformations are applied using R scripts, does scheduled refresh work in this scenario? https://docs.microsoft.com/en-us/power-bi/report-server/data-sources#list-of-supported-authentication-methods-for-model-refresh
R Script in direct query
I am using R Script in Direct Query to retrieve data from SQL server using SP by passing logged-in user id. In Power Query Editor window, the script runs fine and retrieves data. But on applying changes to the report, it is throwing below error: Microsoft SQL: Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'. Sample code: let RScript = R.Execute("output <- read.table(text=system2(""whoami"", stdout=TRUE))"), output = Record.FieldValues(RScript{[Name="output"]}[Value]{0}){0}, Source = Sql.Database("SQLServerName", "DBName", [Query="EXEC SP_Name '"&output&"'"]) in SourceR-Script Visual Runtime Error in Power BI Server
Hi Everyone, I wrote a R-Script that works/runs perfectly in Power BI Desktop but doesn't work when published to the Power BI Service. I'm not able to pinpoint the error, is it the pipe operator (%<%)? Any help would be highly apperciated, I've been struggling with this issue for some time now. Script Runtime Error: Evaulation Error: Level sets of factors are different R-Script: # The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script: # dataset <- data.frame(ProjectName, Commodity, WBS_code, Months, Running_Total_V2, Total_Scope_V2, RTPercentCompleteV2, Project_Description) # dataset <- unique(dataset) # Paste or type your script code here: library(dplyr) library(ggplot2) library(RColorBrewer) proposal_project <- unique(dataset[["Project_Description"]]) # there are duplicates due to WBS df <- dataset %>% select(ProjectName, Commodity, Months, RTPercentCompleteV2) %>% distinct() # for projects with multiple entries for the same percent complete, keep the highest value df <- df %>% group_by(ProjectName, Commodity, RTPercentCompleteV2) %>% slice(which.max(Months)) %>% arrange(Months) # group by and interpolate pctcomplete <- seq(0.1, 1, by=0.05) df <- df %>% group_by(ProjectName, Commodity) %>% group_modify(~ {approx(.x$RTPercentCompleteV2, .x$Months, xout=pctcomplete) %>% data.frame()}) %>% ungroup() prop_df <- df %>% filter(ProjectName == proposal_project) # split out proposal data after transformations df2df <- df %>% filter(ProjectName != proposal_project) data_wo_proposal <- df2df %>% group_by(Commodity,x) %>% summarise_at(vars(y),list(mean_duration = mean)) # plot mindiff <- min(c(0)) maxdiff <- max(c(max(data_wo_proposal$mean_duration, na.rm=TRUE), max(prop_df$y, na.rm=TRUE))) ticks <- round(seq(mindiff, maxdiff, by=2)) tick_y <- seq(0,100, by=5) g <- ggplot(data=data_wo_proposal, aes(x=mean_duration, y=x, colour=Commodity)) + geom_line(linetype=2) + geom_point() final_plot <- g + geom_line(data = prop_df, aes(x=y,y=x,colour=Commodity)) + geom_point(data=prop_df,aes(x=y,y=x,colour=Commodity)) final_plot_label <- final_plot + labs(title='Average Duration of Reference Projects vs Proposal Project', subtitle=paste('Proposal Project:',prop_df$ProjectName[1], '(solid line in figure below)'), x= 'Duration (Months)',y='Percent Complete') + theme_bw() + theme(legend.position='bottom', legend.title=element_blank()) + scale_colour_brewer(palette='Set1') + scale_x_continuous(breaks=ticks)+scale_y_continuous(labels = scales::percent_format(accuracy = 1),breaks=seq(0,1, by=.05)) final_plot_label Thank you, Val
R Custom Power BI Visualization Variable Input Handling
I am trying to create a custom viz (scatter plot) with the ability to provide different options for colour and shape of the points using ggplot2. My visualisation works fine when I specify both the Shape and Colour parameter, however, I want to be able to only supply one or the other down the road, not always both. This is what my parameter options look like: While my R script.r file for ingesting data is: This is the error I get when I don't include Colour or Shape or both. I feel that the ability to handle not supplying an argument should be handled within the script which I have tried to do. However, I am now stuck.
