Essbase 11.x Workshop - 1: ASO Target of a partition Part I

Hi Peers,

I got some time now. I thought of conducting work shop to test the new features of Essbase 11.x and how they are helpful in delivering the requirements and in improving the system. 

Technologies that I have used for this work shop are
1. Windows 2008 server 32 bit
2. MS SQL SERVER 2005 sp3
3. Oracle WebLogic Server 11gR1 (10.3.4)
4. Oracle Hyperion Foundation Services Release 11.1.2.1.0
5. Oracle Essbase Release 11.1.2.1.0
6. Oracle Essbase Admin Services Release 11.1.2.1.0
7. MS Office 2007

First of all, I would like to discuss the new features provided with partition involving ASO cubes.

This workshop will demonstrate the ability to create partition with ASO cube as target.

What is supported up to Essbase 9.x?  

§  There was full support only for BSO partitioning.

§  ASO could only be the source of a transparent partition means ASO to BSO transparent partition.

These features are already in practice and hope no discussion required now.

What is expected to support from Essbase 11.x?

§  As in 9.x, there was full support for BSO partitioning.

§  ASO to BSO transparent partition.

§  BSO to ASO transparent partition.(New)

§  ASO to ASO transparent partition. (New)

§  BSO to ASO replicated partition. (New) 

We will have a detailed description of the new flavors in the partition. 

1.       BSO to ASO transparent partition:

Preparation and set up

1.      Make a copy of the Samppart application called BSRC (BSO Source), by right-clicking on Samppart to and choosing Copy from the menu.

 

This will create an application with the same outline, but without the partitions and data. It will also copy the file Calccomp.txt where we will load the data from.

    

2.       Right-click on the database and pick “Load data”. Load the file calccomp.txt. It is a freeform file. No need of rule file.

     

 3.      And then perform a default calculation.

4.       Create an ASO application for example called ATRG(ASO Target)

5.       With a database called Basic.

6.       Here we require outline should be in sync with source BSO application. So BSO outline should be converted to ASO outline. We are using Aggregate storage outline conversion wizard to do this.

7.       Converting BSO outline into ASO outline: This section will discuss the process and pains in converting a BSO outline into ASO outline. 

a)      Open the Aggregate storage outline conversion wizard

b)      Select the source BSO outline from the database BSRC.Company.

c)       Click Next. It will move to the Verify Corrections to the outline window.

d)      See we had errors and warning s for the shared hierarchy in the Product dimension and for the formula members in the Scenario dimension. To fix these, select Interactive outline correction and click next. Then we will see a interface called Aggregate storage outline conversion just like a outline editor

e)      Change the Scenario dimension from stored to dynamic and delete the Diet hierarchy in the Product dimension.

f)       Verify the outline.

g)      Outline verified successfully. Now click on done. It will move to the Select Destination for Aggregate Storage Outline wizard. Select Essbase Server and select the database as ATRG.Basic.Basic as shown in the below snapshot.

h)      Click on Next.

i)        Click on Yes. That will complete the conversion and leads to

j)        Click on finish.

k)      Open the ATRG.Basic outline in the Edit mode and click on verify. We can see the MDX formula errors as below

l)        I don’t want to waste the time in fixing as the context of workshop is different. Modified the BSO formulas as below to justify minimum.

Member name	BSO Formula	Modified ASO Formula
Opening Inventory	IF(NOT @ISMBR(Jan))"Opening Inventory"=@PRIOR("Ending Inventory"); ENDIF;	(Year.CurrentMember.Lag (1), [Ending Inventory]).Value)
Margin %	Margin % Sales;	([Margin] / [Sales]) *100
Profit %	Profit % Sales;	([Profit] / [Sales])*100
Variance	@VAR(Actual, Budget);	[Actual]-[Budget]
Variance %	@VARPER(Actual, Budget);	([Variance]/[Actual])*100

m)    And then verified after successfully.

n)       Click on save to save the outline.

8.       Now both source BSRC and target ATRG applications are ready. 

9.       Before creating transparent partition from BSO source to ASO target, we will copy the actual data from BSO to ASO.  

10.   This we can do it in many ways like

a)      Exporting actual data from BSO using calc script and can import into ASO with rule files.

b)       Export the levo data from BSO and can load into ASO. After loading we can clear the budget in ASO.

c)       We can use the new feature BSO to ASO replicated partition to copy actual data into ASO application

Have chosen the option c as it is the best and at the same time we can validate the new feature how it works.

11.       BSO to ASO Replicated Partition: In this section we will discuss how we can employ the replicated partition to transport the data from BSO to ASO application. 

a)      First we will take the high level snap shot of the actual data in BSRC cube.

 

	Product	Market
	Actual	Actual	Actual	Actual
	Qtr1	Qtr2	Qtr3	Qtr4
Sales	75199	79230	82239	76789
COGS	33797	35777	37279	34556
Margin	41402	43453	44960	42233
Marketing	12318	13005	13605	12588
Payroll	9573	9639	9573	9573
Misc	188	201	216	208
Total Expenses	22079	22845	23394	22369
Profit	19323	20608	21566	19864

b)      Create the partition for actual Type: Replicated

c)      Connection:

Data Source: BSRC.Company (BSO)

Data Target: ATRG.Basic (ASO)

d)      Areas: Partition is restricted to Actual scenario and excluding the shared hierarchy in Product dimension.

e)      Cell count verification

f)      Validating partition.

g)      Save the partition and verify.

h)       Now Replicate the data from source to target with selection Update all sells.

i)      Replication of data completed successfully.

j)      Verify the data in the target.

	Product	Market
	Actual	Actual	Actual	Actual
	Qtr1	Qtr2	Qtr3	Qtr4
Sales	75199	79230	82239	76789
COGS	33797	35777	37279	34556
Margin	41402	43453	44960	42233
Marketing	12318	13005	13605	12588
Payroll	9573	9639	9573	9573
Misc	188	201	216	208
Total Expenses	22079	22845	23394	22369
Profit	19323	20608	21566	19864

k)       Data verified successfully.

l)      Drop the replicated partition. It is not required to be intact once data is replicated.

 

12.       BSO to ASO replicated partition is validated is successfully and now ASO application is loaded with actual data.

 Findings here at this moment are ASO can be a target of replicated partition but it can not be a source for the replicated partition.

        Watch out for the second part of the blog post Essbase 11.x Workshop - 1: ASO Target of a partition Part II