SQL Server Performance Forum – Threads Archive
Connecting to a cube using SSIS
Hi, I am trying to connect to a cube using SSIS. These are the steps i am following: 1. i have created a new package.2 Next i added a data flow task is the control flow pane of the package.
3. Moving to the Data flow pane, i have added an OLE DB source.
4. To specify the connection properties, i have selected the Provider as Microsoft OLE DB provider for Analysis Services 9.0 Now comes the issue…..
I want to be able to write an MDX query. However when i try to view the available tables for this connection, it shows me only the dimension tables and no fact tables. How do i query the fact tables?? Am i doing something wrong???? Please help. Thanks in advance!! Ritika
You can even use Sql server management studio to write MDX query.
In SSMS, you will be able to see all the dimensions and measuregroups. Open SSMS and click on file->New Analysis server MDX query and write down your MDX and test it.
After that you can use that query in your SSIS package.
Hi,
In SSMS click on object explorer, connect to analysis server.
Type in all the details of AS server and confirm whether the AS DB you have created is deployed or not.
If you can see the db, check whether it is completely processed through properties.
Expand the tree structure and browse to check all the cubes in place.
If it is, then right click on DB and select NEW MDX. Also The option you tried in your thread was writing DMX and if you have not installed ay data mining then you came across the above error.
Ranjit, I got the error. Actually i had forgotten to specify the axis for the fileds in the MDX query, hence i got the error. And as for not being able to see both the cubes, i had not selected the second cube before clicking on new query, hence the error. Thanks anywyaz….
]]>