1

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? – Michael Green Jun 18 '15 at 11:27
2

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 +
+            +
+------------+

This kind of depends on your ETL loading procedures and reporting requirements.

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          |
+---------------------------+

Then you can link that dimension to your fact table.

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. – selva Jun 19 '15 at 4:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.