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

External notification of database updates

MS-SQL 2005, C# 2.0

I will have many client databases that will be updated. When they are updated I need to transfer some of that data to a central Server database somewhere on the Internet. Note, schemas do not necessarily match.

Transfering the data (Web Services, Remoting) is not a problem.

What I am looking for is a cool, correct, advisable way, for the client database to notify me of an Insert, Update or Delete. I can then initiate the connection to the Server and 'push' the data (maybe pull some back from the Server too).

Obviously Triggers may be a place to start... But I need to know 'external to the database' that an update has happened or can I send the data from within the SQL Server assembly?

Anyone have any ideas or a technique, maybe something new in SQL Server 2005 (all databases will be 2005), for acheiving this? Just so I don't go down the 'wrong' path...

There are some BLOB's involved (1-2 page PDF's), if that makes a difference.

I envisage that the process transfering the data will be a Windows Service running on each client. The connection may not always be available, so some kind of 'to do' list of outstanding data to be transfered is required.

I'm just starting on this, so any pointers would be great, I'm sure it's all been done before ;)

Thanks

Rob.

One interesting technique to consider would be using triggers and service broker. When an insert, update or delete occurs, send a message via service broker to your central server informing it of the change along with whatever data is necessary to identify it. The central server can then asynchronously receive and process the message.

Dan