Irfan's World

February 27, 2011

What’s new in Business Objects 3.1 SP2

In Business Objects 3.1 SP2, you will see some enhancements in the following products:

        1. Translation Manager
        2. Live Office
        3. Web Intelligence
        4. Query as a Web Service
        5. Universe Designer
        6. Report Conversion Tool

1) Translation Manager:

      In universes:  You can now translate custom hierarchy metadata.
     In Web Intelligence, you can translate:
                Prompts
                Report names
                Query or data provider names
                Local variable names
                Alerter names and descriptions
                Content in a Web Intelligence formula
                     Formats. (Ex : You can now create and save multiple custom formats in a document.)

2) Live Office

In this release, Live Office provides new functionality as detailed below:

     Modify Object Refresh Order
                  (Ex : Live Office objects can now be refreshed in the user specified order. When there are multiple objects in a document, the user can modify the refresh order of the objects. This allows the data returned by an object to act as the prompt or parameter 
                  for subsequent object.)

     Queries: Using an object as a filter.
                              (Ex: When inserting a query into your Live Office enabled document, you can now filter the query results with an object.)

3) Web Intelligence

       Forward compatibility
               
Web Intelligence documents created using SAP BusinessObjects XI 3.1 SP2 cannot be opened in SAP BusinessObjects XI 3.1.

       Backward compatibility
               
Documents created using SAP BusinessObjects XI 3.1 can be opened in SAP BusinessObjects XI 3.1 SP2.

       Input controls
               Input controls provide a convenient way of filtering and analyzing report data. Some of them are list boxes, radio buttons and sliders. These can be assigned to report elements(blocks, section headers) Selecting values in an input control filters the values in the
              
associated report element. Also Tables and charts can themselves be defined as input controls. Selecting values in the table or chart filters the values in the associated report element.  For example, a variable in a formula can be assigned to a slider control.
               Changing the variable with the control allows a user to examine the effects of different variable values on the formula result.

image

 

     BI services

          BI services are web services that publish Web Intelligence content over the web. They are defined and accessed using the standard WSDL and SOAP protocols.

     More translatable content in multilingual reports in WebI

               The following report elements can now appear translated in multilingual Web Intelligence reports:

                    Query name
                    Report name
                    Free cell content
                    Local variable name
                    Alert name, description and alert message
                    In report’s structure, object name
                    Text in the new GetLocalized() function

    New locale- and localization-related functions

          The Web Intelligence formula language contains three new functions and they are: 

          GetPreferredViewingLocale returns the user’s Preferred Viewing Locale.
          GetDominantPreferredViewingLocale returns the dominant locale.
          GetLocalized returns the translation of report text in the user’s
Preferred Viewing Locale. It also allows report creators to provide notes for translators to aid them in translating text in Web Intelligence reports.

image

    Query on Query 

          Query on query allows users to define a query condition based on an object resulting from another query.

   PDP-API Exposition

         
Personal data provider(PDP) is a feature that enables Web Intelligence users to build documents on another data source as the Universe. The following are the PDP’s which are supported:
                        Excel
                        Text files – *.txt (usually Tab delimiter)
                        Formatted text files – *.prn (Space delimiter)
                        ASCII files – *.asc (Tab delimiter)

   Fold and Unfold

This feature will allow Web Intelligence Reports Viewers/Editors to be able to Fold/Collapse Information in sections.

image

    Query Filter on QaaWS and LiveOffice

         This feature will allow users to design query prompts, using universe objects as comparison terms. The aim is to extend the prompt feature scope, to the level of Web Intelligence query panel, so QaaWS users are able to easily design and consume efficient queries, as  
         they are available with WebIntelligence.

   Rich Client PDP – Web Intelligence Web Services
             Generic Web Services Plug-in
             Business Intelligence Services Plug-in

   Customize Web Intelligence functions with Extension Points
             You can now use calculation extensions to customize the Web Intelligence reporting functions so that they follow any logic you require.

4) Query as Web Service

      Business Intelligence (BI) content is usually bound to a specific user interface of BI tools. Query as a Web Service changes lets BI content be delivered as a query to any user interface that can process Web Services.

5) Designer

       New SQL parameter SMART_AGGREGATE allows use of most detailed aggregate tables
       New @Variable settings for retrieving reports and displaying information in different locales. The @Variable function can now be used to define Language (Locale) settings using the locales variables like GetPreferredViewingLocale, GetDominantPreferredViewingLocale
           and GetLocalized

        @Prompt editor
                    This feature applies to both relational and OLAP universes. It aims at providing users with a User Interface guided workflow to build the prompt definition and correctly enter List Of Values (LOV) and default values in that definition.

       MDX dictionary for OLAP universes
                  
The object definition editor includes a dictionary of MDX elements that can be inserted in the object. The MDX elements are dependent on the data source. The available parameters are stored in the relevant parameters (.PRM) file.

       New OLAP parameters and controls at universe generation time
                 
This feature offers users some options to control and configure the universe generation to obtain the expected universe and relieve users from having to remove unwanted objects in the universe after the generation. Those options apply also at update (Refresh 
                 
Structure):

                  Replace prefix.
                  Generate Technical Name (Tech_Name) as detail.
                  Rename Level00 to All
                  Generate level 00
                  Set measures aggregation to delegated

6) Report Conversion Tool

      Query on Query filter is fully converted
                
Reports that contain the Query on Query feature will be fully converted.

      Fold/Unfold feature is partially converted
                
Reports with sections that contain the Fold/Unfold feature are fully converted.
                      Reports with tables, crosstabs, and breaks that contain the Fold/Unfold feature are partially converted.

      Converting the data in reports to text format
               
Report Conversion Tool provides an option with which a user can convert Desktop Intelligence document data format into text format.

Most of the information in this post is gathered from Business Objects User Conference that I attended.  There are many other products from Business Objects XI 3.1 suite that has been enhanced but not listed in this post.

February 18, 2011

How to display a database column values in a single cell in Web Intelligence Rich Client ?

In this post, I would like to show you how to display an object value in a single cell separated by a delimiter in a WebI report.

I have a table name called Account in Xtreme database and it has the following values,  the requirement is to display all the account names in a single cell in the report.

image

 

This requirement can be fulfilled if we have access to Universe Designer in which three following objects needs to be created with the syntax shown, I am using SQL Server as my database.

    Column Value
    Delimiter
    Actual Object Name – Single Line

1) Create a ColumnValue object as below:

SNAGHTMLae414a1

2) Create a Delimiter object as below:

image

3) Create a Account Name object as below:

This step needs two things, make sure the definition of the object looks like below plus the qualification should be a measure with projection as SUM

SNAGHTMLb151b37

 

Objects qualification and projection

image

All the ground work is done, now I will use the object in a sample report.  Let’s see what it returns ?

image

The report output is shown below…. voila !!!

image

That is all for today.Wave

February 17, 2011

How to get all indexes of table(s) in SQL Server 2008?

Filed under: SQL Scripts — Tags: , , , , — Irfan @ 11:20 pm

1) To get the indexes from a given table and schema.

Use the following script to get all the indexes for a given table and schema name.

DECLARE @TableName SYSNAME,
      
@SchemaName
SYSNAME

SET @TableName = 'Employee'
SET @SchemaName =
'HumanResources'
SELECT
  
i.OBJECT_ID
,
  
i.index_id
,
  
i.name IndexName
,
  
OBJECT_NAME(i.OBJECT_ID) TableName
,
  
c.name ColumnName
,
  
i.index_id
,
  
df.name FileGroupName
,
  
i.fill_factor
,
  
i.is_unique
,
  
i.type_desc
,
  
ic.is_included_column
,
  
schema_name(o.schema_id)
SchemaName
FROM
sys.indexes i
INNER JOIN
sys.index_columns ic
  
ON      i.index_id = ic.index_id
AND
          
i.OBJECT_ID = ic.
OBJECT_ID
INNER JOIN
sys.columns c
  
ON      ic.column_id = c.column_id
AND
          
i.OBJECT_ID = c.
OBJECT_ID
INNER JOIN
sys.database_files df
  
ON i.data_space_id =
df.data_space_id
INNER JOIN
sys.objects o
  
ON i.OBJECT_ID = o.
OBJECT_ID
WHERE
  
OBJECT_NAME(i.OBJECT_ID) = @TableName
AND
  
schema_name(o.schema_id) = @SchemaName
AND
  
i.index_id <>
1
ORDER BY
i.index_id

Following is the result when I run the above query for HumanResources Schema.

image

   2) To get all the indexes for a complete schema you can comment the line that checks for the TableName in the WHERE clause.

   DECLARE -- @TableName sysname,
      
@SchemaName
SYSNAME

   -- SET @TableName = 'customer'
   SET @SchemaName =
'sales'
   SELECT
     
i.OBJECT_ID
,
     
i.index_id
,
     
i.name IndexName
,
    
OBJECT_NAME(i.OBJECT_ID) TableName
,
    
c.name ColumnName
,
    
i.index_id
,
    
df.name FileGroupName
,
    
i.fill_factor
,
    
i.is_unique
,
    
i.type_desc
,
    
ic.is_included_column
,
    
schema_name(o.schema_id)
SchemaName
  FROM
sys.indexes i
  INNER JOIN
sys.index_columns ic
     
ON      i.index_id = ic.index_id
AND
             
i.OBJECT_ID = ic.
OBJECT_ID
  INNER JOIN
sys.columns c
    
ON      ic.column_id = c.column_id
AND
          
i.OBJECT_ID = c.
OBJECT_ID
  INNER JOIN
sys.database_files df
    
ON i.data_space_id =
df.data_space_id
  INNER JOIN
sys.objects o
    
ON i.OBJECT_ID = o.
OBJECT_ID
  WHERE
    -- object_name(i.object_id) = @TableName AND
   
schema_name(o.schema_id) = @SchemaName
AND
   
i.index_id <>
1
  ORDER BY
i.index_id

image

The above script is useful to find table column indexes and I use it when I develop filters or objects that can go to WHERE part of the query panel while developing reports using Web Intelligence (Rich Client) or Desktop Intelligence and it boost the performance of the query and that is all what we need at the end of the day.

February 5, 2011

Distribute a System DSN to other machines in your environment

Filed under: Administration — Tags: , , , , , — Irfan @ 6:54 pm

If you are working in network services or doing a kind of administration work using Windows based systems, some times you may come across this kind of work, where it involves setting up new ODBC system DSN’s for a group of 5 or more developer’s to connect to servers, and where the environment is huge with different database servers to connect, develop, test their development.  It really becomes tedious to go to every developer’s machine and do the ODBC setups.    There is a quicker way to move the DSNs by hacking the registry, and transfer all the ODBC’s from machine A to machine B

Note : First take the backup of the registry and save it and keep it in a safe place (on a thumb drive or on network drive).  This is important because even if your system crashes you can restore the ODBC’s from this file.

Following are some of the steps that I have outlined to take the ODBC’s on one machine and transfer to other.

Go to machine A and do this:

     Go to Start menu on machine A.
     Click ‘Run’ or open a command prompt window.
     Enter regedit and then press enter, it will open the registry window
     Go to the following system registry key path and select it.
     HKEY_LOCAL_MACHINE\SOFTWARE\ODBC                                                 (this is where all the ODBC DSN’s are saved)
    
Now, Go to File –> click Export.                                                                     (export Registry File window opens)
     Enter a filename ‘allodbc.reg’ and save the file                                                  (on a Network location, say for instance N:\ODBC )

Go to machine B and do this:

      Go to Start menu on machine B.
     Click ‘Run’ or open a command prompt window.
     Enter regedit and then press enter, it will open the registry window
     Go to the following system registry path and highlight it:
    
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC
     Go to File –> click Import.                                                                               (import Registry File window opens)
    
Select the System registry file ‘allodbc.reg’ that you exported to N:\ODBC (or get it from your location) and Click Import.

You will now see all the ODBC DSNs from the machine A when you go to Start –> Settings –> Control Panel  -> Administrative Tools –> Data Sources (ODBC).   Talk to the hand

Create a free website or blog at WordPress.com.