12/31/09
Hyperion Web Analysis
12/27/09
Java Tips
- Inheritance is the mechanism that a sub class inherit the behavior and attributes of a super class.
- An interface is a clollection of methods that indicate a class has some behavior in addition to what it inherits from its superclasses. The methods included in an interface do not define this behavior, that task is left for the classes that implement the inteface. To use an interface, include implements keyword as part of your class definition
public class AnimationSign extends javax.swing.JApplet
implements Runable {...} - Overriding: the method created in the sub class has the same nane as the super class, this is called overrriding.
- Package is a way of grouping related classes and interfaces. Such as: java.awt.*,java.lang.*.
- Literal: A literal is any number,text,or other information that directly represents a value. include: number literal(1,2,3), boolean literal(true,false),character literal(\n,\t),string literal("sss").
- A reference is an address that indicates where an object's variable and methods are stored.
- Casting between primitive types enables you to convert the value of one type to another primitive type. such as from int to float. Sample: (int)(x/y),(classname)object,(typename)value.
- To find out what an object's class is: String name = key.getClass().getName();
- instanceof operator return true or false: "Texta" instanceof String //true
- this keyword refer to the current object: t = this.x;
- Overloading: Methods with the same name but with different number of arguments or/and data type.
- Constructor mrthod is a method that is called on an object when it is created. The method has the same name as the class. this(arg1,arg2,arg3) can be used to call a constructor method. Constructor can be overloaded too.
- Use the super keyword to call the original method from inside a method definition:
void mymethod(String a, String b) {super.mymethod(a,b);} - finalizer method is called just before the object is collected for garbage and has its memory reclaimed.
protected void finalize() throws Throwable {super.finalize();} Finalizer method is used for optimizing the removal of an object. In most cases, you don't need to use finalize(0 at all - Applet runs on browser that support Java.
- Painting is how an applet displays something onscreen. repaint();
- Java archive is a collection of Java classes and other files packaged into a single file.
jar cf Animated.jar *.class *.gif - Swing is a part of the Java Foundation Classes library, provide a way to offer a graphical user interface. The swing is an extension of awt. javax.swing, javax.swing.JFrame,javax.swing.JWindow. Two other packages that are used with graphical user interface programming are java.awt and java.awt.event
- Threads are parts od program that are set up to run on their own while the rest of program does something else. This is called multitasking because the program can handle more than one tasks simultaneously.
- final class cannot be subclassed
- You can define a class inside a class, as if it were a method or a variable.These classes are called inner classes. Inner class are invisible to all other classes,no need to worry about the name conflicts between it and other classes.
public class Inner extends javax.swing.Applet {...} - Exception catch:
try{}
catch (IOExceptio e){}
catch (ClassNotFoundException e){}
catch (InterrptedException e){}
finally{
} - finnally statement is actually useful outside exceptions,you can aalso use it to execute cleanup code after s return,a break,or a continue inside loops.
- The throw clause: public boolean myMethod(int x,int y) throws AnException {}
- A digital signiture is an encriped file that accompany a program indicating exactly from whom the files come from. The document that represents this digital signiture is called a certificate.
- java.io: FileInputStream and FileOutputStream - Byte streams stored in files on disk,CD-ROM,or other storage devices
- java.io: DataInputStream and DataOutputStream - A filtered byte stream from which data such as integers and floating-point numbers can be read
- Java handles access to external data via the use of a class of objects called streams. A stream is an object that carries data from one place to another.
- Object serialization: the capability to reaad and write an object using steams.
- Objects can be serialized to disk on a single machine or can be serialized across a network.
- Persistence : the capability of an object to exist and function outside the program that created it. Serialization enables object persistence.
FileOutputStream disk = new FileOutputStream("SavedObject.dat")
ObjectOutputStream obj = new ObjectOutputStream(disk) - RMI(remote method invocation): creates Java application that can talk to other Java application over a network. RMI allows an application to call methods and access variables inside another application, which might be running in a different Java Envirnment or different OS altogether, and pass objects back and forth over a network connection.
java.rmi.server, java.rmi.registry, java.rmi.dgc - Before code with RMI, use rmic command-line program to generate the Stub and Skeleton layers so that RMI can actually work between the two sides of the process
- RPC(Remote Procedure calls): sends only procedure calls over the wire, while RMI actually pass the whole object.
- Socket: Java provides the Socket and ServerSocket classes as an abstraction of standard TCP socket program techniques. The Socket class provides s client side socket interface similar to standard UNIX socket.
Socket connection = new Socket(hostName, portNum);
connection.setSoTimeOut(50000);
BufferedInputStream bis = new BufferedInputStream(connection.getInputStream());
DataInputStream in = new DataInputStream(bis);
BufferedOutputStream bos = new BufferedOutputStream(connection.getOutputStream());
DataOutputStream out = new DataOutputStream(bos);
- Sever Side sockets work similar to client side sockets, with the exception of the accept() method. The accept() method accepts a connection from that client..
- JavaBeans is an architecture and platform independent set of classes for crreating and using Java software components. You can drop a JavaBean component directly into an application without writing any code.
- Java Database connectiion, 2 ways: JDBC-ODBC bridge, or JDBC drivers
JDBC-ODBC bridge allows JDBC drivers to be used as ODBC drivers by converting JDBC method calls into ODBC function calls. The next is a sample for JDBC-ODBC:
Connection payday = DriverManager.getConnection("jdbc.odbc:Payroll","Doc","Notnow"); - The next is a sample for JDBC:
Connection payday = DriverManager.getConnection("jdbc:JDataConnect://localhost:1150/Presidents","",""); - Java Data Structure
- BitSet class implements a group of bits or flags that can be set an cleared individually. class Somebits {
public static final int READABLE = 0;
public static final int WRITABLE = 1;
public static final int STREAMABLE = 2;
public static final int FLEXIBLE = 3;
}
- Vectr class implements an extendable array of objects
Vector v = new Vector();
v.add("Bob");
- Stacks are a classic data structure used, last in, first out
Stack s = new Stack();
s.push("One");
s.push("two");
String s1 = (String)s.pop();
- Map interface defines a framework for implementing a basic key-mapped data structure.
- Hashtable class is derived from dictionary, implements the Map interface, and provides a complete implementation of a key-mapped data structure.
12/26/09
Hyperion Report System Migration
Migration involves migrating information from the source system to Oracle's Hyperion® Shared Services and the Oracle's Hyperion Reporting and Analysis Repository.
Migration involves: users, groups, roles, and repository content. The source systems include:
. Brio Portal 7.0.5 or later
. BrioONE (Brio Portal 7.0.5 or later and OnDemand Server 6.6.4 or later)
. Brio Intelligence Server (OnDemand Server and Broadcast Server 6.6.4 or later)
. Hyperion Analyzer 6.5, 7.0.1, 7.2, or 7.2.1
. Hyperion Performance Suite 8.x
. Hyperion Reports 7.0.x or 7.2
-----------------------------------------------
Prerequisites for Using the Migration Utility:
. Administrator permission on both source and target system
. Disable the Harvester service.
. If your target system is Oracle, set the NLS_LENGTH_SEMANTICS parameter to char.
nls_length_semantics=char
. For systems using X-Windows on UNIX, start vncserver so that it uses (at a minimum) HiColor.
For example, vncserver -depth 15 or vncserver -depth 16.
. If the source is Brio Portal or Hyperion Performance Suite 8: turn off the services on the source and target systems
. If the source is Brio Intelligence: 1. Turn off the services on the source system. (The services on the target system should stay running.). 2. For UNIX systems, add the BQ_FONT_PATH to the services.sh file located in the/BrioPlatform/bin directory and restart the services. The BQ_FONT_PATH environment variable is needed by the Interactive Reporting Service on the target system to ensure that BQY jobs and documents are rendered properly in the new environment.
------------------------------------------------------------
Other Considerations:
. Special Symbols in Users and Groups
. Invalid Characters in Workspace
. SmartCuts - You need reconfigure the SmartCut in source so that the characters are valid in new Hyperion Workspace.
------------------------------------------------------------
Run Migration Wizard and follow the steps:
About Migrating from Brio Portal, steps:
● Defining the Location of the Portal Repository Database
● Identifying Portal Groups with Circular References
● Selecting the Objects to Migrate
● Defining Reporting and Analysis Repository Database Information
● Defining the Location of the Reporting and Analysis Repository Database
● Defining the Location of the Shared Services Server
● Defining User/Group Names, Descriptions and Passwords
● Reviewing User Names
● Reviewing Group Names
● Mapping Hosts
● Defining Additional Migration Options
● Running the Migration
-----------------------------------------------
Other options:
1. LDAP and Shared Service backup
2. Export the security file to external file and modification and load back
3. Source and target run in different computer servers.
4. The migration should not affect the original content in source server normally
5. Migration to a test server first before production migration
6. UNIT test include: 1). general functionality test, such as: can we see the report from workspace? 2). User security test 3). Repository object test, such as the report job, web folder 4).Report modification because of version difference.
Migration involves: users, groups, roles, and repository content. The source systems include:
. Brio Portal 7.0.5 or later
. BrioONE (Brio Portal 7.0.5 or later and OnDemand Server 6.6.4 or later)
. Brio Intelligence Server (OnDemand Server and Broadcast Server 6.6.4 or later)
. Hyperion Analyzer 6.5, 7.0.1, 7.2, or 7.2.1
. Hyperion Performance Suite 8.x
. Hyperion Reports 7.0.x or 7.2
-----------------------------------------------
Prerequisites for Using the Migration Utility:
. Administrator permission on both source and target system
. Disable the Harvester service.
. If your target system is Oracle, set the NLS_LENGTH_SEMANTICS parameter to char.
nls_length_semantics=char
. For systems using X-Windows on UNIX, start vncserver so that it uses (at a minimum) HiColor.
For example, vncserver -depth 15 or vncserver -depth 16.
. If the source is Brio Portal or Hyperion Performance Suite 8: turn off the services on the source and target systems
. If the source is Brio Intelligence: 1. Turn off the services on the source system. (The services on the target system should stay running.). 2. For UNIX systems, add the BQ_FONT_PATH to the services.sh file located in the
------------------------------------------------------------
Other Considerations:
. Special Symbols in Users and Groups
. Invalid Characters in Workspace
. SmartCuts - You need reconfigure the SmartCut in source so that the characters are valid in new Hyperion Workspace.
------------------------------------------------------------
Run Migration Wizard and follow the steps:
About Migrating from Brio Portal, steps:
● Defining the Location of the Portal Repository Database
● Identifying Portal Groups with Circular References
● Selecting the Objects to Migrate
● Defining Reporting and Analysis Repository Database Information
● Defining the Location of the Reporting and Analysis Repository Database
● Defining the Location of the Shared Services Server
● Defining User/Group Names, Descriptions and Passwords
● Reviewing User Names
● Reviewing Group Names
● Mapping Hosts
● Defining Additional Migration Options
● Running the Migration
-----------------------------------------------
Other options:
1. LDAP and Shared Service backup
2. Export the security file to external file and modification and load back
3. Source and target run in different computer servers.
4. The migration should not affect the original content in source server normally
5. Migration to a test server first before production migration
6. UNIT test include: 1). general functionality test, such as: can we see the report from workspace? 2). User security test 3). Repository object test, such as the report job, web folder 4).Report modification because of version difference.
Essbase Migration
EAM provides a Wizard for migrating Essbase applications, databases, and user/group security from one server to another. For example, you might migrate an application from a development to test server or from a production to a test server to try out a new release of Essbase software. With Migration Wizard, you can migrate application and database objects and security without leaving the Administration Services console.
The following points apply to applications migrated using Migration Wizard:
1. Only application and database objects and user/group security are migrated. No data is transferred.
2. Objects and security on the source server are unchanged. Migration Wizard makes copies on the target server.
3. Source and target servers may be on different operating systems. For example, you can migrate an application from a Windows to a Unix server.
The data can be loded to the target after the running of Migration Wizard.
Prerequisites:
Before starting this tutorial, you should:
1. Have both an Essbase Release 7.1.0 server and an Essbase Release 9.3.1 server installed and running.
2. Utilize Essbase native security on both servers.
3. Have an Essbase application on the Release 7.1.0 server available for migrating. This tutorial migrates an application called Mexcorp.
The following points apply to applications migrated using Migration Wizard:
1. Only application and database objects and user/group security are migrated. No data is transferred.
2. Objects and security on the source server are unchanged. Migration Wizard makes copies on the target server.
3. Source and target servers may be on different operating systems. For example, you can migrate an application from a Windows to a Unix server.
The data can be loded to the target after the running of Migration Wizard.
Prerequisites:
Before starting this tutorial, you should:
1. Have both an Essbase Release 7.1.0 server and an Essbase Release 9.3.1 server installed and running.
2. Utilize Essbase native security on both servers.
3. Have an Essbase application on the Release 7.1.0 server available for migrating. This tutorial migrates an application called Mexcorp.
12/25/09
Embedded Java API - Hyperion Application Builder
The next picture is a sample that is developed by Hyperion Application Builder:
The Embedded Java Application Programming Interface (JAPI) feature is a simple deployment of the JAPI solution. Enabling this feature will provide the Application Builder for .NET applications to establish connection with the Essbase servers even when Provider Services is not installed, see Figure. The connection protocol between the Application Builder for .NET
applications and the Essbase servers is TCP/IP or HTTPS.
The embedded JAPI deployment for Application Builder for .NET includes .jar files and property files. When you enable the embedded JAPI feature, the Application Programming Interface (API) client will include these .jar files and property files in the Application Builder for .NET application.
Prerequisites
Some of the current Java API customers may require only Java API functionality, and will not require a full installation of the Provider Services product with the extended functionality such as Clustering, High Availability, Connection Pooling, and XMLA provider.
Complete these prerequisites to enable this feature:
● %APS_HOME% should contain both \bin and \data folders
● %APS_HOME%\bin should contain essbase.properties file
● Update the Habnet.properties file.
In \HABNET_HOME\products\Essbase\habnet\Server\lib\, In Habnet.properties file include value EDSUrl=Embedded.
● From Essbase server installation location: HABNET_HOME\products\Essbase\habnet\server\lib copy the Essbase.properties file to %APS_HOME%\bin. Enabling Embedded JAPI
The embedded JAPI feature is available when you install this release of Application Builder for .NET. However, to enable this feature, you must do some updates to the Habnet.properties file. With this feature update to the Application Builder for .NET release, to establish connection between Application Builder for .NET applications and Essbase has a option of using either Provider Services or embedded JAPI depending on the requirement.
This utility is run based on the following assumptions:
● The password information of the Essbase servers are read without any encryption.
● The user ID and password information is used for authentication and this utility will update only the server name information in the domain.db file.
● The utility will read the environment variables APS_HOME and ABNET_HOME to get the location information of the files domain.db and essbase_servers.xml.
➤ To enable the embedded JAPI utility:
1 In the Habnet.properties file, for EDSUrl provide the value Embedded.
2 Save and close the Habnet.properties file.
➤ To revert to Provider Services:
1 In the Habnet.properties file, for EDSUrl provide the value EDSUrl = http://
name>:/aps/JAPI.
2 Save and close the Habnet.properties file.
Managing the Embedded JAPI Feature
This feature offers a utility that gathers the Essbase server information (server name, user ID,and password) from the XML template file essbase_servers.xml and updates the domain.db file. The user has a privilege to add the server information in essbase_servers.xml to "add","delete" and "None" actions to update the domain.db file.
The essbase_servers.xml file must include these server information:
"password">
"password">
In essbase_servers.xml file the action attribute can have one of the following values:
Add, Delete and None. Add value adds the specified server information to domain.db file.
Delete value deletes the specified server information from the domain.db file. None value means no action performed on the domain.db file. The Application Builder for .NET applications will use this information that is present in the domain.db file to communicate with the Essbase servers. The domain.db file is present in the folder APS_HOME\data.
Running the Embedded JAPI Utility
As prerequisite, the essbase_servers.xml file must be present in the location \HABNET_HOME\products\Essbase\habnet\Server\lib. Once this utility is run, the domain.db file is created in the location APS_HOME\data.
You can run the embedded JAPI utility from the Application Builder for .NET installation directories. The embedded JAPI utilty will update the server information in the domain.db file.
➤ To run the embedded JAPI utility and update the XML template file:
1 For Windows: from %HABNET_HOME%\products\Essbase\habnet\Server\bin\, run command
ESSJapiUtil.bat.
2 For Unix: from $HABNET_HOME/products/Essbase/habnet/Server/bin/, run command
ESSJapiUtil.sh.
You can register or deregister the Essbase servers with or without resetting the password or exporting the Essbase server information.
➤ To register the Essbase servers and reset the Essbase server passwords:
1 For Windows: from %HABNET_HOME%\products\Essbase\habnet\Server\bin\, run command
ESSJapiUtil.bat register.
2 For Unix: from $HABNET_HOME/products/Essbase/habnet/Server/bin/, run command
ESSJapiUtil.sh register.
➤ To export the Essbase server information (This command will not record the password information):
1 In \HABNET_HOME\products\Essbase\habnet\Server\lib create the
essbase_servers_export.xml file.
2 For Windows: from %HABNET_HOME%\products\Essbase\habnet\Server\bin\, run command
ESSJapiUtil.bat export.
3 For Unix: from $HABNET_HOME/products/Essbase/habnet/Server/bin/, run command
ESSJapiUtil.sh export.
➤ To register Essbase servers without passwords reset:
1 For Windows: from %HABNET_HOME%\products\Essbase\habnet\Server\bin\, run command
ESSJapiUtil.bat registernoreset.
2 For Unix: from $HABNET_HOME/products/Essbase/habnet/Server/bin/, run command
ESSJapiUtil.sh registernoreset.
applications and the Essbase servers is TCP/IP or HTTPS.
The embedded JAPI deployment for Application Builder for .NET includes .jar files and property files. When you enable the embedded JAPI feature, the Application Programming Interface (API) client will include these .jar files and property files in the Application Builder for .NET application.
Prerequisites
Some of the current Java API customers may require only Java API functionality, and will not require a full installation of the Provider Services product with the extended functionality such as Clustering, High Availability, Connection Pooling, and XMLA provider.
Complete these prerequisites to enable this feature:
● %APS_HOME% should contain both \bin and \data folders
● %APS_HOME%\bin should contain essbase.properties file
● Update the Habnet.properties file.
In \HABNET_HOME\products\Essbase\habnet\Server\lib\, In Habnet.properties file include value EDSUrl=Embedded.
● From Essbase server installation location: HABNET_HOME\products\Essbase\habnet\server\lib copy the Essbase.properties file to %APS_HOME%\bin. Enabling Embedded JAPI
The embedded JAPI feature is available when you install this release of Application Builder for .NET. However, to enable this feature, you must do some updates to the Habnet.properties file. With this feature update to the Application Builder for .NET release, to establish connection between Application Builder for .NET applications and Essbase has a option of using either Provider Services or embedded JAPI depending on the requirement.
This utility is run based on the following assumptions:
● The password information of the Essbase servers are read without any encryption.
● The user ID and password information is used for authentication and this utility will update only the server name information in the domain.db file.
● The utility will read the environment variables APS_HOME and ABNET_HOME to get the location information of the files domain.db and essbase_servers.xml.
➤ To enable the embedded JAPI utility:
1 In the Habnet.properties file, for EDSUrl provide the value Embedded.
2 Save and close the Habnet.properties file.
➤ To revert to Provider Services:
1 In the Habnet.properties file, for EDSUrl provide the value EDSUrl = http://
name>:
2 Save and close the Habnet.properties file.
Managing the Embedded JAPI Feature
This feature offers a utility that gathers the Essbase server information (server name, user ID,and password) from the XML template file essbase_servers.xml and updates the domain.db file. The user has a privilege to add the server information in essbase_servers.xml to "add","delete" and "None" actions to update the domain.db file.
The essbase_servers.xml file must include these server information:
"password">
"password">
In essbase_servers.xml file the action attribute can have one of the following values:
Add, Delete and None. Add value adds the specified server information to domain.db file.
Delete value deletes the specified server information from the domain.db file. None value means no action performed on the domain.db file. The Application Builder for .NET applications will use this information that is present in the domain.db file to communicate with the Essbase servers. The domain.db file is present in the folder APS_HOME\data.
Running the Embedded JAPI Utility
As prerequisite, the essbase_servers.xml file must be present in the location \HABNET_HOME\products\Essbase\habnet\Server\lib. Once this utility is run, the domain.db file is created in the location APS_HOME\data.
You can run the embedded JAPI utility from the Application Builder for .NET installation directories. The embedded JAPI utilty will update the server information in the domain.db file.
➤ To run the embedded JAPI utility and update the XML template file:
1 For Windows: from %HABNET_HOME%\products\Essbase\habnet\Server\bin\, run command
ESSJapiUtil.bat.
2 For Unix: from $HABNET_HOME/products/Essbase/habnet/Server/bin/, run command
ESSJapiUtil.sh.
You can register or deregister the Essbase servers with or without resetting the password or exporting the Essbase server information.
➤ To register the Essbase servers and reset the Essbase server passwords:
1 For Windows: from %HABNET_HOME%\products\Essbase\habnet\Server\bin\, run command
ESSJapiUtil.bat register.
2 For Unix: from $HABNET_HOME/products/Essbase/habnet/Server/bin/, run command
ESSJapiUtil.sh register.
➤ To export the Essbase server information (This command will not record the password information):
1 In \HABNET_HOME\products\Essbase\habnet\Server\lib create the
essbase_servers_export.xml file.
2 For Windows: from %HABNET_HOME%\products\Essbase\habnet\Server\bin\, run command
ESSJapiUtil.bat export.
3 For Unix: from $HABNET_HOME/products/Essbase/habnet/Server/bin/, run command
ESSJapiUtil.sh export.
➤ To register Essbase servers without passwords reset:
1 For Windows: from %HABNET_HOME%\products\Essbase\habnet\Server\bin\, run command
ESSJapiUtil.bat registernoreset.
2 For Unix: from $HABNET_HOME/products/Essbase/habnet/Server/bin/, run command
ESSJapiUtil.sh registernoreset.
Application Builder for .NET
Application Builder for .NET provides a comprehensive set of OLAP-aware classes for data navigation, selection, reporting, and visualization to assist you in building custom analytical applications. These controls provide you with the tools to quickly create and deploy applications in stand-alone, client-server, Web, or distributed processing environments. Application Builder for .NET is a component of the Oracle Enterprise Performance Management system that provides an application development workbench for companies wanting to use the Microsoft .NET Framework to create tailored business performance management solutions. Application Builder for .NET includes the following key features:
● Drag and Drop components for creating OLAP aware applications for Web and Windows using MS Visual Studio.Net
● .NET Framework compatibility
● Web Service -based architecture (SOAP)
Application Builder for .NET provides the following functionality:
● Base classes. These classes contain basic OLAP functionality and encapsulate functionality necessary to connect to and perform operations on Essbase applications and databases.
● Dialog components. These classes encapsulate the functionality of Essbase dialog boxes, including the sign-on and member selection dialog boxes.
To develop Application Builder for .NET applications within Visual Studio for .NET, perform the following steps:
● Install and configure Microsoft Visual Studio for .NET.
● Install Application Builder for .NET and note which directory contains theApplication Builder for .NET assemblies.
● If you are developing an ASP .NET (Active Server Pages) application, install or ensure access to an instance of Microsoft Internet Information Server (IIS).
● If you are developing applications using Microsoft Office Web Components (OWC11), you may need to add Microsoft Office Primary Interop Assemblies and install additional assemblies available from http://www.microsoft.com/.
● Install or ensure access to an Essbase server and application.
This guide assumes that you will be using the Sample application and Basic database installed with Essbase.
● Ensure that the Application Builder for .NET .WAR file has been applied to your Web application server.
● Ensure that Oracle Hyperion Provider Services™ is running, and that your application server can access it.
● Drag and Drop components for creating OLAP aware applications for Web and Windows using MS Visual Studio.Net
● .NET Framework compatibility
● Web Service -based architecture (SOAP)
Application Builder for .NET provides the following functionality:
● Base classes. These classes contain basic OLAP functionality and encapsulate functionality necessary to connect to and perform operations on Essbase applications and databases.
● Dialog components. These classes encapsulate the functionality of Essbase dialog boxes, including the sign-on and member selection dialog boxes.
To develop Application Builder for .NET applications within Visual Studio for .NET, perform the following steps:
● Install and configure Microsoft Visual Studio for .NET.
● Install Application Builder for .NET and note which directory contains theApplication Builder for .NET assemblies.
● If you are developing an ASP .NET (Active Server Pages) application, install or ensure access to an instance of Microsoft Internet Information Server (IIS).
● If you are developing applications using Microsoft Office Web Components (OWC11), you may need to add Microsoft Office Primary Interop Assemblies and install additional assemblies available from http://www.microsoft.com/.
● Install or ensure access to an Essbase server and application.
This guide assumes that you will be using the Sample application and Basic database installed with Essbase.
● Ensure that the Application Builder for .NET .WAR file has been applied to your Web application server.
● Ensure that Oracle Hyperion Provider Services™ is running, and that your application server can access it.
Essbase Studio
Essbase Studio only exists in version 11, it is derived from earlier Essbase Integration Service, but it is more powerful than EIS. It is used to design the Essbase outline from RLDB data source and flat file.
Also, it has a functonality that can migrate EIS catalog to Essbase Studio Catalog. Same as EIS, a common metadata repository, or catalog, captures all metadata related to all Essbase applications built in the enterprise and allows the reuse of metadata at the lowest level of granularity. The catalog gives Essbase Studio knowledge of the common metadata that is shared across the various applications enterprise-wide.The metadata catalog is still saved in RLDB, it describes the metadata repository.
The Source Navigator, displayed by default in the right pane of the Essbase Studio Console, has two tabs, the Data Sources tab and the Minischemas tab.
. Data Sources tab—Lists the physical data sources to which you have created connections. You can also launch the Connection Wizard from this tab, where you create data source connections.
. Minischemas tab—Lists the graphical representations of the tables you select from one or more data sources connections. You can create minischemas when creating data source connections, or you can create them later.
Also, it has a functonality that can migrate EIS catalog to Essbase Studio Catalog. Same as EIS, a common metadata repository, or catalog, captures all metadata related to all Essbase applications built in the enterprise and allows the reuse of metadata at the lowest level of granularity. The catalog gives Essbase Studio knowledge of the common metadata that is shared across the various applications enterprise-wide.The metadata catalog is still saved in RLDB, it describes the metadata repository.
The Source Navigator, displayed by default in the right pane of the Essbase Studio Console, has two tabs, the Data Sources tab and the Minischemas tab.
. Data Sources tab—Lists the physical data sources to which you have created connections. You can also launch the Connection Wizard from this tab, where you create data source connections.
. Minischemas tab—Lists the graphical representations of the tables you select from one or more data sources connections. You can create minischemas when creating data source connections, or you can create them later.
12/20/09
Informatica PowerCenter ETL steps
Informatica PowerCenter is included inside Hyperion Integration Management now. It has another name called DIM as a Hyperion component. The basics steps:
1. Create the 1st repository using Workflow Manager
2. Set up the Source connection and Target connection using Workflow Manager
3. Create the target tables using Designer->Tools->Warehouse Designer, select create table and drop table, don't need to add primary key
4. Use Designer->Tools->Transformation Developer to set up the transformation rules, such as : Max(), Min(), and some more complicated functions
4. Using Mapping Designer to set the mapping between the source tables and the target tables, and drag in the transformation objects. Save the repository
5. Use Workflow manager->Session->Task, to create a session that include the previous mapping
6. Create the work flow that include the session task
7. Everytime if you change the mapping and the transformation,you need to refresh the session and workflow, there is no refresh button, but you need to edit the connections and save the repository and validate. This is important even if you make a slightly change in the mapping.
8. Run the workflow and see it in the workflow onitor.
1. Create the 1st repository using Workflow Manager
2. Set up the Source connection and Target connection using Workflow Manager
3. Create the target tables using Designer->Tools->Warehouse Designer, select create table and drop table, don't need to add primary key
4. Use Designer->Tools->Transformation Developer to set up the transformation rules, such as : Max(), Min(), and some more complicated functions
4. Using Mapping Designer to set the mapping between the source tables and the target tables, and drag in the transformation objects. Save the repository
5. Use Workflow manager->Session->Task, to create a session that include the previous mapping
6. Create the work flow that include the session task
7. Everytime if you change the mapping and the transformation,you need to refresh the session and workflow, there is no refresh button, but you need to edit the connections and save the repository and validate. This is important even if you make a slightly change in the mapping.
8. Run the workflow and see it in the workflow onitor.
12/15/09
Java - the package list
com.sqribe.rm.*
- Hyperion Performance Suite classes
com.essbase.api.*
- Hyperion Essbase API classes
java.io.*
- input, output
java.io.File
- It is the central class in working with files and directories
java.swing.*
- an API for providing a graphical user interface (GUI) for Java programs.
java.awt.*
- Contains all of the classes for creating user interfaces and for painting graphics and images.
java.awt.event.*
- Provides interfaces and classes for dealing with different types of events fired by AWT components.
java.util.*
- The package java.util contains a variety of utility classes including the collections framework,
internationalization, String utilities, etc
java.lang
- Provides classes that are fundamental to the design of the Java programming language.
- Hyperion Performance Suite classes
com.essbase.api.*
- Hyperion Essbase API classes
java.io.*
- input, output
java.io.File
- It is the central class in working with files and directories
java.swing.*
- an API for providing a graphical user interface (GUI) for Java programs.
java.awt.*
- Contains all of the classes for creating user interfaces and for painting graphics and images.
java.awt.event.*
- Provides interfaces and classes for dealing with different types of events fired by AWT components.
java.util.*
- The package java.util contains a variety of utility classes including the collections framework,
internationalization, String utilities, etc
java.lang
- Provides classes that are fundamental to the design of the Java programming language.
12/14/09
Java-Object Serialization
.Object serialization: the capability to read and write an object using stream.
.Reflection:the capability of one object to learn details about another object
.Remote method invocation: the capability to query another object to investigate its features and call its methods.
A programming concept involved in object serialization is persistence - the capability of an object to exist and function outside the pragram that create it.
An object indicates that it can be used with streams by implementing the Serializable interface. This interface, which is part of the java.io package, differs from other interfaces you have worked withit does not contain any methods that must be included in the classes that implement it. The sole purpose of Serializable is to indicate that objects of that class can be stored an retrieved in serial form.
The following code creates an output stream and an associated object output stream:
FileOutputStream disk = new FileOutputStream("SavedObject.dat");
ObjectOutputStream obj = new ObjectOutputStream(disk);
The object to Disk program:
----------
import java.io.*;
import java.util.*;
public class ObjectToDisk {
public static void main(String[] arguments) {
Message mess = new Message();
String author = "Sam Wainwright, London";
String recipient = "George Bailey, Bedford Falls";
String[] letter = { "Mr. Gower cabled you need cash. Stop.",
"My office instructed to advance you up to twenty-five",
"thousand dollars. Stop. Hee-haw and Merry Christmas." };
Date now = new Date();
mess.writeMessage(author, recipient, now, letter);
try {
FileOutputStream fo = new FileOutputStream("Message.obj");
ObjectOutputStream oo = new ObjectOutputStream(fo);
oo.writeObject(mess);
oo.close();
System.out.println("Object created successfully.");
} catch (IOException e) {
System.out.println("Error -- " + e.toString());
}
}
}
class Message implements Serializable {
int lineCount;
String from, to;
Date when;
String[] text;
void writeMessage(String inFrom,
String inTo,
Date inWhen,
String[] inText) {
text = new String[inText.length];
for (int i = 0; i < inText.length; i++)
text[i] = inText[i];
lineCount = inText.length;
to = inTo;
from = inFrom;
when = inWhen;
}
}
--------------
The object from disk program:
import java.io.*;
import java.util.*;
public class ObjectFromDisk {
public static void main(String[] arguments) {
try {
FileInputStream fi = new FileInputStream("message.obj");
ObjectInputStream oi = new ObjectInputStream(fi);
Message mess = (Message) oi.readObject();
System.out.println("Message:\n");
System.out.println("From: " + mess.from);
System.out.println("To: " + mess.to);
System.out.println("Date: " + mess.when + "\n");
for (int i = 0; i < mess.lineCount; i++)
System.out.println(mess.text[i]);
oi.close();
} catch (Exception e) {
System.out.println("Error -- " + e.toString());
}
}
}
.Reflection:the capability of one object to learn details about another object
.Remote method invocation: the capability to query another object to investigate its features and call its methods.
A programming concept involved in object serialization is persistence - the capability of an object to exist and function outside the pragram that create it.
An object indicates that it can be used with streams by implementing the Serializable interface. This interface, which is part of the java.io package, differs from other interfaces you have worked withit does not contain any methods that must be included in the classes that implement it. The sole purpose of Serializable is to indicate that objects of that class can be stored an retrieved in serial form.
The following code creates an output stream and an associated object output stream:
FileOutputStream disk = new FileOutputStream("SavedObject.dat");
ObjectOutputStream obj = new ObjectOutputStream(disk);
The object to Disk program:
----------
import java.io.*;
import java.util.*;
public class ObjectToDisk {
public static void main(String[] arguments) {
Message mess = new Message();
String author = "Sam Wainwright, London";
String recipient = "George Bailey, Bedford Falls";
String[] letter = { "Mr. Gower cabled you need cash. Stop.",
"My office instructed to advance you up to twenty-five",
"thousand dollars. Stop. Hee-haw and Merry Christmas." };
Date now = new Date();
mess.writeMessage(author, recipient, now, letter);
try {
FileOutputStream fo = new FileOutputStream("Message.obj");
ObjectOutputStream oo = new ObjectOutputStream(fo);
oo.writeObject(mess);
oo.close();
System.out.println("Object created successfully.");
} catch (IOException e) {
System.out.println("Error -- " + e.toString());
}
}
}
class Message implements Serializable {
int lineCount;
String from, to;
Date when;
String[] text;
void writeMessage(String inFrom,
String inTo,
Date inWhen,
String[] inText) {
text = new String[inText.length];
for (int i = 0; i < inText.length; i++)
text[i] = inText[i];
lineCount = inText.length;
to = inTo;
from = inFrom;
when = inWhen;
}
}
--------------
The object from disk program:
import java.io.*;
import java.util.*;
public class ObjectFromDisk {
public static void main(String[] arguments) {
try {
FileInputStream fi = new FileInputStream("message.obj");
ObjectInputStream oi = new ObjectInputStream(fi);
Message mess = (Message) oi.readObject();
System.out.println("Message:\n");
System.out.println("From: " + mess.from);
System.out.println("To: " + mess.to);
System.out.println("Date: " + mess.when + "\n");
for (int i = 0; i < mess.lineCount; i++)
System.out.println(mess.text[i]);
oi.close();
} catch (Exception e) {
System.out.println("Error -- " + e.toString());
}
}
}
Java - display all methods in a class
This method can display all metjods inside a class. This will use the Class class, which is part of java.lang package, is used to learn about and create classes,interfaces,and primitive types. To try the below program, enter:
java SeeMethods java.util.Random, and you will get
Method: next()
Modifiers: protected synchronized
Return type: int
Parameters: int
Method: nextDouble()
Modifiers: public
Return type: double
Parameters:
......
--------------------------------
import java.lang.reflect.*;
public class SeeMethods {
public static void main(String[] arguments) {
Class inspect;
try {
if (arguments.length > 0)
inspect = Class.forName(arguments[0]);
else
inspect = Class.forName("SeeMethods");
Method[] methods = inspect.getDeclaredMethods();
for (int i = 0; i < methods.length; i++) {
Method methVal = methods[i];
Class returnVal = methVal.getReturnType();
int mods = methVal.getModifiers();
String modVal = Modifier.toString(mods);
Class[] paramVal = methVal.getParameterTypes();
StringBuffer params = new StringBuffer();
for (int j = 0; j < paramVal.length; j++) {
if (j > 0)
params.append(", ");
params.append(paramVal[j].getName());
}
System.out.println("Method: " + methVal.getName() + "()");
System.out.println("Modifiers: " + modVal);
System.out.println("Return Type: " + returnVal.getName());
System.out.println("Parameters: " + params + "\n");
}
} catch (ClassNotFoundException c) {
System.out.println(c.toString());
}
}
}
java SeeMethods java.util.Random, and you will get
Method: next()
Modifiers: protected synchronized
Return type: int
Parameters: int
Method: nextDouble()
Modifiers: public
Return type: double
Parameters:
......
--------------------------------
import java.lang.reflect.*;
public class SeeMethods {
public static void main(String[] arguments) {
Class inspect;
try {
if (arguments.length > 0)
inspect = Class.forName(arguments[0]);
else
inspect = Class.forName("SeeMethods");
Method[] methods = inspect.getDeclaredMethods();
for (int i = 0; i < methods.length; i++) {
Method methVal = methods[i];
Class returnVal = methVal.getReturnType();
int mods = methVal.getModifiers();
String modVal = Modifier.toString(mods);
Class[] paramVal = methVal.getParameterTypes();
StringBuffer params = new StringBuffer();
for (int j = 0; j < paramVal.length; j++) {
if (j > 0)
params.append(", ");
params.append(paramVal[j].getName());
}
System.out.println("Method: " + methVal.getName() + "()");
System.out.println("Modifiers: " + modVal);
System.out.println("Return Type: " + returnVal.getName());
System.out.println("Parameters: " + params + "\n");
}
} catch (ClassNotFoundException c) {
System.out.println(c.toString());
}
}
}
JavaBeans
JavaBean is a software object that interacts with other objects according to a strict set of guidelines - the JavaBean Specification. By following these guidlines, the bean can most easily be used with other objects. JavaBean is an architecture - and platform-independent set of classes for creating and using Java software components. JavaBean inherit "persistence".Persistence is handled automatically in JavaBean by using serialization mechanism. Serialization is the process of storing or retrieving information through a standard protocol. JavaBean specifies a rich of mechanisms for interaction between objects,along with common actions that most objectd will need to support,such as persistence and event handling.
With visual tools,you can use a variety of JavaBean components together without necessarily writing any code.JavaBeans components expose their own interfaces visually,providing a means to edit their properties without programming.Furthermore,by using a visual editor,you can drop a JavaBeans component directly into an application without writing any code.This is entirely new level of flexibility and reusability that was impossible in Java alone.
With visual tools,you can use a variety of JavaBean components together without necessarily writing any code.JavaBeans components expose their own interfaces visually,providing a means to edit their properties without programming.Furthermore,by using a visual editor,you can drop a JavaBeans component directly into an application without writing any code.This is entirely new level of flexibility and reusability that was impossible in Java alone.
Java - JDBC connect DB
Use JDBC driver to access DB.
import java.sql.*;
public class Presidents {
public static void main(String[] arguments) {
String data =
try {
Class.forName("JData2_0.sql.$Driver");
Connection conn = DriverManager.getConnection("jdbc:JDataConnect://localhost:1150/Presidents","username","password");
Statement st = conn.createStatement();
ResultSet rec = st.executeQuery(
"SELECT * FROM Contacts ORDER BY NAME");
while(rec.next()) {
System.out.println(rec.getString("NAME") + "\n"
+ rec.getString("ADDRESS1") + "\n"
+ rec.getString("ADDRESS2") + "\n"
+ rec.getString("PHONE") + "\n"
+ rec.getString("E-MAIL") + "\n");
}
st.close();
} catch (Exception e) {
System.out.println("Error -- " + e.toString());
}
}
}
import java.sql.*;
public class Presidents {
public static void main(String[] arguments) {
String data =
try {
Class.forName("JData2_0.sql.$Driver");
Connection conn = DriverManager.getConnection("jdbc:JDataConnect://localhost:1150/Presidents","username","password");
Statement st = conn.createStatement();
ResultSet rec = st.executeQuery(
"SELECT * FROM Contacts ORDER BY NAME");
while(rec.next()) {
System.out.println(rec.getString("NAME") + "\n"
+ rec.getString("ADDRESS1") + "\n"
+ rec.getString("ADDRESS2") + "\n"
+ rec.getString("PHONE") + "\n"
+ rec.getString("E-MAIL") + "\n");
}
st.close();
} catch (Exception e) {
System.out.println("Error -- " + e.toString());
}
}
}
Java - JDBC-ODBC bridge
JDBC is a set of classes that can be used to develop client/server database applications using Java. JDBC-ODBC bridge allows JDBC drivers to be used as ODBC drivers by coverting JDBC method into ODBC function calls. sun.jdbc.odbc.JdbcOdbcDriver is required.
import java.sql.*;
public class CoalTotals {
public static void main(String[] arguments) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = DriverManager.getConnection("jdbc:odbc:SampleDataSource", "username", "password");
Statement st = conn.createStatement();
ResultSet rec = st.executeQuery(
"SELECT * " +
"FROM Coal " +
"WHERE " +
"(Country='" + arguments[0] + "') " +
"ORDER BY Year");
System.out.println("FIPS\tCOUNTRY\t\tYEAR\t" +
"ANTHRACITE PRODUCTION");
while(rec.next()) {
System.out.println(rec.getString(1) + "\t"
+ rec.getString(2) + "\t\t"
+ rec.getString(3) + "\t"
+ rec.getString(4));
}
st.close();
} catch (SQLException s) {
System.out.println("SQL Error: " + s.toString() + " "+ s.getErrorCode() + " " + s.getSQLState());
} catch (Exception e) {
System.out.println("Error: " + e.toString()+ e.getMessage());
}
}
}
import java.sql.*;
public class CoalTotals {
public static void main(String[] arguments) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = DriverManager.getConnection("jdbc:odbc:SampleDataSource", "username", "password");
Statement st = conn.createStatement();
ResultSet rec = st.executeQuery(
"SELECT * " +
"FROM Coal " +
"WHERE " +
"(Country='" + arguments[0] + "') " +
"ORDER BY Year");
System.out.println("FIPS\tCOUNTRY\t\tYEAR\t" +
"ANTHRACITE PRODUCTION");
while(rec.next()) {
System.out.println(rec.getString(1) + "\t"
+ rec.getString(2) + "\t\t"
+ rec.getString(3) + "\t"
+ rec.getString(4));
}
st.close();
} catch (SQLException s) {
System.out.println("SQL Error: " + s.toString() + " "+ s.getErrorCode() + " " + s.getSQLState());
} catch (Exception e) {
System.out.println("Error: " + e.toString()+ e.getMessage());
}
}
}
12/10/09
Hyperion Planning Dimension Sequence
The order of dimensions is critical for the structure and performance of Essbase databases. Optimize performance by ordering dimensions according to these guidelines:
. Make Period and Account dense, and order dense dimensions from most to least dense. The most dense is usually Period, followed by Account. Dense dimensions calculate faster than sparse dimensions.
. Separate sparse dimensions into aggregating and non-aggregating dimensions. Place aggregating dimensions before non-aggregating dimensions. Order sparse dimensions from most to least dense. Aggregating dimensions, such as Account, aggregate, or consolidate,children into the parent to create new data. Non-aggregating dimensions, such as Scenario,
do not consolidate children to create data.
. Make Period and Account dense, and order dense dimensions from most to least dense. The most dense is usually Period, followed by Account. Dense dimensions calculate faster than sparse dimensions.
. Separate sparse dimensions into aggregating and non-aggregating dimensions. Place aggregating dimensions before non-aggregating dimensions. Order sparse dimensions from most to least dense. Aggregating dimensions, such as Account, aggregate, or consolidate,children into the parent to create new data. Non-aggregating dimensions, such as Scenario,
do not consolidate children to create data.
12/9/09
Essbase Migration Keys
1. Make hardware and the version upgrade.
2. Install the application on the new server.
3. Use the migration wizard, this would migrate all the objects, but not the data.
4. Import and export is the way to get the data after migration.
5. Open ports on the servers, make sure no port conflict.
6. Make sure the same language is selected
7. Create the ODBC drivers if SQL interface is used.
8. Put the customized Java functions in the UDF folder
9. Copy Substitution variables to the new server
10. Verify security on the new server, sometimes the migration wizard doe not do it properly
11. Check Essbase config file and copy over needed parameters
12. Do lots of testing of calc scripts, report scripts, and MDX scripts
2. Install the application on the new server.
3. Use the migration wizard, this would migrate all the objects, but not the data.
4. Import and export is the way to get the data after migration.
5. Open ports on the servers, make sure no port conflict.
6. Make sure the same language is selected
7. Create the ODBC drivers if SQL interface is used.
8. Put the customized Java functions in the UDF folder
9. Copy Substitution variables to the new server
10. Verify security on the new server, sometimes the migration wizard doe not do it properly
11. Check Essbase config file and copy over needed parameters
12. Do lots of testing of calc scripts, report scripts, and MDX scripts
12/7/09
Hyperion SQR Performance Tuning
SQR Performance and SQL Statements
Whenever a program contains a BEGIN-SELECT, BEGIN-SQL, or EXECUTE command, it performs a SQL statement. Processing SQL statements typically consumes significant computing resources. Tuning
SQL statements typically yields higher performance gains than tuning any other part of your program.
This paper focuses on SQR tools for simplifying SQL statements and reducing the number of SQL executions. There are several techniques, including:
• Simplify a complex select paragraph.
• Use LOAD-LOOKUP to simplify joins.
• Improve SQL performance with dynamic SQL.
• Examine SQL cursor status.
• Avoid temporary database tables.
• Create multiple reports in one pass.
• Tune SQR numerics.
• Compile SQR programs and use SQR Execute.
• Set processing limits.
• Buffer fetched rows.
• Run programs on the database server.
Simplifying a Complex Select Paragraph
With relational database design, information is often normalized by storing data entities in separate tables. To display the normalized information, we must write a select paragraph that joins these tables together. With many database systems, performance suffers when you join more than three or four tables in one select paragraph.
With SQR, we can perform multiple select paragraphs and nest them. In this way, we can break a large join into several simpler selects. For example, we can break a select paragraph that joins the orders and the products tables into two selects. The first select retrieves the orders in which we are interested. For each order that is retrieved, a second select retrieves the products that were ordered. The second select is correlated to the first select by having a condition such as:
where order_num = &order_num
This condition specifies that the second select retrieves only products for the current order.
Similarly, if the report is based on products that were ordered, you can make the first select retrieve the products and make the second select retrieve the orders for each product.
This method improves performance in many cases, but not all. To achieve the best performance, we need to experiment with the different alternatives.
Using LOAD-LOOKUP to Simplify Joins
Database tables often contain key columns, such as a employee id or customer number. To retrieve a certain piece of information, we join two or more tables that contain the same column. For example, to obtain a product description, we can join the orders table with the products table by using the product_code column as the key.
With LOAD-LOOKUP, you can reduce the number of tables that are joined in one select. Use this command with LOOKUP commands.
The LOAD-LOOKUP command defines an array containing a set of keys and values and loads it into memory. The LOOKUP command looks up a key in the array and returns the associated value. In some programs, this technique performs better than a conventional table join.
We can use LOAD-LOOKUP in the SETUP section or in a procedure. If used in the SETUP section, it is processed only once. If used in a procedure, it is processed each time it is encountered.
LOAD-LOOKUP retrieves two fields from the database: the KEY field and the RETURN_VALUE field. Rows are ordered by KEY and stored in an array. The KEY field must be unique and contain no null values.
When the LOOKUP command is used, the array is searched (by using a binary search) to find the RETURN_VALUE field corresponding to the KEY that is referenced in the lookup.
The following code example illustrates LOAD-LOOKUP and LOOKUP:
begin-setup
load-lookup
name=NAMES
table=EMPNAME
key=EMPLOYEE_ID
return_value=EMPLOYEE_NAME
end-setup
...
begin-select
BUSINESS_UNIT (+1,1)
EMPLOYEE_ID
lookup NAMES &EMPLOYEE_ID $EMPLOYEE_NAME
print $EMPLOYEE_NAME (,15)
from JOB
end-select
In this code example, the LOAD-LOOKUP command loads an array with the EMPLOYEE_ID and EMPLOYEE_NAME columns from the EMPNAME table. The lookup array is named NAMES. The EMPLOYEE_ID column is the key and the EMPLOYEE_NAME column is the return value. In the select paragraph, a LOOKUP on the NAMES array retrieves the EMPLOYEE_NAME for each EMPLOYEE_ID. This technique eliminates the need to join the EMPNAME table in the select.
If the JOB and EMPNAME tables were joined in the select (without LOAD-LOOKUP), the code would look like this:
begin-select
BUSINESS_UNIT (+1,1)
JOB.EMPLOYEE_IDproduct_code
EMPLOYEE_NAME (,15)
from JOB, EMPNAME
where JOB.EMPLOYEE_ID = EMPNAME.EMPLOYEE_ID
end-select
Whether a database join or LOAD-LOOKUP is faster depends on the program. LOAD-LOOKUP improves performance when:
• It is used with multiple select paragraphs.
• It keeps the number of tables being joined from exceeding three or four.
• The number of entries in the LOAD-LOOKUP table is small compared to the number of rows in the select, and they are used often.
• Most entries in the LOAD-LOOKUP table are used.
Note. You can concatenate columns if you want RETURN_VALUE to return more than one column. The concatenation symbol is database specific.
Improving SQL Performance with Dynamic SQL
We can use dynamic SQL in some situations to simplify a SQL statement and gain performance:
begin-select
BUSINESS_UNIT
from JOB, LOCATION_TBL
where JOB.BUSINESS_UNIT = LOCATION_TBL.BUSINESS_UNIT
and ($state = 'CA' and EFF_DATE > $start_date
or $state != 'CA' and TRANSFER_DATE > $start_date)
end-select
In this example, a given value of $state, EFF_DATE, or TRANSFER_DATE is compared to $start_date. The OR operator in the condition makes such multiple comparisons possible. With most databases, an OR operator slows processing. It can cause the database to perform more work than necessary.
However, the same work can be done with a simpler select. For example, if $state is ‘CA,’ the following select works:
begin-select
BUSINESS_UNIT
from JOB, LOCATION_TBL
where JOB.BUSINESS_UNIT = LOCATION_TBL.BUSINESS_UNIT
and EFF_DATE > $start_date
end-select
Dynamic SQL enables you to check the value of $state and create the simpler condition:
if $state = 'CA'
let $datecol = 'EFF_DATE'
else
let $datecol = 'TRANSFER_DATE'
end-if
begin-select
BUSINESS_UNIT
from JOB, LOCATION_TBL
where JOB.BUSINESS_UNIT = LOCATION_TBL.BUSINESS_UNIT
and [$datecol] > $start_date
end-select
The [$datecol] substitution variable substitutes the name of the column to be compared with $state_date. The select is simpler and no longer uses an OR operator. In most cases, this use of dynamic SQL improves performance.
Examining SQL Cursor Status
Because SQR programs select and manipulate data from a SQL database, it is helpful to understand how SQR processes SQL statements and queries.
SQR programs can perform multiple SQL statements. Moreover, the same SQL statement can be run multiple times.
When a program runs, a pool of SQL statement handles—called cursors—is maintained. A cursor is a storage location for one SQL statement; for example, SELECT, INSERT, or UPDATE. Every SQL statement uses a cursor for processing. A cursor holds the context for the execution of a SQL statement.
The cursor pool contains 30 cursors, and its size cannot be changed. When a SQL statement is rerun, its cursor can be immediately reused if it is still in the cursor pool. When an SQR program runs more than 30 different SQL statement, cursors in the pool are reassigned.
To examine how cursors are managed, use the -S command-line flag. This flag displays cursor status information at the end of a run.
The following information appears for each cursor:
Cursor #nn:
SQL =
Compiles = nn
Executes = nn
Rows = nn
The listing also includes the number of compiles, which vary according to the database and the complexity of the query. With Oracle, for example, a simple query is compiled only once. With SYBASE, a SQL statement is compiled before it is first run and recompiled for the purpose of validation during the SQR compile phase. Therefore, you may see two compiles for a SQL statement. Later when the SQL is rerun, if its cursor is found in the cursor pool, it can proceed without recompiling.
Avoiding Temporary Database Tables
Programs often use temporary database tables to hold intermediate results. Creating, updating, and deleting database temporary tables is a very resource-consuming task, however, and can hurt your program’s performance. SQR provides two alternatives to using temporary database tables.
The first alternative is to store intermediate results in an SQR array. The second is to store intermediate results in a local flat file. Both techniques can bring about a significant performance gain. You can use the SQR language to manipulate data stored in an array or a flat file.
These two methods are explained and demonstrated in the following sections. Methods for sorting data in SQR arrays or flat files are also explained.
Using and Sorting Arrays
An SQR array can hold as many records as can fit in memory. During the first pass, when records are retrieved from the database, you can store them in the array. Subsequent passes on the data can be made without additional database access.
The following code example retrieves records, prints them, and saves them into an array named
ADDRESS_DETAILS_ARRAY:
create-array name=ADDRESS_DETAILS_ARRAY size=1000
field=ADDRESSLINE1:char field=ADDRESSLINE2:char
field=PINCODE:char field=PHONE:char
let #counter = 0
begin-select
ADDRESSLINE1 (,1)
ADDRESSLINE2 (,7)
PINCODE (,24)
PHONE (,55)
position (+1)
put &ADDRESSLINE1 &ADDRESSLINE2 &PINCODE &PHONE into ADDRESS_DETAILS_ARRAY(#counter)
add 1 to #counter
from ADDRESS
end-select
The ADDRESS_DETAILS_ARRAY array has four fields that correspond to the four columns that are selected from the ADDRESS table, and it can hold up to 1,000 rows. If the ADDRESS table had more than 1,000 rows, it would be necessary to create a larger array.
The select paragraph prints the data. The PUT command then stores the data in the array. You could use the LET command to assign values to array fields, however the PUT command performs the same work, with fewer lines of code. With PUT, you can assign all four fields in one command.
The #counter variable serves as the array subscript. It starts with zero and maintains the subscript of the next available entry. At the end of the select paragraph, the value of #counter is the number of records in the array.
The next code example retrieves the data from ADDRESS_DETAILS_ARRAY and prints it:
let #i = 0
while #i < #counter get &ADDRESSLINE1 &ADDRESSLINE2 &PINCODE &PHONE from ADDRESS_DETAILS_ARRAY(#i) print $ADDRESSLINE1 (,1) print $ADDRESSLINE2 (,7) print $PINCODE (,24) print $PHONE (,55) position (+1) add 1 to #i end-while In this code example, #i goes from 0 to #counter-1. The fields from each record are moved into the corresponding variables: $ADDRESSLINE1, $ADDRESSLINE2, $PINCODE and $PHONE. These values are then printed. Using and Sorting Flat Files An alternative to an array is a flat file. You can use a flat file when the required array size exceeds the available memory. As is the case with an array, you may need a sorting utility that supports NLS. The code example in the previous section can be rewritten to use a file instead of an array. The advantage of using a file is that the program is not constrained by the amount of memory that is available. The disadvantage of using a file is that the program performs more input/output (I/O). However, it may still be faster than performing another SQL statement to retrieve the same data. This program uses the UNIX/Linux sort utility to sort the file by name. This example can be extended to include other operating systems. The following code example is rewritten to use the cust.dat file instead of the array: Program ex25b.sqr begin-program do main end-programbegin-procedure main ! ! Open cust.dat ! open 'cust.dat' as 1 for-writing record=80:vary begin-select ADDRESSLINE1 (,1) ADDRESSLINE2 (,7) PINCODE (,24) PHONE (55) position (+1) ! Put data in the file write 1 from &ADDRESSLINE1:30 &ADDRESSLINE1:30 &PINCODE:6 &PHONE:10 from ADDRESS order by PINCODE end-select position (+2) ! ! Close cust.dat close 1 ! Sort cust.dat by name ! call system using 'sort cust.dat > cust2.dat' #status
if #status <> 0
display 'Error in sort'
stop
end-if
!
! Print ADDRESS (which are now sorted by name)
!
open 'cust2.dat' as 1 for-reading record=80:vary
while 1 ! loop until break
! Get data from the file
read 1 into $ADDRESSLINE1:30 $ADDRESSLINE2:30 $PINCODE:6 $PHONE:10
if #end-file
break ! End of file reached
end-if
print $ADDRESSLINE1 (,1)
print $ADDRESSLINE2 (,7)
print $PINCODE (,24)
print $PHONE (,55)
position (+1)
end-while
!
! close cust2.dat
close 1
end-procedure ! main
The program starts by opening a cust.dat file:
open 'cust.dat' as 1 for-writing record=80:vary
The OPEN command opens the file for writing and assigns it file number 1. You can open as many as 12 files in one SQR program. The file is set to support records of varying lengths with a maximum of 80 bytes (characters). For this example, you can also use fixed-length records.
As the program selects records from the database and prints them, it writes them to cust.dat:
write 1 from &ADDRESSLINE1:30 &ADDRESSLINE2:30 &PINCODE:6 &PHONE:10
The WRITE command writes the four columns into file number 1—the currently open cust.dat. It writes the name first, which makes it easier to sort the file by name. The program writes fixed-length fields. For example, &ADDRESSLINE1:30 specifies that the name column uses exactly 30 characters. If the actual name is shorter, it is padded with blanks. When the program has finished writing data to the file, it closes the file by using the CLOSE command.
The file is sorted with the UNIX sort utility:
call system using 'sort cust.dat > cust2.dat' #status
The sort cust.dat > cust2.datis command sent to the UNIX system. It invokes the UNIX sort command to sort cust.dat and direct the output to cust2.dat. The completion status is saved in #status; a status of 0 indicates success. Because name is at the beginning of each record, the file is sorted by name.
Next, we open cust2.dat for reading. The following command reads one record from the file and places the first 30 characters in $ADDRESSLINE1:
read 1 into $ADDRESSLINE1:30 $ADDRESSLINE2:30 $PINCODE:6 $PHONE:10
The next two characters are placed in $PINCODE and so on. When the end of the file is encountered, the #end-file reserved variable is automatically set to 1 (true). The program checks for #end-file and breaks out of the loop when the end of the file is reached. Finally, the program closes the file by using the CLOSE command.
Creating Multiple Reports in One Pass
Sometimes you must create multiple reports that are based on the same data. In many cases, these reports are similar, with only a difference in layout or summary. Typically, you can create multiple programs and even reuse code. However, if each program is run separately, the database has to repeat the query. Such repeated processing is often unnecessary.
With SQR, one program can create multiple reports simultaneously. In this method, a single program creates multiple reports, making just one pass on the data and reducing the amount of database processing.
Tuning SQR Numerics
SQR for PeopleSoft provides three types of numeric values:
• Machine floating point numbers
• Decimal numbers
• Integers
Machine floating point numbers are the default. They use the floating point arithmetic that is provided by the hardware. This method is very fast. It uses binary floating point and normally holds up to 15 digits of precision.
Some accuracy can be lost when converting decimal fractions to binary floating point numbers. To overcome this loss of accuracy, you can sometimes use the ROUND option of commands such as ADD, SUBTRACT, MULTIPLY, and DIVIDE. You can also use the round function of LET or numeric edit masks that round the results to the needed precision.
Decimal numbers provide exact math and precision of up to 38 digits. Math is performed in the software. This is the most accurate method, but also the slowest.
You can use integers for numbers that are known to be integers. There are several benefits for using integers because they:
• Enforce the integer type by not allowing fractions.
• Adhere to integer rules when dividing numbers.
Integer math is also the fastest, typically faster than floating point numbers.
If you use the DECLARE-VARIABLE command, the -DNT command-line flag, or the DEFAULT-NUMERIC entry in the Default-Settings section of the PSSQR.INI file, you can select the type of numbers that SQR uses. Moreover, you can select the type for individual variables in the program with the DECLARE-VARIABLE command. When you select decimal numbers, you can also specify the needed precision.
Selecting the numeric type for variables enables you to fine-tune the precision of numbers in your program. For most applications, however, this type of tuning does not yield a significant performance improvement, so it's best to select decimal. The default is machine floating point to provide compatibility with older releases of the product.
Setting Processing Limits
Use a startup file and the Processing-Limits section of PSSQR.INI to define the sizes and limitations of some of the internal structures that are used by SQR. An -M command-line flag can specify a startup file whose entries override those in PSSQR.INI. If you use the -Mb command-line flag, then corresponding sections of the file are not processed. Many of these settings have a direct affect on memory requirements.
Tuning of memory requirements used to be a factor with older, 16-bit operating systems, such as Windows 3.1. Today, most operating systems use virtual memory and tuning memory requirements normally do not affect performance in any significant way. The only case in which you might need to be concerned with processing limit settings is with large SQR programs that exceed default processing limit settings. In such cases you must increase the corresponding settings
Buffering Fetched Rows
When a BEGIN-SELECT command is run, records are fetched from the database server. To improve performance, they are fetched in groups rather than one at a time. The default is groups of 10 records. The records are buffered, and a program processes these records one at a time. A database fetch operation is therefore performed after every 10 records, instead of after every single record. This is a substantial performance gain. If the database server is on another computer, then network traffic is also significantly reduced.
Modify the number of records to fetch together by using the -B command-line flag or, for an individual BEGIN-SELECT command, by using its -B option. In both cases, specify the number of records to be fetched together. For example -B100 specifies that records be fetched in groups of 100. This means that the number of database fetch operations is further reduced.
This feature is currently available with SQR for ODBC and SQR for the Oracle or SYBASE databases.
Running Programs on the Database Server
To reduce network traffic and improve performance, run SQR programs directly on the database server machine. The SQR server is available on many server platforms including Windows NT and UNIX/Linux.
SQR Programming Principles
• Develop all SQRs using Structured Programming
Structured Programming
A technique for organizing and coding computer programs in which a hierarch of modules issued, each having a single entry and a single exit point, and in which control is passed downward through the structure without unconditional branches to higher levels of the structure.
Structured programming is often associated with a top-down approach to design. In this way designers map out the large scale structure of a program in terms of smaller operations, implement and test the smaller operations, and then tie them together into a whole program.
• Pseudo-Code
o Flowchart the program logic
o Research and ask a few questions:
􀂃 How much data will be pulled? Will it be100 rows or 1000 rows?
􀂃 How many rows will come from each table?
􀂃 What kind of data will be pulled? Student Data? Setup Data?
􀂃 What does the key structure look like for the tables being pulled?
􀂃 Is this table the parent or the child?
􀂃 How important is this table?
􀂃 What fields will be needed from this table? Are they available somewhere else?
o Write SQL and test in SQL Plus before coding SQR
• Use Linear Programming – easier to program and debug
• Comment
Summary
The following techniques can be used to improve the performance of your SQR programs:
• Simplify complex SELECT statements.
• Use LOAD-LOOKUP to simplify joins.
• Use dynamic SQL instead of a condition in a SELECT statement.
• Avoid using temporary database tables. Two alternatives to temporary
database tables are SQR arrays and flat files.
• Write programs that create multiple reports with one pass on the data.
• Use the most efficient numeric type for numeric variables (machine
floating point, decimal, or integer).
• Save compiled SQR programs and rerun them with SQR Execute.
• Adjust settings in the [Processing-Limits] section of SQR.INI or in a
startup file.
• Increase buffering of rows in SELECT statements with the -B flag.
• Execute programs on the database server machine.
• SQR Programming Principles
Whenever a program contains a BEGIN-SELECT, BEGIN-SQL, or EXECUTE command, it performs a SQL statement. Processing SQL statements typically consumes significant computing resources. Tuning
SQL statements typically yields higher performance gains than tuning any other part of your program.
This paper focuses on SQR tools for simplifying SQL statements and reducing the number of SQL executions. There are several techniques, including:
• Simplify a complex select paragraph.
• Use LOAD-LOOKUP to simplify joins.
• Improve SQL performance with dynamic SQL.
• Examine SQL cursor status.
• Avoid temporary database tables.
• Create multiple reports in one pass.
• Tune SQR numerics.
• Compile SQR programs and use SQR Execute.
• Set processing limits.
• Buffer fetched rows.
• Run programs on the database server.
Simplifying a Complex Select Paragraph
With relational database design, information is often normalized by storing data entities in separate tables. To display the normalized information, we must write a select paragraph that joins these tables together. With many database systems, performance suffers when you join more than three or four tables in one select paragraph.
With SQR, we can perform multiple select paragraphs and nest them. In this way, we can break a large join into several simpler selects. For example, we can break a select paragraph that joins the orders and the products tables into two selects. The first select retrieves the orders in which we are interested. For each order that is retrieved, a second select retrieves the products that were ordered. The second select is correlated to the first select by having a condition such as:
where order_num = &order_num
This condition specifies that the second select retrieves only products for the current order.
Similarly, if the report is based on products that were ordered, you can make the first select retrieve the products and make the second select retrieve the orders for each product.
This method improves performance in many cases, but not all. To achieve the best performance, we need to experiment with the different alternatives.
Using LOAD-LOOKUP to Simplify Joins
Database tables often contain key columns, such as a employee id or customer number. To retrieve a certain piece of information, we join two or more tables that contain the same column. For example, to obtain a product description, we can join the orders table with the products table by using the product_code column as the key.
With LOAD-LOOKUP, you can reduce the number of tables that are joined in one select. Use this command with LOOKUP commands.
The LOAD-LOOKUP command defines an array containing a set of keys and values and loads it into memory. The LOOKUP command looks up a key in the array and returns the associated value. In some programs, this technique performs better than a conventional table join.
We can use LOAD-LOOKUP in the SETUP section or in a procedure. If used in the SETUP section, it is processed only once. If used in a procedure, it is processed each time it is encountered.
LOAD-LOOKUP retrieves two fields from the database: the KEY field and the RETURN_VALUE field. Rows are ordered by KEY and stored in an array. The KEY field must be unique and contain no null values.
When the LOOKUP command is used, the array is searched (by using a binary search) to find the RETURN_VALUE field corresponding to the KEY that is referenced in the lookup.
The following code example illustrates LOAD-LOOKUP and LOOKUP:
begin-setup
load-lookup
name=NAMES
table=EMPNAME
key=EMPLOYEE_ID
return_value=EMPLOYEE_NAME
end-setup
...
begin-select
BUSINESS_UNIT (+1,1)
EMPLOYEE_ID
lookup NAMES &EMPLOYEE_ID $EMPLOYEE_NAME
print $EMPLOYEE_NAME (,15)
from JOB
end-select
In this code example, the LOAD-LOOKUP command loads an array with the EMPLOYEE_ID and EMPLOYEE_NAME columns from the EMPNAME table. The lookup array is named NAMES. The EMPLOYEE_ID column is the key and the EMPLOYEE_NAME column is the return value. In the select paragraph, a LOOKUP on the NAMES array retrieves the EMPLOYEE_NAME for each EMPLOYEE_ID. This technique eliminates the need to join the EMPNAME table in the select.
If the JOB and EMPNAME tables were joined in the select (without LOAD-LOOKUP), the code would look like this:
begin-select
BUSINESS_UNIT (+1,1)
JOB.EMPLOYEE_IDproduct_code
EMPLOYEE_NAME (,15)
from JOB, EMPNAME
where JOB.EMPLOYEE_ID = EMPNAME.EMPLOYEE_ID
end-select
Whether a database join or LOAD-LOOKUP is faster depends on the program. LOAD-LOOKUP improves performance when:
• It is used with multiple select paragraphs.
• It keeps the number of tables being joined from exceeding three or four.
• The number of entries in the LOAD-LOOKUP table is small compared to the number of rows in the select, and they are used often.
• Most entries in the LOAD-LOOKUP table are used.
Note. You can concatenate columns if you want RETURN_VALUE to return more than one column. The concatenation symbol is database specific.
Improving SQL Performance with Dynamic SQL
We can use dynamic SQL in some situations to simplify a SQL statement and gain performance:
begin-select
BUSINESS_UNIT
from JOB, LOCATION_TBL
where JOB.BUSINESS_UNIT = LOCATION_TBL.BUSINESS_UNIT
and ($state = 'CA' and EFF_DATE > $start_date
or $state != 'CA' and TRANSFER_DATE > $start_date)
end-select
In this example, a given value of $state, EFF_DATE, or TRANSFER_DATE is compared to $start_date. The OR operator in the condition makes such multiple comparisons possible. With most databases, an OR operator slows processing. It can cause the database to perform more work than necessary.
However, the same work can be done with a simpler select. For example, if $state is ‘CA,’ the following select works:
begin-select
BUSINESS_UNIT
from JOB, LOCATION_TBL
where JOB.BUSINESS_UNIT = LOCATION_TBL.BUSINESS_UNIT
and EFF_DATE > $start_date
end-select
Dynamic SQL enables you to check the value of $state and create the simpler condition:
if $state = 'CA'
let $datecol = 'EFF_DATE'
else
let $datecol = 'TRANSFER_DATE'
end-if
begin-select
BUSINESS_UNIT
from JOB, LOCATION_TBL
where JOB.BUSINESS_UNIT = LOCATION_TBL.BUSINESS_UNIT
and [$datecol] > $start_date
end-select
The [$datecol] substitution variable substitutes the name of the column to be compared with $state_date. The select is simpler and no longer uses an OR operator. In most cases, this use of dynamic SQL improves performance.
Examining SQL Cursor Status
Because SQR programs select and manipulate data from a SQL database, it is helpful to understand how SQR processes SQL statements and queries.
SQR programs can perform multiple SQL statements. Moreover, the same SQL statement can be run multiple times.
When a program runs, a pool of SQL statement handles—called cursors—is maintained. A cursor is a storage location for one SQL statement; for example, SELECT, INSERT, or UPDATE. Every SQL statement uses a cursor for processing. A cursor holds the context for the execution of a SQL statement.
The cursor pool contains 30 cursors, and its size cannot be changed. When a SQL statement is rerun, its cursor can be immediately reused if it is still in the cursor pool. When an SQR program runs more than 30 different SQL statement, cursors in the pool are reassigned.
To examine how cursors are managed, use the -S command-line flag. This flag displays cursor status information at the end of a run.
The following information appears for each cursor:
Cursor #nn:
SQL =
Compiles = nn
Executes = nn
Rows = nn
The listing also includes the number of compiles, which vary according to the database and the complexity of the query. With Oracle, for example, a simple query is compiled only once. With SYBASE, a SQL statement is compiled before it is first run and recompiled for the purpose of validation during the SQR compile phase. Therefore, you may see two compiles for a SQL statement. Later when the SQL is rerun, if its cursor is found in the cursor pool, it can proceed without recompiling.
Avoiding Temporary Database Tables
Programs often use temporary database tables to hold intermediate results. Creating, updating, and deleting database temporary tables is a very resource-consuming task, however, and can hurt your program’s performance. SQR provides two alternatives to using temporary database tables.
The first alternative is to store intermediate results in an SQR array. The second is to store intermediate results in a local flat file. Both techniques can bring about a significant performance gain. You can use the SQR language to manipulate data stored in an array or a flat file.
These two methods are explained and demonstrated in the following sections. Methods for sorting data in SQR arrays or flat files are also explained.
Using and Sorting Arrays
An SQR array can hold as many records as can fit in memory. During the first pass, when records are retrieved from the database, you can store them in the array. Subsequent passes on the data can be made without additional database access.
The following code example retrieves records, prints them, and saves them into an array named
ADDRESS_DETAILS_ARRAY:
create-array name=ADDRESS_DETAILS_ARRAY size=1000
field=ADDRESSLINE1:char field=ADDRESSLINE2:char
field=PINCODE:char field=PHONE:char
let #counter = 0
begin-select
ADDRESSLINE1 (,1)
ADDRESSLINE2 (,7)
PINCODE (,24)
PHONE (,55)
position (+1)
put &ADDRESSLINE1 &ADDRESSLINE2 &PINCODE &PHONE into ADDRESS_DETAILS_ARRAY(#counter)
add 1 to #counter
from ADDRESS
end-select
The ADDRESS_DETAILS_ARRAY array has four fields that correspond to the four columns that are selected from the ADDRESS table, and it can hold up to 1,000 rows. If the ADDRESS table had more than 1,000 rows, it would be necessary to create a larger array.
The select paragraph prints the data. The PUT command then stores the data in the array. You could use the LET command to assign values to array fields, however the PUT command performs the same work, with fewer lines of code. With PUT, you can assign all four fields in one command.
The #counter variable serves as the array subscript. It starts with zero and maintains the subscript of the next available entry. At the end of the select paragraph, the value of #counter is the number of records in the array.
The next code example retrieves the data from ADDRESS_DETAILS_ARRAY and prints it:
let #i = 0
while #i < #counter get &ADDRESSLINE1 &ADDRESSLINE2 &PINCODE &PHONE from ADDRESS_DETAILS_ARRAY(#i) print $ADDRESSLINE1 (,1) print $ADDRESSLINE2 (,7) print $PINCODE (,24) print $PHONE (,55) position (+1) add 1 to #i end-while In this code example, #i goes from 0 to #counter-1. The fields from each record are moved into the corresponding variables: $ADDRESSLINE1, $ADDRESSLINE2, $PINCODE and $PHONE. These values are then printed. Using and Sorting Flat Files An alternative to an array is a flat file. You can use a flat file when the required array size exceeds the available memory. As is the case with an array, you may need a sorting utility that supports NLS. The code example in the previous section can be rewritten to use a file instead of an array. The advantage of using a file is that the program is not constrained by the amount of memory that is available. The disadvantage of using a file is that the program performs more input/output (I/O). However, it may still be faster than performing another SQL statement to retrieve the same data. This program uses the UNIX/Linux sort utility to sort the file by name. This example can be extended to include other operating systems. The following code example is rewritten to use the cust.dat file instead of the array: Program ex25b.sqr begin-program do main end-programbegin-procedure main ! ! Open cust.dat ! open 'cust.dat' as 1 for-writing record=80:vary begin-select ADDRESSLINE1 (,1) ADDRESSLINE2 (,7) PINCODE (,24) PHONE (55) position (+1) ! Put data in the file write 1 from &ADDRESSLINE1:30 &ADDRESSLINE1:30 &PINCODE:6 &PHONE:10 from ADDRESS order by PINCODE end-select position (+2) ! ! Close cust.dat close 1 ! Sort cust.dat by name ! call system using 'sort cust.dat > cust2.dat' #status
if #status <> 0
display 'Error in sort'
stop
end-if
!
! Print ADDRESS (which are now sorted by name)
!
open 'cust2.dat' as 1 for-reading record=80:vary
while 1 ! loop until break
! Get data from the file
read 1 into $ADDRESSLINE1:30 $ADDRESSLINE2:30 $PINCODE:6 $PHONE:10
if #end-file
break ! End of file reached
end-if
print $ADDRESSLINE1 (,1)
print $ADDRESSLINE2 (,7)
print $PINCODE (,24)
print $PHONE (,55)
position (+1)
end-while
!
! close cust2.dat
close 1
end-procedure ! main
The program starts by opening a cust.dat file:
open 'cust.dat' as 1 for-writing record=80:vary
The OPEN command opens the file for writing and assigns it file number 1. You can open as many as 12 files in one SQR program. The file is set to support records of varying lengths with a maximum of 80 bytes (characters). For this example, you can also use fixed-length records.
As the program selects records from the database and prints them, it writes them to cust.dat:
write 1 from &ADDRESSLINE1:30 &ADDRESSLINE2:30 &PINCODE:6 &PHONE:10
The WRITE command writes the four columns into file number 1—the currently open cust.dat. It writes the name first, which makes it easier to sort the file by name. The program writes fixed-length fields. For example, &ADDRESSLINE1:30 specifies that the name column uses exactly 30 characters. If the actual name is shorter, it is padded with blanks. When the program has finished writing data to the file, it closes the file by using the CLOSE command.
The file is sorted with the UNIX sort utility:
call system using 'sort cust.dat > cust2.dat' #status
The sort cust.dat > cust2.datis command sent to the UNIX system. It invokes the UNIX sort command to sort cust.dat and direct the output to cust2.dat. The completion status is saved in #status; a status of 0 indicates success. Because name is at the beginning of each record, the file is sorted by name.
Next, we open cust2.dat for reading. The following command reads one record from the file and places the first 30 characters in $ADDRESSLINE1:
read 1 into $ADDRESSLINE1:30 $ADDRESSLINE2:30 $PINCODE:6 $PHONE:10
The next two characters are placed in $PINCODE and so on. When the end of the file is encountered, the #end-file reserved variable is automatically set to 1 (true). The program checks for #end-file and breaks out of the loop when the end of the file is reached. Finally, the program closes the file by using the CLOSE command.
Creating Multiple Reports in One Pass
Sometimes you must create multiple reports that are based on the same data. In many cases, these reports are similar, with only a difference in layout or summary. Typically, you can create multiple programs and even reuse code. However, if each program is run separately, the database has to repeat the query. Such repeated processing is often unnecessary.
With SQR, one program can create multiple reports simultaneously. In this method, a single program creates multiple reports, making just one pass on the data and reducing the amount of database processing.
Tuning SQR Numerics
SQR for PeopleSoft provides three types of numeric values:
• Machine floating point numbers
• Decimal numbers
• Integers
Machine floating point numbers are the default. They use the floating point arithmetic that is provided by the hardware. This method is very fast. It uses binary floating point and normally holds up to 15 digits of precision.
Some accuracy can be lost when converting decimal fractions to binary floating point numbers. To overcome this loss of accuracy, you can sometimes use the ROUND option of commands such as ADD, SUBTRACT, MULTIPLY, and DIVIDE. You can also use the round function of LET or numeric edit masks that round the results to the needed precision.
Decimal numbers provide exact math and precision of up to 38 digits. Math is performed in the software. This is the most accurate method, but also the slowest.
You can use integers for numbers that are known to be integers. There are several benefits for using integers because they:
• Enforce the integer type by not allowing fractions.
• Adhere to integer rules when dividing numbers.
Integer math is also the fastest, typically faster than floating point numbers.
If you use the DECLARE-VARIABLE command, the -DNT command-line flag, or the DEFAULT-NUMERIC entry in the Default-Settings section of the PSSQR.INI file, you can select the type of numbers that SQR uses. Moreover, you can select the type for individual variables in the program with the DECLARE-VARIABLE command. When you select decimal numbers, you can also specify the needed precision.
Selecting the numeric type for variables enables you to fine-tune the precision of numbers in your program. For most applications, however, this type of tuning does not yield a significant performance improvement, so it's best to select decimal. The default is machine floating point to provide compatibility with older releases of the product.
Setting Processing Limits
Use a startup file and the Processing-Limits section of PSSQR.INI to define the sizes and limitations of some of the internal structures that are used by SQR. An -M command-line flag can specify a startup file whose entries override those in PSSQR.INI. If you use the -Mb command-line flag, then corresponding sections of the file are not processed. Many of these settings have a direct affect on memory requirements.
Tuning of memory requirements used to be a factor with older, 16-bit operating systems, such as Windows 3.1. Today, most operating systems use virtual memory and tuning memory requirements normally do not affect performance in any significant way. The only case in which you might need to be concerned with processing limit settings is with large SQR programs that exceed default processing limit settings. In such cases you must increase the corresponding settings
Buffering Fetched Rows
When a BEGIN-SELECT command is run, records are fetched from the database server. To improve performance, they are fetched in groups rather than one at a time. The default is groups of 10 records. The records are buffered, and a program processes these records one at a time. A database fetch operation is therefore performed after every 10 records, instead of after every single record. This is a substantial performance gain. If the database server is on another computer, then network traffic is also significantly reduced.
Modify the number of records to fetch together by using the -B command-line flag or, for an individual BEGIN-SELECT command, by using its -B option. In both cases, specify the number of records to be fetched together. For example -B100 specifies that records be fetched in groups of 100. This means that the number of database fetch operations is further reduced.
This feature is currently available with SQR for ODBC and SQR for the Oracle or SYBASE databases.
Running Programs on the Database Server
To reduce network traffic and improve performance, run SQR programs directly on the database server machine. The SQR server is available on many server platforms including Windows NT and UNIX/Linux.
SQR Programming Principles
• Develop all SQRs using Structured Programming
Structured Programming
A technique for organizing and coding computer programs in which a hierarch of modules issued, each having a single entry and a single exit point, and in which control is passed downward through the structure without unconditional branches to higher levels of the structure.
Structured programming is often associated with a top-down approach to design. In this way designers map out the large scale structure of a program in terms of smaller operations, implement and test the smaller operations, and then tie them together into a whole program.
• Pseudo-Code
o Flowchart the program logic
o Research and ask a few questions:
􀂃 How much data will be pulled? Will it be100 rows or 1000 rows?
􀂃 How many rows will come from each table?
􀂃 What kind of data will be pulled? Student Data? Setup Data?
􀂃 What does the key structure look like for the tables being pulled?
􀂃 Is this table the parent or the child?
􀂃 How important is this table?
􀂃 What fields will be needed from this table? Are they available somewhere else?
o Write SQL and test in SQL Plus before coding SQR
• Use Linear Programming – easier to program and debug
• Comment
Summary
The following techniques can be used to improve the performance of your SQR programs:
• Simplify complex SELECT statements.
• Use LOAD-LOOKUP to simplify joins.
• Use dynamic SQL instead of a condition in a SELECT statement.
• Avoid using temporary database tables. Two alternatives to temporary
database tables are SQR arrays and flat files.
• Write programs that create multiple reports with one pass on the data.
• Use the most efficient numeric type for numeric variables (machine
floating point, decimal, or integer).
• Save compiled SQR programs and rerun them with SQR Execute.
• Adjust settings in the [Processing-Limits] section of SQR.INI or in a
startup file.
• Increase buffering of rows in SELECT statements with the -B flag.
• Execute programs on the database server machine.
• SQR Programming Principles
12/6/09
Smart View
Smart View provides a common Microsoft Office interface for the following EPM Workspace components:Financial Reporting,Production Reporting,Web Analysis,Interactive Reporting.It also provides a common Microsoft Office interface for Essbase, Financial Management and Planning.
The centralized interface enables simultaneous use of multiple Hyperion products and improves integration with Microsoft Office (2000, 2002, and 2003). The Smart View implementation provides the following EPM Workspace functionality:
. Exports the current page of the current data object to Excel, Word, or PowerPoint
. Exposes Financial Management and Essbase functions in Excel, Word, and PowerPoint content
The centralized interface enables simultaneous use of multiple Hyperion products and improves integration with Microsoft Office (2000, 2002, and 2003). The Smart View implementation provides the following EPM Workspace functionality:
. Exports the current page of the current data object to Excel, Word, or PowerPoint
. Exposes Financial Management and Essbase functions in Excel, Word, and PowerPoint content
Hyperion Workspace Management Reporting
Financial Report(Grid, chart, POV, prompt, OLAP)
- Dynamic report (*.des)
- Snapshot reports (*.rpt)
- Books (*.kbk)
- Snapshot Books (*.kbt)
- Batch files (*.bch)
Web Analysis (.apt) (Essbase, RLDB, Drill through)
- Spreadsheet
- Chart
- Pinboard
- SQL spreadsheet
- Freeform grid
Interactive Report(.bqy)
Production Report(.sqr)
Excel Spreadsheet Addin report (can be imported into workspace, add user permission)
Job Scheduling for Financial Report:
- Scheduling batch for financial report on recurring time based
- Command Line Scheduler for financial report batch
(You can use an external scheduler to schedule batch jobs in EPM Workspace
You automate the process of launching batches using an external scheduler or launching batches after an external event occurs, such as the completion of a consolidation.)
Interactive Report(.bqy) and .Production Report(.sqr)
- Time based event
- External Trigger Event
Associate job with the events
Method to trigger the External Event:
- Java method: call ExternalTriggerEvent class, triggerEXE() method
- PL/SQL method: updated Hyperion.v8_trigger table with new date time
- Dynamic report (*.des)
- Snapshot reports (*.rpt)
- Books (*.kbk)
- Snapshot Books (*.kbt)
- Batch files (*.bch)
Web Analysis (.apt) (Essbase, RLDB, Drill through)
- Spreadsheet
- Chart
- Pinboard
- SQL spreadsheet
- Freeform grid
Interactive Report(.bqy)
Production Report(.sqr)
Excel Spreadsheet Addin report (can be imported into workspace, add user permission)
Job Scheduling for Financial Report:
- Scheduling batch for financial report on recurring time based
- Command Line Scheduler for financial report batch
(You can use an external scheduler to schedule batch jobs in EPM Workspace
You automate the process of launching batches using an external scheduler or launching batches after an external event occurs, such as the completion of a consolidation.)
Interactive Report(.bqy) and .Production Report(.sqr)
- Time based event
- External Trigger Event
Associate job with the events
Method to trigger the External Event:
- Java method: call ExternalTriggerEvent class, triggerEXE() method
- PL/SQL method: updated Hyperion.v8_trigger table with new date time
12/5/09
Java - Trigger External Event
External Event Based job can be scheduled in Hyperion Portal Server, the key is to trigger the Event when the backend data loading is finished. The next code is about how to trigger the external event using Java method.
import com.sqribe.rm.*;
import java.util.Properties;
/*** example class for triggering an External event */
public class TriggerExternalEvent
{
public TriggerExternalEvent(String [] args)
{
try
{
Session session;
//--------------------------------------------------------------------
// obtain a session connection
//--------------------------------------------------------------------
if (args.length < 5) { throw new IndexOutOfBoundsException(); } else { session = SessionFactory.getInstance(args[0], args[1], args[2], new Integer(args[3]).intValue()); } System.out.println("connection established"); //---------------------------------------------------------------- // trigger external event //---------------------------------------------------------------- try { String EventName = args[4]; Scheduler sch = session.getScheduler(); ExternallyTriggeredEvent MyEvent = null; MyEvent = (ExternallyTriggeredEvent) .getExternallyTriggeredEvent(EventName); System.out.println("Event found..."); MyEvent.triggerETE(); System.out.println("Triggerred Event :: " + EventName); } catch (ReportMartException e) { System.out.println("Event not found"); e.printStackTrace(); } //---------------------------------------------------------------- // close the session when you no longer need it //---------------------------------------------------------------- session.close(); System.out.println("connection closed"); } catch (ReportMartException e) { System.out.println("connection could not be established"); e.printStackTrace(); } } /** * program entry point *
import com.sqribe.rm.*;
import java.util.Properties;
/*** example class for triggering an External event */
public class TriggerExternalEvent
{
public TriggerExternalEvent(String [] args)
{
try
{
Session session;
//--------------------------------------------------------------------
// obtain a session connection
//--------------------------------------------------------------------
if (args.length < 5) { throw new IndexOutOfBoundsException(); } else { session = SessionFactory.getInstance(args[0], args[1], args[2], new Integer(args[3]).intValue()); } System.out.println("connection established"); //---------------------------------------------------------------- // trigger external event //---------------------------------------------------------------- try { String EventName = args[4]; Scheduler sch = session.getScheduler(); ExternallyTriggeredEvent MyEvent = null; MyEvent = (ExternallyTriggeredEvent) .getExternallyTriggeredEvent(EventName); System.out.println("Event found..."); MyEvent.triggerETE(); System.out.println("Triggerred Event :: " + EventName); } catch (ReportMartException e) { System.out.println("Event not found"); e.printStackTrace(); } //---------------------------------------------------------------- // close the session when you no longer need it //---------------------------------------------------------------- session.close(); System.out.println("connection closed"); } catch (ReportMartException e) { System.out.println("connection could not be established"); e.printStackTrace(); } } /** * program entry point *
* @param String [] args - command line arguments
*/
public static void main(String [] args)
{
try
{
new TriggerExternalEvent(args);
}
catch (IndexOutOfBoundsException e)
{
System.err.println("usage: TriggerExternalEvent
}
}
}
12/2/09
Java - Run Essbase Report Script
/*
RunReport.java
*/
package com.essbase.samples.japi;
import com.essbase.api.base.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.*;
//import com.essbase.api.dataquery.*;
import com.essbase.api.domain.*;
//import com.essbase.api.metadata.*;
/**RunReport Example does the following: Signs on to essbase domain,
Runs a report and Signs Off.
In order for this sample to work in your environment, make sure to
change the s_* variables to suit your environment.
*/
public class RunReport {
// NOTE: Change the following variables to suit your setup.
private static String s_userName = "admin";
private static String s_password = "password";
private static String s_olapSvrName = "bobpc";
/* Possible values for s_provider:
"Embedded" or "http://localhost:13080/aps/JAPI" */
private static String s_provider = "Embedded"; // Default
private static final int FAILURE_CODE = 1;
public static void main(String[] args) {
int statusCode = 0;
IEssbase ess = null;
IEssOlapServer olapSvr = null;
try {
acceptArgs(args);
// Create JAPI instance.
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
// Sign On to the Provider
IEssDomain dom
= ess.signOn(s_userName, s_password, false, null, s_provider);
// Open connection with OLAP server and get the cube.
olapSvr = (IEssOlapServer)dom.getOlapServer(s_olapSvrName);
olapSvr.connect();
IEssCube cube = olapSvr.getApplication("Sample").getCube("Basic");
executeReport(cube);
System.out.println("\nReport Execution Sample Completed.");
} catch (EssException x) {
System.out.println("Error: " + x.getMessage());
statusCode = FAILURE_CODE;
} finally {
// Close OLAP server connection and sign off from the domain.
try {
if (olapSvr != null && olapSvr.isConnected() == true)
olapSvr.disconnect();
} catch (EssException x) {
System.err.println("Error: " + x.getMessage());
}
try {
if (ess != null && ess.isSignedOn() == true)
ess.signOff();
} catch (EssException x) {
System.err.println("Error: " + x.getMessage());
}
}
// Set status to failure only if exception occurs and do abnormal termination
// otherwise, it will by default terminate normally
if (statusCode == FAILURE_CODE) System.exit(FAILURE_CODE);
}
static void executeReport(IEssCube cube) throws EssException {
try {
// Execute a report by passing the specification string.
String repSpec = "{TABDELIMIT}" +
"{SUPALL COLHEADING NAMESON BLOCKHEADERS PAGEHEAD INDENTGEN 2 DECIMALS VARIABLE}" +
"
"
"{INCZEROROWS}" +
"{MISSINGTEXT \"n/a\"}
"
"{ OUTALTNAMES }" +
"
System.out.println("Executing a report by passing the specification string....");
System.out.println("\n\nReport Output for spec: "+repSpec+"\n----------------"+
"------------------------------------------------------------");
String output = cube.report(repSpec, true, false);
System.out.println(output);
// Execute a report by passing file name in the server.
String reportFile = "Top";
System.out.println("\nExecuting a report by passing file name in the server....");
System.out.println("\n\nReport Output for file: " + reportFile +
"\n-----------------------------");
output = cube.report(true, false, reportFile, false);
System.out.println(output);
// Copy a report file from server to JAPI client, and execute the
// report by passing this client file name.
reportFile = System.getProperty("java.io.tmpdir")+"Bottom.rep";
cube.copyOlapFileObjectFromServer(IEssOlapFileObject.TYPE_REPORT,
"Bottom", reportFile, false);
System.out.println("\n\nReport Output for file: " + reportFile +
"\n-----------------------------");
output = cube.report(true, false, reportFile, true);
System.out.println(output);
// Execute a report and get the output as an iterator. This is
// useful when report returns large resultset.
reportFile = "Asym";
System.out.println("\nExecuting a report to get output as an iterator....");
System.out.println("\n\nReport Output for file: " + reportFile +
"\n-----------------------------");
IEssSequentialIterator iterator = cube.report(reportFile, true, false,
true, false);
while ((iterator.isEndOfData() == false) &&
((output = iterator.getNextString()) != null))
System.out.println(output);
} catch (EssException x) {
System.err.println("Error: " + x.getMessage());
}
}
static void acceptArgs(String[] args) throws EssException {
if (args.length >= 4) {
s_userName = args[0];
s_password = args[1];
s_olapSvrName = args[2];
s_provider = args[3]; //PROVIDER
} else if (args.length != 0) {
System.err.println("ERROR: Incorrect Usage of this sample.");
System.err.println(
"Usage: java " + RunReport.class.getName() + "");
System.exit(1); // Simply end
}
}
}
RunReport.java
*/
package com.essbase.samples.japi;
import com.essbase.api.base.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.*;
//import com.essbase.api.dataquery.*;
import com.essbase.api.domain.*;
//import com.essbase.api.metadata.*;
/**RunReport Example does the following: Signs on to essbase domain,
Runs a report and Signs Off.
In order for this sample to work in your environment, make sure to
change the s_* variables to suit your environment.
*/
public class RunReport {
// NOTE: Change the following variables to suit your setup.
private static String s_userName = "admin";
private static String s_password = "password";
private static String s_olapSvrName = "bobpc";
/* Possible values for s_provider:
"Embedded" or "http://localhost:13080/aps/JAPI" */
private static String s_provider = "Embedded"; // Default
private static final int FAILURE_CODE = 1;
public static void main(String[] args) {
int statusCode = 0;
IEssbase ess = null;
IEssOlapServer olapSvr = null;
try {
acceptArgs(args);
// Create JAPI instance.
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
// Sign On to the Provider
IEssDomain dom
= ess.signOn(s_userName, s_password, false, null, s_provider);
// Open connection with OLAP server and get the cube.
olapSvr = (IEssOlapServer)dom.getOlapServer(s_olapSvrName);
olapSvr.connect();
IEssCube cube = olapSvr.getApplication("Sample").getCube("Basic");
executeReport(cube);
System.out.println("\nReport Execution Sample Completed.");
} catch (EssException x) {
System.out.println("Error: " + x.getMessage());
statusCode = FAILURE_CODE;
} finally {
// Close OLAP server connection and sign off from the domain.
try {
if (olapSvr != null && olapSvr.isConnected() == true)
olapSvr.disconnect();
} catch (EssException x) {
System.err.println("Error: " + x.getMessage());
}
try {
if (ess != null && ess.isSignedOn() == true)
ess.signOff();
} catch (EssException x) {
System.err.println("Error: " + x.getMessage());
}
}
// Set status to failure only if exception occurs and do abnormal termination
// otherwise, it will by default terminate normally
if (statusCode == FAILURE_CODE) System.exit(FAILURE_CODE);
}
static void executeReport(IEssCube cube) throws EssException {
try {
// Execute a report by passing the specification string.
String repSpec = "{TABDELIMIT}" +
"{SUPALL COLHEADING NAMESON BLOCKHEADERS PAGEHEAD INDENTGEN 2 DECIMALS VARIABLE}" +
"
"
"{INCZEROROWS}" +
"{MISSINGTEXT \"n/a\"}
"
"{ OUTALTNAMES }" +
"
System.out.println("Executing a report by passing the specification string....");
System.out.println("\n\nReport Output for spec: "+repSpec+"\n----------------"+
"------------------------------------------------------------");
String output = cube.report(repSpec, true, false);
System.out.println(output);
// Execute a report by passing file name in the server.
String reportFile = "Top";
System.out.println("\nExecuting a report by passing file name in the server....");
System.out.println("\n\nReport Output for file: " + reportFile +
"\n-----------------------------");
output = cube.report(true, false, reportFile, false);
System.out.println(output);
// Copy a report file from server to JAPI client, and execute the
// report by passing this client file name.
reportFile = System.getProperty("java.io.tmpdir")+"Bottom.rep";
cube.copyOlapFileObjectFromServer(IEssOlapFileObject.TYPE_REPORT,
"Bottom", reportFile, false);
System.out.println("\n\nReport Output for file: " + reportFile +
"\n-----------------------------");
output = cube.report(true, false, reportFile, true);
System.out.println(output);
// Execute a report and get the output as an iterator. This is
// useful when report returns large resultset.
reportFile = "Asym";
System.out.println("\nExecuting a report to get output as an iterator....");
System.out.println("\n\nReport Output for file: " + reportFile +
"\n-----------------------------");
IEssSequentialIterator iterator = cube.report(reportFile, true, false,
true, false);
while ((iterator.isEndOfData() == false) &&
((output = iterator.getNextString()) != null))
System.out.println(output);
} catch (EssException x) {
System.err.println("Error: " + x.getMessage());
}
}
static void acceptArgs(String[] args) throws EssException {
if (args.length >= 4) {
s_userName = args[0];
s_password = args[1];
s_olapSvrName = args[2];
s_provider = args[3]; //PROVIDER
} else if (args.length != 0) {
System.err.println("ERROR: Incorrect Usage of this sample.");
System.err.println(
"Usage: java " + RunReport.class.getName() + "
System.exit(1); // Simply end
}
}
}
11/29/09
Essbase Excel Report
The report developing with Excel is very simple. The way is:
1. Design an interactive interface on Excel, that include: listing box, drop down box, text box, label, button, radio option, check box,etc. For example, the region listing box, the product category listing box, the process(data retrieve) button, the submit or update button.
2. Add proper VBA code and call VBA MACRO functions on these control boxes. Macro such as: ESSConnect(), ESSMenu(), ESSRetrieve(),etc
3. Then we can run the on demand interactive Excel report to make special query on the Essbase data, and navigate on different sheets.
4. The VBA code related to these control boxes can be saved as a VBA function library, so that the code can be reused on other Excel report
The next is a simple but standard sample:
Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal username As Variant, ByVal password As Variant, ByVal server As Variant, ByVal application As Variant, ByVal database As Variant) As Long
Declare Function EssVGetMemberInfo Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal mbrName As Variant, ByVal action As Variant, ByVal aliases As Variant) As Variant
Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVFreeMemberInfo Lib "ESSEXCLN.XLL" (ByRef memInfo As Variant) As Long
Const EssBottomLevel = 3
Const EssChildLevel = 1
Const username = "admin"
Const password = "password"
Const ServerName = "bobpc"
Const AppllicationName = "Sample"
Const EssbaseDataBase = "Basic"
Sub Disconnect()
sFileName = ThisWorkbook.Name
For Each wsSheet In Worksheets
X = EssVDisconnect(sFileName & wsSheet.Name)
Next wsSheet
If X = 0 Then
answer = MsgBox("Disconnected!", 64, "Connection Dialog")
End If
End Sub
Sub lstRegion_Change()
v_ListIndex = Sheet1.Shapes(2).ControlFormat.ListIndex
v_ListText = Sheet1.Shapes(2).ControlFormat.List(v_ListIndex)
Call GetBottom(v_ListText)
End Sub
Sub UnselectListBox(ByVal ListBoxNam As Variant)
End Sub
Sub Refresh()
Call GetChild("Market")
Call GetBottom("Market")
End Sub
Sub Process()
End Sub
Sub Submit()
End Sub
Sub Export()
With Sheet1
Set cb = .Shapes.AddFormControl(xlCheckBox, 10, 10, 100, 10)
cb.ControlFormat.LinkedCell = "A1"
End With
End Sub
Sub Quit()
End Sub
Sub Actual()
'MsgBox (Sheet1.Shapes.Item(3).Name)
End Sub
Sub Budget()
'MsgBox (Sheet1.Shapes(3).ControlFormat.LinkedCell
End Sub
Sub Conn()
sFileName = ThisWorkbook.Name
For Each wsSheet In Worksheets
X = EssVConnect(sFileName & wsSheet.Name, username, password, ServerName, AppllicationName, EssbaseDataBase)
Next wsSheet
If X = 0 Then
' answer = MsgBox("Connected!", 64, "Connection Dialog")
Else
answer = MsgBox("not Connected!", 64, "Connection Dialog")
End If
End Sub
Public Sub Tester()
Dim rng As Range
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range(LastRow - 1)
rng.Select
End Sub
Sub SelectFirstToLastInColumn()
' Select From the First NonBlank to the Last Nonblank in the Column
Cells(1, 1).Select
Set TopCell = Cells(1, ActiveCell.Column)
Set BottomCell = Cells(Rows.Count, ActiveCell.Column)
If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then
ActiveCell.Select
Else
Range(TopCell, BottomCell).Select
End If
End Sub
Sub GetChild(ByVal mbrName As Variant)
Call Conn
Dim vt As Variant
Dim cbItems As Variant
Dim i As Integer
Dim pMember As String
vt = EssVGetMemberInfo(ThisWorkbook.Name & "Sheet1", mbrName, EssChildLevel, False)
If IsArray(vt) Then
cbItems = UBound(vt) + 1
For i = 0 To UBound(vt)
Sheet1.Cells(i + 1, 1) = vt(i)
Next
Else
MsgBox ("Return Value = " + Str(vt))
End If
' Select From the First NonBlank to the Last Nonblank in the Column
Cells(1, 1).Select
Set TopCell = Cells(1, ActiveCell.Column)
Set BottomCell = Cells(Rows.Count, ActiveCell.Column)
If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then
ActiveCell.Select
Else
Range(TopCell, BottomCell).Select
End If
Sheet1.Shapes(2).ControlFormat.ListFillRange = Range(TopCell, BottomCell).Address
'MsgBox (Worksheets(1).Shapes(2).Name)
X = EssVFreeMemberInfo(vt)
End Sub
Sub lstCity_Clear()
For i = 1 To 20
Sheet1.Cells(i, 2) = Null
Next
End Sub
Sub GetBottom(ByVal mbrName As Variant)
Call lstCity_Clear
Dim vt As Variant
Dim cbItems As Variant
Dim i As Integer
Dim pMember As String
vt = EssVGetMemberInfo(ThisWorkbook.Name & "Sheet1", mbrName, EssBottomLevel, False)
If IsArray(vt) Then
cbItems = UBound(vt) + 1
For i = 0 To UBound(vt)
Sheet1.Cells(i + 1, 2) = vt(i)
Next
Else
MsgBox ("Return Value = " + Str(vt))
End If
Cells(1, 2).Select
Set TopCell = Cells(1, ActiveCell.Column)
Set BottomCell = Cells(Rows.Count, ActiveCell.Column)
If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then
ActiveCell.Select
Else
Range(TopCell, BottomCell).Select
End If
Sheet1.Shapes(3).ControlFormat.ListFillRange = Range(TopCell, BottomCell).Address
X = EssVFreeMemberInfo(vt)
End Sub
1. Design an interactive interface on Excel, that include: listing box, drop down box, text box, label, button, radio option, check box,etc. For example, the region listing box, the product category listing box, the process(data retrieve) button, the submit or update button.
2. Add proper VBA code and call VBA MACRO functions on these control boxes. Macro such as: ESSConnect(), ESSMenu(), ESSRetrieve(),etc
3. Then we can run the on demand interactive Excel report to make special query on the Essbase data, and navigate on different sheets.
4. The VBA code related to these control boxes can be saved as a VBA function library, so that the code can be reused on other Excel report
The next is a simple but standard sample:
Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal username As Variant, ByVal password As Variant, ByVal server As Variant, ByVal application As Variant, ByVal database As Variant) As Long
Declare Function EssVGetMemberInfo Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal mbrName As Variant, ByVal action As Variant, ByVal aliases As Variant) As Variant
Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVFreeMemberInfo Lib "ESSEXCLN.XLL" (ByRef memInfo As Variant) As Long
Const EssBottomLevel = 3
Const EssChildLevel = 1
Const username = "admin"
Const password = "password"
Const ServerName = "bobpc"
Const AppllicationName = "Sample"
Const EssbaseDataBase = "Basic"
Sub Disconnect()
sFileName = ThisWorkbook.Name
For Each wsSheet In Worksheets
X = EssVDisconnect(sFileName & wsSheet.Name)
Next wsSheet
If X = 0 Then
answer = MsgBox("Disconnected!", 64, "Connection Dialog")
End If
End Sub
Sub lstRegion_Change()
v_ListIndex = Sheet1.Shapes(2).ControlFormat.ListIndex
v_ListText = Sheet1.Shapes(2).ControlFormat.List(v_ListIndex)
Call GetBottom(v_ListText)
End Sub
Sub UnselectListBox(ByVal ListBoxNam As Variant)
End Sub
Sub Refresh()
Call GetChild("Market")
Call GetBottom("Market")
End Sub
Sub Process()
End Sub
Sub Submit()
End Sub
Sub Export()
With Sheet1
Set cb = .Shapes.AddFormControl(xlCheckBox, 10, 10, 100, 10)
cb.ControlFormat.LinkedCell = "A1"
End With
End Sub
Sub Quit()
End Sub
Sub Actual()
'MsgBox (Sheet1.Shapes.Item(3).Name)
End Sub
Sub Budget()
'MsgBox (Sheet1.Shapes(3).ControlFormat.LinkedCell
End Sub
Sub Conn()
sFileName = ThisWorkbook.Name
For Each wsSheet In Worksheets
X = EssVConnect(sFileName & wsSheet.Name, username, password, ServerName, AppllicationName, EssbaseDataBase)
Next wsSheet
If X = 0 Then
' answer = MsgBox("Connected!", 64, "Connection Dialog")
Else
answer = MsgBox("not Connected!", 64, "Connection Dialog")
End If
End Sub
Public Sub Tester()
Dim rng As Range
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range(LastRow - 1)
rng.Select
End Sub
Sub SelectFirstToLastInColumn()
' Select From the First NonBlank to the Last Nonblank in the Column
Cells(1, 1).Select
Set TopCell = Cells(1, ActiveCell.Column)
Set BottomCell = Cells(Rows.Count, ActiveCell.Column)
If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then
ActiveCell.Select
Else
Range(TopCell, BottomCell).Select
End If
End Sub
Sub GetChild(ByVal mbrName As Variant)
Call Conn
Dim vt As Variant
Dim cbItems As Variant
Dim i As Integer
Dim pMember As String
vt = EssVGetMemberInfo(ThisWorkbook.Name & "Sheet1", mbrName, EssChildLevel, False)
If IsArray(vt) Then
cbItems = UBound(vt) + 1
For i = 0 To UBound(vt)
Sheet1.Cells(i + 1, 1) = vt(i)
Next
Else
MsgBox ("Return Value = " + Str(vt))
End If
' Select From the First NonBlank to the Last Nonblank in the Column
Cells(1, 1).Select
Set TopCell = Cells(1, ActiveCell.Column)
Set BottomCell = Cells(Rows.Count, ActiveCell.Column)
If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then
ActiveCell.Select
Else
Range(TopCell, BottomCell).Select
End If
Sheet1.Shapes(2).ControlFormat.ListFillRange = Range(TopCell, BottomCell).Address
'MsgBox (Worksheets(1).Shapes(2).Name)
X = EssVFreeMemberInfo(vt)
End Sub
Sub lstCity_Clear()
For i = 1 To 20
Sheet1.Cells(i, 2) = Null
Next
End Sub
Sub GetBottom(ByVal mbrName As Variant)
Call lstCity_Clear
Dim vt As Variant
Dim cbItems As Variant
Dim i As Integer
Dim pMember As String
vt = EssVGetMemberInfo(ThisWorkbook.Name & "Sheet1", mbrName, EssBottomLevel, False)
If IsArray(vt) Then
cbItems = UBound(vt) + 1
For i = 0 To UBound(vt)
Sheet1.Cells(i + 1, 2) = vt(i)
Next
Else
MsgBox ("Return Value = " + Str(vt))
End If
Cells(1, 2).Select
Set TopCell = Cells(1, ActiveCell.Column)
Set BottomCell = Cells(Rows.Count, ActiveCell.Column)
If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then
ActiveCell.Select
Else
Range(TopCell, BottomCell).Select
End If
Sheet1.Shapes(3).ControlFormat.ListFillRange = Range(TopCell, BottomCell).Address
X = EssVFreeMemberInfo(vt)
End Sub
BSO Intelligent Calculation
You can calculate the correct results by telling Essbase not to mark the calculated data blocks as
clean. The following calculation script calculates the correct results:
SET CLEARUPDATESTATUS OFF;
FIX(“New York”)
CALC DIM(Product);
ENDFIX
SET CLEARUPDATESTATUS AFTER;
FIX(“New York”)
CALC DIM(Year);
ENDFIX
With the SET CLEARUPDATESTATUS OFF command, Essbase calculates dirty data blocks but
does not to mark them as clean, unlike the SET CLEARUPDATESTATUS AFTER command.
This solution assumes that the data blocks are not marked as clean from a previous partial
calculation of the database.
You can ensure that all data blocks are calculated, regardless of their status, by disabling
Intelligent Calculation. The following calculation script calculates all specified data blocks,
regardless of their clean or dirty status:
SET UPDATECALC OFF;
FIX(“New York”)
CALC DIM(Year, Product);
ENDFIX
Because you have not used the SET CLEARUPDATESTATUS AFTER command, Essbase does
not mark calculated data blocks as clean.
clean. The following calculation script calculates the correct results:
SET CLEARUPDATESTATUS OFF;
FIX(“New York”)
CALC DIM(Product);
ENDFIX
SET CLEARUPDATESTATUS AFTER;
FIX(“New York”)
CALC DIM(Year);
ENDFIX
With the SET CLEARUPDATESTATUS OFF command, Essbase calculates dirty data blocks but
does not to mark them as clean, unlike the SET CLEARUPDATESTATUS AFTER command.
This solution assumes that the data blocks are not marked as clean from a previous partial
calculation of the database.
You can ensure that all data blocks are calculated, regardless of their status, by disabling
Intelligent Calculation. The following calculation script calculates all specified data blocks,
regardless of their clean or dirty status:
SET UPDATECALC OFF;
FIX(“New York”)
CALC DIM(Year, Product);
ENDFIX
Because you have not used the SET CLEARUPDATESTATUS AFTER command, Essbase does
not mark calculated data blocks as clean.
Optimizing Parallel Data Load Processing
Although Essbase uses parallel processing to optimize processor resources across the data load stages, processor resources are idle at times. To take advantage of these times, Essbase can further divide record processing in the preparation and write stages. To tailor parallel processing to your situation, you can use the DLTHREADSPREPARE and DLTHREADSWRITE essbase.cfg settings to tell Essbase to use additional threads during these stages.
DLTHREADSPREPARE: Specifies how many threads Essbase may use during the data load stage that codifies and organizes the data in preparation to being written to blocks in memory.
DLTHREADSWRITE: Specifies how many threads Essbase may use during the data load stage that writes data to the disk. High values may require allocation of additional cache. See Implications in Sizing the Data Cache.
For ASO, Essbase Server uses one thread with aggregate storage cache. The DLTHREADSWRITE setting is ignored.
DLSINGLETHREADPERSTAGE: Specifies that Essbase use a single thread per stage, ignoring the values in the DLTHREADSPREPARE and DLTHREADSWRITE settings
DLTHREADSPREPARE: Specifies how many threads Essbase may use during the data load stage that codifies and organizes the data in preparation to being written to blocks in memory.
DLTHREADSWRITE: Specifies how many threads Essbase may use during the data load stage that writes data to the disk. High values may require allocation of additional cache. See Implications in Sizing the Data Cache.
For ASO, Essbase Server uses one thread with aggregate storage cache. The DLTHREADSWRITE setting is ignored.
DLSINGLETHREADPERSTAGE: Specifies that Essbase use a single thread per stage, ignoring the values in the DLTHREADSPREPARE and DLTHREADSWRITE settings
11/28/09
Java - backup Essbase
We can create Java procedure that will automatically backup the Essbase database, and even recover the essbase database when necessary.
package com.essbase.samples.japi;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.io.File;
import com.essbase.api.base.EssException;
import com.essbase.api.datasource.EssSEQID;
import com.essbase.api.datasource.EssTRANSACTION;
import com.essbase.api.datasource.EssTRANSACTION_REPLAY;
import com.essbase.api.datasource.IEssCube;
import com.essbase.api.datasource.IEssOlapFileObject;
import com.essbase.api.datasource.IEssOlapServer;
import com.essbase.api.domain.IEssDomain;
import com.essbase.api.session.IEssbase;
/**
* Signs on to essbase domain,
* creates a App and Cube, backups the database(cube) and then restores it.
* In order for this sample to work in your environment, make sure to
* change the s_* variables to suit your environment.
*
* @author
* @version
*/
public class BackupAndRestoreDatabase {
// NOTE: Change the following variables to suit your setup.
private static String s_userName = "system";
private static String s_password = "password";
private static String s_olapSvrName = "localhost";
/* Possible values for s_provider:
"Embedded" or "http://localhost:13080/aps/JAPI" */
private static String s_provider = "Embedded"; // Default
private static final int FAILURE_CODE = 1;
public static void main(String[] args) {
int statusCode = 0;
IEssbase ess = null;
IEssOlapServer olapSvr = null;
try {
acceptArgs(args);
// Create JAPI instance.
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
// Sign On to the Provider
IEssDomain dom = ess.signOn(s_userName, s_password, false, null,s_provider);
// Open connection with OLAP server and get the cube.
olapSvr = (IEssOlapServer) dom.getOlapServer(s_olapSvrName);
olapSvr.connect();
try {
// Delete the App if it already exists
olapSvr.getApplication("BackUp").delete();
} catch (EssException x) {
// Ignore Error
}
// Create a new Application/Cube : BackUp/Basic - Copy of Sample/Basic
olapSvr.createApplication("BackUp");
dom.copyCube(s_olapSvrName, "Sample", "Basic", s_olapSvrName, "BackUp","Basic");
olapSvr.disconnect();
olapSvr.connect();
IEssCube cube = olapSvr.getApplication("BackUp").getCube("Basic");
BackUpAndRestore(cube);
System.out.println("Cube Archive and Restore Sample completed.");
// Transaction logging requires the below essbase property to be set in essbase.cfg. Choose one
// of the below ways to set it.
// TransactionLogLocation AppName DbName LogLocation NATIVE ENABLE|DISABLE
// TransactionLogLocation AppName LogLocation NATIVE ENABLE
// TransactionLogLocation LogLocation NATIVE ENABLE
// Ex: TransactionLogLocation Sample Basic D:\Hyperion\AnalyticServices-950\app\Sample\Basic NATVIE ENABLE
// TransactionLoggingAndReplay(cube);
// Delete newly created Application.
olapSvr.getApplication("BackUp").delete();
} catch (EssException x) {
System.out.println("Error: " + x.getMessage());
statusCode = FAILURE_CODE;
} finally {
// Close OLAP server connection and sign off from the domain.
try {
if (olapSvr != null && olapSvr.isConnected() == true)
olapSvr.disconnect();
} catch (EssException x) {
System.out.println("Error: " + x.getMessage());
}
try {
if (ess != null && ess.isSignedOn() == true)
ess.signOff();
} catch (EssException x) {
System.out.println("Error: " + x.getMessage());
}
}
// Set status to failure only if exception occurs and do abnormal termination
// otherwise, it will by default terminate normally
if (statusCode == FAILURE_CODE)
System.exit(FAILURE_CODE);
}
static void TransactionLoggingAndReplay(IEssCube cube) throws EssException {
Calendar cal = Calendar.getInstance();
cal.setTime(new Date());
cal.add(Calendar.DATE, -2);
cal.getTimeInMillis();
SimpleDateFormat fmt = new SimpleDateFormat("MM/dd/yyyy:HH:mm:ss");
String date = fmt.format(cal.getTime());
System.out.println("From Date specified for Transactions is :"+ date);
EssTRANSACTION[] list = cube.listTransactions((short)1, date, IEssCube.ESS_LIST_TRANSACTIONS_TOCLIENT, "");
if (list == null || list.length ==0) {
System.out.println("\nNo transactions to List or Replay since "+ date +".\n"
+"Please comment out the BackUpAndRestore(cube) function call in this sample and\n ensure you have executed a transaction like loaddata prior to running this sample.");
return;
}
for (int i = 0; i < list.length; i++) { System.out.println(list[i] +"\n"); } System.out.println("List transactions complete"); EssTRANSACTION_REPLAY replayTran = new EssTRANSACTION_REPLAY((byte)2, date, 1); EssSEQID[] seqIds = new EssSEQID[1]; seqIds[0] = new EssSEQID(list[0].getSeq_id(), list[0].getSeq_id_upper(), 1, list[0].getSeq_id_upper()); cube.replayTransactions(replayTran, seqIds); System.out.println("Relplay transactions complete"); } static void BackUpAndRestore(IEssCube cube) throws EssException { cube.loadData(true, false, "Product Market Actual Sales Jan 4469\n" + "Product Market Actual Sales Feb 42494"); String ArchiveFile = System.getProperty("java.io.tmpdir") + "demobasic.arc"; cube.archiveDatabase(ArchiveFile, "", true); // Take backup. cube.loadData(IEssOlapFileObject.TYPE_RULES, null, IEssOlapFileObject.TYPE_TEXT, "Calcdat", false); String[] Src = null; String[] Dest = null; // Unload Database before restoring. do { try { Thread.sleep(5000); cube.stop(); break; } catch(EssException x){ // If error occurs in unloading database because database is in use(Error #1013113), // wait for 5 sec. and try again. if(x.getNativeCode() == 1013113) continue; else break; } catch (Exception x) { break; } } while (true); cube.restoreDatabase(ArchiveFile, false, Src, Dest); // Restore database. (new File (ArchiveFile)).delete(); } static void acceptArgs(String[] args) throws EssException { if (args.length >= 4) {
s_userName = args[0];
s_password = args[1];
s_olapSvrName = args[2];
s_provider = args[3]; //PROVIDER
} else if (args.length != 0) {
System.err.println("ERROR: Incorrect Usage of this sample.");
System.err.println("Usage: java " + BackupAndRestoreDatabase.class.getName()
+ " ");
System.exit(1); // Simply end
}
}
}
package com.essbase.samples.japi;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.io.File;
import com.essbase.api.base.EssException;
import com.essbase.api.datasource.EssSEQID;
import com.essbase.api.datasource.EssTRANSACTION;
import com.essbase.api.datasource.EssTRANSACTION_REPLAY;
import com.essbase.api.datasource.IEssCube;
import com.essbase.api.datasource.IEssOlapFileObject;
import com.essbase.api.datasource.IEssOlapServer;
import com.essbase.api.domain.IEssDomain;
import com.essbase.api.session.IEssbase;
/**
* Signs on to essbase domain,
* creates a App and Cube, backups the database(cube) and then restores it.
* In order for this sample to work in your environment, make sure to
* change the s_* variables to suit your environment.
*
* @author
* @version
*/
public class BackupAndRestoreDatabase {
// NOTE: Change the following variables to suit your setup.
private static String s_userName = "system";
private static String s_password = "password";
private static String s_olapSvrName = "localhost";
/* Possible values for s_provider:
"Embedded" or "http://localhost:13080/aps/JAPI" */
private static String s_provider = "Embedded"; // Default
private static final int FAILURE_CODE = 1;
public static void main(String[] args) {
int statusCode = 0;
IEssbase ess = null;
IEssOlapServer olapSvr = null;
try {
acceptArgs(args);
// Create JAPI instance.
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
// Sign On to the Provider
IEssDomain dom = ess.signOn(s_userName, s_password, false, null,s_provider);
// Open connection with OLAP server and get the cube.
olapSvr = (IEssOlapServer) dom.getOlapServer(s_olapSvrName);
olapSvr.connect();
try {
// Delete the App if it already exists
olapSvr.getApplication("BackUp").delete();
} catch (EssException x) {
// Ignore Error
}
// Create a new Application/Cube : BackUp/Basic - Copy of Sample/Basic
olapSvr.createApplication("BackUp");
dom.copyCube(s_olapSvrName, "Sample", "Basic", s_olapSvrName, "BackUp","Basic");
olapSvr.disconnect();
olapSvr.connect();
IEssCube cube = olapSvr.getApplication("BackUp").getCube("Basic");
BackUpAndRestore(cube);
System.out.println("Cube Archive and Restore Sample completed.");
// Transaction logging requires the below essbase property to be set in essbase.cfg. Choose one
// of the below ways to set it.
// TransactionLogLocation AppName DbName LogLocation NATIVE ENABLE|DISABLE
// TransactionLogLocation AppName LogLocation NATIVE ENABLE
// TransactionLogLocation LogLocation NATIVE ENABLE
// Ex: TransactionLogLocation Sample Basic D:\Hyperion\AnalyticServices-950\app\Sample\Basic NATVIE ENABLE
// TransactionLoggingAndReplay(cube);
// Delete newly created Application.
olapSvr.getApplication("BackUp").delete();
} catch (EssException x) {
System.out.println("Error: " + x.getMessage());
statusCode = FAILURE_CODE;
} finally {
// Close OLAP server connection and sign off from the domain.
try {
if (olapSvr != null && olapSvr.isConnected() == true)
olapSvr.disconnect();
} catch (EssException x) {
System.out.println("Error: " + x.getMessage());
}
try {
if (ess != null && ess.isSignedOn() == true)
ess.signOff();
} catch (EssException x) {
System.out.println("Error: " + x.getMessage());
}
}
// Set status to failure only if exception occurs and do abnormal termination
// otherwise, it will by default terminate normally
if (statusCode == FAILURE_CODE)
System.exit(FAILURE_CODE);
}
static void TransactionLoggingAndReplay(IEssCube cube) throws EssException {
Calendar cal = Calendar.getInstance();
cal.setTime(new Date());
cal.add(Calendar.DATE, -2);
cal.getTimeInMillis();
SimpleDateFormat fmt = new SimpleDateFormat("MM/dd/yyyy:HH:mm:ss");
String date = fmt.format(cal.getTime());
System.out.println("From Date specified for Transactions is :"+ date);
EssTRANSACTION[] list = cube.listTransactions((short)1, date, IEssCube.ESS_LIST_TRANSACTIONS_TOCLIENT, "");
if (list == null || list.length ==0) {
System.out.println("\nNo transactions to List or Replay since "+ date +".\n"
+"Please comment out the BackUpAndRestore(cube) function call in this sample and\n ensure you have executed a transaction like loaddata prior to running this sample.");
return;
}
for (int i = 0; i < list.length; i++) { System.out.println(list[i] +"\n"); } System.out.println("List transactions complete"); EssTRANSACTION_REPLAY replayTran = new EssTRANSACTION_REPLAY((byte)2, date, 1); EssSEQID[] seqIds = new EssSEQID[1]; seqIds[0] = new EssSEQID(list[0].getSeq_id(), list[0].getSeq_id_upper(), 1, list[0].getSeq_id_upper()); cube.replayTransactions(replayTran, seqIds); System.out.println("Relplay transactions complete"); } static void BackUpAndRestore(IEssCube cube) throws EssException { cube.loadData(true, false, "Product Market Actual Sales Jan 4469\n" + "Product Market Actual Sales Feb 42494"); String ArchiveFile = System.getProperty("java.io.tmpdir") + "demobasic.arc"; cube.archiveDatabase(ArchiveFile, "", true); // Take backup. cube.loadData(IEssOlapFileObject.TYPE_RULES, null, IEssOlapFileObject.TYPE_TEXT, "Calcdat", false); String[] Src = null; String[] Dest = null; // Unload Database before restoring. do { try { Thread.sleep(5000); cube.stop(); break; } catch(EssException x){ // If error occurs in unloading database because database is in use(Error #1013113), // wait for 5 sec. and try again. if(x.getNativeCode() == 1013113) continue; else break; } catch (Exception x) { break; } } while (true); cube.restoreDatabase(ArchiveFile, false, Src, Dest); // Restore database. (new File (ArchiveFile)).delete(); } static void acceptArgs(String[] args) throws EssException { if (args.length >= 4) {
s_userName = args[0];
s_password = args[1];
s_olapSvrName = args[2];
s_provider = args[3]; //PROVIDER
} else if (args.length != 0) {
System.err.println("ERROR: Incorrect Usage of this sample.");
System.err.println("Usage: java " + BackupAndRestoreDatabase.class.getName()
+ "
System.exit(1); // Simply end
}
}
}
Load huge outline member for ASO
I am building a ASO cube with Essbase(9.3.1) for shipment industry.
outline involve 3 large munber of dimension members:
1. DIM_SHIPPER (1,465,000), max generation is 5
2. NA_STATE (162,300), max generation is 3
3. DIM_CONSINEE (875,200), max generation is 5
4.... others dimension member maximum no more then 100,000. Top 2 dimensions are 80000 and 60000, others only thounsand level.
outline total including 10 dimension (not involve the account dimension).
Question:
1. After I finished loading all dimensions except DIM_SHIPPER, NA_STATE,DIM_CONSINEE. I found I can't open the outline with edit model, and Essbase report 'out of memory'error. At the same time I can't use ODI to load dimension anymore, ODI always report network error and time out error. (I think time out error is because long time no response from Essbase, am I correct?)
Answer:
1. Since can't use ODI to build dimension, so I change the loading ways and use flat file to build dimension. This ways is work, can build dimension in Essbase, but the performance is very poor. I found need to cost nearly 8 hours to finished DIM_CONSINEE dimension building.
2. Check for Disk Space. We usually get this error when there is insufficient disk space to create .pag files.
3. Increase your agent thread count, so research these essbase.cfg settings. I know I have seen that issue in the past with the agent being too busy, and adjusting threads greatly helped.
AGENTTHREADS
AGTSVRCONNECTIONS
SERVERTHREADS
Also, do not make AgtSvrConnections greater than AgentThreads. Note that using more threads will consume more memory, and is very specific to your OS so you probably should ask Oracle support what they recommend based on your specific environment before you change it.
4. Following are the minimum and max settings:
AGENTDELAY 20 60
AGENTTHREADS 2 500
AGTSVRCONNECTIONS 1 7
-----------
Answers:
outline involve 3 large munber of dimension members:
1. DIM_SHIPPER (1,465,000), max generation is 5
2. NA_STATE (162,300), max generation is 3
3. DIM_CONSINEE (875,200), max generation is 5
4.... others dimension member maximum no more then 100,000. Top 2 dimensions are 80000 and 60000, others only thounsand level.
outline total including 10 dimension (not involve the account dimension).
Question:
1. After I finished loading all dimensions except DIM_SHIPPER, NA_STATE,DIM_CONSINEE. I found I can't open the outline with edit model, and Essbase report 'out of memory'error. At the same time I can't use ODI to load dimension anymore, ODI always report network error and time out error. (I think time out error is because long time no response from Essbase, am I correct?)
Answer:
1. Since can't use ODI to build dimension, so I change the loading ways and use flat file to build dimension. This ways is work, can build dimension in Essbase, but the performance is very poor. I found need to cost nearly 8 hours to finished DIM_CONSINEE dimension building.
2. Check for Disk Space. We usually get this error when there is insufficient disk space to create .pag files.
3. Increase your agent thread count, so research these essbase.cfg settings. I know I have seen that issue in the past with the agent being too busy, and adjusting threads greatly helped.
AGENTTHREADS
AGTSVRCONNECTIONS
SERVERTHREADS
Also, do not make AgtSvrConnections greater than AgentThreads. Note that using more threads will consume more memory, and is very specific to your OS so you probably should ask Oracle support what they recommend based on your specific environment before you change it.
4. Following are the minimum and max settings:
AGENTDELAY 20 60
AGENTTHREADS 2 500
AGTSVRCONNECTIONS 1 7
-----------
Answers:
11/25/09
Java Customized Calc Function
The essbase already have many functions for calculation. We can create customized calculation functions by Java. Steps:
1. Create a Java function,CalcFunc.java
public class CalcFunc {
public static double sum (double[] data) {
int i, n = data.length;
double sum = 0.0d;
for (i=0; i< n ; i++)
{
double d = data [i];
sum = sum + d;
}
return sum;
}
}
2. compile,run with javac CalcFunc.java, and generate CalcFunc.class
javac CalcFunc.java
C:\app\bob\product\11.1.0\db_1\jdk\bin\javac.exe C:\Hyperion\CustJava\CalcFunc.java
3. Put the class file in the jar file
jar cf jar_filename class_filename
jar cf CalcFunc.jar CalcFunc.class
4.On the computer running Essbase Server, copy the .jar file to one of the following directories (if the directory does not exist, create it):
For .jar files containing global custom-defined functions:
ARBORPATH/java/udf/
C:\Hyperion\products\Essbase\EssbaseServer\java\udf
For .jar files to be used only with specific applications:
ARBORPATH/app/AppName/udf/
5. Register
create function Sample.'@JSUM'
as 'CalcFunc.sum'
spec '@JSUM(memberRange)'
comment 'adds list of input members';
1. Create a Java function,CalcFunc.java
public class CalcFunc {
public static double sum (double[] data) {
int i, n = data.length;
double sum = 0.0d;
for (i=0; i< n ; i++)
{
double d = data [i];
sum = sum + d;
}
return sum;
}
}
2. compile,run with javac CalcFunc.java, and generate CalcFunc.class
javac CalcFunc.java
C:\app\bob\product\11.1.0\db_1\jdk\bin\javac.exe C:\Hyperion\CustJava\CalcFunc.java
3. Put the class file in the jar file
jar cf jar_filename class_filename
jar cf CalcFunc.jar CalcFunc.class
4.On the computer running Essbase Server, copy the .jar file to one of the following directories (if the directory does not exist, create it):
For .jar files containing global custom-defined functions:
ARBORPATH/java/udf/
C:\Hyperion\products\Essbase\EssbaseServer\java\udf
For .jar files to be used only with specific applications:
ARBORPATH/app/AppName/udf/
5. Register
create function Sample.'@JSUM'
as 'CalcFunc.sum'
spec '@JSUM(memberRange)'
comment 'adds list of input members';
MaxL Perl Functions
1. connect (user, password, host), Sample:
my $dbh = Essbase->connect("user","password", "host");
2. do (statement); a MaxL statement to be passed to the Essbase Server
$dbh->do("display user");
Where "display user" is a valid MaxL statement
3. pop_msg(); Navigates through MaxL status messages one at a time.
Arguments: none.Returns: a list of the form (, , )
4. fetch_desc(); Returns a reference to a row of query results and a reference to a corresponding row of datatypes for the query results.
($column_name, $datatypes) = $dbh->fetch_desc();
5. fetch_row();Returns a reference to a row of query results in a MaxL output table, as a list
6. disconnect();
my $dbh = Essbase->connect("user","password", "host");
2. do (statement); a MaxL statement to be passed to the Essbase Server
$dbh->do("display user");
Where "display user" is a valid MaxL statement
3. pop_msg(); Navigates through MaxL status messages one at a time.
Arguments: none.Returns: a list of the form (
4. fetch_desc(); Returns a reference to a row of query results and a reference to a corresponding row of datatypes for the query results.
($column_name, $datatypes) = $dbh->fetch_desc();
5. fetch_row();Returns a reference to a row of query results in a MaxL output table, as a list
6. disconnect();
MaxL Perl Module
Windows: Download Perl source from http://www.cpan.org/ and build it yourself. You may use ActivePerl, available from http://www.activestate.com/. Before you install the Essbase.pm extension to Perl, ensure that:
You have Perl 5.6 (or higher) installed on your system.You have Microsoft Visual C++ version 6 or higher installed on your system.The Essbase Server is either installed locally, or you have at least the Runtime Client installed and your system's environment is set up to access a remote Essbase Server. Your system should have an environment variable $ESSBASEPATH pointing to the root directory of the Essbase Server installation. In addition, %ESSBASEPATH%\Bin should be included in your path variable. Note:MaxL Perl Module can only be used with the same version Essbase Server.
You have Perl 5.6 (or higher) installed on your system.You have Microsoft Visual C++ version 6 or higher installed on your system.The Essbase Server is either installed locally, or you have at least the Runtime Client installed and your system's environment is set up to access a remote Essbase Server. Your system should have an environment variable $ESSBASEPATH pointing to the root directory of the Essbase Server installation. In addition, %ESSBASEPATH%\Bin should be included in your path variable. Note:MaxL Perl Module can only be used with the same version Essbase Server.
MaxL Shell Commands
Spool on/off
Set Display Column Width
Set Message Level
Set Timestamp
Echo
Shell Escape
Nesting
Error Checking and Branching
Version
Logout
Exit
Set Display Column Width
Set Message Level
Set Timestamp
Echo
Shell Escape
Nesting
Error Checking and Branching
Version
Logout
Exit
11/24/09
MaxL Automation Backup/Recovery
This paper is for the auto backup and recovery process for LDAP Shared Service and Essbase database.
1. In the essbase.cfg file, add:
SPLITARCHIVEFILE TRUE
TRANSACTIONLOGDATALOADARCHIVE SERVER_CLIENT
The 1st command will split big archive file into smaller one automatically. The 2nd command will enable the transaction logging.
2. backupLDAP.bat
The code inside:
@echo off
REM Backup LDAP with HOTBackup
c:\Hyperion\products\Foundation\server\scripts\backup.bat C:\Hyperion\backup\HSS_backup
3. RecoveryLDAP.bat, the code inside:
@echo off
REM Backup LDAP with HOTBackup
c:\Hyperion\products\Foundation\server\scripts\recover.bat C:\Hyperion\backup\HSS_backup
4. BackupEssbaseMaxL.msh
spool on to 'c:\Hyperion\Logs\EssbaseArchive.log';
login $1 $2 on $3;
alter database Sample.Basic force archive to file 'C:\Hyperion\backup\SampleBasic';
alter database Demo.Basic force archive to file 'c:\Hyperion\backup\DemoBasic';
spool off;
5. backupEssbaseBSO.bat
echo off
REM Backup BSO Essbase
set uid=admin
set pwd=password
set svr=bobpc
essmsh "C:\Hyperion\auto\BackupEssbaseMaxL.msh" %uid% %pwd% %svr% %dt% %tm%
ren C:\Hyperion\backup\SampleBasic SampleBasic%date:~4,2%-%date:~7,2%-%date:~10%.arc
ren C:\Hyperion\backup\DemoBasic DemoBasic%date:~4,2%-%date:~7,2%-%date:~10%.arc
The idea is backup with general name, and use bat script rename the backup file with time stamp.
6. RecoveryEssbaseMaxL.msh
spool on to 'c:\Hyperion\Logs\EssbaseArchive.log';
alter database Sample.Basic force restore from file 'C:\Hyperion\backup\SampleBasic.arc';
alter database Demo.Basic force restore from file 'c:\Hyperion\backup\DemoBasic.arc';
alter database Sample.Basic replay transactions using sequence_id_range 2 to 2;
alter database Demo.Basic replay transactions using sequence_id_range 2 to 2;
spool off;
Please select the sequence id range by query
query database Sample.Basic list transactions;
7. RecoveryEssbaseBSO.bat
@echo off
REM Recovery BSO Essbase
essmsh "C:\Hyperion\auto\RecoveryEssbaseMaxL.msh"
The bat file for backup can be scheduled to run daily using Windows Scheduler.
1. In the essbase.cfg file, add:
SPLITARCHIVEFILE TRUE
TRANSACTIONLOGDATALOADARCHIVE SERVER_CLIENT
The 1st command will split big archive file into smaller one automatically. The 2nd command will enable the transaction logging.
2. backupLDAP.bat
The code inside:
@echo off
REM Backup LDAP with HOTBackup
c:\Hyperion\products\Foundation\server\scripts\backup.bat C:\Hyperion\backup\HSS_backup
3. RecoveryLDAP.bat, the code inside:
@echo off
REM Backup LDAP with HOTBackup
c:\Hyperion\products\Foundation\server\scripts\recover.bat C:\Hyperion\backup\HSS_backup
4. BackupEssbaseMaxL.msh
spool on to 'c:\Hyperion\Logs\EssbaseArchive.log';
login $1 $2 on $3;
alter database Sample.Basic force archive to file 'C:\Hyperion\backup\SampleBasic';
alter database Demo.Basic force archive to file 'c:\Hyperion\backup\DemoBasic';
spool off;
5. backupEssbaseBSO.bat
echo off
REM Backup BSO Essbase
set uid=admin
set pwd=password
set svr=bobpc
essmsh "C:\Hyperion\auto\BackupEssbaseMaxL.msh" %uid% %pwd% %svr% %dt% %tm%
ren C:\Hyperion\backup\SampleBasic SampleBasic%date:~4,2%-%date:~7,2%-%date:~10%.arc
ren C:\Hyperion\backup\DemoBasic DemoBasic%date:~4,2%-%date:~7,2%-%date:~10%.arc
The idea is backup with general name, and use bat script rename the backup file with time stamp.
6. RecoveryEssbaseMaxL.msh
spool on to 'c:\Hyperion\Logs\EssbaseArchive.log';
alter database Sample.Basic force restore from file 'C:\Hyperion\backup\SampleBasic.arc';
alter database Demo.Basic force restore from file 'c:\Hyperion\backup\DemoBasic.arc';
alter database Sample.Basic replay transactions using sequence_id_range 2 to 2;
alter database Demo.Basic replay transactions using sequence_id_range 2 to 2;
spool off;
Please select the sequence id range by query
query database Sample.Basic list transactions;
7. RecoveryEssbaseBSO.bat
@echo off
REM Recovery BSO Essbase
essmsh "C:\Hyperion\auto\RecoveryEssbaseMaxL.msh"
The bat file for backup can be scheduled to run daily using Windows Scheduler.
ASO - add huge dim members
ASO always have a lot of members. the most polular methods are generation reference method and children reference method.
1. Create some rule files
2. Create some data files, the data files are not for data loading, but for member creation. Check the consistence between the data file and the rule files
3. If there are too much data members, you may have to use a computer language(such as JavaScript) to generate the better formated data files from a more basic original data file
4. Use Maxl Import command to load data to buffer from the data files in parallel
5. Use Maxl import command to load data from buffer to essbase
import database AsoSamp.Sample data connect as TBC identified by 'password' using multiple rules_file 'rule1','rule2' to load_buffer_block starting with buffer_id 100 on error write to "error.txt";
import database AsoSamp.Sample data from load_buffer with buffer_id 1, 2;
1. Create some rule files
2. Create some data files, the data files are not for data loading, but for member creation. Check the consistence between the data file and the rule files
3. If there are too much data members, you may have to use a computer language(such as JavaScript) to generate the better formated data files from a more basic original data file
4. Use Maxl Import command to load data to buffer from the data files in parallel
5. Use Maxl import command to load data from buffer to essbase
import database AsoSamp.Sample data connect as TBC identified by 'password' using multiple rules_file 'rule1','rule2' to load_buffer_block starting with buffer_id 100 on error write to "error.txt";
import database AsoSamp.Sample data from load_buffer with buffer_id 1, 2;
11/22/09
V11 Service Starting Sequence
The next is the sequence to correctly start the EAS for version 11:
1. Start the RLDB that hold the reporsitory of LDAP, Shared Service, and Essbase Server, in Windows system, it is related the next 3 services:
OracleOraDb11g_home1ConfigurationManager
OracleServiceORCL
OracleOraDb11g_home1TNSListener
2. Start the LDAP service, and the Hyperion Shared Service
Hyperion Foundation OpenLDAP
Hyperion Foundation Shared Services - Web Application
3. Start Essbase Server
Hyperion Essbase Services 11.1.1 - hypservice_1
4. Start the Essbase Administration Service
Hyperion Administration Services - Web Application
http://hyperionexpert.blogspot.com/
1. Start the RLDB that hold the reporsitory of LDAP, Shared Service, and Essbase Server, in Windows system, it is related the next 3 services:
OracleOraDb11g_home1ConfigurationManager
OracleServiceORCL
OracleOraDb11g_home1TNSListener
2. Start the LDAP service, and the Hyperion Shared Service
Hyperion Foundation OpenLDAP
Hyperion Foundation Shared Services - Web Application
3. Start Essbase Server
Hyperion Essbase Services 11.1.1 - hypservice_1
4. Start the Essbase Administration Service
Hyperion Administration Services - Web Application
http://hyperionexpert.blogspot.com/
MDX - Open Inventory
WITH
MEMBER [Measures].[Starting Inventory] AS
'
IIF (
IsLeaf (Year.CurrentMember),
[Measures].[Opening Inventory],
([Measures].[Opening Inventory],
OpeningPeriod (
[Year].Levels(0),
[Year].CurrentMember
))
)'
MEMBER [Measures].[Closing Inventory] AS
'
IIF (
Isleaf(Year.CurrentMember),
[Measures].[Ending Inventory],
([Measures].[Closing Inventory],
ClosingPeriod (
[Year].Levels(0),
[Year].CurrentMember
)
)
)'
SELECT
CrossJoin (
{ [100-10] },
{ [Measures].[Starting Inventory], [Measures].[Closing Inventory] }
)
ON COLUMNS,
Hierarchize ( [Year].Members , POST)
ON ROWS
FROM Sample.Basic
returns the grid:
MEMBER [Measures].[Starting Inventory] AS
'
IIF (
IsLeaf (Year.CurrentMember),
[Measures].[Opening Inventory],
([Measures].[Opening Inventory],
OpeningPeriod (
[Year].Levels(0),
[Year].CurrentMember
))
)'
MEMBER [Measures].[Closing Inventory] AS
'
IIF (
Isleaf(Year.CurrentMember),
[Measures].[Ending Inventory],
([Measures].[Closing Inventory],
ClosingPeriod (
[Year].Levels(0),
[Year].CurrentMember
)
)
)'
SELECT
CrossJoin (
{ [100-10] },
{ [Measures].[Starting Inventory], [Measures].[Closing Inventory] }
)
ON COLUMNS,
Hierarchize ( [Year].Members , POST)
ON ROWS
FROM Sample.Basic
returns the grid:
MDX Query - BottomPercent
WITH
SET [Lowest 5% products] AS
'BottomPercent (
{ [Product].members },
5,
([Measures].[Sales], [Year].[Qtr2])
)'
MEMBER
[Product].[Sum of all lowest prods] AS
'Sum ( [Lowest 5% products] )'
MEMBER [Product].[Percent that lowest sellers hold of all product sales] AS
'Sum ( [Lowest 5% products] ) / [Product] '
SELECT
{[Year].[Qtr2].children}
on columns,
{
[Lowest 5% products],
[Product].[Sum of all lowest prods],
[Product],
[Product].[Percent that lowest sellers hold of all product sales]
}
on rows
FROM Sample.Basic
WHERE ([Measures].[Sales])
SET [Lowest 5% products] AS
'BottomPercent (
{ [Product].members },
5,
([Measures].[Sales], [Year].[Qtr2])
)'
MEMBER
[Product].[Sum of all lowest prods] AS
'Sum ( [Lowest 5% products] )'
MEMBER [Product].[Percent that lowest sellers hold of all product sales] AS
'Sum ( [Lowest 5% products] ) / [Product] '
SELECT
{[Year].[Qtr2].children}
on columns,
{
[Lowest 5% products],
[Product].[Sum of all lowest prods],
[Product],
[Product].[Percent that lowest sellers hold of all product sales]
}
on rows
FROM Sample.Basic
WHERE ([Measures].[Sales])
Subscribe to:
Posts (Atom)
