To use the OpenQuery() procedures within SQL Server to query a SQL Server Analysis Services Cube, you need to add a Linked Server with Credentials of a valid Domain User, as below:
USE [master] GO /****** Object: LinkedServer [CUBE_FINANCEV2] Script Date: 10/15/2019 11:53:16 AM ******/ EXEC master.dbo.sp_addlinkedserver @server = N'CUBE_FINANCEV2', @srvproduct=N'MSOLAP', @provider=N'MSOLAP', @datasrc=N'mp-bi', @catalog=N'Finance-v2' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CUBE_FINANCEV2',@useself=N'False',@locallogin=NULL,@rmtuser=N'MUC\mp_BI_SQL_AS',@rmtpassword='########' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CUBE_FINANCEV2',@useself=N'True',@locallogin=N'MUC\mp_BI_SQL_AS',@rmtuser=NULL,@rmtpassword=NULL GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'rpc', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'rpc out', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'connect timeout', @optvalue=N'45' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'use remote collation', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'remote proc transaction promotion', @optvalue=N'true' GO
To Query the Processing Status or the last date and time of processing of the Cube, you may create and execute the following Stored Procedure (that uses the created Linked Server from above):
USE [DWH]
GO
/****** Object: StoredProcedure [etl].[GetCubeProcessingStatus] Script Date: 14.10.2019 18:21:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Cordt Bien
-- Create date: 29.05.2019
--
-- Last Modified: Date, Name (short reason.)
--
-- Description: Returns the date and time of the last processing of a cube or perspective. May return multiple rows.
--
-- Info: EXEC etl.GetCubeProcessingStatus 'Finance-v2', NULL
-- OR EXEC etl.GetCubeProcessingStatus 'Finance-v2', 'Datev-Finance'
-- =============================================
CREATE PROCEDURE [etl].[GetCubeProcessingStatus]
@cubeDatabaseName sysname = 'Finance-v2', -- must not be null!
@cubeOrPerspectiveName sysname = NULL -- optional to limit returned result to one row.
--WITH EXECUTE AS 'InternalExecutor' -- (IF YOU USE THE "WITH EXECUTE AS" clause, the Execution will fail due to Security Context Exceptions of the Linked Server)
AS
BEGIN
SET NOCOUNT ON;
-- Check Preconditions
BEGIN TRY
IF @cubeDatabaseName IS NULL RAISERROR('cubeDatabaseName must not be null!', 11, 1);
END TRY
BEGIN CATCH
EXEC internal.ReThrowError @@PROCID;
RETURN -1;
END CATCH
-- Implementation
BEGIN TRY
IF @cubeDatabaseName = 'Finance-v2' BEGIN
SELECT DatabaseName, CubeOrPerspectiveName, CubeOrPerspectiveDisplayName, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), GETDATE()), LastProcessDate) as LastProcessDate, BaseCubeName
FROM OpenQuery(CUBE_FINANCEV2
, N'SELECT [CATALOG_NAME] AS DatabaseName,[CUBE_NAME] AS CubeOrPerspectiveName,[CUBE_CAPTION] AS CubeOrPerspectiveDisplayName,[LAST_DATA_UPDATE] AS LastProcessDate,[BASE_CUBE_NAME] AS BaseCubeName FROM $system.mdschema_cubes WHERE CUBE_SOURCE=1')
WHERE (@cubeOrPerspectiveName IS NULL OR CAST(CubeOrPerspectiveName as nvarchar(128)) = @cubeOrPerspectiveName);
END
ELSE BEGIN
RAISERROR('A cubeDatabaseName other than "Finance-v2" is currently not supported or implemented! DEV-Info: Add a new linked server to the requested cube and insert it to this stored procedure (etl.GetCubeProcessingStatus).', 11, 1);
SELECT TOP 0 DatabaseName, CubeOrPerspectiveName, CubeOrPerspectiveDisplayName, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), GETDATE()), LastProcessDate) as LastProcessDate, BaseCubeName
FROM OpenQuery(CUBE_FINANCEV2
, N'SELECT [CATALOG_NAME] AS DatabaseName,[CUBE_NAME] AS CubeOrPerspectiveName,[CUBE_CAPTION] AS CubeOrPerspectiveDisplayName,[LAST_DATA_UPDATE] AS LastProcessDate,[BASE_CUBE_NAME] AS BaseCubeName FROM $system.mdschema_cubes WHERE CUBE_SOURCE=1')
WHERE (@cubeOrPerspectiveName IS NULL OR CAST(CubeOrPerspectiveName as nvarchar(128)) = @cubeOrPerspectiveName);
END
END TRY
BEGIN CATCH
EXEC internal.ReThrowError @@PROCID;
RETURN -2;
END CATCH
END
