Genesys CTI User Forum
Genesys CTI User Forum => Genesys CTI Technical Discussion => Topic started by: izai on April 02, 2009, 05:12:18 AM
-
We have 2 timeprofiles defined, 1 hour & 30 minutes aggregation.
However values in table R_1_STAT_RES (30 mins) were lesser than table R_2_STAT_RES(1 hour)
Anyone has any ideas what is the problem ?
-
??? Can you please be more clear? How are the jobs configured? What are those tables? Stats definitions? Logs? Lesser how?
-
I think they mean that, when you add up 2 x 30 mins, the Totals are less than the 1 x 1 hour...
If I had a penny for every time that someone asked that... ::)
izai - is that the problem?
Tony
-
when we generate report using these 2 tables,
for example, we got:
1 hour table : N_Inbound = 339,Total talk time = 03:34:17
30 mins table : N_Inbound = 182,Total talk time = 02:00:22
-
??? Is it me or I don't see nothing wrong here?
Hehe Tony, that is a common question for, how was it? lewsers? hehe
-
actually the figures were total for one whole day, it should tally at the end of the day, am i right ?
-
Sorry but don't get it.
-
ummm i d like 2 help, but dont understand the problem. ???
-
Hi Izai,
Could you please post the whole extract from both tables for only one object and spanning through two whole days? Put the extract in to excel spreadsheet.
Cheers,
ecki.
-
Attached are requested data from both tables.. hope anyone can help me..
-
Can you also post the definition of your stats and if they are TotalNumber or TotalNumberAdjusted / Action or Status? My guess is that your definition is as Action so you are watching the info in this way. Meaning, the stat is only counted when the event finished and not while occuring in the time profile selected.
-
I see the error you are doing. You are comparing two different time spans. In the hour table your time span is from 2pm to 11pm where as in the subhour table the time span is from 2pm to 6:30pm. If you summarized the exact time span from both tables, you would get correct numbers.
Cheers,
ecki.
-
yes you were right, let me check few things here again..
thanks
-
cavagnaro : here are the sample of our stat definition
[TotalNumberInboundCalls]
Category=TotalNumber
Description=Total inbound call
Formula=DCID
MainMask=CallInbound, HandlingInbound
Objects=Agent, GroupAgents, GroupPlaces, Place, RegDN
Subject=DNAction
[Total_Calls]
Category=TotalAdjustedNumber
Description=Total number of times agent completed handling a call
Formula=DCID
MainMask=CallUnknown, CallConsult, CallInternal, HandlingInternal, CallOutbound, HandlingOutbound, CallInbound, HandlingInbound, ASM_Outbound
Objects=Agent, GroupAgents, GroupPlaces, Place
Subject=AgentStatus
anything went wrong ?
-
ecki is right, doing the sum in the correct time span it does sum ok.
-
sorry everyone, i've wrongly extract from the other centre which the problem was caused by the filter in hyperion.
The attached should be the right data extract from the problematic site. :-)
-
That looks like as the data come from completely different planets. Could you pls export both report layouts from DMA in to xml files and post it here?
e.
-
Secondly, it looks like the 30 minutes report layout has some troubles. It is very strange that the intervals 10:00 and 1:30 has zeros in each stats except logging time. As the Stat server would lose connection to TServers for the whole collection interval. How does look like the CCA topology? Is the data from first table collected by the same Data sourcer as the data from second table?
-
Did you mean, Layout Templates? Coz I can't export Report Layout, the Export menu is disabled.
Attached is .zip contains 4 layout templates in .xml. FYI, our x-developer has created 4 layout templates in our DMA.
How should I know, which currenty is being used? All in active
-
[quote author=ecki link=topic=4047.msg17953#msg17953 date=1239096421]
Secondly, it looks like the 30 minutes report layout has some troubles. It is very strange that the intervals 10:00 and 1:30 has zeros in each stats except logging time. As the Stat server would lose connection to TServers for the whole collection interval. How does look like the CCA topology? Is the data from first table collected by the same Data sourcer as the data from second table?
[/quote]
only one Data Sourcer for this site.
-
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 [i]"Business Rules" [/i] being applied to the statistical definitions within Brio/Hyperion. The column headings both state [b]N_Inbound [/b] - but are they really what they say they are...? As you know it is very very easy to create a [i]Computed Item [/i] with various statistics added together and minus that from the degrees relative to the position of the sun and [i]call [/i] it [b]N_Inbound [/b] ... :)
I am with you on a couple of things;
[b]1. [/b] 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 [i]Transfer [/i] or the [i]Aggregation [/i] Java scripts are running concurrently and competing for resource.
[b]1a. [/b] 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 [i]Aggregations [/i] to run at 00:11+0:30 and [i]Transfers [/i] 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.
[b]1b. [/b] 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 [i]java -Xmx512m[/i] 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.
[b]2.[/b] I agree that it would also be beneficial for the OP (izai) to produce his Hourly reports [i]from [/i] 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 [b]32[/b] 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, [b]all of the time[/b].... 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 = "[i]Business Rules[/i]") 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 ?
-
[quote author=izai link=topic=4047.msg18028#msg18028 date=1239350489]
I'm extracting from Brio
ETL_Runtime & Data Sourcer version 7.0.1
[/quote]
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 [i]Transform[/i] is set to run every minute...? [b]StartTime=0:00+[u]0:01[/u][/b] ???
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
[quote author=izai link=topic=4047.msg18026#msg18026 date=1239338238]
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
[/quote]
-
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.