Thursday, April 21, 2011

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



No comments:

Post a Comment