Retrieve all members using ADO-MD

Question

Retrieve all members using ADO-MD

Tiago Rente

Default port

Points: 1403
More actions
August 24, 2005 at 9:54 am

#110903

Hi,

Our end-users use intensively the Pivot Tables to access the OLAP cubes. Sometimes there is a need to search a particular value in the members and levels of a particular dimension, but the PTS does not have this functionality.
We have tried out this functionality of Microsoft’s Cube Analysis, but we never were able to use it.
Nevertheless, since our end-users prefer the PTS, we develop an add-in to Excel that implements this functionality. However, the functionality works fairly fast in big dimensions if the OLAP server is in the LAN. When the OLAP server is in the WAN the response time is impractical slow.

The problem we were able to see is that the method used in getting each member at the time, even if we use the Cache Policy=7.
We use ADO-MD instead of DSO, because not all users have DSO installed on their PCs.

Is there a way to retrieve all members (including levels) from a dimension using ADO-MD is one instruction?

Thanks in advance.
PS: Originally posted in http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.olap

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Have you tried using the SQL retrieval method but still using ADOMD? In BOL there is a section on using a recorset (instead of cellset) for data retrieval. This will also let you use SQL intead of MDX, this is irrelevant really but it may make it easier to word the query. Below is a query using a linked server (ie you'd be setting the server etc in your connection [cn]) but the concept is the same.

select * from openquery( LINKED_OLAP, 'select distinct [Productroduct Family] from sales ')

this returns a distinct list of Product Family names from the product family level in the product dimension in Sales.

You could do the same thing in MDX (like... SELECT {[Product].[Product Family].MEMBERS} ON 0 FROM SALES ) which will return the list of members into the columns of a cellset (with the default measure of the cube) and you could then loop through that.

I sort of like the SQL approach, you can do some neat stuff with it (although this could prove to be slow when run across a cube with a large fact table), like this ->

SELECT distinct [Productroduct Category!NAME] FROM (select * from openquery( LINKED_OLAP, 'select distinct [Productroduct Category] from sales')) a WHERE [Productroduct Category!NAME] LIKE 'Canned%' ORDER BY 1 ASC

HTH,

Steve.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

wonder if thats a record for most un-intentional smilies in a single post??

Steve.

Tiago Rente Default port Points: 1403 More actions · Answer 3

Thank you Steve.

We will try out your suggestions.

Tiago.