Connecting to Microsoft Analysis Server using SSIS Import/Export-Assistant

0

Goal is to export Data from a Microsoft Analysis Server Cube into a flatfile in a lean way.

I use the SQL Server-Import/Export-Assistant. Using the option "Copy data from one or more tables or views" the download runs smoothly. -> No problems

When I use an MDX expression ("Write a query to specify the data to transfer") query pre-executing it gives an error message

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

Edit: Please note that I try to run the SQL Server 2016 RC0 Import and Export Data Wizard -> Must use this one as it is the only valid licence.

Edit: I recognized differences in the following MDX expressions.

Query 1

SELECT
  [Measures].[measure1] ON COLUMNS,
NON EMPTY
  [order].[orderid].[orderid].AllMembers
* [order_spec].[spec].[spec].AllMembers ON ROWS
FROM [cube];
  1. SQL Server 2014 Management Studio: works
  2. SQL Server Import and Export Data Wizard: Error 0x80040E21
  3. Visual Studio 2015, Data Flow Task: Error 0x80040E21

Query 2

SELECT * FROM 
[cube].[cube_tab].[$spec2]
  1. SQL Server 2014 Management Studio: works
  2. SQL Server Import and Export Data Wizard: works
  3. Visual Studio 2015, Data Flow Task: works

Also note, that when I use the option "Copy data from one or more tables or views" I cannot choose any measures.

Also I am not able to set up any openquery procedure using the Wizard.

ssis
connection
mdx
asked on Stack Overflow Apr 4, 2016 by Mathias Quetschlich • edited Nov 12, 2016 by Mathias Quetschlich

1 Answer

0

Looks like you have SQL-server

Create a linked server to the cube database

You can then write an mdx query inside a tsql query like described here: https://technet.microsoft.com/en-us/library/aa936673(v=sql.80).aspx

I used scripts in the form of the last snippet in the above reference:

select * from openquery 
( LINKED_OLAP, 'select { measures.[unit sales] } on columns,
 non empty nest( nest( [customer location].[country].members, 
[gender].members ), [product category].[bread].children ) on rows 
from sales ') 

If the above looks like a possibility then I would strongly suggest going straight to the following - it is a wonderful stored procedure:

https://olapextensions.codeplex.com/


Edit

This isn't standard mdx:

SELECT
  {[Measures].[measure1]} 
DIMENSION PROPERTIES 
    [MEMBER_UNIQUE_NAME]
   ,[MEMBER_CAPTION] ON COLUMNS,
NON EMPTY 
  CROSSJOIN(
    [order].[orderid].[orderid].AllMembers,
    [order_spec].[spec].[spec].AllMembers
  )
DIMENSION PROPERTIES
    [MEMBER_UNIQUE_NAME]
   ,[MEMBER_CAPTION] ON ROWS
FROM [cube]

This is a lot simpler:

SELECT
  [Measures].[measure1] ON COLUMNS,
NON EMPTY 
     [order].[orderid].[orderid].AllMembers,
   * [order_spec].[spec].[spec].AllMembers
  ON ROWS
FROM [cube];

Maybe this is also sufficient:

SELECT
  [Measures].[measure1] ON COLUMNS,
NON EMPTY 
     [order].[orderid].[orderid].MEMBERS,
   * [order_spec].[spec].[spec].MEMBERS
  ON ROWS
FROM [cube];
answered on Stack Overflow Apr 4, 2016 by whytheq • edited Apr 9, 2016 by whytheq

User contributions licensed under CC BY-SA 3.0