MDX resultset in SSIS

Hi, I'm trying to write a MDX query resultset from a cube to a database table
via an SSIS package. I create an OLE DB source in a data flow and choose OLE
DB Provider for Analysis Services 9.0 as the connection manager. Then I run
it through some node afterwards but it's the OLE DB source node that's giving
me a headache.
Once run it gives me a...
[Source - Query [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 2005" Hresult: 0x80004005 Description:
"Server: The operation has been cancelled.".
and...
[DTS.Pipeline] Error: component "Source - Query" (1) failed the pre-execute
phase and returned error code 0xC0202009.
I sould say that the query is quite heavy.
Can anyone help, thank you!
Jacob
Hi,
Is there really no one who can help me here? I would really appreciate it.
Alternatively, maybe someone could tell me where/to whom I might pose my
questions.
Thanks a lot.
/Jacob

MDX resultset in SSIS

Hi, I'm trying to write a MDX query resultset from a cube to a database tabl
e
via an SSIS package. I create an OLE DB source in a data flow and choose OLE
DB Provider for Analysis Services 9.0 as the connection manager. Then I run
it through some node afterwards but it's the OLE DB source node that's givin
g
me a headache.
Once run it gives me a...
[Source - Query [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 2005" Hresult: 0x80004005 Description:
"Server: The operation has been cancelled.".
and...
[DTS.Pipeline] Error: component "Source - Query" (1) failed the pre-exec
ute
phase and returned error code 0xC0202009.
I sould say that the query is quite heavy.
Can anyone help, thank you!
JacobHi,
Is there really no one who can help me here? I would really appreciate it.
Alternatively, maybe someone could tell me where/to whom I might pose my
questions.
Thanks a lot.
/Jacob|||Hi Jacob!
We are trying to accomplish the same thing as you describe above (with same
error messages). Were you successful with your efforts, or should we go anot
her way?
Thank you!
Radim Hampel

MDX Query never finishes

I am populating flatfiles from MDX queries run i SSIS against AS databases.

I have ran in to problems with MDX queries returning large resultsets that never finishes.

The query performed is purchase calculations over different time periods for each member in the customer dimension. There are 6-8 calculated members created and the customer dimension contains about 3,5 million members.

When query is run, after 1-2 hours the server stop allocating memory and starts to get data from cache in intervals. In the end of each interval, memory is dropped. In perfmon it seems to be no activity during long periods

I have given it as long as over 3 days without any result.

Server is 8 x 64bit cpu with 16 gig memory.

Some help in this matter would be much appreciated!

/David

Can you please provide the expressions for the calculated members and what they are intended to do. Perhaps this forum will be able to suggest optimizations. 3.5 million members in the dimension is not that big. While calculations could take some time - it should definitely be in seconds, not in hours.|||

If I am not reading this in the wrong way this is about transforming cube records dimension members to a text file with SSIS.

I am not sure about the API:s that SSIS uses for this task.

Perhaps it is better to write this query in SSRS2005 and use export to csv or xls-files?

Regards

Thomas Ivarsson

|||

Here is one of the MDX queries which never finishes:

WITH
MEMBER [hush?ll?vrigaK?pSen12M?nBeloppV?rde] AS SUM(({LASTPERIODS(12,[Date].[Year - Month].&[200612])}),[Po?ngbelopp]),NON_EMPTY_BEHAVIOR=[Measures].[Po?ngbelopp]
MEMBER [hush?ll?vrigaK?pSen12M?nBes?kV?rde] AS SUM(({LASTPERIODS(12,[Date].[Year - Month].&[200612])}),[AntalBesok]),NON_EMPTY_BEHAVIOR=[Measures].[AntalBesok]

MEMBER [hush?ll?vrigaK?pFgSen12M?nBeloppV?rde] AS SUM(({LASTPERIODS(12,[Date].[Year - Month].&[200512])}),[Po?ngbelopp]),NON_EMPTY_BEHAVIOR=[Measures].[Po?ngbelopp]
MEMBER [hush?ll?vrigaK?pFgSen12M?nBes?kV?rde] AS SUM(({LASTPERIODS(12,[Date].[Year - Month].&[200512])}),[AntalBesok]),NON_EMPTY_BEHAVIOR=[Measures].[AntalBesok]

MEMBER [hush?ll?vrigaK?pFfgSen12M?nBeloppV?rde] AS SUM(({LASTPERIODS(12,[Date].[Year - Month].&[200412])}),[Po?ngbelopp]),NON_EMPTY_BEHAVIOR=[Measures].[Po?ngbelopp]
MEMBER [hush?ll?vrigaK?pFfgSen12M?nBes?kV?rde] AS SUM(({LASTPERIODS(12,[Date].[Year - Month].&[200412])}),[AntalBesok]),NON_EMPTY_BEHAVIOR=[Measures].[AntalBesok]

SELECT {

[hush?ll?vrigaK?pSen12M?nBeloppV?rde],
[hush?ll?vrigaK?pSen12M?nBes?kV?rde] ,

[hush?ll?vrigaK?pFgSen12M?nBeloppV?rde],
[hush?ll?vrigaK?pFgSen12M?nBes?kV?rde],

[hush?ll?vrigaK?pFfgSen12M?nBeloppV?rde],
[hush?ll?vrigaK?pFfgSen12M?nBes?kV?rde]

}
ON 0,
[Kund].[Premiegrupp].children DIMENSION PROPERTIES MEMBER_KEY, MEMBER_CAPTION ON 1

FROM [xxxxxx]
WHERE {[Butik].[Kedja].&[7],
[Butik].[Kedja].&,
[Butik].[Kedja].&[2],
[Butik].[Kedja].&[10],
[Butik].[Kedja].&[13],
[Butik].[Kedja].&[16]
}

The following MDX finishes after 30-40 min:

SELECT { [Po?ngbelopp],[AntalBesok] } ON 0,

{nonemptyCrossjoin([Kund].[Premiegrupp].children)} ,

{[Stambutik].[Kedja - Butik].[Kedja].&[14].children})} DIMENSION PROPERTIES MEMBER_KEY, MEMBER_CAPTION,[Stambutik].[Kedja - Butik].[butiksnummer] ON 1 FROM [xxxxx]

The language is swedish and the meanings are [Kund]=[Customer], [Butik]=[Store], [Po?ngbelopp]=[Amount], [Antal bes?k]=[number of visits]

To use SSIS for this type task usually works fine, some small problems with datatypes though. I will look in to how SSRS works thanks for the tip.

|||

OK, I got it (Butik is a slang for Store in Russian too, I never knew it had Swedish origin!). First query has many problems with it, so I think we should start with the second query. The syntax for set on ROWS seems to be wrong, but I assume that you really want to do NonEmptyCrossJoin(Customers, Some stores). Given that there is usually strong affinity between customers and stores, the relationship is almost many to one, sometimes called "many to few", I expect much less than 3.5 million rows in the resultset. Can you tell how many rows are in the result for that query ?

When you look into Profiler, what do you see ? How long did it take to scan partition ? How long does serialization of the result takes ? I hope you are using OLEDB and not ADOMD.NET here too, because consuming big results in ADOMD.NET is not as optimal as in OLEDB...

|||

Thank you for your quick reply.

I the second query is actually not a problem, I just submitted it as a reference that works fine. But it returns about the same number of rows as the first one. This join includes a factless fact which describes the most frequently visited store, and therefore the result is bigger than an ordinary [Customer]-[Store] relation. I forgot to mention [Stambutik]=[favourite Store]

The problem i have is with the first query, and I am very interested in the problems you found there.

|||For me 30-40 minutes to execute a query is the problem. The second query doesn't have any calculations, of course once you start doing SUM(PeriodsToDate()) the running time increases greatly (although I wonder whether you use SP2). Can you please answer the questions from the previous post.|||

I use a DataReader source SSIS using an OLEDB connection. SP2 is not installed.

I have no data for the time to scan partitions, serialization takes 5 min of min total execution-time. the [favourite store] is related to sales via a many-to-many relation and . I also noticed I forgot to include the "WHERE {lastperiods(12,[Date].[Year - Month].[Month].&[200612])}"

|||So what exactly do you see in the profiler when you run the query. How many rows do you get back (both questions apply to the second query).|||

Now I have set up a new partition scheme for the cube so the second query executes considerably faster.

The second query executes in 15 min,

What I see in SQLprofiler is:

Calculate Non Empty: 5 min duration

and a few Query Subcube events.

994 916 rows returned.

The first query still never finishes.

It has a lot of Query Subcube events.

I hope this helps you.

MDX Queries in SSIS

Posted to SSIS forum but was told this forum might be of better help.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=215075&SiteID=1

Essentially I'm using an OLE DB Source to fire off an MDX Query and I want to dump the results into a csv file.

I think I have this setup properly but when I run the package I get the following error.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2005" Hresult: 0x80004005 Description: "Internal error: An unexpected error occurred (file 'pcrsstore.cpp', line 325, function 'PCRSStore::UnLock').".

Can seem to find anything about this error, can anyone help?

Keith

Hi Keith.

First
Have you installed SP1 on you machine. If not, please try installing it and trying your experiment again.

Second.
Can you explain in a bit more details the way you've build your task in SSIS

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Yes I have installed SP1 on both the Ananlysis Server and my Client tools.

The tasks are pretty simple. I have 2 connections and 1 Data Flow Task.

Create OLEDB Connection, select
Native OLE DB\Microsoft OLE DB Provider for Analysis Services 9.0
as OLE DB provider, enter connection information.

In data flow, create OLE DB Source component, choose this connection, in Data access mode field select SQL command. Enter your MDX query in SQL command text box.

At this point I can preview the results of the query after receiving the following warning.

Warning at {C498A441-B272-48F7-AA8E-1C1BC11DDCD2} [OLE DB Source [1]]: The output "OLE DB Source Output" (11) references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR will be used instead.

I then see the results of the MDX query and the data is returned as expected.

I then have a Flat File Destination setup where I point the Data Flow Output to. It's a simple 2 column 1 row result set. I recieve no warnings or errors in the designer but when I run the package I get the error as explained above.

Just as a sanity check becuase this is my first time working in SSIS. I Changed the query to be a simple SQL query returning 2 rows and was able to run that just fine so I think I have the data flow setup properly.

In addition I have run a SQL Profile and I can see the query being sent to AS and the results being returned.

Appreciate the help.

|||

This looks like a bug with Analysis Services.

If you have some ideas try to work around it using SQL like syntax when sending query to Analysis services.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.