Is there a way of working with DAX queries on
rs-connect servers? One of my colleague is facing this issue, where the query fetches correct result on windows but not on unix machines. I won't be able to post an exact reproducible code, since the query is protected; but I will share the other functions below -
library(odbc) con <- dbConnect(odbc(), Driver = "sql server", UID = "user", PWD = "password", Server = "server_name") query <- "SELECT * FROM OPENQUERY([XXX], 'EVALUATE SUMMARIZECOLUMNS ( Yyyy[Aaaa, Yyyyy], Yyyy[Bbbb], Yyyy[Cccc], Yyyy[Dddd], FILTER ( Yyyy, NOT ( ISBLANK ( Yyyy[Bbbbb] ) ) ) ) ORDER BY Yyyy[Cccc] Column_Name, Yyyy[Aaaa, Yyyyy], Yyyy[Bbbb]')" testQuery <- dbSendQuery(con, query) testTable <- dbFetch(testQuery, n = -1) Results: OS: Windows complete_table (example : rows = 10, columns = 4, elements = as expected) OS:Unix empty_table (example : rows = 10, columns = 4, elements = NULL)
The query fetches correct results on windows but returns blank values in the table on unix. The interesting part is that it returns correct number of rows. This means there is no problem in
dbConnect(). Another point to highlight is that with
library(RODBC) there is no problem executing above DAX query; irrespective of OS, unix or windows.
My problem is, I want to standardize it with
odbc. Could anyone help me figure this out?