Get value from Measure in OLAP Cube and Insert it in a SQL table

Question

I need help with the the following:

I got an OLAP Cube, let's call it "company_prod" on "server\instance"

That Cube has (among many) a calculated member called "[Measures].[Value]"

One of the Dimensions in the Cube is for time (Year, Month, Date and so on). e.g. [TIME].[Y_M_D].[YEAR].&[2020]

Our main frontend is Excel, where we retrieve Data from the Cube with CUBEELEMENT, CUBEVALUE etc.

We got some measures which unfortunately, when I update the Excel report now and show numbers for last year, the result is different when I update that same report in a few weeks or months. This is something I won't be able to change and in some reports it's the desired behaviour because underlying data from SAP is changed and sometimes valid_from and valid_to dates are changed retroactively.

Now I want to get the value from my "[Measures].[Value]" on a certain date, let's say April 1st. I then want to insert the value I get on April 1st for 2020 in a SQL table. This should be done by an agent job that executes a stored procedure or runs a dtsx package or anything else, whichever works.

I hope it's clear what I am trying to accomplish...

Answer 1

If you can create linked servers from your SQL Server to SSAS Server, then you can run your MDX query against the linked SSAS Server using OPENQUERY and save the result directly to the SQL Server table.

i.e.,

INSERT INTO <your table>
EXECUTE <your mdx statement> AT <linked server>

You can add the run the above via your SQL Agent job on schedule.