Virtually all major statistical and analytical software packages have provision for obtaining data from a relational data source using SQL queries. Unfortunately, many of the tools that work with SQL do not help when the data source is an Analysis Services database and the query language is MDX or DAX. As mentioned in the previous installment, the ADO libraries provide a means for querying Analysis Services data from Matlab. Unfortunately, this technique is only applicable for Matlab running under Windows.
We always start by obtaining an instance of an ADODB connection object:
conn = actxserver(’ADODB.Connection’);
Notice that the OleDb data provider must be ”msolap” if we wish to connect to and query the analysis services. Note also that in this particular example, the tabular mode server we are using for our example is not the default instance, so the Data Source value includes the full name. Unless you are running your Analysis Services instance on the local machine, you will need to replace the ”.” with the server name or its ip address.
conn.Open(’Provider=msolap;Data Source=.\Tabular;Initial Catalog=ContosoRetail;Integrated Security=SSPI;’);
Matlab commands end when you hit the <enter> key; this can force us to create some fairly long lines of code. These long lines can be especially unpleasant when we are embedding queries in Matlab code, whether SQL, MDX, or DAX. If we put strings into an array, we can break the assignment statement into multiple lines using ellipsis. In this example, we create a simple MDX query:
query=[’SELECT [Measures].[Sum of ResellerSalesAmount] ON 0,’ …
’[Geography].[ContinentName].Members ON 1’ …
This simple trick to break a string (or other array assignments) into multiple lines seems not to be widely appreciated by Matlab users. Despite the appearance of the square brackets in the assignment statement, the ”query” variable is a single string and can be used as such in our script.
Execution of the MDX query from our Matlab script is no different from executing an SQL query. And, just as with SQL, the result set will appear in a cell array that is pivoted from the orientation familiar to those working with database queries. If we wish, we can transpose the cell set when it is created.
Classic Analysis Services multidimensional databases support only the MDX query language. In contrast, the newer tabular mode databases support both MDX and DAX queries. As long as the DAX query starts with the EVALUATE command, telling the server to return the results in a conventional rowset format, we can query data from Matlab using the DAX language.
query=[’EVALUATE SUMMARIZE(ResellerSales’ …
’, ”Date”[CalendarYear]’ …
’, ProductCategory[ProductCategoryName]’ …
’, ”Sales Amount (USD)”, SUM(ResellerSales[SalesAmount])’ …
’, ”Discount Amount (USD)”, SUM(ResellerSales[DiscountAmount]))’]
Note that this example query accesses a table named Date. Since date is a keyword, the DAX language demands that the name Date be within single quotes. In Matlab, then, we must have two single quote characters around Date so Matlab knows to create one literal single quote character rather than end the string at that point.
A significant advantage to using the ADO libraries in Matlab is that they open up the world of Analysis Services data to direct study from Matlab.
Check out my previous blog to learn how to query SQL server data from Matlab using ADO.