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

extended connect timeout does not work

I have a stored proc to query a very large database, and it always times out at the 30th sec, even though I have changed the connect timeout to 300, as follows

(Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.)

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Administrator\My Documents\abc.mdf";Integrated Security=True;Connect Timeout=300;User Instance=True

This timeout issue does not occur to smaller databases. Any idea?

hi,

the connection timeout setting is relevant to "connection timeout"

you have to modify the command timeout property within your code so that the executing command enables more time..

tipically

Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand With cmd .CommandText = "the command text or procedure name" .CommandType = CommandType.Text ' or CommandType.StoredProcedure .CommandTimeout = 300 'or whatever .Connection = yourConnectionObject End With

regards

|||Thanks for pointing out this. Can this be done once for all / globally, say by using a SQL statement?|||

hi,

AFAIK, that should be done for each command..

regards

|||I wish I am not asking too much, but how can this be done in a stored procedure, please?|||

hi,

actually that should be done outside the procedure, in the command (or whatever you use) to execute the actual procedure..

there's a server wide option, query wait, but I'd not set it to a defined/manual value as this one will be valid for all queries at instance level (again, server wide option).. I'd go for the standard behavior only setting "manual configurations" for particular activities via the (external) command property..

regards

Expression Constraint and Precedence

I have a flow like this:

Container A
|
|
++
| |
| Container B
| |
| |
Container C That Both Connect To
I want to conditionally execute Container B based on a variable. I did this with a Precedence constraint on the line from A to B.

In this case however if it doesn't execute Container C doesn't execute. This is bad since Container C should always execute.

I changed the precedence constraint of B to C to be a logical Or and now Container C starts executing while Container B is executing! Not what I am looking for.

How do I do this? I thought about disabling Container B using an expression on the container but that seemed ugly.

Thanks,

ChrisHi Chris,
Wow, this is a weird one! I've had some fun trying to work this out

1) Put B into a sequence container. Lets call our new sequence container "D".
2) Put an OnSuccess precedence constraint from A to D
3) Put an OnSuccess precedence constraint from A to C
4) Place a script task into D. It doesn't need any code in it because its not going to do anything
5) Put an expression precedence constraint from the script task to B.

B will execute depending on whatever you have in the expression on the precedence constraint (which is what you want) but C will always execute regardless!

You can download a demo of this solution from here: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20050727PrecedenceConstraintanomoly.zip
-Jamie

P.S. The link to your blog (www.vergentsoftware.com/blog/ckinsman) doesnt work. Well it didn't 30 seconds ago anyway.|||Put A and B into a sequence.
Put a precedence constraint between the sequence and C|||Thanks for the blog tip. I left out a letter.

Thanks also for the solution!

express edition

Hi,
is it possible to connect to the sql server express edition from other
computer on the LAN or not?
I have tested connecting but doesn't work. I have used different
conditions but ..
thanks,
AliStart->All Programs->Microsoft SQL Server 2005->Configuration Tools->SQL
Server Surface Area Configuration
spelunk untill you fine it, the change is easy.
"Ali" <nikzad.a@.gmail.com> wrote in message
news:1192571122.912160.54820@.e34g2000pro.googlegroups.com...
> Hi,
> is it possible to connect to the sql server express edition from other
> computer on the LAN or not?
> I have tested connecting but doesn't work. I have used different
> conditions but ..
> thanks,
> Ali
>|||By default, Surface Area Configuration for Remote connections is turned off
in SQL Server 2005. Enable it first
"Ali" <nikzad.a@.gmail.com> wrote in message
news:1192571122.912160.54820@.e34g2000pro.googlegroups.com...
> Hi,
> is it possible to connect to the sql server express edition from other
> computer on the LAN or not?
> I have tested connecting but doesn't work. I have used different
> conditions but ..
> thanks,
> Ali
>|||I'd like to add a note to messages of my friends above.
If you are going to be connecting to your SQL Server Express Edition
instance, then you'll need to configure your TCP\IP or NP settings from SQL
Server Configuration Manager.
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Ali" <nikzad.a@.gmail.com> wrote in message
news:1192571122.912160.54820@.e34g2000pro.googlegroups.com...
> Hi,
> is it possible to connect to the sql server express edition from other
> computer on the LAN or not?
> I have tested connecting but doesn't work. I have used different
> conditions but ..
> thanks,
> Ali
>