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];
Query 2
SELECT * FROM
[cube].[cube_tab].[$spec2]
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.
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];
User contributions licensed under CC BY-SA 3.0