f_query_datamodel: Query 'DataModel' of a '.pbix'

In pbixr: Access Data and Metadata from 'Microsoft' 'Power BI' Documents

Description

A query of 'DataModel' of a '.pbix' currently open in 'Power BI' is developed. The query is exchanged with 'Analysis Services' via 'PowerShell'. Results are written to a temporary file, which is (1) read into R and (2) deleted.

Usage

f_query_datamodel(queryPowerBI, connection_string)

Arguments

queryPowerBI	Query of 'DataModel' (e.g. 'DAX', 'MDX').
connection_string	Connection to 'DataModel' intiated in 'Analysis Services'. Please note: (1) '.pbix' must be open in 'Power BI' to connect to 'DataModel' and (2) the identifier and port used in the connection change each time a '.pbix' is opened with 'Power BI'.

Value

Result from a query of 'DataModel'. For one table, a data.frame is returned. For many tables, a list is returned. For an error, perhaps due to incorrect 'DAX' or 'MDX' or incorrect connection, a list of 1 equal to NULL.

Note

'Power BI' and 'PowerShell' are required.

Author(s)

Don Diproto

Examples

## Not run: 
# Get dummy data ------------------------------------------------------------
# Create a temporary directory
temp_dir <- file.path(tempdir(),"functionTest")
if(!dir.exists(temp_dir)) {
	dir.create(temp_dir)
}
sample_file_name <- "OR_sample_func.pbix"
pathFileSample <- file.path(temp_dir, sample_file_name)

# See if dummy data already exists in temporary directory
parent_temp_dir <- dirname(temp_dir)
existing_file <- list.files(parent_temp_dir,
pattern = sample_file_name, recursive = TRUE, full.names = TRUE)

# Download the sample .pbix if it doesn't exist
if (length(existing_file) == 0) {
   url_pt1 <- "https://github.com/KoenVerbeeck/PowerBI-Course/blob/"
   url_pt2 <- "master/pbix/TopMovies.pbix?raw=true"
   url <- paste0(url_pt1, url_pt2)
   req <- download.file(url, destfile = pathFileSample, mode = "wb")
} else {
   pathFileSample <- existing_file[1]
}
# Do stuff ------------------------------------------------------------------
OR_pathFileSample <- pathFileSample

# Open the .pbix with 'Power BI' if it is not already open.
#
# Identify the right port
connections_open <- f_get_connections()
connections_open$pbix <- gsub(" - Power BI Desktop", "",
connections_open$pbix_name)
connections_open <- connections_open[which(connections_open$pbix ==
gsub("[.]pbix", "", basename(OR_pathFileSample))), ][1, ]
correct_port <- as.numeric(connections_open$ports)
# Construct the connection
connection_db <- paste0("Provider=MSOLAP.8;Data Source=localhost:",
                        correct_port, ";MDX Compatibility=1")

# Example 1
# No need to change the syntax
queryPowerBI <- "evaluate TopMovies"
getQueryPowerBIData <- f_query_datamodel(queryPowerBI, connection_db)
str(getQueryPowerBIData)

# Example 2
# Escape dollar sign so that it can run via PowerShell
queryPowerBI <- paste0("select MEASURE_NAME, EXPRESSION, MEASUREGROUP_NAME ",
                    "from `$SYSTEM.MDSCHEMA_MEASURES")
getQueryPowerBIData <- f_query_datamodel(queryPowerBI, connection_db)
str(getQueryPowerBIData)

# Example 3
# Escape double quotes so that it can run via PowerShell
queryPowerBI <- paste0("evaluate(summarizecolumns('TopMovies'[Rank],",
                    "'TopMovies'[Title],\\\"\\\"Value\\\"\\\",",
                    "TopMovies[Avg Metascore]))")
getQueryPowerBIData <- f_query_datamodel(queryPowerBI, connection_db)
str(getQueryPowerBIData)

# Example 4
# Return results from multiple EVALUATE.
# Remember to put white spaces after statements like DEFINE and EVALUATE
# the code runs
queryPowerBI <- paste0(
  "DEFINE ",
  "VAR test_average = CALCULATE(AVERAGE('TopMovies'[imdbRating])) ",
  "VAR test_median = CALCULATE(MEDIAN('TopMovies'[imdbRating])) ",
  "EVALUATE ",
  "  ROW( ",
  "    \\\"\\\"MinRuntime\\\"\\\", CALCULATE(MIN('TopMovies'[Runtime])),",
  "    \\\"\\\"MaxRuntime\\\"\\\", CALCULATE(MAX('TopMovies'[Runtime])),",
  "    \\\"\\\"average\\\"\\\", test_average) ",
  "EVALUATE ",
  "  ROW(",
  "    \\\"\\\"MinRuntime\\\"\\\", CALCULATE(MIN('TopMovies'[Runtime])),",
  "    \\\"\\\"MaxRuntime\\\"\\\", CALCULATE(MAX('TopMovies'[Runtime])),",
  "    \\\"\\\"median\\\"\\\", test_median)"
)
getQueryPowerBIData <- f_query_datamodel(queryPowerBI, connection_db)
str(getQueryPowerBIData[[1]])
str(getQueryPowerBIData[[2]])

# Example 5
# Use single quotes when white space occurs in table name
# Note that single quotation marks don't have to be escaped for
# 'PowerShell'.
queryPowerBI <- "evaluate 'Genre Bridge'"
getQueryPowerBIData <- f_query_datamodel(queryPowerBI, connection_db)
str(getQueryPowerBIData)

# Example 6
# Statement that won't work.
queryPowerBI <- "hello, world"
getQueryPowerBIData <- f_query_datamodel(queryPowerBI, connection_db)
getQueryPowerBIData
  
## End(Not run)

pbixr documentation built on Oct. 27, 2020, 5:07 p.m.