Thursday, 8 May 2014

Create Linked Servers (SQL Server)

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.

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.

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.

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

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
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 
 [TheaCube] MDX Query      
exec sp_executesql @GeoMDX  --Execute T-SQL Query