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

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?