Row-wise initialization: Variables Extension

Row-wise initialization:
It is used to retrieve and initialize a list of values to the non-system session variable.
For example:
If a user belongs to more than one region then to allow the user to see data from those regions, we will have to use Row-wise initialization to pass list of values of region (say east and central) to a non-system session variable instead of passing a single value.
Remember that a non-system session variable can only hold single value Either East or west or central but can not hold multiple values like an array , that is why we make use of Row-wise initialization.


Demonstration:

• Suppose we have a table called USER_MULTI_GROUP which contains sales representative and their corresponding cities as shown below. Note that a Sales representative called Marc Belongs to 2 cities.

• Open the repository in online mode, Import this table in physical layer. Let us set key on this table. Here City and salesrep column together can be a key. make these columns key column as follows:

• Now create a new Business model called ROW_WISE_BM in BMM layer and drag and drop USE_MULTI_GROUP table from physical layer to BMM. Since we have only one table under ROW_WISE_BM, we must duplicate the table  USE_MULTI_GROUP and make it a fact table. Create a join between these two. Now drag and drop ROW_WISE_BM subject area in Presentation layer. Delete one table and keep only one as we don't require 2 tables in Presentation layer as shown below:

     

• Now create User Marc and Group ROW_WISE_GROUP in console. Also create an application role called "ROW_WISE_ROLE" in enterprise manager. Assign ROW_WISE_GROUP to ROW_WISE_ROLE.(How to do this is written in Variables part 2 blog:System and Non-system Session variables)

 User:

Group:
   

Application role:

     

• Synchronize this role in repository. Manage-->Identity-->Select BIRepository-->Action--> Synchronize Application roles. Go to Application Role tab and check if all Users like Marc, John are under it.
• Now we need to create a new Initialization block as shown below:

               

• ok. check in changes and save.
• Now we need to create data filter. Go to manage-->identity-->highligh BIRepository-->Application role tab-->check out ROW_WISE_ROLE -->Permissions-->Data filter tab-->add-->select USER_MULTI_GROUP table --> select --> select the newly added line and edit expression-->add filter of USER_MGROUP instea of non-system session variable used in Variables part2 as shown below:

    click on ok-->ok--ok--> Check in changes and save.

• Now log into Analytics using Administrator privileges, developer a sample report in ROW_WISE_BM using all columns. Save the report as "Row wise  data security report" as shown below:

• Now login using MARC user, go to catalog and open the saved report "Row wise  data security report". MARC can see only his data for 2 cities since he belongs to more than 1 cities as shown below:

This is how we use row-wise initialization to pass multiple values to initialization block for retrieving more than one value unlike non-system session variable.