Share via

MDX query from SSAS change type

Dimitar 1 Reputation point
2020-09-15T06:44:41.743+00:00

Hi All,

I have one MDX query and It takes the data directly from SSAS. The problem is that some of the measures are coming as string but not as a number like they are in the SSAS. I want to change the data type or something else. Now Im trying to use it in SSIS with ADO .NET connector to take its data and export it in different table. The MDX query is coming as NTEXT and I've noticed that I have some "NaN" values + 1,03433342E-13, maybe If I convert these values the problem will dissapear, I dont know. Please for your help. Thanks.

P.S.: Here is my query:

SELECT NON EMPTY { [Measures].[M_E_Amount], [Measures].[PrevAndNext] } ON COLUMNS, NON EMPTY { ([fact_Employment].[DATAAREAID].[DATAAREAID].ALLMEMBERS *

[fact_Employment].[Business Line Code].[Business Line Code].ALLMEMBERS * [fact_Employment].[ID].[ID].ALLMEMBERS *[fact_Employment].[PersonnelNumber].

[PersonnelNumber].ALLMEMBERS * [dim_Calendar].[YYYYMM].[YYYYMM].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM

[Projects] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The problem comes from [Measure].[PrevAndNext]

Best regards,
Dimitar

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,252 questions
0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. m 4,271 Reputation points
    2020-09-15T10:14:10.387+00:00

    Hi @Dimitar ,

    Try Syntax as next:
    StrToValue(MDX_Expression [,CONSTRAINED] )

    BR,
    Mia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. Dimitar 1 Reputation point
    2020-09-15T10:43:58.283+00:00

    I've tried with a lots of different ways to use StrToValue but without success. How to implement it properly in my code?

    These are the errors:

    24843-image.png

    0 comments
  3. Darren Gosbell 2,376 Reputation points
    2020-09-16T01:13:39.957+00:00

    The issue here is that "columns" in MDX are not strongly typed, each cell has a type, but the column does not. This is because you can put measures on rows and something else on columns. Or you can have IF statements or scoped assignments that overwrite a given tuple with a different value which can be a completely different type.

    So if you want to export that results of an MDX query to a strongly typed table you would have to add a Data Conversion task inside your Data Flow in your SSIS package.

  4. m 4,271 Reputation points
    2020-09-16T01:50:28.867+00:00

    Hi @Dimitar ，

    Could you please show your code?
    Please try code as next: 

    With Member Measures.N As StrToValue ([Measures].[PrevAndNext],CONSTRAINED)   
SELECT NON EMPTY { [Measures].[M_E_Amount], [Measures].[PrevAndNext] } ON COLUMNS,   
NON EMPTY { ([fact_Employment].[DATAAREAID].[DATAAREAID].ALLMEMBERS *[fact_Employment].[Business Line Code].[Business Line Code].ALLMEMBERS [fact_Employment].[ID].[ID].ALLMEMBERS [fact_Employment].[PersonnelNumber].  
[PersonnelNumber].ALLMEMBERS * [dim_Calendar].[YYYYMM].[YYYYMM].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS  
FROM [Projects] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    BR,
    Mia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  5. Dimitar 1 Reputation point
    2020-09-16T06:42:16.363+00:00

    @Yuximiao-MSFT

    With your code I have the following error: 

       Query (3, 142) The syntax for '[fact_Employment]' is incorrect. ( With Member Measures.N As StrToValue ([Measures].[PrevAndNext],CONSTRAINED) 
     SELECT NON EMPTY { [Measures].[M_E_Amount], [Measures].[PrevAndNext] } ON COLUMNS, 
     NON EMPTY { ([fact_Employment].[DATAAREAID].[DATAAREAID].ALLMEMBERS *[fact_Employment].[Business Line Code].[Business Line Code].ALLMEMBERS [fact_Employment].[ID].[ID].ALLMEMBERS [fact_Employment].[PersonnelNumber].
     [PersonnelNumber].ALLMEMBERS * [dim_Calendar].[YYYYMM].[YYYYMM].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
     FROM [Projects] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS ). (Microsoft SQL Server 2016 Analysis Services)

    BR,
    Dimitar