It contains lots on Business Intelligence scenarios and solutions along with real problems regarding Microsoft Excel, SQL Server Reporting Services, SQL Server Integration & Analysis Services and various databases and also may contain other popular posts.

Thursday, 8 May 2014

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.IssueFilters 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.ExampleFilter'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.ReasonIt is happening because filters data is coming from database(Relational database) while 'SSRS' data is coming from 'SSAS' cube(OLAP).SolutionTo 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 ServerStep 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’.

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-SQLBelow 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,