« previous next »
Pages: [1]   Go Down

Author Topic: DMA Time Profiles vs. ODS/ETL Aggregations  (Read 7546 times)

This topic contains a post which is marked as Best Answer. Press here if you would like to see it.

tony

  • Guest
DMA Time Profiles vs. ODS/ETL Aggregations
« on: August 27, 2009, 08:09:15 AM »
Hi everyone!  ;D

I'm having a debate at the moment, something along the lines of....

I was of the opinion that NOAGG was pre-defined in ETL to reflect 15 minutes of data, but now I think otherwise....  If you set up a DMA Layout with a 30 minute Time Profile when you review it in ETL Assistant, the NOAGG level of data is 30 minutes...

But what if you set up a time profile for 60 minutes...?  The NOAGG level would reflect 60 minutes - and isn't that the same as the HOURLY...?  Or are the aggregation rules different for each Aggregation level...?

I've been working with DMA/ETL/Brio for quite a while now and I think my theories are correct - that there are no pre-set "standards" for NOAGG (since it takes it's parameters from the DMA Layout Time Profile), but there are pre-set standards for HOURLY, DAILY, WEEKLY, etc...

Is anyone able to clarify this, any better than my ramblings...?  ???

Thanks!

Tony
Logged

Offline ecki

  • Sr. Member
  • ****
  • Posts: 329
  • Karma: 8
Re: DMA Time Profiles vs. ODS/ETL Aggregations
« Reply #1 on: August 27, 2009, 08:51:29 AM »
Hi Tony,

The NOAGG means Not aggregated, so the table contains "raw" data transferred from ODS. If you switch off the aggregation job, you will see that your other aggregates will be empty except NOAGG.

Cheers,

ecki.
Logged

Offline ecki

  • Sr. Member
  • ****
  • Posts: 329
  • Karma: 8
Re: DMA Time Profiles vs. ODS/ETL Aggregations
« Reply #2 on: August 27, 2009, 08:56:51 AM »
Interesting would be how aggregation process will cope with hourly aggregates if you set up collection interval to 65 mins. :)

e.
Logged

tony

  • Guest
Re: DMA Time Profiles vs. ODS/ETL Aggregations
« Reply #3 on: August 27, 2009, 09:20:56 AM »
???

...oh...

I'm not talking about the [i]ODS [/i] Tables... the [i]DATAMART [/i] Tables for NOAGG appear to reflect the Time Profile... if the Time Profile for data transferral is set to 15 mins, then 15 min time chunks are presented in the [i]DATAMART [/i] NOAGG Tables.  I just checked some of our DATAMART Tables for NOAGG and the Time Keys reflect 15 mins - I am assuming that this is because the Time Profile is set to 15 mins...

???

T

Logged

Offline ecki

  • Sr. Member
  • ****
  • Posts: 329
  • Karma: 8
Re: DMA Time Profiles vs. ODS/ETL Aggregations
« Reply #4 on: August 27, 2009, 09:51:38 AM »
Well it appears like that, but only because those data are exactly the same data statserver collected and dumped in to ODS for configured collection time profile. There is no additional aggregation involved for this table. Data there are merely transferred data. Here are examples from my DM NOAGG time_key


200702280831PST
200702280845PST
200702280900PST
200702280915PST

or

200907010345PDT
200907010403PDT
200907010425PDT
200907010437PDT
200907010445PDT
200907010500PDT
200907010515PDT


You can see there are hiccups.

Cheers,

e.
« Last Edit: August 27, 2009, 10:04:36 AM by ecki »
Logged

tony

  • Guest
Re: DMA Time Profiles vs. ODS/ETL Aggregations
« Reply #5 on: August 27, 2009, 11:08:35 AM »
...I'm still not sure we are talking about the same things...

The ETL transfers the raw data to the MART, at pre-defined intervals.  This is defined in the parameters for the ETL (Runtime) in CME (and also within the Java Properties files).  They look something like this;

ETL Runtime: ETL_Trans_Only>Command>java -Xmx2048m -jar transform.jar -conf etl.properties
ETL Runtime: ETL_Trans_Only>StartTime>0:00+0:01

So, the data is transferred from the ODS to the Datamart, regularly.

When it has arrived, Aggregation takes place (based on the Time Profile???).  Again, our ETL Runtime has parameters for this;

ETL Runtime: ETL_Agg_Only>Command>Java -Xmx2048m -jar transform.jar -conf etl.properties -aggOnly -
ETL Runtime: ETL_Agg_Only>StartTime>0:11+0:30

- So far, all of this has been about how the data is transferred and aggregated to the Datamart.  Now, when I look at a NOAGG Table in the Datamart, my Time Keys are like this:

For a Layout which is set to the Time Profile "30 mins":

200908271100BST
200908271130BST
200908271200BST

For a Layout which is set to the "Default" Time Profile:

200908271115BST
200908271130BST
200908271145BST
200908271200BST

Returning to my question...  It seems to me that NOAGG in the Datamart is defined by the Time Profile set in the DMA Layout and it should not, therefore, be assumed to be 15 mins - because it may not be....

I hope that is clearer?

T
Logged
Marked as best answer by on Today at 08:22:43 AM

Offline ecki

  • Sr. Member
  • ****
  • Posts: 329
  • Karma: 8
Re: DMA Time Profiles vs. ODS/ETL Aggregations
« Reply #6 on: August 27, 2009, 11:59:28 AM »
  • Undo Best Answer
    • Yes Tony, we are talking about the same thing. There is no reason to have extra aggregation process for data in NOAGG as this is already happening in Stat Server. You are getting summary for timeprofile dumped in to ods where then the ETL_Trans_only takes the data and places them into NOAGG source tables. So if your Stat Server was restarted in the middle of the time profile , begin time for data collection will be different and so will be your time_key in your _NOAGG source table as I showed you in my sample time_keys.

    Just for sure, here is a whole process how I think the CCA is working:

    Data Collection

    TS -> SS (every 15 min for example) -> ODS

    Transfer:(Set in your case set to check every minute for new data in ODS )

    ODS -> ETL_Trans_Only -> some_physical_table_stat_res which is source table for view V_<layout>_NOAGG


    Aggregation: ( set in your case to do the job every 30 minutes)

    <soucre table for>_NOAGG  -> ETL_agg_only  -> <source table for>_HOURLY
                                                                  -> <source table for>_DAY
                                                                  -> <source table for>_WEEK
                                                                  -> <source table for>_MONTH
                                                                  -> <source table for>_YEAR
                                                                  -> <source table for>_QUARTER

    Cheers,

    e.
    Logged

    tony

    • Guest
    Re: DMA Time Profiles vs. ODS/ETL Aggregations
    « Reply #7 on: August 27, 2009, 12:04:05 PM »
    Thanks ecki... we are talking about the same things! :)

    So, my question remains:

    What defines the length of time between the Time Keys applied in the Datamart NOAGG table?  Is it the DMA Layout, some StatServer Settings, ETL trans or agg - or something else...?  Because, from what I can see, it appears to be the Time Profile in the DMA Layout....

    ?

    Tony
    Logged

    Offline ecki

    • Sr. Member
    • ****
    • Posts: 329
    • Karma: 8
    Re: DMA Time Profiles vs. ODS/ETL Aggregations
    « Reply #8 on: August 27, 2009, 12:19:04 PM »
    Well, I think it is the beginning time of the collection process set by StatServer. But Stat Server is instructed by DataSourcer when to start and how long to collect based ofcourse on Time profile.
    Logged

    Offline Seb Reeve

    • Jr. Member
    • **
    • Posts: 62
    • Karma: 0
      • Sabio Limited
    Re: DMA Time Profiles vs. ODS/ETL Aggregations
    « Reply #9 on: October 01, 2009, 06:43:16 PM »
    The time keys in NOAGG are indeed only defined by the 0:00+0:15 collection interval definition.

    A re-read of the ETL Runtime User Manual implies that Hour level aggregation is simply derived from a calculation of the number of NOAGG intervals required. It also warns against defining a CollectionInterval duration which does not wholly divide into 60 minutes. (e.g.  60/45 minutes = 1.5) - Though I have never tried to do this to see what it does! Also setting value to over 60 will always result in an illegal value - again though I have no idea of consequences of doing this.

    In essence if you set your collection interval to 5 minutes, for newly activated layouts, NO AGG will collect in 5 minute time segments (you will need to define you collection frequency lower for ETL). Then Aggregation will take 60/5 = 12 NOAGG segments and aggregate these to hour level - and so on.
    Logged

    tony

    • Guest
    Re: DMA Time Profiles vs. ODS/ETL Aggregations
    « Reply #10 on: October 02, 2009, 09:32:37 AM »
    Thank you, everyone... Much clearer now...

    If I set a TimeProfile of 5 minutes (00:00+0:05), NOAGG will be in 5 minute chunks - HOURLY will simply be the total 12 of those chunks.  However, if I did something crazy, like 00:00+0:07, NOAGG will be in 7 minute chunks and HOURLY would be... broken...?

    T
    Logged
    Pages: [1]   Go Up
    « previous next »