Oracle Openworld Directory Index

Mark Thompson
Braun Consulting

Oracle Open World 2001
December 4, 2001
Agenda
Introduction
1: Rules for Efficient Cube Design
2: Using Functions for Complex Modeling
3: Capturing OFA’s Instructions to Express
4: Viewing the Task Queue in latest-to-earliest order
5: Executing a Solve Definition from the command line
6: Defining and using a “User Type” attribute
7: Removing all data formatting from a document
8: Performing Task Queue maintenance
9: Performing an “invisible” auto-distribution of support objects
10: Auto-login without entering a password
Q&A
Agenda
Introduction
1: Rules for Efficient Cube Design
2: Using Functions for Complex Modeling
3: Capturing OFA’s Instructions to Express
4: Viewing the Task Queue in latest-to-earliest order
5: Executing a Solve Definition from the command line
6: Defining and using a “User Type” attribute
7: Removing all data formatting from a document
8: Performing Task Queue maintenance
9: Performing an “invisible” auto-distribution of support objects
10: Auto-login without entering a password
Q&A
(1) Rules for Efficient Cube Design
Put TIME first or last, depending on how you will process data
ACTUAL < LINE ORG PROD TIME >
BUDGET < TIME LINE ORG PROD >
Never put TIME in the composite
ACTUAL < LINE CMP < ORG PROD TIME >>
ACTUAL < LINE CMP < ORG PROD > TIME >
Never put all your dimensions in the composite
Never put the composite first in the dimension order
ACTUAL < CMP < ORG PROD > LINE TIME >
ACTUAL < LINE CMP < ORG PROD > TIME >
(1) Rules for Efficient Cube Design
Agenda
Introduction
1: Rules for Efficient Cube Design
2: Using Functions for Complex Modeling
3: Capturing OFA’s Instructions to Express
4: Viewing the Task Queue in latest-to-earliest order
5: Executing a Solve Definition from the command line
6: Defining and using a “User Type” attribute
7: Removing all data formatting from a document
8: Performing Task Queue maintenance
9: Performing an “invisible” auto-distribution of support objects
10: Auto-login without entering a password
Q&A
(2) Using Functions for Complex Modeling
Here’s a simple example:

Services Revenue = Input
Software Revenue = Input
Total Revenue = Services Revenue + Software Revenue
Salary Expense = Input
Travel Expense = Input
Rent Expense = Input
Total Expense = Salary Expense + Travel Expense + Rent Expense
Gross Profit = Total Revenue – Total Expense
Tax Rate = Input
Taxes = Gross Profit * Tax Rate
Net Profit = Gross Profit – Taxes
(2) Using Functions for Complex Modeling
(2) Using Functions for Complex Modeling
(2) Using Functions for Complex Modeling
(2) Using Functions for Complex Modeling
(2) Using Functions for Complex Modeling
Agenda
Introduction
1: Rules for Efficient Cube Design
2: Using Functions for Complex Modeling
3: Capturing OFA’s Instructions to Express
4: Viewing the Task Queue in latest-to-earliest order
5: Executing a Solve Definition from the command line
6: Defining and using a “User Type” attribute
7: Removing all data formatting from a document
8: Performing Task Queue maintenance
9: Performing an “invisible” auto-distribution of support objects
10: Auto-login without entering a password
Q&A
(3) Capturing OFA’s Instructions to Express
OFA is a Visual Basic application issuing Express SPL instructions to the underlying databases
To reproduce an OFA front-end function with a program, these instructions must simply be captured
Open the Express Monitor within an OFA workstation
Type: dbgoutfile /path/filename.extension (e.g. dbgoutfile /olap_home/list.txt)
Type: ifcopy = yes
Leave Express Monitor open
Perform the OFA function you wish to capture
Return to the Express Monitor window
Type: dbgoutfile eof
(3) Capturing OFA’s Instructions to Express
(3) Capturing OFA’s Instructions to Express
Agenda
Introduction
1: Rules for Efficient Cube Design
2: Using Functions for Complex Modeling
3: Capturing OFA’s Instructions to Express
4: Viewing the Task Queue in latest-to-earliest order
5: Executing a Solve Definition from the command line
6: Defining and using a “User Type” attribute
7: Removing all data formatting from a document
8: Performing Task Queue maintenance
9: Performing an “invisible” auto-distribution of support objects
10: Auto-login without entering a password
Q&A
(4) Viewing the Task Queue in latest-to-earliest order
(4) Viewing the Task Queue in latest-to-earliest order
(4) Viewing the Task Queue in latest-to-earliest order
Agenda
Introduction
1: Rules for Efficient Cube Design
2: Using Functions for Complex Modeling
3: Capturing OFA’s Instructions to Express
4: Viewing the Task Queue in latest-to-earliest order
5: Executing a Solve Definition from the command line
6: Defining and using a “User Type” attribute
7: Removing all data formatting from a document
8: Performing Task Queue maintenance
9: Performing an “invisible” auto-distribution of support objects
10: Auto-login without entering a password
Q&A
(5) Executing a Solve Definition from command line
(5) Executing a Solve Definition from command line
Agenda
Introduction
1: Rules for Efficient Cube Design
2: Using Functions for Complex Modeling
3: Capturing OFA’s Instructions to Express
4: Viewing the Task Queue in latest-to-earliest order
5: Executing a Solve Definition from the command line
6: Defining and using a “User Type” attribute
7: Removing all data formatting from a document
8: Performing Task Queue maintenance
9: Performing an “invisible” auto-distribution of support objects
10: Auto-login without entering a password
Q&A
(6) Defining and using a User Type attribute
Define a new dimension: UserType (UTYPE), with four values (Budget, Analyst, DBA, External)

Define and populate the “User Type” attribute. One-to-many, base dimension = User, Grouping Dimension = “UserType”

You can now select Users with the “Attribute” tool
Agenda
Introduction
1: Rules for Efficient Cube Design
2: Using Functions for Complex Modeling
3: Capturing OFA’s Instructions to Express
4: Viewing the Task Queue in latest-to-earliest order
5: Executing a Solve Definition from the command line
6: Defining and using a “User Type” attribute
7: Removing all data formatting from a document
8: Performing Task Queue maintenance
9: Performing an “invisible” auto-distribution of support objects
10: Auto-login without entering a password
Q&A
(7) Removing data formats from a document
My formats are conflicting, and I can’t fix them.

Formats are held in CO.CATALOG, in the EXCEPT.TEXT and FORMAT properties

The syntax is CALL CLEAN.DOC.FMT(‘DI.xxxxxx’), where the argument is a value of the DI.ENTRY dimension
(7) Removing data formats from a document
(7) Removing data formats from a document
Agenda
Introduction
1: Rules for Efficient Cube Design
2: Using Functions for Complex Modeling
3: Capturing OFA’s Instructions to Express
4: Viewing the Task Queue in latest-to-earliest order
5: Executing a Solve Definition from the command line
6: Defining and using a “User Type” attribute
7: Removing all data formatting from a document
8: Performing Task Queue maintenance
9: Performing an “invisible” auto-distribution of support objects
10: Auto-login without entering a password
Q&A
(8) Performing Task Queue Maintenance
OFASTASK database, read-write

Remove a pending distribution from a user
tk.l.dist.a = Last Distribution Assigned
tk.l.dist.r = Last Distribution Received

(8) Performing Task Queue Maintenance
See date and time of submitted tasks
Tk.catalog(tk.prop ‘TIME.SB’) is an integer number of seconds

(8) Performing Task Queue Maintenance
Cancel a distribution entirely










Task Processor must not be running; if running in background or in foreground of TPM workstation, the shared will have already been altered


Agenda
Introduction
1: Rules for Efficient Cube Design
2: Using Functions for Complex Modeling
3: Capturing OFA’s Instructions to Express
4: Viewing the Task Queue in latest-to-earliest order
5: Executing a Solve Definition from the command line
6: Defining and using a “User Type” attribute
7: Removing all data formatting from a document
8: Performing Task Queue maintenance
9: Performing an “invisible” distribution of support objects
10: Auto-login without entering a password
Q&A
(9) Invisible distributions of support FDIs
Examples: Number of months to lag for YTD or PY

Single-dimension FDIs, formulas, one-to-many attributes

Define normally in OFA, populate normally (worksheet or attribute)

Manually define prefix.BW (if not yet defined)

Add prefix.BW and support FDIs to
DM.CATALOG(DM.PROP ‘VARTYPE’)

Dm.catalog(dm.prop ‘VARTYPE’ dm.entry ‘FTIME’) = Joinlines(‘FTIME.BW’,’YTDLAG.FTIME’)

Distribute dimension values normally

Do NOT distribute the FDI with Distribute Structure
Agenda
Introduction
1: Rules for Efficient Cube Design
2: Using Functions for Complex Modeling
3: Capturing OFA’s Instructions to Express
4: Viewing the Task Queue in latest-to-earliest order
5: Executing a Solve Definition from the command line
6: Defining and using a “User Type” attribute
7: Removing all data formatting from a document
8: Performing Task Queue maintenance
9: Performing an “invisible” auto-distribution of support objects
10: Auto-login without entering a password
Q&A
(10) Auto-login without entering a password
(11) Other Resources
Any Oracle customer can register for MetaLink (http://metalink.oracle.com)
Discussion forums
Technical notes on OFA meta data, hooks to customize OFA, database design guide
Product documentation
Bug database
Business Intelligence Group Special Interest Group (BIG SIG) of the IOUG-A
Discussion forum
Listserve
Regular newsletter and conference calls
Questions?

Ten Things Every OFA Developer Should Know

Mark Thompson
Braun Consulting

Oracle Open World 2001
December 4, 2001