PCMCS Error: “Unknown Member Used in Query”


Performing an extract in managed query is resulting in the following error: “Unknown Member Used in Query”

Essbase does not support forward slash in Dimension or Member names.

Attributes contain unsupported characters in the name, such as the “/”.

Recreate the application and be sure there are no members with unsupported characters in the dimension or member names.

Using the Migration featur:
• Export the entire application using Migration
• Delete the application
• Download the Migration zip file – find the Dimension files inside of it and keep separately (outside of the zip) on local laptop
• Modify the attribute dimension files to correct the name (you do this in the header of each file)
• Create new Application
• Use Update dimensions to import all of the dimensions – you can do this is one action because the UI allows you to select multiple files.
• Deploy cube to verify the outline is correct
• Using Migration, import the Model. Assuming attribute dimensions are not used in any filters the Model will import with no warnings. But even if attribute dimensions may be used in filters, the model will still import – you will just need to correct the affected rules from the UI (by opening them, fixing the filter, and re-saving)
• Manually re-create the Queries

Thanks,

~KKT~

Advertisements

Smart View: Smart List is not Visible when Alias Changed to Default – PBCS


Administrator developed a Smart View ad-hoc template to load the data. When Alias is set as None users are able to view smart list associated with account and also able to submit data (for BegBalance and Jan to Dec) however when the user changed alias to default, smart lists associated with account not showing the list.

This issue is identified as Bug 26476354 and fixed in PBCS version 17.08 as per DEV, but fix will not work with out implementing below steps manually:

1. A child of a root member m1 has a duplicate alias. For example:
assumptions alias is assigned to m1 and also to some other member under the tree.

2. In that case Planning should generate correct qualified name, that is:
[root_name].[m1] in case root member does not have an alias.
Or
[root_alias].[assumptions] in case root member has an alias.
Other combinations [root_name].[assumptions] or [root_alias].[ m1]  are invalid.

When the user has a saved xls worksheets with incorrect qualified names, they need to  manually update it to fix these names, using Member Selection as  without manual updates Planning server will not be able to resolve these names and will handle incorrectly qualified names as comments.

Thanks,

~KKT~

SmartView Issue – COM Addin Not Getting Added To Excel By Default


The Smart View Addin does not show up automatically when opening Excel.  However, once Excel is started, it can be loaded by manually enabling the addin from Excel Options.

Under Add-ins in Excel  the current load behavior of the particular add-in can be seen. The load behavior of the add-in should be set to “Load at Startup”.
It was showing as Unloaded.

To enable the Smart View addin to display when opening Excel:

1.  Open your registry editor. To do this, open your Windows Start menu and type “regedit”.

2.  Go to HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\Hyperion.CommonAddin. Ensure the LoadBehavior is set to 3.

3.  If the LoadBehavior is anything other than “3” then follow these steps:

a.  Close Excel. Make sure the “excel.exe” process is not running in the Windows Task Manager.

b.  Open the registry editor again. Double-click the registry key to edit it.

c.  Change the Load Behavior to “3.” Close the registry editor.

d.  Re-open the registry editor to ensure “3” is still there.

4.  Open Excel. The Smart View add-in should now load automatically at startup.

IMP Note – A registry backup is required before making changes to the registry.

Thanks,

~KKT~

FDMEE Error – Essbase Error(1003007): Data Value [X] Encountered Before All Dimensions Selected, [1] Records Completed


Dimension details in the Target Application screen do not look like what is in Planning.
When setting up a new Data Load Rule, all of the available Plan Types are not showing.
When trying to run a Data Load Rule, the following error occurs: “Essbase Error(1003007): Data Value [X] Encountered Before All Dimensions Selected, [1] Records Completed.”
Mappings are not being applied to all dimensions even though mappings exist.

There are situations where the metadata that FDMEE has stored for a Planning application within PBCS is different from the actual Planning metadata. If changes are made via LCM or simply through the Planning application, it may not be the same as when the application was registered within FDMEE.

Use the following steps to resolve the issue:

  • Before trying anything, you should go into the Target Application screen, choose the Planning application and click the “Refresh Metadata” button and see if the issue is resolved.
  • If there is still an issue, back up all of the artifacts for this target application using LCM so that you have a backup.
  • Go into the Data Load Mappings screen, into each location and export mappings for all dimensions so that you have a backup(s) of your mappings.
  • Go to the Target Application screen, highlight the target application and click the red X to delete the target application.
  • Navigate to another screen within FDMEE, then back to the Target Application screen.  The problem target application should not exist anymore.  Register the Planning application
  • Restore your artifacts in LCM
  • Restore your mappings for each location in the Data Load Mappings screen

Thanks,

~KKT~

Mass Import Security Access into PBCS or EPBCS


How to mass import Security Access in PBCS or EPBCS, for Dimensions, Forms, Form Folders, Business Rules and Task Lists

Overview:
1. Generate your LCM zip file
2. Download the LCM zip file
3. Edit the XML files
4. Re-zip the LCM zip file
5. Upload and import the amended LCM zip

Details:
1. Generate your LCM zip file
Application > Migration
Select Planning link in Categories tab
Select Security only, and export

2. Download the LCM zip file
Application > Migration > Snapshots
Download the snapshot made in step 1

3. Edit the XML files
Users.xml contains all entries for user specific access
Under Groups folder, each <groupname>.xml is specific to that group
Start tag: <acls>
End tag: </acls>
Only use the above start and end tag ONCE in the file. If you start with a blank file, it will ONLY have these two tags in it
Review tables below to add your required access to the .xml file

User Group
Dimension <acl>
<name>user@oracle.com</name>
<objectName>MemberName</objectName>
<objectType>SL_DIMENSION</objectType>
<accessMode>READ</accessMode>
<flag>MEMBER</flag>
<isUser>Y</isUser>
</acl>
<acl>
<name>GroupName</name>
<objectName>MemberName</objectName>
<objectType>SL_DIMENSION</objectType>
<accessMode>READ</accessMode>
<flag>MEMBER</flag>
<isUser>N</isUser>
</acl>
Rule <acl>
<name>user@oracle.com</name>
<objectName>RuleName</objectName>
<objectType>SL_CALCRULE</objectType>
<accessMode>LAUNCH</accessMode>
<flag>MEMBER</flag>
<isUser>Y</isUser>
</acl>
<acl>
<name>GroupName</name>
<objectName>RuleName</objectName>
<objectType>SL_CALCRULE</objectType>
<accessMode>LAUNCH</accessMode>
<flag>MEMBER</flag>
<isUser>N</isUser>
</acl>
Form <acl>
<name>user@oracle.com</name>
<objectName>FormName</objectName>
<objectType>SL_FORM</objectType>
<accessMode>READ</accessMode>
<flag>MEMBER</flag>
<isUser>Y</isUser>
</acl>
<acl>
<name>GroupName</name>
<objectName>FormName</objectName>
<objectType>SL_FORM</objectType>
<accessMode>READ</accessMode>
<flag>MEMBER</flag>
<isUser>N</isUser>
</acl>
Form Folder <acl>
<name>user@oracle.com</name>
<objectName>FolderName</objectName>
<objectType>SL_FORMFOLDER</objectType>
<accessMode>READ</accessMode>
<flag>MEMBER</flag>
<isUser>Y</isUser>
</acl>
<acl>
<name>GroupName</name>
<objectName>FolderName</objectName>
<objectType>SL_FORMFOLDER</objectType>
<accessMode>READ</accessMode>
<flag>MEMBER</flag>
<isUser>N</isUser>
</acl>
Task List <acl>
<name>user@oracle.com</name>
<objectName>TaskName</objectName>
<objectType>SL_TASKLIST</objectType>
<accessMode>READ</accessMode>
<flag>MEMBER</flag>
<isUser>Y</isUser>
</acl>
<acl>
<name>GroupName</name>
<objectName>TaskName</objectName>
<objectType>SL_TASKLIST</objectType>
<accessMode>READ</accessMode>
<flag>MEMBER</flag>
<isUser>N</isUser>
</acl>

Table2

accessMode flag
Dimension – READ
– READWRITE
– NONE
– MEMBER
– @DESCENDANTS
– @IDESCENDANTS
– @CHILDREN
– @ICHILDREN
Rule – LAUNCH
– NONE
MEMBER
Form – READ
– READWRITE
– NONE
MEMBER
Form Folder – READ
– READWRITE
– NONE
MEMBER
Task List – READ*
– WRITE
– READWRITE
– NONE
MEMBER

*Note:
Read=Assign
Write=Manage
ReadWrite=Manage and Assign
None=None

4. Re-zip the LCM zip file
Use 7-Zip or any other zip build tool to re-zip the LCM file after edits are made.
Rename it from the original, to distinct as edited version

5. Upload and import the amended LCM zip
Application > Migration > Snapshots
Select Upload and browse to your new .zip file
Select Gear Icon > Import to import the new .zip file

Thanks,

~KKT~

FDMEE – Unable To See Newly Created PBCS Plan Type When Creating Data Load Rule


After creating new Plan Type in PBCS, it cannot be used in Data Management as a Target a Plan Type. When creating Data Load Rule in Data Management (FDMEE), the Target Plan Type drop down will not show the newly created Plan Type.

Follow the below steps to see the newly created Plan Type under Target Plan Type in Data Management.

– Add a new Cube at Administration > Manage > Cubes
– Refresh Database as usual
– Check that the new cube appears at the Planning Application windows
– Refresh Metadata and Members at Data Management Target Application window
– Click on Save
– Check that the new cube is not shown in the Plans list at Data Load Rule creation

Thanks,

~KKT~

Hyperion Planning: Deploy Option is Disabled for EPMA Application


Deploy option is disabled for EPMA applications

In general, majority of EPMA deploy option grey out issues occurs only when previous deployment failed or diagnostics tests are failed.Issue should be resolved after doing below steps:

1. In EPMA Application Library, right-click on the application and select ‘Run Diagnostics’ – tick all the tests.

2. If that doesn’t work, we can adjust the AWBConfig.properties and reduce the deploy timeout. The default is 8 hours (480), you can change it to 10 minutes.

It’s here:
%MIDDLEWARE_HOME%\EPMSystem11R1\products\Foundation\BPMA\config\Webtier

Restart of EPMA Web is required.

Thanks,

~KKT~

 

FDMEE – Export the ODI Operator Log from the ODI Console


Hi,

I am frequently getting this query for 11.1.2.3. Below solution

This is a simple procedure that can be carried out by anyone who has access to the ODI console and knows the username and password for the ODI Console.

  1. Logon to the ODI Console by going to http://server:19000/odiconsole in a web browser (where server is the server where FDMEE is installed).
  2. Expand ‘Runtime’, then expand ‘Sessions/Load Plan Executions’ and you should be able to see the ODI sessions that have been run.
  3. Click on ‘Sessions/Load Plan Executions’ & then click the ‘Export’ icon (a small white square with a blue arrow pointing upwards on the ‘Browse’ tab).

Show the Operator logs

4. In the ‘Export Log’ screen, select a date and time for the ‘From’ and ‘To’ values that match when the FDMEE process started and finished (so that only a relevant subset of the logs are exported).

Set date filters for the export

5. Click the export button and you will get logs.

Thanks,

~KKT~

FDMEE Automated Batch Loads Time Out


In Financial Data Quality Management Enterprise Edition (FDMEE) automated batch loads time out.

There are two most likely causes of this issue:

  • Batch timeout setting not defined or insufficient in system and/or application settings in the FDMEE application
  • Stuck thread timeout setting needs increasing in the ErpIntegrator0 managed server (weblogic)

To increase the batch timeout setting in the FDMEE application:

  1. Navigate to Data Management from EPM workspace.
  2. From the Setup tab, and then under Configure, select System Settings.
  3. In System Settings, from Profile Type, select Other.
  4. Define a timeout value for the “Batch timeout in minutes” setting.

To increase the Stuck Thread timeout settting in the ErpIntegrator0 managed server in weblogic:

  1. Navigate to weblogic admin console.
  2. Under domain structure, expand Environment and choose servers.
  3. Choose ErpIntegrator0 from the servers list.
  4. Under configuration, choose tuning.
  5. Choose Lock & Edit to modify the setting value.
  6. Increase the Stuck Thread Max time value. The value is in seconds. By default it will be 600 seconds (5 minutes).
  7. Choose activate configuration so that the settings take effect.
  8. Restart FDMEE service.

Hope this helps.

Thanks,

~KKT~

FDQM/EE Loading Metadata to on-premise and cloud EPM Applications – Part-2


Import Format

The import format will map the columns from the source file to the dimensions in the custom application, or set defaults for properties that are the same for every member. In other cases, some of the members are set to the text “BLANK” so that mapping will succeed, but then values for the BLANK members may or may not be set by the by the event script. The use of the text for BLANK is specifically required when using the output dat file to generate the metadata file. If pulling directly from TDATASEG the user may not need to use this method.

Please also note that the AMOUNT field should be set to any valid numeric amount so that the input file will be successfully processed. In addition, a SKIP row may be required if the input file includes a header record.
For this example, the Import Format is defined as follows:

Event Scripts
In this example, an AftExportToDat and BefLoad script were used to process the file and then push the metadata to HFM. It’s possible to perform everything needed in a single script if desired by the user, and alternate implementations are possible. The script details are as follows:

AftExportToDat – Generate the metadata file in the format required by HFM

import shutil
#Open the .dat file generated by FDMEE
datFileName = str(fdmContext[“OUTBOXDIR”].replace(“\\”,”/”)) + “/” + str(fdmContext[“TARGETAPPNAME”]) + “_” + str(fdmContext[“LOADID”]) + “.dat”
datFile = open(datFileName,”r”)
#Open the .app file to be created for HFM
appFileName = str(fdmContext[“OUTBOXDIR”].replace(“\\”,”/”)) + “/” + str(fdmContext[“TARGETAPPNAME”]) + “_” + str(fdmContext[“LOADID”]) + “.app”
appFile = open(appFileName,”w”)
#Create .app file Header lines
appFile.write(“!FILE_FORMAT=11.12\n!VERSION=11.1.5250\n!CUSTOM_ORDER=Movement;CostCenter;Location;Nature\n\n”)
appFile.write(“!MEMBERS=Entity\n’Label;DefCurrency;AllowAdjs;IsICP;AllowAdjFromChildren;SecurityClass;UserDefined1;UserDefined2;UserDefined3;HoldingCompany;SecurityAsPartner;Default Parent;Description(s)\n”)
#Include a counter to skip record in the .dat file generated by FDMEE
i = 0
#Parse the records in .dat file & replace BLANKs with empty string. (FDMEE puts BLANK for empty columns by default & might be rejected by target EPM application.)
for memberLine in datFile:
i = i + 1
if i > 1:
memberLine = memberLine.replace(“BLANK”,””)
memberLine = memberLine.split(“,”)
#Derive the Default Parent
if memberLine[15] != “”:
DefParent = memberLine[14]
elif memberLine[14] != “”:
DefParent = memberLine[13]
else:
DefParent = ‘#root’
#Write the .app file record for members using the below format.
appFile.write(“%s;%s;%s;%s;%s;%s;%s;%s;%s;%s;%s;DefaultParent=%s;English=%s\n” % (memberLine[0],memberLine[1],memberLine[2],memberLine[3],memberLine[4],memberLine[5],memberLine[6],memberLine[7],memberLine[8],memberLine[9],memberLine[10],DefParent,memberLine[12]))
#Create another header record in the .app file for the hierarchy section.
appFile.write(“\n!HIERARCHIES=Entity\n”)
#Close the .dat file
datFile.close()
#Open the .dat file again for parsing the records for hierarchy section.
datFile = open(datFileName,”r”)
#Write a logic such that hierarchy sections are not repeated in the .app file.
j = 0
seen = set()
for hierarchyLine in datFile:
j = j + 1
if j > 1:
hierarchyLine = hierarchyLine.replace(“BLANK”,””)
hierarchyLine = hierarchyLine.split(“,”)
gen1 = “;%s\n” % (hierarchyLine[13])
gen2 = “%s;%s\n” % (hierarchyLine[13],hierarchyLine[14])
gen3 = “%s;%s\n” % (hierarchyLine[14],hierarchyLine[15])
if gen1 not in seen and hierarchyLine[13] != “”:
seen.add(gen1)
appFile.write(gen1)
if gen2 not in seen and hierarchyLine[14] != “”:
seen.add(gen2)
appFile.write(gen2)
if gen3 not in seen and hierarchyLine[15] != “”:
seen.add(gen3)
appFile.write(gen3)
#close the .app file & .dat file.
appFile.close()
datFile.close()

BefLoad – Load the file to HFM

#Import required HFM & Java libraries
from java.util import Locale
from java.io import File
import shutil
from oracle.epm.fm.hssservice import HSSUtilManager
from oracle.epm.fm.domainobject.application import SessionOM
from oracle.epm.fm.domainobject.loadextract import LoadExtractOM
from oracle.epm.fm.common.datatype import transport
#HSS Username / Password
UserName = “admin”
Password = “Password”
#Target HFM connection properties
clusterName = “HFMCluster”
application = “DEVHFM”
#Authenticate user & create HFM Session
ssoToken = HSSUtilManager.getSecurityManager().authenticateUser(UserName,Password)
hfmsession = SessionOM().createSession(ssoToken, Locale.ENGLISH,clusterName,application)
#Load members to HFM
loadOM = LoadExtractOM(hfmsession)
appFileName = str(fdmContext[“OUTBOXDIR”].replace(“\\”,”/”)) + “/” + str(fdmContext[“TARGETAPPNAME”]) + “_” + str(fdmContext[“LOADID”]) + “.app”
metadata_file = File(appFileName)
memberLoadOptions = transport.MetadataLoadOptions()
memberLoadOptions.setUseReplaceMode(False)
memberLoadOptions.setEntities(True)
memberLoadOptions.setDelimiter(“;”)
setCustomDims = [False,False,False,False]
memberLoadOptions.setCustomDims(setCustomDims)
loadInfo = loadOM.loadMetadata(metadata_file, memberLoadOptions)
logFile = loadInfo.getLogFile()
logFileName = str(fdmContext[“OUTBOXDIR”].replace(“\\”,”/”)) + “/” + str(fdmContext[“TARGETAPPNAME”]) + “_” + str(fdmContext[“LOADID”]) + “.log”
shutil.copy(logFile.getPath().replace(“\\”,”/”),logFileName)
SessionOM().closeSession(hfmsession)
fdmAPI.showCustomMessage(“Metadata Loaded Successfully”)

Continue – Part-3