Here are some quick tips to check when tuning your BSO application:
1. Dimension Order
♦ Use the hourglass model:
Largest dense to smallest dense, smallest sparse to largest sparse
♦ Attribute dimensions are always last.
2. Dense/Sparse Settings
♦ Typically, your accounts and period dimensions are classified as dense.
3. Member Properties
♦ Tag all upper-level members on your accounts and period dimension as dynamic calc. This not only will impact the size of the database which affects performance, but also reduces calculation times.
4. Block Size
♦ The recommended block size is 8-100KB.
♦ Be sure to check your Average Clustering Ratio (Right click database, Edit, Properties, Statistics). An Average Cluster Ratio of 1 is ideal in Essbase. You can achieve this number through a database restructure or by clearing your database and reloading your export file.
♦Be sure you have set configuration settings in the server environment.
♦ Right click on the database and click restructure. There are three different types of restructures:
♦ Your Index Cache should be equal to the size of the index file. If it is higher, you will not see improvement in performance.
♦ Use the set command SET CACHE to improve calculation performance. This command specifies the size of the calculator cache.
8. Calc commands
♦ It is recommended to If on dense members, and fix on sparse members.
♦ Use SET Commands as necessary to improve performance.
♦ When designing your calc script, keep in mind to minimize the number of passes on the database.
9. Avoid loading zeros to databases. It is best practice to replace zeros with #missings as a blank cell uses no memory as opposed to a zero.
In this post, I will discuss Security and the role is has within HFM applications. I will show how to change user access for different Security Classes, and also how to create a new user in an application. Both of these topics are very significant for assigning accessibility to your new HFM application.
The Security Class within HFM is very important when setting up your new application. They allow you to specify who is allowed to see different things within the application. You can restrict which accounts someone has access to, which entities they can see, different scenarios they can look at, which Inter Company Partners they can see, and the access to different custom dimensions. After the Security Class has been associated with a certain dimension, you can assign the user privileges to those classes. This is done in Shared Services.
Once in Shared Services, you can assign access control for users and determine which access type users can have per security class.
The Default access type is None, meaning there is no security for that user with those Security Classes. There are five access types: None, Metadata, Read, Promote, and All.
Since I am the Administrator User, I have set the access for all the classes to All.
You can also create new users within the Shared Services Console. Right-click on User to pull up that option.
Once you have created the user, you need to assign it to an application. You do so by Right-clicking the user, and selecting Provisions.
Then find and select the application you want to give the user access to.
After you find the application, you must give the user a role. The role I gave User2 was the Consolidate All with Data Role.
After you click OK, you will get a screen that tells you every role that has been assigned to that user.
From there, you can determine which access to give the new user in the existing Security Classes.
I gave User2 access to the US and EUROPE Security Classes.
Now User2 will only be able to run Consolidate All with Data in those two Security Classes.
In this blog, I will be discussing how to create a task list and also what purpose they serve in HFM. They can be a resourceful tool within the application to help manage your workload and I will explain how in the following paragraph.
A Task List in HFM serves as a folder for documents that you frequently use. This can be helpful for creating a centralized location where you can find everything you need to accomplish the current job. You could set up a task list for the month-end reporting so that it has all the reports necessary to do the monthly reporting. That would keep you from having to go through all of your documents and determine which ones you need every time. The task list keeps them all together, and you can work your way through each one with ease.
To create a new Task List, go into the application documents and select Actions.
From there, you can select which type of documents you want to place in your task list by clicking on Add. First, I put a Data Form into mine.
As you can see here, it has been copied into the task list.
Next, I placed a Data Grid into the task list.
Both the Data Form and Data Grid have been copied into the new task list.
Then, you will need to save the task list so you can have it for future use.
When you go back into the Application Documents, click on Actions again and select Refresh. You have to Refresh the page in order for the newly saved task list to show up.
Whenever you need to run the task list, you can find and open it within the Application Documents.
Creating an Application Blog
When creating a new HFM application, there are specific steps that must be taken to get the application up and running. It starts with creating and naming the application. To create an HFM application, once you are logged in to Workspace:
Go to File > New > Application
From there, you will name your new application and define the type of application it will be.
Once the application is created, there are several Application Properties that need to be established. They are as follows:
Default Currency – The Default Currency should be set to USD (US Dollars).
Balance Account Exchange Rate – This will always be the ClosingRate. Balance accounts use the month rate because the amount at the end of any period does not carry over into the next period.
Flow Account Exchange Rate – this will always be the AverageRate. Flow accounts use the average rate because the amounts in those accounts roll over to the next period.
PVA must be set for Flow Account – PVA (Periodic Value Add) is necessary for Flow Accounts because the amounts at the end of the periods need to be adjusted for exchange rates. Since exchange rates are always fluctuating and the Flow Account amounts carry over, PVA needs to be checked.
ICP Weight – This determines what percentage of your Inter Company Partners is consolidated up to their parent members. The default for this property is 0.0, so a change to this property is necessary for your ICPs to consolidate. The standard ICP Weight is 1, meaning 100%, allows the full value of the ICPs to be consolidated up through the hierarchy.
Validation Account – This account acts as a checks and balances system. It allows you to make sure any account balance you determine comes out to zero before a submission is made, or HFM will not allow it to go through. It is possible to have multiple validation accounts, but they are set up in a hierarchy.
Consolidation Rules – There are two options within this property: Y (Yes) and N (No). N is the default HFM selection and means that you will not be establishing any custom consolidation rules. Y means that you intend to set up your own custom rules for consolidating data.
Node Security – This property is where you determine if you will set up Security Access for your dimensions. There are several options you can select for Node Security: Accounts, Entities, Scenario, Generic, and ICP. You can enable security for all of the dimensions if you select the Node Security tab. Otherwise, you can individually select the dimensions you want to have security for, which would be based on the client’s preference. The only Dimension that requires Node Security is Entity.
Once you have set up these properties, you can begin setting up your dimensions. To set up dimensions, Right-click the Application name within the Dimension Library. A window will come up, then name the Dimension and select the Dimension Type.
The next step is to establish the Dimension Associations. Some of these dimensions require associations with other dimensions. To setup a Dimension Association, Right-click on the dimension that you want to associate, then select the dimension that you are associating with the first dimension. The dimensions that require associations are: Account, Entity, Scenario, Generic (Your custom dimensions), ICP, Value, Currency, and Consolidation Method.
After you have finished associating the required dimensions with each other, you need to load your dimension members with an .ADS file. In order to do this, you need to set up an Import Profile.
Go to File > Import > Create Profile
First, give the profile a name. Select Flat File under Import Type. Then choose the Application that you want the members imported to. Finally, browse for the .ADS file and upload it to the Import Profile, and run the import.
Once you have imported the members, you need to validate the application. This checks the entire application for errors before the application is deployed. If the Job Console confirms that there are no errors, you can deploy the application. Deploying the application is the final step in creating an application. This creates the application on the EPMA server and it is now fully functional and ready for data.
What are shared members?
Shared members “share values between members.” In other words, data associated with the member comes from another member with the same name. For example, lets say under the Product dimension, I would like to add the member “Coke” under Caffeine and share it under Diet. Look below for another example.
Quick Facts about Shared Members
- Always level 0
- Positioned below stored member in outline
- A shared member must be placed in the same dimension
- Do not store data
- A shared member should be placed lower in the outline tree than the actual member
- To ensure consolidation, sometimes it is recommended to consolidate the shared member to ~ property (or its parent)
- During calculation, references the data in associated stored member
A few check points when loading shared members:
- Use a separate rules file
- Do not tick “allow moves” in the dimension build settings
- Only a parent/child relationship will automatically share an existing member in the outline
As you can see, the four members listed under 100,200,300 and 400 are also shared under parent, Diet. Checkpoints:
1. Consolidation property is set to ~ (Diet)
2. Members are all in the Product dimension
3. Shared members are placed lower in the outline and are positioned below stored members
4. Shared members are level 0 members
There are three different types of partitions in Essbase. Today, we will focus on transparent partitions.
- Transparent partition
- Linked partition
- Replicated partition
A transparent partition allows a user to access and manipulate data from the source database as if it were stored in the target database. Essentially, this type of partition is sharing the data without actually moving the data across, in other words, there is visibility to the data. The database that owns the information should be the source.
What are the benefits of a transparent partition?
- Allows users to update data
- When a user updates data at the source, Essbase makes those changes at the target
- Shows users latest version of data at target level, or real time data
- Decreases disk space
Step by Step Process
1. Right click on the source database and create a new partition.
2. Select the partition type
3. Click on the connection tab and select your data source and target databases. Insert your login credentials.
3. Using the member selection tool, map the source and target database members.
5. Finally, validate your mapping and click save in order to save the partition. If you have any errors, the errors will show in the message panel. The partition should now be visible in the source database.
Not only is Essbase a leading calculation engine, but as a multidimensional database, it is also efficient at storing data. Essbase creates data blocks and an index system in order to access and store data into a cube.
The cube above contains three dimensions:
Data values are intersections of members from different dimensions that are stored in one cell of the database. For instance, an example of a data value in the cube above is where Sales intersects with Actual and January.
As mentioned above, Essbase accesses and stores data by using data blocks and an index system. A data block is created for every sparse dimension intersection. When that block is created, the index is used to locate the block containing a data value, in this case, Sales -> Actual -> January. In other words, the index or the locator provides a pointer to the correct data block. Once that data block is located, Essbase can retrieve the data value and it can be viewed via Smartview.
Note: Be sure to label dense and sparse correctly in order to maximized the efficiency of the block storage system. This can be done by turning autoconfigure to false in order to be able to change your storage type. Changing a dense dimension to sparse will reduce your block size. The recommended block size is 8KB-100KB for 32 bit servers and 100KB-1MB for 64 bit servers.
- Data block is created
- Index created
- Once data value is located using the index, Essbase retrieves data.
The importance of Essbase and its multidimensionality play a vital role when discussing the use of data blocks and an index system. Using the multidimensional technique, users can gain insight and analyze data from any business perspective as shown in the example above (sales -> actual-> January).