Oh!! I wasn’t aware that ADOMD makes it so easy. Using ADOMD class Library, I have created a small asp script which can make a connection with the Microsoft Analysis services and run a MDX query. The APIs also allows catching an error i.e the error while attempting an invalid MDX can also be displayed.
To start with we need to prepare Analysis services instance for connection. What this means is the instance should allow access to IUSR_MACHINENAME. This can be achieved by adding a role in the catalog you wish to connect to. Secondly, you should know the connection string which should be used for connection to the instance. For a local instance, most probably it will be
Once this is done we are ready to connect to the AS instance.
- Declare the connection properties i.e the connection string and the default database
- Connect with the Microsoft Analysis services instance, using the connection properties used above
- After step 2, we have established a connection with the Analysis services instance, and we can now use the cnn connection object to query the server
- cel.open above has triggered an MDX query (i.e. strMDX here), the result of which can be read from cel (CellSet Object). I wrote a small routine to show the returned set in an HTML table
The full source for the OLAP Application can be downloaded from here.
Here output which you get when querying for
Select ( [Order Date].[CalendarYear].members) ON COLUMNS, ([Customer].[English Education].Members) ON ROWS From [Adventure Works DW] Where [Measures].[Sales Amount]