Solved

How to get in MDX the lowest level source of a dimension?

Posted on 2006-07-09
2
467 Views
Last Modified: 2008-02-01
Dear experts,
I need to get the lowest level in a dimension, for example level "Store Name" in dimension STORE:

     Level             Source Column             Source Table             Member Count
     Store Country             store_country             store             3
     Store State             store_state             store             10
     Store City             store_city             store             24
     Store Name             store_id             store             24

(this is Sales cube in FoodMart Olap database example)

I need to know his Source Column and Source Table. (store_id and store respectivly).

More generally, how do you get metadata from MDX?

Thanks in advance
0
Comment
Question by:icad01
2 Comments
 
LVL 8

Accepted Solution

by:
raj_ earned 500 total points
ID: 17067578
please investigate this thread:
http://groups.google.com/group/microsoft.public.sqlserver.datawarehouse/browse_frm/thread/cf29f044418bf3a9/9d78aaee689fe1fe?tvc=1&q=information+schema+in+MDX&hl=en#9d78aaee689fe1fe


MDX always works with the cubes, so it is not possible to obtain list of the
cubes through MDX.
If you already know the cube, you can get some information about dimensions
and levels, i.e.

WITH MEMBER Measures.X AS Dimensions.Count
SELECT {X} ON 0 FROM Sales


Will give you count of dimensions. After that you can do something like


WITH MEMBER Measures.X AS Dimensions(0).Name
SELECT {X} ON 0 FROM Sales


to get the name of the dimension, and


WITH MEMBER Measures.X AS Dimensions(0).Levels.Count
SELECT {X} ON 0 FROM Sales


0
 

Author Comment

by:icad01
ID: 17068528
Dear raj,
thanks for your fast answer.
I realize that I have a lot to learn before using MDX.
I didn't know where to put the code to get any result (in ADO?)!
Thanks anyway.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 32
What is the proper way to use for criteria in left join? 7 28
optimize stored procedure 6 28
SQL Server Error 21 8 25
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question