SSAS Cube Measure fact table error

0
votes

I have a SSAS Cube with dimensions and measure/facts tables. I'm not able to add any string data type columns to measure group while creating cube. I get below error. "Errors in the metadata manager. The data type of the 'Loan Type' measure is not valid because the data type of the measure is a string type."

I did double check the data type for my column and it's varchar() on database and wchar in SSAS cube. I have changed the data type to Inherited and it still doesn't work. Not sure what to do.

Question: Can i add string/varchar data type to measure group in cube. I know measures are usually Int/double/date columns. Is there a possibility, Let me know also the error.

Thanks!!

2
ssisssasssas-2008
why tag informatica ? its MSBI right? and you could always convert varchar to int (stackoverflow.com/questions/15977911/…) – vhadalgi
Thanks for response. I shouldn't have added informatica but your answer doesn't make any sense. Does it work if i change to int because actual column in database is varchar or string. – user1810575
Measures in SSAS are typically aggregated with a sum/count/etc. You can aggregate something and then format it. Can you explain what you are doing with loan type string in relation to a measure? – mmarie
I'm trying to do a drill thru information so that Business users can see more information when they drill through and loan_type is one of the string data type columns. – user1810575
'Loan Type' should be an attribute in a dimension, not a measure. – StrayCatDBA

2 Answers

1
votes

Loan type should be an attribute of a dimension rather than a measure. SSAS works best with a dimensional model.

If you need to make a drill though action, there is built-in functionality for that in SSAS which allows you to choose which dimension attributes should be included). If you move to Tabular SSAS, you can use BIDS Helper to help you build the drill through.

0
votes

"Measures are the bits of numerical data that we need to aggregate." (Delivering Business Intelligence with Microsoft SQL Server 2012, Third Edition, by Brian Larson).

You mentioned that "measures are usually Int/double/date columns." Have you tried using numerical values for your measures? Then you could use string data types for your dimensions if needed.