none
Getting an error with measure's data type

    Question

  • Hello guys,

                       I have one fact table which has certain columns which i need to build report on means i need to use them as MEASURES , now the problem is that particular column/columns are not only containing int, float values but nvarchar values to like MEASUREMENT MEETS THE REQUIREMENT, this is one example value i have in that particular column. So when i tried outside of ssas cube, run the query like this , it works but not in a ssas cube due to an error.

    SELECT DISTINCT AnalyteID,NUMRES
    INTO #1
    FROM [LIMSDM].[dbo].[FactDP]
    
    
      SELECT ANALYTEID, MAX(NUMRES)
      FROM #1
      GROUP BY ANALYTEID

    but when i tried and make that column as measure and applied same MAX aggregation in BIDS it throws me an error like,

    Error	5	Errors in the metadata manager. 
    The data type of the 'NUMRES' measure is not valid 
    because the data type of the measure is a string type. 		

    PLEASE HELP! i need to finish this one by this friday


    ANK HIT


    • Edited by ank hit Tuesday, June 12, 2012 8:04 PM
    Tuesday, June 12, 2012 8:03 PM

Answers

  • This is what I have in the sample fact table I created to test this, does this look anything like a section of what you are working with?

    As you can see, there is no foreign key column here for NUMRES, just itself.

    Once you resolve this, it might be a good idea to go back to your data model and make sure that this NUMRES column should really be just a degenerate dimension. If it has any meaning outside of this fact table, you may benefit from moving it into its own dimension. This is more info about Degenerate dimensions: http://en.wikipedia.org/wiki/Degenerate_dimension

    Shabnam


    Thursday, June 14, 2012 6:21 PM

All replies

  • Hi Ank,

        Measures in SSAS cannot be of String type. You can create a degenerate dimension (a dimension built off of your fact table itself) from the column you are trying to report on. Then you add that dimension to your cube and build a calculated measure off of the currentMember of that dimension. Like this for example:

    CREATE MEMBER CURRENTCUBE.[Measures].[CALULATED NUMRES] AS

      [NUMRES DIMENSION].[NUMRES].CURRENTMEMBER.NAME,

    VISIBLE = 1;

    Your query should include the new dimension so that you get to the lowest level of your fact table. If your query does not get to the lowest level, you will see ALL as the value of this measure.

    There are several other posts in this Forum with similar questions, see this: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/db0d86c9-ec6c-4478-9115-52838c725e3e

    Hope this helps.

    Shabnam

    Wednesday, June 13, 2012 2:03 AM
  • So what should i keep in a fact table? like I have NUMRES values, so should i keep it as it is or PK from that new Dim table and keep it as FK in Fact table? 

    ANK HIT

    Wednesday, June 13, 2012 12:11 PM
  • I followed that link, the problem is when I click on ADD DIMENSION, i don't see NEW DIMENSION option?

    ANK HIT

    Wednesday, June 13, 2012 12:24 PM
  • What is the data type of NUMRES, and what is it that you want to report from this?

    If you need to report on NUMRES, I would look at creating a degenerate dimension with those attributes, and not use them as a measure. If you don't need them in the cube, simply leave them out.

    Wednesday, June 13, 2012 12:24 PM
  • Hi,

    One thing you can do is you can edit your Data Source View. You can pull the records from Fact where the Field is Numeric.

    For this you can use ISNUMERIC function.

    With this function you will have only Numeric records in the cube fact.


    Please mark the post as answered if it answers your question

    Wednesday, June 13, 2012 12:50 PM
  • I followed the LINK to generate degenerative dimension table, and i have created one too but the problem is, that NUMRES column is not unique and when i deploy, i am getting an error which says, 

    Warning	4	Errors in the OLAP storage engine: 
    A duplicate attribute key has been found when
     processing: Table: 'dbo_FactDP', Column: 
    'NUMRES', Value: '<0.01'. The attribute is 
    'NUMRES'.		
    

    So let me recall, when i click on NEW DIMENSION, it asks me for a MAIN TABLE and key columns and name columns so what shoul i select in them? like i have plenty FK in fact tables so do i need to select them or just that NUMRES?


    ANK HIT

    Wednesday, June 13, 2012 12:52 PM
  • Hi,

    One thing you can do is you can edit your Data Source View. You can pull the records from Fact where the Field is Numeric.

    For this you can use ISNUMERIC function.

    With this function you will have only Numeric records in the cube fact.


    Please mark the post as answered if it answers your question

    I gave a shot to it but it is something not possible, since most of the values are non numeric. 

    ANK HIT

    Wednesday, June 13, 2012 1:04 PM
  • If you put a FK in fact table, how are you planning to get max of the NUMERS field.

    Please mark the post as answered if it answers your question

    Wednesday, June 13, 2012 1:10 PM
  • Ank,

    Assuming that you do need the text values to be included in your report along with the numeric ones in one metric, here is how you set up the degenerate dimension:

    Create a New Dimension --> Choose Use an existing Table --> Choose your fact table --> For the Key Column pick NUMRES, for the Name Column pick NUMRES.

    In your fact table, you keep the NUMRES column and anything else you had before.

    Let me know if this helps.


    Shabnam

      

    Wednesday, June 13, 2012 1:12 PM
  • Ank,

       If you still get the duplicate attribute key error, chances are you have NULL values and empty strings in NUMRES column. Select distinct values in SQL to see if there are NULLs and Empty Strings. If there are, convert all the NULLs to Empty String or some other string that makes sense for you like 'N/A' before you pass them to SSAS. You can do this by replacing the table with a Named Query in the DSV or by putting a view on top of the table in the SQL database and using that in your DSV.

    Shabnam

    Wednesday, June 13, 2012 1:19 PM
  • Ank,

    Assuming that you do need the text values to be included in your report along with the numeric ones in one metric, here is how you set up the degenerate dimension:

    Create a New Dimension --> Choose Use an existing Table --> Choose your fact table --> For the Key Column pick NUMRES, for the Name Column pick NUMRES.

    In your fact table, you keep the NUMRES column and anything else you had before.

    Let me know if this helps.


    Shabnam

      

    I tried that but it gives me an above error which says and true that NUMRES column is not unique.

    ANK HIT

    Wednesday, June 13, 2012 1:20 PM
  • If you put a FK in fact table, how are you planning to get max of the NUMERS field.

    Please mark the post as answered if it answers your question

    FK means the keys which are PK in dimension tables, that what i meant.

    ANK HIT

    Wednesday, June 13, 2012 1:21 PM
  • Ank,

       If you still get the duplicate attribute key error, chances are you have NULL values and empty strings in NUMRES column. Select distinct values in SQL to see if there are NULLs and Empty Strings. If there are, convert all the NULLs to Empty String or some other string that makes sense for you like 'N/A' before you pass them to SSAS. You can do this by replacing the table with a Named Query in the DSV or by putting a view on top of the table in the SQL database and using that in your DSV.

    Shabnam

    No it's not about NULLs, i checked back using SSMS, some values like '<0.05' has 213 counts and there are many more like this.

    ANK HIT

    Wednesday, June 13, 2012 1:36 PM
  • Ank,

        When SSAS builds a dimension out of that column it submits a SELECT DISTINCT query to that column, so it does not matter that the values in the column repeat. I am not sure why you are getting that error. The only time I have seen that error when the key and name properties were set to the same column, is when there are NULLs and Empty Strings in the values or when there are spaces around characters. For example, you have " Measurement Not Valid" and another time you have "    Measurement Not Valid". When you do a SELECT DISTINCT from that column, what values do you get? Try to put a trim function in the SQL in front of the table and see if that makes a difference. Try this ltrim(rtrim(NUMRES)). Check the total row count to see if the number of rows reduces. IF yes, then I would try the trim with the dimension.

      You can get the exact query that SSAS uses to load the dimension from the window that opens when you process the dimension. If you click on the plus sign next to the statement, it will expand and show you the query.  


    Shabnam

    Thursday, June 14, 2012 12:57 PM
  • Ank,

        When SSAS builds a dimension out of that column it submits a SELECT DISTINCT query to that column, so it does not matter that the values in the column repeat. I am not sure why you are getting that error. The only time I have seen that error when the key and name properties were set to the same column, is when there are NULLs and Empty Strings in the values or when there are spaces around characters. For example, you have " Measurement Not Valid" and another time you have "    Measurement Not Valid". When you do a SELECT DISTINCT from that column, what values do you get? Try to put a trim function in the SQL in front of the table and see if that makes a difference. Try this ltrim(rtrim(NUMRES)). Check the total row count to see if the number of rows reduces. IF yes, then I would try the trim with the dimension.

      You can get the exact query that SSAS uses to load the dimension from the window that opens when you process the dimension. If you click on the plus sign next to the statement, it will expand and show you the query.  


    Shabnam

    Is it possible because I have selected NUMRES column in KEY COLUMN field as well as inNAME COLUMN field? if yes then what to select in them? Since I don't yet create any PK on fact table so is it the possibility? And I have a trim function when i was loading fact table so that shouldn't be the case.

    ANK HIT

    Thursday, June 14, 2012 1:57 PM
  • When you create a dimension from a fact table, SSAS creates its own internal keys to connect the dimension table to the fact table. You do not need to create a foreign key column for NUMRES in your fact table. You just use the same column to build the new dimension. Your dimension should look like this:

    Once you get the properties of the NUMRES attribute, you should see this:

    Thursday, June 14, 2012 6:10 PM
  • If you want to see what query SSAS is using to populate the dimension, you can get it from the processing window:

    If you run the query, you should get a clue as to where the duplicate is happening. Once you have the query, can you post it here for me to see?

    Thursday, June 14, 2012 6:21 PM
  • This is what I have in the sample fact table I created to test this, does this look anything like a section of what you are working with?

    As you can see, there is no foreign key column here for NUMRES, just itself.

    Once you resolve this, it might be a good idea to go back to your data model and make sure that this NUMRES column should really be just a degenerate dimension. If it has any meaning outside of this fact table, you may benefit from moving it into its own dimension. This is more info about Degenerate dimensions: http://en.wikipedia.org/wiki/Degenerate_dimension

    Shabnam


    Thursday, June 14, 2012 6:21 PM