{"action":"created","issue":{"url":"https://api.github.com/repos/laurolins/nanocube/issues/45","repository_url":"https://api.github.com/repos/laurolins/nanocube","labels_url":"https://api.github.com/repos/laurolins/nanocube/issues/45/labels{/name}","comments_url":"https://api.github.com/repos/laurolins/nanocube/issues/45/comments","events_url":"https://api.github.com/repos/laurolins/nanocube/issues/45/events","html_url":"https://github.com/laurolins/nanocube/issues/45","id":131582661,"number":45,"title":"Nanocubes for time ranges?","user":{"login":"hagmonk","id":13660,"avatar_url":"https://avatars.githubusercontent.com/u/13660?v=3","gravatar_id":"","url":"https://api.github.com/users/hagmonk","html_url":"https://github.com/hagmonk","followers_url":"https://api.github.com/users/hagmonk/followers","following_url":"https://api.github.com/users/hagmonk/following{/other_user}","gists_url":"https://api.github.com/users/hagmonk/gists{/gist_id}","starred_url":"https://api.github.com/users/hagmonk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hagmonk/subscriptions","organizations_url":"https://api.github.com/users/hagmonk/orgs","repos_url":"https://api.github.com/users/hagmonk/repos","events_url":"https://api.github.com/users/hagmonk/events{/privacy}","received_events_url":"https://api.github.com/users/hagmonk/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2016-02-05T08:16:20Z","updated_at":"2016-02-05T18:39:50Z","closed_at":null,"body":"Hi there! I'm grappling with an OLAP style problem and I'm hoping to apply nanocubes, but I'm not entirely sure how well my problem maps to this domain. \r\n\r\nI've got an event stream representing changes to a set of entities. Something like 30 million entities, each of which might have a dozen dimensions. New events for each entity could arrive years or seconds apart.  There is no spatial component to the data. \r\n\r\nI mostly answer queries along the lines of 'at midnight every day between 2015-01-01 and 2015-07-31, how many entities had dimensions A = 1, B = 8, C = 3'. Maybe a colloquial way of stating the problem could be 'at midnight each day, how many people are watching netflix, eating popcorn, and wearing red socks'. My event stream only tells me when events change.\r\n\r\nSo in Postgres (after months of research into the validity of this approach) I end up building table partitions for each dimension, each row containing the entity id, the dimension's value, and the tsrange for which this fact was true. Then the problem reduces to intersecting time ranges, and building plain old macro scale cubes to cache aggregation results. But the bloat is staggering: ~6 GB of compressed data when unpacked this way and indexed tops 120 GB, and I'm not even considering all the possible dimensions yet. I feel like I'm forcing myself towards a big data problem I shouldn't have.\r\n\r\nHow might one introduce the concept of an event with a duration into a nanocube? If you can point me in the right direction I'll be sure to contribute some sample code back to the repo :) "},"comment":{"url":"https://api.github.com/repos/laurolins/nanocube/issues/comments/180492834","html_url":"https://github.com/laurolins/nanocube/issues/45#issuecomment-180492834","issue_url":"https://api.github.com/repos/laurolins/nanocube/issues/45","id":180492834,"user":{"login":"hagmonk","id":13660,"avatar_url":"https://avatars.githubusercontent.com/u/13660?v=3","gravatar_id":"","url":"https://api.github.com/users/hagmonk","html_url":"https://github.com/hagmonk","followers_url":"https://api.github.com/users/hagmonk/followers","following_url":"https://api.github.com/users/hagmonk/following{/other_user}","gists_url":"https://api.github.com/users/hagmonk/gists{/gist_id}","starred_url":"https://api.github.com/users/hagmonk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hagmonk/subscriptions","organizations_url":"https://api.github.com/users/hagmonk/orgs","repos_url":"https://api.github.com/users/hagmonk/repos","events_url":"https://api.github.com/users/hagmonk/events{/privacy}","received_events_url":"https://api.github.com/users/hagmonk/received_events","type":"User","site_admin":false},"created_at":"2016-02-05T18:39:50Z","updated_at":"2016-02-05T18:39:50Z","body":"@mehmetminanc, thanks for the comments. It is something of a hard problem :) \r\n\r\nI do wonder whether nanocubes are truly unable to handle this. Thinking more, my instinct tells me that the spatial dimensions could be used/abused to model time. You could map the tsrange into latitude (leaving the longitude dimension at 0), then map the unique entity identifier to a time value. A query for spatial coordinates across the full dataset would normally give you the list of events that occurred in that region … in this model it would return you a list of entities that experienced changes in that time range.\r\n\r\nI remembered this general approach from [IndexiGoh, Cheng Hian, et al. \"Indexing temporal data using existing B+-trees.\" Data & Knowledge Engineering 18.2 (1996): 147-165.\r\n](https://www.comp.nus.edu.sg/~ooibc/stbtree95.pdf). They suggest modeling time ranges as a (time,duration) tuple which makes them mappable onto a coordinate system. \r\n\r\nI feel like the nanocube structure itself is quite generalized and my problem is likely a case of not knowing the right question to ask :)"}}