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…

BO 4.0 - New Features in Information Design Tool

You have upgraded to BO 4.0; now what…

Here is a compilation of new features available in Information Design Tool. I would be using the ‘Island Resort Marketing’ universe that has already been converted to the BO 4.0 version.

DATA FOUNDATION

Right Click – New Derived Tables

Now you can create a derived table just with a right click. This helps when you want to create derived tables on large tables. Simply create the table with a right click and apply selections, filters etc.

Right Click – Replace By

A great feature to quickly disconnect your database tables from the structure and replace with an alias or to even to correct your or the DBAs mistakeJ. You can now very easily replace a table with new ‘database table’.

The new table maintains the joins if the same keys/columns are available. In this case only the ‘region_id’ field got mapped when we tried to replace ‘Region’ table with the ‘Region_SLine’.

Tables Merge

This would be my most used feature. In many cases, there is always an urge/need to combine 2 or more tables to reduce clutter or to apply business logic that is achieved by joining the multiple tables. This feature makes life easier by creating a derived table maintaining the proper joins, filters, etc. that can then be modifies as required. Let’s see this in action.

Select 2 or more tables by pressing the ‘Ctrl’ key while selecting. Right-click on one of the selected table and click ‘Merge’.

After you provide a name and decide if you want to keep the original tables, you would have a new derived table that is automatically created based on the joins defined earlier.

A word of caution, the objects defined on the old tables will have to be touched up and re-verified.

Change Table Behavior

There are a couple of options to change the behavior of a table. Right click menu provides options to ‘Edit’ table names, field types and even length. This option also lets you hide/unhide fields.

You can even change the database connection and owners by selecting ‘Change Qualifier/Owner’ option. This can be applied to multiple tables at once.

Select Related Tables

I like this feature, because it highlights the tables linked/related to the subject table. With multiple selections you would be able to see all the relations in complex universes.

First pass of related tables for City

Second pass of related table for City (observe the highlighted tables)

Insert Calculated Columns

This is a very simple way to create calculated fields directly in the ‘data foundation’ which can be reference as a generic field in the ‘business view layer’. To create one simple right click on the table and select ‘Insert Calculated Column…’.In our example, we will create a ‘price increase’ field in the Service table with a 5% price increase.

A quick view at the ‘table values’ will show seamless integration of the resultant field.

Search

One of the long awaited search feature is now available. You can now search by criteria, by typing, and would be amazed to see how the tool highlights the search results.

BUSINESS VIEW

Object Properties

A lot of new and exciting addition to the object properties is now available. One of the best is the object qualification to make it ‘Deprecated’

Check Integrity Panel

Check Integrity Panel is a huge development over the older version. The panel lets you selectively run integrity checks.

The ‘Check Integrity Problems’ option under ‘Windows’ provides a quick way to check on the issues in the universe. You can decide to clear up the issue log as well.

You can also set-up the severity level of the various checks that you would like to perform in the environment. This is accessible under preferences.

Security Editor

Security Editor is a several leap forward. It provides a nice interface to apply security both at the Data Foundation and the Business View layers.

Data Security Profile allows to apply security at:

·         Connection – ability to define replacement connection

·         Controls – execution time, rows returns etc.

·         SQL – use of sub queries, multiple queries etc.

·         Rows – classic row level security using ‘where’ clause

·         Table – ability to define replacement table

Business Security Profile allows to apply security at:

·         Create Query  – ability to grant or restrict access to ‘views’ defined in the business view layer and ability to restrict objects in the business view layer

·         Display Data – ability to grant or restrict access to data from different objects defined in the business view

·         Filters – ability to create filters on the objects and selectively apply them to the profile

Others

Define Optional Prompt in the universe itself.

Define Static LOVs in the universe.

Multi-select from Hierarchy Prompt

One of the other greatest features that are now available is the ability to select prompt values from multiple levels of a hierarchy. We will do this one step by step using the ‘Island Resort Marketing’ universe.

1.       A Navigation Path for Region and City is already available in the ‘Customer Hierarchy’

2.       In Business View, create a LOV based on ‘List of values based on Business Layer Objects’

3.       Select ‘List of values based on a custom hierarchy’

4.       Add dimension Region and City

5.       The preview would show something like this

6.       Once the LOV is created, select City object and change the LOV to ‘Region_City’

7.       Now open WebI and create a query using the City object as a prompt with ‘In List’ operand

8.       And voila, you would be able to select region or city for this prompt

9.       Select multiple values from each level and see the magic…

Table without filters                                        Table with above selection

That’s all folks… Hope you enjoyed the trip…

BO 4.0 UNV to UNX Conversion

So, now you have BO 4.0 in the environment, and want to have all your universes converted to UNX… how do you do it…

1.       Run ‘Information Design Tool’

2.       Open Session to your CMS Repository

This is to make sure that you have the connection you would like to use.

3.       Create a new Project

4.       Provide a logical name and location

This will be the placeholder for the new Data Foundation and Business View Layer

5.       Now the fun starts. From File menu select ‘Convert .unv Universe’

6.       You can select a universe file from your desktop or get it from the repository. To get a universe from the repository, select the server button.

7.       Once you connect to the repository, you can select a universe. We would work with the classic eFashion universe.

8.       Once you select the universe to convert, select the ‘destination repository folder’ and ‘local project folder’. In this case we have the destination the same as source. If you are planning to save locally, you would be able to save the new unx file for all users.

9.       Click OK, and the engine starts. The time taken to convert a universe is dependent on the size of the universe.

10.   And the universe is now converted to unx.

11.   After a few clicks of ’close’ and ‘ok’, you will have the eFashion universe in unx format.

12.   And that is it…

13.   Based on the selection of converting @prompts earlier, you may have additional LOV listed in the Business View layer

14.   Happy IDTing…

SBO Designer - Enable use of Synonyms in Universe

My personal encounter with synonyms in universe was not a pleasant one. It took long hours and ultimately the ‘trick in my bag’ for it to work.

Environment: SQL Server 2005; BO XI R3 SP1 FP3

Scenario: A Universe needs to be built that will source data from large tables in different databases.

Problem Statement: Data in deferent databases can be accessed using linked servers (if the databases are physically in different machines) or direct views (if the databases are in the same machine). In our case, the databases were in the same machine. ‘Select *’ views were created in the target database, but that was not a viable solution as the tables in the source database were huge. Any query on the views even with filtering conditions might result in full table scans depending on the tables involved.

The next option was to use synonyms that would not do a full table scan of the source tables whenever they are accessed. The problem is that synonyms cannot be accessed in the “Table Browser” with the default strategies.

Pic 1: BO Universe "Table Browser" does not list synonyms

SAP was contacted in this regard, and they said that synonyms cannot be accessed in the universes both in case of Oracle and SQL Server databases at it current state of patch level.

The Solution: The only other solution to achieve this was to use external strategies for tables that can be set in the Universe parameters.

Pic 2: Setting up universe to use 'External Strategies'

“Table Browser” within the universe typically runs a metadata query in the selected database based on the database type. This query returns the table and column definitions in a format usable by the “Table Browser”.

When the strategy is changed to use “External Stratgy”, Designer looks for a particular file for the definition of the query that needs to be run. This file is available at <install_path>\BusinessObjects Enterprise 12.0\win32_x86\dataAccess\connectionServer\odbc\sqlsrv.stg and can be changed to suit the purpose. To check the file location in your environment, open ‘Universe Parameters’, run a connection test, and click on the ‘Details’ button on the ‘connection success’ window. Scroll down to check the path for the strategy file.

Pic 3: Determine location of strategy file

Note: We are discussing an example of SQL Server 2005 accessed over ODBC, for other database and connection type, the file locations would be different.

By now you would have guessed that this file would have the metadata query required for generating the list of tables and columns, and you are correct. The SQL query is available at the following tag:

                <Strategy Name="Synonyms">

                                <Type>STRUCT</Type>

                                <SQL>

The aim now is to update the SQL query that will also bring in the synonym details. The problem here is that the column definitions of synonyms are not stored in the system tables of a SQL Server database, and hence would not show up in the “Table Browser” window. To make it work, a query needs to be written that would extract the base_object_name from the synonyms and query the parent database for the actual table definition. This is made possible with the following query.

____________________________________________________________________

/******SYNONYM PART*****/

select          distinct

isTable.table_catalog,'|',

                        isTable.table_schema,'|',

                        Psysobjects.name,'|',

                        Psyscolumns.name,'|',

                        case

                                                when Psyscolumns.xtype in (127,104,106,62,56,60,108,59,52,122,48) then 'N'

                                                when Psyscolumns.xtype in (175,167) then 'C'

                                                when Psyscolumns.xtype in (61,58) then 'D'

                                                when Psyscolumns.xtype in (35) then 'T'

                                                when Psyscolumns.xtype in (173,34,165,36,189,231,239,99) then 'B'

                                                else 'C'

                                                end,'|',

                        'N','|'

from             (select * from rakshit.dbo.sysobjects) Psysobjects,                            /*source table database of the synonym*/

                        (select * from rakshit.dbo.syscolumns) Psyscolumns,

                        (select * from rakshit.INFORMATION_SCHEMA.TABLES) isTable,

                        sys.synonyms isSyn

where         (Psysobjects.name = reverse(substring(reverse(isSyn.base_object_name),2, charindex('[',reverse(isSyn.base_object_name),1)-2)))

and                (Psyscolumns.id = Psysobjects.id)

and                (isTable.table_name = isSyn.name)

UNION

/******TABLE PART*****/

SELECT DISTINCT

                       isTable.table_catalog,'|',

                       isTable.table_schema,'|',

                       dbo.sysobjects.name,'|',

                       dbo.syscolumns.name,'|',

                       case

                                                when dbo.syscolumns.xtype in (127,104,106,62,56,60,108,59,52,122,48) then 'N'

                                                when dbo.syscolumns.xtype in (175,167) then 'C'

                                                when dbo.syscolumns.xtype in (61,58) then 'D'

                                                when dbo.syscolumns.xtype in (35) then 'T'

                                                when dbo.syscolumns.xtype in (173,34,165,36,189,231,239,99) then 'B'

                                                else 'C'

                                                end,'|',

                       'N','|'

FROM          dbo.sysusers,

                       dbo.sysobjects,

                       dbo.syscolumns,

                       INFORMATION_SCHEMA.TABLES isTable

____________________________________________________________________

When this query is run, the following output includes the table and column definition in the format usable by the “Table Browser”.

Pic 4: Extract for "Table Browser"

After changing the query with the above in the strategy file and saving it, the synonyms should be available for use in the designer. BUT, in most cases this query does not work fine when used from the universe and would result in no results in the “Table Browser”. The changed query does not run fine because there are multiple databases involved and access restrictions/grants do not work properly when the query is called from within BO Designer.

This is where a trick from ‘my bag of tricks’ comes to play. At the end of the day, BO Designer just need data in the above format to populate the “Table Browser”, it does not care where the data is coming from.

The Trick: Create a database view with the metadata query and use that as the source in the strategy file. As we are creating a view for the metadata query, it will be always up to date with new tables or synonyms. The only caution is that the view would require proper column names. The SQL section in the strategy file would look like:

                <Strategy Name="Synonyms">

                                <Type>STRUCT</Type>

                                <SQL>SELECT table_catalog, delim1,

table_schema, delim2,

tab_syn_name, delim3,                                                     

col_name, delim4,

col_type, delim5,

value1, delim6

FROM     DEV_D.dbo.UNIVERSE_METADATA

ORDER BY 1, 3, 5

</SQL>

                </Strategy>

Note that the order by clause is very important to make sure that the table and columns are grouped properly.

And, now you have synonyms in your universe. Enjoy your cruise…