Wednesday, June 24, 2009

Replacing #Missing label with 0 Zero not text 0

Put the #Missing label as (0) and excel will interpret it as a numeric zero.Technically, it's a negative zero.
If (0) it didn't work, it may be because the cell is already formatted as a text entry rather than general formatting.Clear the formatting of the data area and then retry the (0) setting.

Works for Essbase Addin 7X and above.

Wednesday, June 17, 2009

Some Old School Stuff

What is the Outline?
•Organize the data into dimensions and hierarchies
•Assign member and dimension properties
–Storage
–Two Pass
–Member formulas
•Tag dimensions dense or sparse

Why is the Outline Important?
•Determines how data is stored
•Determines the size of the blocks
•Determines some of the calculations for the database
•Determines the calculation order for dimensions
•Determines how users can perform analysis
•One of the major things that you can tune to improve performance

AutoConfiguration of sparse dense
•Don’t use autoconfiguration
•Autoconfiguration of dense and sparse dimension provides only an approximation
•Essbase cannot take in account the nature of the data loaded into the database or the requirements of calc scripts
•Automatic configuration can be disabled for manual definition of dense and sparse settings

Blocks in Essbase
•Dimensions tagged dense will make up the cells within a block
•Dimensions tagged sparse will create the blocks themselves
–Essbase creates a data block for each unique combination of sparse dimension members that contain data
•Tagging dimensions as dense and sparse determines the overall size and structure of the database

Calculate Block Size
•Data block size is determined by the amount of data in a particular combination of dense dimensions
•Data block size is 8n bytes, where n is the number of cells that exist for that combination of dense dimensions

How do you reduce block size?
•Change a dense dimension to a sparse dimension
•Make members in a dense dimension dynamic
–Label only
–Dynamic calc

Old School Dimension Order
•Hourglass
•Accounts first, Time second
•Dynamic calcsalways slow retrievals

The Improved Way -Dimension Ordering
•Largest Dense Dimensions
•Smallest Dense Dimensions
•Smallest Aggregating Sparse Dimensions
•Largest Aggregating Sparse Dimensions
•Non-aggregating Sparse Dimensions
Referred to as hourglass on a stick

Dimension Ordering Guidelines
•Dense dimensions -define the data block and must reside at the top of the outline
•Aggregating Sparse dimensions -dimensions that will be calculated to create new parent values
–Should reside directly below the last Dense dimension in the outline
–Placing these dimensions as the first Sparse dimensions positions them to be the first dimensions included in the calculator cache
–Gives them an ideal location within the database for optimized calculation performance.

Friday, May 29, 2009

How to kick off calc automatically in essbase after dataload thru FDM?

IF you want to set off a calculation in Essbase using FDM, this is how you do it:
1. Under Integration settings enable consolidation
2. For Default Calculation Method under integration settings choose file
3. Create a validation entity for your Essbase location
4. Where it says Parent Entity put in the exact name of your calc script ie CalcDB
5. For Entity I always have put the top level member Entity
6. Check the consolidate box
7. Start period is 0
8. Type - All
9. Sequence 0
10. Assign this validation entity to your essbase location.

HFM & FDM Migration

Create a folder just for this move, save all files here.
Open HFM Client (desktop software) if you have BPMA none of this applies.
Connect to DEV app.Extract Security.Extract Metadata.
Extract Member Lists.
Extract Rules.
Extract Data (one file per combo of Year and Scenario).
Extract Journals (same as data).
Find Application Profile used to create DEV.
Click Create Application, fill in info, use above profile.
Load security.
Load metadata.
Load rules.
Load data.
Load journals.
Go into app, register with shared services.consolidate each year-scenario.Migrate reports.
Go into DEV app.Extract each document.Load each document into Prod.

OR
HFMCopyApplication.exe utility (located in the HFM \Server folder.
You should be able to use this to copy from one server/database to another.

========================================================

For FDM Open DEV app in webFile > ExportSave .xml file to folder
Connect to Prod serverOnce app is created, File > Import

Hyperion Interview questions

Some of the interview questions for Essbase:
1) Difference between ASO & BSO?
2) What is Incremental Dimensional Built and one time Dimension Built?
3) What are the different types of LOG Files?
4) What is the Extension of Cal Scripts and Rule File?
5) Why are Filters used?
6) What are dense and sparse dimensions?
7) What are Filters?
8) What are Attributes?
9) Different Types of Attributes?
10) What is Substitution Variable?
11) How is Data Stored in Essbase?
12) What is an hour glass modle?
13) Types of Build Methods?
14) What is Two Pass Calculation?
15) What is TB First and TB Last?
16) How do you calculate the Size of the datablock?
17) How many data blocks are there in your cube?
18) What is meant by Descendents and can you give me the best example to describe it?
19) How do you do the Data Load?
20) Where is IS Member Command used?
21) What is the difference between FIX and If Command?
22) What is intelligent Calcuation?
23) What is meant by Clean block and Dirty block?
24) Commands of Intelligent calcuation?
25) How do you calculate the subset of a cube?
26) Difference between standard dimension and attribute dimension?
27) Difference between UDA and Attribute Dimension?
28) What is meant by XREF Function?
29) Can you give the same name to different members in UDA?
30) What is Data Cahce ?
31) What is the Difference between the Data Cache and Data File Cache?
32) What is the Size of your cube?
33) What is Shared members? Can shared members can have children below it ?
34) What are the different storage properties in Essbase?
35) What is the difference between Dynamic calc and Dynamic Calc and Store?
36) How does Essbase consolidates the Data?
37) Different types of Dimension Building?
38) What is Label only? Give the the example of it?
39) Difference between calc all and Calc Dim?
40) Explain me about your project? And tell me any difficulities that you have faced and how did you resolve it?
41) What is Commit Block?
42) Tell me different steps so that your can optimze the performance your cube?
43) What is Partition?
44) What are different types of Partition? Have you ever worked on partation?
45) What is MAXL ? and where is it used?
46) What is Alias?
47) How many Alias Tables can be created?
48) How is Data Retrived from Essbase?
49) How is Data Stored in ASO?
50) Different types of Attributes in ASO?
51) Tell me about your project and what is your role in your project?
52) Did you write any MDX Member formulas in your Project?
53) What are different Types of Data Blocks?
54) Where do you check the data has been loaded properly or not?
55) What is Fact Table ? What are supporting Dimensions?
56) What is your Fact Table in Cube?
57) How do you create shared members in your cube?
58) Why do you use Fix Command?
59) Can you use the same logic which are used in calculation scripts in MDX formulas?
60) You have 5 dimensions in your cube and to load the data in the cube you have got only 4 columns what do you do?
61) Why do you use set update cal on and set update calc off?
62) What is mean by free form data load?
63) When do delete one member from the outline what will be status of the block?
64) What are different Types of blocks?
65) What is the difference between Attributes and UDA?
66) What is meant by location Alias?

Thursday, May 28, 2009

Deleting Account Annotations in Hyperion Planning

You need to clear below tables to deleteaccount annotation from planning.
HSP_CELL_NOTE
HSP_CELL_NOTE_ITEM
HSP_ACCOUNT_DESC
Don't forget to recycle planning services.