Using ETL to for unions on many templates

[victor]

Hi,

we are taking over a really crappy Genesys "consulting company" (I am using the word sardonically here) from Singapore that should be banned from ever touching Genesys ever again and I am working on fixing the mess they have left behind.

To make long story short, there are 10 different layouts for Agent Skill in DMA all built on slightly different templates. Some of the templates only collected data for few months, some of them still continue to collect data, but we only need several fields from them.

Their Brio report is using a VIEW that... are you ready... uses a UNION between all the views generated by all these templates, so we have like 70 unions ... Needless to say, this causes a simple Hyperion query take 5 minutes to just list a daily stat for 80 agents!!!

I try very hard not to seethe, but this is simply ridiculous...

Obviously, I want to use ETL to aggregate all these templates, thus removing the need to do runtime UNIONS. My gut instinct is to rename all templates layouts so they all have the same name, thus forcing ETL to throw a UNION between them automatically; however, I was wondering if there is an easier and less risky way of doing this... Client needs data collected by previous layouts, but we really cannot continue just using UNIONS like this... it is nothing short of disgrace...

When Genesys PS came in and saw it, he was like:" we paid them money for this?!!!"

Arghhh

[tony]

Hi Vic,

From what I can understand, using a UNION to create a "master" VIEW from all available VIEWS is where you are at - and wanting one VIEW with no UNIONs is where you want to be..?

You could try to understand how it was built and where/why the UNIONs exist but I think it would be easier and quicker to understand what you want to keep from previous active Layouts - and what is still needed (ongoing) produce yourself a "single" VIEW from those requirements, as you have suggested...

Not a technical answer but hopefully a pragmatic one...

Tony

[victor]

Hi, Tony,

I was thinking about getting rid of the previous layouts and migrating to new ones, but at the end of the day, it will be two or three years before client would no longer need to see data from the old ones, thus while it will pay off at the end, I am thinking of somehow using the inherent ETL capabilities to get things done.

[René]

Hi Vic,

I have an idea but not sure it's doable... Anyway, I was thinking about creating new template that will include all statistics required by client. Once you activate that template ETL will create set of new tables required by it. The second step would be manually copying historical values from existing 99 templates to new one. I think it can be done but would require a lot of work and man should be very careful not breaking references between the tables.

[quote]My gut instinct is to rename all templates layouts so they all have the same name, thus forcing ETL to throw a UNION between them automatically[/quote]
Based on my experience with CCA ETL, it won't work. I guess ETL will just throw an error and stops aggregation process...

R.

[tony]

Vic,

How long do they keep their Aggregate Levels..?  You might want to plan to replace each Aggregate Level, as it reaches it's natural end of life/Purge....

As an example:

NOAGG should be something you can apply straight away - in the short term, you would still need the UNIONs you have today and yet another UNION for the new View but, once the old stats are PURGED you can remove all of the UNIONs and continue with just one Layout/View.

It would be a sort of slow move away from all of the current UNIONs, to none at all....

All other Aggregate Levels could follow the same process..?

T

[b]EDIT:

Vic - you mentioned that it is taking 5 minutes to get a simple return from the Hyperion Report.  It may be that the Connection File (.OCE?) is pulling in all of the Tables and Views (even if it does not use them), prior to running any Queries within the Report.  I did some work on this for Brio but the concept should be the same for Hyperion, if it still uses an .OCE Connection File..

Basically, edit the .OCE so that it does not pre-empt any Query/Queries by pre-populating the Report with all of the Database Tables/Views.  Here is where I posted the Hint previously:

[url=http://www.sggu.com/smf/index.php/topic,2335.0.html]http://www.sggu.com/smf/index.php/topic,2335.0.html[/url]

T
[/b]

[victor]

I don't know how I forgot about OCE - I remember reading it a long time ago and testing it. Yes, this will be the very first thing I do. My next thing will be to force aggregation...

Regarding NOAGG: the idiots behind the design (I will be venting about them for quite some time...) when asked to prepare a half-an-hour report, are taking a NOAGG table for calculation of 30 min intervals...

I would love to get rid of NOAGG table; however, what do you have in mind?

[tony]

There's quite a few methods I can think of, off the top of my head.  Here's a couple;

...check purging rules of (e.g.) NOAGG...
...create a new Layout with Objects/Stats you actually need, ongoing from whatever AGG level is appropriate...
...create a UNION between the new Layout and old NOAGG Layout/Tables...
...remove all UNIONs once purging has removed Stats from NOAGG...

- Repeat for HOURLY, DAILY, WEEKLY, MONTHLY, QUARTERLY and ANNUALLY.

OR

...check purging rules of (e.g.) NOAGG...
...create a new Layout wtih the Object/Stats you need, ongoing...
...update Hyperion Reports to include new Query for data from the new Layout...
...remove Hyperion Report Query references to the NOAGG data/Layouts, once purging has removed all Stats from the NOAGG Tables...

- Repeat for HOURLY, DAILY, WEEKLY, MONTHLY, QUARTERLY and ANNUALLY.