In BPC 10.1 we have introduced the possibility for the line of business (LoB) user to create local data. This can be either done by uploading a csv file and actively creating a local provider or by creating local planning enabled measures in the BPC local query designer.
On the other hand the central IT can provide the user with planning applications on (global) InfoCubes/aDSOs. As the goal of BPC Embedded is to enable the combination of IT driven global scenarios and LoB driven scenarios it is necessary to enable the exchange of data between the global and the local providers.
Data exchange between providers in usually done via planning functions. In this blog we show how such a planning function can be created in both cases. Note that the way how to proceed is slightly different in the two cases. At the end of the blog we give a small summary.
Case 1: Local data created by uploading local provider
We want to use a concrete example to explain the way how to proceed. Let us assume that we have a globally operating company that is doing a work force planning for the next year. The company is only interested in the effort-days per country. It does not need to know the numbers by employee. The planning for each country is done in decentralized teams (divisions) that are free in how they do their planning process as long as they deliver the effort-days in the end of the process.
For the local planner it is more convenient to do the planning on a more details level, knowing the employees, knowing when they are in vacation or parental leave, and knowing whether there will be a new employee and when this employee will start working operatively. Thus the planner wants to plan by country, employee, and month.
So we have a (simplified) ‘global’ planning InfoCube that contains the characteristic Country (TECNTRY) and the key figure TEED (Employee Days).
The local planner has created a local provider (by uploading a file) that contains a dimension for country, a dimension for employee and twelve key figures, one for each month. When uploading the local provider the system generates a default aggregation level on the local provider and the local dimensions.
This is an example of the uploaded file (Please keep in mind that the file upload is currently necessary for building the structure of the provider. The data in the local provider can later be entered for example in an Analysis for Office workbook):
And this is the information about the aggregation level you receive after uploading the file:
In the details for the local provider we also find the information about the local dimensions and the local names:
We do not have a CompositeProvider (or MultiProvider) that contains the target (global InfoProvider) and the source (local provider) of the planning function – which usually is prerequisite to create the desired aggregation level and planning function.
However we can use a Fox formula to do the trick. In Fox formulas we can read data from arbitrary aggregation levels. Thus we have the ability to create the Fox formula on the target InfoProvider and read the source data from the local provider via the new Fox feature. As we have all necessary information we can go ahead and create our Fox Formula on an aggregation level on the global plan InfoProvider:
* we have to declare the name of the aggregation level we want to read data from
INFOPROVIDER B100_0001. * we need various variables DATA CNTRY TYPE TECNTRY. DATA SUM TYPE I. DATA KYF TYPE KEYFIGURE_NAME.
* we need the name of the local country and employee DATA LOC_CNTRY TYPE '@3B100_LOC_PLAN@F0000'. DATA EMP TYPE '@3B100_LOC_PLAN@F0001'.
* country is not in the fields to be changed but we want to know which * is the country we are currently working on so we can make sure we read * data for the same country from the local provider CNTRY = OBJV(). LOC_CNTRY = CNTRY. * our target provider does only contain one key figure; * we go through all 12 key figures in the local provider and * add the data over all employees and all key figures; * the data from the (local) aggregation level is read in the following way:
* Name_of_ALVL.{Name_of_key_figure, local_country, local_employee}
FOREACH EMP IN B100_0001. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0002, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0003, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0004, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0005, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0006, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0007, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0008, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0009, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0010, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0011, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0012, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0013, LOC_CNTRY, EMP} . ENDFOR.
* this is an alternative way to total up the data:
*FOREACH KYF, EMP IN B100_0001.
* SUM = SUM + B100_0001.{KYF, LOC_CNTRY, EMP } .
*ENDFOR. {TEED} = SUM.
Now we can create a work book using a query on our local provider for data entry, a query on the global provider for displaying (and maybe changing) the required global plan data, and a button that runs our planning function for transmitting the data.
Case 2: Local data created by local query designer
The creation of a local provider we have described above already requires some modelling skills by the LoB user. With BPC10.1 and BW 7.50 we have introduced a concept where the LoB user can easily extend an existing data model (usually provided by IT department) in the BPC Web Admin local query designer.
Let us assume that again we are doing some headcount planning. The IT department has provided some infrastructure for this: a composite provider containing two aDSOs or InfoCubes (one for the actual data and one for the plan data).
We have a BPC Embedded environment in place and have set the following settings allowing the LoB users to create local queries.
The LoB planner uses the new BPC Web Admin client based local query designer to create a planning enabled query that shows the 2015 actual data in one (display-) column and the 2016 plan data in a planning enabled column. The data in the plan column will be visible throughout the entire BW (provided the user has the correct BW authorizations), this is why we call this data ‘global’.
Please note that it is not necessary to base the local query on an aggregation level – this will be done by the system implicitly.
New the user wants to start the planning but wants to have his ‘private’ version of the data that is not visible to user that are not working in the same area as our user. Users working in the same area share the same environment in our model. So the user creates a ‘local planning enabled measure’ that will hold to local plan data. This local data is only visible within the environment. As opposed to our first case the user does not have to know about the provider but simply adds the local measure to the query. The system will generate a corresponding provider automatically.
The new measure can now be used in the column. It makes sense to restrict it to the same year (and if necessary version) as the column for the global plan data.
Now we can use the local query in a workbook and enter data in the local plan column. Please note that you can also create a local planning enabled measure by simply extending an existing reporting query.
Above the query definition the system indicates the name of the provider the query in based on. This provider is an automatically generated local CompositeProvider that comprises the original (Composite) provider defined by IT and the local AINX holding the data of the local planning enabled measure.
We can use this provider to create an aggregation level and a planning function that copied the data from the local provider to the global planning provider.
Go to the transaction rsplan (or use the eclipse based BW modelling tools) and create a new aggregation level. The name of the local CompositeProvider will not be available from the value help in BW but you can copy and paste the name from the BPC Web Admin Client (in our case @3B477_PU2R9).
Now create a Fox formula in order to copy the data. We cannot use a copy function as a copy function cannot copy from one key figure to another. But the Fox formula is rather simple. In our case we just use the InfoProvider in the characteristics that are to be changed. In the value help for the key figure and for the provider you will find also the name of the local key figure and the name of the local provider – thus you do not even have to search for the names in the Web Admin Client.
The final Foxformula looks as follows:
{TEED,TE15_PL} = {'@3B477_PU2R9@F001','@3B477_61111140329583'} .
TEED is the technical name of the key figure in the global InfoProvider, TE15_PL is the InfoProvider.
@3B477_PU2R9@F001 is the generated name of the local planning enabled measure and @3B477_61111140329583 is the name of the generated local index (provider).
Finally create a filter and use the planning function with the filter, e.g. in an Analysis for Office workbook.
Summary and Outlook
Above we have described two cases of how local data can be generated and how the transfer is done:
Case 1: Local data created by uploading local provider
Create a Fox formula on the target InfoProvider and use the reference data feature to read local data from the generated aggregation level on the local provider.
Case 2: Local data created by local query designer
Create an aggregation level on the generated local Composite Provider and build a Fox formula copying from the local provider to the global provider.
For future releases of the BPC we are exploring possibilities for the LoB user to create simple planning functions doing the copy directly from the BPC Web Admin Client.
On the other hand the central IT can provide the user with planning applications on (global) InfoCubes/aDSOs. As the goal of BPC Embedded is to enable the combination of IT driven global scenarios and LoB driven scenarios it is necessary to enable the exchange of data between the global and the local providers.
Data exchange between providers in usually done via planning functions. In this blog we show how such a planning function can be created in both cases. Note that the way how to proceed is slightly different in the two cases. At the end of the blog we give a small summary.
Case 1: Local data created by uploading local provider
We want to use a concrete example to explain the way how to proceed. Let us assume that we have a globally operating company that is doing a work force planning for the next year. The company is only interested in the effort-days per country. It does not need to know the numbers by employee. The planning for each country is done in decentralized teams (divisions) that are free in how they do their planning process as long as they deliver the effort-days in the end of the process.
For the local planner it is more convenient to do the planning on a more details level, knowing the employees, knowing when they are in vacation or parental leave, and knowing whether there will be a new employee and when this employee will start working operatively. Thus the planner wants to plan by country, employee, and month.
So we have a (simplified) ‘global’ planning InfoCube that contains the characteristic Country (TECNTRY) and the key figure TEED (Employee Days).
The local planner has created a local provider (by uploading a file) that contains a dimension for country, a dimension for employee and twelve key figures, one for each month. When uploading the local provider the system generates a default aggregation level on the local provider and the local dimensions.
This is an example of the uploaded file (Please keep in mind that the file upload is currently necessary for building the structure of the provider. The data in the local provider can later be entered for example in an Analysis for Office workbook):
And this is the information about the aggregation level you receive after uploading the file:
In the details for the local provider we also find the information about the local dimensions and the local names:
We do not have a CompositeProvider (or MultiProvider) that contains the target (global InfoProvider) and the source (local provider) of the planning function – which usually is prerequisite to create the desired aggregation level and planning function.
However we can use a Fox formula to do the trick. In Fox formulas we can read data from arbitrary aggregation levels. Thus we have the ability to create the Fox formula on the target InfoProvider and read the source data from the local provider via the new Fox feature. As we have all necessary information we can go ahead and create our Fox Formula on an aggregation level on the global plan InfoProvider:
* we have to declare the name of the aggregation level we want to read data from
INFOPROVIDER B100_0001. * we need various variables DATA CNTRY TYPE TECNTRY. DATA SUM TYPE I. DATA KYF TYPE KEYFIGURE_NAME.
* we need the name of the local country and employee DATA LOC_CNTRY TYPE '@3B100_LOC_PLAN@F0000'. DATA EMP TYPE '@3B100_LOC_PLAN@F0001'.
* country is not in the fields to be changed but we want to know which * is the country we are currently working on so we can make sure we read * data for the same country from the local provider CNTRY = OBJV(). LOC_CNTRY = CNTRY. * our target provider does only contain one key figure; * we go through all 12 key figures in the local provider and * add the data over all employees and all key figures; * the data from the (local) aggregation level is read in the following way:
* Name_of_ALVL.{Name_of_key_figure, local_country, local_employee}
FOREACH EMP IN B100_0001. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0002, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0003, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0004, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0005, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0006, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0007, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0008, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0009, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0010, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0011, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0012, LOC_CNTRY, EMP}. SUM = SUM + B100_0001.{@3B100_LOC_PLAN@F0013, LOC_CNTRY, EMP} . ENDFOR.
* this is an alternative way to total up the data:
*FOREACH KYF, EMP IN B100_0001.
* SUM = SUM + B100_0001.{KYF, LOC_CNTRY, EMP } .
*ENDFOR. {TEED} = SUM.
Now we can create a work book using a query on our local provider for data entry, a query on the global provider for displaying (and maybe changing) the required global plan data, and a button that runs our planning function for transmitting the data.
Case 2: Local data created by local query designer
The creation of a local provider we have described above already requires some modelling skills by the LoB user. With BPC10.1 and BW 7.50 we have introduced a concept where the LoB user can easily extend an existing data model (usually provided by IT department) in the BPC Web Admin local query designer.
Let us assume that again we are doing some headcount planning. The IT department has provided some infrastructure for this: a composite provider containing two aDSOs or InfoCubes (one for the actual data and one for the plan data).
We have a BPC Embedded environment in place and have set the following settings allowing the LoB users to create local queries.
The LoB planner uses the new BPC Web Admin client based local query designer to create a planning enabled query that shows the 2015 actual data in one (display-) column and the 2016 plan data in a planning enabled column. The data in the plan column will be visible throughout the entire BW (provided the user has the correct BW authorizations), this is why we call this data ‘global’.
Please note that it is not necessary to base the local query on an aggregation level – this will be done by the system implicitly.
New the user wants to start the planning but wants to have his ‘private’ version of the data that is not visible to user that are not working in the same area as our user. Users working in the same area share the same environment in our model. So the user creates a ‘local planning enabled measure’ that will hold to local plan data. This local data is only visible within the environment. As opposed to our first case the user does not have to know about the provider but simply adds the local measure to the query. The system will generate a corresponding provider automatically.
The new measure can now be used in the column. It makes sense to restrict it to the same year (and if necessary version) as the column for the global plan data.
Now we can use the local query in a workbook and enter data in the local plan column. Please note that you can also create a local planning enabled measure by simply extending an existing reporting query.
Above the query definition the system indicates the name of the provider the query in based on. This provider is an automatically generated local CompositeProvider that comprises the original (Composite) provider defined by IT and the local AINX holding the data of the local planning enabled measure.
We can use this provider to create an aggregation level and a planning function that copied the data from the local provider to the global planning provider.
Go to the transaction rsplan (or use the eclipse based BW modelling tools) and create a new aggregation level. The name of the local CompositeProvider will not be available from the value help in BW but you can copy and paste the name from the BPC Web Admin Client (in our case @3B477_PU2R9).
Now create a Fox formula in order to copy the data. We cannot use a copy function as a copy function cannot copy from one key figure to another. But the Fox formula is rather simple. In our case we just use the InfoProvider in the characteristics that are to be changed. In the value help for the key figure and for the provider you will find also the name of the local key figure and the name of the local provider – thus you do not even have to search for the names in the Web Admin Client.
The final Foxformula looks as follows:
{TEED,TE15_PL} = {'@3B477_PU2R9@F001','@3B477_61111140329583'} .
TEED is the technical name of the key figure in the global InfoProvider, TE15_PL is the InfoProvider.
@3B477_PU2R9@F001 is the generated name of the local planning enabled measure and @3B477_61111140329583 is the name of the generated local index (provider).
Finally create a filter and use the planning function with the filter, e.g. in an Analysis for Office workbook.
Summary and Outlook
Above we have described two cases of how local data can be generated and how the transfer is done:
Case 1: Local data created by uploading local provider
Create a Fox formula on the target InfoProvider and use the reference data feature to read local data from the generated aggregation level on the local provider.
Case 2: Local data created by local query designer
Create an aggregation level on the generated local Composite Provider and build a Fox formula copying from the local provider to the global provider.
For future releases of the BPC we are exploring possibilities for the LoB user to create simple planning functions doing the copy directly from the BPC Web Admin Client.
- SAP Managed Tags:
3 Comments
