SQL window function can get the rolling weekly average. Try google "window function rolling average". Put the date dimension on cube, plus the window function, together give you what you want.
On Fri, Mar 2, 2018 at 12:58 AM, deva namaste <ohd...@gmail.com> wrote: > Thanks Alberto. So you would recommend me to create daily one record in > fact table? so from 6 records for year, you would recommend to create 365 > records with difference invalues between them. So I can sort data from > dimension based on week, month, year, etc. But I was more worried about > amount of data will be stored in fact table in cube. so for 10 Million > items, we are talking about 10 x 365 = 3650 Millions. Do you think > performance will be impacted? or other method where I can put only 6 > records per item in fact table, so 10 million x 6 = 60 Millions and then > use some sql for better performance? thanks > > On Thu, Mar 1, 2018 at 11:36 AM, Alberto Ramón <a.ramonporto...@gmail.com> > wrote: > >> You cant portioned your cube per week. Must be per yyyy-mm-dd >> >> You can perform your own test. Doing a calculate per year as dim and >> year as sum of days >> >> On 1 Mar 2018 3:50 p.m., "deva namaste" <ohd...@gmail.com> wrote: >> >>> Hi Alberto, >>> >>> when I was saying 6 vs 365 its for one item. for 20 Million items it >>> will multiply by a lot. Do you think it wont make much differnce? >>> Also what is YY-MM-WW ? so I can explain you? Basically I need same >>> avg() for week, month, year, etc. >>> >>> Thanks >>> Deva >>> >>> On Thu, Mar 1, 2018 at 8:42 AM, Alberto Ramón <a.ramonporto...@gmail.com >>> > wrote: >>> >>>> - the 95% of time response, are latencies (= there is no difference >>>> between sum one int or 365, I thought the same when I started with >>>> Kylin) >>>> - The YY-MM-WW, is not implemented, but can be nice if you can >>>> contribute to it >>>> >>>> Alb >>>> >>>> On 28 February 2018 at 22:59, deva namaste <ohd...@gmail.com> wrote: >>>> >>>>> I was thinking of saving only 6 records in kylin instead of splitting >>>>> them outside in daily avg and adding 365 records for each item. So is >>>>> there anyway I can achieve using sql level in kylin or have changes to >>>>> model to accomodate above change? Please advice. Thanks >>>>> >>>>> On Wed, Feb 28, 2018 at 5:51 PM, Alberto Ramón < >>>>> a.ramonporto...@gmail.com> wrote: >>>>> >>>>>> Sounds like: >>>>>> - your minimum granularity for queries are on Weeks, your fact table >>>>>> need be on weeks (or less, like days) >>>>>> - you will need expand you actual fact table to weeks (or more, days) >>>>>> Example use a hive view >>>>>> - as extra: Kylin can't use partition format columns on weeks, the >>>>>> minimum es days >>>>>> >>>>>> Alb >>>>>> >>>>>> On 28 February 2018 at 21:51, deva namaste <ohd...@gmail.com> wrote: >>>>>> >>>>>>> Hello, >>>>>>> >>>>>>> How would I calculate value for a week while I have bi-monthly >>>>>>> values. >>>>>>> >>>>>>> e.g. Here is my data looks like - >>>>>>> >>>>>>> Date - Value >>>>>>> 01/18/2017 - 100 >>>>>>> 03/27/2017 - 130 (68 Days) >>>>>>> 05/17/2017 - 102 (51 Days) >>>>>>> >>>>>>> I need average value per week, as below. Lets consider between 03/27 >>>>>>> and 05/17. So total days between period are 51. so Daily average would >>>>>>> be >>>>>>> 102/51= 2.04 >>>>>>> >>>>>>> Week4 (Starting March 26, #days = 4) = (4 x 2.04) = 8.16 >>>>>>> Week1 (Starting Apr 2, #days = 7) = 14.28 >>>>>>> Week2 (starting Apr 9, #days = 7)= 14.28 >>>>>>> Week3 (starting Apr 16, #days = 7)= 14.28 >>>>>>> Week4 (starting Apr 23, #days = 7)= 14.28 >>>>>>> week5 (Starting Apr 30, #days =7)= 14.28 >>>>>>> week1 (starting May 7, #days = 7)= 14.28 >>>>>>> Week2 (starting May 14, #days = 4)= 8.16 >>>>>>> >>>>>>> But as you see that period from 01/18 to 03/27, have 68 days and >>>>>>> daily average would be 130/68=1.91 >>>>>>> >>>>>>> So really to get complete week I need 3 days from 130 value and 4 >>>>>>> days from 102 value. >>>>>>> >>>>>>> So real total for that first week would be - >>>>>>> Week4 (Starting March 26, #days = 4) = (4x2.04=8.16) + (3x1.91=5.73) >>>>>>> = 13.89 >>>>>>> >>>>>>> How would I achieve this in Kylin? Any function? or other method I >>>>>>> can use? >>>>>>> Just for 6 records for year, I dont want to populate daily records. >>>>>>> Thanks >>>>>>> Deva >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >
