Posts
Hello again.
So today I want to talk about the requirement I came across during the project, which is about seeing the data they want to see based on the filter criteria selected on the dashboard. Like any requirement gathering process, we have analyzed the requirement, translated into the technical language and built the report as first cut. During the validation stage, as the analysts and designated users played around with the reports, they have brought more points that they wanted to see modifying. This is quite easy to understand since we can just point at the rows in the report and say what want it or not. Yet, the scenario is still quite complex that required several days of trials and errors.
So let's look at the following report:
The above is just an example. It shows a list of objects (LSPs) and their start and end time. By the way, in any data warehouse projects, keeping historical record is always a big topic. It is essential to filter the right historical data accurately. Back to this requirement. We have start/end date that goes for 3 days. In each day, LSP starts and ends at different hours. There are also LSPs that doesn't have end date, which means it is still active now.
Now the requirement is that:
1. when user enters a date (July 20th) in the dashboard prompt, that report should return all of the LSPs that starts on that date or prior (anything less than or equal July 20th). Then end date should be after or on that date (July 20th)
2. If the record has no end date, that means it is currently active, so as long as its start date is earlier than user entered date (July 20th), it should come on to the report no matter what.
3. If the record has end dates, then based on the hours that users select, only those that starts and end within the hour selected should come on to the report along with those that doesn't have end date.
4. If a record that doesn't start between the hours selected by the users, however it ends during the hours, it should still show up on the report. In other words, if selected range is 9-12, when a lSP started at 7 but ends at 11, it should still show up.
5. If an object starts at 23 and ends on 0 the next day, it should still be consider as current date record, it should show on the report when user set the start hour as 23 of that date even if the end date is 0 the next day.
6. All of the hour prompt values still have to base on the date prompt values. In other words, if the date prompt value is on a specific date like July 20th, then all of the records should be on or prior to july 20th, then within this result set, users can zoom in their search by selecting specific hourly period to see what LSP is active at this period, meaning either it starts or ends during this period, or starts at this period and never ends.
So enough said, it looks too much for me to grasp and get it all right at once. But it may not be as bad as it looks if I break things down into smaller components and tackle them one at the time..
First of all, the start and end date from the report is a timestamp and it has to show this way in the report. The date prompt is passing only date data type. So my first step is to add a few columns on the report by creating a date column and hour column. So we know have LSP Start/end date and LSP Start/End Hour. I will use the date column to filter from the date prompt and hour columns to filter hour prompt values.
The formula for both columns are pretty simple. use cast(lsp start date as date) will give you only date value, and use hour(lsp start date) will give you the hour of the day with numbers from 0 to 23. Now for end hour, I decided to convert 0 to 24 for the requirement outlined in 5. I made the same change for the dashboard prompt so start hour prompt will have 0 to 23 in the drop down and end hour will have 1 to 24 in the drop down.
Based on this set up, I have finally created the below filter sets after 3 days of trials and errors:
So today I want to talk about the requirement I came across during the project, which is about seeing the data they want to see based on the filter criteria selected on the dashboard. Like any requirement gathering process, we have analyzed the requirement, translated into the technical language and built the report as first cut. During the validation stage, as the analysts and designated users played around with the reports, they have brought more points that they wanted to see modifying. This is quite easy to understand since we can just point at the rows in the report and say what want it or not. Yet, the scenario is still quite complex that required several days of trials and errors.
So let's look at the following report:
The above is just an example. It shows a list of objects (LSPs) and their start and end time. By the way, in any data warehouse projects, keeping historical record is always a big topic. It is essential to filter the right historical data accurately. Back to this requirement. We have start/end date that goes for 3 days. In each day, LSP starts and ends at different hours. There are also LSPs that doesn't have end date, which means it is still active now.
Now the requirement is that:
1. when user enters a date (July 20th) in the dashboard prompt, that report should return all of the LSPs that starts on that date or prior (anything less than or equal July 20th). Then end date should be after or on that date (July 20th)
2. If the record has no end date, that means it is currently active, so as long as its start date is earlier than user entered date (July 20th), it should come on to the report no matter what.
3. If the record has end dates, then based on the hours that users select, only those that starts and end within the hour selected should come on to the report along with those that doesn't have end date.
4. If a record that doesn't start between the hours selected by the users, however it ends during the hours, it should still show up on the report. In other words, if selected range is 9-12, when a lSP started at 7 but ends at 11, it should still show up.
5. If an object starts at 23 and ends on 0 the next day, it should still be consider as current date record, it should show on the report when user set the start hour as 23 of that date even if the end date is 0 the next day.
6. All of the hour prompt values still have to base on the date prompt values. In other words, if the date prompt value is on a specific date like July 20th, then all of the records should be on or prior to july 20th, then within this result set, users can zoom in their search by selecting specific hourly period to see what LSP is active at this period, meaning either it starts or ends during this period, or starts at this period and never ends.
So enough said, it looks too much for me to grasp and get it all right at once. But it may not be as bad as it looks if I break things down into smaller components and tackle them one at the time..
First of all, the start and end date from the report is a timestamp and it has to show this way in the report. The date prompt is passing only date data type. So my first step is to add a few columns on the report by creating a date column and hour column. So we know have LSP Start/end date and LSP Start/End Hour. I will use the date column to filter from the date prompt and hour columns to filter hour prompt values.
The formula for both columns are pretty simple. use cast(lsp start date as date) will give you only date value, and use hour(lsp start date) will give you the hour of the day with numbers from 0 to 23. Now for end hour, I decided to convert 0 to 24 for the requirement outlined in 5. I made the same change for the dashboard prompt so start hour prompt will have 0 to 23 in the drop down and end hour will have 1 to 24 in the drop down.
Based on this set up, I have finally created the below filter sets after 3 days of trials and errors:
So let's walk through it.
The first set of filter takes care of the date part of the requirement. I am taking values passed from the prompt via a presentation variable "varconfighistorydate", then compare it with start date (without time) and end date (without time). It is greater than or equal start date and less than or equal end date to make sure it fall in between.
Then I have 2 other filter conditions for the hours. The start hour is between the 2 values passed from the prompt with values "starthour" and "endhour". Because it is also required to return records that ends on the same hour range even if it doesn't start within the same range as outlined in 4 from above, I also have to create the same filter for end hour column as well.
Now here is the tricky thing as indicated in item 3 from the requirement list that, if the record doesn't have an end date, it should show up on the report as long as its start hour is in the range of hours. This means, I have to find a way to populate the end hour with a certain value that will always match the selected value from the hour prompt when the corresponding end date is Null.
So after some deliberation, I have decided to enter the following formula for end date column:
Case when LSP End date is NULL then @{endhour}{24} else case when hour(lsp end date) = 0 then 24 else hour(lsp end date) end end
In other words, when there is no end date, this column will have the same value as whatever selected from the end hour prompt on the dashboard, guaranteeing it being picked anytime.
It is also important to "AND" these 2 set of filter groups as they have to work both at the same time. I have tried "OR" and requirement 6 from the above list failed.
Now it is working as expected as shown the below 3 screenshots with each representing each date value selected:
Playing with the hour numbers, the report also changes display according to the expectation.
So the bottom-line is, when it comes to dealing with requirements that seems too complex at once, it's better to slow down, and break it down to smaller pieces. Figure out what are the must-haves, from there, keep building with more layers.
Thanks,
Until next time.
PS: I have realized later on that the solutions described in this post isn't 100% correct. Therefore, I have added another post as a follow up for this one with a complete solutions.
0 comments:
Post a Comment