DAX queries with odbc

[heramb]

Hello,

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?

Thanks!
Heramb

[andresrcs]

Have you tried specifying the encoding of the connection?

con <- dbConnect(odbc::odbc(), .connection_string = connection_string, encoding = "utf8")

[heramb]

@andresrcs

con <- dbConnect(odbc::odbc(), .connection_string = connection_string, encoding = "utf8")

@andresrcs : I tried but did not work. I am still getting blank rows in the output. What's interesting is that with RODBC it works like any other SQL query.

Thanks!
Heramb

[andresrcs]

I recently had a similar issue with odbc package on Ubuntu 18 and it turned out to be a locale incompatibility problem, are you using en_US.UTF-8? If not, try changing your locale to this one before making the connection.

[heramb]

@andresrcs: I am using en_US.UTF-8. OS is RHEL Server 7.2

LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=