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 <[email protected]> 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" <[email protected]> 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 <[email protected]> >> 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 <[email protected]> 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 < >>>> [email protected]> 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 <[email protected]> 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 >>>>>> >>>>>> >>>>>> >>>>> >>>> >>> >>
