ETL Aggregation Issues

That is fine. Yes you are right, you cannot export layouts.
The layouts are using the same stat definitions, so this should not be the problem.
But as the 30 minutes layout has less data, I am assuming the issue could be on the collection side. The report layout is set to collect data every 30 minutes. This should not be a problem as report layouts by default are set to collect data every 15 minutes. However if the server is underdimensioned and stat server is dumping data for way more layouts during the same time, some data could get lost. In this case you probably would see in DMA null values when you open some chunk of the latest created report layouts. Could you check if this is the case?

If you do not see nulls there, could you compare the stats in chunks for each active agent report layout? Does the summary for the same time span give you the same odd results as you are getting from Data mart?

Just one question out of topic, why are you doing this? It would be easier to have the 30 minutes layouts and the hour stats you could just collect from aggregated tables.

When you do not find anything positive in the DMA, could you then tell me what are the tables’ names you extracted the data? Or are you using views?

I dont see any ‘null’ values, instead of ‘0’ values sometimes, this could be normal data.

I’ve tried to extract the report from different of views.
The 15mins & hourly gave me the same result, except 30mins report.

Hourly report extract from
V_AGENT_HOUR -->> V_2_COMPSTAT → R_2_STAT_RES

30mins report extract from
V_AGENT_30_NO_AGG → V_268_COMPSTAT → R_1_STAT_RES

15mins report extract from
V_AGENT_NO_AGG → V_1_COMPSTAT → R_1_STAT_RES

btw, what do you mean by this ‘It would be easier to have the 30 minutes layouts and the hour stats you could just collect from aggregated tables.’ ?

Well, surely you know this but CCA is collecting data for layouts according to collection interval you set. Then it is transfering via ETL-transfer process to Data mart and stored them in xxx_NO_AGG tables. Then ETL-Aggregation process takes the data from xxx_NO_AGG and populates aggregated tables xxx_HOUR, xxx_DAY, xxx_WEEK, xxx_MONTH, xxx_YEAR, xxx_QUARTER. You have obviously two layouts collecting the same stats (according to the templates, but I can be wrong as report layouts could be different) and set to collect data one for every 30 minutes and second every 1h. Right? So it would be enough if you have only the 30 minutes layout active and get the hour stats from aggregated table xxx_HOUR.

Now back to the two excel spreadsheets you posted. Is the source of the 30 min view v_agent_30_no_agg and the hour v_agent_no_agg?

And have you checked the nulls in the DMA?

Great work so far ecki… I like your style!

I’ve been keeping an eye on this thread for a while and I am scepticle when I get an exported “Excel” reports to compare stats, rather than looking at the original .BQY file. The simple reason for this is the possibility of “Business Rules” being applied to the statistical definitions within Brio/Hyperion. The column headings both state N_Inbound - but are they really what they say they are…? As you know it is very very easy to create a Computed Item with various statistics added together and minus that from the degrees relative to the position of the sun and call it N_Inbound …

I am with you on a couple of things;

1. I agree that it may be a case of the ETL not completing a data chunk transfer or aggregation correctly and some of the data is missing within the 30 min intervals. This usually means that either the Transfer or the Aggregation Java scripts are running concurrently and competing for resource.
1a. I would suggest that the OP (izai) look at his ETL data Aggregation and Transfer timings (CME>Applicatiions>ETL Runtime>Sections>ETL_XXXX (Trans, Agg and possibly Trans_and_Agg)) Then perhaps set Aggregations to run at 00:11+0:30 and Transfers to run at 00:00+0:01? That will mean the ETL (Java spawned processes) won’t be falling over themselves trying to Aggregate and also Transfer data to the DATAMART at around the same time.
1b. The OP (izai) might also want to look at beefing up the memory allocated to the ETL Aggregations and Transfers (CME>Applicatiions>ETL Runtime>Sections>ETL_XXXX>Command) - where the java -Xmx512m entry can be updated to 1024, 2048 or higher, to allow a higher allocation of memory to the Java process. Of course, you need to be careful not to allocate too much memory to each ETL Type, since you don’t want to overallocate the resources available on the Server.

2. I agree that it would also be beneficial for the OP (izai) to produce his Hourly reports from the 30min aggregate data, rather than using 2 sets of aggregated stats for what appears to be the same outputs. This would also mean you don’t need the Hourly stats and you can de-activate that Layout. (Less stress on the Server…)

Sorry if I stepped on your thread ecki - but I wanted to get my thoughts down, too… I hope it helps, rather than gets in the way…

Tony

Hi Tony,

Not at all mate! I welcome any thoughts and ideas. Unfortunately I sometimes unintentional make things more complicated as they are : However I believe he is accessing directly the source views on database level as he knows the base tables/views, though still the views could be the problem. Some people/developers tend to tinker with them as well instead to create their own custom views and introduce custom bugs.

Of course the race condition of ETL processes could be also problem but I doubt that the aggregation would fail if the Transfer is not completed at the time. I am sure that the aggregation will catch up the missing data in the next run.

Still my best bet would be the datasourcer-Stat server bottleneck. If they are on the same server then it could cause ugly data lost for the last layouts. Otherwise I cannot explain the zeros for some collections intervals with 900s of login time in the 30mins layouts. That is really strange.

Izai, probably it would be good idea to check the datasourcer and ETL logfiles for errors as well.
Also how many statistic requests has the Data sourcer registered against Stat Server? You will find this information in DMA on the status bar.

ecki.

…

Now you and I have taken the thread - I hope the OP (izai) is taking notes!

I’ve seen a situation in the past where a java processe was spawned by ETL for Aggregation and it wasn’t finished by the time the next one was spawned, half an hour later! When I checked, there were actually 32 java processes running (!) When I checked the PID’s, they indicated that half were Aggregation and half were Transfers - meaning the whole engine was continually playing “catch up” for the workload based on an 8-hour time span, all of the time… The CPU was at 100% the whole time, too… It was the worse case scenario and I was unfortunate enough to witness it! :-\

I understand what you mean about extracting data through direct access to the Database Tables and I agree that a level of tinkering (my post = “Business Rules”) may still have been applied, since the naming conventions indicate we are looking at Views - not Tables… :o

Still, I hope that izai is able to make some sense of our collective ramblings…

Tony

… Izai, mate… it is your turn now ;D

Ok, then I will over run you again ;D

That is quite interesting Tony. Never happened to me, although quite presumable from this product;) . Thanks for sharing. It had to be a quite old version. Hopefully Izai do not have the same version as you had.

Well lets wait what Izai will come with.

Cheers,

ecki.

guys, tq for your reply, really appreciate it.

ecki - to answer your question, rgrding null data, like I said earlier, I dont see any ‘null’ value, except ‘0’,
did you mean ‘null’ or ‘0’ ?

Tony - N_inbound in my posted .xls is original N_inbound, it was not a computed item

For your recommendations under item 1, 1a & 1b, below are our current configuration of our ETL. So I guess, the time for app & transferring should not conflict, am I right ?
FYI, we have 4 ETL_runtime running on the same server. As I’ve chked, all have the same configuration as on below :

[ETL_Agg_Only]
Command=java -Xmx256m -jar transform.jar -conf etl.properties -aggOnly -
Command0=loglevel=SQL-STATS:3,EVENT:3,COMMAND:3,START_AGG:3,AGG:9,INFO:9,WARNING:8,ERROR:9,ALL:0 -
Command1=maxLevelOfAgg=AGG_BY_YEAR -
Command2=updateStatsForLevel0Views -updateStatsForTablesAndIndexes
Priority=medium
StartTime=0:11+1:00

[ETL_Purging]
Command=java -jar purge.jar -conf purge.properties
Priority=medium
StartTime=20:25

[ETL_Tracking]
Command=java -jar conf_report.jar -conf etl.properties
Priority=medium
StartTime=00:23+1:00

[ETL_Trans_Only]
Command=java -Xmx256m -jar transform.jar -conf etl.properties -
Command0=loglevel=WAKE:3,START_WRITE_MON:3,DROP:9,WRITE:3,INFO:9,PURGE:9,WARNING:8,ERROR:9,ALL:0 -
Command1=maxNumberOfConnToDist=2
Priority=medium
StartTime=0:00+0:01

I’ll start looking the other possibilities as suggested in here.
Thanks

Question,
Are you extracting this from Brio or from CCPulse?
What version of ETL and ODS are you runnig?

I’m extracting from Brio
ETL_Runtime & Data Sourcer version 7.0.1

question:
can 2 report layouts using the same layout template but with different time profile ?

I do remember having a similar issue on a installation with that version were values for the group were twice the sum of the agents alone. I did an upgrade to 7.2 and problems got solved…maybe you are having a similar issue.

??? Someone else needs to verify/confirm this but it looks like your Transform is set to run every minute…? StartTime=0:00+0:01 ???

Also - yes, your versions are very much out of date - we’ve recently start moving towards 7.6… A lot of things have been fixed in the meantime…

Tony

But DataSourcer and ETL are only available in 7.2 versions.

guys;
last friday, I’d de-activated an existing template which pointed to ‘AGENT’ template - time profile was set 15mins
I’d created new report layout using existing ‘AGENT’ template and set the time profile to 30mins collection.
Reason : I need to refresh the 30 mins data collection since existing one having a problem (.xls posted)

But somehow, I’ve a problem to retrieve the data either using brio. or sql tool from v_agent_hour, in t_agent_hour the time key is exist and uptodate - it seems hang.
When I check in oracle alert long - we got ‘WARNING: aiowait timed out 1 times’
is this I/O problem or sthing related to my report layout - because the data are now mix 15mins & 30mins ?

Pls help.

izai,

We began to gather information around the circumstances of your original problem - we’ve never really got to the bottom of the problem. Now you have found a new problem whilst attempting to circumvent your original problem, by using a new Layout based on another Time Profile. The Layout is still using the core components which may actually be the problem.

If it were me, I would not attempt to build anything new (or as a replacement) until I was sure that the components parts (ETL/DS 7.0.1) were not the root cause. The first advice was for you to upgrade your components to at least version beyond 7.2.x - is it possible for you to do this..?

Tony

We will consider about the upgrade very soon.

At this point of time, i need to resolve this internally first.
As per yesterday, I managed to create new report layout using new layout template, and the data is now looks tally between hourly & 30mins.

I think the problem was might due to report layout conflict - multiple layout defined in DMA, but at least, with your help, I got an accurate data for 30mins report, eventually.

Thanks guys.