Search code examples
databaseserviceanalysiscube

Import data from Analysis Services cube into MS SQL database

Is there a possibility to insert data from Analysis Services cube into MS SQL database?

At the moment I can use excel to get data from Analysis Services cube, but I want to store some data from CUBE into my database.

Solution

  • ALTER 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 {[Measures].[col1]} ON COLUMNS, { [Time].[Month].[' + CONVERT(CHAR(7), GETDATE(),126) + ']} ON ROWS FROM [database] WHERE [field].[field].[value]'

    set @linked_server = 'LINKED'

    set @open_query = 'SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())), * FROM OpenQuery ("'+@linked_server+'",'''+ @mdx_query + ''')'

    execute sp_executesql @open_query

    END