0

I have the following 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 with it is that I cannot limit or change the type of [Measures].[PrevAndNext]. I want to do it, because Im using this query in SSIS and Im trying to import it in database as value. The problem is that the SSIS gave me this error: "Conversion failed because the data value overflowed the specified type.". in the Source and Im not able to change it. The only way is to optimize the query. Is there anyone who can help me? Big thanks!

jarlh
  • 42,561
  • 8
  • 45
  • 63
HackingWiz
  • 49
  • 5
  • What are the data types of `[Measures].[PrevAndNext]` in cube and in the SSIS Source Component? – Ferdipux Sep 14 '20 at 06:32
  • The data type in the Cube is Decimal Number, in SSIS Source Component they are comming as string. – HackingWiz Sep 15 '20 at 07:03
  • Check the article https://stackoverflow.com/questions/44592041/configuring-an-mdx-query-on-ssis on how to set up MDX query as a SSIS data source. – Ferdipux Sep 15 '20 at 07:19
  • My setup is the same, the problem is only with data type of the measure called PrevAndNext and I dont know how to change it thru MDX – HackingWiz Sep 15 '20 at 08:25
  • If you check Advanced settings of OLE DB Source and see settings for external col and output col for PrevAndNext? Can you add screenshots with the settings to the text? – Ferdipux Sep 16 '20 at 09:55
  • The datatype for external columns [PrevAndNext] is Unicode string [DT_WSTR] 255. It is the same for Output columns – HackingWiz Sep 16 '20 at 10:50
  • What is *Decimal Number* in Cube - what is defined in Cube model in VS for [PrevAndNext] measure? MS describes [data types in SSAS](https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/olap-physical/data-types-in-analysis-services?view=asallproducts-allversions) but there is no Decimal, only Currency or Single/Double data types. – Ferdipux Sep 16 '20 at 12:53

0 Answers0