# File path of the sample
input_pbix <- path_file_sample
pbi_content <- f_get_pbix_info(input_pbix)
pbi_content_names <- pbi_content$Name
# Identify exentsions
pbi_content_types <- unique(tools::file_ext(pbi_content_names))
# Show content Make the size look pretty
biggest_file <- max(pbi_content$Length)
pbi_content$Length <- prettyNum(pbi_content$Length, big.mark = ",")
knitr::kable(pbi_content)

# Search only for .png files
image_reg <- "[.]png"
# Get the images
image_information <- f_extract_images(input_pbix, image_reg)
image_name <- gsub(".*/", "", image_information[[2]]$Name)
# Plot the first image
im <- imager::load.image(image_information[[1]])
plot(im, main = image_name)

# Get the byte sequence of a first-level file.  The string is too long to fit
# nicely for this vignette so I will break it and paste it together.
variable_pt1 <- "Report/CustomVisuals/PBI_CV_9053509D_D461_463A_B485_"
variable_pt2 <- "CE9F6AB0F13C/resources/PBI_CV_9053509D_D461_463A_B485"
variable_pt3 <- "_CE9F6AB0F13C.pbiviz.json"
variable <- paste0(variable_pt1, variable_pt2, variable_pt3)
raw_img_viewer_visual <- f_get_pbix_fir(path_file_sample, variable)

# Write to a temporary file
temp_file_2 <- file.path(tempdir(), "pbiviz_1")
zz <- file(temp_file_2, "wb")
writeBin(raw_img_viewer_visual, zz)
close(zz)

# Read temporary file back in
char_img_viewer_visual <- readLines(temp_file_2, warn = FALSE)

# Get image data from the relvant line
image_img_viewer_visual <- gsub(");}", "", gsub(".*data:image/png;base64,", "", grep("data:image", 
    char_img_viewer_visual, value = TRUE)))

# Decode
raw_image_img_viewer_visual <- RCurl::base64Decode(image_img_viewer_visual, mode = "raw")

# Write to a temporary file
temp_file_3 <- file.path(tempdir(), "pbiviz_2.png")
zz <- file(temp_file_3, "wb")
writeBin(raw_image_img_viewer_visual, zz)
close(zz)

# Plot Get the name of custom visual for the main title
plot_title <- paste0("Visual: '", gsub(".*:\"", "", gsub("\", \"displayName.*", "", 
    char_img_viewer_visual)), "'")
# Load the image and create the plot
load_image_img_viewer_visual <- imager::load.image(temp_file_3)
plot(load_image_img_viewer_visual, main = plot_title)

# Get the query
m_query <- f_get_dama_m(input_pbix)
#> Error in f_get_dama_m(input_pbix): argument "remove_temp" is missing, with no default
# Show the first ten lines of the query
top_m <- paste0(head(m_query, 10), "\n")
#> Error in head(m_query, 10): object 'm_query' not found

cat(top_m)
#> Error in cat(top_m): object 'top_m' not found

# Get details of .xml files included in the DataMashup file
xml_details <- f_get_dama_xml_details(input_pbix)
# Identify the start and end bytes of the .xml file we want from the DataMashup
# file
xml_start <- (xml_details[[1]][1]/2) + 1
xml_end <- xml_details[[3]][1]
# Get the desired .xml file from DataMashup file
input_xml <- f_get_dama_xml(input_pbix, xml_start, xml_end)
# Get metadata from the the desired .xml file
get_xml_data <- f_get_dama_xml_data(input_xml)

# Show content
knitr::kable(get_xml_data[[1]])

# Show content of first query
show_first_query <- get_xml_data[[2]] %>% filter(query == "Section1/Actors%20Bridge")
knitr::kable(show_first_query)

# Show content of first query
knitr::kable(data.frame(step = grep("Section1/Actors%20Bridge", get_xml_data[[3]], 
    value = TRUE)))

gsub__1 <- paste0(".*sections")
gsub__2 <- "{\"id\":0,\"sections"
# Run the function
get_layout <- f_read_layout(path_file_sample, gsub__1, gsub__2)
#> An error occured reading without data exclusion. The file was read with data exclusion: gsub(".*sections","{"id":0,"sections", ...)

computer_name <- get_layout$sections$name
human_name <- get_layout$sections$displayName
names <- data.frame(computer_name = computer_name, human_name = human_name)
names
#>     computer_name
#> 1   ReportSection
#> 2  ReportSection2
#> 3  ReportSection1
#> 4  ReportSection3
#> 5  ReportSection5
#> 6  ReportSection4
#> 7  ReportSection6
#> 8  ReportSection9
#> 9  ReportSection7
#> 10 ReportSection8
#>                  human_name
#> 1         OverviewTopMovies
#> 2           AnalysisByGenre
#> 3           AnalysisByRated
#> 4                Clustering
#> 5                TimeSlicer
#> 6  Grouping,CondForm&others
#> 7                   Binning
#> 8      Numericslicer+Matrix
#> 9                       DAX
#> 10            QuickMeasures

json_sect <- get_layout$sections
visual_containers <- json_sect$visualContainers
filter_index <- which(unlist(lapply(lapply(lapply(visual_containers, function(x) colnames(x)), 
    function(z) grep("filters", z)), length)) > 0)
json_sect$displayName[filter_index]
#> [1] "AnalysisByGenre"         
#> [2] "Grouping,CondForm&others"

contain_df <- visual_containers[[1]]
plot(contain_df$x, contain_df$y * -1, main = paste0("Sheet: ", as.character(names[3, 
    2])), sub = "Top-left corner of box", pch = 3)

jsonlite::prettify(contain_df[11, "dataTransforms"])
#> {
#>     "objects": {
#>         "TextScrollDetail": [
#>             {
#>                 "properties": {
#>                     "fontSize": {
#>                         "expr": {
#>                             "Literal": {
#>                                 "Value": "14D"
#>                             }
#>                         }
#>                     }
#>                 }
#>             }
#>         ]
#>     },
#>     "projectionOrdering": {
#>         "Category": [
#>             0
#>         ]
#>     },
#>     "queryMetadata": {
#>         "Select": [
#>             {
#>                 "Restatement": "Plot",
#>                 "Name": "IMDBTop250.Plot",
#>                 "Type": 2048
#>             }
#>         ]
#>     },
#>     "visualElements": [
#>         {
#>             "DataRoles": [
#>                 {
#>                     "Name": "Category",
#>                     "Projection": 0,
#>                     "isActive": false
#>                 }
#>             ]
#>         }
#>     ],
#>     "selects": [
#>         {
#>             "displayName": "Plot",
#>             "queryName": "IMDBTop250.Plot",
#>             "roles": {
#>                 "Category": true
#>             },
#>             "type": {
#>                 "category": null,
#>                 "underlyingType": 1
#>             },
#>             "expr": {
#>                 "Column": {
#>                     "Expression": {
#>                         "SourceRef": {
#>                             "Entity": "TopMovies"
#>                         }
#>                     },
#>                     "Property": "Plot"
#>                 }
#>             }
#>         }
#>     ]
#> }
#> 

connections_open <- f_get_connections() %>% mutate(pbix = gsub(" \\[Read-Only\\]", 
    "", gsub(" - Power BI Desktop", "", pbix_name))) %>% filter(pbix == gsub("[.]pbix", 
    "", basename(path_file_sample)))
correct_port <- as.numeric(connections_open$ports[1])
connection_db <- paste0("Provider=MSOLAP.8;Data Source=localhost:", correct_port, 
    ";MDX Compatibility=1")
# Expression to get the DAX queries
sql_measures <- paste0("select MEASURE_NAME, EXPRESSION, MEASUREGROUP_NAME ", "from $SYSTEM.MDSCHEMA_MEASURES")
# Query the analysis service
get_dax <- f_query_datamodel(sql_measures, connection_db)
# Display a result
t_data <- t(tail(get_dax, 1))
colnames(t_data) <- NULL
# To improve display of the DAX with kable, I removed new lines and tabs that
# occured in the DAX
knitr::kable(trimws(gsub("\\t", "", gsub("\\n", "", t_data))))
#> Warning in kable_markdown(x
#> = structure(c("MEASURE_NAME",
#> "EXPRESSION", : The table should have
#> a header (column names)

# Get relationships
sql_relationships <- paste0("select FromTableID, FromColumnID, ToTableID,", " ToColumnID  from $SYSTEM.TMSCHEMA_RELATIONSHIPS")
get_relationships <- f_query_datamodel(sql_relationships, connection_db)

# Get names of columns
sql_columns <- "select * from $SYSTEM.TMSCHEMA_COLUMNS"
get_columns <- f_query_datamodel(sql_columns, connection_db) %>% mutate(ColumnName = ifelse(nchar(as.character(ExplicitName)) == 
    0, as.character(InferredName), as.character(ExplicitName))) %>% select(ColumnID = ID, 
    ColumnName)

# Get names of tables
sql_table <- "select * from $SYSTEM.TMSCHEMA_TABLES"
get_tables <- f_query_datamodel(sql_table, connection_db) %>% select(TableID = ID, 
    TableName = Name)

# Merge things together
get_relationship_names <- get_relationships %>% merge(get_tables, by.x = "FromTableID", 
    by.y = "TableID") %>% rename(FromTable = TableName) %>% merge(get_tables, by.x = "ToTableID", 
    by.y = "TableID") %>% rename(ToTable = TableName) %>% merge(get_columns, by.x = "FromColumnID", 
    by.y = "ColumnID") %>% rename(FromColumn = ColumnName) %>% merge(get_columns, 
    by.x = "ToColumnID", by.y = "ColumnID") %>% rename(ToColumn = ColumnName) %>% 
    select(FromTable, ToTable, FromColumn, ToColumn)

# Make things all characters drop table with long name. Doing this just for
# display purposes here.
get_relationship_names <- get_relationship_names[-5, ] %>% mutate(FromTable = as.character(FromTable)) %>% 
    mutate(ToTable = as.character(ToTable))

# Plot the results with igraph
c_list <- list()
for (i in 1:nrow(get_relationship_names)) {
    c_list[[i]] <- get_relationship_names[i, 1:2]
}
ed <- as.character(unlist(c_list))
# Use igraph, ggraph, ggplo2 to show preliminary example of data model
g1 <- igraph::graph(edges = ed, directed = F)
ggraph::ggraph(g1, layout = "stress") + ggraph::geom_edge_link() + ggraph::geom_node_point(size = 2, 
    col = "red") + ggraph::geom_node_text(ggplot2::aes(label = name)) + ggplot2::theme(panel.background = ggplot2::element_blank())

query_topmovies <- "evaluate TopMovies"
get_data_topmovies <- f_query_datamodel(query_topmovies, connection_db)
# Display some results
knitr::kable(get_data_topmovies[1:3, 1:3])