Problem

I am currently working with a client that is updating a planning application and one of the changes is to remove a dimension.  After the new application was setup and the hierarchies were modified to meet the objectives, migrating artifacts was the next step.  As many of you know, if you try to migrate web forms and composite forms, they will error during the migration due to the additional dimension in the LCM file.  It wouldn’t be a huge deal to edit a few XML files, but when there are hundreds of them, it is extremely time consuming (and boring, which is what drove me to create this solution).

Assumptions

To fully understand this article, a basic understanding of XML is recommended.  The example below assumes an LCM extract was run on a Planning application and it will be used to migrate the forms to the same application without a CustomerSegment dimension.  It is also assumed that the LCM extract has been downloaded and decompressed.

Solution

I have been learning and implementing PowerShell scripts for the last 6 months and am overwhelmed by how easy it is to complete complex tasks.  So, PowerShell was my choice to modify these XML files in bulk.

It would be great to write some long article on how smart this solution is and overwhelm you with my whit, but there is not much too it.  A few lines of PowerShell will loop through all the files and remove the XML tags related to a predefined dimension.  So, let’s get to it.

Step 1 – Understand The XML

There are two folders of files we will look to.  Forms are under the plan type and the composite forms are under the global artifacts.  Both of these are located inside the resource folder.  If there are composite forms that hold the dimension in question as a shared dimension, both will need to be impacted.  Scripts will be included to update both of these areas.

Inside each of the web form files will be a tag for each dimension, and it will vary in location based on whether the dimension is in the POV, page, column, or row.  In this particular example, the CustomerSegment dimension is in the POV section.  What we want to accomplish is removing the <dimension/> tag where the name attribute is equal to CustomerSegment.

For the composite forms, the XML tag is slightly different, although the concept is the same.  The tag in composite form XML files is <sharedDimension/> and the attribute is dimension, rather than name.

Step 2 – Breaking Down the PowerShell

The first piece of the script is just setting some environment variables so the script can be changed quickly so that it can be used wherever and whenever it is needed.  The first variable is the path of the Data Forms folder to be executed on.  The second is the dimension to be removed.

# Identify the source of the Data Forms folder and the dimension to be removed
# List all files, recursively, that exist in the path above
$files = Get-ChildItem $lcmSourceDir -Recurse | 
where {$_.Attributes -notmatch 'Directory'} |

The next piece of the script is recursing through the folder and storing the files in an array.  There is a where statement to exclude directories so the code only executes on files.

# List all files, recursively, that exist in the path above
$files = Get-ChildItem $lcmSourceDir -Recurse | 
where {$_.Attributes -notmatch 'Directory'} |
Step 3 – Removing The Unwanted Dimension

The last section of the script does most of the work.  This will loop through each file in the $files array and

  1. Opens the file
  2. Loops through all tags and deletes any <dimension/> tag with a name attribute with a value equal to the $dimName variable
  3. Saves the file
# Loop through the files and find an XML tag equal to the dimension to be removed
Foreach-Object {

$xml = Get-Content $_.FullName
$node = $xml.SelectNodes(“//dimension”) |
Where-Object {$_.name -eq $dimName} | ForEach-Object {
# Remove each node from its parent
[void][/void]$_.ParentNode.RemoveChild($_)
}
$xml.save($_.FullName)
Write-Host “($_.FullName) updated.”
}

Executing The Logic On Composite Forms

The above concepts are exactly the same to apply the same logic on composite forms files in the LCM.  If this is compared to the script applied to the web forms files, there are three differences.

  1. The node, or XML tag, that needs to be removed is called sharedDimension, not dimension. (highlighted in red)
  2. The attribute is not name in this instance, but is called dimension.  (highlighted in red)
  3. We have added a counter to identify whether the file has the dimension to be removed and only saves the file if it was altered.  (highlighted in green)
The Script
$lcmSourceDir = "Z:\Downloads\KG04\HP-SanPlan\resource\Global Artifacts\Composite Forms"
$dimName = "CustomerSegment"
# List all files
$files = Get-ChildItem $lcmSourceDir -Recurse | where {$_.Attributes -notmatch 'Directory'} |
# Remove CustomerSegment
Foreach-Object {
  # Reset a counter to 0 - used later when files is saved
  $fileCount = 0

$xml = Get-Content $_.FullName
$node = $xml.SelectNodes(“//sharedDimension“) | Where-Object {$_.dimension -eq $dimName}  | ForEach-Object {
#Increase the counter for each file that matches the criteria
    $fileCount++
# Remove each node from its parent
[void][/void]$_.ParentNode.RemoveChild($_)
}
# If the dimension was found in the file, save the updated contents.
  if($fileCount -ge 1) {
$xml.save($_.FullName)
Write-Host “$_.FullName updated.”
    }
}

Summary

The first script may need to be run on multiple plan types, but the results is an identical folder structure with altered files that have the identified dimension removed.  This can be zipped and uploaded to Shared Services and used to migrate the forms to the application that has the dimension removed.

The scripts above can be copied and pasted into PowerShell, or the code can be Downloaded.

 

If you are using PBCS, you may run into some challenges with large files being passed through FDMEE.  Whether performance is an issue or you just want to parse a file my month/year, this script might save you some time.

The Challenge

I recently had the need to break apart a file.  The source provided one large text file that included 2 years of data that was needed to populate the history of an employee metrics application.  The current process loaded files by month and we wanted to be able to piggy back off the existing scripts to load and process data in FDMEE and the monthly Planning data pushes to the ASO reporting cube.  Read more

 

I have been using PBCS for over a year now.  As much as it surprises me, I am a fan.  From my experience, I have seen good performance, above average stability, above average accessibility, and I enjoy the cutting edge functionality that is lacking with on-premise implementations.  I created some info-graphics for presentations and thought I would share with the community.  As always, if you would like to contract me for design work, I am always excited to take on interesting and challenging work.  If you would like to donate, click the link to the right.

Please remember that I put a lot of work into these works, so please do not alter or use without consent.

 

 

 

 

Working with Jake Turrell always benefits me in many ways.  Jake found a fantastic way to minimize the effort it takes to create test accounts for testing and training Planning users.  You no longer have to create new multiple accounts.

“During the testing phase of most Planning implementations, developers need to create test user accounts.  I typically create at least one test user for each security group so I can verify that the correct access has been assigned.  With an on-premises Hyperion Planning implementation, this is easy – simply create user ID’s in the Shared Services native directory.  With PBCS, creating bulk test ID’s can be difficult, as each user ID requires a unique e-mail address.  If you need 50 test users, should you create 50 fake/temporary e-mail accounts?  Luckily the answer is no.”

Check out how here.

About Jake

Jake Turrell is a Hyperion Architect and Oracle Ace Associate with over 20 years of experience implementing Enterprise Performance Management solutions. Jake’s technology career began in the early 90’s as a Financial Systems intern at Dell in Austin, Texas, administering IMRS Micro Control (the DOS-based predecessor to Hyperion Enterprise). After working at Dell, Jake joined Ernst & Young’s Management Consulting practice where he worked with a variety of technologies. He later returned to the Hyperion world and joined a boutique Hyperion consulting firm in Dallas, Texas.
Jake has spent the last 17 years implementing Hyperion Planning and Essbase solutions for a variety of clients across multiple industries. Certified in both Hyperion Planning and Essbase, Jake holds a BBA from the University of Texas at Austin.

 

Jake TurrellKscope wouldn’t be the same without checking out Jake – one of the best.  The amount of time and effort he puts into his presentations is crazy and it shows.  If you want to learn something new, below is schedule.  If you get a picture of him in his formal wear (you will know what I mean if you see it), please post a picture in a comment and tweet me…PLEASE!

Presentations

Introduction to the Hyperion Planning REST API
Monday, Jun 27, 2016, Session 6, 4:30 pm – 5:30 pm

Introduction to Essbase Hybrid Aggregation Mode
Tuesday, Jun 28, 2016, Session 12, 4:45 pm – 5:45 pm

Hands-on-Lab

Introduction to Essbase Hybrid Aggregation Mode
Wednesday, June 29, 4:30 – 5:30 PM

I can’t go this year because, oddly, Jake can’t work on our project.  So, you all enjoy – hope to see you all next year.

 

Follow Jake

 

For those of you that know me, I have a little graphic designer in me.  I frequently develop things that may be useful to others in the industry and like to share.  Below are 2 images that you may use with the understanding that the logo has to remain and can’t be re-branded.  If you would like to have this customized and your logo added, contact me and we can discuss the options.

PBCS Architecture

I was asked to summarize the PBCS Architecture.  Since this isn’t proprietary, here you go.

PBCS Architecture

 

Pizza As A Service

I won’t take credit for this concept.  You can find this all over the internet.  I was a little bored and wanted to present the concept to a client so I decided to recreate it a little more professionally.  This compares software as a service (SASS) to pizza.  Hope you can benefit from it and maybe get a few laughs.

Pizza As A Service

 

PBCS is about to release a major upgrade (1 of 2 every year scheduled). Oracle released a 29 page document laying out everything that should be expected. Want the abbreviated version?

 

  • Want your users to see the simplified user interface? You will be able to make it the default.
  • Welcome to EPBCS. This enhanced version will include modules for Financials, Workforce, Projects, and Capital.
  • Users can now create dashboards that include editable forms and ad hoc grids, and include new chart types.
  • Forms, task lists, and reports can be viewed in either list view or hierarchical view.
  • You can now use an attribute dimension as a dimension, as a filter in forms and reports, and within ad hoc grids. Using attribute dimensions enables administrators and end users to perform tasks such as:
    1. Filtering data using attribute members, such as by products with a certain color
    2. Performing cross-dimensional rollups across attribute members
    3. Reporting and analysis with attribute dimension members using Smart View, or financial reports
    4. Using attribute dimensions in dynamic user variables 
Attribute dimensions are optional and are listed separately on the Layout tab of the Form Designer. Drag the Attribute dimension to a Point of View or to a row or column to add it to the form grid.
  • Administrators can create aliases for artifacts similar to alias tables where things like forms can be viewed in native languages.
  • For new applications, administrators can optionally choose a simplified multicurrency option during application creation. Using simplified currency avoids the use of the Hsp_Rates dimension and adds a Currency dimension with exchange rates stored in the Account dimension
  • You can now create smart lists based on dimension hierarchies. This dynamically updates smart list values based on member updates.
  • Form grid display can be tied to the start and end period for the respective scenarios on display
  • A new action menu in the console allows customers to clear specific areas within both input and reporting cubes
  • Users can now drill on shared members to get to the children of the base member.
  • Form designers can now prevent the form save confirmation message from being displayed to users by specifying an option in form design.
  • The usability and readability of forms is increased with duplicate aliases. Aliases can now contain the same name within an alias table and across alias tables.
  • Import Metadata functionality is extended to Microsoft Word.
  • You can now quickly add attribute dimensions to an ad hoc grid at any time during the ad hoc session.
  • In the Planning Admin Extension, you can now work with attribute dimensions and the Time Period dimension. Just as with regular dimensions, you can use the Planning Admin Extension in the Smart View application to quickly import and edit attribute and time dimension application metadata.
  • System Templates are now displayed under New Objects.
  • You can now add a warning or an error to a step using validation conditions. Errors prevent the next step. Warnings allow the next step after you click OK on the warning message. You can use a design- time prompt or function on the validation condition. This allows you to use functions on design-time prompts without having to create non-promptable design-time prompts.
  • When you are debugging business rules, a Condition Builder is now available to help you build conditions.
  • You can use the Member Selector dialog box to create MDX syntax and validation before running a partial clear.
  • The following new design-time prompt types are available:
    1. Percent
    2. Integer
    3. StringAsNumber
    4. DateAsNumber
    5. Smart List
    6. UDA

New Design-Time Prompt Functions

  • @AVAILDIMCOUNT – Returns the number of available dimensions.
  • @DEPENDENCY – “Inclusive” returns member(s) from Input 1 for which Input 2 has member(s) specified from the same dimensions. “Exclusive” returns members from Input 1 for which Input 2 has no specified members in the same dimensions.
  • @DIMATTRIBUTE – Returns the attribute name if the specified attribute is associated with a dimension.
  • @DIMNAME – Returns the name of a dimension if it is valid for the database.
  • @DIMUDA – Returns the UDA name if the specified UDA is valid for the dimension.
  • @EVALUATE – Returns the result of an expression.
  • @FINDFIRST – Finds the first substring of a string that matches the given regular expression.
  • @FINDLAST – Finds the last substring of a string that matches the given regular expression.
  • @GETDATA – Returns the value of the slice.
  • @INTEGER – Returns an integer.
  • @ISDATAMISSING – Returns true if the value of the slice is missing.
  • @ISANDBOXED – Determines if the current application is sandboxed.
  • @ISVARIABLE – Determines if the argument is a variable.
  • @MATCHES – Returns “true” if the first substring of a string matches the given regular expression.
  • @MEMBERGENERATION – Returns the generation number of a member.
  • @MEMBERLEVEL – Returns the level number of a member.
  • @MSGFORMAT – Takes a set of objects, formats them, and then inserts the formatted strings into the pattern at the appropriate places.
  • @OPENDIMCOUNT – Returns the number of dimensions for which a member was not specified.
  • @VALUEDIMCOUNT – Returns the number of dimensions for which a member was specified.
  • @TOMDX – Returns an MDX expression.

New Design Time Prompt Types

  1. Percent
  2. Integer
  3. StringAsNumber
  4. DateAsNumber
  5. Smart List
  6. UDA

New Custom Defined Functions

  • @CalcMgrBitAnd – Performs a bitwise AND operation, which compares each bit of the first operand to the corresponding bit of the second operand. If both bits are 1, the corresponding result bit is set to 1; otherwise, the corresponding result bit is set to 0.
  • @CalcMgrBitOR – Performs a bitwise OR operation, which compares each bit of the first operand to the corresponding bit of the second operand. If either bit is 1, the corresponding result bit is set to 1; otherwise, the corresponding result bit is set to 0.
  • @CalcMgrBitExOR – Performs an exclusive bitwise OR operation, which compares each bit of the first operand to the corresponding bit of the second operand. If either bit is 1, the corresponding result bit is set to 1; otherwise, the corresponding result bit is set to 0.
  • @CalcMgrBitExBoolOR – Performs an exclusive boolean bitwise OR operation.
  • @CalcMgrBitCompliment – Performs a unary bitwise complement, which reverses each bit.
  • @CalcMgrBitShiftLeft – Performs a signed left shift.
  • @CalcMgrBitShiftRight – Performs a signed right shift.
  • @CalcMgrBitUnsignedShiftRight – Performs an unsigned right shift.
  • @CalcMgrCounterClearAll – Removes all keys and values from the counter
  • @CalcMgrCounterClearKey – Removes the value from the counter associated with the key
  • @CalcMgrCounterDecrement – Decrements the value in the counter based on the key. If the key is not found, a value of zero is set for the key
  • @CalcMgrCounterDecrementKey – Decrements the value in the counter based on the key. If the key is not found, a value of zero is set for the key
  • @CalcMgrCounterGetKeyNumber – Returns the text found in the counter based on the key. If the key is not found, missing value is returned.
  • @CalcMgrCounterGetKeyText – Returns the text found in the counter based on the key. If the key is not found, missing value is returned.
  • @CalcMgrCounterGetNumber – Returns the number from the counter specified by the key. If the key is not found or the value is not a number, missing value is returned.
  • @CalcMgrCounterGetText – Returns the text found in the counter based on the key. If the key is not found, missing value is returned.
  • @CalcMgrCounterIncrement – Increment the value in the counter specified by the key
  • @CalcMgrCounterIncrementKey – Increments the value in the counter based on the key. If the key is not found, a value of zero is set for the key.
  • @CalcMgrExcelToDate – Converts an Excel date to YYYYMMDD format.
  • @CalcMgrExcelToDateTime – Converts an Excel date to YYYYMMDDHHMMSS format.
  • @CalcMgrGetStringFormattedDateTime – Converts the date defined by format to date in the YYYYMMddHHmmss format.
  • @CalcMgrDateToExcel – Converts a date in YYYYMMDD format to an Excel date
  • @CalcMgrDateTimeToExcel – Converts a date in YYYYMMDDHHMMSS format to an Excel date
  • @CalcMgrRollDay – Roll the day up or down to the date which is in the YYYYMMDD format
  • @CalcMgrRollDate – Adds or subtracts (up or down) a single unit of time on the given date field without changing larger fields.
  • Possible values of date_part are: day, month, week and year.
  • @CalcMgrRollMonth – Roll the month up or down to the date which is in the YYYYMMDD format.
  • @CalcMgrRollYear – Roll the year up or down to the date which is in the YYYYMMDD format.
  • @CalcMgrExcelACCRINT – Returns the accrued interest for a security that pays periodic interest
  • @CalcMgrExcelACCRINTM – Returns the accrued interest for a security that pays interest at maturity
  • @CalcMgrExcelAMORDEGRC – Returns the depreciation for each accounting period by using a depreciation coefficient
  • @CalcMgrExcelAMORLINC – Returns the depreciation for each accounting period
  • @CalcMgrExcelCOUPDAYBS – Returns the number of days from the beginning of the coupon period to the settlement date
@CalcMgrExcelCOUPDAYS – Returns the number of days in the coupon period that contains the settlement date
  • @CalcMgrExcelCOUPDAYSNC – Returns the number of days from the settlement date to the next coupon date
  • @CalcMgrExcelCOUPNCD – Returns a number that represents the next coupon date after the settlement date
  • @CalcMgrExcelCOUPNUM – Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon
  • @CalcMgrExcelCOUPPCD – Returns a number that represents the previous coupon date before the settlement date
  • @CalcMgrExcelCUMIPMT – Returns the cumulative interest paid on a loan between start_period and end_period
@CalcMgrExcelCUMPRINC – Returns the cumulative principal paid on a loan between the start period and the end period
  • @CalcMgrExcelDB – Returns the depreciation of an asset for a specified period using the fixed-declining balance method
  • @CalcMgrExcelDDB – Returns the depreciation of an asset for a specified period using the double- declining balance method or some other method you specify
  • @CalcMgrExcelDISC – Returns the discount rate for a security
  • @CalcMgrExcelDOLLARDE – Converts a dollar price expressed as an integer part and a fraction part, such as 1.02, into a dollar price expressed as a decimal number. Fractional dollar numbers are sometimes used for security prices.
  • @CalcMgrExcelDOLLARFR – Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
@CalcMgrExcelDURATION – Returns the annual duration of a security with periodic interest payments
  • @CalcMgrExcelEFFECT – Returns the effective annual interest rate
  • @CalcMgrExcelFV – Returns the future value of an investment
  • @CalcMgrExcelFVSCHEDULE – Returns the future value of an initial principal after applying a series of compound interest rates
  • @CalcMgrExcelINTRATE – Returns the interest rate for a fully invested security @CalcMgrExcelIPMT – Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate
  • @CalcMgrExcelIRR – Returns the internal rate of return for a series of cash flows
  • @CalcMgrExcelISPMT – Calculates the interest paid during a specific period of an investment
  • @CalcMgrExcelMDURATION – Returns the Macauley modified duration for a security with an assumed par value of $100
  • @CalcMgrExcelMIRR – Returns the internal rate of return where positive and negative cash flows are financed at different rates
  • @CalcMgrExcelNOMINAL – Returns the annual nominal interest rate
  • @CalcMgrExcelNPER – Returns the number of periods for an investment
  • @CalcMgrExcelNPV – Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
  • @CalcMgrExcelPMT – Returns the periodic payment for an annuity
  • @CalcMgrExcelPPMT – Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate
  • @CalcMgrExcelPRICE – Returns the price per $100 face value of a security that pays periodic interest
  • @CalcMgrExcelPRICEDISC – Returns the price per $100 face value of a discounted security
  • @CalcMgrExcelPRICEMAT – Returns the price per $100 face value of a security that pays interest at maturity
  • @CalcMgrExcelPV – Returns the present value of an investment
@CalcMgrExcelRATE – Returns the interest rate per period of an annuity
  • @CalcMgrExcelRECEIVED – Returns the amount received at maturity for a fully invested security
  • @CalcMgrExcelSLN – Returns the straight-line depreciation of an asset for one period
  • @CalcMgrExcelSYD – Returns the sum-of-years’ digits depreciation of an asset for a specified period
  • @CalcMgrExcelTBILLEQ – Returns the bond-equivalent yield for a Treasury bill
  • @CalcMgrExcelTBILLPRICE – Returns the price per $100 face value for a Treasury bill
  • @CalcMgrExcelTBILLYIELD – Returns the yield for a Treasury bill
  • @CalcMgrExcelXIRR – Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic
  • @CalcMgrExcelXNPV – Returns the net present value for a schedule of cash flows that is not necessarily periodic
  • @CalcMgrExcelYIELD – Returns the yield on a security that pays periodic interest
  • @CalcMgrExcelYIELDDISC – Returns the annual yield for a discounted security; for example, a Treasury bill
  • @CalcMgrExcelYIELDMAT – Returns the annual yield of a security that pays interest at maturity
  • @CalcMgrExcelCEILING – Rounds a number up (away from zero) to the nearest integer or to the nearest multiple of significance
  • @CalcMgrExcelCOMBIN – Returns the number of combinations for a given number of objects
  • @CalcMgrExcelEVEN – Rounds a number up to the nearest even integer
  • @CalcMgrExcelFACT – Returns the factorial of a number
  • @CalcMgrExcelFACTDOUBLE – Returns the double factorial of a number
  • @CalcMgrExcelFLOOR – Rounds a number down, toward zero
  • @CalcMgrExcelGCD – Returns the greatest common divisor
  • @CalcMgrExcelLCM – Returns the least common multiple
  • @CalcMgrExcelMROUND – Rounds a number to a specified number of digits
  • @CalcMgrExcelMULTINOMIAL – Returns the multi-nominal of a set of numbers
  • @CalcMgrExcelODD – Rounds a number up to the nearest odd integer
  • @CalcMgrExcelPOWER – Returns the result of a number raised to a power
  • @CalcMgrExcelPRODUCT – Multiplies its arguments
  • @CalcMgrExcelROUNDDOWN – Rounds a number down, towards zero
  • @CalcMgrExcelROUNDUP – Rounds a number up, away from zero
  • @CalcMgrExcelSQRT – Returns a positive square root
  • @CalcMgrExcelSQRTPI – Returns the square root of (number * pi)
  • @CalcMgrExcelSUMSQ – Returns the sum of the squares of the arguments
  • @CalcMgrExcelSUMPRODUCT – Returns the sum of the products of corresponding array components
  • @CalcMgrExcelAVEDEV – Returns the average of the absolute deviations of data points from their mean
  • @CalcMgrExcelDEVSQ – Returns the sum of squares of deviations
  • @CalcMgrExcelLARGE – Returns the nth highest number
  • @CalcMgrExcelMEDIAN – Returns the median of the given numbers
  • @CalcMgrExcelSMALL – Returns the nth smallest number
  • @CalcMgrExcelSTDEV- Estimates standard deviation based on a sample
  • @CalcMgrExcelVAR – Estimates variance based on a sample
  • @CalcMgrExcelVARP – Estimates variance based on the entire population
  • @CalcMgrFindFirst – Find the first substring of this string that matches the given regular expression.
  • @CalcMgrFindLast – Find the last substring of this string that matches the given regular expression.
  • @CalcMgrMatches – Returns true, if the first substring of this string that matches the given regular expression. For regular expression, see ”java.util.regex.Pattern” in the Java docs.
  • @CalcMgrMessageFormat – Creates a string with the given pattern and uses it to format the given arguments.
  • @CalcMgrStartsWith – Tests if this string starts with the specified prefix.

Security

  • While the overall access rights granted to a user are controlled by the assigned identity domain role, Service Administrators can use the Access Control feature from the Console to assign additional application-level access by provisioning users and Native Directory groups with application-specific roles. For instance, a Planner in the service can now be assigned the Approvals Administrator role to enable the user to perform approvals-related activities.
 

As you all notice PBCS does not yet offer the possibility to create attribute dimensions. One of the solutions to get around this is to choose to map a smart list to a dimension in the map reporting module. Follow the steps below to configure your mapping.
Read more