# Having Non-Numeric values as measures in Fact table

According to my research regarding having non-numeric value as measures, I found that having non-numeric won't be an optimal one.

So I planned to maintain numeric values. My scenario was "Employee's Attendance" and the measure may be "Casual Leave", "Sick Leave", "UnInformed Leave", "Comp off".

Is that I need to maintain column for all of these (4 measures) and will be updating based on the corresponding user's status. If I maintain like that means if a new one added means it will be like structure of the cube getting changed.

Is that will be a good one or any optimal solution available?

• Why not have LEAVE TYPE as a dimension and NUMBER OF DAYS as your measure? Commented Jun 18, 2015 at 11:27

As you noted in the comments for your other question your fact table data seems to be something like this:

``````+------------+
+ Sick leave +
+            +
+ Comp off   +
+ Sick leave +
+            +
+------------+
``````

You essentially have 2 options

Create separate measures

To achieve this you need to have to create 4 columns in your fact table and have the corresponding value set to 1 or 0.

For example

``````+--------------------------------------------------------+
| Casual Leave | Sick Leave | UnInformed Leave |Comp off |
+--------------------------------------------------------+
| 1            | 0          | 0                |0        |
| 0            | 0          | 0                |0        |
| 0            | 1          | 0                |0        |
| 0            | 0          | 1                |0        |
| 0            | 0          | 0                |0        |
| 0            | 0          | 0                |1        |
+--------------------------------------------------------+
``````

Then you can have a measure simply using a sum on all four columns to end up with 4 measures.

Create an extra dimension

You could create an extra dimension table for LeaveType like this:

``````+-----------------------------+
| Type_key | Type_Label       |
+-----------------------------+
| 0        | None             |
| 1        | Casual leave     |
| 2        | Sick leave       |
| 3        | Uninformed leave |
| 4        | Comp off         |
+-----------------------------+
``````

And then in your ETL you can determine which type of leave it is.

``````+---------------------------+
| Leave_Type   | Leave_Days |
+---------------------------+
| 1            | 1          |
| 0            | 0          |
| 2            | 1          |
| 3            | 1          |
| 4            | 1          |
| 0            | 0          |
+---------------------------+
``````

Decision points

The ease of implementation depends on your ETL process (do you have one?). One can be easier to implement over the other.

The behavior of the result is also a bit different. For example with multiple measures you have to add every measure to your report, with a dimension you have to put the dimension on an axis.

It also has an impact on possible ways to do MDX calculations, filtering and so on.

I cannot decide in your place what the best option is and would suggest you do a small proof of concept and play around with it to see what suits you best.

For example if you put your dimension on the horizontal axis in excel the leave types with no values will not be shown. If you have several measures you will have an empty column the length of your report.

Another example is maintainability. Depending on your tooling and possible ETL setup, how much work is it to add a new leave type 'child sick'.

• Hi thanks for the suggestion . It was helpful. Commented Jun 19, 2015 at 4:01