none
query the measure group using where RRS feed

  • Question

  • I need to know why the cube data are different from the DB. so I'd like to query the measure group, but I don't know how to get all the columns in the measure group.

    this doesn't work as "top" is not supported in MDX 

    select top 10  * from  [DB Name].[Cube1].[MG1]

    Actually I only need to query the data for a partition whose period_key is 20091010.

    But the following doesn't work as it complains column period_key can't be found

    select  * from  [DB Name].[Cube1].[MG1]where [period_key]='20091010'

    Sunday, October 9, 2011 8:09 AM

Answers

  • I got the answer

     

    we can use 

    SELECT * FROM [$system].[DBSCHEMA_COLUMNS]

     

    to get all the available columns.

     

    Thanks,

     

     

     

    • Marked as answer by Daniel Wu Monday, October 10, 2011 1:07 AM
    Monday, October 10, 2011 1:06 AM

All replies

  • Hi Daniel,

    I'd say not data, but the way it's stored and abstracted to the end user is different in cubes in order to do the complex analytics and  to over come issues which could be seen in RDBMS. T-SQL & MDX both are different languages although they might look similar.

    Where are you using select  * from  [DB Name].[Cube1].[MG1]where [period_key]='20091010' query? Do you want to write an MDX? What exactly you want to fetch?

    So if you can provide more details it could be helpful to address your problem.

    Regards,
    Santosh


    It feels good if you give us some points for helpful post. :)
    Sunday, October 9, 2011 8:51 AM
  • MDX-syntax is quite different from T-SQL:

    MDX

    SELECT something
    
    ON COLUMNS
    
    something
    
    ON ROWS
    
    FROM somecube
    
    WHERE [DimPeriod].[Period].&[20091010]

    You can put the specified measures on the rows, and one or dimension members on the columns.

    HTH

     

    Sunday, October 9, 2011 1:55 PM
  • I got the answer

     

    we can use 

    SELECT * FROM [$system].[DBSCHEMA_COLUMNS]

     

    to get all the available columns.

     

    Thanks,

     

     

     

    • Marked as answer by Daniel Wu Monday, October 10, 2011 1:07 AM
    Monday, October 10, 2011 1:06 AM