extracting planning security

So true we can export/import security and import security using utilities exportsecurity or import security utilities respectively.SO at one of my client i ran into issue where these utils turned out be dead animal.So its dead end for me:-((.
So had to take a detour querying planning relational repository and now there is standard list of tables in there.I did some research creating a test app and studied the planning tables combining with some pieces information in few blogs.Posted on oracle forums to gather some more information .After that with my own test application assigned some security to some of the dimensions to make up some data into the tables.

Finally a nd slowly Took some 5+ weeks to explore the planning tables entirely .It wasn't that hard though.

This is a one of the version of sql query i wrote........

SELECT HSP_OBJECT.OBJECT_NAME,

HSP_OBJECT_1.OBJECT_NAME AS MEMBERNAME,

CASE HSP_ACCESS_CONTROL.ACCESS_MODE

WHEN 1 THEN 'READ'

WHEN 3 THEN 'WRITE'

WHEN - 1 THEN 'DENY' END AS ACCESSMODE,

CASE HSP_ACCESS_CONTROL.FLAGS

WHEN 0 THEN 'MEMBER'

WHEN 5 THEN 'CHILDREN'

WHEN 6 THEN 'ICHILDREN'

WHEN 8 THEN 'DESCENDANTS'

WHEN 9 THEN 'IDESCENDANTS' END AS ACCESSLEVEL,

HSP_OBJECT_TYPE.TYPE_NAME, HSP_OBJECT_1.GENERATION,

CASE HSP_OBJECT_1.HAS_CHILDREN

WHEN 0 THEN 'NO'

WHEN 1 THEN 'YES' END AS 'HAS CHILDREN',

HSP_OBJECT_2.OBJECT_NAME AS ALIASNAME

FROM HSP_OBJECT AS HSP_OBJECT_2 INNER JOIN

HSP_ALIAS ON HSP_OBJECT_2.OBJECT_ID = HSP_ALIAS.ALIAS_ID RIGHT OUTER JOIN

HSP_OBJECT INNER JOIN

HSP_GROUP ON HSP_OBJECT.OBJECT_ID = HSP_GROUP.GROUP_ID INNER JOIN

HSP_OBJECT AS HSP_OBJECT_1 INNER JOIN

HSP_ACCESS_CONTROL ON HSP_OBJECT_1.OBJECT_ID = HSP_ACCESS_CONTROL.OBJECT_ID INNER JOIN

HSP_OBJECT_TYPE ON HSP_OBJECT_1.OBJECT_TYPE = HSP_OBJECT_TYPE.OBJECT_TYPE ON

HSP_GROUP.GROUP_ID = HSP_ACCESS_CONTROL.USER_ID ON HSP_ALIAS.MEMBER_ID = HSP_ACCESS_CONTROL.OBJECT_ID

WHERE (HSP_OBJECT_1.OBJECT_TYPE IN (31, 32, 33, 35, 50))

ORDER BY HSP_OBJECT_1.OBJECT_TYPE

Managing Databases and Applications ->Essbase

The common types of database artifacts in Essbase are outline,data sources,load and build rule files , calculation scripts,report scripts ,security definitions,filters,LRO's and partitions ,while some of the above like calc scripts,filters and LRO's are optional.

so where are these artifacts and essbase related files are stored on the file system???
Essbase installation files and files that are created when using Essbase are stored in the following
locations:
l .ESSBASEPATH—The Essbase installation directory.
In a default installation, the ESSBASEPATH directories are:
For Essbase Server:
Oracle/Middleware/EPMSystem11R1/products/Essbase/EssbaseServer
For Essbase Client:
Oracle/Middleware/EPMSystem11R1/products/Essbase/EssbaseClient

For a list of directories that are created under ESSBASEPATH, see the Oracle Hyperion
Enterprise Performance Management System Installation and Configuration Guide.

2. ARBORPATH:
The app directory location where Essbase application files (as they are created) and
sample applications and databases (provided with Essbase) are stored.
The bin directory location where Essbase configuration setting (essbase.cfg) and
security (essbase.sec and essbase.bak) files (as they are created) are stored.
In a default installation, ARBORPATH is:
MIDDLEWARE_HOME/user_projects/epmsystem1/EssbaseServer/essbaseserver1

In a default installation, MIDDLEWARE_HOME is: Oracle/Middleware

EPM_ORACLE_HOME—Directory under which all Oracle Hyperion Enterprise Performance
Management System products are installed.
In a default installation, EPM_ORACLE_HOME is:MIDDLEWARE_HOME/EPMSystem11R1
For example:Oracle/Middleware/EPMSystem11R1
l.EPM_ORACLE_INSTANCE—Deployed product location (includes data and applications,
deployed Web applications, and log files).
In a default installation, EPM_ORACLE_INSTANCE is:MIDDLEWARE_HOME/user_projects/epmsystem1
For example:
Oracle/Middleware/user_projects/epmsystem1
2 MIDDLEWARE_HOME/user_projects/epmsystem1/common—Directory where common
in.ternal and third-party components are stored.
3. MIDDLEWARE_HOME/user_projects/epmsystem1/diagnostics/logs—Directory
where log files are stored.
For more information about MIDDLEWARE_HOME, EPM_ORACLE_HOME, and
ESSBASE_ORACLE_INSTANCE, see the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide.

Maintaining Essbase

Managing Essbase using OPMn will be covered later

Starting Esssbase server in the Background depends on th OS
To start Essbase Server in the foreground, use a method for the operating system on which Essbase Server runs (these examples assume that Essbase Server had been started at least once before):l For UNIX, enter the following command at the operating system prompt:
ESSBASE
2 For Windows, choose a method:
Enter the following command at the operating system prompt:
essbase
3 On the Start menu, select Programs, then Oracle EPM System, then Essbase, then Essbase
Server, and then Essbase.
4 In the file system, double-click the essbase.exe file.
5 On the Start menu, select Run and enter essbase.

Note: You cannot start Essbase Server from ESSCMD or MaxL.
To start Essbase Server in the background on UNIX, or on Windows systems utilizing a UNIXlike shell such as MKS, enter the following command at a command prompt:
essbase -b &

STOPPING ESSBASE
You need Administrator permissions to stop or shut down Essbase Server.
ä To stop Essbase Server and all running applications, use
quit exit Enter the Agent command at the command prompt in the Essbase Server console window.
Using MaxL alter system shutdown
Using ESSCMD SHUTDOWNSERVER (refer:Oracle Essbase Technical Reference)

Hour Glass structure Too outdated

Courtesy
Edward, Oracle ACE Director - HyperionI give a presentation every year at the Hyperion conferences called "How Essbase Thinks." The "Hour Glass Model" is something that I have to readdress every year, because there' a ton of bad information out there on it.

First of all, the whole "largest dense to smallest dense, smallest sparse to largest sparse" thing is a serious oversimplication. Back in the pre-Essbase 7 days, here's what it really meant (in order from top to bottom):
- Densest dense dimension down to your least dense dense dimension.
- Sparse dimension with the smallest ratio of parents to children up through your sparse dimension with the largest ratio of parents to children.

The whole "put Accounts first and Time second" only matters if you still calculate your database doing a CALC ALL (which no one should be doing unless your database is the size of Sample.Basic). Might as well put them first, so the logic went, since CALC ALL was going to calculates your Account dimension first then your Time dimension anyway (because that's the order of dimensions CALC ALL uses).

That said, this all went out the window with the advent of parallel calculation. This made it much better to put all your non-aggregating sparse dimensions at the end of your outline. Non-aggregating dimensions are dimensions with no stored ancestor values (like Scenario, Versions, and Year, typically are, for instance).

Further, with the advent of databases being able to use multiple compression types (Essbase 7x and later), you should be using RLE compression (most of the time, at least) with your Time dimension first to maximize repeating values in your outline.

That gives us a current "Modified Hourglass" model of the following:
- Time (if it's dense)
- Accounts (if it's dense)
- Densest dense dimension to
- Least densest dense dimension
- Sparse dimension with the smallest ratio of ancestors to level 0 members
- Sparse dimension with the largest ratio of ancestors to level 0 members
- Largest sparse dimension (if you're using the calculator cache)
- Non-aggregating sparse dimensions

It's complicated, I know, which is why people have been saying Hourglass since 1995. I could also go on to add that since dense dimensions are typically dynamically calculated at all the upper-levels that the order of your dense dimensions is more about storage and retrieval (and less about calcing) these days, but that would be too much boringness for one post.

On Essbase Optimization:
For anyone that has to optimize Essbase cubes at their company, there's an entire day spent on Essbase optimization (including both optimizing outlines taught by Tracy McMullen and optimizing calc scripts taught by me) at ODTUG Kaleidoscope. At last check, there were 15 attendee spots still available in the Essbase track at Kaleidoscope:
http://www.odtugkaleidoscope.com/hyperion.html

Unable to connect to Provider Server

Coutesy:John Goodwin
If it is not reachable then it sounds like APS is not fully up and running.
Have you checked the aps log, if on windows check user_projects\epmsystem1\diagnostics\logs\services\ HyS9aps-sysout.log and HyS9aps-syserr.log

Once APS is running

The URL for a shared connection should be :- http://:19000/workspace/SmartViewProviders
This is entered in excel, smartview options > advanced > shared connections url

You will need to make sure "Provider Services","Foundation Services - Managed Server", "Oracle Process Manager (ohsinstance....)" are running.

Re: error 1051419 essabase application {app name}Registration failed

You have to use a Microsoft word.Open a word document->Go to File->Click Open ->type the url http://server:28080/interop/content/ in the file name.(look screen shot 1)



It will prompt for user and password type in and then go to Files->AdminProjects->Default application Group(look screen shot 2)
then look for the application and then delete it.


also look in for the application name in that project folder too.....(shot 2)

If MS word is not working also you can navigate through using MAP NETWORK DRIVE option in the windows and follow the same steps above.

You can also use Dav Explorer
http://davexplorer.org/download.html
and also follow their installation instructions

Removing Bulk User from Groups ---shared services v 11.1.x.x

1.login into shared services
go to the group from from which you want to remove .Double click the groups and you see a window pop up got to the USER MEMBER from there you can remove it manually.
2.if you want to users from many groups ,you can also do this
login into that shared services
go to FOundation Application group->click sharedservices->drop down native directory ->Right click on Groups and select export for edit.THat will save you Groups.csv file.
1,open that file
2.go to the group ,from which you want to remove the users (ctrl +F) .DO remove all the unwanted users.
3.remove the users entry under that group (whole row)and save it to the same file..
4go to FOundation Application group->click sharedservices->drop down native directory ->Right click on Groups and select IMPORT for edit.

thats all

or
3.you can maxl to do this
http://download.oracle.com/docs/cd/E12032_01/doc/epm.921/html_techref/maxl/ddl/statements/altusr.htm

Moving Essbase Security From essbase 7.X to 11.1.2.0

if you have LDAP/MSAD try to configure it first .That will get your users

Now using maxl

spool on to GROUP.txt
display gruoup all;
.....
spool on to USER.txt
display user in group all;
.....
for test purpose create a test group and a test user from the shared services.

Now using GROUP.txt
make up maxl statements to create groups(use any advanced text editor or MS excel to get your work done fast)
create group 'groupname';
...
.
now login into that shared services
go to FOundation Application group->click sharedservices->drop down native directory ->Right click on Groups and select export for edit.THat will save you Groups.csv file.

Now
1.Open that Groups.csv file
2.Using USER,txt ,paste the users in that file under their respective group.(Look for test group created that should give you an idea!!!)
3.Paste user correctly and save it to the same file Groups.csv
4.go to FOundation Application group->click sharedservices->drop down native directory ->Right click on Groups and select IMPORT for edit.
5.that will get your users into the groups.
________filters_______
Using maxl again
When we use the migration wizard filters will move along with cubes
(special case(not sure if its same with every one out there): filters do not happened to move migrating to oracle essbase 11.1.2.0)
spool on to FILTER.txt
display filter row all;
.............
spool on to GRPRIVILEGE.txt
display privilege group all;
...
Now using FILTER.txt
create maxl statements
(use any advanced text editor or MS excel to get your work done fast)

Ex: create filter app.database.filtername read/write/none/metaread on 'AREA ' ;
..........
(The above filter creation can be omitted if filters move in along with migration using migration wizard)

Using GRPRIVILEGE.txt
create maxl statements
grant filter app.databse.filtername to 'groupname';
........
that should get your filters created and assigned.


else you can use Advanced Security Manger
http://www.appliedolap.com/free-tools/advanced-security-manager

Creating BSO and ASO application.

go to windows start->oracle EPM->Essbase->Essbase administration services->start administration service console



now enter the login details



OR u can use the url in an internet browser(ie6 or mozilla 2.x)
http://servername:10080\easconsole\easconsole.jnlp

Right click on application node-?select create application you can either crete ASO or BSO application.


Select USING AGGREGATE STORAGE
select the essbase server and give an application name which shoudn't exceed 8 bytes(i have given testASO1). and click ok
similary select USING BLOCK STORAGE ,give a name to it (eg:testBSO1). and click ok.



now right click on the application and select create database give an database name for now i will leave the option allow duplicate members names unchecked and then click ok.





repeat the same for creating a database In BSO application.

next i will show you outline building using rule files ,free form loading and dataloading.

Star Shema and OLAP

STAR SCHEMA
also known as star join schema.
Don't get so worried about Star schema, it is important to know about it, because it builds up the concept of Multidimensional database system.
"It is a method or a way to implement a multidimensional data base system from relational database".............
So what does this Star schema got to do ?
where do we actually need to it?


THe main feature of the star schema is FACT TABLE and DIMENSION TABLES,Which allows browsing,drill down,specifying criteria.

FACT TABLE:The fact table contains the primary key madeup of Foreign keys that have migrated from all dimensions.The fact table is usually skinny and long.
Facts and Dimensions

FACTS AND DIMENSIONS

When examining how people look at data, they usually want to see some sort of aggregated data. These data are called measures or facts. These measures are numeric values that are measurable and usually additive.
For example, sales dollars are a perfect measure. Every order that comes in generates a certain sales volume measured in some currency. If your company sells 20 products in one day, each for $5 dollars, they generate 100 dollars in total sales. Therefore, sales dollars is one measure or fact most companies track. Companies may also want to know how many customers they had that day. Did 5 customers buy an average of 4 products each, or did just one customer buy 20 products? Sales dollars and customer counts are two measures businesses may want to track.

DIMENSIONS
Just tracking measures isn't enough, however. People need to look at measures using those "by" conditions. The "by" conditions are called dimensions. In order to examine sales dollars, people almost always wan to see them by day, or by quarter, or by year. There is almost always a time dimension on anything people ask for. They may also want to know sales by category or by product. These "by" conditions will map into dimensions: there is almost always a time dimension, and product and geography dimensions are very common as well.

Therefore, in designing a star schema, the first order of business is usually to determine what people want to see (the measures) and how they want to see it (the dimensions).



Reference:
Designing the Star Schema Database
Version 1.1
By Craig Utley
This is one of the best articles.

CLICK TO READ THE ARTICLE

Things to Know before You Start with hyperion Essbase

Two key concepts you should clearly bullet into your brains else you are always HAUNTED
1.Dense
2.Sparse
Dense: The name itself refers more concentrated speaking related to the content,where the more data or matter or details exist.

Sparse: It means less concentrated or speaking related to the content,where the data may exist may not exist at all or relatively very less compared to dense matter.Speaking more technically where the data is not uniformly or smoothly distributed.

Lets say you own a International Food Supply Business,now lets say you manufacture Bread,Noodles and Rice(Its an Assumption...ok)
lets Go little ahead
Now your RICE gets sold strictly only in INDIA
and your BREAD gets sold strictly only in UNITED STATES
and NOODLES strictly only in CHINA.

Now all the above products are not sold in every country ,that define your products RICE,BREAD and NOODLES as Sparse.

Secondly you are selling all the three products in their respected countries all round the YEAR since decades.Thats not enough still u have keep the track of the records,i mean ACCOUNTS for the products or each product,whether you are making good sales,are you making a profit or loss.By using the track of the these records you can make decisions whether to increase the production of the goods in a particular country if you are making a profit else reduce the production if you are at Loss and all other related stuff like expenses,revenues....etc,.

Finally your company sells al the 3 products dring all the months(time) of the YEAR and you got maintain the ACCOUNTS for the 3 products all the time.(Weekly,monthly Quartely,yearly).So the TIME and ACCOUNTS exists for all the PRODUCTS in each COUNTRY.
You can conclude that TIME AND ACCOUNTS as DENSE DIMENSIONS.