A hit a day... Browser Vs. App

pic.twitter.com/hp2HD1XiwD
— BI Intelligence (@BIIntelligence) September 29, 2014

While there is a constant business and technical conflict for companies to chose between desktop browsers, mobile browsers and apps development; this graphic by BI Intelligence provides some insights.

I think companies should also look at their business model and determine how much to invest in the varied technologies. In this case, the business model of Amazon and ebay is different form other companies like Best Buy. While a app is very helpful and easy for bidding and checking bids (ebay), and to pick an item from the shelf and check if it is available for a better price (Amazon); a browser based site is important for retailers who want you to visit the stores and experience the product.

You might still want to visit Walmart to check out the item and open ebay and Amazon app to check for a better price.

Looks like its a right move for browsers to provide an easy switch between browser and mobile versions (Safari).

Row Level Security Application in Crystal Reports using Business Views

This is a particular way of applying row-level security using business views for Crystal Reports. This security is applied directly at the prompt level. A restricted user would be provided with selections that he has access to, and hence would be able to run the report for data that he is allowed to only. In this scenario we would talk about user restricted to particular plants. This document is valid for security configuration for Crystal Reports in the BO 3x AND BO 4+ world.

There are a couple of components that work together for the proper execution and application of row level security in this manner. They are:

a.            Restricted group in BOE

b.            Row level security table

c.            Security views for each LOV

d.            Command table in Business View data foundation

e.            Filter in Business View data foundation

f.             Rights application on the filters

g.            LOV based on the command table

h.            Flow of events in LOV restriction

a.      Restricted group in BOE

This BOE group is required to apply the restricted LOV to the people who would be restricted. All users who need a restricted level should be part of this group; users who are not part of this group will not be restricted. There should be an entry in the row level security table for the restriction to work properly.

Important: If an entry is not present in the security table and the user is in the restricted group, the user will get an empty list in the prompt.

Where located	BOE CMS
How created	Using CMS, created by BOE administrator
Specification	None
Contents	User ids as members, who need restricted access

b.      Row level security table

This table is the heart of the configuration. The actual access is defined in this table. The table contains information about the user and his area (departments etc.) of access, and the actual access (value). It contains the application/departments (FIN – Finance, ITS – Information Technology etc.) information, so that a single table can maintain for the whole enterprise environment. The restricted LOV is governed by the data available in the value column. A restricted user can only choose prompt values that are available in the value column in this table.

It has some other columns that are specifically used for administrative purposes.

Table Structure description:

Column	Description	Possible values
BI_AREA	Contains the application/department name, this is referenced in the filter defined at the business view level	Based on the enterprise FIN, ITS, PAP…
PREFERENCE	Area / LOV on which restriction is applied	Each application/department can have multiple areas of restriction. For instance Logistics user can be restricted to plant/warehouse or division etc…
VALUE	Actual area id or code by which the area could be referenced. For a plant preference the value would be plant id.	Plant id, division id…
NT_USERID	The network id of the user who needs to be restricted. This should be equivalent to the user id used in BOE.	User id

This table can be maintained by the BOE administrator and Application Support team.

Where located	Any central database accessible from other reporting databases, in our case we have created a table named BO_ROW_SECURITY
How created	Using SQL, created by DBA
Specification	History tracking can be made available using status fields in the table
Contents	Row level security definition

c.      Security views for LOV

This view is created to increase the performance of the LOV. Let us take the example of plant LOV. The view creates a list of all the possible combinations of plants in the database (from a plant dimension table, in this case DIM_PLANT) and the entries available in the row level security table. This is required so that the same LOV (view) could be used for users who do not have any restriction as well. The list would have rows with NULL in nt_userid which is required to create the full list of plants from DIM_PLANT table for users who does not have any restrictions. The view also restricts the list to a particular preference, in this case to ‘plant’.

The query also takes care of hierarchical security. For instance if plants roll-up to divisions, and a user is restricted to a division (multiple plants) then this query will create the list at the plant level including all the plants in that division.

SQL text of view (SEC_PLANT):

select distinct pl.PLANT_ID as Plant_Id,    

  ltrim(rtrim(pl.DESCRIPTION)) as Plant_Desc,    

  ltrim(rtrim(pl.DESCRIPTION)) + case ltrim(rtrim(pl.DESCRIPTION)) when '' then '' else ' - ' end + ltrim(rtrim(pl.PLANT_ID)) as                 Plant_Id_Desc, 

  ltrim(rtrim(pl.PLANT_ID)) + case ltrim(rtrim(pl.DESCRIPTION)) when '' then '' else ' - ' + ltrim(rtrim(pl.DESCRIPTION)) end as                 Plant_Desc_Id,    

  se.bi_area, se.preference, se.nt_userid    

from DIM_PLANT pl left outer join

                (SELECT [BI_AREA],'plant' as [PREFERENCE],[VALUE] as div_value,pl.plant_id as [VALUE],[NT_USERID]

                  FROM [BO_ROW_SECURITY] bse, DIM_PLANT pl

                  where ltrim(rtrim(bse.value)) = ltrim(rtrim(pl.business_unit_id))

                  and bse.preference = 'division'

                union all

                SELECT [BI_AREA],[PREFERENCE],[VALUE] as div_value,[VALUE],[NT_USERID]

                  FROM [BO_ROW_SECURITY] bse

                  where bse.preference = 'plant'

                  ) se    

  on se.value = pl.PLANT_ID and se.preference = 'plant'

Let’s assume the division and plants are defined in the system as follows:

Then the output of the view would look like (SEC_PLANT):

Let’s discuss a few important pointers on the above table:

·         Lines 2 and 3 are automatically generated for joe’s access to all the plants under division CA001

·         Lines 6 and 7 are automatically generated for tom’s access to all the plants under division US002

·         Lines 8 to 13 are generated for all other users that have no restrictions to any plant or division

·         Columns GL_Plant_Id_Desc and GL_Plant_Desc_Id are generated for different flavors of the LOV as per the group or report preference/requirement

·         Columns GL_Plant_Desc, GL_Plant_Id_Desc and GL_Plant_Desc_Id are used for the LOV display. For any selection of these values, the plant id from column GL_Plant_Id is passed onto the report

Where located	Any database accessible globally
How created	Using SQL, created by DBA
Contents	Row level security definition combined with LOV content from master tables

d.      Command table in Business View data foundation

Command table are required when it is not possible to get the required data set directly from database tables. The command table for security application is a straight forward select and the view SEC_PLANT could have been directly used, but a command table was created to keep room for future developments/changes that might affect the view. The output of command table, Plant is similar to a select * output of the sec_plant view.

SQL text of command table (BV GL_Plant):

SELECT [GL_Plant_Id]

      ,[GL_Plant_Desc]

      ,[GL_Plant_Id_Desc]

      ,[GL_Plant_Desc_Id]

      ,[bi_area]

      ,[preference]

      ,[nt_userid]

  FROM [SEC_PLANT]

order by 2

Output of command table (BV GL_Plant) will be similar to the SEC_PLANT view:

Where located	Business View data foundation
How created	Using SQL, created by BV administrator
Specification	Can be accessed throughout
Contents	Row level security definition from security view

e.      Filter in Business View data foundation

By this time we have the full data set that is required to apply a restriction on an LOV. The filter defined in business view actually applies the restriction on the data set. For a particular user this filter is either ON of OFF which is governed by his/her existence in the BOE Restricted Group. The filter uses environment variable, CurrentCEUserName to capture the user id from runtime and joins it with the dataset from the view to return rows that correspond to the particular user. The filter does not get applied for a user who is not restricted and hence sees the distinct values from the view; this distinct list matches the values in the master table.

As we have different LOVs for different preferences (plant, division…), we have to create different filters. Each filter uses the preferences part to determine the area it is running for.

Filter application in Business View Manager:

Where located	Business View data foundation
How created	Using Business View Manager, created by BV administrator
Specification	Can be accessed throughout
Contents	Row level security application

f.        Rights application on the filters

This is a very important step as it dictates when the filter is going to be applied. Rights applied to the filter can be at the user or a group level. If applied to a group, the filter is effective whenever a user in that group references it. Rights can be applied on a filter by right-clicking and opening ‘Edit Rights’. In our case, restriction rights are applied at the group level and not at individual user level.

Rights application to filter:

Where located	Business View data foundation
How created	Using Business View Manager, applied by BV administrator. The rights has to be manually applied to the filters in the migrated environment when an LOV is migrated for the first time
Specification	Can be accessed throughout
Contents	Row level security application at BOE group level

g.      LOV based on the command table

All LOVs that are created based on the table (command or normal) having reference in the filters, will have restrictive characteristics. The restriction application is not guided by the LOV, but purely is an effect of filter and the rights application of the filter. The LOV can reference lesser columns than there are in the actual table.

On a deeper level, there is actually a query that runs on the database whenever a LOV is called/ referenced in the report. Query generated in case when filter is applied:

SELECT                distinct [GL_Plant_Id], [GL_Plant_Desc], [GL_Plant_Id_Desc], [GL_Plant_Desc_Id]

  FROM [DM_CORPORATE].[dbo].[SEC_PLANT]

Where   [bi_area] = ‘LIS’

And        [preference] = ‘plant’

And        [nt_userid] = <runtime value of CurrentCEUserName>

order by 2

Query generated in case when filter is not applied:

SELECT                distinct [GL_Plant_Id], [GL_Plant_Desc], [GL_Plant_Id_Desc], [GL_Plant_Desc_Id]

  FROM [DM_CORPORATE].[dbo].[SEC_PLANT]

order by 2

Where located	Business View data foundation
How created	Using Business View Manager, created by BV administrator
Specification	Can be accessed throughout
Contents	Row level security application at LOV

h.      Flow of events in LOV restriction

 

BO - Hyperlinks in a 'Drill' Report

Problem Statement: 

A master detail report scenario where the format of detail report does not match that of master and the master report is a drill report.

Solution:

Building a hyperlink in the hierarchal report is little bit complex when compared to a regular hyperlink report since at any point we would not know at which level of hierarchy you are, when you click on the hyperlink.

We would use the ‘Island Resort Marketing’ for our example purposes. ‘Customer Hierarchy’ would be a good pick for the exercise.

Since the master report is a drill report, a user can run the detail report at any level of hierarchy; the detail report then needs to be run at the correct level with the proper input parameters.

Let’s create the sample master and detail report. Here the master report is revenue by country with a drill on the customer hierarchy. The detail report is revenue by service line that is called when the Link is clicked on the master report.

The trick here is that the ‘detail report’ should run fine and in perspective to whichever hierarchy level the ‘link’ is clicked. The detail report is built with each hierarchy level as an optional prompt, in addition to any other required prompt such as time etc.

We would exploit the following properties of drill functionality for making this work:

·         There can be only one value of a hierarchy level ‘selected’ at any given point in time

·         The one selected value can either be a discrete dimension value or ‘All’

·         A combination of all levels of any hierarchy is always unique

Keeping these in mind there are a few conditions we would need to take care of:

·         There can be a few skip level of hierarchies as a result of analysis

This can be taken care of because of the property that each set of hierarchy level is always a unique set. So if a level is ‘All’ we simply won’t pass the value of that level to the detail report.

·         The detail report could be called from any level of the master report

Like above, we would consider all unselected lower levels of the hierarchy as ‘All’.

Now, there is no in built function available in WebI that can decipher the hierarchy level of the selected value. There is a function called ‘drillfilters()’ but that skips the vales when a level is selected as ‘All’ and hence cannot be used effectively. By looking at the value of this function we would not be able to determine which level is skipped.

So, to effectively design a hyperlink to the detail report we have to know the following at any given point in time:

·         Selected level of hierarchy – to determine what levels to skip

·         Selected value of hierarchy

Enough of describing the problem let us now work on the solution. I would like to put a solution statement here so that we start looking at the problem from a different angle.

Solution Statement:

Create a hyperlink that will pass all the relevant information to run the detail report from a master report with drill.

Create a variable in the master report for each of the hierarchy level. These variables will equate at each display row level and would be used to build the hyperlink. So in our case, we will have the following variables:

v_CheckCountry   = if (Max (Count ([Country of origin])) = 1; [Country of origin]; “”)

v_CheckRegion     = if (Max (Count ([Region])) = 1; [Region]; “”)

v_CheckCity          = if (Max (Count ([City])) = 1; [City]; “”)

v_CheckCust         = if (Max (Count ([Customer])) = 1; [Customer]; “”)

count() function determines if that dimension is at the lowest level in the hierarchy at any point in time. If the dimension is at the lowest level then it returns 1. Or else display nothing which equates to passing a NULL to the detail report for the dimension.

max() function is applied to make sure the data row for a dimension is at the lowest level.

This is how the variables would work in runtime with different set of selected drill values. On top is the sample data (US only). On left in the If column is the selection scenarios. Drill dimension is the dimension that a use would see in the report block during normal drill navigation. Rest of the columns displays the output of the v_function functions. The v_function will not be required to be available in the report layout, but will provide the values for the hyperlink at each row level. These four values from the v_function will be passed as prompts to the detail report.

Explanation of a few scenarios based on the sample data. You would be able to guess the rest.

1.       When nothing is selected; all v_CheckXXX will return more than 1 row as count except the starting level of dimension. So when you click on the link by ‘US’ the prompt values passed to the detail report would be:

Prompt	Value
Country	US
Region	NULL
City	NULL
Cust	NULL

2.       When you drill down on ‘US’, the regions would be visible. So when you click on the link by ‘East Coast’ the prompt values passed to the detail report would be:

Prompt	Value
Country	US
Region	East Coast
City	NULL
Cust	NULL

And when you click on the link by ‘Mid West’ the prompt values passed to the detail report would be:

Prompt	Value
Country	US
Region	Mid West
City	Chicago
Cust	Baker

In this case you would say that the City and Customer values are passed to the detail report unnecessarily, but they are passes as there is only one city and one customer for ‘Mid West’ and hence it would not affect the data fetched in the detail report.

3.       When you drill down on ‘East Coast’, the city would be visible. So when you click on the link by ‘Washington D.C.’ the prompt values passed to the detail report would be:

Prompt	Value
Country	US
Region	East Coast
City	Washington D.C.
Cust	Swenson

4.       At this step we drill down to cities for ‘New York City’. Let’s skip to #5

5.       Now let’s select ‘All City’ in the City drill filter drop down. This will show all the cities in ‘East Coast’. So when you click on the link by ‘Brendt’ the prompt values passed to the detail report would be:

Prompt	Value
Country	US
Region	East Coast
City	New York City
Cust	Brendt

6.       Let’s skip this one; as by now you would have got the idea.

The observation here is that irrespective of what is displayed, the link is always going to pass the correct prompt combination for any selected drill dimension at any level of the drill.

Now let’s create the hyperlink. I personally like the method of directly creating them as functions:

v_Hyperlink_To_Detail ="<a "+[opendoc with TargetRefresh]+ "&sDocName=Detail Report

&lsS Enter Country:=" + URLEncode([v_ CheckCountry]) + "

&lsS Enter Region:=" + URLEncode([v_ CheckRegion]) + "

&lsS Enter City:=" + URLEncode([v_ CheckCity]) + "

&lsS Enter Customer:=" + URLEncode([v_ CheckCust]) + "' > " + "Link" + "</a>"

Add this variable v_Hyperlink_To_Detail in the report block of master report and you would be able to now drill to the relevant detail report with the correct data based on any combination of drill level or drill filters applied.

That’s all folks… Happy linking…