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