You will be implementing a web based dashboard application,
a reporting tool that consolidates, aggregates and arranges
measurements, metrics (measurements compared to goals).
The application will be implemented in Perl or PHP with
Mysql as the database.
We will make the collection of the raw data into the tables,
your job is to read that data, parse it and display it into
the dashboard application (the original tables where the data
is colected will not be optimized so you may have to parse
them and generate new tables that are more optimized for
fast web access, this is up to you).
The tables are:
- [login to view URL] (id, name, description, status, group_id )
- [login to view URL] (id, name): parameters will get separated on
groups and each group will get displayed on a separate tab.
- dashboard.variable_history_mmYY(id, variable_id, value, insert_time)
(where the _mmYY is the curent month, this means you will get
a diffrent table for each month like: variable_history_0108,
variable_history_0208, variable_history_0308 etc.).The dashboard
will have to display data from all those tables in the same report
like the data is stored to just one single table so you will have
to find a solution for this (you can for example make a cronjob
that is parsing the variable_history_* tables and generate your
own optimized tables).
- dashboard.standard_values: id, variable_id, range ,DV, NV, Goal,
DV_direction, NV_direction, Goal_direction.
(for more details about this tables see bellow)
- other new tables: for any other functions from the dashboard you are
free to create any new tables and desigin the structure as you consider
the best.
Each variable will have an id, a name and a number of collected
values. Example variables: "Hits", "Page displays", "Sessions",
"User logins", "Failed login attempts" etc, all are numbers and
we will collect them into the variable_history_* on a regular interval
that can be better 1 and 24 hours. This means some variables will get
collected evey 24 hours, some every 1 hours, or even every 6 hours.
The variable_history_mmYY.insert_time will save the date and time
when that variable is collected, and at some point we may decide that
a variable that was collected every 1 hour to only be colected once
every 24 hours so your scipts must be able to detect this chnange
based on the variable_history_mmYY.insert_time value.
Regarding the standard values from the dashboard.standard_values table
we will save in this table the values that are considered to be dangarous,
normal and goals. The standard values are a combination of a number and
a direction. The direction can take as values 'M' or 'B' where M means more
then the value and B means bollow that value like:
- DV, DV_direction: this is the dangerous values for example for variable
"Page views" we could have DV=100000 and DV_direction=B, this means if the
"Page views" is bellow 100 000 then we have reached the dangerous value, or
another example for variable "Failed login attempts" we could have DV=1000,
and DV_direction=M means if we have more then 1000 failed logins then we
reached the dandgerous value.
- NV, NV_direction: this is the normal value, it's the same as with dangerous
value but its related to what we consider to be normal for that variable.
- Goal, Goal direction: This is the goal we want to reach for that parameter
and it behaves the same as the other two.
- [login to view URL]: this field can take as values "DAY", "MONTH", "YEAR",
"GENERALL". This means we will have difrent DV,NV,Goal values for each four time
ranges. So for each parameter we will have 4 rows into the table standard_values
one row for each range. The "DAY" are refering to daily standard values,
"MONTH" is referring to montly values, "YEAR" is year standard values, and
"GENERALL" is refering to all time (all the data we have colected for that
variable until now). One note: for example for comparing if a variable
have reached the GENERALL goal you whould need to open all the variable_history_*
tables that we have and this will take too much resources, you have to find
a solution for making this fast.
The web application will have the following sections:
1) Main Dashboard overview page:
This is the homepage of the application and it will display 2 sections:
- favorites parameters: the dashboard will allow to set some
paramaters as favorites and we will get them displayed on the overview
page. The way the parameter is displayed on the overview page depending
on the settings used when you add the favorites variables.
- the alarms: all the alarms for all paramaters will get displayed on
the overview page (when the parameter is bellow the DV value)
2) Groups pages:
This page will display for each grouup of variables the following:
- the favorites parameters for that group
- the alarms for that group
- all the variables from that group in a table with link to the paremeter
detail pages
3) Parameter details page:
This page will display statistical data for that variable: alexa like charts,
odometers, the table with the last values and for each recorded data and
based on the standard values we have to display the results and this can take
as values: OUT OF CONTROL, DANGEROUS,NORMAL,GOAL REACHED from where:
- below DV = OUT OF CONTROL
- between DV and NV = DANGEROUS
- between NV and GOAL = NORMAL
- over Goal = GOAL REACHED.
Note that the odometers must have those 4 areas on them with diffrent colors,
for each of the results areas.
Under the table will have the form for setting the standard values for that
variable and links to the comparation results pages for that parameter, see
bellow.
4) Settings pages for each group:
This will display a form with all variables in that group, we can update
the standard values for all parameters in that group. Note that standard
values could alos take dynamic values based on other variables, so they can
take as imput formulas and not just a simple value, the formuals could use
other variable values for example: "Page display" goal could be 10% of
"Hits" parameter, for entering the fomulas we need an ajax based formuala
editor, see bellow.
5) Standard values formular editor page(s):
The ajax formula editor may allow selecting parameter values and other
formulas output as variables and the formula will be a diffrent section
into the applicaion: we set the fomula and save it with a title and
text description and then into the standardvalue we will have a small
icon (forumla) for each value and when we click on it a new small popup will
open with all the formulas we have and we can select one. It is your responsbilty
to find the best visual solution for this section.
6) Settings history page:
All values for standard values will get recorded in a history table and we
should be able to restore them at any moment.
This page will display all past values we set for each of the standard
values and it will allow to bring back any of the past values.
7) Comparation rule bassed editor page
This will be a page that will allow us to add/delete/modify comparation
rules for variables at year, montly, weekly, daily and hourly levels.
Here you have an example:
- year => we compare "hits" for 2006 with "hits" for 2007
- montly => we compare "hits" for November 2006 with "hits"
for Novermber 2007
- weekly => we compare "hits" for firts week of Novemeber 2006 with
the firts week of Novemeber 2007.
- daily => we compare all "hits" we get in all Mondays until now
- hourly => we compare the "hits" for day of week hourly, so monday
11:00-12:00 with with others mondays 11:00-12:00.
This comparation is on demand, means by default a variable will have
no comparation rules at all. From the variable details page we will have
a link to this comparation rules editor from where we could add rules
for that variable. Once for exampe we will a montly comparation for
"Hits" variable we will start collecting the comparation data for that
variable means you will need to make some kind of cronjobs that is
executed regulary for processing the data for all the comparations.
We also want to be able to compare diffrent varables like: "login failure
with ad income" or "income ads with user registrations" etc.
It is your responsability to find the best visual interface for this
section (for most of the other pages you will get a prototype).
8) Comparation results display page:
This page will display the comparation results for rules created before,
for each we will display the history data and charts.
9) Dashboard overview configurator:
This will allow us to add/delete favorite variables that will get displayed
into the overview page. From here we alos select what information will get
displayed into the overview page for each parameter (the type of chart).
10) Alerts configuration page:
This will allows us to configure the way we receive alerts from the system.
Alerts can be sent via email or SMS (to send an sms you just have to send
a special formatted email message to our sms prvider).
11) Groups management:
This page will allow to add/delete/update variable groups
12) Waiting variables page:
This page will alow us to add new variables to the system. Note that the data
collecting for those varibales will be made by us so to add a new variable
to the system you only have to add it to the main [login to view URL] table
with staust WAITING. For this you use the [login to view URL] field and the
purpose if for us to know that we have to collect the values for that new variable.
To add a new variable we must select a group, description, and it must also
allow to select another variable from a diffrent group that will be a base for
that new variable.
You will get a more detailed prototype for the application after you will start
working on the project. Note that for the variable comparation section and the
standard variables formula editor you will have to design the visual interface of
the application yourself. Some pages from the application also have an option to
export data to excel, this will apper into the prototype.