Storing and reporting data at granular time

Adam Elvin

I have a dataset from a monitoring system, there are three measures, lower limit, upper limit and actual reading. In general four readings are taken per day, but not always at the same time so the data contains a date timestamp, so the chart is to look something like this

You will notice that the data points are dense but the x axis is displaying dates two weeks apart. What I have done so far is to create an entity called Time of Day with 1440 members, i.e. no. of minutes in a day with a code T00:00 and a Desc 00:00.  I loaded a cube with entity structure Day and Time of day and then made a data layout with Day and Time of Day in the rows.

 

 

This produced the following chart in BOARD

Which does correctly show the data but has some problems from a representation point of view.  Firstly because of the row nesting and because I want to select three months at a time, the day groupings are illegible, (and actually not required)

I actually want to show the Day on the x axis at 2 week intervals like the image above, but Instead I get time of day on the axis because it is the lowest entity on the rows.

 

The only solution I came up with was to use a custom time entity for Time of Day which has a relationship back to day, it will have a unique code for each minute of the day but the description will be the same for every minute of the day, so then I could have Time of Day only on the rows of my chart. The problem I see with this though is that I would need to generate 1440 new entity members for each day in my database, even on my small test set of 3 years it will be over 1.5m members creating massive sparcity and for starters excel doesn't have enough rows to allow me to create the dataset, as well is it being extremely laborius.

 

Can anyone suggest another way to approach this problem?

Stephen Kirk

I have also had this issue recently in trying to plot share prices - the number of days is very high for plotting say a few years of history which looks horrible on the x-axis.

It is possible to have a nicer x-axis by inputting an interval into the settings:

But this interval is now fixed, and if say the user wanted to zoom to a more granular level - the interval doesn't scale with the zoom.

Ideally the interval should be able to be dynamic - similar to the autorange option for the y-axis.