locked
Find data type of measures in Cube using MDX queries RRS feed

  • Question

  • User1035898639 posted

    I am working SSAS MDX queries. Now I need to identify the data type of each columns from the MDX queries. I have already tried as below.

    WITH MEMBER PageSize AS 1  Member [Measures].[My Type] as TypeName([Measures].[Budget Delivered COGS])

    enter image description here

    But it returns double for the currency field. First of all does Cubes have the data type as currency, percentage?

    If not, is there is any way to find out whether the column is currency or percentage or double or integer?

    I have seen this post Find the data type. But that is not feasible with the currency and percentage.

    Here I am pasting my entire query.

     WITH MEMBER PageSize AS
      1
    MEMBER [Measures].[My Type] AS
      TypeName ( [Measures].[Budget Delivered COGS] )
    MEMBER [PageNumber] AS
      1
    MEMBER [Measures].[Orderby Measure] AS
      [Measures].[Budget Delivered COGS]
    MEMBER [Orderby] AS
      "BASC"
    SET ROWAXISWOF AS
      NonEmpty (
        ( [Time Periods].[Fiscal Year].[Fiscal Year].Members, [Time Periods].[Fiscal Quarter].[Fiscal Quarter].Members, [Time Periods].[Fiscal Month].[Fiscal Month].Members, [Time Periods].[Fiscal Week].[Fiscal Week].Members ),
        {
          { [Measures].[Budget Delivered COGS] },
          { [Measures].[Break Even Delivered] }
        }
      )
    SET ROWAXISWF AS
      Filter ( ROWAXISWOF, [Measures].[Budget Delivered COGS] )
    SET ROWAXIS AS
      IIf ( 'Filter' = "NotFilter", ROWAXISWF, ROWAXISWOF )
    MEMBER [Measures].[MaxRowCount] AS
      ROWAXIS .Count
    SET ROWAXIS_Count AS
      IIf (
        'Paging' = "Paging",
        (
        CASE
        WHEN [Orderby] = 'BASC'
        THEN Tail (
          TopCount ( ROWAXIS, PageSize * PageNumber, [Measures].[Orderby Measure] ),
          PageSize
        )
        ELSE Tail (
          BottomCount ( ROWAXIS, PageSize * PageNumber, [Measures].[Orderby Measure] ),
          PageSize
        ) END ),
        ROWAXISWOF
      )
    SELECT ( IIf (
      'Paging' = "Paging",
      (
      { [Measures].[Budget Delivered COGS], [Measures].[Break Even Delivered], [Measures].[MaxRowCount], [Measures].[My Type] } ),
      { [Measures].[Budget Delivered COGS], [Measures].[Break Even Delivered], [Measures].[My Type] }
    ) ) ON COLUMNS,
    ROWAXIS_Count ON ROWS
    FROM (
      SELECT (
      { [Time Periods].[Fiscal Year].&[2011], [Time Periods].[Fiscal Year].&[2012], [Time Periods].[Fiscal Year].&[2013], [Time Periods].[Fiscal Year].&[2014], [Time Periods].[Fiscal Year].&[2015] } ) ON COLUMNS
      FROM [Homestore Sales]
    )

    If we get the data in the query, I will loop through the column and find out in C#. Any help is much appreciated. Thanks in advance.

    Wednesday, December 30, 2015 11:37 AM

All replies

  • User1686483761 posted

    I would say that the $ is purely related to the format_string associated with the budget delivered measure - as opposed to a specific type. Are you not able to assume that all data extracted from cube is nvarchar and then convert further later in your process?

    Thursday, December 31, 2015 5:45 AM
  • User1035898639 posted

    Thank you so much for your reply. This PROPERTIES VALUE, FORMAT_STRING will only return the basic types like double or string. But my requirement is bit different. I need to know which one is currency or percentage types too. What I do now is, just looping through each cells and checks whether if it contains $ in it, it is currency and same for % too. Since I am looping through every cells, it is causing some performance issues. 

    Monday, January 4, 2016 12:38 PM
  • User1686483761 posted

    Hi Sibeesh,

    I find a similar thread, you could look it.

    http://stackoverflow.com/questions/34528747/find-data-type-of-measures-in-cube-using-mdx-queries

    Tuesday, January 5, 2016 9:50 AM