How to execute SSIS Packate (SQL Agent) with Turbo Integrator

1.- Create SQL Job in SQL Agent that contains the SQL Packate that you want to execute.

2.- Create Storage Procedure that execute the previus SQL Job.

3.- Create TI process to execute previus Storage Procedure

SQL Server - Transact-SQL

1. MODIFY Column Data Type

ALTER TABLE <table name> ALTER COLUMN <Column Name> <New Data Type> <allow null>;

ALTER TABLE TB_EXAM ALTER COLUMN INTEREST_RATE Float null;

2. ADD Column Data Type

ALTER TABLE <table name> ADD <Column Name> <New Data Type> <allow null>;

ALTER TABLE TB_EXAM2 ADD INTEREST_RATE Numeric(18,3) not null;

Export Outline

Export metadata, either from the active database outline or an input outline file, to a specified XML file. Permission required: database manager.

Syntax

You can export metadata information from a database in the following ways using export outline.

Keyword	Description
DBS-NAME	Specify the database name instead of the outline file path.
FILE-NAME	Specify the outline file path instead of the database name.
all dimensions	Export information about all dimensions in the database.
list dimensions	Export information about only the listed dimensions. Specify each dimension name within curly braces, and separated by commas.
tree	Export only the member names in the hierarchy, omitting full metadata details.
with alias_table	Export using only the member names indicated in the specified alias table.
to xml_file	Specify the full path to the output XML file.

Notes

• This statement requires the database to be started.
• The following general outline information is included in the XML export:
  
  • Case sensitiveness
  • Outline Type
  • Duplicate Member Names allowed
  • Typed Measures Enabled
  • Date Format
  • Varying Attributes Enabled
  • Alias Table count and list
  • Active Alias Table
  • Attribute information
  • Auto configure
  • Text list definitions
  • Universal member comments
  • Locale, if it exists
  • Query hint list (if aggregate storage)
  • Get Implied Shared Setting
• The following dimension information is included in the XML export:
  
  • Name
  • Two pass calc
  • Type
  • Text list, if text typed
  • Has relational descendants
  • IsHAEnabled (Hybrid Analysis enabled)?
  • Formula
  • Format String
  • Comment
  • Extended member comment
  • Dimension category
  • Attribute type
  • Data Storage
  • Dimension Storage
  • Alias Names, if any
  • UDAs, if any
  • Consolidation
  • Attribute dimension associated
  • Independent dimensions, if any
  • Time balance
  • Skip options
  • Variance reporting
  • Currency conversion
  • Currency conversion member
  • Dynamic Time Series enabled list
  • Attachment level, if linked attribute dimension
  • Dimension solve order
  • Is Non Unique dimension?
  • Hierarchy type
  • Level usage for aggregation (for aggregate storage hierarchies)
  • Is Compression dimension? (if aggregate storage)
  • Storage category
• The following member information is included in the XML export:
  
  • Name
  • Two pass calc
  • Type
  • Text list, if text typed
  • Is shared?
  • Shared member name, if shared
  • Formula
  • Format string
  • Comment
  • Extended member comment
  • Attribute type
  • Data storage
  • Dimension storage
  • Alias names, if any
  • UDAs, if any
  • Consolidation
  • Attribute member associated
  • Validity sets, if any
  • Time balance
  • Skip options
  • Variance reporting
  • Currency conversion
  • Currency conversion member
  • Member solve order (if aggregate storage)
  • Level usage for aggregation (for aggregate storage hierarchy members)

Example

export outline sample.basic all dimensions to xml_file "c:/temp/basic.xml";

Exports all outline information from Sample Basic to the specified XML file, basic.xml.

export outline sample.basic list dimensions {"Product", "Market"} tree to xml_file "c:/temp/basic.xml";

Exports information about Sample Basic dimensions Product and Market from to the XML file.

Export outline "c:/temp/basic.otl" all dimensions with alias_table "Default" to xml_file "c:/temp/basic.xml";

Exports information about all dimensions in Sample Basic from the specified outline file to the XML file, using only default alias names.

Hyperion: How to change Admin password In Case if you forgot the Admin password in Hyperion 11.1.2.1

Login to your database with the user/password that is the owner of the shared services repository.
Run the following SQL command in Oracle SQL Developer or equivalent tool user by your Relational Database System.

update CSS_USERS
set PASSWORD='{SHA}W6ph5Mm5Pz8GgiULbPgzG37mj9g='
where name='admin';
commit;

This will set it to default password which is password 

UNIX - The VI Editor Tutorial

There are many ways to edit files in Unix and for me one of the best ways is using screen-oriented text editor vi.
This editor enable you to edit lines in context with other lines in the file.
Now a days you would find an improved version of vi editor which is called VIM. Here VIM stands for Vi
IMproved.
The vi is g enerally considered the de facto standard in Unix editors because:

• It's usually available on all the flavors of Unix system.
• Its implementations are very similar across the board.
• It requires very few resources.
• It is more user friendly than any other editors like ed or ex.

You can use vi editor to edit an existing file or to create a new file from scratch. You can also use this editor to
just read a text file.

Starting the vi Editor:

There are following way you can start using vi editor:

Following is the example to create a new file testfile if it already does not exist in the current working directory:

As a result you would see a screen something like as follows:

You will notice a tilde (~) on each line following the cursor. A tilde represents an unused line. If a line does not

beg in with a tilde and appears to be blank, there is a space, tab, newline, or some other nonviewable character

present.

So now you have opened one file to start with. Before proceeding further let us understanding few minor but

important concepts explained below.

Operation Modes:

While working withvi editor youwould come across following two modes:

      1. Command mode: This mode enables you to perform administrative tasks such as saving files,

executing commands, moving the cursor, cutting (yanking ) and pasting lines or words, and finding and

replacing . In this mode, whatever you type is interpreted as a command.

      2. Insert mode: This mode enables you to insert text into the file. Everything that's typed in this mode is

interpreted as input and finally it is put in the file .

The vi always starts in command mode. To enter text, you must be in insert mode. To come in insert mode you

simply type i. To g et out of insert mode, press the Esc key, which will put you back into command mode.

Hint: If you are not sure which mode you are in, press the Esc key twice, and then you'll be in command mode.

You open a file using vi editor and start type some characters and then come in command mode to understand the

difference.

Getting Out of vi:

The command to quit out of vi is :q. Once in command mode, type colon, and 'q', followed by return. If your file has

been modified in any way, the editor will warn you of this, and not let you quit. To ignore this messag e, the

command to quit out of vi without saving is :q!. This lets you exit vi without saving any of the chang es.

The command to save the contents of the editor is :w. You can combine the above command with the quit

command, or :wq and return.

The easiest way to save your chang es and exit out of vi is the ZZ command. When you are in command mode,

type ZZ and it will do the equivalent of :wq.

You can specify a different file name to save to by specifying the name after the :w. For example, if you wanted to

save the file you were working as another filename called filename2, you would type :w filename2 and return.

Try it once.

Moving within a File:

To move around within a file without affecting your text, you must be in command mode (press Esc twice). Here

are some of the commands you can use to move around one character at a time:

There are following two important points to be noted:

• The vi is case-sensitive, so you need to pay special attention to capitalization when using commands.
• Most commands in vi can be prefaced by the number of times you want the action to occur. For example,

2j moves cursor two lines down the cursor location.

There are many other ways to move within a file in vi. Remember that you must be in command mode (press Esc

twice). Here are some more commands you can use to move around the file:

Control Commands:

There are following useful command which you can use along with Control Key:

Editing Files:

To edit the file, you need to be in the insert mode. There are many ways to enter insert mode from the commandmode:

Deleting Characters:

Here is the list of important commands which can be used to delete characters and lines in an opened file:

As mentioned above, most commands in vi can be prefaced by the number of times you want the action to occur.

For example, 2x deletes two character under the cursor location and 2dd deletes two lines the cursor is on.

I would highly recommend to exercise allthe above commands properly before proceeding further.

Change Commands:

You also have the capability to chang e characters, words, or lines in vi without deleting them. Here are the

relevant commands:

Copy and Past Commands:

You can copy lines or words from one place and then you can past them at another place using following

commands:

Advanced Commands:

There are some advanced commands that simplify day-to-day editing and allow for more efficient use of vi:

Word and Character Searching:

The vi editor has two kinds of searches: string and character. For a string search, the / and ? commands are

used. When you start these commands, the command just typed will be shown on the bottom line, where you type

the particular string to look for.

These two commands differ only in the direction where the search takes place:

• The / command searches forwards (downwards) in the file.
• The ? command searches backwards (upwards) in the file.

The n and N commands repeat the previous search command in the same or opposite direction, respectively.

Some characters have special meaning s while using in search command and preceded by a backslash (\) to be

included as part of the search expression.

The character search searches within one line to find a character entered after the command. The f and F

commands search for a character onthe current line only. f searches forwards and F searches backwards and

the cursor moves to the position of the found character.

The t and T commands search for a character on the current line only, but for t, the cursor moves to the position

before the character, and T searches the line backwards to the position after the character.

Set Commands:

You can chang e the look and feel of your vi screen using the following :set commands. To use these commands

you have to come in command mode then type :set followed by any of the following options:

Running Commands:

The vi has the capability to run commands from within the editor. To run a command, you only need to g o into

command mode and type :! command.

For example, if you want to check whether a file exists before you try to save your file to that filename, you can

type :! ls and you will see the output of ls on the screen.

When you press any key (or the command's escape sequence), you are returned to your vi session.

Replacing Text:

The substitution command (:s/) enables you to quickly replace words or g roups of words within your files. Here

is the simple syntax:

The g stands for g lobally. The result of this command is that all occurrences on the cursor's line are changed.

IMPORTANT:

Here are the key points to your success with vi:

• You must be in command mode to use commands. (Press Esc twice at any time to ensure that you are in command mode.)
• You must be careful to use the proper case (capitalization) for all commands.
• You must be in insert mode to enter text.

Essbase: Exporting Data with Calc Script

SET UPDATECALC OFF;
SET DATAEXPORTOPTIONS
 {
 DataExportLevel LEVEL0;
 DataExportDynamicCalc OFF;
 DataExportNonExistingBlocks OFF;
 DataExportDecimal 3;
 DataExportPrecision n;
 DataExportColFormat ON;
 DataExportColHeader "Métricas";
 DataExportDimHeader ON;
 DataExportRelationalFile OFF;
 DataExportOverwriteFile ON;
 DataExportDryRun OFF;
 };


FIX("Jan":"Dec", "Real",@Relative("Currency",0), "Version Working", "FY12",@Relative("Entity",0), @Relative("Customer",0))

/*Same cube folder */
     DATAEXPORT "File" "|" "Sales_YYYYMMDD.txt" "0";

ENDFIX

------------------------------------------------------------------------------------------------------------

SET DATAEXPORTOPTIONS
{
DataExportRelationalFile ON;
DataExportLevel Level0;
DataExportDynamicCalc ON;
DataExportNonExistingBlocks OFF;
DataExportDimHeader OFF;
DataExportOverwriteFile ON;
};


FIX("Unit Price","Unit Quantity","USD", "CLP", "FY13","Working Version", @Relative("Customer",0), @Descendants("Total Año")

/* Other destination folder */
    DATAEXPORT "File" "," "F:\cantidad.txt";

ENDFIX

Hyperion: LCM Best Practices

If you are migrating an entire Planning application at once, Lifecycle Management handles most migration dependencies automatically. However, if you are migrating artifacts individually, Oracle recommends that you migrate Planning artifacts in this order:

1. Shared Services Native Directory (Security)
2. Application Settings under Configuration Properties
3. Global Artifacts:
   
   • Spread Patterns
   • Smart Lists
   • Common Dimensions
   • Exchange Rates
4. All Standard and Attribute Dimensions under Plan Type
5. Configuration:
   
   • User Variables
   • User Preferences
6. Substitution Variables under Global Artifacts
7. Plan Type:
   
   • Substitution Variables
   • Calc Scripts
   • Rules files
   • Calculation Manager Rules
8. Global Artifacts:
   
   • Business Rules
   • Custom Menus
   • Calculation Manager Rule sets
9. Data Forms under Plan Type
10. Global Artifacts:
    
    • Composite Forms
    • Task Lists
11. Relational Data
12. Access Permissions under Security
13. Reporting and Analysis (Financial Reporting and Web Analysis)

Hyperion: Setting Up an Audit Trail in Planning

This tutorial covers how to set up audit trails in Planning.
As an Administrator, you can select aspects of the application for change tracking. You can track changes to metadata, data forms, business rules, workflow, users, and access permissions.
To view audits, you create and run reports by using RDBMS report writers.

Scenario

As Administrator, you have been asked to turn on data and offline change tracking for your Planning application. After setting up change tracking, you need to verify data changes in the HSP_AUDIT_RECORDS table in SQL Server.

Prerequisites

Before starting this tutorial, you should:

1.	Have Administrator rights to Planning applications.
2.	Have access to Workspace and Planning.
3.	Have Administrator access to Microsoft SQL Server Enterprise Manager.

Setting Up Change Tracking

You can enable auditing for dimension administration, alias table administration, data modification, launching business rules, data form definition, data form folder administration, workflow, copy versions, security, user administration, group administration, and offline access.
In this topic, you set up change tracking for data modification and offline access.

To set up change tracking:

1.	Launch Workspace and log on as Administrator.
2.	Select Navigate > Applications > Planning > <Planning Application>.
3.	In Planning, select Administration > Reporting.
4.	Select the Auditing tab.
5.	Select the Data and Offline check boxes, and click Save Selections.
6.	Click Close .
7.	In the view pane, expand Forms. Under Forms, click a folder name to display its content, and click a data form.
8.	Click an unlocked cell, enter data, and click Save .

Viewing and Clearing Audit Reports in SQL Server

Depending on selected audit options, application changes are recorded in a HSP_AUDIT_RECORDS table, stored in the relational database. When using SQL Server, you can view and clear audit reports in Enterprise Manager.

To view and clear audit reports in SQL Server:

1.	Open Microsoft SQL Server - Enterprise Manager. From the Console Root, expand Microsoft SQL Servers > SQL Server Group > < SQL Server e.g. local> > Databases > < Planning application database > > Tables.
2.	Select the HSP_AUDIT_RECORDS table.
3.	Right-click HSP_AUDIT_RECORDS and select Open Table > Return all rows.
4.	Review the latest records, and click Close . In this example, data changes applied to all affected cells were posted to the table.
5.	Right-click HSP_AUDIT_RECORDS and select Open Table > Query.
6.	Right-click SELECT, and select Change Type > Delete.
7.	Click Run.
8.	When prompted that the query was executed successfully, click OK.
9.	Click Close .
10.	Right-click HSP_AUDIT_RECORDS and select Open Table > Return all rows.
11.	Verify that all records were deleted .

Hyperion: Creating Classic Planning Applications in Oracle Hyperion Planning, Fusion Edition 11.1.2

Overview

You can create Planning applications using Classic Application Administration. To use Classic Application Administration, you must be assigned the appropriate roles, as described in the Oracle Hyperion Enterprise Performance Management System User and Role Security Guide.

For Classic Planning applications, you use Classic Application Wizard to create and delete applications, and register with Hyperion® Shared Services. When you create Classic applications, you can set them up to calculate business rules by using Business Rules or Calculation Manager.

Software and Hardware Requirements

The following is a list of software requirements:

• Oracle Hyperion Planning Plus 11.1.2

Oracle Hyperion Planning Plus 11.1.2 includes the following components:

• Oracle Hyperion Enterprise Performance Management System Release 11.1.2 - Start Here: Installation Documents and Readmes
• Oracle Hyperion Enterprise Performance Management System Installer, Fusion Edition Release 11.1.2
• Hyperion Enterprise Performance Management System Foundation Services Release 11.1.2
• Oracle Hyperion Enterprise Performance Management System Additional Content Release 11.1.2
• Oracle Hyperion Calculation Manager Release 11.1.2
• Oracle Hyperion Enterprise Performance Management Architect, Fusion Edition Release 11.1.2
• Oracle Essbase Release 11.1.2
• Oracle Essbase Clients Release 11.1.2
• Oracle Essbase Spreadsheet Add-in Release 11.1.2
• Oracle Hyperion Financial Reporting, Fusion Edition Release 11.1.2
• Oracle Hyperion Enterprise Performance Management Reporting and Analysis Core Components Release 11.1.2
• Hyperion Web Analysis Release 11.1.2
• Oracle Hyperion Planning, Fusion Edition Release 11.1.2
• Optional: Hyperion Data Integration Management Adapters Release 11.1.1.1.0
• Optional: Oracle Hyperion Smart View for Office, Fusion Edition Release 11.1.2
• Optional: Oracle Data Integrator, Oracle Data Profiling, and Oracle Data Quality for Data Integrator 10g (10.1.3.5.0)
• Optional: Oracle Identity Management 11g (11.1.1.1.0)

Relational storage databases are required in the installation and configuration of Oracle Hyperion Planning Plus 11.1.2. This tutorial requires a relational storage for the Classic Planning application. See the Oracle Hyperion EPM System Release 11.1.2 - Start Here: Installation Documents and Readmes.

Prerequisites

Before starting this tutorial, you should:

1 .	Have administrator access to a working installation of Oracle Hyperion Planning 11.1.2.
2 .	Have access to relational storage used to create a Planning data source.
3 .	Have administrator access to Administration Services Console.
4 .	Have defined an Essbase Server view in Administration Services Console.

Managing Planning Data Sources

Dimensions and members are stored in the Essbase databases and in the Planning repository (Planning relational database). Each Planning application requires a Planning repository.

To create Planning data sources:

1.	Log on to Workspace.
2.	Select Navigate, then Administer, then Classic Application Administration, and then Planning Administration.
3.	In the left pane, click Manage Data Source. You can also click the Manage Data Source button in the content pane.
4.	Click Create Data Source.
5.	Perform the following actions for the application database: Select a database platform. In this example, Oracle is selected. Enter the server that hosts your database. In this example, localhost is entered. Enter the database port number. The default port for the Oracle database is 1521. Enter the Oracle service name or system identifier (SID). In this example, the Oracle SID is ORCL. Enter the user associated with the database schema. In this example, ST01PLN2 is used. Enter the schema password. Click Validate Database Connection. The following message confirms that the connection was successful:
6.	Enter the connection information to your Essbase Server, and click Validate Essbase Connection. The following message confirms that the connection was successful:
7.	Click Finish.

Creating a Classic Planning Application

To create a classic Planning application in Classic Application Administration: 

1.	In the left pane, click Create Application. You can also click Create from Classic Application Wizard to start the application creation process.
2.	On the Select tab, perform the following actions: Select a datasource. In this example, the ST01PLN2 datasource created in the previous topic is selected. Enter the name of the application. The name can contain up to eight characters. It must be different than an existing Essbase application. In this example, we name the application PlanBud. Enter an application description. In this example, Planning and Budgeting application is entered. Select a Shared Services Project (application group). In this example, the PlanBud application will be grouped under the Default Application Group. Select an instance (cluster) to use for the application. In this example, Default is selected. Select an application type. In this example, General is selected. For Calculation Module, select Business Rules or Calculation Manager. In this example, Calculation Manager is selected.
3.	Select the Calendar tab.
4.	Select the base time period, fiscal start year, fiscal start month, weekly distribution, and total years.
5.	Select the Currencies tab.
6.	Select the default application currency. If the application supports multiple currencies, select Yes.
7.	Select the Plan Types tab.
8.	Perform the following actions: Select Plan Type 1 and Plan Type 2, and clear all other plan type selections. Enter a name for both plan types. In this example, Incstmt and Balsheet are entered. Note: The name and number of plan types depend on your business requirements.
9.	Select the Finish tab.
10.	Review your selections and click Finish to create the application. When the application creation is successfully completed, the following message is displayed:

Opening the Planning Application

To open the Planning application in Workspace after creating it in Classic Application Administration:

1.	In Workspace, select Navigate, then Applications, and then Refresh. The Refresh menu option updates the navigation menu with new applications.
2.	Select Navigate, then Applications, then Planning, and then [Application Name]. In this example, you open the PlanBud application.
3.	The PlanBud application is displayed. After creating Classic Planning applications, you can add dimension members, data forms, menus, business rules, and task lists.
4.	Select Administration, then Manage, and then Dimensions. You can manage dimensions, set performance settings, and change the evaluation order in Dimension Editor.
5.	Select Administration, the Manage, and then Data Forms and Ad Hoc Grids. The Data Form Management page is displayed. You manage data forms and folders, and ad hoc grids in the Data Form Management page.
6.	After making changes to your application, you must refresh the Essbase database outline. Select Administration, then Application, and then Refresh Database.
7.	Make your selections and click Refresh. The following example displays a Planning application with defined dimensions, members, data forms, and other Planning objects.

Loading Data Into the Application

Prior to loading data to the application, ensure that you:

• Have a Planning application with the required metadata.
• Have a data file that maps to the metadata defined in the application.
• Have administrator access to Planning and Administration Services Console.
• Have defined an Essbase Server view in Administration Services Console.

This tutorial shows you how to load data into the Incstmt plan type in the PlanBud application. To load data into the application:

1.	Log on to Administration Services Console.
2.	In the left pane, expand Enterprise View, then Essbase Servers, then [Server View], then Applications, thenPlanbud, and then [Plan Type]. In this example, the PlanBud application is accessed from the UPKPS1RC4:1423 Essbase Server view. TheIncstmt plan type is selected. Essbase refers to plan types as databases.
3.	Select Actions, and then Load data for "[Plan Type / Database]".
4.	In the Data Load dialog box, click Find Data File.
5.	Select the File System tab, locate your data file, and click OK.
6.	Click OK to execute the data load process. You can also select the Abort on Error option in the Data Load dialog box to stop the loading process when an error occurs. The following progress box is displayed during the data load process:
7.	When the data load process is completed, the Data Load Results dialog box displays a status of Success.
8.	Open a data form in your Planning application to verify that data was loaded successfully.

Summary

In this tutorial, you have learned how to:

• Manage Planning datasources.
• Create a Classic Planning application.
• Open the Classic Planning application that you created.
• Load data into Planning applications.