Create Linked Servers (SQL Server)

Scenario:
In an application there is a 'SSRS' report which is fetching data from 'SSAS Cube'. The cube refresh is happened every morning in the week. Once cube is refreshed then latest data is available to 'SSRS' reports. For user interface, this 'SSRS' report is integrated with .Net page, contains few filters.

Issue
Filters data should dynamic and available data range should based on data into 'SSRS' report. Some times it is observed that filters data is more than available data into 'SSRS' reports.

Example
Filter's data is available till December 2013 while in 'SSRS' data is present only till November. As filters data is dynamic so it is expected that filters data and 'SSRS' data range should be same.

Reason
It is happening because filters data is coming from database(Relational database) while 'SSRS' data is coming from 'SSAS' cube(OLAP).

Solution
To keep data same in 'filters area' and 'SSRS' report, data source should be same. Data source for 'SSRS' cannot be changed. So better if we fill filters area with cube data.

In order to implement same it is required to configure a linked sever which will help to fetch data from cube using simple T-SQL.

Configuration of Linked Server

Step 1 – Connect SQL server, using ‘Database Engine’ as server type along with proper Server name and authentication.

Step 2 – Into object explorer, navigate to linked server under ‘Server Objects’.

(Refer the below screen shot for reference)

Step 3 – Right click on ‘Linked Servers’ - select ‘New Linked Server’. A dialog will come as below -

General Tab - Fill the required details as per below screen shots. 
(Refer the below screen shot for reference)

Security Tab - Provide account detail for remote login “Here administrator account has been provided”.
(Refer the below screen shot for reference)

Server Options -
(Refer the below screen shot for reference)

Step 4 – After filling the entire details click on Ok button, Linked server is configured and it should work. In case if it doesn’t respond, once restart the SQL server.

Fetch data from SSAS Cube using T-SQL

Below is the sample query which i used to solve my problem-

declare @GeoMDX nvarchar(max) --Declare a variable to hold the SQL query

 

SET @GeoMDX = 'SELECT "[Measures].[TC_Caption]" AS TC_Caption,"[Measures].[TC_Value]" AS TC_Value,

"[Measures].[TSC_Caption]" AS TSC_Caption,  

"[Measures].[TSC_Value]" AS TSC_Value, "[Measures].[Lab_Caption]" AS Lab_Caption, "[Measures].[Lab_Value]" AS Lab_Value, 

"[Measures].[Brand_Caption]" AS Brand_Caption, "[Measures].[Brand_Value]" AS Brand_Value,

"[Measures].[Reimbursement_Caption]" As Reimbursement_Caption,"[Measures].[Reimbursement_Value]" As Reimbursement_Value SQL Query

FROM OPENQUERY(ASSERVER, --Linked server object

''WITH MEMBER [Measures].[TC_Caption] AS 

 [Product Details].[Therapeutic Class - Thea].CURRENTMEMBER.MEMBER_CAPTION 

MEMBER [Measures].[TC_Value] AS  

 [Product Details].[Therapeutic Class - Thea].CURRENTMEMBER.UNIQUE_NAME 

MEMBER [Measures].[TSC_Caption] AS  

 [Product Details].[Therapeutic Sub Class].CURRENTMEMBER.MEMBER_CAPTION 

MEMBER [Measures].[TSC_Value] AS  

 [Product Details].[Therapeutic Sub Class].CURRENTMEMBER.UNIQUE_NAME  

MEMBER [Measures].[Lab_Caption] As 

 [Product Details].[Laboratories - Thea].CURRENTMEMBER.MEMBER_CAPTION 

MEMBER [Measures].[Lab_Value] As  

 [Product Details].[Laboratories - Thea].CURRENTMEMBER.UNIQUE_NAME  

MEMBER [Measures].[Brand_Caption] As 

 [Product Details].[Product Consolidated].CURRENTMEMBER.MEMBER_CAPTION 

MEMBER [Measures].[Brand_Value] As 

 [Product Details].[Product Consolidated].CURRENTMEMBER.UNIQUE_NAME  

MEMBER [Measures].[Reimbursement_Caption] As

Case [Product Details].[Reimbursement].CURRENTMEMBER.MEMBER_CAPTION

When ''''R'''' then ''''Reimbursed'''' when ''''NR'''' then ''''Not Reimbursed'''' else ''''Not Applicable''''end

MEMBER [Measures].[Reimbursement_Value] As

[Product Details].[Reimbursement].CURRENTMEMBER.UNIQUE_NAME

SELECT { 

 [Measures].[TC_Caption], 

 [Measures].[TC_Value], 

 [Measures].[TSC_Caption], 

 [Measures].[TSC_Value], 

 [Measures].[Lab_Caption], 

 [Measures].[Lab_Value], 

 [Measures].[Brand_Caption], 

 [Measures].[Brand_Value],

 [Measures].[Reimbursement_Caption],

 [Measures].[Reimbursement_Value] 

 } 

ON 0, 

{ 

[Product Details].[Therapeutic Class - Thea].CHILDREN* 

[Product Details].[Therapeutic Sub Class].CHILDREN* 

EXCEPT([Product Details].[Laboratories - Thea].CHILDREN, [Product Details].[Laboratories - Thea].&[])* 

EXCEPT([Product Details].[Product Consolidated].CHILDREN, [Product Details].[Product Consolidated].&[])*

[Product Details].[Reimbursement].CHILDREN

} ON 1 

FROM  

 [TheaCube] MDX Query      

'')' 

exec sp_executesql @GeoMDX  --Execute T-SQL Query

This worked for me, I wish it will help you also.

References
http://msdn.microsoft.com/en-IN/library/ff772782.aspx

http://technet.microsoft.com/en-in/library/ms188279.aspx

http://support.microsoft.com/kb/218592

Other Posts
Pivot Example in SQL Server
Microsoft sql server error 18456 login failed for user