Rotate Attribute Table

Has anybody tried to create a rotated view or roteated material view on large llattrdata table for easier livereports against categories/attributes ? With the current llattrdata table structure, it is very difficult to write reports when the table is very large. A livereport with multiple self joins could easily make the DB unavailable for regular users. Any thoughts ?

Find more posts tagged with

Comments

eLink User

Message from via eLinkAre you using SQL server or oracle? If you are running SQL server, add' with (nolock)' and you will at least not make the dbunavailable as well as greatly speed up the report. What I have done forthings like this is to nightly build tables containing the appropriate data.A view would be fine too - I just have more experience with tables. Forinstance, we made great use of KUAFRIGHTSLIST in Livelink LiveReports butwith SP3, that table is no longer maintained. What I did was create ascript that will nightly build that table. Presently I am building a table to store Audit data in columns not rowswhich in effect is rotating the table. I am just using a bunch of temptables to build the data and once I have it, I will dump it in table. Ifyou use one transaction to both rename the old table and then add the newone, the table is never not available.Hope this helps. I am not a DBA - I just pretend and I have gotten by quitewell so far.-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: November 18, 2003 7:00 AMTo: eLink RecipientSubject: Rotate Attribute TableRotate Attribute TablePosted by Paul, Gigi on 11/18/2003 06:43 AMHas anybody tried to create a rotated view or roteated material view onlarge llattrdata table for easier livereports against categories/attributes? With the current llattrdata table structure, it is very difficult towrite reports when the table is very large. A livereport with multiple selfjoins could easily make the DB unavailable for regular users. Any thoughts ?[To reply to this thread, use your normal e-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

Len_Palmeri_(kmartadmin_-_(deleted))

I use Oracle. I use material view in oracle, which is actually a table. I was thinking instead of creating number of tables like this, create one master rotated table( If we can) and generate reports out of it. So the future maintenance is much lesser.