MDX Help : All cube either does not exist or has not been processed

Question

Hi All,

I am getting below error when I am trying to put MDX query in OLE DB Source Editor in SSIS package.

Portfolio- All cube either does not exist or has not been processed

Also in my analysis services databases Cube Database name is different from cube name present in cube tab.e.g. Analysis services cube database name is ProdDB_R120 but in that particular cube database in cube option cube name is 'Portfolio'.

How should i write the MDX query to fetch data for above  cube where cube name and cube database name are different?

select [Measures].value on columns,

         [Dimenisons].Members on rows

from [Cube Name]

Any work around to avoid this error.Please help !

Thanks,

Vaibhav

Answer 1

One database can have multiple cubes with in it .Also it is not neccesary that the cube name should be same a the database name. The MDX query should be written on the cube only i.e your from clause should have the cube name.

To database name can be changed during deployment , using project properties.

Please take a look into ADventureworks solution as a sample which has multiple cube under one database.

Answer 2

What Sorna is telling is correct. Generally during cube name does not change between what your dev server and production server. But your AS database may change. It is like you SQL database name may change, but table name does not change...

Worst case scenario, if that is the case then your options is to execute the query from variable and have the variable dynamically set with Expression=True. So when dynamically building the variable your cube name will come from config file. here in config file you will have a config entry for CubeName variable which you will change accordingly during deployment. But again, this is not what generally you would want to do..

 

---

vinu

Answer 3

Thank you Sorna and Vinu for your reply.

In my case data is getting refresh with some tool which will create a new database after every run in analysis services.

E.g. If today tool is executing then it may create database with name Run1 though cube name would be Portfolio.

If tomorrow tool is getting executed then it may create database with name Run2 though cube name would be Portfolio.

So in this scenario, I want to get the data from latest database which got created due to tool run say Run2.

How should i ensure that MDX query which I have returned would only return the result form Run2 database and not from Run1 database for the same cube name Portfolio.

I was trying to follow similar syntax which we use in SQL USE [Database Name] but it is not working with MDX :(

Could you please guide me.

Thanks,

Vaibhav

Answer 4

BTW , where are you executing this MDX ?

In SSMS means , you should choose the right database and then on top of the cube you will be executing the MDX.Here in SSAS you dont have USe database.

IF you are using some linked server or ODBC connectivity , you should change the intial catalog name in the connection string.

Could you please give more details on where you are executing the MDX and what is the requirement ?

 

Answer 5

Basically, the client tool creates different AS databases. I don't think it changes the names of the cube within the AS database. 

For the kind of requirement you have, i feel it is wise to give you information on how you can get the info of AS databases, cubes etc and then from there on you can decide how you want to use these information to make your query dynamic.

XMLA Command to get list of databases

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

  <RequestType>DBSCHEMA_CATALOGS</RequestType>

  <Restrictions />

  <Properties />

</Discover>

http://www.ssas-info.com/analysis-services-faq/29-mgmt/147-how-get-a-list-of-analysis-services-databases-on-the-server-using-xmla-discover

DMV for getting the cube within AS database (AS2008)

select * from $system.mdschema_cubes

http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

In SSIS

You can declare variable. Set it's EvaluateExpression=true and then build the MDX dynamically. Then in OLEDB Source, use source as from Variable instead of table/view/sql command etc

Run the XMLA Command to get the databases. Pick the database name based on modified data. latest will be the one to pick. Then based on this get the cube names. I think it unnecessarily complicated.The right thing for the tool to do is not create separate databases/cube whenever it runs. Even if so, the tool should log the details for the consumer to pick the info from there.

---

vinu

Answer 6

Thanks Vinu,Sorna for your response.

I am able to get the all database names now with following query:

select

[CATALOG_NAME] from $system.dbschema_catalogs

Now I want to build the dynamic MDX query which will use [Catalog Name] and retrieve the data from [Catalog Name] database for particular Cube Say A.

I am planning to use SSIS to execute this MDX query and get the data in excel sheet.

Can we write the MDX query in following ways?

USE [Catalog Name]

select [Measures].value on columns,

         [Dimenisons].Members on rows

from [A] where A is Cube Name

Please guide.

Thanks,

Vaibhav

Answer 7

Try the below approach in SSIS

1. Create a datasource with OLEDB Source for SSAS and recordset destination. Provide this query as a source in the source and populate the recordset.

2. Create a for each loop which loops though each record of the abpve recordset.

3. Inside the for each loop create a data flow task to load data from the SSAS cube to the excel file. During the loop , change the intial catalog of the datasource SSAS source connection to point to each catalog in the recordset. Also you may have to change the excel file name also using an expression.