Relational Database Tables in HFM/FDM and queries which help in information required

HFM/FDM stores login or date information in gregorian calendar format.  To Convert gregorian calender to normal calendar format we use to_date function in oracle.

FDM Query to check user activity at given time

--------------------------------------------------------------

select username,to_char(starttime,'DD/MM/YYYY HH24:MI:SS') starttime,to_char(endtime,'DD/MM/YYYY HH24:MI:SS') endtime,eventinfo,errorinfo,iosource,status,partitionkey,periodkey,categorykey from tlogactivity

where starttime like '%10-JAN-15%'

To get user level of access in FDM

------------------------------------------

select a.userid,b.appsecleveldesc from tsecuser a,tsecuserlevel b

where a.appseclevel=b.appseclevel

Activity carried out & consolidation timings in HFM for a specific time

----------------------------------------------------------------------------------------

select activityuserid,servername,strdescription,to_char(to_date('01/1900','MM/YYYY')+Starttime-2,'DD/MM/YYYY HH24:MI:SS') "start",to_char(to_date('01/1900','MM/YYYY')+endtime-2,'DD/MM/YYYY HH24:MI:SS') "end" ,to_char(to_date('01/1900','MM/YYYY')+(endtime-starttime),'HH24:MI:SS') "Duration"

from AppName_task_audit

where strdescription is not null and to_char(to_date('01/1900','MM/YYYY')+Starttime-2,'DD/MM/YYYY HH24:MI:SS') > '10/01/2015'

order by 4 asc

Activity carried out by users & consolidation timings in HFM during or after a specific time

----------------------------------------------------------------------------------------------------------------------

select h.susername,g.servername,g.strdescription,to_char(to_date('01/1900','MM/YYYY')+g.Starttime-2,'DD/MM/YYYY HH24:MI:SS') "start",to_char(to_date('01/1900','MM/YYYY')+g.endtime-2,'DD/MM/YYYY HH24:MI:SS') "end" ,to_char(to_date('01/1900','MM/YYYY')+(g.endtime-g.starttime),'HH24:MI:SS') "Duration"

from AppName_task_audit g,hsv_activity_users h

where strdescription is not null and to_char(to_date('01/1900','MM/YYYY')+Starttime-2,'DD/MM/YYYY HH24:MI:SS') > '10/01/2015' and h.luserid=g.activityuserid

order by 4 asc

select g.servername,g.appname,to_char((to_date('01/1900','MM/YYYY')+g.starttime-2),'DD/MM/YYYY hh24:mi:ss'),to_char((to_date('01/1900','MM/YYYY')+g.endtime-2),'DD/MM/YYYY hh24:mi:ss'),g.strdescription,s.susername

from AppName_task_audit g,hsv_activity_users s

where g.activityuserid in s.luserid and s.susername not like '%admin%'

Consolidation Timings taken to complete in HFM

----------------------------------------------------------------

select g.servername,g.appname,to_char((to_date('01/1900','MM/YYYY')+g.starttime-2),'DD/MM/YYYY hh24:mi:ss') starttime,to_char((to_date('01/1900','MM/YYYY')+g.endtime-2),'DD/MM/YYYY hh24:mi:ss') endtime,g.strdescription,s.susername,(endtime-starttime)*24*60 TimeTaken

from AppName_task_audit g,hsv_activity_users s

where g.activityuserid in s.luserid and g.strdescription is not null

order by 3 desc

HFM Error Log Analysis converting the gregorian date to normal date format

---------------------------------------------------------------------------

select * from hfm_errorlog

where TO_CHAR(TO_DATE('01/1900','MM/YYYY')+DTIMESTAMP,'DD/MM/YYYY')='10/01/2015'

what is the activity being carried out by users currently-- the same can be obtained through users on system feature in workspace in HFM (Activity Codes are mentioned at the end of this post)

--------------------------------------------------------------------------------------------------------------------------------

SELECT H.SSERVERNAME,s.susername,TO_CHAR((TO_DATE('01/1900','MM/YYYY')+H.DSTARTTIME-2),'DD/MM/YYYY hh24:mi:ss'),H.LACTIVITYCODE,H.SACTIVITYDESC FROM HSV_USERS_ON_SYSTEM H,HSV_ACTIVITY_USERS S

WHERE H.LUSERID IN S.LUSERID and H.LACTIVITYCODE<>0

order by sservername

To Check # of clusters & servers in application

------------------------------------------------------------

select * from HSX_CLUSTER_INFO

select * from HSX_CLUSTER_SERVERS

---------------to get no.of sessions created by each user----------------

select s.susername,count(1) from HSV_USERS_ON_SYSTEM h,HSV_ACTIVITY_USERS s

where h.LUSERID IN s.LUSERID and sservername='XXXXXXXXXX'

group by s.susername

Activity Carried out by user

------------------------------------

SELECT H.SSERVERNAME,s.susername,TO_CHAR((TO_DATE('01/1900','MM/YYYY')+H.DSTARTTIME-2),'DD/MM/YYYY hh24:mi:ss'),H.LACTIVITYCODE,H.SACTIVITYDESC FROM HSV_USERS_ON_SYSTEM H,HSV_ACTIVITY_USERS S

WHERE H.LUSERID IN S.LUSERID and H.LACTIVITYCODE<>0

order by sservername

HFM Errorlog total records

-------------------------------------

select count(*)  from hfm_errorlog;

HFM_ERRORLOG records per month

-------------------------------------------------

select to_char(to_date('01/1900', 'MM/YYYY')+DTIMESTAMP-2,'MM/YYYY') as "MMYYY", count(*) as "Number of entries"

from hfm_errorlog

group by to_char(to_date('01/1900', 'MM/YYYY')+DTIMESTAMP-2,'MM/YYYY');

Task Audit records per month

---------------------------------------

select to_char(to_date('01/1900', 'MM/YYYY')+StartTime-2,'MM/YYYY') as "MMYYYY", count(*) as "Number of entries"

from  AppName_TASK_AUDIT

group by to_char(to_date('01/1900', 'MM/YYYY')+StartTime-2,'MM/YYYY');

select to_char(to_date('01/1900', 'MM/YYYY')+StartTime-2,'DD/MM/YYYY') as "DDMMYYYY", count(*) as "Number of entries"

FROM AppName_TASK_AUDIT

GROUP BY TO_CHAR(TO_DATE('01/1900', 'MM/YYYY')+STARTTIME-2,'DD/MM/YYYY')

order by substr(to_char(to_date('01/1900', 'MM/YYYY')+StartTime-2,'DD/MM/YYYY'),7,4),substr(to_char(to_date('01/1900', 'MM/YYYY')+StartTime-2,'DD/MM/YYYY'),4,2) asc

To get records in task audit for 3 months in HFM

-----------------------------------------------------------

select g.servername,g.appname,to_char((to_date('01/1900','MM/YYYY')+g.starttime-2),'DD/MM/YYYY hh24:mi:ss'),

to_char((to_date('01/1900','MM/YYYY')+g.endtime-2),'D/MM/YYYY hh24:mi:ss'),g.strdescription,s.susername

from AppName_task_audit g,hsv_activity_users s

where to_char((to_date('01/1900','MM/YYYY')+g.starttime-2),'MM/YYYY') > to_char(add_months (sysdate, -3),'MM/YYYY') and

to_char((to_date('01/1900','MM/YYYY')+g.starttime-2),'YYYY') = to_char(sysdate,'YYYY')

Data Audit records per month

--------------------------------------------

select to_char(to_date('01/1900', 'MM/YYYY')+DTIMESTAMP-2,'MM/YYYY') as "MMYYYY", count(*) as "Number of entries"

from AppName_DATA_AUDIT

group by to_char(to_date('01/1900', 'MM/YYYY')+DTIMESTAMP-2,'MM/YYYY');

 Activity Codes in HFM

 ------------------------------

Activity Code	Activity Description
0	Idle
1	RulesLoad
2	RulesScan
3	RulesExtract
4	Consolidation
5	ChartLogic
6	Translation
7	CustomLogic
8	Allocate
9	DataLoad
10	DataExtract
11	DataExtractviaHAL
12	DataEntry
13	DataRetrieval
14	DataClear
15	DataCopy
16	JournalEntry
17	JournalRetrieval
18	JournalPosting
19	JournalUnposting
20	JournalTemplateEntry
21	MetadataLoad
22	MetadataExtract
23	MemberListLoad
24	MemberListScan
25	MemberListExtract
26	SecurityLoad
27	SecurityScan
28	SecurityExtract
29	Logon
30	LogonFailure
31	Logoff
32	External
33	MetadataScan
34	DataScan
35	ExtendedAnalyticsExport
36	ExtendedAnalyticsSchemaDelete
37	TransactionsLoad
38	TransactionsExtract
39	DocumentAttachments
40	DocumentDetachments
41	CreateTransactions
42	EditTransactions
43	DeleteTransactions
44	PostTransactions
45	UnpostTransactions
46	DeleteInvalidRecords
47	DataAuditPurged
48	TaskAuditPurged
49	PostAllTransactions
50	UnpostAllTransactions
51	DeleteAllTransactions
52	UnmatchAllTransactions
53	AutoMatchbyID
54	AutoMatchbyAccount
55	IntercompanyMatchingReportbyID
56	IntercompanyMatchingReportbyAcct
57	IntercompanyTransactionReport
58	ManualMatch
59	UnmatchSelected
60	ManageICPeriods
61	Lock/UnlockICEntities
62	ManageICReasonCodes
63	Null

Extracting user based security from Hyperion Planning

The Post takes me through my experience in providing solution to my client for they were very much in need of this as to analysis exact number of users and security assigned to them which helped in putting together the details interms of licenses and managing application security.

To Extract User Ids from Application Schema who belongs to application groups


Run Below Query in Planning Application Schema (Ex: PlnApp is my application schema)
-------------------------------------------------------------------------------------------------------------
create table unameingrp as
select g2.sid,o1.object_name from hsp_usersingroup g1,hsp_object o1,hsp_group g2
where o1.object_id=g1.user_id and g2.group_id=g1.group_id
order by 2



GRANT SELECT ON UNAMEINGRP TO HSS

Run Below Query in Shared Services Schema
-----------------------------------------
create table GrpUsrNames as
select c1.name,c2.object_name from css_groups c1,PlnApp.unameingrp c2
where c2.sid=c1.identity_id
order by c2.object_name

With this you have users in each application group.

Load Your Application Secfile into a RDBMS table I used sql loader with a control file(PlnApp.ctl)
-----------------------------------------------------------------------------
Your secfile can be obtained by running ExportSecuirty Utility available in Planning under below path.

user_projects/Dev/Planning/planning1

 ./ExportSecurity.sh -f:password.txt /A=PlnApp,/U=admin,/TO_FILE=/dev01/PlnAppSec

You should have your table columns in CAPS or else it gives column doesn't exist

Before you start loading data make sure you have created table in HSS schema as "PlnAppSecfile" as i am loading secfile data into that table through sql loader.

Control File Code (I named this control file as PlnApp.ctl)








LOAD DATA
APPEND INTO TABLE PlnAppSecfile
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
("GROUPNAME","DIMENSIONAME","ACCESSTYPE","ACCESSLEVEL","UNKNOWN")

if you have tab delimited fields then you need to use fields terminated by as below

LOAD DATA
APPEND INTO TABLE PlnAppSecfile
FIELDS TERMINATED BY x'09'
TRAILING NULLCOLS
("GRPS","NAM")

Batch File Code
rename PlnAppSec.txt Sec.dat
sqlldr username/password@DatabaseName DATA=Sec.dat control=PlnApp.ctl log=PlnApp.log errors=10000 rows=1000000 DIRECT=TRUE

Make Sure your PlnAppSec and batch file and ctl file are in same path, if not use absolute path.
Now Just double click the batch file your secfile would have got loaded into HSS schema table PlnAppSecfile, incase of any error refer to logfile.

Now you have Users under each group and access assigned on each group & username, so joining these 2 should give us the final result.

incase if you want more details in terms of dimension name after every artefact then you need to extract dimension details as well, like A12100987- Account,E1213232 - Entity...etc


Run Below Query in PlnApp Schema

1.create table test2 as select * from hsp_object;

2.update test2
set object_id=object_id-1;

create table dimdetails as
select h1.object_id,h2.type_name,h1.object_name actname,t2.object_name as aliasname from hsp_object h1,hsp_object_type h2,test2 t2
where h1.object_type in h2.object_type and h2.type_name='Account' and t2.object_type=10 and h1.object_id=t2.object_id
union all
select h1.object_id,h2.type_name,h1.object_name actname,t2.object_name as aliasname from hsp_object h1,hsp_object_type h2,test2 t2
where h1.object_type in h2.object_type and h2.type_name='Scenario' and t2.object_type=10 and h1.object_id=t2.object_id
union all
select h1.object_id,h2.type_name,h1.object_name actname,t2.object_name as aliasname from hsp_object h1,hsp_object_type h2,test2 t2
where h1.object_type in h2.object_type and h2.type_name='Version' and t2.object_type=10 and h1.object_id=t2.object_id
union all
select h1.object_id,h2.type_name,h1.object_name actname,t2.object_name as aliasname from hsp_object h1,hsp_object_type h2,test2 t2
where h1.object_type in h2.object_type and h2.type_name='Entity' and t2.object_type=10 and h1.object_id=t2.object_id
union all
select h1.object_id,h2.type_name,h1.object_name actname,t2.object_name as aliasname from hsp_object h1,hsp_object_type h2,test2 t2
where h1.object_type in h2.object_type and h2.type_name='User Defined Dimension Member' and t2.object_type=10 and h1.object_id=t2.object_id

GRANT SELECT ON DIMDETAILS TO HSS

come back to HSS schema and join your 3 tables:GrpUsrNames,PlnAppSecfile,DimDetails which is your Final result like,

user belongs to A,B,C groups and has access to Account,Entity,Scenario,CustomDimenion,Forms,BRs etc.

Hope this is useful.

Hyperion Planning RDBMS tables

To get Native USers Belonging to native groups in HSS
-------------------------------------------------------------------
select c1.name,c2.member_identity,c3.name  from css_groups c1,css_group_members c2,css_users c3
where c1.identity_id=c2.group_identity and c3.identity_id in c2.member_identity
order by c1.name


To Get users belonging to each native group in HSS
----------------------------------------------------------------
select c1.name,c2.member_identity from css_groups c1,css_group_members c2
where c1.identity_id=c2.group_identity
order by c1.name

Provision Report for Roles on Groups in HSS
-------------------------------------------------------
select p.application_id,p.createdtimestamp,c1.name,o.name,o.locale from css_provisioning_info p,css_groups c1,css_role_locales o
where p.member_identity in c1.identity_id and p.role_identity in o.identity_id and o.locale='en_US' and p.application_id like '%PLNAPP%'
order by c1.name

Object Types in Classic Application
Scenario=31,Account=32,Entity=33,Version=35

Object Types in EPMA Application
Scenario=134,Account=127,Entity=130,Version=155{Period=132,Year=157} 

Ignore vs Never

First Example shows the result of Ignore consolidation property when it is applied and second one shows Never consolidation property.

Ignore will not make the member to aggregate/roll up to its parent and that member can be aggregated across different dimension.

Never will not make the member not to aggregate to its parent nor across different dimension.

	C1	C2	C3	C	Dimension3
A1	1	10	100	111	111
A2	2	20	200	222	222		Ignore
A3	3	30	300	333	333
A	4	40	400	444	444
A321	#Missing	#Missing	#Missing	#Missing	#Missing
A332	#Missing	#Missing	#Missing	#Missing	#Missing
A123	#Missing	#Missing	#Missing	#Missing	#Missing
Dimension1	4	40	400	444	444
	C1	C2	C3	C	Dimension3
B1	1	10	100	111	111
B2	2	20	200	#Missing	#Missing		Never
B3	3	30	300	333	333
B	4	40	400	444	444
Dimension2	4	40	400	444	444