VSS Newsletter September 2004
In this monthly newsletter we inform you about recent and upcoming events in Vlamis Software and the Oracle business intelligence and data warehousing communities. We hope you find this information helpful.
To have your name removed from this mailing list, follow the directions at the bottom of this message.
Vlamis Software Solutions is a consulting company specializing in Oracle-related business intelligence and data warehouse solutions. Learn more about us at our web site at www.vlamis.com. Contact us at sales@vlamis.com or (816) 781-2880.
Analytic Workspace Tools
Oracle Development has created a set of tools to allow direct, command line, or programmatic management of database standard form metadata. If you have defined objects with Analytic Workspace Manager, only to find that you have to fill in the requisite metadata by hand, these tools are for you! With these tools you can:
- Create database standard form catalog objects.
- Create objects such as dimensions, cubes and measures.
- Set properties on existing objects, for example descriptive labels of elements of the logical model.
- Maintaining objects use to implement hierarchies after changes have been made to data values or parentage within a dimension (like the EDDE.HIERMNT program).
- Delete objects.
A note from the documentation:
"This tool set is provided 'as is'. It is not a component to the Oracle Database and is not supported by Oracle Worldwide Support. Program source is provided in clear text and can be modified."
If you are interested in receiving a copy of these tools, send us an e-mail at sales@vlamis.com.
Vlamis Software Solutions Expanding
Demand for people skilled in Oracle OLAP is building. We are looking to work with individuals and companies that have experience with Express or Oracle OLAP. If you are interested in working with one of the premier Oracle Business Intelligence consulting companies, send a cover letter and resume to Dan Vlamis at dvlamis@vlamis.com.
KCOUG
Dan Vlamis will be presenting "Using Oracle Business Intelligence Tools to Analyze Data Warehouses" on October 26th, 2004, at the KCOUG Full-Day Fall Conference. The abstract is available on our website's Papers and Presentations page.
For more information, visit the Kansas City Oracle Users Group.
Oracle OpenWorld
This year, Oracle OpenWorld will include the content from Oracle AppsWorld and will have sessions contributed by IOUG, ODTUG, and OAUG.
Of course, the highlight of the conference will be the three presentations being given by Vlamis Software Solutions! Here are some quotes from the evaluations of VSS presentations a recent conference:
"Best presentation I have attended-speaker is knowledgeable and explains the whole process simply"
"One of the best in the conference"
"Tons of demos in an hour-fantastic"
"Well communicated. Thorough knowledge of subject"
At Oracle OpenWorld, we will be presenting the following papers.
- Discoverer 10g or BI Beans - Which Is Right for You?
- Developing Advanced Thin Client Applications with BI Beans
- Using Oracle Business Intelligence Tools to Analyze Data Warehouses
Come join us for what promises to be a great conference. Details have not been posted on the Oracle website yet, but from what we hear, there will be a special emphasis on Business Intelligence this year. Register to attend Oracle OpenWorld by October 15 and receive a $500 early-bird discount. If you only want to attend one day, single day passes are available at nearly half-off the regular registration fee. Visit the Oracle OpenWorld website for details.
VSS Class on OLAP
VSS has been giving it's week-long Oracle OLAP class for over a year now. Participants learn:
- The structure of Oracle OLAP - how the various pieces fit together
- How to manage Oracle OLAP
- How to create ROLAP cubes
- How to create analytic workspaces from those ROLAP cubes
- How to enable AWs for use by BI Beans-based applications
- How to program in Oracle OLAP DML language and extend AWs
- How to use OLAP_TABLE to write SQL against data in AWs
- How to create BI Beans applications
In hands-on exercises, students build their own cubes, analytic workspaces, and BI Beans applications.
Sign up for our next class to be held in October or November in Kansas City, Missouri, or contact us today so we can bring the class to you. We can also present the class using your own data and customize it to your needs.
Dan's OLAP Tip: Minimize AW Size and Growth
My OLAP Tip in the prior newsletter addressed how changes are to AWs are written to TEMP tablespaces before being committed. This month, I address how to manage and explore the size of your AWs. You can investigate the size, free space in your AWs and the objects that occupy the most space in your AW with the commands shown in the following session log:
->" show the size of an AW in bytes:
->show joinchars('Size of AW = ' aw(pages) * aw(pagesize) ' bytes')
Size of AW = 32,365,696 bytes
->
->" show the number of bytes in an AW that are in the free page pool, ready to be used during the next update cycle
->show joinchars('Free AW Space = ' aw(freepages) * aw(pagesize) ' bytes')
Free AW Space = 10,338,816 bytes
->
->limit name to all
->sort name d obj(disksize)
->limit name keep first 20
->
->" Report out a ranked list of the largest 20 objects in your AW.
->" Typically, you will see your data variables at the top of this list, followed by any composite dimensions.
->
->report w 30 down name w 20 dec 0 heading 'Bytes' obj(disksize) * aw(pagesize)
NAME Bytes
------------------------------ --------------------
F.SALES 4,072,128
F.OLD_QUOTA 2,869,184
F.QUOTA 2,568,448
F.TREND 1,276,096
F.PCTVARIANCE 1,219,200
F.EXP 1,121,664
F.WINTER 950,976
SALES.C.CUBE 918,464
SALES.CUBE 918,464
STKPRICE.CUBE 788,416
F.LINEAR 747,776
F.FORECAST 642,112
F.NONLINEAR 642,112
F.BESTFIT 642,112
Y0.SHORTLABEL 130,048
Y0.LONGLABEL 130,048
A0.SHORTLABEL 130,048
A0.LONGLABEL 130,048
C1.SHORTLABEL 130,048
C1.LONGLABEL 130,048
->
If your analytic workspace is significantly larger than you would expect and you are running Oracle9i (this is different for Oracle10g), you may need to following this procedure (an example is shown):
1. attach your AW read/write
->aw attach data rw
2. create any old scalar variable with the define command and make a change to it
->define tempvar integer ->tempvar = 1
3. Update and commit
->update ->commit
4. Make another change to the AW
->tempvar = 2
5. Update and commit
->update ->commit
This has the effect of freeing up the largest number of pages changed since you last followed this procedure in your AW and can help control growth of your AWs. You can also control the growth of your AW by cutting down on the number of pages you change during an update. In other words, update more often.
There are other optimizations that can control the time it takes to update and the size of your analytic workspaces in Oracle9i and Oracle10g that are beyond the scope of this article. Let us know if these are issues you face in your implementations.
Look for other tips in upcoming VSS Newsletters or contact me at dvlamis@vlamis.com for more information.
Dan
Prior Newsletter Articles
We feature breaking news in the "Headline" section of our home page at www.vlamis.com. More news items can be found on our news page at www.vlamis.com/news. An archive of our newsletters going back through 2002 can be found at http://www.vlamis.com/newsletters.
Our Summer 2004 newsletter featured the following articles:
- Oracle OLAP 10g Update
- BI Beta
- Vlamis Software Solutions Expanding
- VSS Class on OLAP Scheduled for September
- Oracle World Combined with Appsworld
- ODTUG 2004
- Ohio Oracle User's Group
- Index of Previous VSS Newsletters Available
- Dan's OLAP Tip: Allocate Large Temp Tablespace and Update Often
Our May 2004 newsletter featured the following articles: