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

-----------------------------------------------------------------------------------------

This Article is From: http://www.c-sharpcorner.com/uploadfile/shinuraj587/querying-sql-cube-from-net-application-using-stored-procedure/