Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Exporting MDX queries to SQL Relational Databases using SSIS 2005


Exporting MDX queries to SQL Relational Databases using SSIS 2005

Author
Message
Andrew.Chen
Andrew.Chen
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 13
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
ANDREW.MUYOBO
ANDREW.MUYOBO
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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.
Satch24
Satch24
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 87
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
kec.santosh.joshi
kec.santosh.joshi
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 172
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search