Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Exporting MDX queries to SQL Relational Databases using SSIS 2005 Expand / Collapse
Author
Message
Posted Thursday, March 11, 2010 2:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 16, 2010 10:19 PM
Points: 1, Visits: 11
Hi,
In the Data access mode drop down box i select SQL Command, and paste the following MDX Query into the SQL command text box.
SELECT NON EMPTY { [Measures].[SaleAmount], [Measures].[SaleCount]} ON COLUMNS,
NON EMPTY { ([Customer].[Customer].[Customer].ALLMEMBERS
* [Date].[Year-Month].[Month].ALLMEMBERS
* [Date].[Year].[Year].ALLMEMBERS) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [CustomerAnalysis]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

then when i clicked the Columns menu item, the program did NOT response. it was blocked.
I guessed that was because SSAS was parsing the mdx query.
BIDS finally issued an out-of-memory error.
in fact, if executed, the mdx query should return tens of millions of records. and i'm pretty sure that parsing a mdx query is essentially executing the query and then discarding the results.
it seems mdx query is not suitable for extracting large amount of data from a cube, am i right?

Andrew


Andrew
Post #880805
Posted Thursday, April 8, 2010 9:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 20, 2011 4:29 AM
Points: 4, Visits: 51
I wouldn't use MDX to extract tens of millions of rows. Analysis Services performs better with data sliced and aggregated to more manageable levels. Your data warehouse should be summarizing/aggregating/grouping data for Analysis Services to use. If you need to extract tens of millions of rows this should be done in TSQL\BCP exporting to .csv files.
Post #899719
Posted Friday, March 11, 2011 2:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:13 AM
Points: 31, Visits: 83
I followed the steps but when i add Format=tabular and try to click on test connection i get the following error

Test connection failed because of an error in initializing provider. Internal error: An unexpected exception occured.
Internal error: An unexpected exception occured.
XML parsing failed at line 1, column 515: Unexpected end of input.
.

I am using SQL Server 2005 SP3.

Thanks
Post #1077166
Posted Thursday, November 17, 2011 1:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 23, 2014 9:37 PM
Points: 6, Visits: 128
Thanks for the post, however when I am implementing it in my package it's not very consistent.

I have my SSIS 2005 package which fetches data from SSAS 2008. The MDX query takes around 5 minutes to execute and the package when runs succesfully takes almost the double time (validation + execution). But sometimes the package stop abruptly and I see below errors -

1.
Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2008." Hresult: 0x80004005 Description: "Server: The operation has been cancelled.".

And some times this one -

2.
Error: An OLE DB error has occurred. Error code: 0x80040E05. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2008." Hresult: 0x00000001 Description: "Error Code = 0x80040E05, External Code = 0x00000000:.".

Any idea what is the issue here? I have increased TimeOut and ConnectionTimeOut, and it made the package more stable but somehow it's not 100%.

Any help/idea here would be really appreciated. Thanks again for your post!

Kind regards,
Santosh Joshi
Post #1207302
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse