Querying SQL Cube from .NET application using Stored Procedure

Since the SQL cube is located inside a SQL Analytical database we need to add that as a linked server to our working SQL Server.

Following are the steps for linking the SQL Analytical service database to our local SQL Server:

Step 1: Open SQL Server Management studio and connect to our local/remote SQL Server

Step 2: Expand Server Object -> right click the Linked Severs -> click the "New Linked Server" option from the context menu. (See the figure below.)



Step 3: The following popup will open for adding a "New Linked Server". Make sure that you select the provider as "Microsoft OLE DB Provider for Analysis Services 10.0"


Step 4: Now you can view your cubes from the "Catalogs" folder in the linked server.

Step 5: Use the Openquery SQL statement as shown below to query the data from the cube:

SELECT * FROM Openquery ("LinkedServerName",'MDX Query for fetching data from cube')

Step 6: Create one stored procedure with this Openquery SQL statement and call this SP from a .Net application.
Sample Stored Procedure:

CREATE PROCEDURE [dbo].[SP_FetchDataFromCube]

AS
 
BEGIN

declare @mdx_query as varchar(max),
@open_query as nvarchar(max),
@linked_server as varchar(max)

set @mdx_query = 'SELECT NON EMPTY {[Measures].[Premium Paid]} ON COLUMNS,
NON EMPTY {([Branch].[Branch].[City])} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Comm_Per]'

set @linked_server = 'linked server OLAP'

set @open_query = 'SELECT * FROM OpenQuery
("'+@linked_server+'",'''+ @mdx_query + ''')'

execute sp_executesql @open_query

END

GO