Latest Posts

Essbase Hybrid Aggregation Mode & BSO Limits

There is currently a lot of excitement around the new Essbase Hybrid Aggregation Mode. As this new feature matures, clients are starting to ask about the types of cubes that are best suited for conversion to the new calculation engine. While Hybrid Aggregation Mode is pretty amazing, a side project of mine recently reminded me of a specific class of cubes that are not yet appropriate for conversion.

First, a bit about the side project . . . I’m working on building a set of “reference” cubes that I can easily deploy in various environments to benchmark physical (and virtual) infrastructure. Imagine a set of cubes that run through a standard series of data loads, calculations and retrieves where the performance is recorded for comparison against other environments. The idea is to arrive at a “score”, so I know early in an implementation when I’m working on suboptimal hardware. For this to work, I need some really big cubes (BSO, ASO and Hybrid). And when one builds really big cubes, they are sometimes reminded of Essbase’s size limitations, because when reached . . . dimension builds fail. These limits are very nicely documented here.

The basic Essbase size limitations are pretty straight-forward:

  • BSO cubes can have a maximum of roughly 1,000,000 members.
  • ASO cubes can have a maximum of roughly 10,000,000 – 20,000,000 members.

Hybrid cube limitations are not specifically called out in the documentation (yet), but we can make some assumptions:

  • We know that Hybrid cubes start out life as normal BSO cubes.
  • We assume that BSO limits apply to Hybrid cubes. (My limited testing appears to confirm this.)
  • We know that BSO cubes have lower size limitations than ASO cubes.
  • Therefore, certain large ASO cubes can not be converted to Hybrid.

In addition to the basic size limitations above, there is a slightly different limit that developers are more likely to encounter:

  • BSO cubes can have a maximum of 2104 stored sparse member combinations.

Here’s what makes this limit so interesting . . . the documentation (which is very, very good overall) is incorrect. BSO cubes are not limited to 2104 stored sparse member combinations. Instead, they are limited to “Two Groups of 252”stored sparse member combinations. What does that mean? How do we know the documentation is wrong? Let’s dig a little deeper. 

Over 20 Nonillion Stored Sparse Member Combinations!

2104 is a very large number. It’s a smidge over 20 nonillion. Don’t know what a “nonillion” is? I didn’t either.

2104 = 20,282,409,603,651,700,000,000,000,000,000

To understand this limit, we must first understand how to calculate the number of potential stored sparse member combinations. (These are “potential” combinations until there is data at a particular intersection of members . . . then they become “actual” combinations.) To arrive at the number of potential stored sparse member combinations, simply multiply the number of stored members from each sparse dimension together.

For example:

  • Sparse Dimension #1: 10 Stored Members
  • Sparse Dimension #2: 30 Stored Members
  • Sparse Dimension #3: 100 Stored Members
  • Sparse Dimension #4: 1000 Stored Members

10 * 30 * 100 * 1000 = 30,000,000 Potential Stored Sparse Member Combinations

In other words, there are 30 million unique possible combinations of sparse members if we take one member from each of the above four dimensions.

If we were in fact limited to 2104 potential stored sparse member combinations in a BSO cube, it is unlikely that anyone would hit this limit. This is because another limit would most likely kick in first. Remember, developers can have a maximum of roughly 1,000,000 members in a BSO cube. Try arriving at 2104 potential stored sparse member combinations when you only have a million total members to work with . . . it’s possible, however it requires an unusual number of sparse dimensions. 

Will the Real BSO Limit Please Stand Up?

If the 2104 limit is incorrect, what is the real limit? Luckily, Essbase returns the correct error message during a dimension build . . . it’s only the documentation that is incorrect. Here is what shows up in the Essbase application log after the dimension build fails:

Essbase_App_Log

It’s easy to see how the 2104 limit was incorrectly derived. 2 * 252 = 2104, right??? Wrong.

252 = 4,503,599,627,370,500

2 * 252 = 9,007,199,254,740,990   (Note that this number is much less than 20+ nonillion or 2104.)

However, don’t be fooled into thinking that the real limit for potential stored sparse member combinations in a BSO cube is 9,007,199,254,740,990. That’s not correct either. Remember, we get two groups of 252 stored sparse member combinations. How the dimensions fall into these two groups is very important. 

Show Me the Groups!

Here are the basic steps for determining whether or not you will exceed the “Two Groups of 252” limit:

  1. Locate the first sparse dimension (closest to the top of the outline).
  2. Multiply the number of stored members in this dimension with the number of stored members in the next dimension.
  3. Repeat until the product of stored sparse members exceeds 252 (4,503,599,627,370,500).
  4. Back up one dimension. The 1st sparse dimension down to this dimension makes up the first “group”. The idea is that a group’s stored member product cannot exceed 252.
  5. Start multiplying the stored members from each subsequent sparse dimension together. These dimensions represent the 2nd group. If the product exceeds 252 on the second group, the limit has been reached and the dimension build will fail.

Here is an example of two groups of sparse dimension members:

Example_Groups

When building the dimensions in the BSO cube described above, Group 1 ends after the 6th sparse dimension (“Sparse_06”). This is because including the next dimension (“Sparse_07”) would cause the sparse member combinations in that group to exceed 252. As soon as Group 1 is as full as possible without exceeding this limit, Group 2 begins. Unfortunately, we can see that the dimension build fails at the 10th sparse dimension (“Sparse_10”), because the second group exceeds 252 and we are only allowed a maximum of two groups of 252.

Hybrid and Dynamic Sparse Members

Readers who are familiar with Essbase Hybrid Aggregation Mode may recall that one of the key design elements in a Hybrid cube involves leveraging dynamically calculated sparse parents (a general no-no in BSO, but required in Hybrid). However the “Two Groups of 252” limit is all based upon stored sparse member combinations. So could a BSO cube that failed due to this limit potentially work using Hybrid? Maybe. It all depends on how many sparse members are changed from “stored” to “dynamic” during the conversion to Hybrid Aggregation Mode.

Conclusion

If you’re thinking about taking the new Essbase Hybrid Aggregation Mode for a spin (and you should!), remember that size does matter. Some ASO cubes may not be suitable for conversion to Hybrid due to BSO size limitations. But remember that a key feature of Hybrid cubes involves setting upper-level sparse members to “Dynamic”, thus reducing the number “Stored” members in a sparse dimension. This change may create some additional “headroom” before you actually hit the “Two Groups of 252” limit with a Hybrid cube.

If you would like to hear more about this topic, please plan on attending Infratects’ Top Gun US conference on September 17-18. I’ll be presenting on Hybrid Aggregation Mode and will specifically address questions around conversions. As always, I will have a live demo.

Pop Quiz!

In the example BSO cube above, the dimension build fails at the 10th dimension. Assume the following:

  • If the cube remains a BSO cube . . .
  • If no members are deleted . . .
  • If all dense/sparse settings remain the same . . .
  • If all data storage settings remain the same . . .

What could be done to this cube to make the dimension build successful?

Tweet me the answer at @HyperionNerd.

The Ultimate EPM Demo Laptop

As part of my job, I’m often running demos for clients or presenting at conferences and user groups. As such, I need to run a good portion of the EPM stack on my laptop (running virtual machines). Because this requires a fairly beefy laptop, I typically gravitate towards “workstation replacement” type notebooks. I’ve had a Lenovo W520 for several years, and it has served me very well, but with the rapid pace of hardware improvements and my increasing needs, it was time to upgrade. Websites like Tom’s Hardware and AnandTech do a great job of highlighting the newest and fastest bits of hardware, and some are so new that you need an entirely new laptop to run them. I spent a fair amount of time selecting and configuring my demo laptop, and I thought others might benefit from my experience.

Calling a laptop the “ultimate” laptop is a risky proposition. Different people use laptops in different ways. Hardware that represents the epitome of high performance today can look embarrassingly low-tech tomorrow. The items below represent the values that guided my selection:

  • I need to run complex VM’s during demos and presentations.
  • I need a machine that’s portable, but just barely.
  • I need a machine that supports 32 GB of RAM.
  • I need a machine that will support the latest hard drives.
  • I value overall performance more than finding a good deal.

The search for a workstation-class laptop usually starts with either Lenovo’s “W” line of laptops or Dell’s “Precision” line. I’ve had both in the past, and I’ve been happy in each case. But neither line currently pushes the performance envelope. There are currently (as of May 2015) two big limitations with these lines:

  1. They are limited to mobile CPUs (currently the Intel Core i7 4940MX, up to 4.0 GHz).
  2. They do not support the latest PCIe 80mm M.2 SSDs (very fast hard drives).

Toyota vs Ferrari

Mainstream manufacturers need to supply laptops that can satisfy corporate purchasing departments that want reliable and affordable hardware . . . something akin to a Toyota. But what if you’re looking for more of a Ferrari? What if you want bleeding edge technology? The answer can be found in video games. Custom “gaming” laptop manufacturers often offer the latest, greatest components long before the mainstream manufacturers. And they also offer premium services (think monitor calibration, or custom paint jobs). If the idea of selecting the thermal paste used to mount your CPU to the motherboard excites you, this is an avenue you should explore.

As a khaki pants wearing, corporate type of guy, ordering a custom gaming laptop for work did not seem like a mature or even sensible idea. However, I found that most of these builders have options that look fairly tame on the outside. These are laptops that won’t stand out in the corporate world. I ended up looking at laptops from companies like XOTIC PC, Sager, Eurocom and Digital Storm. Several of these companies leverage a chassis and other components from Clevo, and to varying degrees, offer their own special software and internals. In other words, don’t be surprised if you see laptops that look similar on the outside from several of these companies. Pay attention to what’s being offered on the inside, because it can vary, and the internal components are what’s really important. Let’s discuss a couple of the more important components.

Go Big or Go Mobile?

The natural assumption is that laptops are limited to mobile processors, but this does not have to be the case. Several of the aforementioned companies will drop a desktop or even server CPU into a laptop. For example:

  • Sager NP9772-S (Desktop Processor: Intel Core i7 4970K, up to 4.4 GHz)
  • Eurocom Panther 5 SE (Server Processor: Intel XEON with 12 cores)

These processors can offer better performance than the highest-spec mobile processors – sometimes faster, sometimes with more cores. Of course, the consequence is usually a battery that doesn’t last as long. But then again, no one who drives a Ferrari is worried about gas mileage, and the same should be true for a high performance laptop.

Good Things Come in Small Packages

Most of us are familiar with the old 2.5” laptop hard drives (HDDs). These are quite slow by modern standards. If you still have one of these, you are overdue for an upgrade.

HDD

More recent drives look like the SSD below. Unlike the HDD above, these do not have physical disks that spin within the drive. Instead, they leverage flash memory for dramatically improved performance.

SSD

Unfortunately, the drives above both leverage the SATA standard for connecting storage devices within a computer. The SATA standard works with SSDs, but can’t fully exploit the potential speeds of newer hard drives. In other words, as drives have become faster, the software that connects those drives to the computer has become the limiting factor affecting performance. Most current laptops only support SATA drives. But certain newer ones support PCIe, which offers higher throughput than SATA.

The PCIe drive below is a 512GB Samsung SM951. Technically, this is also an SSD because it uses flash-based memory to store data, but it does not leverage the throughput-limited SATA interface like the Samsung 840 EVO above. It’s difficult to tell from the picture, but it’s about the size of a stick of Wrigley’s chewing gum. And it is very, very fast.

PCIe

Note the dramatically higher sequential read/write speeds of the PCIe drive (right), compared to a SATA SSD (left).

Crystal_Diskmark

Current PCIe laptop drives leverage the ACHI standard. ACHI was originally designed to work with older HDD’s (as opposed to flash-memory based drives). A newer standard called NVMe has been developed specifically for PCIe drives, however PCIe/NVMe drives are not currently available for laptops. Several have been announced, and should be available within months. These newer PCIe drives that leverage the newest standard (NVMe) should be even faster than the PCIe drive above, especially with regard to random input/output operations per second (IOPS).

The key takeaway is this . . . laptop hard drive performance has recently improved dramatically, but few laptops currently have the PCIe slots required for the new drives. In the next several months, PCIe drives that leverage the NVMe standard will further improve laptop hard drive performance. All of this will translate into better performing demos.

In summary . . .

Drive_Hierarchy

The “Ultimate” Laptop

After a significant amount of research, I decided on the following specifications for my “Ultimate” EPM Demo Laptop:

  • Sager NP9772-S (17.3” IPS LED-Backlit Matt Finish Display)
  • Intel Core i7 4970K, up to 4.4 GHz (Desktop Processor)
  • 32GB Kingston HyperX RAM
  • Nvidia GeForce GTX 980M GPU w/ 8GB Video Memory
  • 330W Power Adapters (2)
  • Hard Drives:
    • C:\ (OS) Micron M600 512GB M.2 SSD (SATA)
    • D:\ (Data) Samsung 840 Evo 1TB SSD (SATA . . . from old laptop)
    • E:\ (VMs) Samsung SM951 80mm M.2 512GB SSD (PCIe)

To say this laptop is fast is an understatement. It screams. It’s an upgrade from my old laptop in so many ways that it’s difficult to tell exactly what components are contributing to the improved performance. Everything is faster.

Of course, there are a few downsides . . .

  • It’s big and heavy. I wasn’t worried about that . . . I have a roller bag for my laptop.
  • The keyboard is just “ok”. Sadly, nobody makes keyboards like the old Lenovos.
  • The AC Adapter is so big and heavy that I bought two, just so I wouldn’t have to lug it around. On a positive note, it can double as ship ballast or a blunt weapon.
  • The latest hard-drives aren’t always available directly from computer manufacturers. You will need to be comfortable opening up your laptop and swapping some components.
  • This laptop will definitely not improve your carbon footprint.

 

Putting it All Together

At the time I ordered my laptop, the Samsung SM951 wasn’t available from Sager, nor was it available from regular retail outlets. In fact, it’s currently an “OEM only” component, meaning it is only sold directly to computer manufacturers like Dell, Lenovo, etc. for use in a select few of their laptops. That meant I had to order it from Ram City . . . in Australia. On a positive note, the exchange rate is pretty good right now, and Ram City’s customer service is excellent. Everything else was purchased as part of a complete laptop directly from Sager Notebooks.

Laptop

Summary

If you’re looking for a laptop that will present your EPM demos in a positive light, it’s worth it to color outside the lines, and to step away from the mainstream manufacturers. Purchasing a gaming laptop will allow you to use the fastest processors and the latest components in your pursuit of ultimate speed.

Disclaimer:

I have not been compensated by the companies discussed in this post in any way. All opinions expressed are my own and are based upon my own research and experience.

Introduction to Essbase Hybrid Aggregation Mode

The Best Thing Since Sliced Data

If you haven’t heard about Essbase Hybrid Aggregation Mode, you’re not alone. Oracle slipped this new calculation engine into the 11.1.2.3.500 patch without much fanfare. Documentation was light, and the list of limitations was long, however Oracle’s release strategy allowed early adopters to try out some fairly amazing new features. As a side benefit, the lack of documentation effectively prevented anyone from trying Hybrid Aggregation Mode in a production environment before the technology had time to “mature”.

When 11.1.2.4 was released, the list of limitations started to shrink. It reminded me of the release of ASO Essbase (when there was no writeback). That limitation didn’t last long, and the product became much better over time. I suspect we can anticipate the same rapid progress with Hybrid Aggregation Mode.

So what is it?

Hybrid Aggregation Mode is an option applied to BSO Essbase cubes that combines the strengths of BSO and ASO into a single cube. In simple terms, the bottom of the cube is BSO, while upper-level sparse member combinations are calculated dynamically . . . ASO-style.

2-23-2015 9-29-17 PMThe Benefits

This combination of features allows something special. It allows the BSO portion of the cube to be calculated using normal BSO calc scripts. But here’s the magic . . . sparse dimensions do not need to be aggregated. Just like an ASO cube, these aggregations are performed when the user retrieves data, in memory. This means developers don’t need to worry about level-0 data not aggregating correctly just because an agg script hasn’t completed. Sparse aggregations are effectively real-time, which is a serious step towards OLAP nirvana.

This design has some really interesting side effects. Because there are so few blocks in the cube, database restructures are much faster. Developers have a certain amount of control here as they can determine how much of the cube is BSO vs. ASO (I’ll show you how in our example). This has a follow-on effect of faster batch times and lower disk space requirements.

The most important benefit is reduced complexity.  Think of all of the applications out there that cobble together BSO and ASO cubes for maximum performance. These solutions require some serious jujitsu to keep hierarchies, data and security in sync. Now imagine these solutions reduced to a single cube . . . sounds pretty good, right?

The Catch

Unfortunately, the news isn’t all good. When a user retrieves data, Essbase will first try to perform the calculation (or aggregation) using Hybrid Aggregation Mode. If this is not possible (and there are a number of reasons why this might be the case), Essbase will try to perform a dynamic sparse calculation using Block Storage Mode. You may recall from Essbase Bootcamp that dynamic sparse calculations are not very efficient. Depending on how many blocks the calculation requires, these calculations can result in very poor retrieval performance. With enough users executing these performance crushing retrieves, the developer is essentially launching a denial of service attack on their own Essbase server. Therefore, it is very important to understand what calculations will fire in Hybrid, and what calculations will not. Let’s explore the details in our example below.

The Steps

The main steps for leveraging Hybrid Aggregation Mode are as follows:

  1. Create a BSO Essbase Cube
  2. Modify the Essbase Config File
  3. Set Upper-Level Sparse Members to Dynamic
  4. Load Data
  5. Calculate “Base” Blocks (if necessary)
  6. Retrieve Data
  7. Review the Log File

Step 1:  Create a BSO Essbase Cube

I’m going to assume most readers can create a BSO Essbase cube. There’s no need to build a fancy cube to play with Hybrid Aggregation Mode. I’m going to use a RedBox demo I built for a presentation to the North Texas Hyperion User Group. It’s simple, but it’s large.  It includes every RedBox location in the country.

3-2-2015 8-02-53 PM

Step 2:  Modify the Essbase Config File

To enable Hybrid Aggregation Mode, a setting is required in the Essbase Config file called “ASODYNAMICAGGINBSO”. This setting can be enabled or disabled for an entire server, application or specific database.

3-2-2015 8-23-59 PM

Developers can select from “Partial”, “Full” or “None” mode.

  • Partial enables Hybrid Aggregation Mode for simple outline aggregations (+, -, ~) only. Everything else (formulas, other unary operators, etc) will fire in Block Storage Mode.
  • Full enables Hybrid Aggregation Mode for simple outline aggregations (+, -, ~) and for certain formulas.  Note that there are quite a few limitations that we will cover later.
  • None disables Hybrid Aggregation Mode. This is the default.

Additional details on this setting for version 11.1.2.4 can be found here in the Essbase technical reference. Developers using version 11.1.2.3.500 will need to review the patch readme files.

Don’t forget to restart Essbase after updating the Config file.

Step 3:  Set Upper-Level Sparse Members to Dynamic

If you’ve worked with Essbase for very long, you probably know that Dynamic calculations work best with Dense dimensions and can be used sparingly with certain Sparse dimensions. But under no circumstances would we ever set all of our upper-level Sparse members to Dynamic . . . until now.

It seems crazy, but if you want to leverage Hybrid Aggregation Mode, you will need to set at least some of your upper-level sparse members to “Dynamic Calc”. In this example, I’m going to set them all to Dynamic. Just remember . . . any sparse members not set to dynamic will need to be aggregated with a calc script (assuming they are aggregated dimensions).

3-2-2015 11-36-54 PM

Step 4:  Load Data

We will need data for testing purposes. Mine is all fake test data . . . about 48,000 records.

Step 5:  Calculate “Base” Blocks (if necessary)

If you’ve ever written a procedural calculation for an ASO cube, you know they’re a bit clunky (even with the enhancements to Calc Manager). Hybrid Aggregation Mode gets us away from this technology by allowing us to use BSO Calc Scripts against the BSO portion of the cube. Remember, the “BSO portion of the cube” is represented by the non-dynamic sparse member combinations. In my example, this is simply level zero sparse members (because I set all aggregated upper-level sparse members to “Dynamic Calc”).

So if you need to run a stored calculation, break out your old BSO calc scripting skills! But make sure you only reference the BSO portion of the cube in your calc script, otherwise, you’ll take a performance hit.

Step 6:  Retrieve Data

This is it . . . La Pièce de Résistance!!!  We can now retrieve data at upper-level sparse member combinations, and we never had to aggregate our cube. In fact, our cube has no upper-level blocks at all.

3-2-2015 11-48-23 PM

Some interesting notes about the query above:

  • Note that Hybrid now supports Dynamic Time Series & Time Balance (not supported in 11.1.2.3.500).
  • In Block Storage Mode, without pre-aggregation, this query would have required over 40,000 blocks.
  • In Hybrid Aggregation Mode, the query only took 0.38 seconds (with no tuning whatsoever).

3-2-2015 11-56-35 PM

Step 7:  Review the Log File

The best way to verify that a query successfully ran in Hybrid Aggregation Mode is to look in the application log.

3-2-2015 11-44-47 PM

The green circle above indicates that a query ran in Hybrid Aggregation Mode.

Here’s an example of a query that did not successfully run in Hybrid Aggregation Mode.

3-3-2015 12-06-37 AM

You’ll want to monitor your logs to ensure the text above (circled in red) isn’t showing up.  The good news is that if Hybrid fails, Essbase will tell you why. The error above tells me that it failed because I have a member called “ThisIsABadAccount”, and it has a cross-dimensional operator in the formula.  We will cover Hybrid Aggregation Mode limitations in more detail below.

3-3-2015 12-10-16 AM

It should be noted that the member formula above is perfectly acceptable as long as you’re retrieving data in the BSO section of the cube. But once you stray into the ASO section (think upper-level sparse member), the query goes south and reverts back into Block Storage Mode (and becomes quite slow).

Certain Limitations Apply

There is a healthy list of limitations regarding formulas that can execute in Hybrid Aggregation Mode. But keep in mind these only apply to calculations you’re running in memory in the ASO portion of the cube, and not to stored calculations that you may execute as part of a calc script on the BSO portion of the cube. None of the limitations below apply to your BSO calc script as long as it is referencing stored sparse member combinations.

Some things you can not do in Hybrid Aggregation Mode:

  • Attribute Calcs
  • Formulas with Cross Dimensional Operators
  • Dynamic Calcs with Formulas that are Target of Transparent Partition
  • Queries with both Two Pass and One Pass Dynamic Calc members from Same Dimension
  • XOLAP
  • Text Measures

Any of the above situations will cause your Essbase query to revert back to Block Storage Mode. This doesn’t mean that the query won’t work. But it probably means it’ll be slow. Potentially very slow.

In addition to the limitations above, there are restrictions on formulas that reference other dimensions. The situations below will work in Hybrid Aggregation Mode.

(Anything else will not.)

  • Sparse-to-Sparse:   Formula on Sparse member . . . only references other Sparse members.
  • Dense-to-Dense:   Formula on Dense member . . . only references other Dense members.
  • Sparse-to-Sparse_AND_Dense:   Formula on Sparse member . . . references both Sparse and Dense members. In this case, dense members must be stored.

Lastly, there are certain formulas that are not supported with Hybrid. For a complete list, click here. This list is different for 11.1.2.3.500, so check the Essbase readme file if you’re using that version.

Remember that by querying members with formulas, Essbase will revert to Block Storage Mode if the “ASODYNAMICAGGINBSO” property is set to “Partial”. Use “Full” if you plan on leveraging member formulas with Hybrid Aggregation Mode.

An Ounce of Prevention is Worth a Pound of Cure

We spend a lot of time testing our cubes. But what if we miss something? Imagine a scenario where your user executes a query, and for whatever reason, the query reverts back to Block Storage Mode. Essbase is now feverishly trying to pull a bazillion blocks into memory. Now imagine that lots of other users are running the same query. The lights in the building start to dim . . . you might want to put a query governor in place.

We can limit either the amount of time a query will run, or the number of blocks a query can reference (or both).

QRYGOVEXECTIME [appname [dbname]] n

- Sets max amount of time a query can execute before being terminated

- Measured in seconds

QRYGOVEXECBLK [appname [dbname]]

- Sets max number of blocks a query can retrieve before being terminated

Conclusions

When Hybrid Aggregation Mode came out with the 11.1.2.3.500 patch, there were two deal-killer limitations. Both Dynamic Time Series and Time Balance account settings would force calculations back to Block Storage Mode. And with the limitations on formulas, it was impossible to create your own custom DTS or Time Balance. Both of these limitations were addressed with 11.1.2.4. Many of the remaining limitations appear to have workarounds (try moving complex calc logic to calc scripts). In my opinion, the limitations are getting much less restrictive.

Is Hybrid ready for production cubes? Maybe. That will depend on risk tolerance within your organization. It’s still very new. But with the enhancements in 11.1.2.4, the documentation, and the support for usage with Hyperion Planning . . . it’s getting tempting.

Pop Quiz!

You’ve read this far . . . surely you want to test your knowledge, right?

I’ll post answers on the blog in a week or so.

  1. Which of the following steps are required to enable Hybrid Aggregation Mode?
    1. Essbase Config File Setting
    2. Create a BSO Essbase Cube
    3. Set Upper-Level Dense Members to “Dynamic Calc”
    4. Pray to the Hyperion Gods
  2. True or False:   Essbase 11.1.2.3.500 supports dynamic time series with Hybrid Aggregation Mode.
  3. True or False:   Sparse dynamic calculations are generally fast in Block Storage Mode.
  4. True or False:   Essbase does not create upper-level blocks associated with dynamic sparse member combinations when Hybrid Aggregation Mode is enabled.
  5. True or False:   A formula that is not technically supported in Hybrid Aggregation Mode may actually run in Hybrid Aggregation Mode if the query avoids upper-level sparse dynamic member combinations.
  6. True or False:   The default “mode” for the “ASODYNAMICAGGINBSO” Essbase config file setting is “None”.
  7. True or False:   Developers should use Essbase query governors if Hybrid Aggregation Mode is enabled.
  8. True or False:   Multiplication and Division unary operators will function in Hybrid Aggregation Mode.
  9. True or False:   A formula on a Dense member that references a Sparse member will execute in Hybrid Aggregation Mode.
  10. True or False:   A query that includes both a Two-Pass and a One-Pass dynamic calc member from the same dimension will execute in Hybrid Aggregation Mode.

Bonus Question!  (This wasn’t covered in the post, so you may have to search . . . )

True or False:   The file system associated with the ASO-style portion of a Hybrid cube disappears when the application is stopped.

I hope you’ll take Hybrid Aggregation Mode for a spin!

DIY Hyperion Planning – Create a Planning App

Some Context . . .

This is the ninth (and final) post in a multi-part blog educating readers on how to build a personal “sandbox” environment for Hyperion Planning. Click here to see all nine posts in the series.

Creating a Test Hyperion Planning Application

Once the EPM products are configured, there are three additional prerequisites for creating an application:

  • Create the Planning Application Repository
  • Create the Planning Application Repository Schema User
  • Create the Planning Data Source

If you’ve been following this tutorial from the beginning, you will have already created the Repository (PLANAPP1) and Repository User (also PLANAPP1). These were created when you pasted the SQL code into SQL Plus.

For the purposes of this example, we will be creating a “Classic” Hyperion Planning application. The alternative approach (using EPMA) is rapidly losing popularity. Oracle does not appear to be investing in this product, so “Classic” it is . . .

Start the EPM Services

Locate the “Start EPM System” icon within the apps on your VM. (I would recommend right-clicking on this icon and selecting “Pin to Start”. Do the same with the “Stop EPM System” script.) Run the start script to startup the EPM services on your VM. Some notes about the startup process:

  • On my laptop, startup takes a little less than 9 minutes.
  • Faster processors and hard drives will improve startup time.
  • Compact deployments will start faster than “regular” deployments.
  • The script will close when startup is complete.
  • Watch Task Manager on the VM during startup to monitor CPU and memory utilization.
  • Upon completion of the script, ensure that all services started as expected.
  • Don’t forget to stop the EPM services before you shut down your VM.

Create a Data Source

Once the services have started, open a browser on your host. Navigate to the following URL:

http://<INSERT SERVER NAME HERE>:8300/HyperionPlanning

2-17-2015 11-46-14 AM

Enter the Shared Services admin user ID and password.

Select “Sign In”.

2-17-2015 11-49-38 AM

Select “Manage Data Source”.

2-17-2015 11-50-02 AM

Select “Create”.

2-17-2015 8-08-47 AM

Enter the App Database connectivity information.

Enter the Essbase Server connectivity information.

Validate each connection.

Select “Save”.

Create the Planning Application

2-17-2015 8-10-22 AM

Select Manage Applications

2-17-2015 8-12-30 AM

Select “Create”.

2-17-2015 8-55-03 AM

Select “Advanced Planning”.

2-17-2015 8-55-46 AM

Name your application and select “Next”.

2-17-2015 8-57-05 AM

Select the application parameters. The simplified Planning interface appears to force applications to have an ASO plan type.

(This would appear to be a bug, as the on-premise interface does not require this.)

Select “Next”.

2-17-2015 8-57-38 AM

Review the application settings. Select “Create”.

2-17-2015 8-58-10 AM

Planning will begin creating the application.

2-17-2015 8-58-35 AM

Select “OK”. The application interface will be displayed.

2-17-2015 8-58-58 AM

Next Steps

With the application created, users may now begin the typical Hyperion Planning development activities, including:

  • Building Dimensions
  • Building Input Forms
  • Loading Data
  • Building Calc Manager Rules
  • Building Reports
  • Creating Approvals Processes

DIY Hyperion Planning – Configure Hyperion Planning

Some Context . . .

This is the eighth post in a multi-part blog educating readers on how to build a personal “sandbox” environment for Hyperion Planning. Click here to see all nine posts in the series.

Installation vs Configuration

When we installed Hyperion Planning and the related EPM applications in the previous post, we installed all products at once. As we move on to configuration, we will configure the products one at a time. Could we attempt to configure everything at once? Yes, however in previous versions, this caused problems, so we will take a more conservative approach.

Configure Shared Services

To launch the configuration utility, navigate to the Apps window in Server 2012 (from the Start Menu).

2-9-2015 1-48-44 PM

Right-click on the “EPM System Configurator” and select “Run as Administrator”. The EPM System Configurator will be displayed.

2-9-2015 1-51-33 PM

Select the Home Directory and Instance Name. Select “Next”.

2-13-2015 4-40-42 PM

Select “Perform first time configuration of Shared Services database”.

Confirm the database driver, server and port.

Enter the Oracle SID, user name and password for the Shared Services repository.

ENSURE THAT THE USER ID AND PASSWORD ARE CORRECT!!! If the wrong user ID and password are used, Shared Services tables and other objects will be created in the wrong schema, making a mess.

Select “Next”.

The screen below should be displayed.  If a network IO error is displayed instead, please see the point in the previous post about updating the server name in the “listener.ora” file.  Ensure that the server was rebooted (or the Oracle RDBMS service restarted) after that change.

2-9-2015 2-07-42 PM

Select “Uncheck All” and expand “Hyperion Foundation”.

2-9-2015 2-10-23 PM

Select “Configure Common Settings” and “Configure Database”.

Select “Next”.

2-14-2015 12-01-19 AM

Accept the defaults and select “Next”.

2-9-2015 2-13-19 PM

Enter a user ID and password for the Shared Services admin user.

Make a note of this very important user ID and password.

Select “Next”.

2-9-2015 2-14-20 PM

Confirm the configuration tasks.

Select “Next”.

2-9-2015 2-22-02 PM

Ensure that all configuration steps are successful.

Select “Task Panel” to continue the configuration process, or “Finish”. If “Finish” is selected, simply restart the Config Tool to continue the configuration process.

Configure Essbase and EAS

Select the options below in the Config Tool to configure Essbase and EAS.

2-9-2015 3-00-02 PM

Select “Next”.

2-13-2015 5-02-00 PM

Update the Username and Password for the correct EAS schema.

ENSURE THAT THE USER ID AND PASSWORD ARE CORRECT!!! If the wrong user ID and password are used, EAS tables and other objects will be created in the wrong schema, making a mess.

Select “Next”.

2-13-2015 5-05-29 PM

Accept the default Essbase server information. Select “Next”.

2-13-2015 11-58-26 PM

Confirm the configuration tasks.

Select “Next”.

2-9-2015 7-59-59 PM

Ensure that all tasks complete successfully.

Select “Task Panel” to continue the configuration process, or “Finish”. If “Finish” is selected, simply restart the Config Tool to continue the configuration process.

Configure EPMA

Select the options below in the Config Tool to configure EPMA.

2-9-2015 8-04-41 PM

Select “Next”.

2-13-2015 5-13-22 PM

Update the Username and Password for the correct EPMA schema.

ENSURE THAT THE USER ID AND PASSWORD ARE CORRECT!!! If the wrong user ID and password are used, EPMA tables and other objects will be created in the wrong schema, making a mess.

Select “Next”.

2-9-2015 8-07-41 PM

Accept the default dimension server ports.

Select “Next”.

2-9-2015 8-08-31 PM

Confirm the configuration tasks.

Select “Next”.

2-9-2015 8-12-44 PM

Confirm that all configuration steps completed successfully.

Select “Task Panel” to continue the configuration process, or “Finish”. If “Finish” is selected, simply restart the Config Tool to continue the configuration process.

Configure Planning

Select the options below in the Config Tool to configure Planning.

2-9-2015 8-19-17 PM

Select “Next”.

2-13-2015 5-25-10 PM

Update the Username and Password for the correct Planning schema.

ENSURE THAT THE USER ID AND PASSWORD ARE CORRECT!!! If the wrong user ID and password are used, Planning tables and other objects will be created in the wrong schema, making a mess.

Select “Next”.

2-9-2015 8-21-19 PM

Accept the default RMI port.

Select “Next”.

2-9-2015 8-22-46 PM

Confirm the configuration tasks.

Select “Next”.

2-9-2015 8-27-08 PM

Confirm that all configuration steps completed successfully.

Select “Task Panel” to continue the configuration process, or “Finish”. If “Finish” is selected, simply restart the Config Tool to continue the configuration process.

Configure Calc Manager

Select the options below in the Config Tool to configure Calc Manager.

2-9-2015 8-35-12 PM

Select “Next”.

2-13-2015 7-43-44 PM

Update the Username and Password for the correct Calc Manager schema.

ENSURE THAT THE USER ID AND PASSWORD ARE CORRECT!!! If the wrong user ID and password are used, Calc Manager tables and other objects will be created in the wrong schema, making a mess.

Select “Next”.

2-9-2015 8-36-44 PM

Confirm the configuration tasks.

Select “Next”.

Confirm that all configuration steps completed successfully.

2-9-2015 8-39-30 PM

Select “Task Panel” to continue the configuration process, or “Finish”. If “Finish” is selected, simply restart the Config Tool to continue the configuration process.

Configure Financial Reports

Select the options below in the Config Tool to configure Financial Reports.

2-9-2015 8-44-31 PM

Select “Next”.

2-13-2015 7-57-05 PM

Update the Username and Password for the correct Reporting and Analysis schema.

ENSURE THAT THE USER ID AND PASSWORD ARE CORRECT!!! If the wrong user ID and password are used, Reporting and Analysis tables and other objects will be created in the wrong schema, making a mess.

Select “Next”.

2-9-2015 8-47-06 PM

Select the default location and port range.

Select “Next”.

2-9-2015 8-47-56 PM

Accept the default ports.

Select “Next”.

2-9-2015 8-49-01 PM

Select the default RMI ports.

Select “Next”.

2-9-2015 8-50-23 PM

Confirm the configuration tasks.

Select “Next”.

2-9-2015 8-57-17 PM

Confirm that all configuration steps completed successfully.

Select “Task Panel” to continue the configuration process, or “Finish”. If “Finish” is selected, simply restart the Config Tool to continue the configuration process.

Deploy the App Server

The previous steps configured the repositories of each individual product. The next step is to deploy to the application server. We will do this for all products at once.

Select the options below in the Config Tool to deploy to the application server.

2-9-2015 9-09-56 PM

Select “Next”.

2-13-2015 8-25-49 PM

Enter a password for the domain admin.

Select “Next”.

2-9-2015 9-24-15 PM

Pay special attention to the check box at the top of this screen. The option to deploy the Java web applications to a single managed server is referred to as a “Compact Deployment”. Compact Deployment is often useful when limited server memory is available. (E.g., less than 16GB) Unfortunately, compact deployment can make it difficult to debug problems with individual products. I prefer to not use Compact Deployment; however there are situations where it is appropriate.

Select “Next”.

2-9-2015 9-33-47 PM

Confirm the configuration tasks.

Select “Next”.

2-9-2015 11-23-39 PM

Confirm that all configuration steps completed successfully.

Select “Task Panel” to continue the configuration process, or “Finish”. If “Finish” is selected, simply restart the Config Tool to continue the configuration process.

Configure Web Server

Select the options below in the Config Tool to configure the web server.

2-9-2015 11-29-55 PM

Select “Next”.

2-9-2015 11-38-32 PM

Use the Oracle HTTP Server.

Select “Next”.

2-9-2015 11-39-50 PM

Confirm the configuration tasks.

Select “Next”.

A command prompt will open.  A script will launch and eventually close.

2-9-2015 11-53-47 PM

Confirm that the configuration was a success.

Select “Finish”.

Configure the Services

The services created by the Config Tool in the previous steps will be set to start automatically. I prefer setting these to start manually. They can then be started by running the pre-built startup and shutdown scripts.

** NOTE – Most infrastructure consultants create custom start/stop scripts.

To change the services to manual startup, navigate to Control Panel by selecting the “Start” button.

Select “Administration Tools”.

2-10-2015 11-31-45 AM

Double-click “Services”. Scroll down to the Oracle services.

2-10-2015 11-33-05 AM

Right-click on each Oracle service (except RDBMS services) and select “Properties”.

2-10-2015 11-33-45 AM

Change the startup type to “Manual”.

Select “OK”.

2-10-2015 1-03-52 PM

Perform this step for each service in the red square above. The remaining Oracle services are RDBMS-related, and can be left as-is.

Starting the EPM System

Restart your VM.

Navigate to the applications in Windows Server 2012.

2-10-2015 3-46-14 PM

Take note of the two scripts above used to Start and Stop EPM services (circled in green).

Select the “Start EPM System” icon. A script will run to start the EPM services (this may take a bit). Once the script completes, confirm that all Oracle EPM services are running by navigating to Services.

2-12-2015 4-00-13 PM

Alternatively, you may also review the startup logs in:

C:\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\starter

The Moment of Truth . . .

Open a browser on your host.

Navigate to the following URL to access Workspace:

http://<INSERT SERVER NAME HERE>:19000/workspace/index.jsp

2-10-2015 3-55-25 PM

Login with the Shared Services “admin” user ID and password.

Logout.

Navigate to the following URL to access the new simplified Hyperion Planning interface:

http://<INSERT SERVER NAME HERE>:8300/HyperionPlanning

2-17-2015 11-46-14 AM

Login with the “admin” user ID and password.

A screen will be displayed stating that there are no applications (we will create an app in the next post).

Logout.

Run the script to shut down the EPM Services. Always try to gracefully shut down the services prior to shutting down your VM.

Take a snapshot.

Congratulations!!!  You now have your very own Hyperion Planning sandbox environment.

In the Next Post

Things should be looking very good at this point.  The next step is to build an actual Hyperion Planning application!  Click here for the last post in the series.

DIY Hyperion Planning – Install Hyperion Planning

Some Context . . .

This is the seventh post in a multi-part blog educating readers on how to build a personal “sandbox” environment for Hyperion Planning. Click here to see all nine posts in the series.

So far . . . so good . . .

In the previous posts, we laid the foundation for our Hyperion Planning environment. We completed the following steps:

  • Downloaded the Relevant Files
  • Created a Virtual Server
  • Installed and Configured the Server Operating System
  • Installed and Configured the Oracle RDBMS
  • Created our RDBMS Users and Schemas

Now would be a good time to take a snapshot of your virtual machine.

Installation Time

To begin the Hyperion Planning installation, navigate to the zipped files previously downloaded from e-delivery. Unzip all of the downloaded files. I recommend 7zip. Unzip these files from the host computer.

2-9-2015 12-39-55 AM

You may receive warnings about the location of your unzipped files already having certain files in place. This is because Oracle sometimes packages the same file in multiple zip files, and should not cause concern.

2-9-2015 12-42-23 AM

Simply select “Yes to All” when prompted. After unzipping, your folder structure will look something like this:

2-9-2015 12-45-25 AM

Login to your VM. Navigate to the unzipped files (on your shared folder) and locate the file “installTool.cmd”. Right click on this file and select “Run as Administrator”.

2-9-2015 1-07-31 AM

Select a language.

2-9-2015 1-08-33 AM

Select “OK”.

The EPM System Installer will open.

2-9-2015 1-09-38 AM

Make sure that the prerequisite checks all pass. If any of the four lines above do not have a check box, you must stop the installation, as something is wrong.

Assuming all four lines have a check next to them, select “Next”.

2-9-2015 1-12-58 AM

Select a Middleware Home.

Select “Next”.

2-9-2015 1-16-55 AM

Choose the install type (only one option will be available).

Select “Next”.

2-9-2015 5-59-00 PM

Select the software to install.  Note that we are not really installing Financial Management above.  There is a required component under this product.

Select “Next”.

A .NET warning will appear.

2-9-2015 1-20-06 AM

Select “OK”.

Confirm the components to be installed.

Select “Next” and the installation will begin. This portion will take some time.

2-9-2015 6-23-16 PM

Ensure that everything was installed successfully. Select “Finish”. Take a snapshot before configuring the software.

In the Next Post

With Hyperion Planning installed, it’s time to move on to the configuration. Click here for the next post in this series.

DIY Hyperion Planning – Create the Repositories

Some Context . . .

This is the sixth post in a multi-part blog educating readers on how to build a personal “sandbox” environment for Hyperion Planning. Click here to see all nine posts in the series.

Configure Repositories

Most products in the EPM stack require a relational repository. In the steps below, we will create blank schemas in our Oracle 12c database. Later, these schemas will be populated automatically during product configuration. But for now, all we need are blank schemas, user ID’s and some general settings.

To get started, make sure your VM is up and running.

2-5-2015 2-11-33 PM

Within your VM, open SQL Plus.

2-5-2015 2-13-42 PM

Enter the user ID “system”. Use the password entered during the Oracle 12c installation . . . this is the administrative password.

Paste the following commands into SQL Plus:

/* Database Settings */
ALTER SYSTEM SET OPEN_CURSORS=5000 scope=SPFILE;
ALTER SYSTEM SET PROCESSES=1000 scope=SPFILE;
ALTER SYSTEM SET SESSIONS=2000 scope=SPFILE;
ALTER SYSTEM SET SESSION_CACHED_CURSORS=200 scope=SPFILE;

/* Increase Temp Tablespace Size */
ALTER DATABASE
TEMPFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\TEMP01.DBF'
DROP INCLUDING DATAFILES;

ALTER TABLESPACE TEMP ADD TEMPFILE
'C:\APP\ORACLE_SA\ORADATA\ORCL\TEMP01.DBF' SIZE 2G
AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

/* Create EAS Repository */
CREATE SMALLFILE TABLESPACE EAS
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\EAS_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;

CREATE USER EAS IDENTIFIED BY epm_EAS
DEFAULT TABLESPACE EAS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT
CREATE ANY SYNONYM
, CREATE CLUSTER
, CREATE INDEXTYPE
, CREATE PROCEDURE
, CREATE SEQUENCE
, CREATE SESSION
, CREATE TABLE
, CREATE TRIGGER
, CREATE TYPE
, CREATE VIEW
, DROP ANY SYNONYM
, UNLIMITED TABLESPACE
TO EAS;

/* Create Shared Services Repository */
CREATE SMALLFILE TABLESPACE SS
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\SS_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;

CREATE USER SS IDENTIFIED BY epm_SS
DEFAULT TABLESPACE SS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT
CREATE ANY SYNONYM
, CREATE CLUSTER
, CREATE INDEXTYPE
, CREATE PROCEDURE
, CREATE SEQUENCE
, CREATE SESSION
, CREATE TABLE
, CREATE TRIGGER
, CREATE TYPE
, CREATE VIEW
, DROP ANY SYNONYM
, UNLIMITED TABLESPACE
TO SS;

/* Create EPMA Repository */
CREATE SMALLFILE TABLESPACE EPMA
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\EPMA_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;

CREATE USER EPMA IDENTIFIED BY epm_EPMA
DEFAULT TABLESPACE EPMA TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT
CREATE ANY SYNONYM
, CREATE CLUSTER
, CREATE INDEXTYPE
, CREATE PROCEDURE
, CREATE SEQUENCE
, CREATE SESSION
, CREATE TABLE
, CREATE TRIGGER
, CREATE TYPE
, CREATE VIEW
, DROP ANY SYNONYM
, UNLIMITED TABLESPACE
TO EPMA;

/* Create Planning Repository */
CREATE SMALLFILE TABLESPACE PLANSYS
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\PLANSYS_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;

CREATE USER PLANSYS IDENTIFIED BY epm_PLANSYS
DEFAULT TABLESPACE PLANSYS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT
CREATE ANY SYNONYM
, CREATE CLUSTER
, CREATE INDEXTYPE
, CREATE PROCEDURE
, CREATE SEQUENCE
, CREATE SESSION
, CREATE TABLE
, CREATE TRIGGER
, CREATE TYPE
, CREATE VIEW
, DROP ANY SYNONYM
, UNLIMITED TABLESPACE
TO PLANSYS;

/* Create Planning App Repository */
CREATE SMALLFILE TABLESPACE PLANAPP1
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\PLANAPP1_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;

CREATE USER PLANAPP1 IDENTIFIED BY epm_PLANAPP1
DEFAULT TABLESPACE PLANAPP1 TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT
CREATE ANY SYNONYM
, CREATE CLUSTER
, CREATE INDEXTYPE
, CREATE PROCEDURE
, CREATE SEQUENCE
, CREATE SESSION
, CREATE TABLE
, CREATE TRIGGER
, CREATE TYPE
, CREATE VIEW
, DROP ANY SYNONYM
, UNLIMITED TABLESPACE
TO PLANAPP1;

/* Create Calc Manager Repository */
CREATE SMALLFILE TABLESPACE CALC
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\CALC_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;

CREATE USER CALC IDENTIFIED BY epm_CALC
DEFAULT TABLESPACE CALC TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT
CREATE ANY SYNONYM
, CREATE CLUSTER
, CREATE INDEXTYPE
, CREATE PROCEDURE
, CREATE SEQUENCE
, CREATE SESSION
, CREATE TABLE
, CREATE TRIGGER
, CREATE TYPE
, CREATE VIEW
, DROP ANY SYNONYM
, UNLIMITED TABLESPACE
TO CALC;

/* Create Reporting and Analysis Repository */
CREATE SMALLFILE TABLESPACE BIPLUS
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\BIPLUS_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;

CREATE USER BIPLUS IDENTIFIED BY epm_BIPLUS
DEFAULT TABLESPACE BIPLUS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT
CREATE ANY SYNONYM
, CREATE CLUSTER
, CREATE INDEXTYPE
, CREATE PROCEDURE
, CREATE SEQUENCE
, CREATE SESSION
, CREATE TABLE
, CREATE TRIGGER
, CREATE TYPE
, CREATE VIEW
, DROP ANY SYNONYM
, UNLIMITED TABLESPACE
TO BIPLUS;

The table below includes a summary of the repositories created in the SQL above.

Schema Description User ID Password
SS Shared Services SS epm_SS
EPMA Enterprise Performance Management Architect EPMA epm_EPMA
EAS Essbase Administration Services EAS epm_EAS
PLANSYS Planning System Repository PLANSYS epm_PLANSYS
PLANAPP1 Planning Application Repository PLANAPP1 epm_PLANAPP1
CALC Calculation Manager CALC epm_CALC
BIPLUS Financial Reporting and Web Analysis BIPLUS epm_BIPLUS

The following screenshot illustrates the creation of the EPMA repository and user:

2-5-2015 3-00-55 PM

In the Next Post

Now that the application schemas and user ID’s have been created, we are finally ready to install Hyperion Planning and the related EPM products!!! Click here for the next post in the series.

DIY Hyperion Planning – Install Oracle RDBMS

Some Context . . .

This is the fifth post in a multi-part blog educating readers on how to build a personal “sandbox” environment for Hyperion Planning. Click here to see all nine posts in the series.

Installing the Oracle Database

Although Hyperion Planning stores the majority of its data in Essbase, most of the products in the EPM stack have relational repositories. For this reason, a relational database is required. In this example, we will use Oracle’s 12c database.

Shared Folders

Before we can install any software on our VM, we need to ensure that our server can see files stored on our host (because we downloaded all of the software on our host and there’s no point in storing these files on the VM as well). VMware Workstation has the ability to create “Shared” folders that show up as mapped drives on the VM. This makes it easy to access files on the host from the VM.

From VMware Workstation, right click on the server.

2-13-2015 9-32-42 AM

Select “Settings”, then select the “Options” tab.

Select the “Shared Folders” setting.

Select the “Always Enabled” radio button. Check the box to map as a network drive.

Select “Add”. The following wizard will be displayed:

2-3-2015 9-21-56 PM

Select “Next”.

2-3-2015 9-22-42 PM

Browse to a directory. Make sure this directory contains the software downloaded from the second post in this series. (Note – you can map an entire drive if you want.) Provide a name for the shared folder.

Select “Next”.

2-3-2015 9-24-28 PM

Select the check box to enable the share. Select “Finish”.

Select “OK”.

If your virtual server isn’t already running, start it.  Check to ensure you can access your host from the virtual machine in File Explorer.

2-3-2015 9-32-21 PM

Install Oracle 12c

In the second post in this series, we downloaded the following files from edelivery:

File Contents
V38894-01 Part 1 of 2 Oracle Database 12c Release 1 (12.1.0.1.0) for Microsoft Windows x64 (64-bit) (Part 1 of 2)
V38894-01 Part 2 of 2 Oracle Database 12c Release 1 (12.1.0.1.0) for Microsoft Windows x64 (64-bit) (Part 2 of 2)

 

Unzip these files into a single directory on your host.

Make sure these files are available under the directory you shared in the steps above.

After logging into your virtual server, open File Explorer. Browse to the shared folder in “Network locations”.

Make sure you can access the Oracle 12c software that was previously unzipped on your host.

Open the “database” folder.

Right-click on “setup” and select “Run as administrator”. The Oracle Universal Installer will open.

2-4-2015 7-49-46 PM

Enter your Oracle support contact information if desired. This is not required. Select “Next”.

2-4-2015 7-51-56 PM

Enter Oracle Support credentials to download software or updates, or skip software updates.

Select “Next”.

2-4-2015 7-53-51 PM

Select “Create and configure a database”. Select “Next”.

2-4-2015 7-55-08 PM

Select “Desktop class”. Select “Next”.

2-4-2015 7-58-15 PM

Select “Create New Windows User”. Enter a user ID and password. Write these down.

2-9-2015 5-16-03 PM

Accept the default file locations.

Select the database edition.

Use the “AL32UTF8” Character set.

Enter an admin password.

DO NOT CREATE AS A CONTAINER DATABASE!!!  Please make sure this check box is not selected.

2-4-2015 8-06-52 PMSelect “Next”. Installation will begin.

The installer will check prerequisites and then present a summary of selected options.

2-9-2015 5-17-16 PM

Select “Install”.  This part takes a while.  Eventually the screen below will be displayed.

2-16-2015 4-05-15 PM

Select “OK”, then “Close”.

Update Oracle Networking Files

There are three key files that must be updated, called “tnsnames.ora”, “listener.ora” and “sqlnet.ora”.  These files are located in the following directory:

C:\app\Oracle_sa\product\12.1.0\dbhome_1\NETWORK\ADMIN

Edit the “listener.ora” file.  Update the HOST with the static IP address you assigned to the network adapter.

2-16-2015 5-37-33 PM

Save the “listener.ora” file.

Edit the “tnsnames.ora” file.  Update the HOST with the static IP address you assigned to the network adapter.

2-16-2015 5-38-09 PM

Note that the IP address is updated in two locations above.

Save the “tnsnames.ora” file.

Edit the “sqlnet.ora” file.  Add the line circled below.

2-16-2015 6-03-38 PM

Save the “sqlnet.ora” file.

Shut down your VM and take a snapshot.

In the Next Post

With the relational database installed, it’s time to create the application repositories. Click here for the next post in the series.

DIY Hyperion Planning – Create a Virtual Server

Some Context . . .

This is the fourth post in a multi-part blog educating readers on how to build a personal “sandbox” environment for Hyperion Planning. Click here to see all nine posts in the series.

Creating a Virtual Machine

Now that VMware Workstation has been installed, the fun begins. The following steps will create a virtual server that runs on your host. In my case, the host is my laptop. Make sure you have your server operating system software available, along with any required license key.

To get started, launch VMware Workstation.

0126

Select “Create a New Virtual Machine”.

0125

Create a “Typical” VM. Select “Next”.

0124

Tell VMware where the Windows Server installation media is located. You can point to either a drive, or an ISO file. I’m using an ISO file above. Select “Next”.

0122

Select the version of Windows you are using. Enter the product key, user ID and password. This is the user ID and password that you will use to log into Windows Server 2012. Select “Next”.

 

2-13-2015 8-23-10 AM

Enter a name for the virtual machine. This is not the actual “computer name” in Windows, but rather the name of the VM within VMware Workstation. I tend to use the same name from my VM and Windows computer name.

Provide a location for the files. I recommend creating a specific directory for each VM as many files are created. Having them all in a single directory makes it easier to move them around. Select “Next”.

0028

Give your VM somewhere around 70-100GB. You can use less space if you don’t intend on loading a lot of data into Essbase.

Make sure to select the option to split the VM files into multiple files. This will simplify moving these files at a later date. Select “Next”.

2-13-2015 8-23-59 AM

Review your VM settings. Be especially aware of where you are placing the VM files as these can take significant space.

Some of these settings will be updated later. Select “Finish”.

VMware will begin installing Windows Server 2012 R2.

0025

The install takes a bit. Windows Server will eventually restart.

0023

Your server operating system is now installed on your VM, however there are some remaining configuration steps that are still required.

Changing the Server Name

After logging back into your virtual server, Server Manager should be displayed. Select the “Local Server” tab.

0021

Click on the server name (mine is “WIN-LPSIVO2KTLS” above, however yours will be different).

0020

Press the “Change” button, and provide a name for the virtual server.

2-13-2015 8-40-10 AM

Select “OK”. After being prompted to restart, select “OK”. Close the System Properties window and select “Restart Now”.

Run Windows Update (Optional)

Navigate to Control Panel.  Select “System and Security”.  Under “Windows Update”, select “Check for Updates”.

0018

Select “Install Updates”. Get some coffee . . . this takes a while.

0017

Select “Restart Now”.

Firewalls

Firewalls can complicate the installation and configuration of certain EPM products. If you use your VM’s intermittently, you may consider turning off the firewall on your VM’s operating system. Please note that this would likely not be appropriate for a “real” environment. To turn off the Windows firewall on your VM, launch the Server Manager dashboard. Select “Local Server”.

2-13-2015 8-47-59 AM

Click the “Public: On” link.

Select “Turn Windows Firewall on or off”. Turn off the firewall.

2-4-2015 6-06-42 PM

Select “OK”.

Select “Finish”.

Assign Static IP Addresses

Navigate to Control Panel.

2-16-2015 1-01-31 AM

Select “Network and Internet”.

2-16-2015 1-02-46 AM

Select “Network and Sharing Center”.

2-16-2015 5-33-45 PM

Select “Change Adapter Settings”.

2-16-2015 5-34-25 PM

Right-click on the network adapter.  Select “Properties”.

Double-click on “Internet Protocol Version 4 (TCP/IPv4)”.

2-16-2015 5-35-02 PM

Assign a “non-routable” IP address (like the one I used above).

Select “OK”, then select “OK” again.

Update the HOSTS File

Edit the hosts file located in:

C:\Windows\System32\drivers\etc

2-16-2015 5-36-00 PM

Add the line circled above, using the static IP address you assigned to your network adapter. Make sure to use the name of your server.

Disable UAC

Before installing Hyperion Planning, UAC (User Account Control) must be deactivated. To do this, open a command prompt. Press the Windows Key then “X”.

2-4-2015 7-26-43 PM

Select “Command Prompt (Admin)”.

Type “regedit” in the command prompt.

Navigate to “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\policies\system”.

2-4-2015 7-30-58 PM

Select the “system” member above.

Right-click the DWORD “EnableLUA”. Select “Modify”.

2-4-2015 7-32-39 PM

Change the data value from 1 to 0. Select “OK”.

Restart the virtual server.

Miscellaneous VM Settings

Before going too far, now may be a good time to increase the memory and processors dedicated to the VM. I recommend 12GB of RAM for a “compact deployment” and 20GB for a regular deployment. I also start with 4 processors. These settings can be changed later on, so don’t worry too much about getting them exactly “right”.

Click on your VM’s memory under “Devices”.

2-13-2015 9-28-36 AM

Increase memory and CPU count as required.

Select “OK”.

Photo Finish

At this point, your operating system is configured! After all of this work, it’s a good idea to take a snapshot in VMware Workstation. In the event that something goes wrong later in the installation, developers can revert to a snapshot so they aren’t starting over from scratch.

The ability to revert to a snapshot is the most incredibly awesome feature of any virtualization software. It allows developers to try things, make mistakes, and then revert to an old snapshot. It’s like a free “do-over”. But it only works if you actually take the snapshot, so take snapshots often. And be aware that they do take disk space.

Right-click on the server name in VMware Workstation and select “Snapshot”, then “Take Snapshot”.

2-13-2015 9-30-48 AM

Take notes regarding the work done to date.

Select “Take Snapshot”.

Restart the VM.

In the Next Post

With the operating system installed, it’s time to install and configure the relational database. Click here for the next post in the series.

DIY Hyperion Planning – Install VMware Workstation

Some Context . . .

This is the third post in a multi-part blog educating readers on how to build a personal “sandbox” environment for Hyperion Planning. Click here to see all nine posts in the series.

Installing VMware Workstation

Before we can install Hyperion Planning, we need to create our virtual server. The first step is to install our virtualization software. In this example, we will be using VMware Workstation 11. Installing VMware Workstation is a fairly straightforward process. Start by double-clicking the VMware Workstation executable.

0139

Select “Next”.

0137

Accept the license agreement.

0136

Select “Typical” installation.

0135

Select the installation location. Select “Next”.

0134

Determine whether VMware Workstation checks for updates. Select “Next”.

0133

Determine whether usage data gets sent to VMware. Select “Next”.

0132

Determine which shortcuts get created. Select “Next”.

0131

Select “Continue” to begin the installation.

0129

Installation is in progress . . .

0128

Enter the license key.

0127

Success!  Select “Finish”.

In the Next Post

With VMware Workstation installed, it’s time to create a virtual server. Click here for the next post in the series.