Encryption using MAXL

Folks, I find encryption fascinating with MAXL and hence this post. Today we will do a real word small example regarding the same.

In the example below we will encrypt a MAXL file, Decrypt it and create a batch for automation.

Step 1) Type essmsh -gk

Generate Public and Private Keys

Public Key for Encryption: 30289,2059835629

Private Key for Decryption: 1898169137,2059835629

Step 2) Select the script you want to execute. In above case, lets take a simple script(encryptit.msh) which logs into the Essbase and Sets the Database note for one of the cube.

In your case username, password, server and Essbase cube names will be different.

login vaneetgupta ‘welcome’ on ‘dce-spapp02’;

Alter database T_FIN_V2.T_FIN_V2 Set Note “Testing Encryption”;


Step 3) In the next step we encrypt the file and the new file encryptit.mshs will be created.

Type essmsh -E encryptit.msh 30289,2059835629

As you can see above the login id and password has changed to keys.

Step 4) Decrypt the file using private key and create a batch as shown below

Step 5) Run the batch and check the Essbase for Database note

So we have seen how we have done the needful without anyone looking at the username password of the person who logs in.

Download the code from: http://www.mediafire.com/?im2dq4u6asi36ss

Password = encryptit




Gartner 2011 Report

Folks I have been busy with everything but Hyperion for a long time. I was having a feel of OBIEE Apps, Informatica and SQL and was working with real time databases. As it was a learning curve for me in this I was kept busy with that.

Recently saw that Gartner 2011 report is out and to my surprise they haven’t mentioned Hyperion in that. Your comments on this would be most welcome.

You can either go to http://www.gartner.com/technology/media-products/reprints/qliktech/article3/article3.html and read or alternatively

Go to http://www.qlikview.com/us/explore/resources/analyst-reports/gartner-report-magic-quadrant-for-business-intelligence-platforms-2011 and register for the report.

Until next time, Cheers!

Essbase 11.1.2 – New Features

Essbase 11.1.2 brings along these new features :-

EPM security link – Essbase apparently does not store user details in the .SEC file, if running in EPM security mode. This means there is no need to synchronize security between Shared Services and Essbase any more. This is very good news, as this synchronization added an extra step which could be forgotten and meant security issues all the time.

Allocations on ASO cubes – You can now perform allocations on ASO cubes, previously this could only be done in BSO. One more reason to go ASO rather than BSO as a default? If that is not all, the next feature might convince you…

Custom Calculations on ASO – Basically this means you can now write calc scripts for ASO cubes. Not as fully featured yet as for BSO, for example you can only target level 0 cells, but a step in making ASO on par with BSO in terms of calculation capability. Will BSO eventually fall away…?

New @XWRITE function – the inverse of the @XREF function. During calculation you can write to data blocks in the same or other remote cubes (whereas @XREF pulls data from remote cubes). Very useful if you have distributed cube environment with data dependencies, as from a central calculation you can update all dependent cubes, rather than pulling data from calcs in each cube.

32 alias tables – Instead of 10 you can now have up to 32 alias tables. I guess this is good for some applications, I must be honest in all my years of using Essbase I have never used more than 6 alias tables in a single cube, and that was an extreme case.

IPv6 support – As all the other modules in EPM 11.1.2, Essbase also supports the new IPv6 internet protocol, which aims to allow many more addressable internet addresses.

OCI support – Essbase can now use OCI to connect directly to Oracle sources in load rules, rather than ODBC. It helps that Oracle is now the boss…

Monitor progress of data loads and dim builds – While data loads or dim builds are in progress, you can now query the progress, to get info such as which stage of the process is taking place, number of records processed and rejected, etc. This can be done if you kick off an asynchronous data load or dim build only. Very useful to track progress, something you previously couldn’t really do, short of continually refreshing the log file to get some idea of what is happening.

Error handling in Calc Scripts – Using the new @RETURN function you can exit a calc script with a custom error code based on results of an IF..THEN statement. This means you can add a lot more logic to error handling in calc scripts, and return meaningful codes.

MaxL Error Handling improved – You have an IfError in the MaXL Shell that can detect errors like syntax and no permission errors. You can therefore directly in the MaxL shell test for more complicated errors and exit with a return code, or continue processing. This should make error trapping and processing in MaxL scripts a little better than currently possible.

Export metadata to XML files – Another addition to the sorely lacking metadata export in Essbase has been added. You can now export metadata to an XML file, using a MaxL command (EXPORT OUTLINE). These XML files can then be used by other proceses where you need outline information, or to compare outlines. I assume at a later stage you will be able to import from these XML files?

Failover support – You could do failover for Essbase with High Availability Services, but they now made it possible through using Oracle Process Manager and Notification Server (OPMN). This offers comprehensive failoverand clustering support for Essbase.

This is some of the more important new features, there are more, if you want to read about these yourself you can access the EPM documentation at the following link:


Analysis :: Gartner Report 2010 vs 2009


Noticed that Gartner’s MQ for Business Intelligence is now out there for 2010:


Compared to 2009, it appears that Microsoft ,Oracle, and Micro Strategy moved up and to the right in the leader’s quadrant.


Of all the players in the MQ, it looks like Actuate is the big loser – it made a pretty big move down and to the left in the niche quadrant.

Also I have seen Qlikview reports in version 9, it provides fantastic dashboard capabilities and we might go for that soon. The maximum limit of 2 billion rows is no more there. All I would say it’s a fantastic tool for the users.

In the quadrant as well and you see, it is now becoming much more accepted in the industry. Organizations are rapidly embracing the idea of providing data to end users and empowering them with an ability to navigate and visualize the data in a "surf and save" mode as an alternative to a report-only architecture. Threatened by the success of these vendors, traditional BI platform vendors are attempting to imitate them with easy-to-use interactive visualization alternatives (for example, Microsoft with PowerPivot, SAP with SAP BusinessObjects Explorer, IBM with IBM Cognos Express, and Information Builders with WebFocus Visual Discovery) often incorporating in-memory technology.

Have a look at these rare articles and have happy reading.

What is BI and why do we need it

Business intelligence

Business intelligence (BI) is a business management term which refers to applications and technologies which are used to gather, provide access to, and analyze data and information about their company operations. Business intelligence systems can help companies to have a more comprehensive knowledge of the factors affecting their business, such as metrics on sales, production, internal operations, and they can help companies to make better business decisions. Business Intelligence should not be confused with competitive intelligence, which is a separate management concept.

Rationale for using BI

Business Intelligence applications and technologies can enable organizations to make more informed business decisions, and they may give a company a competitive advantage. For example, a company could use business intelligence applications or technologies to extrapolate information from indicators in the external environment and forecast the future trends in their sector. Business intelligence is used to improve the timeliness and quality of information, and enable managers to be able to better understand the position of their firm as in comparison to its competitors.

Business intelligence applications and technologies can help companies to analyze changing trends in market share; changes in customer behaviour and spending patterns; customers’ preferences; company capabilities; and market conditions. Business intelligence can be used to help analysts and managers determine which adjustments are most likely to respond to changing trends.

Using BI systems can help companies to develop a more consistent, data-based decision making process for business decisions, which can produce better results than making business decisions by “guesswork.” As well, business intelligence applications can enhance communication among departments, coordinate activities, and enable companies to respond more quickly to changes (e.g., in financial conditions, customer preferences, supply chain operations, etc.). When a BI system is well-designed and properly integrated into a company’s processes and decision-making process, it may be able to improve a company’s performance. Having access to timely and accurate information is an important resource for a company, which can expedite decision-making and improve customers’ experience.

In the competitive customer-service sector, companies need to have accurate, up-to-date information on customer preferences, so that the company can quickly adapt to their changing demands. Business Intelligence enables companies to gather information on the trends in the marketplace and come up with innovative products or services in anticipation of customer’s changing demands. Business Intelligence applications can also help managers to be better informed about actions that a company’s competitors are taking. As well, BI can help companies to share selected strategic information with business partners. For example, some businesses use BI systems to share information with their suppliers (e.g., inventory levels, performance metrics, and other supply chain data).

BI systems can also be designed to provide managers with information on the state of economic trends or marketplace factors, or to provide managers with in depth knowledge about the internal operations of a business.

Why Business Intelligence is a Valuable Investment:

  • Empower decision makers with a single version of the truth
  • Make dynamic business decisions by having accurate, current and relevant information available when you most need it
  • Harness business power with near instant response time on massive data volumes
  • Get instantaneous answers to those “what if” questions
  • Meet or exceed your customer expectations – based on factual information
  • Minimise training requirements by using a familiar working environment
  • Find latent problems by building a picture of the information you can’t see
  • Drill down into reports and pull together information that can make the difference between a great business decision and a poor one
  • Quickly recognise what your competitive advantages are
  • A continuous overview of the development in sales in connection with for instance product group, time, campaign, sales person, market etc.
  • An opportunity to analyze and explain the development of the financial records by looking into the balance sheet and the income statement.
  • A more efficient allocation of resources and a better focus on critical areas of the business. A BI-tool will therefore improve the company’s productivity and bottom-line.
  • An opportunity to create KPI based score cards to monitor the company’s results. Thus making the company strategy measurable in practice.
  • Updated know-how and better insights and thereby improves decision making in all levels of the organization.
  • A shorter response time and a faster “time to market”. Thus, an improvement of the company’s competitive advantage.

How to Rename Bulk Members in Planning without loosing data?

I have seen many people face the problem of renaming members in Planning. Usually the mode followed is doing it manually through front-end followed by refresh.

If you are using tools like HAL, there is no way you can update the same, instead it will create new members and you will end up loosing data.
I have found out a way which might help you guys as well

The following code can be used to Rename members in Planning without loosing data:-

update hsp_object
set object_name = , modified =getdate()
where object_name =

Once done, you may need to bounce the planning service to see the changes appear. Then of course you will need to perform a refresh to push this to essbase.
Let me know how you feel on this. I always welcome comments.

Hybrid Analysis Versus Drill through

This is a question that came up to me lately by one of my Senior and initially i though that both Hybrid analysis and Drill through are same but wasnt that way. After some research this is what I found:

Hybrid Analysis requires hierarchy is balanced, relational drill thru does not.
If we are not planning to use Hybrid analysis then we don’t need to worry about balanced hierarchy.

Now what is Balanced and Unbalanced Hierarchy? Lets dig that.

Balanced Hierarchy

A hierarchy with meaningful levels and branches that have a consistent depth. Each level’s logical parent is in the level directly above it. A balanced hierarchy can represent time where the meaning and depth of each level, such as Year, Quarter, and Month, is consistent. They are consistent because each level represents the same type of information, and each level is logically equivalent. Figure 1 shows an example of a balanced time hierarchy.


Figure 1. Balanced hierarchy. Example of a balanced hierarchy

Unbalanced Hierarchy

A hierarchy with levels that have a consistent parent-child relationship but have a logically inconsistent levels. The hierarchy branches also can have inconsistent depths. An unbalanced hierarchy can represent an organization chart. For example, Figure 2 shows a chief executive officer (CEO) on the top level of the hierarchy and at least two of the people that might branch off below including the chief operating officer and the executive secretary. The chief operating officer has more people branching off also, but the executive secretary does not. The parent-child relationships on both branches of the hierarchy are consistent. However, the levels of both branches are not logical equivalents. For example, an executive secretary is not the logical equivalent of a chief operating officer.


Figure 2. Unbalanced hierarchy. Example of an unbalanced hierarchy

My Take : In most cases we would have unbalanced hierarchies in Essbase so we should go for Drill through generally, unless we have specific requirement.