Thursday, April 21, 2011

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


This is the second part of the blog post. first read the fiirst part  Essbase 11.x Workshop - 1: ASOTarget of a partition Part I. 

In this second part we will discuss the

a)      BSO to ASO transparent partition
b)      ASO target of the two sources - BSO and ASO 

Before start working on these items a short while we will see the missing thing in the Part I.    This is to test whether we can employ the replicated partitions with ASO as source. 

We will try to create the replicate partition from ATRG.Basic (ASO cube) to BSRC.Comapny (BSO cube). 

a)      Create partition for Aggregate Storage application. Type: Replicated



b)      Connection:
Data Source: ATRG.Basic
Data Target: BSRC.Company



c)       Cell Count matching verification



d)      Validate the partition



This proves replicated partition will not be supported ASO as a data source.

Now we will move to the next BSO to ASO transperent partition. 

1.       BSO to ASO transparent partition:  Now we will create transparent partition between BSO source and ATRG(ASO target) for budget as ASO cube is preloaded with actual data 

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

Product
Market



Budget
Budget
Budget
Budget

Qtr1
Qtr2
Qtr3
Qtr4
Sales
70790
74650
77590
71100
COGS
30300
32200
33640
30550
     Margin
40490
42450
43950
40550
Marketing
9210
9810
10320
8970
Payroll
7200
7320
7200
6420
Misc
#Missing
#Missing
#Missing
#Missing
     Total Expenses
16410
17130
17520
15390
          Profit
24080
25320
26430
25160

b)      Create the partition for actual Type: Transparent


c)       Connection:
Data Source: BSRC.Company (BSO)
Data Target: ATRG.Basic (ASO)


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


e)      Cell count verification


f)       Validating transparent partition.


g)      Will click on ok that leads to the warnings.


h)      This is weird as partition is not overlapped and we defined for the independent area Budget and actual data is there in local to the ASO cube. We will expect to see both Local (Actual) data and remote (Budget) data. But the warning message is about to restricting the query from the local data Actual.  
i)        Any how we will save the partition and see the queries.


j)        Just a look how the partition was created. It’s fine.


k)      We will try to query the actual and budget from the Target ASO cube.



Product
Market




Qtr1
Qtr2
Qtr3
Qtr4
Actual
Sales
#Missing
#Missing
#Missing
#Missing
Actual
COGS
#Missing
#Missing
#Missing
#Missing
Actual
     Margin
#Missing
#Missing
#Missing
#Missing
Actual
Marketing
#Missing
#Missing
#Missing
#Missing
Actual
Payroll
#Missing
#Missing
#Missing
#Missing
Actual
Misc
#Missing
#Missing
#Missing
#Missing
Actual
     Total Expenses
#Missing
#Missing
#Missing
#Missing
Actual
          Profit
#Missing
#Missing
#Missing
#Missing
Budget
Sales
70790
74650
77590
71100
Budget
COGS
30300
32200
33640
30550
Budget
     Margin
40490
42450
43950
40550
Budget
Marketing
9210
9810
10320
8970
Budget
Payroll
7200
7320
7200
6420
Budget
Misc
#Missing
#Missing
#Missing
#Missing
Budget
     Total Expenses
16410
17130
17520
15390
Budget
          Profit
24080
25320
26430
25160

l)        Budget data is validating successfully with source BSO cube BSRC.Company. But actuals queried against the local data is returning Missing data means the data was ignored during query time because of the partition as seen in the warning message.  
m)    Will see whether we can query the data after dropping the partition.



a)      Will see the retrieval again.



Product
Market




Qtr1
Qtr2
Qtr3
Qtr4
Actual
Sales
75199
79230
82239
76789
Actual
COGS
33797
35777
37279
34556
Actual
     Margin
41402
43453
44960
42233
Actual
Marketing
12318
13005
13605
12588
Actual
Payroll
9573
9639
9573
9573
Actual
Misc
188
201
216
208
Actual
     Total Expenses
22079
22845
23394
22369
Actual
          Profit
19323
20608
21566
19864
Budget
Sales
#Missing
#Missing
#Missing
#Missing
Budget
COGS
#Missing
#Missing
#Missing
#Missing
Budget
     Margin
#Missing
#Missing
#Missing
#Missing
Budget
Marketing
#Missing
#Missing
#Missing
#Missing
Budget
Payroll
#Missing
#Missing
#Missing
#Missing
Budget
Misc
#Missing
#Missing
#Missing
#Missing
Budget
     Total Expenses
#Missing
#Missing
#Missing
#Missing
Budget
          Profit
#Missing
#Missing
#Missing
#Missing
Now we are seeing actual and budget intersections returned missing data.  This is justified as there exists no transparent partition to pull the budget data

        Findings here at this moment are ASO can be a target of transparent partition to pull the entire data from the source but not useful to report local as well as remote data simultaneously.

Next we will move on to the item ASO target of two sources - BSO and ASO.
1.       ASO target of the two sources - BSO and ASO: we will test this scenario as well. For this we require Two ASO applications and one BSO application. The plan is BSO application holds budget data. One ASO application holds actual data and another ASO application is the target for both ASO and BSO sources. Target application didn’t have any data and it is only interface to read the data from source cubes.
 
a)      First we will create another ASO application by copying ATRG application called AINT (ASO Interface).


b)      Copying ASO application copy data also. So shall clear the data.


c)       Click yes to proceed. 
d)      First we will create the transparent partition between two ASO applications ATRG and AINT. 
e)      Create partition for aggregate storage application. Type: Transparent


f)        Connection
Data Source: ATRG.Basic
Data Target: AINT.Basic


g)      Areas: Outlines are exactly equal. So partition is defined for Actual
h)      Cell Count matching verification


i)        Validating the transparent partition between two ASO applications.


j)        No issues. It got validated. We will save it and verify the queries.


k)      Below are the retrieval results. Found no issues.



Product
Market




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

l)        Then we will create the partition from the second source i.e. BSO cube BSRC.
This just repetition of the steps “a” to “j” in the step 10 – BSO to ASO transparent partition except the warnings in the partition validation as there is no local data in the target cube (AINT). I am excluding that section.
a)      Just look in how the partitions are created.

b)      What the above diagram is revealing? - One ASO cube AINT.Basic is the target of two cubes  one BSO cube BSRC.Company and another ASO  cube ATRG.Basic and both are in transparent partition with target ASO cube.
c)        will query the data from both sources and see the results.




Product
Market




Qtr1
Qtr2
Qtr3
Qtr4
Actual
Sales
75199
79230
82239
76789
Actual
COGS
33797
35777
37279
34556
Actual
     Margin
41402
43453
44960
42233
Actual
Marketing
12318
13005
13605
12588
Actual
Payroll
9573
9639
9573
9573
Actual
Misc
188
201
216
208
Actual
     Total Expenses
22079
22845
23394
22369
Actual
          Profit
19323
20608
21566
19864
Budget
Sales
70790
74650
77590
71100
Budget
COGS
30300
32200
33640
30550
Budget
     Margin
40490
42450
43950
40550
Budget
Marketing
9210
9810
10320
8970
Budget
Payroll
7200
7320
7200
6420
Budget
Misc
#Missing
#Missing
#Missing
#Missing
Budget
     Total Expenses
16410
17130
17520
15390
Budget
          Profit
24080
25320
26430
25160

d)      We are able to read the data from both different sources through one ASO cube successfully.
Findings/ Suggestions:
a)      ASO can be a target of transparent partition but we cannot read the local data available ASO though your partition definition is not overlapped
b)      We can use multiple sources with different flavors for the same ASO target.
c)       BSO to ASO replicated partition – We can use the BSO as a calc engine to process complex calculations and formulae at lev0 and can replicate the lev0 data into ASO to leverage the efficient storage and aggregating abilities in ASO.
d)      For the same cause we can use BSO to ASO transparent partition where calculated lev0 data exists in BSO cube and the transparent partition can be defined at lev0. ASO will provide the consolidation level data at the query time. I have had some doubts regarding the performance in this case when processing large volumes of data in production.
e)    ASO can not be the data source for replicated partition.

Workshop evaluation:
a)      Are you planning on using the features discussed? If so, could you describe the use case?
b)      Have you previously been using other flavors of partitioning? If so, which ones? Can you briefly describe the use cases?
c)       Other comments?


 

1 comment:

  1. This post of yours had a lot of informational and useful things. Thank you so much for writing for us,i appreciate your hard work.

    ReplyDelete