Solved

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

Posted on 2006-07-09
2
443 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now