Insert an Analysis Services Result Set into a SQL Server Table

Connect to Analysis Services database, and insert a result set into a SQL Server relational table using a linked server and a query.  An example is outlined below.  Pay attention to the placeholders you require in your linked server, as well as the data types you define for your table.  If your table’s data types are unknown you can try a SELECT * INTO instead of INSERT.

Insert Example :


EXEC sp_addlinkedserver

@server='OLAPServer',

@provider='MSOLAP',

@srvproduct = '',

@datasrc='YourAnalysisServicesServer',

@catalog='YourAnalysisServicesDatabase'

CREATE TABLE dbo.Olap_Resultset

(TextColumn ntext, NumericColumn numeric(10,2))

INSERT dbo.Olap_Resultset

SELECT

*

from

openquery(OLAPServer, 'SELECT [Measures].[YourMeasure] ON 0, NON EMPTY([Dimension].[Attribute].Children) ON 1 FROM YourAnalysisServicesCube

' )

SELECT * FROM Olap_Resultset

If you are unsure of the datatypes coming from the cube use a SELECT * INTO, example here …


SELECT

* INTO dbo.OlapResultset2

from

openquery(OLAPServer, 'SELECT [Measures].[YourMeasure] ON 0, NON EMPTY([Dimension].[Attribute].Children) ON 1 FROM SMACube

' )

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s