i have a string column (named status) in a fact table and i want to add it as a measure in the cube.
when i choose Usage="No aggregations" , i got the following error:
Errors in the metadata manager. The data type of the 'Status' measure is not valid because the data type of the measure is a string type.
am i choosing the wrong Usage ? how should i treat string measures or columns in cubes ?
- Changed type Butmah Wednesday, October 17, 2012 5:47 AM because it converted to a discussion
because it's a "status" on the combination of the ids in the fact table. the fact table is about timesheet; so the "status" indicates whether the timesheet for a certain employee under a certain supervisor in a specific date is accepted, rejected, or pending ....etc.
i though about degenerated dimension, but, first i'm not sure if this is the best solution for such case, second i don't know how to create a degenerated dimension in visual studio 2008 :P
i would not create a degenerated dimension for this. Instead create a static dimension say Status and have the data populated it through initial script. Most likely the chances of adding new status is less. If added, i would store it as unknown first and then update it.
Then in ETL, i would get the id using lookup and then store the Statusid in fact.
degenerated dimension is not a good idea as it will try to do a distinct of all fact values to create this one dimension with single attribute
you cannot create a measure on data type = string
I have encountered a similar problem where I needed to display textual statuses.
I handled it this way:
- I used numerical values for my measure, because as you discovered, SSAS doesn't like textual measures very much. You'll need to decide for your own situation how you want the status to be aggregated, but it's a lot easier to do aggregations with numbers, anyway.
- I then wrote a calculation to "translate" that into text, because SSAS doesn't seem to mind as much when you have textual calculation results.
If you had measures [AcceptedCount], [RejectedCount], and [PendingCount] and you want to display a status message based on whether there are any rejected/pending items, you could create a textual status calculation like this:
[Measures].[StatusText] = CASE WHEN [Measures].[PendingCount] > 0 or [Measures].[RejectedCount] > 0 THEN "Pending/Rejected" ELSE WHEN [Measures].[AcceptedCount] > 0
THEN "All Accepted"
ELSE "None submitted"
- Edited by Gregg_ Tuesday, October 16, 2012 5:07 PM
thanks all for your replies,
Vinuthan, would you please explain more why you suggested to have a physical dimension table instead of a degenerated one? . i've chose degenerated because the dimension has no columns except the status, so this dimension would be empty without the "transactional key" .
Gregg, thanks, but i didn't like having it hardcoded
In this case u would not prefer degenerated because status values are static and repetitive in nature...additionally since the degenerated dimension is built of your fact table, it would do a SELECT distinct status from Fact_Table. This is costly even if you put a index on the status column to make distinct work faster.
Hence i would suggest a physical or regular dimension.
Candidates for degenerate dimensions should have a cardinality reasonably close to the number of rows in the fact table. These dimensions take awhile to process because as Vinuthan stated above, the processing of that dimension involves issuing a SELECT DISTINCT Status FROM fact against the source. Ideally, fields such as Status that have important analytic usefulness belong in a Junk dimension. This junk dimension should consolidate several low cardinality attributes into a single dimension to reduce the dimensional explosion that will occur when a single business process has several different status that codes that may be useful.