Frustrated using 'timestamp' data

[shearerg]

Hi,

I'm finding it really frustrating trying to design reports which are capable of responding quickly when base tables are keyed using timestamps.

I have production, delay, runtime etc data which is generally keyed in the original tables using a timestamp. Some data goes back to 2001. Selection of this data for a time period, from the native tables and within DAI, is very fast using 'between' criteria. For example, the following query returns data within about a second from a table of 80 million rows.

select * from log where
machine = 'NM10' and
timestamp >= date('16-Jun-2008') and
timestamp < date('16-Jun-2008') + date('1 day')

However, within DAI use of 'between' criteria is not always appropriate. For example, displaying a runchart of data for a single day should only require the user to pick a single date from a calendar input. I can produce views of the data tables which include separate calculated 'date' and 'time' fields, but using these requires limiting the available date range otherwise processing time is extremely long. That is, I cannot simply allow the entire table contents to be considered. One table, for example, has around 80 million rows back to 2001. While selecting data for any time period using 'between' criteria is almost instantaneous, using a view becomes unusably slow.

So, what I really want to be able to do is to implement a 'between' type select statement whilst only having to input a single date (day/month/year) preferably using the DAI calendar picker.

I've nearly been able to achieve what I want using a dynamic sql view with a date parameter, shown below. The only problem is that I can see no way of forcing the date parameter entry to be done via a date picker, which would be really, really nice! This is not a real show stopper, but easy date picking would be a real feature for a lot of applications I have in mind. I can arrange for a date picker to work OK when using a dynamic sql view ... but the limitation on depth of data availability means this approach won't generally be reliable.

ScreenShot090.jpg (23.48 KiB) Viewed 8238 times

ScreenShot091.jpg (104.31 KiB) Viewed 8238 times

Any other advice on how to work with large data sets having timestamp keys would be appreciated. Does it sound like I'm doing something really wrong?

Regards,
Greg Shearer

[Carsten Sørensen]

Hi Greg,

We should have an option to trigger the date-picker for @PAR - I'll look into it.

We have had the request before regarding single date selection with timestamps - and "ignored it" as it could be worked around with between - I agree however that it is a bit "clumsy" - so let us look into it - and we'll get back to you on the issue!

Best regards,
Carsten

[shearerg]

Hi Carsten,

Must admit I'm quite ok using 'between' selection with the dynamic view and @PAR, as this offers a straight forward means of controlling the interval of interest. For production related presentation, supervisors and managers are generally interested in performance figures for shift, day, week, month and yearly periods, and I think the approach outlined can be modified to manage this kind of selection pretty well. For instance, using a 2nd @PAR to select the interval. So single date selection would be nice for some situations, but as you said, not essential.

However, having cracked the problem using this approach, being able to trigger a date picker for @PAR would be nice.

Only other problem I've found is that I haven't been able to create any 'Extra columns'. Whenever I try to do this I get an Ingres error. Could be something wrong with my formatting, as I don't find the examples very clear.

Just so you know, Ingres does have the 'between' sql operator, but I only realised this recently and have been using >= and < for so long it will take me a while to break the habit!

Regards,
Greg

[Carsten Sørensen]

Hi Greg,

If you enter:

@DATE:NOW()

in the default for the @PAR, like:

datepickerview.jpg (39.38 KiB) Viewed 8231 times

then it will tricker the date-picker:

datepicker.jpg (36.01 KiB) Viewed 8231 times

Best regards,
Carsten

[Bob Cergol]

Hi Greg,

Good work! Keep Carsten busy adding more enhancements!

Have you seen the extensive list of date functions that you can use as a condition in any report?

For example on the conditions tab in design you could just set the condtion for [Cost_Date] to @mth-1()

This would constrain the result set to records where cost_date is in the previous month relative to now. Then you could still have a Cost_Date filter in your report where the user could select a single day. Of course in that case you might want the report title to include the phrase "Previous Month's Costs", etc.

Bob

P.S. I also like to group by YYYY, then MM, then date on some reports where I know managers will want to drill into specific dates, but also like to start at "high altitutde" to see productivity numbers, etc. It is very fast on my data -- though I don't have 80 million row tables! My largest tables have several million records and most are well under a million.

[shearerg]

Hi Carsten and Bob,

Carsten,
Worked like a charm I've realised that my suggestion of using a 2nd @PAR for specifying an interval wouldn't be a clever idea, as I'd want it to appear as a dropdown list ... and I don't think you can do that ... So is this where I could make use of field variable I've got a lot to learn

Bob,
The sort of drill down you describe sounds just what I have in mind as well. The particularly large table I have is a bit ridiculous I must admit, although retrieving data from it is extremely fast, as long as you utilise the primary index. It contains minute-by-minute runtime status of machines, and there has never really been a need to limit it, although I should do that some day. Even then, it is likely to remain a reasonably large table. It is used to construct machine runtime graphs within shift and daily reports. It seemed a good candidate for checking out DAI capabilities.

I'm definitely still just learning how DAI really works, but I do have a preference to try and solve what I think are going to be my biggest problems first when investigating anything. That way I can gain some confidence that all other requirements should be able to be met. I've just found the list of date functions you mentioned (as I went looking for them) in the Release 6.4 documentation ... now I just have to understand how to make use of them!

Also, the use of DAI field references in queries, calculations etc is something that I haven't yet investigated. When ever I've glanced over the documentation it just hasn't clicked, and I've had other things to investigate so haven't pursued further. However, it looks like something I should get onto straight away, as I suspect it may make things a lot clearer once understood! I may have some problems in this area, as when I have tentatively tried to create calculated columns I've received error messages from the database server, so I need to investigate further.

Thanks for the advice!
Greg