March 29, 2015 at 3:54 pm
Hi.
I'm building an OLAP cube about Customer Service Requests which has multiple date fields in the fact table. These join to a Time Dimension. To keep the example simple lets just say I have 2 date columns. One for the date the request was recieved and another for when the request was completed.
All requests have a recieved date, but if the request is incomplete, the Completed Date is NULL.
In my data source view I have two joins. One from each of the fact date fields to the Time Dimension date column. In the cube SSAS has created an extra dimension on completed date.
I have a single measure which counts all rows. I found that if I browse the cube and select the measure without any filters or dimensions, I get the total count minus the incomplete records. (Is there any way to influence this behaviour? why don't I get all rows? Can I a set it to ignore NULLS/Unknown?).
I've tried replacing the NULL completed dates with a future date '2999-01-01' and that seems to help, but I'm not sure if that is the best approach. I've read about using '-1' for unknown but as the Time dimension uses a date field I assume that wouldn't be possible.
If I perform a query and count records grouped by month, how can I guarantee that it's grouping/joining by the completed date rather than the recieved date and vice versa.
I assume this must be a common problem. Am I on the right track? Can anyone reccomend a better approach?
Cheers
Pete
March 30, 2015 at 2:04 am
This measure that counts all rows, what does it count exactly?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 20, 2015 at 2:44 pm
If you double click on a measure in Visual Studio a window pops up called "Edit Measure"
The window has 3 controls(?)
Usage: "Count all rows" is selected in the drop down box
Source table: is my fact table.
Source column: Can't be selected with "Count all rows" so I assume it counts all rows rather than calculate something on a specifc column.
April 20, 2015 at 4:29 pm
Yes, there is a way to influence the behavior of the null values. You can set/change these in your error configuration properties.
With that being said, it's really best practice to not have any orphaned fact records...meaning that no dimension keys should have null values. Each dimension should have a default record, and that key should be used for missing/non-existent dimension members. For the date dimension, "1900-01-01" is typically used as default.
Coming back to your specific scenario, I think it will be better addressed with a "Status" dimension or indicator. It would be fairly simple to then slice the row count by that indicator (complete/incomplete), as opposed to expecting your end users to figure it out (by means of interpreting the missing or default dates).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply