Executing DAX Queries From R

R and DAX

Christine, a student in Learning Tree’s course SQL Server Analysis Services for Business Intelligence, writes “I just created my first Analysis Services tabular model database, and I am having lots of fun. But now I want to do some more sophisticated statistical analysis using R. I can’t seem to find a way to use DAX queries to read tabular data into R.”

Well, Christine, I’m glad you’re enjoying the Analysis Services. I myself have not had much luck with R tools purported to work with MDX, and I am not aware of any tools at all that allow R to query a tabular database using DAX. There is, however, a workaround that is reliable, even if it is not as elegant as solution as we might wish.

SQL Server linked servers have always supported what are called passthrough queries. In a passthrough query, SQL Server does not parse the entire query received from the client, but relays the passthrough portion directly to the linked server. This means that if we set up our tabular mode database as a linked server in SQL Server, we can then get the DAX results we want using good old-fashioned ODBC.

The first step is to create a linked server, which can be done either in T-SQL or by using the SSMS dialogs. In T-SQL, we define the linked server and then define the security credentials that SQL Server will need to connect.

EXEC sp_addlinkedserver @server = N’LINKEDTABULAR’, @srvproduct=N”, @provider=N’MSOLAP’, @datasrc=N’localhost\TabularInst’, @catalog=N’ContosoRetail’

GO

EXEC sp_addlinkedsrvlogin @rmtsrvname = N’LINKEDTABULAR’, @locallogin = NULL , @useself = N’True’

The same information can be added via the linked server dialog, we can be found by opening the “Server Objects” folder then right-clicking on the “Linked Servers” folder and choosing “New Linked Server…”.

The name chosen for the linked server, LINKEDTABULAR in this example, is completely arbitrary. Choose whatever name you like. Be sure to select the provider for the Anaysis services.

NewLinkedServer

Clicking on “Security” in the Select A Page list we can specify our security requirements.

NewLinkedServerSecurity

In this example, we select “Be made using the login’s current security context. This means that whatever credentials you will be using to connect R to SQL Server will also be used to access the tabular mode database. If you are using different credentials for the two servers, then you may have to explicitly enter a windows login name and password.

Once you click OK you’re ready to go. We can confirm the linked server works by executing the following passthrough query in the Managment Studio:

SELECT * FROM

OPENQUERY([LINKEDTABULAR],‘Evaluate(”Date”)’)

Note that we have replaced the single quotes around the table name Date with a pair of single quotes. This is critical to ensure the SELECT statement meets the requirements of SQL syntax.

If you test using some other table than the sample Contoso date table, be sure it is not too large; evaluating a large tabular table with no filter to reduce row number is a great way to swamp system resources.

Connecting to SQL Server using RODBC

There are two alternatives for connecting using ODBC. In the long run, it is probably easiest to create a system DSN, or Data Source Name, and then use that name anytime we want to connect the server. The other alternative is the so-called DSN-less method, which provides all the necessary connection information right in the commandline. This method has the advantage that your R code does not require a DSN to be defined on the machine and will therefore run on any machine that has the same network access to your SQL Server.

Creating a Connection with RODBC and a DSN:

> library(RODBC)

> ch <- odbcConnect(“localSqlServer”)

Creating a Connection with RODBC without a DSN:

> library(RODBC)

> ch2<-odbcDriverConnect(“DRIVER={SQL Server Native Client 11.0};SERVER=localhost;Database=Northwind;Trusted_Connection=Yes”)

Now for the Ugly Part

To run a DAX query as a passthrough query, we must adjust the syntax. I encourage you to develop and test your query first in the Management Studio and then paste it into R as a variable.

Step One: The Single Quotes

As before, we must replace each single quote with a pair of single quotes to keep SQL Server happy.

SELECT * FROM

OPENQUERY([LINKEDTABULAR],

‘EVALUATE SUMMARIZE(RetailSales

, ”Date”[CalendarYear]

, ProductCategory[ProductCategoryName]

, “Sales Amount (USD)”, SUM(RetailSales[SalesAmount])

, “Discount Amount (USD)”, SUM(RetailSales[DiscountAmount])

)’)

We are not yet ready to paste this query into R, however. The double quotes around the column names in the DAX query will upset R. We must escape them with a backslash.

Step Two: Escape the Double Quotes

SELECT * FROM

OPENQUERY([LINKEDTABULAR],

‘EVALUATE SUMMARIZE(RetailSales

, ”Date”[CalendarYear]

, ProductCategory[ProductCategoryName]

, \”Sales Amount (USD)\”, SUM(RetailSales[SalesAmount])

, \”Discount Amount (USD)\”, SUM(RetailSales[DiscountAmount])

)’)

Now we are almost done. The only thing remaining is to put the entire thing between double quotes so we can paste it into R.

“SELECT * FROM

OPENQUERY([LINKEDTABULAR],

‘EVALUATE SUMMARIZE(RetailSales

, ”Date”[CalendarYear]

, ProductCategory[ProductCategoryName]

, \”Sales Amount (USD)\“, SUM(RetailSales[SalesAmount])

, \”Discount Amount (USD)\“, SUM(RetailSales[DiscountAmount])

)’)”

At the command line in R, type

sql <-

Then paste the query string, including the double quotes, into the command line. Hit <enter>. The lack of any feedback seems anticlimactic after all the work preparing the query.

Execute the query with the RODBC command sqlQuery:

results <- sqlQuery(ch, sql)

That’s it. We now have a data frame called results that we can study in the R environment.

Conclusion

Microsoft created DAX with Excel analysts in mind; Excel and related tools in the Microsoft BI stack remain the only tools with quality DAX support. However, we can use an SQL Server linked server to achieve connectivity for any application that supports ODBC, including the popular and powerful statistical package R.

image sources

  • R and DAX: Dan Buskirk

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.