extract data from SSAS and feed to a formated file / another application

Hello,

I'm looking for a tool which is able to connect to SSAS Cube, extract data and transfer it to a file (as interface to another application). With reporting services I can extract Cube data but can't transform to the right file format and with SSIS I can't extract the data from Cube.

Do you have any ideas, tools, links, etc. ?

Thanks in advanced!

-flyall-

Why would you say: "with SSIS I can't extract the data from Cube" - what is the issue? Others in the forum are apparently using SSIS for this purpose, like:

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

>>

...

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

...

>>

|||

Hi,

I tried a MDX query in "OLE DB Source" -> "SQL Command" but the tool can't identify the output coumns.

Is this a typical problem?

cu

-flyall-

|||

You can do it if you use a Data Reader source, but you should also be aware that all of your columns will be Unicode Text (ie the DT_NText type); this is because the AS provider isn't strongly typed because there are quite valid scenarios where a measure could return values of different types, such as a numeric value or a string. It's perfectly possible to do the conversion downstream if you need to though.

HTH,

Chris

sql

extract data from SSAS and feed to a formated file / another application

Hello,

I'm looking for a tool which is able to connect to SSAS Cube, extract data and transfer it to a file (as interface to another application). With reporting services I can extract Cube data but can't transform to the right file format and with SSIS I can't extract the data from Cube.

Do you have any ideas, tools, links, etc. ?

Thanks in advanced!

-flyall-

Why would you say: "with SSIS I can't extract the data from Cube" - what is the issue? Others in the forum are apparently using SSIS for this purpose, like:

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

>>

...

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

...

>>

|||

Hi,

I tried a MDX query in "OLE DB Source" -> "SQL Command" but the tool can't identify the output coumns.

Is this a typical problem?

cu

-flyall-

|||

You can do it if you use a Data Reader source, but you should also be aware that all of your columns will be Unicode Text (ie the DT_NText type); this is because the AS provider isn't strongly typed because there are quite valid scenarios where a measure could return values of different types, such as a numeric value or a string. It's perfectly possible to do the conversion downstream if you need to though.

HTH,

Chris

Expression from within sproc?

If I'm inside a stored procedure called by an MDX query, the following would query the cube and figure out the sales for the current tuple:

new Expression("[Measures].[Internet Sales Amount]").Calculate(null).ToDouble()

So I was expecting to be able to do that from within a stored proc called from a DMX predict query. I was hoping that I could evaluate the $Cluster expression from within a sproc and figure out what cluster the current row in the DMX query belongs to. Basically, I'm trying to make my sproc function signature more elegant so I don't have to pass in the NodeId like:

select $Cluster, MyAssembly.MySproc(PredictNodeId(Cluster())) as CoolData
from [Customer Clusters]
NATURAL PREDICTION JOIN
(select * from [Customer Clusters].Cases) as x
where $Cluster = 'Cluster 10'

Is there any way to do that?

Unfortunately no, although this is an improvement we are considering for future versions. The improvement could be to enable you to access the input case directly and/or call the models prediction functions.

Exporting Tools

Hi,

I'm looking how export a Cube of my MS Analyzer Services.
I made a cube from the Foodmart DB, and i nedd to share the cube with some mates.
We don't want to build the cube every time that we change the notebook where we work :)

thanks a lotYou can use backup/restore to do this.