Exporting MDX queries to SQL Relational Databases using SSIS 2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by ANDREW.MUYOBO » Exporting MDX queries to SQL Relational...

14 posts, Page 2 of 2

««1 2

Exporting MDX queries to SQL Relational Databases using SSIS 2005

Rate Topic

Display Mode

Topic Options

Author

Message

Andrew.Chen

Posted Thursday, March 11, 2010 2:01 AM

Forum 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

ANDREW.MUYOBO

Posted Thursday, April 08, 2010 9:50 AM

Forum 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

Satch24

Posted Friday, March 11, 2011 2:12 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 9:27 AM
Points: 25, Visits: 70

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

kec.santosh.joshi

Posted Thursday, November 17, 2011 1:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 18, 2013 10:58 PM
Points: 6, Visits: 121

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 »

14 posts, Page 2 of 2

««1 2

Permissions